索引失效的几种情况

目录

1. 索引为什么会失效？

2. 索引失效的几种情况

2.1  对查询字段使用函数导致索引失效

2.2 对查询字段做计算导致索引失效

2.3. 类型转换导致索引失效

2.4 范围查找时右边的索引字段会失效

2.5 不等于(!= 或 <>) 导致索引失效

2.6 IS NULL 可以使用索引，IS NOT NULL 会索引失效

2.7 LIKE 通配符条件以 "%" 开头会导致索引失效

2.8 OR 关键字前后存在非索引的字段列，索引失效

---

1. 索引为什么会失效？

其实用不用索引，最终都是由优化器说了算，优化器是数据库比较偏底层的一个东西，这里我就不详细说明了，我们在数据库执行一条SQL指令的时候，都会经过优化器，优化器会以效率最优的方式来处理我们的SQL语句，我们在做一条查询语句的时候，有可能使用索引效率会更高，有可能不使用索引效率会更高，这都是有优化器说了算的，这也就产生了索引会失效的情况，下面我就来详细说一说索引可能失效的几种情况。

2. 索引失效的几种情况

2.1  对查询字段使用函数导致索引失效

如下所示，我为 student 学生表 name 字段创建索引，然后我列举了两种查询方式

# 为 student 表的 name 字段创建索引
CREATE INDEX idx_name ON student(name);

# 查询方式一：采用 like 右模糊查询
SELECT * FROM student WHERE name LIKE 'abc%';

# 查询方式二：采用函数left查询
SELECT * FROM student LEFT(student.name,3) = abc;

第一种有模糊查询可以使用索引，因为我们给出的条件是 like "abc%"，左边的查询条件是固定的，而varcher 字段创建索引也正是根据字母的顺序来排列的，所以当左边确定时，底层优化器会使用索引；如果查询条件是 like "%abc%"，则无法使用索引，因为最左边和最右边都是模糊查询，不能充分利用索引的有序性，实际上执行的是全表扫描。

第二种 left 函数会导致索引失效，如果执行该语句，那么底层会将每一条数据都取出来，挨个执行 left 函数，然后再去对比查询条件 abc，执行的是全表扫描，如此一来就不会用到索引，导致索引失效。

2.2 对查询字段做计算导致索引失效

如下，给 stuno 字段添加索引

# 给 student 学生表 stuno 字段创建索引
CREATE INDEX idx_stuno ON student(stuno);

# 第一种字段，在查询条件字段上做运算导致索引失效
SELECT * FROM student WHERE student.stuno+1 = 9001;

# 第二种方式，直接使用字段作为条件查询
SELECT * FROM student WHERE student.stuno = 9000;

这里只是举一个例子，实际开发时查询条件肯定不会这么写的；

第一种查询：导致索引失效，因为查询条件的字段有计算参与，所以底层会先把每个字段取出然后做计算，再将计算后得到的结果作为条件去匹配查询，执行的是全表扫描操作，不会使用到索引。

第二种查询：可以使用索引，因为是直接的等值查询，通过索引可以精准定位到数据所在的位置，直接查询数据。 

2.3. 类型转换导致索引失效

如下，查询条件字段为 name，name字段的类型为 varchar 字符串

# 为 student 表的 name 字段创建索引
CREATE INDEX idx_name ON student(name);

# 方式一：将查询条件标注为字符串
SELECT * FROM student WHERE name = '123%';

# 方式二：不把查询条件标注为字符串，让其底层帮我们做转化
SELECT * FROM student WHERE name = 123;

方式一查询：可以使用索引，因为原本 name 字段的类型就是 varchar ，底层已经为 name 字段排好序了，可以精准查找。

方式二查询：索引会失效，因为 name 字段是 varchar，但我们在查询语句中并没有标注，和刚才的使用函数导致索引失效原理一样，底层再执行此SQL语句时，会先把每条数据的 name 字段都取出来，做一个隐式函数从 varchar 类型转为 int 类型，导致索引失效。

2.4 范围查找时右边的索引字段会失效

# 给 student 学生表age，classId，name三个字段创建联合索引
CREATE INDEX idx_age_classid_name ON student(age,classId,name);

# 通过三个字段查询某条数据
SELECT * FROM student WHERE student.age = 30 and student.classId > 20 AND student.name = 'abc';

可以看到，在上面，我为 student 学生表建立了一个三个字段的联合索引，当执行SQL查询语句时，因为 age 字段是联合字段的第一个，底层优化器会用到该字段；

而第二个字段 classId 的查找条件是一个范围，底层优化器实际上会先根据联合索引 classId 字段去寻找 classId = 20 的那条数据，然后将该条数据后面的数据全部读取到内存中，然后再利用第三个字段的条件挨个去匹配满足条件的数据；

因此第三个联合索引字段 name 就用不上了，所以在联合索引中，如果前面的字段使用了范围查找，会导致联合索引中排在后面的字段索引失效。

在本题中，要想避免这种情况的发生，我们就需要在创建联合索引时将 name 字段排在 classId 字段的前面，这样就算我们编程时查找顺序不按照联合字段顺序写，但底层优化器还是会按照联合字段索引的先后顺序去查询。

在实际开发过程中，往往会遇到查询金额或者日期的SQL语句，从SQL优化的角度和索引的角度来说，建议将这些范围查找放在WHERE语句的最后。

2.5 不等于(!= 或 <>) 导致索引失效

# 为 student 表的 name 字段创建索引
CREATE INDEX idx_name ON student(name);

# 通过不等于查询满足条件的数据
SELECT * FROM student WHERE student.name != 'xxx';

为 name 字段创建索引之后，如果查询条件是 = ，则可以完美的使用索引的有序性精准查找。

但是，在次题中查询条件是 != ，则无法使用索引，底层只能一条数据一条数据的获取，所以，使用 != 会导致索引失效。

2.6 IS NULL 可以使用索引，IS NOT NULL 会索引失效

这个其实跟上面的 "=" ，"!= " 非常类似，IS NULL可以理解为等于某个值，NULL 值在索引中也是有顺序的，所以可以根据索引精准查询，而IS NOT NULL 则会导致索引失效。

所以，建议在设计数据库表之初，就给字段设置 NOT NULL 条件约束，防止此类情况的出现。

2.7 LIKE 通配符条件以 "%" 开头会导致索引失效

这个与上面的我列举的第一种情况使用函数很类似，也是很好理解的，我们在通配符 LIKE 中，如果前面是确定的值，例如 LIKE "aaa%"，前面是确定的值，则仍然可以根据索引查找，因为索引底层会根据字母排序，底层优化器是可以利用到索引的。而如果直接以 "%" 开头，则索引会失效。

2.8 OR 关键字前后存在非索引的字段列，索引失效

# 给 age 字段添加索引
CREATE INDEX idx_age ON student(age);

# 查询条件有两个，满足其一即可，使用 or 连接
SELECT * FROM student WHERE student.age = 20 OR student.name = 'zhangsan';

我们来看分析一下，首先我们给 age 字段添加了索引，那么在执行 age = 20 条件时，底层优化器会使用到索引，但是因为是 OR 连接，满足一个条件就可以，所以我们还需要判断另一个字段 name，而 name  没有添加索引，所以我们就只能使用全表扫描。

如此一来，还不如直接来一遍全表扫描。因此，当 OR 关键字前面条件有字段是非索引字段时，会导致索引失效。如果想使用索引，那么 OR 关键字前后的字段都必须是索引列。