MySQL优化案例之隐式字符编码转换
manongba · 132浏览 · 发布于2022-07-21 +关注

这篇文章主要介绍了MySQL优化案例之隐式字符编码转换,隐式类型转换也会导致同样的放弃走树搜索,更多相关内容具有一定的参考价值,需要的朋友可以参考一下

    索性失效前提

    MySQL中我们知道有:

    • 1、如果对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

    • 2、隐式类型转换也会导致同样的放弃走树搜索。

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

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

    一个真实的案例

    下面来看看隐式字符编码转换导致的一个慢sql

    优化前原始sql分析

    业务上有个sql执行需要1.31秒

    MySQL--SQL优化案例--隐式字符编码转换_联合索引

    看看执行计划:

    MySQL--SQL优化案例--隐式字符编码转换_mysql_02

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

    优化初步处理

    MySQL--SQL优化案例--隐式字符编码转换_mysql_03

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

    MySQL--SQL优化案例--隐式字符编码转换_mysql_04

    结合选择性来看;

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

    初步优化无效分析

    创建后,再次查看执行计划依然无效;

    MySQL--SQL优化案例--隐式字符编码转换_字段_05

    查看表结构:

    MySQL--SQL优化案例--隐式字符编码转换_mysql_06

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

    MySQL--SQL优化案例--隐式字符编码转换_mysql_07

    MySQL--SQL优化案例--隐式字符编码转换_联合索引_08

    MySQL--SQL优化案例--隐式字符编码转换_字段_09

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

    因此:

    MySQL--SQL优化案例--隐式字符编码转换_mysql_10

    这部分会转换后再与h_merge_result_new_indicator关联

    第二次优化处理

    优化就只需要将字符集编码转为utf8再和h_merge_result_new_indicator关联就能用上索引

    MySQL--SQL优化案例--隐式字符编码转换_联合索引_11

    再看查询只需要0.02秒了

    MySQL--SQL优化案例--隐式字符编码转换_联合索引_12

    第三次优化

    但是还有个问题,如上执行计划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 就能够用上索引快速查找,也不会主动做这个语句重写。

    保证在条件索引上不做破坏索引值的有序性,是优化索引的利器。


    相关推荐

    使用SELECT语句检索数据

    奔跑的男人 · 579浏览 · 2019-06-03 09:33:43
    部署MySQL延迟从库的几个好处

    吴振华 · 519浏览 · 2019-05-14 21:57:51
    MongoDB凭什么跻身数据库排行前五?

    iamitnan · 552浏览 · 2019-06-18 10:04:56
    Oracle开启和关闭的几种模式

    qq2360248666 · 507浏览 · 2019-06-04 10:18:47
    加载中

    0评论

    评论
    分类专栏
    小鸟云服务器
    扫码进入手机网页