mysql千万级别数据查询优化(mysql千万级数据量根据索引优化查询速度的实现)
mysql千万级别数据查询优化
mysql千万级数据量根据索引优化查询速度的实现(一)索引的作用
索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了。
能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低。代价小且行之有效的解决方法就是合理的加索引。索引使用得当,能使查询速度提升上千倍,效果惊人。
(二)mysql的索引类型:
mysql的索引有5种:主键索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。
唯一索引和全文索引用的很少,我们主要关注主键索引、普通索引和聚合索引。
1)主键索引:主键索引是加在主键上的索引,设置主键(primary key)的时候,mysql会自动创建主键索引;
2)普通索引:创建在非主键列上的索引;
3)聚合索引:创建在多列上的索引。
(三)索引的语法:
查看某张表的索引:show index from 表名;
创建普通索引:alter table 表名 add index 索引名 (加索引的列)
创建聚合索引:alter table 表名 add index 索引名 (加索引的列1,加索引的列2)
删除某张表的索引:drop index 索引名 on 表名;
(四)性能测试
测试环境:博主工作用台式机
处理器为Intel Core i5-4460 3.2GHz;
内存8G;
64位windows。
1:创建一张测试表
|
DROP TABLE IF EXISTS `test_user`; CREATE TABLE `test_user` ( `id` bigint (20) PRIMARY key not null AUTO_INCREMENT, `username` varchar (11) DEFAULT NULL , `gender` varchar (2) DEFAULT NULL , ` password ` varchar (100) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
存储引擎使用MyISAM是因为此引擎没有事务,插入速度极快,方便我们快速插入千万条测试数据,等我们插完数据,再把存储类型修改为InnoDB。
2:使用存储过程插入1千万条数据
|
create procedure myproc() begin declare num int ; set num=1; while num <= 10000000 do insert into test_user(username,gender, password ) values (num, '保密' , PASSWORD (num)); set num=num+1; end while; end |
|
call myproc(); |
由于使用的MyISAM引擎,插入1千万条数据,仅耗时246秒,若是InnoDB引擎,就要花费数小时了。
然后将存储引擎修改回InnDB。使用如下命令: alter table test_user engine=InnoDB;此命令执行时间大约耗时5分钟,耐心等待。
tips:这里是测试,生产环境中不要随意修改存储引擎,还有alter table 操作,会锁整张表,慎用。其次:myisam引擎没有事务,且只是将数据写到内存中,然后定期将数据刷出到磁盘上,因此突然断电的情况下,会导致数据丢失。而InnDB引擎,是将数据写入日志中,然后定期刷出到磁盘上,所以不怕突然断电等情况。因此在实际生产中能用InnDB则用。
3:sql测试
|
select id,username,gender, password from test_user where id=999999 |
耗时:0.114s。
因为我们建表的时候,将id设成了主键,所以执行此sql的时候,走了主键索引,查询速度才会如此之快。
我们再执行select id,username,gender,password from test_user where username='9000000'
耗时:4.613s。
我们给username列加上普通索引。
|
ALTER TABLE `test_user` ADD INDEX index_name(username) ; |
此过程大约耗时 54.028s,建索引的过程会全表扫描,逐条建索引,当然慢了。
再来执行:selectid,username,gender,password from test_user where username='9000000'
耗时:0.043s。
再用username和password来联合查询
|
select id,username,gender, password from test_user where username= '9000000' and ` password `= '*3A70E147E88D99888804E4D472410EFD9CD890AE' |
此时虽然我们队username加了索引,但是password列未加索引,索引执行password筛选的时候,还是会全表扫描,因此此时
查询速度立马降了下来。
耗时:4.492s。
当我们的sql有多个列的筛选条件的时候,就需要对查询的多个列都加索引组成聚合索引:
加上聚合索引:ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
再来执行:
耗时:0.001s。
开篇也说过软件层面的优化一是合理加索引;二是优化执行慢的sql。此二者相辅相成,缺一不可,如果加了索引,还是查询很慢,这时候就要考虑是sql的问题了,优化sql。
Tips:
1:加了索引,依然全表扫描的可能情况有:
索引列为字符串,而没带引号;
索引列没出现在where条件后面;
索引列出现的位置没在前面。
2:关联查询不走索引的可能情况有:
关联的多张表的字符集不一样;
关联的字段的字符集不一样;
存储引擎不一样;
字段的长度不一样。
到此这篇关于mysql千万级数据量根据索引优化查询速度的实现的文章就介绍到这了,更多相关mysql千万级索引优化查询内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://liboyi.blog.csdn.net/article/details/52192551
- mysql数据类型和使用方法(MySQL数据类型全解析)
- django操作默认数据库(Django数据库类库MySQLdb使用详解)
- mysql触发器入门(MySQL中触发器和游标的介绍与使用)
- mysql语句运行顺序(浅谈mysql执行过程以及顺序)
- innodb和myisam(Mysql InnoDB和MyISAM区别原理解析)
- mysql 查询都是0的值(MySQL按小时查询数据,没有的补0)
- mysql模糊匹配语句(MySQL 数据库 like 语句通配符模糊查询小结)
- docker查找redis配置文件(解决docker重启redis,mysql数据丢失的问题)
- mysql8.0使用(MySQL 8.0新特性 — 检查性约束的使用简介)
- mysql主键什么情况用uuid(Mysql主键UUID和自增主键的区别及优劣分析)
- mysql中常用的三种存储引擎的区别(MySQL 存储过程的优缺点分析)
- mysql怎么设置某个字段默认值(MySQL表字段时间设置默认值)
- netcore连什么数据库好(.Net Core导入千万级数据至Mysql的步骤)
- mysql的innodb引擎数据结构(MySQL InnoDB架构的相关总结)
- mysql索引面试总结(Mysql数据库索引面试题程序员基础技能)
- mysql拼接和过滤(mysql 如何动态修改复制过滤器)
- 王牌部队,你看的剧情我看的时尚(你看的剧情我看的时尚)
- 被鉴定的古董价值300万 当心,你可能遇到诈骗了(被鉴定的古董价值300万)
- 英语难学吗(初中英语难学吗)
- 如何追女孩子(如何追女孩子的技巧和方法)
- 是不是快乐全被你拿走了(而是你得到的)
- 世界上只有妈妈好(世界上只有妈妈好的歌词)
热门推荐
- 性能监视器中常用计数器
- python中的time时间模块使用知识(python实现简单日期工具类)
- 云服务器用于什么方面(美国云服务器与哪些因素有关?)
- dede内容模型管理(Dede网站修改模板路径的方法 拒绝模板泄漏被盗)
- dockerservice启动参数(docker.service启动报错的一次排查详解)
- 如何用python处理excel表格(零基础使用Python读写处理Excel表格的方法)
- ASP.NET中获取匿名对象的属性值
- nginx给需要转发的链接添加参数(Nginx 根据URL带的参数转发的实现)
- dedecms标签使用(DEDECMS 自动缩略图无法生成问题的解决方法)
- 微信小程序function怎么使用(微信小程序在{{ }}中直接使用函数的方法示例)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9