索引失效的原因和如何避免

╰半橙微兮° 2022-03-18 08:36 635阅读 0赞

**一.索引介绍**

mysql索引: 是一种帮助mysql高效的获取数据的数据结构，这些数据结构以某种方式引用数据，这种结构就是索引。可简单理解为排好序的快速查找数据结构。如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩下的sql。

**二.应用场景**

适合条件:  
   主键自动建立唯一索引 频繁作为查询条件的字段因该创建索引 查询中与其他表关联的字段，外键关系建立索引,频繁更新的字段不适合建立索引 where条件里用不到的字段不建立索引 单键/复合索引的选择（高并发下倾向复合）查询中排序的字段因建立索引 查询中统计或分组字段

不适合条件:  
   频繁增删改的表 表记录太少 数据重复且分布平均的表字段。（重复太多索引意义不大

**三.环境准备**

1.随便创建一个User表,然后向其中插入一些测试数据  
![在这里插入图片描述][20190220103252265.png]

2.创建User表的单索引或者双重索引

create index idIndex on User(id);      //单索引
    create index index_id_name on User(id,name);   //双重索引

3.检查索引是否创建成功

show index from User;

显示下面结果表示成功  
![在这里插入图片描述][20190220103658600.png]

**四.双重索引环境下索引失效的原因和如何避免**

1.全值匹配  
   如果是单索引就一个查询参数即可(本例是id),在双重索引的情况下查询参数如果是两个设置的索引参数是最好的

explain select * from User where id = 1 and name ='李'

显示结果:  
![在这里插入图片描述][20190220104510520.png]  
2.最佳左前缀法则，带头大哥不能死，中间兄弟不能少。  
   双重或者多重索引情况下,第一个索引条件和中间的索引条件不能少,下面就是索引条件id没有的情况下的数据显示

![在这里插入图片描述][2019022010545545.jpg]  
3.不要在索引列上做任何操作  
   如果在索引上加上一些sql的操作会使索引失效,前提是其中一个索引不是id,如果是的话是可以做一些简单的操作的

explain select * from User where left(name,1) = '李' and username = '李国辉';

显示结果:  
![在这里插入图片描述][20190220110158422.png]  
4.范围条件查询导致索引失效  
   执行sql的时候如果进行范围查询的话会使索引失效,下面例子查询级别是范围range,会导致name索引查询失败

explain select * from User where id >1 and name ='李'

显示结果:  
![在这里插入图片描述][20190220110448902.jpg]

5.尽量使用覆盖索引减少使用select \*  
   如果查询是\*的话会导致最后的Extra为空,但是不影响索引的使用

为\*的情况下

explain select * from User where id =1 and name ='李国辉'

显示结果:  
![在这里插入图片描述][20190220111110899.jpg]  
为指定字段的情况下

explain select name from User where name ='李国辉'

显示结果:  
![在这里插入图片描述][20190220111504199.jpg]  
6.使用不等于（！= 或者<>）不能使用索引

explain select * from User where name != '李国辉'

显示结果:  
![在这里插入图片描述][20190220111907359.jpg]  
7.使用 is null 或者 is not null 也不能使用索引

explain select * from User where name is not null

显示结果:  
![在这里插入图片描述][20190220112219598.jpg]

8.like 已通配符开头（%abc）导致索引失效 （解决方法：使用覆盖索引）

explain select * from User where name like '%李%';

显示结果:  
![在这里插入图片描述][20190220112410607.jpg]  
9.少用or，用它来连接索引会失效

explain select * from User where name = '李国辉' or id = 2;

显示结果:  
![在这里插入图片描述][20190220112627288.jpg]  
以上就完成了双重索引的失效和如何避免,在单索引和多重索引的情况下是一样的,只要注意即可。

[20190220103252265.png]: /images/20220318/088fcbf2952c497d80dc8d25147756d1.png
[20190220103658600.png]: /images/20220318/a4bf8d524f2f41afb40772adda99b94b.png
[20190220104510520.png]: /images/20220318/1dec849f787c41e79b32c1aaaf69d808.png
[2019022010545545.jpg]: /images/20220318/6ef3b9b0d05e4c25a0625650665ddb67.png
[20190220110158422.png]: /images/20220318/24fac5c188f948f1902fbda68f22bf6b.png
[20190220110448902.jpg]: /images/20220318/d11f9bb328074af998271dfa7a469431.png
[20190220111110899.jpg]: /images/20220318/72ed9fa8fde3419996c815cccd8718db.png
[20190220111504199.jpg]: /images/20220318/8ff77760ed874fd8b25cc48dbbdfd818.png
[20190220111907359.jpg]: /images/20220318/0c4a713ede0842e38d63b05a5b6a4e74.png
[20190220112219598.jpg]: /images/20220318/fcb1261a1256431d8b3caf9f5087bd4d.png
[20190220112410607.jpg]: /images/20220318/b5d0428f9f0c496b87d61e9ff8b96975.png
[20190220112627288.jpg]: /images/20220318/fd496200e9294e39815741ff2001e120.png