MySQL45讲之函数转换导致不使用索引 - flowers

前言

本文介绍三种因为函数转换导致不使用索引的问题。注意，不使用索引指的是不使用树搜索，而是全表扫描索引树。

显式使用函数

比如 select count(*) from tradelog where month(t_modified)=7;，在 t_modified 字段上建立了普通索引。
索引对于 t_modified 是有序的，但是对于 month(t_modified) 不是有序的，所以优化器规定，当遇到使用函数对字段进行操作后，不会使用树搜索，而是全表扫描。这里因为只查询符合条件的行数，并且 t_modified 索引树比主键索引树小，所以使用了索引覆盖。

类型转换

比如 select * from tradelog where tradeid=110717;，其中，tradeid 字段是 varchar 类型。
因为 tradeid 和 110717 两个类型不一致，所以需要类型转换。那是转换成字符串呢还是数字呢？
一个简单的判断方法是，执行 select 9 < "10";，如果返回 1，说明转换成数字，否则转换成字符串。

于是，这条语句等价于 select * from tradelog where CASE(tradeid AS signed int)=110717;，也不能使用树搜索，尽管 tradeid 字段有索引。

不过，对于 select * from t where id = "1"; ，会使用主键索引，因为等价于 select * from t where id = CAST("1" AS signed int);

编码转换

比如 select * from trade_detail where tradeid=$L2.tradeid.value;，其中 tradeid 是 utf8 编码，而 $L2.tradeid.value 是 utf8mb64 编码。
因为 utf8mb64 是 utf8 的超集，所以查询时需要将 tradeid 字段编码转换成 utf8mb64。

如果要优化这个场景，在线上数据量不大且可以做这个 DDL 时，可以将 trade_detail 表的 tradeid 字段设为 utf8mb64 编码。如果不能做这个 DDL 操作，可以采用 select * from trade_detail where CONVERT(tradeid USING utf8)=$L2.tradeid.value;

总结

在遇到显式或者隐式使用函数应用字段时，优化器将不会选择索引，而是采用全表扫描的方式。并且，对于像 where id+1=1000 的表达式同样不会采用索引，写成 where id = 1000-1 就可以，这里感觉优化器偷懒了。

参考

• [1] 为什么这些sql语句逻辑相同，性能却差异巨大