mysql千万级数据量根据索引优化查询速度
时间:2019-05-14 17:22 来源:linux.it.net.cn 作者:IT
（一）索引的作用
索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十秒甚至更多，5秒以上就已经让人难以忍受了。
提升查询速度的方向一是提升硬件(内存、cpu、硬盘)，二是在软件上优化（加索引、优化sql；优化sql不在本文阐述范围之内）。
能在软件上解决的，就不在硬件上解决，毕竟硬件提升代码昂贵，性价比太低。代价小且行之有效的解决方法就是合理的加索引。
索引使用得当，能使查询速度提升上万倍，效果惊人。
（二）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引擎，插入100万条数据就要花费数小时了。
然后将存储引擎修改回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。
实际生产中的sql往往比较复杂，如果数据量过了百万，加了索引后效果还是不理想，使用集群。
Tips：
1：加了索引，依然全表扫描的可能情况有：
索引列为字符串，而没带引号；
索引列没出现在where条件后面；
索引列出现的位置没在前面。
2：关联查询不走索引的可能情况有：
关联的多张表的字符集不一样；
关联的字段的字符集不一样；
存储引擎不一样；
字段的长度不一样。
(责任编辑：IT)
| （一）索引的作用 索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十秒甚至更多，5秒以上就已经让人难以忍受了。 提升查询速度的方向一是提升硬件(内存、cpu、硬盘)，二是在软件上优化（加索引、优化sql；优化sql不在本文阐述范围之内）。 能在软件上解决的，就不在硬件上解决，毕竟硬件提升代码昂贵，性价比太低。代价小且行之有效的解决方法就是合理的加索引。 索引使用得当，能使查询速度提升上万倍，效果惊人。 （二）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引擎，插入100万条数据就要花费数小时了。 然后将存储引擎修改回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。 实际生产中的sql往往比较复杂，如果数据量过了百万，加了索引后效果还是不理想，使用集群。 Tips： 1：加了索引，依然全表扫描的可能情况有： 索引列为字符串，而没带引号； 索引列没出现在where条件后面； 索引列出现的位置没在前面。 2：关联查询不走索引的可能情况有： 关联的多张表的字符集不一样； 关联的字段的字符集不一样； 存储引擎不一样； 字段的长度不一样。 (责任编辑：IT) |