MYSQL索引失效精讲

👏作者简介：大家好，我是小周同志，25届双非校招生Java选手，很高兴认识大家

📕学习出处：本文是学自小林coding (xiaolincoding.com) 网站的MYSQL图解篇

🔥如果感觉博主的文章还不错的话，请👍三连支持👍一下博主哦

 

对索引使用左或者左右模糊匹配

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

explain sql查询语句之后的type字段会为All，也就是全表扫描

type字段的类型 执行效率从低到高的顺序为：

• All（全表扫描）；

• index（全索引扫描）；

• range（索引范围扫描）；

• ref（非唯一索引扫描）；

• eq_ref（唯一索引扫描）；

• const（结果只有一条的主键或唯一索引扫描）。

为什么 like 关键字左或者左右模糊匹配无法走索引呢？

• 因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

  举例：

  假设我们要查询 name 字段前缀为「林」的数据，也就是 name like '林%'，扫描索引的过程：

  • 首节点查询比较：林这个字的拼音大小比首节点的第一个索引值中的陈字大，但是比首节点的第二个索引值中的周字小，所以选择去节点2继续查询；

  • 节点 2 查询比较：节点2的第一个索引值中的陈字的拼音大小比林字小，所以继续看下一个索引值，发现节点2有与林字前缀匹配的索引值，于是就往叶子节点查询，即叶子节点4；

  • 节点 4 查询比较：节点4的第一个索引值的前缀符合林字，于是就读取该行数据，接着继续往右匹配，直到匹配不到前缀为林的索引值。

  如果使用 name like '%林' 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

对索引使用函数

有时候我们会用一些 MySQL 自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效。

(例子取出小林coding图解MYSQL篇，有点懒的想例子了)

除非你添加的索引是也是使用函数的字段，比如：select * from t_user where length(name)=6;

• 这时候explain的type肯定是ALL，除非 alter table t_user add key idx_name_length ((length(name)));

 对索引进行表达式计算

在查询条件中对索引进行表达式计算，也是无法走索引的。

原因跟函数是差不多的，索引b+树不会存储运算结果，MYSQL也没有进行优化，比如 id+1=2 转换成 id=1 ，但MYSQL并没有实现这个功能。

对索引隐式类型转换

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

• 如果索引字段类型为varchar，那么你如果条件查询是int的话，会导致索引失效，从而全表扫描。

• 如果索引字段类型为int，那么即使条件查询的数值是varchar，也会转换成int，不会索引失效。

为什么会这样呢？

• 《mysql45讲》有一个简单的测试方式，就是通过 select “10” > 9 的结果来知道MySQL 的数据类型转换规则是什么：

  • 如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；

  • 如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码） ，那么"10"字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0

联合索引非最左匹配 

联合索引：那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。

• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

为什么联合索引不遵循最左匹配原则就会失效？

• 在联合索引的情况下，数据是按照第一列排序，第一列相同最会按照第二列排序。

• 也就是说，我们要想使用联合索引中尽可能多的列，查询条件就得从联合索引中最左边的字段开始连续的列才行

WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

这是 因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

总结：

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；

• 当我们在查询条件中对索引列使用函数，就会导致索引失效。

• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。

• MySQL 遇到  字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。