浪子归家|MySQL 优化案例中字符集转换-爱可生
一、背景
开发联系我 ， 说是开发库上有一张视图查询速度很慢 ， 9000 条数据要查 10s ， 要求我这边协助排查优化 。
二、问题 SQL
Server version: 5.7.24-log MySQL Community Server (GPL)
这个 SQL 非常简单 ， 定义如下 ， 其中就引用了 view_dataquality_analysis 这张视图 ， 后面跟了两个 where 条件 ， 并且做了分页 。
SELECT *FROM view_dataquality_analysisWHERE 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_taskconfigurationhistory、 dataquality_rule 和metadata_tablebasicinfo 表连接组成 ， 分别用了t、r、b 别名表示 。 因为都是用的 LEFT JOIN ， 所以表连接顺序应该是 t-->r-->b ， 和之前执行计划中显示的一致 。
不知道各位有没有注意到
(convert(`b`.`TableGuid` using utf8mb4) = `r`.`Tableguid`))这一段内容！表连接上居然存在一个字符集的转换 。 那么问题可能就是出在这里 。
起先我以为这一段字符集转换是开发在定义视图的时候自己加上去的 ， 后来询问后发现开发并未如此做 。 尝试将视图定义去掉这一段内容 ， 但是发现保存后 ， 这个转换却会自动生成！！！
五、表定义查看
带着上面的疑问 ， 我们去看下表定义 ， 首先是 b 表 metadata_tablebasicinfo ， 然后是 r 表dataquality_rule ， 最后是 t 表 dataquality_taskconfigurationhistory
CREATE TABLE `metadata_tablebasicinfo` (`TableGuid` varchar(50) NOT NULL,`SqlTableName` varchar(50) DEFAULT NULL,.......PRIMARY KEY (`TableGuid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE 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=utf8mb4CREATE TABLE `dataquality_taskconfigurationhistory` (`RowGuid` varchar(50) NOT NULL,`ModelGuid` varchar(50) DEFAULT NULL,.......PRIMARY KEY (`RowGuid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
推荐阅读
- 浪子在农田|隐瞒丈夫独自抗癌9年，葬礼几百人参加，2012年李婷患癌去世
- 一江春水向东流|陶金：《一江春水向东流》男主角，与李丽华婚外情，最终回归家庭
- 浪子归家 这些先进国产机器人不容错过，自动化浪潮下
- 浪子归家 追风逐日 风光无限
- 浪子归家|华为手机要买就买这3款，最少的钱买最好
- 浪子归家|可怜天下父母心，这些年我入手的儿童手表
- 路人战队|11个必须学会的Mysql数据库基本操作
- 浪子归家|Mac和iPad销量表现强势，苹果公布新季度财报
- 浪子归家 Mac和iPad销量表现强势，苹果公布新季度财报
- 浪子归家|自动化浪潮下，这些先进国产机器人不容错过