5 如何正当应用索引减速
tips：
500万条建表sql参照网盘sql脚本
[root@linux-141 bin]# ./mysql -u root -p itcast < product_list-5072825.sql索引是数据库优化最罕用也是最重要的伎俩之一, 通过索引通常能够帮忙用户解决大多数的MySQL的性能优化问题。
5.1 验证索引晋升查问效率
在咱们筹备的表构造product_list 中， 一共存储了 500多万记录；
mysql> select count(1) from product_list;+----------+| count(1) |+----------+| 5072825 |+----------+1 row in set (1.71 sec)mysql> 1） 依据ID查问
SELECT * FROM product_list WHERE id = 121926;查问速度很快， 靠近0s ， 次要的起因是因为id为主键， 有索引；
2). 依据store_name进行准确查问
执行用时4分钟
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';查看SQL语句的执行打算 ：
explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';解决计划 ， 针对store_name字段， 创立索引 ：
create index product_list_stname on product_list(store_name);索引创立实现之后，再次进行查问 ：
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
通过explain ， 查看执行打算，执行SQL时应用了方才创立的索引
-- 查看SQL语句的执行打算explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';5.2 索引的应用
5.2.1 筹备环境
create table `tb_seller` ( `sellerid` varchar (100), `name` varchar (100) not null, `nickname` varchar (50), `password` varchar (60), `status` varchar (1) not null, `address` varchar (100) not null, `createtime` datetime, primary key(`sellerid`))engine=innodb default charset=utf8; insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官网旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官网旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官网旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');create index idx_seller_name_sta_addr on tb_seller(name,status,address);5.2.2 防止索引生效
组合索引(name,status,address)
1) 全值匹配
对索引中所有列都指定具体值。
-- 全值匹配explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';ken_len = 3 * N + 2;-- name varchar(100) ==302-- status varchar(1) ==5-- address varchar(100) ==3022) 最左前缀法令
如果索引了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始，并且不跳过索引中的列。
匹配最左前缀法令，走索引：
explain select * from tb_seller where name='小米科技';
违反最左前缀法令 ， 索引生效：
explain select * from tb_seller where status='1';explain select * from tb_seller where status='1' and address='北京市';如果合乎最左法令，然而呈现跳跃某一列，只有最左列索引失效：
explain select * from tb_seller where name='小米科技' and address='北京市';3) 范畴查问左边的列
-- 应用范畴查问的状况，左边的列生效 explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';依据后面的两个字段name ， status 查问是走索引的， 然而最初一个条件address 没有用到索引。
4) 禁止列运算
-- 不要在索引列上进行运算操作， 索引将生效。explain select * from tb_seller where substring(name,3,2) ='科技';5) 字符串不加单引号
造成索引生效。
-- 字符串不加单引号,造成索引生效。explain select * from tb_seller where name='科技' and status='0';explain select * from tb_seller where name='科技' and status=0;
因为，在查问时，没有对字符串加单引号，MySQL的查问优化器，会主动的进行类型转换，造成索引生效。
6) 尽量应用笼罩索引
防止select *
尽量应用笼罩索引（只拜访索引的查问（索引列齐全蕴含查问列）），缩小select * 。
-- 尽量应用笼罩索引explain select * from tb_seller where name='科技' and status='0' and address='西安市';explain select name from tb_seller where name='科技' and status='0' and address='西安市';explain select name ,status from tb_seller where name='科技' and status='0' and address='西安市';explain select name ,status,address from tb_seller where name='科技' and status='0' and address='西安市';
如果查问列，超出索引列，也会升高性能。
explain select status,address ,password from tb_seller where name='科技' and status='0' and address='西安市';TIP : using index ：应用笼罩索引的时候就会呈现 using where：在查找应用索引的状况下，须要回表去查问所需的数据 using index condition：查找应用了索引，然而须要回表查问数据 using index ; using where：查找应用了索引，然而须要的数据都在索引列中能找到，所以不须要回表查问数据7) 正当应用or条件
用or宰割开的条件， 如果or前的条件中的列有索引，而前面的列中没有索引，那么波及的索引都不会被用到。
示例，name字段是索引列 ， 而createtime不是索引列，两头是or进行连贯是不走索引的 ：
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'; 8) 正当应用like查问
以%结尾的Like含糊查问，索引生效。
-- 如果仅仅是尾部含糊匹配，索引不会生效。如果是头部含糊匹配，索引生效。explain select * from tb_seller where name like '黑马程序员%';explain select * from tb_seller where name like '%黑马程序';explain select * from tb_seller where name like '%黑马程序员%';解决方案 ： 通过笼罩索引来解决
explain select sellerid from tb_seller where name like '%科技%';explain select sellerid,name from tb_seller where name like '%科技%';explain select sellerid,name,status,address from tb_seller where name like '%科技%';9) 正当评估索引执行
如果MySQL评估应用索引比全表更慢，则不应用索引。
-- 如果MySQL评估应用索引比全表更慢，则不应用索引。create index idx_seller_addr on tb_seller(address);explain select * from tb_seller where address='北京市';explain select * from tb_seller where address='西安市';10) is NULL和 is NOT NULL
<font color='red'>有时</font>索引生效。
-- is NULL和 is NOT NULL explain select * from tb_seller where name is null;explain select * from tb_seller where name is not null;
解决方案：把null值设置一个默认值
11) in和not in
in 走索引， not in 索引生效。
-- in 走索引， not in 索引生效。explain select * from tb_seller where sellerid in('oppo','xiaomi','sina');explain select * from tb_seller where sellerid not in ('oppo','xiaomi','sina');12) 单列索引和复合索引
尽量应用复合索引，而少应用单列索引 。
创立复合索引
create index idx_name_sta_address on tb_seller(name, status, address);就相当于创立了三个索引 ： name name + status name + status + address创立单列索引
create index idx_seller_name on tb_seller(name);create index idx_seller_status on tb_seller(status);create index idx_seller_address on tb_seller(address);数据库会抉择一个最优的索引（辨识度最高索引）来应用，并不会应用全副索引 。
5.3 查看索引应用状况
show status like 'Handler_read%'; show global status like 'Handler_read%'; mysql> show status like 'Handler_read%'; +-----------------------+---------+| Variable_name | Value |+-----------------------+---------+| Handler_read_first | 18 || Handler_read_key | 19 || Handler_read_last | 0 || Handler_read_next | 5072825 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 269 |+-----------------------+---------+7 rows in set (0.02 sec)mysql> Handler_read_first：索引中第一条被读的次数。如果较高，示意服务器正执行大量全索引扫描（这个值越低越好）。Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，示意索引失去的性能改善不高，因为索引不常常应用（这个值越高越好）。Handler_read_next ：依照键程序读下一行的申请数。如果你用范畴束缚或如果执行索引扫描来查问索引列，该值减少。Handler_read_prev：依照键程序读前一行的申请数。该读办法次要用于优化ORDER BY ... DESC。Handler_read_rnd ：依据固定地位读一行的申请数。如果你正执行大量查问并须要对后果进行排序该值较高。你可能应用了大量须要MySQL扫描整个表的查问或你的连贯没有正确应用键。这个值较高，意味着运行效率低，应该建设索引来补救。Handler_read_rnd_next：在数据文件中读下一行的申请数。如果你正进行大量的表扫描，该值较高。通常阐明你的表索引不正确或写入的查问没有利用索引。本文由传智教育博学谷 - 狂野架构师教研团队公布，转载请注明出处！
如果本文对您有帮忙，欢送关注和点赞；如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源