MySQL Case-通过optimizer_trace看MySQL优化器行为

我们在日常维护数据库的时候，如果遇到慢语句查询的时候，我们一般会怎么做？执行EXPLAIN去查看它的执行计划？是的。我们经常会这么做，然后看到执行计划展示给我们的一些信息，告诉我们MySQL是如何执行语句的。但是，执行计划往往只给我们带来了最基础的分析信息，比如是否有使用索引，还有一些其他供我们分析的信息，比如使用了临时表、排序等等。

下面是此次案例的SQL文本：

SELECT
	table_space_info.db_id?AS?table_space_info_db_id,
	NULL?AS?param_2,
	round((?sum(?table_space_info.use_capacity?)?/?sum(?table_space_info.expandable_total_capacity?))?*?100,?2?)?AS?expandable_usage_rate?
FROM
	table_space_info?
WHERE
	table_space_info.create_at?=?(?SELECT?max(?tbs.create_at?)?AS?max_2?FROM?table_space_info?AS?tbs?WHERE?tbs.db_id?=?table_space_info.db_id?)?
	AND?ifnull(?table_space_info.pdb_name,?''?)?!=?'N/A'?
GROUP?BY
	table_space_info.db_id;

下面是mariadb（版本5.5.64-MariaDB）执行计划：用xxx代替上述SQL

MariaDB?[dg-ins]>?explain?xxx;
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
|?id?|?select_type????????|?table????????????|?type??|?possible_keys?|?key???|?key_len?|?ref?????????????????????????|?rows?|?Extra???????|
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
|??1?|?PRIMARY????????????|?table_space_info?|?index?|?NULL??????????|?db_id?|?8???????|?NULL????????????????????????|?6855?|?Using?where?|
|??2?|?DEPENDENT?SUBQUERY?|?tbs??????????????|?ref???|?db_id?????????|?db_id?|?8???????|?test.table_space_info.db_id?|??161?|?????????????|
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
2?rows?in?set?(0.03?sec)

下面是MySQL5.7（版本5.7.32-log）执行计划：用xxx代替上述SQL

mysql>?explain?SELECT?xxx;
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
|?id?|?select_type????????|?table????????????|?type??|?possible_keys?|?key???|?key_len?|?ref?????????????????????????|?rows?|?Extra???????|
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
|??1?|?PRIMARY????????????|?table_space_info?|?index?|?db_id?????????|?db_id?|?8???????|?NULL????????????????????????|?6904?|?Using?where?|
|??2?|?DEPENDENT?SUBQUERY?|?tbs??????????????|?ref???|?db_id?????????|?db_id?|?8???????|?test.table_space_info.db_id?|??300?|?NULL????????|
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
2?rows?in?set,?2?warnings?(0.00?sec)
Note?(Code?1276):?Field?or?reference?'test.table_space_info.db_id'?of?SELECT?#2?was?resolved?in?SELECT?#1
Note?(Code?1003):?/*?select#1?*/?select?`test`.`table_space_info`.`db_id`?AS?`table_space_info_db_id`,NULL?AS?`param_2`,round(((sum(`test`.`table_space_info`.`use_capacity`)?/?sum(`test`.`table_space_info`.`expandable_total_capacity`))?*?100),2)?AS?`expandable_usage_rate`?from?`test`.`table_space_info`?where?((`test`.`table_space_info`.`create_at`?=?(/*?select#2?*/?select?max(`test`.`tbs`.`create_at`)?AS?`max_2`?from?`test`.`table_space_info`?`tbs`?where?(`test`.`tbs`.`db_id`?=?`test`.`table_space_info`.`db_id`)))?and?(ifnull(`test`.`table_space_info`.`pdb_name`,'')?<>?'N/A'))?group?by?`test`.`table_space_info`.`db_id`
mysql>?????
mysql>?show?warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|?Level?|?Code?|?Message??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|?Note??|?1276?|?Field?or?reference?'test.table_space_info.db_id'?of?SELECT?#2?was?resolved?in?SELECT?#1??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|
|?Note??|?1003?|?/*?select#1?*/?select?`test`.`table_space_info`.`db_id`?AS?`table_space_info_db_id`,NULL?AS?`param_2`,round(((sum(`test`.`table_space_info`.`use_capacity`)?/?sum(`test`.`table_space_info`.`expandable_total_capacity`))?*?100),2)?AS?`expandable_usage_rate`?from?`test`.`table_space_info`?where?((`test`.`table_space_info`.`create_at`?=?(/*?select#2?*/?select?max(`test`.`tbs`.`create_at`)?AS?`max_2`?from?`test`.`table_space_info`?`tbs`?where?(`test`.`tbs`.`db_id`?=?`test`.`table_space_info`.`db_id`)))?and?(ifnull(`test`.`table_space_info`.`pdb_name`,'')?<>?'N/A'))?group?by?`test`.`table_space_info`.`db_id`?|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2?rows?in?set?(0.00?sec)

