MySQL 用好 Optimizer Trace，深刻理解 SQL 优化过程！
前面的章节（社区专栏《SQL调优》）我们已经写了很多篇幅关于 MySQL 执行计划的解读，今天我们来继续延伸介绍执行计划的链路跟踪功能，也就是 MySQL 的 Optimizer Trace。 在这之前，先来回顾下 EXPLAIN 的结果： EXPLAIN 展示出来的核心数据有： 如果想快速对于 SQL 进行优化，基于以上的结果完全可以满足。但是想深入了解 MySQL 优化器为什么选择这样的执行计划，基于以上的结果就无法满足。 举例说明： 带着这些疑问，我们来介绍 MySQL 的 Optimizer Trace 功能。 简单来讲，Optimizer Trace 是一个 SQL 执行计划的链路跟踪器，跟踪 SQL 的解析、优化、执行等过程，并且把结果记录到 MySQL 元数据表（ 要使用 Optimizer Trace 功能，首先得打开控制开关。谨记：这个功能非常耗费资源，默认关闭的，可以通过调整以下变量开启： 以上几个参数详细解释下： Optimizer Trace 可以跟踪的语句有： 这里要注意的是，修改任何一个 Optimizer Trace 相关参数，元数据表 我们用一个最简单的例子来看看 Optimizer Trace 的大致结构：do 语句非常简单，只用来验证是否语法正确，不出结果。 下面是 Optimizer Trace 结果： 可以看到，Optimizer Trace 结果是一个 JSON 串， 本篇是Optimizer Trace的开端，由于内容太多，我特地拆分为几篇来写，欢迎继续订阅。mysql:ytt>explain select * from t1 a left join y1 b on a.id = b.id where a.r1<100 order by a.r2 desc;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
| 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 | Using where; Using filesort |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ytt.a.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)explain format=tree / explain format=json）展示出来的额外成本数据a 来讲，为什么有索引 idx_r1，但是实际却没有使用，而走的全表扫？a 驱动表 b，而不是表 b 驱动表 a？r2 有索引，但是依然要走排序？1. 什么是 Optimizer Trace？
information_schema.optimizer_trace），之后可以对这张表分析得到很多个执行计划的“为什么？”！2. 如何使用 Optimizer Trace？
mysql:ytt>show variables like '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 |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)enabled=on/off 启用/禁用 Optmizer Trace 功能；one_line=on/off 启用/禁用 json 格式化存储，一般不需改动。LIMIT 子句一样，用来最终展示 Trace 的 SQL 条数。展示的条数越多，对内存消耗越大，默认展示最近的一条记录。比如设置 optimizer_trace_limit 为 10，optimizer_trace_offset 为 -10，就可以最多展示 10 条 Trace 记录。在数据库里，语句调优一般说的是 SELECT 语句，所以大部分场景跟踪的也只有 SELECT 语句。
元数据表字段解析
mysql:ytt>desc information_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 | | | |
+-----------------------------------+----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)end_markers_in_json 来控制）optimizer_trace_max_mem_size 设置的值后，截断的大小（BYTE）Optimizer Trace 开启步骤
mysql:ytt>set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
mysql:ytt>set optimizer_trace_limit=10;
Query OK, 0 rows affected (0.00 sec)
mysql:ytt>set optimizer_trace_offset=-10;
Query OK, 0 rows affected (0.00 sec)
mysql:ytt>set end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)information_schema 表都会被清空。mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql:ytt>set optimizer_trace_offset=-2;
Query OK, 0 rows affected (0.00 sec)
mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)3. Optimizer Trace 的结果
mysql:ytt>do 1+1;
Query OK, 0 rows affected (0.00 sec)mysql:ytt>select query,trace from information_schema.optimizer_trace\G
*************************** 1. row ***************************
query: do 1+1
trace: {
"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": [
]
}
}
]
}
1 row in set (0.00 sec)key 为 steps，value 是一个数组，数组有三个 key，分别为：expanded_query 对应的值即为 SQL 语句被改写后的内部 SQL。
