本文介绍了由于函数转换而不使用索引的三个问题。请注意,不使用索引意味着不使用树搜索,而是使用全表扫描索引树。
[En]
This article introduces three problems that do not use indexes due to function conversion. * Note that not using an index means not using a tree search, but a full table scan index tree. *
显式使用函数
比如 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
就可以,这里感觉优化器偷懒了。
Original: https://www.cnblogs.com/flowers-bloom/p/mysql45-function-conversion.html
Author: flowers-bloom
Title: MySQL45讲之函数转换导致不使用索引
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/508122/
转载文章受原作者版权保护。转载请注明原作者出处!