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

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 

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

蜀州凯哥

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值