关于数据库: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 数据库、分布式数据库运维方面的技术工作；热衷于运维故障解决、备份复原、降级迁徙、性能优化的学习与分享。