点击蓝字
关注我们
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 =（100*3 3） （100*3 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 就能够用上索引快速查找，也不会主动做这个语句重写。
保证在条件索引上不做破坏索引值的有序性，是优化索引的利器。
相关文章：陈家睿，公众号：数据和云SQL优化——IN和EXISTS谁的效率更高
墨天轮原文链接：https://www.modb.pro/db/153885?sjhy（复制链接至浏览器或点击文末阅读原文查看)
关于作者
陈家睿，云和恩墨MySQL技术顾问,拥有MySQL OCP、PGCE、OBCA、SCDP证书，长期服务于电信行业。现负责公司MySQL数据库、分布式数据库运维方面的技术工作；热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。
END
推荐阅读：331页！2021年度数据库技术年刊
推荐下载：2021数据技术嘉年华视频回放及PPT下载
2021数据技术嘉年华50余个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“2021DTC”获得！
你知道吗？我们的视频号里已经发布了很多精彩的内容，快去看看吧！↓↓↓
点击下图查看更多 ↓
云和恩墨大讲堂 | 一个分享交流的地方
长按，识别二维码，加入万人交流社群
请备注：云和恩墨大讲堂
点个“在看”
你的喜欢会被看到❤
