MySQL隐式转换案例一则

隐式转换，可以说是关系型数据库SQL优化中很隐秘的问题，之前碰到过很多和他相关的案例，

《隐式转换的替代方案不是万能的》

《Oracle、SQL Server和MySQL的隐式转换异同》

《见识一下SQL Server隐式转换处理的不同》

《如何找到隐式转换的SQL？》

《从隐式转换案例，来挖掘开发人员的技能提升》

《浅谈显式转换和隐式转换》

《隐式转换的案例场景》

《一次有意思的错选执行计划问题定位》

数据和云的这篇文章《SQL优化——隐式字符编码转换》则介绍了MySQL中因为字符集不同导致的隐式转换的问题。

MySQL中我们知道，

• 如果对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
• 隐式类型转换也会导致放弃走树搜索。

因为类型转换等价于在条件字段上使用了函数，例如，

假设tradeid字段有索引，且为varchar类型，
mysql> select * from tradelog where tradeid=110717;
等价于，
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

下面来看看隐式字符编码转换导致的一个慢SQL，业务上有个SQL执行需要1.31秒，

a469dae8197421e0d64732bc2e95dbcb.png

看执行计划，

dfdd2562e57839f12713be5bd42c9562.png

从执行计划分析看出问题出在r表也就是h_merge_result_new_indicator表全表扫描，查看该表的表结有联合索引。但是联合索引范围后会失效，于是打算新建一个联合索引，

06147ecaec586a0297771c7b099952fb.png

查看预新建联合索引的字段选择性，

15b819b9fd65728346f2d2d9b83ff477.png

结合选择性来看，

create index idx_hmrni on h_merge_result_new_indicator(keyName,module,BATCH_NO);

创建后，再次看执行计划依然无效，

5bfd5bc3b4a49f07f072dd101593c2f2.png

查看表结构，

66aa389b017d399dd56a4bec70f49448.png

另外3个表结构其中有2个utf8mb4，1个utf8，

12b65c44940a851aeb0c24ccf43983f6.png

f1890770b1185220ee3b605d932d297d.png

0a4cead54464fb6c7cb9de515413ab97.png

字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先将utf8字符串转成utf8mb4字符集，再做比较。因此，

a2a24b0f771271553cdfe434ed220af1.png

这部分会转换后再与h_merge_result_new_indicator关联。优化就只需要将字符集编码转为utf8再和h_merge_result_new_indicator关联就能用上索引，

2c2cb2c5c5e48a5865001d5f15f34df9.png

再看查询只需要0.02秒了，

ee5d9862aa44698c2c8f80dcade5e06f.png

但是还有个问题，如上执行计划key_len是606=(100*3+3)+(100*3+3)。就是说，没有用上BATCH_NO字段上的索引，我们知道索引少一个字段，占用会减少，不会太臃肿。因此，联合索引只需要包含(keyName,module)，

drop index idx_hmrni on h_merge_result_new_indicator;
create index idx_hmrni on h_merge_result_new_indicator(keyName,module）;

对索引字段做函数操作，无论是显式，还是隐式，都会破坏索引值的有序性，进而影响优化器的判断，这对Oracle和MySQL都是相通的。

因此，在开发阶段，以及审核阶段，对这些问题的规避和挖掘，还是值得关注的，投入产出比，还是比较可观的。