兄弟萌在学习 MySQL 索引机制时,想必都听说过索引失效的几大场景吧。
- 违反最左前缀法则,索引失效
- 范围查询右边的列,不能使用索引
- 以 % 开头的 like 模糊查询,索引失效
- 字符串不加单引号,导致索引失效
- 索引列上进行运算操作,索引将失效
- 用 or 分割开的条件,如果 or 前条件中的列有索引,而后面的列中没有索引,那么前面的列索引失效
- 如果 mysql 评估使用索引比全表慢,则不使用索引
但是光背是没有用的,你真的了解了它们为什么会失效吗?下面我们具体分析一下索引失效原理。
1. 为什么违反最左前缀法,索引会失效?
答:如果不遵循最左前缀法则,相当于把 a 干掉了,那么在没有 a 的情况下,b 肯定是无序的,因为 b 有序的条件是 a 相等的情况下。这时在无序的 B+ 树上找到需要的值就用不到索引,只能进行全表扫描。
2. 为什么范围查找的右边为什么索引会失效,如:explain select * from user where a>1 and b=1?
答:查找 a>1 & b=1 的数据,我们首先根据这个 a 的索引顺序能找到 a 大于 1 的这些数据,然后再去找 b = 1 的数据,这时发现 a>1 对应的这些 b 的数据是无序的,所以右边的索引失效了。
3. 为什么以 % 开头的 like 模糊查询,索引会失效?
答:如果 %a,去查找以 a 结尾的字符串,可以看到字符串尾部是没有顺序的,所以查询时只能进行全表扫描,就用不到索引。