专栏首页爱可生开源社区故障分析 | MySQL 优化案例 - 字符集转换

故障分析 | MySQL 优化案例 - 字符集转换

作者:xuty

本文来源:原创投稿 *爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


本文关键字:SQL 优化、字符集

相关文章推荐:

故障分析 | MySQL 派生表优化

故障分析 | 有效解决 MySQL 行锁等待超时问题【建议收藏】

一、背景

开发联系我,说是开发库上有一张视图查询速度很慢,9000 条数据要查 10s,要求我这边协助排查优化。

二、问题 SQL

Server version: 5.7.24-log MySQL Community Server (GPL)

这个 SQL 非常简单,定义如下,其中就引用了 view_dataquality_analysis 这张视图,后面跟了两个 where 条件,并且做了分页。

  SELECT *
  FROM view_dataquality_analysis
  WHERE modelguid = '710adae5-1900-4207-9864-d53ee3a81923'
    AND configurationguid = '6845d000-cda4-43ea-9fd3-9f9f1f22f95d' limit 20;

我们先去开发库上运行一下这条 SQL,下图中可以看到确实运行很慢,要 8s 左右。

三、执行计划

分析一条慢 SQL,最有效的方法便是分析它的执行计划,看是否存在问题。

下面我们看下这条 SQL 的执行计划,主要由三张表(t、r、b)组成,从 t 开始嵌套连接 r,再嵌套连接 b。整个执行逻辑很简单,至于 t、r、b 肯定是视图中定义的表别名。

从执行计划中可以看出 t 嵌套连接 r 的时候走的是主键索引,但是继续嵌套连接 b 的时候,却是走的全表扫描!那么可能很有可能问题就出在这个地方,为什么 b 表没有走索引,是因为缺失了索引吗?

四、视图分析

带着上面执行计划中的疑问,我们去看下 view_dataquality_analysis 这张视图的定义,如下所示。

  SELECT *
  FROM (
      (
      `dataquality_taskconfigurationhistory` `t` LEFT JOIN `dataquality_rule` `r` ON ((`t`.`RuleGuid` = `r`.`RuleGuid`))
      ) LEFT JOIN `metadata_tablebasicinfo` `b` ON ((convert(`b`.`TableGuid` using utf8mb4) = `r`.`Tableguid`))
    )

视图由 dataquality_taskconfigurationhistorydataquality_rulemetadata_tablebasicinfo 表连接组成,分别用了t、r、b 别名表示。因为都是用的 LEFT JOIN,所以表连接顺序应该是 t-->r-->b,和之前执行计划中显示的一致。

不知道各位有没有注意到

(convert(`b`.`TableGuid` using utf8mb4) = `r`.`Tableguid`))

这一段内容!表连接上居然存在一个字符集的转换。那么问题可能就是出在这里。

起先我以为这一段字符集转换是开发在定义视图的时候自己加上去的,后来询问后发现开发并未如此做。尝试将视图定义去掉这一段内容,但是发现保存后,这个转换却会自动生成!!!

五、表定义查看

