索引失效的几种情况

索引失效案例

表结构如下：

全值匹配我最爱

没有创建任何索引的情况下：

explain select * from student_info where student_id=25 and name='jksf';

发现它没有使用任何索引

给前面一个字段建立索引

create index X on student_info(student_id);
explain select * from student_info where student_id=25 and name='jksf';

发现它使用了索引

给查询的两个字段都添加索引

create index X on student_info(student_id);
create index Y on student_info(student_id,name);
explain select * from student_info where student_id=25 and name='jksf';

发现可能使用X,Y的索引，但是优化器选择了Y的索引

最佳左前缀法则

建立student_id和name的联合索引

create index Y on student_info(student_id,name);

执行下面的两条语句：

explain select SQL_NO_CACHE * 
from student_info 
where course_id=1 and name='ajdf' ;

explain select SQL_NO_CACHE * 
from student_info 
where course_id=1 and student_id=1;

发现第一条不能用索引，第二条可以用索引，这是因为我们遵循了最佳左前缀法则。所以我们建立联合索引时，必须按照严格的顺序。
要使用联合索引的话，必须先从左边考虑(必须包含左边的字段，才可以使用后面的字段)。一旦跳过某个字段，则后面的字段都没办法使用，但是前面的字段可以使用索引

主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了.

假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间:
我们按顺序添加101，则它会新建一个页来保存101，其他不会做任何操作
但我们添加9时，则他会把9添加到第一个页中，造成页分裂，第一页的一些记录会移动到第二页。

页面分裂和记录移位意味着什么?意味着:性能损耗!所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议:让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入

计算，函数，类型转化(自动或者手动)导致造成索引失效

我们只给name字段创建索引，执行下面两个语句：

create index x on student_info(name);

explain select SQL_NO_CACHE * 
from student_info 
where name like 'abc%';

explain select SQL_NO_CACHE *
from student_info si 
where left(name,3)='abc';

发现只有上面一个语句使用了索引，因为第二个有用到函数，所以导致索引失效

范围条件右边的列索引失效

当我们创建下面这个联合索引时，使用索引结果如下：

create index x on student_info(name,student_id,course_id);

#drop index x on student_info;
explain select SQL_NO_CACHE * 
from student_info 
where name='jzVeHb' and student_id>10 and course_id =10041;

我们将联合索引的顺序修改后，执行下面这个语句：

create index y on student_info(name,course_id,student_id);
#drop index y on student_info;
explain select SQL_NO_CACHE * 
from student_info 
where name='jzVeHb' and student_id>10 and course_id =10041;

两个语句相互对比，发现下面这个语句使用的索引字段更多。仔细查看，发现前一个语句使用了范围查询字段前面的所有字段，而范围查询后面的字段则没有使用上。
所以，建立联合字段索引时，需要把范围索引字段放在最后面，不然范围索引后面的字段则使用不到 (当然，你只更换查询语句里的顺序，肯定是不能改变效果的,优化器都会帮你修改，而索引是不会改变的)

<>和!=不等于索引失效

因为在B+树中查找不等于时，只能一个个去查找，导致索引失效

is null可以使用索引，is not null不能使用索引

因为is null相当于是等值，is not null相当于是不等于

结论:最好在设计数据表的时候就将字段设置为 NOT NULL约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串()。
拓展:同理，在查询中使用not like 也无法使用索引，导致全表扫描。

like以通配符%开头索引失效

OR前后存在非索引的列，索引失效

下列索引中只有一个时，or查询则会失效。必须有两个分别的索引，or查询才会用到索引(两个字段建立一个联合索引也会全表扫描)

create index x on student_info(student_id);
create index y on student_info(course_id);

explain select SQL_NO_CACHE * 
from student_info 
where student_id=1 or course_id =1;

因为or的话，相当于你两边都要；如果你走索引的话，相当于要先走一遍索引字段，然后再全表走没有索引的字段，这样还不如全表来的更快。

数据库和表的字符集同一使用utf8mb4

不同字符集会导致转换产生乱码，不同的字符集进行比较前会进行转换，造成索引失效