目录一. 索引为什么会失效二. 索引失效的几种情况2.1 对查询字段使用函数导致索引失效2.2 对查询字段做计算导致索引失效2.3. 类型转换导致索引失效2.4 范围查找时右边的索引字段会失效2.5 不等于(! 或 ) 导致索引失效2.6 IS NULL 可以使用索引IS NOT NULL 会索引失效2.7 LIKE 通配符条件以 % 开头会导致索引失效2.8 OR 关键字前后存在非索引的字段列索引失效一. 索引为什么会失效其实用不用索引最终都是由优化器说了算优化器是数据库比较偏底层的一个东西这里我就不详细说明了我们在数据库执行一条SQL指令的时候都会经过优化器优化器会以效率最优的方式来处理我们的SQL语句我们在做一条查询语句的时候有可能使用索引效率会更高有可能不使用索引效率会更高这都是有优化器说了算的这也就产生了索引会失效的情况下面我就来详细说一说索引可能失效的几种情况。二. 索引失效的几种情况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.stuno1 9001; # 第二种方式直接使用字段作为条件查询 SELECT * FROM student WHERE student.stuno 9000;这里只是举一个例子实际开发时查询条件肯定不会这么写的第一种查询导致索引失效因为查询条件的字段有计算参与所以底层会先把每个字段取出然后做计算再将计算后得到的结果作为条件去匹配查询执行的是全表扫描操作不会使用到索引。第二种查询可以使用索引因为是直接的等值查询通过索引可以精准定位到数据所在的位置直接查询数据。2.3. 类型转换导致索引失效如下查询条件字段为 namename字段的类型为 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 学生表ageclassIdname三个字段创建联合索引 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 关键字前后的字段都必须是索引列。