MySQL索引失效原理是什么？

目录

索引创建原则
避免索引失效
（1）全值匹配，对索引中所有列都指定具体值
（2）最左前缀法则
（3）范围查询右边的列，不能使用索引
（4）不要在索引列上进行运算操作，索引将失效
（5）字符串不加单引号，造成索引失效
（6）用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
（7）以%开头的Like模糊查询，索引失效。
（8）如果MySQL评估使用索引比全表更慢，则不使用索引。
（9）in 走索引， not in 索引失效

索引创建原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%

例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，因为年龄的唯一值个数比较多，性别只有两个选项 。性别的识别度是50%。 男 女

2. 经常使用where条件搜索的字段，例如user表的id name等字段。

3. 经常使用表连接的字段（内连接、外连接），可以加快连接的速度。

4. 经常排序的字段 order by，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。

* 注意：那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的 效率。

避免索引失效

（1）全值匹配，对索引中所有列都指定具体值

该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';

 

（2）最左前缀法则

如果索引了多列，这里指的是复合索引(联合索引)，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

注意：如果条件中包含了复合索引的全部字段，那么可以不考虑前后顺序。

匹配最左前缀法则，走索引： 

1.explain select * from tb_seller where name='小米科技有限公司'; -- key_len表示索引字段的长度即占字节个数，不同的编码表计算方式不一致
2.explain select * from tb_seller where name='小米科技有限公司' and status='1'; 
3.explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';

 

违反最左前缀法则 ， 索引失效：

4.explain select * from tb_seller where status='1';
5.explain select * from tb_seller where status='1' and address='上海市';

 

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

6.explain select * from tb_seller where name='小米科技有限公司' and address='上海市';
注意：上述sql语句跳跃了status这一列，所以上述sql语句只是对索引name生效，key_len的结果403只是name索引的长度，而address索引字段并没有起到所以效果。

注意：如果条件中包含了复合索引的全部字段，那么可以不考虑前后顺序。 

explain select * from tb_seller where address='上海市' and status='1' and name='小米科技有限公司' ;

 

（3）范围查询右边的列，不能使用索引

1.explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';
2.explain select * from tb_seller where name='小米科技有限公司' and status>'1' and address='上海市'; -- 只有name和status索引生效

 

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

（4）不要在索引列上进行运算操作，索引将失效

1.-- 3 表示索引 2 表示截取2个字符
select * from tb_seller where substring(name,3,2)='科技';

2.explain select * from tb_seller where substring(name,3,2)='科技';

 

（5）字符串不加单引号，造成索引失效

1.explain select * from tb_seller where name='小米科技有限公司' and status='1';
2.explain select * from tb_seller where name='小米科技有限公司' and status=1; -- 这里name索引字段生效，status索引字段是无效的

 

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

（6）用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

1.explain select * from tb_seller where name='传智播客教育科技有限公司' and createtime = '2088-01-01 12:00:00';
2.explain select * from tb_seller where name='传智播客教育科技有限公司' or createtime = '2088-01-01 12:00:00'; 

 

（7）以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

1.explain select * from tb_seller where name like '传智播客%';
2.explain select * from tb_seller where name like '%传智播客';
3.explain select * from tb_seller where name like '%传智播客%';

 

解决方案 ：通过覆盖索引来解决.

1.explain select sellerid from tb_seller where name like '%传智播客%';
2.explain select sellerid,name from tb_seller where name like '%传智播客%';
3.explain select sellerid,name,status,address from tb_seller where name like '%传智播客%';
4.explain select sellerid,name,status,address,password from tb_seller where name like '%传智播客%';
说明：解决上述sql语句索引失效情况需要使用覆盖索引，而password子字段无索引，所以索引失效。

（8）如果MySQL评估使用索引比全表更慢，则不使用索引。 

1.show index from tb_seller; -- 查看下索引
2.create index idx_address on tb_seller(address); -- 单独创建 address字段为索引
3.explain select * from tb_seller where address='北京市'; -- 走索引，反而效率更低，全表扫描
4.explain select * from tb_seller where address='上海市';

 

（9）in 走索引， not in 索引失效

1.explain select * from tb_seller where sellerid in('baidu','huawei','xiaomi');

2.explain select * from tb_seller where sellerid not in('baidu','huawei','xiaomi');