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

一.索引介绍

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

二.应用场景

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

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

三.环境准备

1.随便创建一个User表,然后向其中插入一些测试数据

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

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

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

show index from User;

显示下面结果表示成功

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

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

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

显示结果:

2.最佳左前缀法则，带头大哥不能死，中间兄弟不能少。
   双重或者多重索引情况下,第一个索引条件和中间的索引条件不能少,下面就是索引条件id没有的情况下的数据显示

3.不要在索引列上做任何操作
   如果在索引上加上一些sql的操作会使索引失效,前提是其中一个索引不是id,如果是的话是可以做一些简单的操作的

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

显示结果:

4.范围条件查询导致索引失效
   执行sql的时候如果进行范围查询的话会使索引失效,下面例子查询级别是范围range,会导致name索引查询失败

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

显示结果:

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

为*的情况下

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

显示结果:

为指定字段的情况下

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

显示结果:

6.使用不等于（！= 或者<>）不能使用索引

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

显示结果:

7.使用 is null 或者 is not null 也不能使用索引

explain select * from User where name is not null

显示结果:

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

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

显示结果:

9.少用or，用它来连接索引会失效

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

显示结果:

以上就完成了双重索引的失效和如何避免,在单索引和多重索引的情况下是一样的,只要注意即可。