上述两个不同环境下，执行计划一摸一样，但是MariaDB执行0.02秒，MySQL5.7要执行5秒。

这个时候，下一步动作可能就要去看optimizer_trace寻找原因

OPTIMIZER_TRACE是什么呢？

它有点类似于Oracle的10053，会给你做简单版的transform，考虑给你做一定程度的优化，同时它也有跟踪功能，跟踪执行的语句的解析优化执行的过程，并将跟踪到的信息记录到INFORMATION_SCHEMA的OPTIMIZER_TRACE表中，可以通过optimizer_trace系统变量启停跟踪功能，MySQL从5.6开始提供了相关的功能，但是MySQL默认是关闭它的，我们在需要使用的时候才会手动去开启。

optimizer_trace可以是会话或者是全局开启，但是每个会话都只能跟踪它自己执行的语句，表中默认只记录最后一个查询的跟踪结果（表中记录的跟踪结果数可以通过optimizer_trace的参数设置）

可跟踪语句对象包括：SELECT/INSERT/REPLACE/UPDATE/DELETE、EXPLAIN、SET、DO、CALL、DECLARE CASE IF RETURN

mysql>?show?variables?like?'%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
|?Variable_name????????????????|?Value??????????????????????????????????????????????????????????????????????|
+------------------------------+----------------------------------------------------------------------------+
|?optimizer_trace??????????????|?enabled=on,one_line=off????????????????????????????????????????????????????|
|?optimizer_trace_features?????|?greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on?|
|?optimizer_trace_limit????????|?1??????????????????????????????????????????????????????????????????????????|
|?optimizer_trace_max_mem_size?|?16384??????????????????????????????????????????????????????????????????????|
|?optimizer_trace_offset???????|?-1?????????????????????????????????????????????????????????????????????????|
+------------------------------+----------------------------------------------------------------------------+
5?rows?in?set?(0.00?sec)
optimizer_trace
*?enabled：启用/禁用optimizer_trace功能
*?one_line：决定了跟踪信息的存储方式，为on表示使用单行存储，否则以JSON树的标准展示形式存储。单行存储中跟踪结果中没有空格，造成可读性极差，但对于JSON解析器来说是可以解析的，将该参数打开唯一的优势就是节省空间，一般不建议开启
optimizer_trace_features：该变量中存储了跟踪信息中可控的打印项，可以通过调整该变量，控制在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的trace列需要打印的JSON项和不需要打印的JSON项。默认打开该参数下的所有项
optimizer_trace_max_mem_size?：optimizer_trace内存的大小，如果跟踪信息超过这个大小，信息将会被截断
optimizer_trace_limit??&?optimizer_trace_offset??
*?这两个参数神似于SELECT语句中的“LIMIT?offset,?row_count”，optimizer_trace_limit?约束的是跟踪信息存储的个数，optimizer_trace_offset?则是约束偏移量。和?LIMIT?一样，optimizer_trace_offset?从0开始计算（最老的一个查询记录的偏移量为0）
*?optimizer_trace_offset?的正负值，不需要太过于去纠结，如下表所示，其实offset?0?=?offset?-5?，它们是一个等价的关系，仅仅是表述方式不同。这样的表述方式和python中的切片的表述是一致的，了解python的童鞋们都知道，切片的时候经常用到-1取列表中最后一个数值或者是反向取值。

抓取optimizer_trace步骤：

#?Turn?tracing?on?(it's?off?by?default):??
SET?optimizer_trace="enabled=on";?
SELECT?...;?#?your?query?here?
SELECT?*?FROM?INFORMATION_SCHEMA.OPTIMIZER_TRACE;?
#?possibly?more?queries...?
#?When?done?with?tracing,?disable?it:?
SET?optimizer_trace="enabled=off";

