索引无法应用的情况，隐式类型转换，隐式字符编码转换

蔚落 2021-08-19 12:03 450阅读 0赞

索引无法应用的情况  
条件字段函数操作

select count(*) from test where month(modified)=7;

如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。

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

MySQL 无法再使用索引快速定位功能，而只能使用全索引扫描。

隐式类型转换

select * from test where tradeid=110717;

tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。此时就会需要走全表扫描，用不上索引。因为MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。所以语句会变成下面这样，相当于给了索引上加了函数。

select * from test where  CAST(tradid AS signed int) = 110717;
    select * from test where id="83126";

这个情况就不会有影响，因为右侧字符串会转变为整型。索引上没有函数。

隐式字符编码转换

tradelog表的tradeid编码是 utf8mb4                       trade\_detail 表的tradeid编码是 utf8

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

因为这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引

MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。  
这个设定很好理解，utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

这里tradelog是驱动表              trade\_detail是被驱动表  所以转换成下面这个状态

select * from trade_detail where tradeid=$L2.tradeid.value; 
    select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

如果换成下面这个sql就可以用上索引了，因为不需要转换了  驱动表变成了trade\_detail   tradelog是被驱动表

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

还有个有趣的情况

\`b\` varchar(10)  有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’

select * from table_a where b='1234567890abcd';

这条 SQL 语句的执行很慢，流程是这样的：

在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；  
这样满足条件的数据有 10 万行；  
因为是 select \*， 所以要做 10 万次回表；  
但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;  
返回结果是空。  
————————————————  
版权声明：本文为CSDN博主「Srodong」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/Srodong/article/details/97265314