1.查询中使用了函数
比如一个具有创建时间的表,记录了从16年到18年的记录,如果查询所有的6月份的数据,并且使用了函数,比如如下的sql
select count(*) from tradelog where month(t_modified) = 6
那么是无法使用索引的
因为将数据记录在索引树上的形式为
只是传入了一个月份的函数，但是开头是年份，MySql是无法快速定位到准确数据的
对一些索引做函数操作，可能会导致破坏了索引有序性，并且决定不走树的搜索功能
但是查看Explain命令，发现这个Sql语句的执行为
尝试使用了t_modified这个索引,但是没有成功,仍然扫描了整个表
如果改为如下的语句,可以将其索引利用起来,但是要求知道对应的年份
select count(*) from tradelog where (create_time >=’2016-7-1′ and create_time<=’2016-7-30′ )or (create_time >= ‘2017-7-1’ and create_time <= ‘2017-7-30’) or (create_time >= ‘2018-7-1’ and create_time <= ‘2018-7-30’)
同理,如下的计算方式也不能使用索引
select * from tardelog where id+1 = 10000,并不能直接使用id索引，必须改为
select * from tradelog where id = 10000-1;才可行
2.sql语句中有隐式的类型转换
比如一个表的主键id为varchar类型，但是输入了一个整型，这就需要隐式的类型转换
类型转换变慢的原因很好理解,就是使用了字符串转为数字的函数
select * from tradelog where tradeid = 100117;
其实就是
select * from tradelog where CAST(tradid as singed int) = 110111;
相当于还是使用了函数导致不走搜索树功能
3.字符集的转换
假设有两行表,一个两个表的字符集不一致,一个是utf-8,一个是utf8mb4
这样,需要进行两个表的联查的时候,可能触发以下的情况
比如如下的联查语句
mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
发现执行计划不对
关于第一个id =2的查询,只是扫描了一行
关于联查的时候,则是进行了全表扫描
至于,为何出现了这个问题,可以看如下的执行流程
根据tradeId到trade_detail表中查询匹配的行的时候,没有使用索引,这是因为一个表的字符集为utf-8,一个的字符集为utf-8mb4
导致了数据的隐式转换
实际上的语句就是
mysql> select * from trade_detail where COUVERT(tradeid USING utf8mb4)=$L2.tradeid.value;
这就是问题所在
如果翻转一下sql语句的查询呢?
mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
会发现就查询了短短的几行
我们依旧看一下表的拆解
select operator from tradelog where traideid =$R4.tradeid.value;
实际上转换的CONVERT函数加载了对应的输入参数上,避免了全表搜索
总体来说,对于无法使用索引的常见问题,都与函数操作有关