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

2021-10-12 02:03:06

我们在日常维护数据库的时候，如果遇到慢语句查询的时候，我们一般会怎么做？执行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立即返回结果

作者：姚崇
Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。

码农公寓

准备阶段：

优化阶段：

join_execution阶段：

相关文章