年轻人不讲武德，乱用索引，你到底走了多少弯路？
本篇是索引相关的第 2 篇文章。上一篇重点介绍的是使用 explain 执行计划查看索引执行情况，以便于快速定位哪张表有索引使用问题。
本篇主要介绍的是索引失效的常见原因和如何用好索引，跟上一篇正好承上启下，给有需要的朋友一个参考。
本文将从以下几个方便进行讲解：
1.索引失效常见原因：
2.索引失效常见误区：
3.索引设计的几个建议：
准备工作
查看当前 mysql 的版本：
查出当前版本为：8.0.21
创建一张表 test1
插入两条数据：
查询一下：
结果：
此外建立了两个索引：idx_code_age_name( code , age , name )联合索引 和 idx_height(height )普通索引。
一. 索引失效常见原因
1.不满足最左前缀原则
第 1 种情况：
where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、name 。
执行 sql 如下：
结果：
从图中标红的地方可以看出已经走了联合索引 idx_code_name_age，并且索引的长度是 188， 188 = 30 * 3 + 2 + 30 * 3 + 2 + 4 ，索引是使用充分的，索引使用效率最佳。
有些朋友可能会问：索引长度为什么是这样计算的？
答：请参考《 explain | 索引优化的这把绝世好剑，你真的会用吗？ 》，里面给出了非常详细的讲解。
第 2 种情况：
where 条件后的字段包含了联合索引的所有索引字段，顺序是不按照： code 、 age 、name。
执行 sql 如下：
结果：
从上图中看出执行结果跟第一种情况一样。
注意：这种情况比较特殊，在查询之前 mysql 会自动优化顺序。
第 3 种情况：
where 条件后的字段包含了联合索引中的： code 字段
执行 sql 如下：
结果：
从上图看出也走了索引，但是索引长度有所变化，现在变成了 92 ， 92 = 30*3 + 2 ，只用到了一个索引字段 code，索引使用不充分。
第 4 种情况：
where 条件后的字段包含了联合索引中的： age 字段
执行 sql 如下：
结果：
从上图中看变成了全表扫描，所有的索引都失效了。
第 5 种情况：
where 条件后的字段包含了联合索引中的： name 字段
执行 sql 如下：
结果：从上图中看变成了全表扫描，所有的索引都失效了。
第 6 种情况：
where 条件后的字段包含了联合索引中的： code 和 age 字段
执行 sql 如下：
结果：
从上图中看出还是走了索引，但是索引长度变成了： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段 code 和 age，索引使用也不充分。
第 7 种情况：
where 条件后的字段包含了联合索引中的： code 和 name 字段
执行 sql 如下：
结果：
从上图中看出走的索引长度跟第 1 种情况一样，长度也是 92 。也就是说只用到了一个索引字段 code ，而 age 字段的索引失效了。
第 8 种情况：
where 条件后的字段包含了联合索引中的： age 和 name 字段
执行 sql 如下：
结果：
从上图中看出变成了全表扫描，所有的索引都失效了。
小结：
code code、age code、age、name
code age name
如果中间出现断层，如： code、name ，只会走第一个索引 code，从断层后的索引都会失效。
age name age,name
2.范围索引列没有放在最后
where 条件后的字段 age 用了大于等于，具体 sql 如下：
结果：
从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段 code 和 age ，而 name 字段的索引失效了。
如果范围查询的语句放到最后：
结果：
什么鬼？怎么索引长度还是： 96 ？
这是一个非常经典的错误
范围查询放最后是指创建联合索引的字段顺序，现在的顺序是：
调整一下把索引字段 name 和 age 的顺序调整一下：
再执行上面的 sql，结果：
从上图中看出索引长度变成： 188 ，索引使用充分了。
回过头再执行刚开始的那条 sql：
结果：
什么？
索引长度也是： 188 。
注意：范围查询放最后，指的是联合索引中的范围列放在最后，不是指 where 条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使 where 条件中的范围列没放最后也能正常走到索引。
3.使用了 select *
其实在《阿里巴巴开发手册》中也明确说了，禁止使用 select * ，这是为什么呢？
结果：
从上图中看出走了全表扫描。
那么如果查询的是索引列：
结果：
从图中可以看出这种情况走了全索引扫描，比全表扫描效率更高。
其实这里用到了： 覆盖索引 。
如果 select 的列都是索引列，则被称为 覆盖索引 。
如果 select 的列不只包含索引列，则需要 回表 ，即回到表中再查询出其他列，效率相当更低一些。 select * 大概率需要查询非索引列，需要 回表 ，因此要少用。
当然，本文中很多示例都使用了 select * ，主要是我表中只有两条数据，为了方便演示，正常业务代码中是要杜绝这种写法的。
4.索引列上有计算
执行 sql 如下：
结果：从上图中可以看出变成全表扫描了，由此可见在索引列上有计算，索引会失效。
5.索引列上使用了函数
如果在索引列加某个函数，具体 sql 如下：
结果：从上图中可以看出变成全表扫描了，由此可见在索引列上加了函数，索引也会失效。
6.字符类型没加引号
废话不多说直接上 sql：
结果：
从图中看出走的全表扫描，索引失效了。
为什么索引会失效呢？
这里有些朋友可能会有点懵。
答： name 字段是 字符类型 ，而等于号右边的是 数字类型 ，类型不匹配导致索引丢失。
所以在使用字符类型字段做判断时，一定要加上单引号。
类型不匹配导致索引丢失问题，是我们平时工作中非常容易忽视的问题，一定要引起足够的重视
7.用 is null 和 is not null 没注意字段是否允许为空
查询 sql 如下：
结果都是：从上图中看出都是全表扫描，索引都失效了。
如果 height 字段改成允许为空的呢？
上面第一条 sql 执行结果：
从上图中看出走了 ref 类型的索引。
上面第二条 sql 执行结果：
从上图中看出走了 range 类型的索引。
小结
如果字段不允许为空，则 is null 和 is not null 这两种情况索引都会失效。
如果字段允许为空，则 is null 走 ref 类型的索引，而 is not null 走 range 类型的索引。
8.like 查询左边有 %
like 查询主要有三种情况：
like '%a'
like 'a%'
like '%a%'
先看看第一种情况：
结果：
从上图看出走的全表扫描，索引失效了。
再看看第二种情况：
结果：
从上图看出走的 range 类型的索引。
最后看看第三种情况：
结果：从上图看出走的全表扫描，索引也失效了。
从这三种结果看出 like 语句只有 % 在右边才能走索引。
如果有些场景就是要使用 like 语句 % 在左边该怎么办呢？
答案：使用覆盖索引
具体 sql 如下：
结果：
从上图看出走的 index 类型的全索引扫描，相对于全表扫描性能更好。
当然，最佳实践是在 sql 中要避免 like 语句 % 在左边的情况，如果有这种业务场景可以使用 es 代替 mysql 存储数据。
小结：
like '%a' 索引失效
like 'a%' 走 range 类型索引
like '%a%' 索引失效
9.使用 or 关键字时没有注意
结果：
从上图中看出走了 range 类型的索引，不是没问题吗？
再把 sql 改一下：
结果：
从上图中可以看出变成了全表扫描，索引失效了。
我们不妨单独查询一下：
结果：
结果：
两种单独查询的情况都走了 ref 类型的索引，但是使用 or 关键字后 sql 的索引会失效。
那么，我们在想使用 or 的场景，又想让索引有效，该怎么办呢？
没错，使用 union 关键字，但是跟 or 关键字的语法稍微有点区别，不过查询的数据结果是一样的。
上面 sql 执行结果如下：
我们看到走了 ref 类型索引。
or 关键字会让索引失效，可以用 union 代替
二. 索引失效的常见误区
1.使用 not in 会导致索引失效
用法如下：
结果：
从上图中看出是走了 range 类型索引的，并没失效。
需要特别说明的是 mysql5.7 和 5.8 不同的版本效果不一样，5.7 中这种情况 sql 执行结果是全表扫描，而 5.8 中使用了 range 类型索引。
2.使用不等于号会导致索引失效
用法如下：
结果：
从图中看出走了 range 类型的索引。
★
需要特别说明的是 mysql5.7 和 5.8 不同的版本效果不一样，5.7 中这种情况 sql 执行结果是全表扫描，而 5.8 中使用了 range 类型索引。5.7 中如果想使用索引该怎么办呢？答案：使用大于和小于代替不等于。
”
在这里温馨的提醒一声，不等于号不只是 != ，还包括 <> 。
3.order by 索引字段顺序不当导致索引失效
sql 中除了 where 后面的字段能走索引之外， order by 后面的字段也能走索引。
结果：
从上图中看出走了 ref 类型的索引，索引长度是 92 ，并且没有额外信息。
但是如果把 order by 后面的条件改成如下两种排序：
结果：
从上图中看出还是走了 ref 类型的索引，索引长度是 92 ，但是额外信息中提示： Using filesort ，即按文件重排序。
上面两个例子能够看出有没有使用索引跟 where 后面的条件有关，而跟 order by 后面的字段没关系。
而需不需要按文件重排序，则跟 order by 后面的字段有直接关系。
问题来了，额外信息中提示： Using filesort 这种该如何优化？
答：这种情况一般是联合索引中索引字段的顺序，跟 sql 中 where 条件及 order by 不一致导致的，只要顺序调整一致就不会出现这个问题。
三. 索引设计的几个建议：
优先使用唯一索引，能够快速定位
为常用查询字段建索引
为排序、分组和联合查询字段建索引
一张表的索引数量不超过 5 个
表数据量少，可以不用建索引
尽量使用占用空间小的字段建索引
用 idx_或 unx_等前缀命名索引，方面查找
删除没用的索引，因为它会占一定空间
四. 彩蛋：
特别说明：索引失效除了上述的常见问题之外， mysql 通过索引扫描的行记录数超过全表的 10%~30% 左右,优化器也可能不会走索引，自动变成全表扫描。
送给大家一个避坑口诀：
全职匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围列后全失效
like 百分写最右，覆盖索引不写*
不等空值还有 or ， 索引影响要注意；
字符字段引号不能丢，sql 优化有诀窍。
最后说一句(求关注，别白嫖我)
如果这篇文章对您有所帮助，或者有所启发的话，帮忙扫描下发二维码关注一下，您的支持是我坚持写作最大的动力。
求一键三连：点赞、转发、关注哦！
还未添加个人签名 2020.11.09 加入
还未添加个人简介
评论