关于数据库:SQL优化隐式字符编码转换

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秒:

看看执行打算:

从执行打算剖析看出问题出在r表也就是 h_merge_result_new_indicator 表全表扫描，查看该表的表结有联结索引。然而联结索引范畴后会生效，于是打算新建一个联结索引：

查看预新建联结索引的字段选择性：

联合选择性来看：

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

创立后，再次查看执行打算仍然有效：

查看表构造：

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

字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比拟的时候，MySQL 外部的操作是：先把 utf8 字符串转成 utf8mb4 字符集，再做比拟。

因而：

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

再看查问只须要0.02秒了：

然而还有个问题，如上执行打算key_len是606 =（1003+3）+（1003+3）

也就是说，没有用上BATCH_NO字段上的索引，咱们晓得索引少一个字段，占用会缩小，不会太臃肿。因而，联结索引只须要蕴含r(keyName,module）：

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

//论断//
对索引字段做函数操作，可能会毁坏索引值的有序性，因而优化器就决定放弃走树搜寻性能。该例子是隐式字符编码转换，它们都跟其余条件索引上应用函数一样，因为要求在索引字段上做函数操作而导致了全索引扫描。

MySQL 的优化器的确有“偷懒”的嫌疑，即便简略地把 where id+1=1000 改写成 where id=1000-1 就可能用上索引疾速查找，也不会被动做这个语句重写。

保障在条件索引上不做毁坏索引值的有序性，是优化索引的利器。

墨天轮原文链接：https://www.modb.pro/db/15388...（复制链接至浏览器或点击文末浏览原文查看)
对于作者
陈家睿，云和恩墨MySQL技术顾问,领有MySQL OCP、PGCE、OBCA、SCDP证书，长期服务于电信行业。现负责公司MySQL数据库、分布式数据库运维方面的技术工作；热衷于运维故障解决、备份复原、降级迁徙、性能优化的学习与分享。