整理如下

set?optimizer_trace_max_mem_size?=?50000;
SET?optimizer_trace="enabled=on";?
SELECT
??table_space_info.db_id?AS?table_space_info_db_id,
??NULL?AS?param_2,
??round((?sum(?table_space_info.use_capacity?)?/?sum(?table_space_info.expandable_total_capacity?))?*?100,?2?)?AS?expandable_usage_rate?
FROM
??table_space_info?
WHERE
??table_space_info.create_at?=?(?SELECT?max(?tbs.create_at?)?AS?max_2?FROM?table_space_info?AS?tbs?WHERE?tbs.db_id?=?table_space_info.db_id?)?
??AND?ifnull(?table_space_info.pdb_name,?''?)?!=?'N/A'?
GROUP?BY
??table_space_info.db_id;
SELECT?trace?FROM?INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET?optimizer_trace="enabled=off";

抓取结果整体结构

整个OPTIMIZER_TRACE的重点就是TRACE的JSON树。TRACE中的JSON树大部分都又臭又长，个人更建议使用带有收缩代码格式的编辑器去围观这棵树，能更清晰地理顺这棵树，如下图所示，我们先来看看TRACE的大框架。

在TRACE的JSON中有三个步骤构成： join_preparation（准备阶段）、join_optimization（优化阶段）、join_execution（执行阶段）。

"join_preparation":?{},（准备阶段）
"join_optimization":?{},（优化阶段）
"join_execution":?{},（执行阶段）

先看下 准备阶段json代码

????{
??????"join_preparation":?{
????????"select#":?1,
????????"steps":?[
??????????{
????????????"join_preparation":?{
??????????????"select#":?2,
??????????????"steps":?[
????????????????{
??????????????????"expanded_query":?"/*?select#2?*/?select?max(`tbs`.`create_at`)?AS?`max_2`?from?`table_space_info`?`tbs`?where?(`tbs`.`db_id`?=?`table_space_info`.`db_id`)"
????????????????}
??????????????]
????????????}
??????????},
??????????{
????????????"expanded_query":?"/*?select#1?*/?select?`table_space_info`.`db_id`?AS?`table_space_info_db_id`,NULL?AS?`param_2`,round(((sum(`table_space_info`.`use_capacity`)?/?sum(`table_space_info`.`expandable_total_capacity`))?*?100),2)?AS?`expandable_usage_rate`?from?`table_space_info`?where?((`table_space_info`.`create_at`?=?(/*?select#2?*/?select?max(`tbs`.`create_at`)?AS?`max_2`?from?`table_space_info`?`tbs`?where?(`tbs`.`db_id`?=?`table_space_info`.`db_id`)))?and?(ifnull(`table_space_info`.`pdb_name`,'')?<>?'N/A'))?group?by?`table_space_info`.`db_id`"
??????????}
????????]
??????}
????}

优化阶段这里忽略掉...

join_execution阶段：

此SQL的optimizer_trace的执行部分，存在几百次的相同的重复单元，如下：

??????????{
????????????"subselect_execution":?{
??????????????"select#":?2,
??????????????"steps":?[
????????????????{
??????????????????"join_execution":?{
????????????????????"select#":?2,
????????????????????"steps":?[
????????????????????]
??????????????????}
????????????????}
??????????????]
????????????}
??????????}

说明嵌套查询几百次，主要耗时在这里。

将SQL改写成如下形式：

SELECT
	table_space_info.db_id?AS?table_space_info_db_id,
	NULL?AS?param_2,
	round((?sum(?table_space_info.use_capacity?)?/?sum(?table_space_info.expandable_total_capacity?))?*?100,?2?)?AS?expandable_usage_rate?
FROM
	table_space_info?
WHERE
	(?table_space_info.db_id,?table_space_info.create_at?)?IN?(?SELECT?db_id,?max(?tbs.create_at?)?AS?max_2?FROM?table_space_info?AS?tbs?GROUP?BY?tbs.db_id?)?
	AND?ifnull(?table_space_info.pdb_name,?''?)?!=?'N/A'?
GROUP?BY
	table_space_info.db_id;

sql立即返回结果