问题
在26问中,我们看到了如下SQL在MySQL5.7中跑得很慢:
我们还分析了执行计划改写后的SQL,通过猜测,增加了hint来解决问题:
这一期,我们通过工具来分析一下:MySQL为什么会使用一个低效的执行计划,以致于我们不得已用hint来调优SQL?
实验
我们接着使用26问中的环境,使用optimizertrace工具,观察MySQL对SQL的优化处理过程。
我们先调大optimizertrace的内存容量(否则trace的输出会被截断),然后开启了optimizertrace功能。
跑完SQL后,可以在INFORMATION_SCHEMA.OPTIMIZER_TRACE看到SQL的优化处理过程:
这会是个巨大的json,我们将其复制出来,找个json的可视化编辑器来分析一下。
小贴士
如果MySQL启动时有配置--secure-file-priv,那可以用,
SELECTTRACEINTODUMPFILEfilenameFROMINFORMATION_SCHEMA.OPTIMIZER_TRACE;
将trace导出到文件里,会更方便一些。
这里我们选择了一个在线的json编辑器,使用起来会方便一点:
可以看到整个优化过程分为6个步骤,前两步都跟创建临时表相关,然后是join的准备工作,再是两步join优化,最后是join的执行。
回忆一下26问中,我们的子查询应使用物化方式,但实际使用了exists子句方式,我们猜测这个选择是在join的优化阶段做出的。
仔细翻一翻,就会找到可疑的部分:
上图中的中文,是从英文翻译过来的。看上去我们找对了位置。
接下来我们逐步看看这个决策的依据是什么:
显然不物化的代价更小,那么优化器选择不物化是正确的选择。
但使用exists子句进行子查询的代价,显然不可能为0,MySQL对这个代价的计算可能有误。
我们得来看看MySQL是如何计算这个代价的:
执行exists子查询的代价=执行一次子查询的代价*子查询需要执行的次数
显然这个子查询不可能只需要执行0次
这里需要做一个额外的思考:在这个场景下,子查询需要执行的次数,与父查询的行数相同。
也就是红框内需要执行的次数,取决于红框外的SQL的结果集条数。
这里MySQL将父表的结果集条数称为扇出度(fanout)
显然,这里父表information_schema.columns的扇出度为0,直接导致了优化器放弃了物化的策略
那information_schema.columns的扇出度为什么是0呢?
查看information_schema.tables中对于COLUMNS表的描述,我们看到MySQL将information_schema中的元数据表做了特殊对待,其行数估计是没有意义的。
到此我们找到了问题所在:MySQL5.7对元数据表使用了区别设计,与普通表的行数估算方式不同。
以后大家在MySQL5.7中使用information_schema中的元数据表做复杂查询时,需要额外注意执行计划,可能需要使用hint指导优化器工作。
MySQL8.0中进行了数据字典的改造,information_schema中的元数据表大部分都变成了视图,其真实的数据源是mysql库中的隐藏元数据表。
对MySQL8.0的元数据表进行复杂查询,执行计划会比MySQL5.7更加合理。