MySQL 用好 Optimizer Trace，深刻理解 SQL 优化过程！

前面的章节（社区专栏《SQL调优》）我们已经写了很多篇幅关于 MySQL 执行计划的解读，今天我们来继续延伸介绍执行计划的链路跟踪功能，也就是 MySQL 的Optimizer Trace。

在这之前，先来回顾下EXPLAIN的结果：

mysql:ytt>explainselect*fromt1 aleftjoiny1 bona.id=b.idwherea.r1<100orderbya.r2desc;+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+|1|SIMPLE|a|NULL|ALL|idx_r1|NULL|NULL|NULL|998222|50.00|Usingwhere;Usingfilesort||1|SIMPLE|b|NULL|eq_ref|PRIMARY|PRIMARY|4|ytt.a.id|1|100.00|NULL|+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+2rowsinset,1warning(0.00sec)

EXPLAIN展示出来的核心数据有：

1. 表关联顺序
2. 优化器筛选过的索引
3. 实际使用的索引
4. 每张表依据统计信息的扫描行数
5. Extra 额外数据提示
6. 两种执行计划（explain format=tree/explain format=json）展示出来的额外成本数据

如果想快速对于 SQL 进行优化，基于以上的结果完全可以满足。但是想深入了解 MySQL 优化器为什么选择这样的执行计划，基于以上的结果就无法满足。

举例说明：

• 我想知道对于表a来讲，为什么有索引idx_r1，但是实际却没有使用，而走的全表扫？
• 两张表关联，为什么选择的顺序是表a驱动表b，而不是表b驱动表a？
• 为什么字段r2有索引，但是依然要走排序？

带着这些疑问，我们来介绍 MySQL 的Optimizer Trace功能。

1. 什么是 Optimizer Trace？

简单来讲，Optimizer Trace是一个 SQL 执行计划的链路跟踪器，跟踪 SQL 的解析、优化、执行等过程，并且把结果记录到 MySQL 元数据表（information_schema.optimizer_trace），之后可以对这张表分析得到很多个执行计划的“为什么？”！

2. 如何使用 Optimizer Trace？

要使用Optimizer Trace功能，首先得打开控制开关。谨记：这个功能非常耗费资源，默认关闭的，可以通过调整以下变量开启：

mysql:ytt>showvariableslike'optimizer_trace%';+------------------------------+----------------------------------------------------------------------------+|Variable_name|Value|+------------------------------+----------------------------------------------------------------------------+|optimizer_trace|enabled=off,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|1048576||optimizer_trace_offset|-1|+------------------------------+----------------------------------------------------------------------------+5rowsinset(0.00sec)

以上几个参数详细解释下：

• optimizer_trace：enabled=on/off启用/禁用Optmizer Trace功能；one_line=on/off启用/禁用 json 格式化存储，一般不需改动。
• optimizer_trace_limit/optimizer_trace_offset：这两个参数和LIMIT子句一样，用来最终展示Trace的 SQL 条数。展示的条数越多，对内存消耗越大，默认展示最近的一条记录。比如设置optimizer_trace_limit为 10，optimizer_trace_offset为 -10，就可以最多展示 10 条Trace记录。
• optimizer_trace_max_mem_size：用来存储Trace结果的最大内存。
• optimizer_trace_features：用来启动/禁用相关Trace特性开关。
• end_markers_in_json：启用/禁用 注释功能。开启这个，Trace结果可读性更强。
• Optimizer Trace可以跟踪的语句有：
  • SELECT、TABLE、VALUES、WITH、INSERT、REPLACE、UPDATE、DELETE
  • EXPLAIN
  • SET（排除设置Optimizer Trace相关参数）
  • DO
  • 存储函数内部、触发器内部等的 DECLARE、CASE、IF、RETURN 语句
  • CALL

在数据库里，语句调优一般说的是 SELECT 语句，所以大部分场景跟踪的也只有 SELECT 语句。

元数据表字段解析

mysql:ytt>descinformation_schema.optimizer_trace;+-----------------------------------+----------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------------------------------+----------------+------+-----+---------+-------+|QUERY|varchar(65535)|NO|||||TRACE|varchar(65535)|NO|||||MISSING_BYTES_BEYOND_MAX_MEM_SIZE|int|NO|||||INSUFFICIENT_PRIVILEGES|tinyint(1)|NO||||+-----------------------------------+----------------+------+-----+---------+-------+4rowsinset(0.00sec)

• QUERY：TRACE的 SQL 语句原文
• TRACE：SQL 语句的TRACE结果，JSON 格式存储（由变量end_markers_in_json来控制）
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE：TRACE结果超过变量optimizer_trace_max_mem_size设置的值后，截断的大小（BYTE）
• INSUFFICIENT_PRIVILEGES：对存储过程、存储函数等包含有 SQL SECURITY DEFINER 的用户是否有对应的权限，有权限为 0，无权限为 1，并且 TRACE 字段为空。

Optimizer Trace 开启步骤

mysql:ytt>setoptimizer_trace='enabled=on';Query OK,0rowsaffected(0.00sec)mysql:ytt>setoptimizer_trace_limit=10;Query OK,0rowsaffected(0.00sec)mysql:ytt>setoptimizer_trace_offset=-10;Query OK,0rowsaffected(0.00sec)mysql:ytt>setend_markers_in_json=on;Query OK,0rowsaffected(0.00sec)

这里要注意的是，修改任何一个 Optimizer Trace 相关参数，元数据表information_schema表都会被清空。

mysql:ytt>selectcount(*)frominformation_schema.optimizer_trace;+----------+|count(*)|+----------+|10|+----------+1rowinset(0.00sec)mysql:ytt>setoptimizer_trace_offset=-2;Query OK,0rowsaffected(0.00sec)mysql:ytt>selectcount(*)frominformation_schema.optimizer_trace;+----------+|count(*)|+----------+|0|+----------+1rowinset(0.00sec)

3. Optimizer Trace 的结果

我们用一个最简单的例子来看看Optimizer Trace的大致结构：do 语句非常简单，只用来验证是否语法正确，不出结果。

mysql:ytt>do 1+1; Query OK, 0 rows affected (0.00 sec)

下面是Optimizer Trace结果：

mysql:ytt>selectquery,tracefrominformation_schema.optimizer_trace\G***************************1.row***************************query:do1+1trace: {"steps":[{"join_preparation": {"select#":1,"steps":[{"expanded_query":"/* select#1 */ select (1 + 1) AS `1+1`"}]} },{"join_optimization": {"select#":1,"steps":[]} },{"join_execution": {"select#":1,"steps":[]} }]}1rowinset(0.00sec)

可以看到，Optimizer Trace结果是一个 JSON 串，key为steps，value是一个数组，数组有三个key，分别为：

• join_preparation 准备阶段：这里会做一些 SQL 改写，关键字识别等等，可以看到expanded_query对应的值即为 SQL 语句被改写后的内部 SQL。
• join_optimization 优化阶段：具体 SQL 优化，包括一些可能的逻辑优化，一些根据表统计信息预估的物理优化等等。
• join_execution 最终执行阶段：最终 SQL 采用的执行计划等等。

本篇是Optimizer Trace的开端，由于内容太多，我特地拆分为几篇来写，欢迎继续订阅。