索引失效案例分析

问题描述

在MySQL中，有很多看上去逻辑相同，但性能却差异巨大的SQL语句。对这些语句使用不当的话，就会不经意间导致整个数据库的压力变大。

案例分析

• 条件字段函数操作
  对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。不过优化器在个问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于select * from tradelog where id + 1 = 10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以，需要你在写SQL语句的时候，手动改写成 where id = 10000 -1才可以。
• 隐式类型转换
  select * from tradelog where tradeid=110717;
  如果交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。
  在MySQL里字符串和数字做比较的话，是将字符串转换成数字。所以以上语句相当于：
  select * from tradelog where CAST(tradid AS signed int) = 110717;
  也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。
• 隐式字符编码转换
  如果在做多表联合查询，多个表的字符编码不一样，可能也会导致索引失效。
  如果一个表的字符集utf8mb4，一个是utf8，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。这就是恰好对应着第一条，对索引字段做函数操作，走全表扫描，不走索引。

总结

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

第二个例子是隐式类型转换，第三个例子是隐式字符编码转换，它们都跟第一个例子一样，因为要求在索引字段上做函数操作而导致了全索引扫描。

MySQL的优化器确实有“偷懒”的嫌疑，即使简单地把where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写。

因此，每次你的业务代码升级时，把可能出现的、新的SQL语句explain一下，是一个很好的习惯。