带着上面的疑问,我们去看下表定义,首先是 bmetadata_tablebasicinfo,然后是 rdataquality_rule,最后是 tdataquality_taskconfigurationhistory

  CREATE TABLE `metadata_tablebasicinfo` (
    `TableGuid` varchar(50) NOT NULL,
    `SqlTableName` varchar(50) DEFAULT NULL,
    .......
    PRIMARY KEY (`TableGuid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  
  CREATE TABLE `dataquality_rule` (
    `RuleGuid` varchar(50) NOT NULL,
    `ModelGuid` varchar(50) DEFAULT NULL,
    .......
    PRIMARY KEY (`RuleGuid`),
    KEY `idx_top` (`RuleGuid`,`Tableguid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  
  CREATE TABLE `dataquality_taskconfigurationhistory` (
    `RowGuid` varchar(50) NOT NULL,
    `ModelGuid` varchar(50) DEFAULT NULL,
    .......
    PRIMARY KEY (`RowGuid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

通过查看表定义后,可以看到 b 表字符集为 utf8,而 t 表与 r 表字符集都为 utf8mb4!!!那么基本可以验证我的猜想,当 MySQL 创建视图时,如果发现表连接字段字符集不相同时,会自动添加字符集转换。

另外之前我们有个为什么 b 表没有走索引,是因为缺失了索引吗?的疑问。从上面 b 表的表结构定义就可以看出, b 表的连接字段为 TableGuid,是 b 表的主键,那么肯定存在主键索引,就更不可能不走索引而选择全表扫描了。

六、修改字符集

为了验证因为字符集问题而导致表连接没有走索引,我们选择将 bmetadata_tablebasicinfo 的字符集修改为 utf8mb4。

过程如下:

  --修改表的默认字符集和所有列的字符集为utf8mb4
  ALTER TABLE metadata_tablebasicinfo CONVERT TO CHARACTER SET utf8mb4;

通过上述操作后,目前 b 表为 utf8mb4 字符集。

七、视图重建

b 表字符集修改为 utf8mb4 后,去查看 view_dataquality_analysis 视图定义,发现还是存在字符集转换,所以猜测这类自动添加转换的机制不会因为表结构更改而自动去掉。

我们再次将视图中字符集转换的内容去掉后,保存视图,发现这次不会自动添加字符集转换。那么这次应该就应该会走索引啦~

我们再次执行问题 SQL,执行时间为 0.2s,速度明显就正常了。

再来看一波执行计划,可以看到 b 表上走的是主键索引,这下舒服了~

八、问题总结

通过这次问题排查,发现了字符集不同原来也会导致索引失效。其实这个问题有点类似于 int=varchar 隐式转换问题,等号左边为 int 类型,右边为 varchar 类型,那么 MySQL 会自动转换类型为一致,因而无法走索引。

下次如果再出现类似的问题,可以先查看下视图定义,如果存在字符集转换的内容,那么就可以检查是否是类似的问题!

另外还有一个注意的点就是,列的字符集也有可能与表的字符集不同!

本文分享自微信公众号 - 爱可生开源社区(ActiontechOSS),作者:xuty

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 技术分享 | 排序(filesort)详细解析(8000 字长文)

    文章末尾有他著作的《深入理解 MySQL 主从原理 32 讲》,深入透彻理解 MySQL 主从,GTID 相关技术知识。

    爱可生开源社区
  • 技术分享 | MySQL:从库出现 system lock 的原因

    本文为笔者 2 年前写一篇说明性文章,发现很多同学都在问这个问题,因此做一次分享。

    爱可生开源社区
  • 技术分享 | 常见索引问题处理

    数据库技术爱好者,爱可生 DBA 团队成员,负责 MySQL 日常问题处理以及数据库运维平台的问题排查,擅长 MySQL 主从复制及优化,喜欢钻研技术问题,还有...

    爱可生开源社区
  • 域名1997.com拍出了159万元的高价

    域名市场中很受欢迎的域名是数字域名,特别是有年份的域名,因其有特殊含义而备受市场青睐。最近,有一枚年份域名1997.com拍出了159万元的高价。

    躲在树上的域小名
  • EMR入门学习之HDFS上的一些常见Shell命令(五)

    文件系统(FS)shell包括各种类似shell的命令,这些命令直接与Hadoop分布式文件系统(HDFS)以及Hadoop支持的其他文件系统交互,例如本地FS...

    披荆斩棘
  • 腾讯与中兴通讯签署5G网络技术与应用合作协议

    ? 近日,腾讯与中兴通讯正式签署5G合作备忘录,双方将加强5G网络技术与应用创新合作,共同促进5G边缘计算、QoS加速、网络切片、网络能力开放等技术的成熟及部...

    腾讯技术工程官方号
  • 使用 Jenkins Blue Ocean 构建 Android 项目

    Blue Ocean 是 Jenkins 推出的一套新的 UI,对比经典 UI 更具有现代化气息。2017 年 4 月 James Dumay 在博客上正式推出...

    DevOps时代
  • C++版 - 剑指offer 面试题16:反转链表(Leetcode 206: Reverse Linked List) 题解

    提交网址: http://www.nowcoder.com/practice/75e878df47f24fdc9dc3e400ec6058ca?tpId=13&...

    Enjoy233
  • Mac 设置静态IP的方法 和 Mac 设置静态IP后 无法联网问题

    在使用 jekins 打包的时候,在同一个局域网内可以访问 jekins 的地址来新增项目、打包、配置等等各种操作,但是如果打包机的IP不是固定的,那么在每次其...

    onety码生
  • Android配置EGL环境

    Android配置egl环境我们根据GLSurfaceView源码来实现。在GLSurfaceView源码里面,当调用setRenderer的时候会开启一个线程...

    曾大稳

扫码关注云+社区

领取腾讯云代金券