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 =(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优化-隐式字符编码转换
分享链接：http://www.shufengxianlan.com/qtweb/news0/62850.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联