MySQL索引失效原理是什么？

刺骨的言语ヽ痛彻心扉 2024-04-06 10:39 73阅读 0赞

索引创建原则  
避免索引失效  
（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='上海市';

![f09bfd9d57ef4aea98bc3189c4a6dc3a.png][]

（2）最左前缀法则

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

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

![f441bad616324f3f870571fcce43aa86.png][]  
匹配最左前缀法则，走索引：

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='上海市';

![d6ca9a6214df47659b4bd5096ceb01fd.png][]

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

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

![fd136f15a2de4fbc9f9a343d63c53cb0.png][]

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

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

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

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

![8f385aa6778d4383a6c9e2cb0fd75e5f.png][]

（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索引生效

![809e5bbc62f6486eb47c3d4b262ca389.png][]

根据前面的两个字段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)='科技';

![ee05a1ce3deb49f7be7ef0c8877ec542.png][]

（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索引字段是无效的

![14ff4a25940b4ce5ac47dbf2eccdd402.png][]

由于，在查询时，没有对字符串加单引号，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';

![3495391da51d4d03be106921e76816f8.png][]

（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 '%传智播客%';

![14288408890041609ab72f58f318d772.png][]

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

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子字段无索引，所以索引失效。

![bcdde8003bb14a968a357f3938dd05ea.png][]  
（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='上海市';

![4b5dd71f291d4f90a56651669ebe2dae.png][]

（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');

![4ac7ebc99e54419f8aeb05135a6ceac1.png][]

[f09bfd9d57ef4aea98bc3189c4a6dc3a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5c0b3b864ff442edaebf04ecd82d6a6d.png
[f441bad616324f3f870571fcce43aa86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/17ff7614c472468a8150c130687b1136.png
[d6ca9a6214df47659b4bd5096ceb01fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/06c3fcf05e8d4d28889846fada2a600a.png
[fd136f15a2de4fbc9f9a343d63c53cb0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9c5f4e6bd9ac4efa8eac6b2ce13b3064.png
[64d506a7cf4347b9993de2c942f7b6fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ebaf553539b3455184fbc8a982d1ae6d.png
[8f385aa6778d4383a6c9e2cb0fd75e5f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9468257164b3492c94902ebc18609b60.png
[809e5bbc62f6486eb47c3d4b262ca389.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/79c68208fbae420e937b69935db28f14.png
[ee05a1ce3deb49f7be7ef0c8877ec542.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f1ade20339ba4b74b0bd86dc658dc7ea.png
[14ff4a25940b4ce5ac47dbf2eccdd402.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5e84fa8e7dae4dc6af1d1a2f356ff85d.png
[3495391da51d4d03be106921e76816f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e8b19523c28a4251bac55ea5e1a603fb.png
[14288408890041609ab72f58f318d772.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c20622d637fa41bda131187b5b438804.png
[bcdde8003bb14a968a357f3938dd05ea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/34d28dcf17294d5e89c3ff3c9ab5d183.png
[4b5dd71f291d4f90a56651669ebe2dae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/33088ce6f5c8410397eee05d19b88f5e.png
[4ac7ebc99e54419f8aeb05135a6ceac1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/709dfd56cc5843e0a04c954dd991e2fb.png