MySQL索引失效之函数操作

条件字段函数操作

在SQL语句中，对索引字段做函数操作时，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。但这并不是说MySQL放弃使用索引，而只是放弃了索引的树搜索功能，只使用了全索引扫描。

例如sql查每年7月的交易数据

select count(*) from tradeLog where month(createTime)=7;
复制代码

使用explain语句查看执行情况，发现虽然会使用索引，但扫描行数接近全表数据，意味着走了整个索引的所有值。

解决办法是，在SQL改为范围查询。例如

mysql> select count(*) from tradeLog where
    -> (createTime >= '2016-7-1' and createTime<'2016-8-1') or
    -> (createTime >= '2017-7-1' and createTime<'2017-8-1') or
    -> (createTime >= '2018-7-1' and createTime<'2018-8-1');
复制代码

有时候SQL语句不会改变有序性，优化器也不会考虑使用索引。例如

select * from tradeLog where id+1=10000;
复制代码

解决办法是，手动改为 id=10000-1即可。

隐式类型转换

mysql> select * from tradeLog where tradeid=1007;
复制代码

这里tradeid是索引，类型是varchar(32)，由于输入的参数是整型，所以需要做类型转换。对于优化器来说，这条SQL语句相当于

mysql> select * from tradeLog where CAST(tradid AS signed int) = 110717;
复制代码

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

这里类型转换的规则是什么，有一个简单的方法，看 select “10” > 9 的结果：

• 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
• 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

这里select “10” > 9 返回的是1，表示为数字比较，需要将字符串转成数字。即要将tradid字段转化为整型去比较。

隐式字符编码转换

当进行联表查询时，如果关联字段对应的两张表各自的字符集不同，可能会将字段进行字符集转换，这里字符集转换用的是内部函数操作，此时优化器会放弃走树搜索的功能。

需要注意的是，字符集不同只是条件之一，连接过程中要求在查询字段（即sql等号左边的字段）的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

链接：https://juejin.cn/post/7034860617659842573