什么是Optimizer Trace

OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。

EXPLAIN可以帮助您查看查询是否进行全表扫描，还是进行稍微优化的索引扫描。但EXPLAIN并没有真正告诉你MySQL为什么这样做。而Optimizer Trace旨在生成人类和程序可读的输出，以帮助理解MySQL优化器所做的决策和操作。

如何使用

通常使用步骤如下

1. 确认开启了优化器跟踪
   默认是关闭的

mysql> SELECT @@optimizer_trace;
+--------------------------+
| @@optimizer_trace        |
+--------------------------+
| enabled=off,one_line=off |
+--------------------------+
1 row in set (0.00 sec)

启用优化器跟踪，在会话中开启

SET SESSION OPTIMIZER_TRACE="enabled=on"; # enable tracingmysql> SELECT @@OPTIMIZER_TRACE;
+--------------------------+
| @@OPTIMIZER_TRACE        |
+--------------------------+
| enabled=off,one_line=off |
+--------------------------+
1 row in set (0.00 sec)

2. 执行sql语句，这条sql就是我们想要跟踪的sql，SELECT, EXPLAIN SELECT, UPDATE, DELETE这些操作都可以

3. 查询优化器跟踪信息：SELECT * FROM information_schema.OPTIMIZER_TRACE;
   注意SELECT * FROM information_schema.OPTIMIZER_TRACE;这条sql也会被跟踪，这就是看不到执行的sql的跟踪信息的原因

4. 关闭优化器跟踪：可选操作，因为开启优化器跟踪会耗费资源
   查询跟踪信息是特定于会话的。因此一旦运行了查询并检索到结果，就可以关闭跟踪功能。防止继续执行查询时也进行跟踪从而造成不必要的资源消耗

可跟踪哪些sql

当跟踪生效时，每个SQL语句都会生成一个跟踪信息；更确切地说，是下面几种sql：

1. SELECT
2. INSERT/REPLACE：使用VALUES或SELECT
3. UPDATE/DELETE及其多表的变体
4. EXPLAIN SELECT
5. SET：除非它修改@@optimizer_trace
6. DECLARE
7. 存储过程元素：比如DO、DECLARE/CASE/IF/RETURN，CALL也是如此

如果上述命令是在单独的步骤中准备和执行的，则准备和执行是分开跟踪的。

相关系统变量

https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_optimizer_trace

优化器跟踪相关的变量都是以optimizer_trace_*开头的
以上参数可用SET语句操作，例如，用如下命令即可打开OPTIMIZER TRACE

SET GLOBAL OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_JSON=on;

optimizer_trace

optimizer_trace总开关，默认值：enabled=off,one_line=off

1. enabled：是否开启optimizer_trace；on表示开启，off表示关闭。
2. one_line：是否开启单行存储。on表示开启；off表示关闭，将会用标准的JSON格式化存储。设置成on将会有良好的格式，设置成off可节省一些空间。

SET OPTIMIZER_TRACE="enabled=on";

也可用SET GLOBAL全局开启。但即使全局开启OPTIMIZER_TRACE，每个Session也只能跟踪它自己执行的语句：

optimizer_trace_offset和optimizer_trace_limit

默认情况下，每个新的跟踪都会覆盖之前的跟踪。因此，如果一条语句包含子语句（例如：调用存储过程、存储函数、触发器），则顶部语句和子语句都会生成跟踪，但在执行结束时，只有最后一个子语句的跟踪可见。如果用户想查看另一个子语句的跟踪，可以启用/禁用所需子语句的跟踪功能，但这需要修改routine模块的代码，这是不可能的。optimizer_trace_offset和optimizer_trace_limit这两个变量来控制生成的优化器跟踪信息的条数

optimizer_trace_limit和optimizer_trace_offset这两个参数经常配合使用，

1. optimizer_trace_offset：第一个要展示的optimizer trace的偏移量，默认-1。
2. optimizer_trace_limit：控制optimizer_trace展示多少条结果，默认1

例如：

SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>

其中OFFSET是带符号整数，LIMIT是正整数。此SET的效果如下：

1. 清除所有保存的跟踪信息
2. 如果OFFSET>=0，OPTIMIZER_TRACE上的后续SELECT返回OFFSET最旧保存的第一个LIMIT的跟踪信息；如果OFFSET<0，则OPTIMIZER_TRACE上的后续SELECT返回-OFFSET最新保存的第二个LIMIT的跟踪信息。

默认情况下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条SQL语句，展示时，每次展示1条数据；

mysql> SELECT @@optimizer_trace_offset, @@optimizer_trace_limit;
+--------------------------+-------------------------+
| @@optimizer_trace_offset | @@optimizer_trace_limit |
+--------------------------+-------------------------+
|                       -1 |                       1 |
+--------------------------+-------------------------+
1 row in set (0.00 sec)

如果改成 SET optimizer_trace_offset=-2, optimizer_trace_limit=1 ，则会记录倒数第二条SQL语句；

例如OFFSET=-1和LIMIT=1的组合将显示最后一条轨迹

OFFSET=-2和LIMIT=1将显示倒数第二条跟踪信息，OFFSET=0和LIMIT=5将显示最后五条跟踪信息。

如果关注一个存储过程的最后几个子语句时，这种负OFFSET可能很有用，如下所示：

SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
CALL stored_routine(); # more than 5 sub-statements in this routine
SELECT * FROM information_schema.OPTIMIZER_TRACE; # see only last 5 traces

要跟踪的子语句比较少时，OFFSET为正数可能很有用。
这两个变量调整得越精确，使用的内存就越少。例如OFFSET=0和LIMIT=5将使用内存来记住5个跟踪，因此如果只需要前三个跟踪，OFFSET=0和LIMIT=3更好（LIMIT等于几，跟踪就会在第几个跟踪后停止，因此第四和第五个跟踪不会创建，也不会占用内存）。存储过程可能有一个循环，该循环执行许多子语句，从而生成许多跟踪，这将使用大量内存；例如，适当的OFFSET和LIMIT可以将跟踪限制在循环的一次迭代中。这也提高了速度，因为跟踪子语句会影响性能。

如果OFFSET>=0，则内存中只保留LIMIT的值对应数量的跟踪信息。如果OFFSET<0，会保留(-OFFSET)个跟踪在内存中；事实上，即使LIMIT < (-OFFSET)，看起来好像排除了最后一条语句，实际上也必须跟踪最后一条语句，因为在执行另一条语句后，它将在LIMIT内从末尾开始计算，由于OFFSET<0，实际上还是会跟踪最后一条语句。

optimizer_trace_features

控制optimizer_trace跟踪的内容，默认值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on，表示开启所有跟踪项。

1. greedy_search：是否跟踪贪心搜索
2. range_optimizer：是否跟踪范围优化器
3. dynamic_range：是否跟踪动态范围优化
4. repeated_subselect：是否跟踪子查询，如果设置成off，只跟踪第一条Item_subselect的执行

optimizer_trace_max_mem_size

optimizer_trace_max_mem_size：optimizer_trace堆栈信息允许的最大内存，默认1048576

end_markers_in_json

end_markers_in_json：如果JSON结构很大，则很难将右括号和左括号配对。为了帮助读者阅读，可将其设置成on，这样会在右括号附近加上注释，默认off。

SET END_MARKERS_IN_JSON=on;

information_schema.OPTIMIZER_TRACE

https://dev.mysql.com/doc/refman/8.4/en/information-schema-optimizer-trace-table.html

information_schema.OPTIMIZER_TRACE用于存放优化器跟踪的结果，该表有4列

1. QUERY：跟踪的sql语句.
2. TRACE：JSON格式的跟踪信息
3. MISSING_BYTES_BEYOND_MAX_MEM_SIZE：每个记住的跟踪信息都是一个字符串，随着优化的进行而扩展并向其附加数据。optimizer_trace_max_mem_size变量对所有当前记住的跟踪使用的内存总量设置了限制。如果达到此限制，则当前跟踪不会扩展（因此是不完整的），并且MISSING_BYTES_BEYOND_MAX_MEM_SIZE列显示了跟踪中缺少的字节数。
4. INSUFFICIENT_PRIVILEGES：如果被跟踪的查询使用具有SQL SECURITY值DEFINER的视图或存储过程，则可能是定义者以外的用户被拒绝查看查询的跟踪。在这种情况下，跟踪显示为空，INSUFFICIENT_PRIVILEGES的值为1。否则，该值为0。

TRACE列内容解读

摘自：https://www.itmuch.com/mysql/optimizer-trace

以这条sql为例，SELECT * FROM film WHERE film_id < 500;

可以通过下面这条sql来查询优化器跟踪的结果：

SELECT TRACE FROM information_schema.OPTIMIZER_TRACE
WHERE QUERY = 'SELECT * FROM film WHERE film_id < 500'\G;

结果如下：我们主要关注的是TRACE列的内容，注意：TRACE的内容是JSON格式，并且会很长。这里TRACE列的内容太多就不全贴出来了

注意，如果sql里结尾带了；号，但是information_schema.OPTIMIZER_TRACE的QUERY列是不带分号的，所以查询的时候需要把分号去掉

介绍一个在线可视化查看JSON的网站：https://www.bejson.com/jsonviewernew/

或者通过https://jsonhero.io/这个网站查看也可以

还可以使用以下函数提取TRACE字段的某些部分：

1. JSON_EXTRACT：此函数提取JSON文档的一部分。
2. JSON_DETAILED：此函数以用户可读的方式显示跟踪

Trace结构中的每个SELECT命令都有3个步骤，对应steps节点（steps是一个对象数组）及其3个子元素即：

1. join_preparation：显示查询重写的内容
2. join_optimization：查询优化的信息
3. join_execution：查询执行的信息

下面分别对这3块内容进行解释

join_preparation 准备阶段

单独拿出来，其实这是一条可执行的sql，对比原来的sql（SELECT * FROM film WHERE film_id < 500’）这个sql很长

/* select#1 */ select `film`.`film_id` AS `film_id`,`film`.`title` AS 
`title`,`film`.`description` AS `description`,`film`.`release_year` AS 
`release_year`,`film`.`language_id` AS `language_id`,`film`.`original_language_id` 
AS `original_language_id`,`film`.`rental_duration` AS 
`rental_duration`,`film`.`rental_rate` AS `rental_rate`,`film`.`length` AS 
`length`,`film`.`replacement_cost` AS `replacement_cost`,`film`.`rating` AS 
`rating`,`film`.`special_features` AS `special_features`,`film`.`last_update` AS 
`last_update` from `film` where (`film`.`film_id` < 500)

这一步是为了将查询转化为mysql服务端可识别的查询，列名前都加上了数据库名称来进行限定

join_optimization 优化阶段

join_optimization展示了优化阶段的执行过程，是分析OPTIMIZER TRACE的重点。这段内容很长，而且分了好多步骤

1. condition_processing
2. substitute_generated_columns
3. table_dependencies
4. ref_optimizer_key_uses
5. rows_estimation
6. considered_execution_plans
7. attaching_conditions_to_tables
8. finalizing_table_conditions
9. refine_plan

每个步骤在TRACE结构里：该步骤名称为key，value是一个对象

上述步骤仅适用于单个SELECT查询。如果SELECT查询有子查询，则每个子查询都将有这些步骤以及额外的步骤或重写，以处理子查询构造。

condition_processing

该段用来做条件处理，主要对WHERE条件进行优化处理。

其中：

1. condition：优化对象类型。WHERE条件句或者是HAVING条件句
2. original_condition：优化前的原始语句
3. steps：主要包括三步，分别是
   quality_propagation（等值条件句转换）
   constant_propagation（常量条件句转换）
   trivial_condition_removal（无效条件移除的转换）
   每个step包含两个字段：
   transformation：转换类型句
   resulting_condition：转换之后的结果输出

WHERE 1 = 1 处理

对于sql中常用的 WHERE 1 = 1其实不会出现在这个阶段，而是在join_preparation阶段就被去掉了
比如SELECT * FROM film WHERE 1 = 1 AND film_id < 500，expanded_query内容如下图：

这个过程也不会包含对where 1 = 1的处理

substitute_generated_columns

substitute_generated_columns用于替换虚拟生成列，这里SELECT * FROM film WHERE film_id < 500这条sql没有生成列，所以substitute_generated_columns这个对象是空的

这里不对虚拟列这个特性探究过多了
https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html

table_dependencies

分析表之间的依赖关系

{"table_dependencies": [{"table": "`film`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]
}

其中：

1. table：涉及的表名，如果有别名，也会展示出来
2. row_may_be_null：行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可能为NULL。如果语句中使用了LEFT JOIN，则后一张表的row_may_be_null会显示为true
3. map_bit：表的映射编号，从0开始递增
4. depends_on_map_bits：依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时，会在depends_on_map_bits中展示前置表的map_bit值。

ref_optimizer_key_uses

列出所有可用的ref类型的索引。如果使用了组合索引的多个部分，则会在ref_optimizer_key_uses下列出多个元素，每个元素中会列出ref使用的索引及对应值。

ref一般用于等值匹配

以SELECT * FROM inventory WHERE store_id = 100 AND film_id = 50;为例

{"ref_optimizer_key_uses": [{"table": "`inventory`","field": "film_id","equals": "50","null_rejecting": true},{"table": "`inventory`","field": "store_id","equals": "100","null_rejecting": true},{"table": "`inventory`","field": "film_id","equals": "50","null_rejecting": true}]
}

store_id和film_id实际上是用到了组合索引

rows_estimation

需要扫描的记录数的估算值

json数组，每个元素是一个对象，包含2个key

1. table：表名称
2. range_analysis

range_analysis是一个json对象，包含的字段如下

table_scan

如果全表扫描的话，需要扫描多少行，以及需要的成本

potential_range_indexes

列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不可用的原因是什么；如果可用会列出索引中可用的字段；

setup_range_conditions

如果有可下推的条件，则带条件考虑范围查询

group_index_range

当使用了GROUP BY或DISTINCT时，是否有合适的索引可用。当未使用GROUP BY或DISTINCT时，会显示chosen=false, cause=not_group_by_or_distinct；如使用了GROUP BY或DISTINCT，但是多表查询时，会显示chosen=false，cause =not_single_table。其他情况下会尝试分析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价

skip_scan_range：是否使用了skip scan

skip_scan_range是MySQL 8.0的新特性，https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html

analyzing_range_alternatives：分析各个索引的使用成本

{"range_scan_alternatives": [ // range 扫描分析{"index": "PRIMARY",  	// 索引名"ranges": [  			// range扫描的条件范围"film_id < 500"],// 是否使用了index dive，该值会被参数eq_range_index_dive_limit变量值影响。"index_dives_for_eq_ranges": true,"rowid_ordered": true, // 该range扫描的结果集是否根据PK值进行排序"using_mrr": false, // 是否使用了mrr"index_only": false,  // 表示是否使用了覆盖索引"rows": 499,   // 扫描的行数"cost": 50.3303,   // 索引的使用成本"chosen": true     // 表示是否使用了该索引},{"index": "idx_title","chosen": false,"cause": "no_valid_range_for_this_index"},{"index": "idx_fk_language_id","chosen": false,"cause": "no_valid_range_for_this_index"},{"index": "idx_fk_original_language_id","chosen": false,"cause": "no_valid_range_for_this_index"}],// 分析是否使用了索引合并（index merge），如果未使用，会在cause中展示原因；如果使用了索引合并，		// 会在该部分展示索引合并的代价。"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"  // 未使用索引合并的原因}
}

chosen_range_access_summary

在前一个步骤中分析了各类索引使用的方法及代价，得出了一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案

{"range_access_plan": {// 展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect"type": "range_scan", "index": "PRIMARY",  // 索引名"rows": 499,  // 扫描的行数"ranges": [   // 扫描的条件范围"film_id < 500"]},"rows_for_plan": 499,  // 该执行计划的扫描行数"cost_for_plan": 50.3303,  // 该执行计划的执行代价"chosen": true  // 是否选择该执行计划
}

considered_execution_plans

负责对比各可行计划的开销，并选择相对最优的执行计划。

[{"plan_prefix": [],  // 当前计划的前置执行计划"table": "`inventory`",  // 涉及的表名，如果有别名，也会展示出来"best_access_path": {  // 通过对比considered_access_paths，选择一个最优的访问路径"considered_access_paths": [ // 当前考虑的访问路径{"access_type": "ref",   // 使用索引的方式，可参考explain中的type字段"index": "idx_fk_film_id",  // 使用的索引"rows": 5,   // 预计扫描行数"cost": 1.75,   // 成本估计值"chosen": true  // 是否选用这种执行路径},{"access_type": "ref","index": "idx_store_id_film_id","rows": 1,"cost": 0.35,"chosen": true},{"access_type": "range","range_details": {   // 使用的索引详情信息"used_index": "idx_store_id_film_id"},"chosen": false,"cause": "heuristic_index_cheaper"}]},"condition_filtering_pct": 100,  // 类似于explain的filtered列，是一个估算值// 执行计划最终的扫描行数，由considered_access_paths.rows 乘以 		// condition_filtering_pct计算获得。"rows_for_plan": 1, "cost_for_plan": 0.35,  // 执行计划的代价，由considered_access_paths.cost相加获得"chosen": true  // 是否选择了该执行计划}
]

attaching_conditions_to_tables

基于considered_execution_plans中选择的执行计划，改造原有where条件，并针对表增加适当的附加条件，以便于单表数据的筛选。

这部分条件的增加主要是为了便于索引条件下推，但ICP是否开启并不影响这部分内容的构造。
其中：

1. original_condition：原始的条件语句
2. attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访3. 问类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
3. attached_conditions_summary：附加之后的情况汇总
   1. table：表名
   2. attached：附加的条件或原语句中能直接下推给单表筛选的条件。

finalizing_table_conditions

最终的、经过优化后的表条件。

refine_plan

改善执行计划

join_execution 执行阶段

join_execution段落展示了执行阶段的执行过程

SELECT * FROM inventory
WHERE store_id = (SELECT store_id 
FROM customer WHERE customer_id = 1) LIMIT 5

返回数据5条，steps里包含了6条

select#的值就是查询计划中id字段对应的查询

mysql> EXPLAIN SELECT * FROM inventory-> WHERE store_id = (SELECT store_id FROM customer WHERE customer_id = 1) LIMIT 5;
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | inventory | NULL       | ref   | idx_store_id_film_id | idx_store_id_film_id | 1       | const | 2270 |   100.00 | Using where |
|  2 | SUBQUERY    | customer  | NULL       | const | PRIMARY              | PRIMARY              | 2       | const |    1 |   100.00 | NULL        |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

不同语句跟踪信息的区别

SELECT和EXPLAIN SELECT产生相同的跟踪信息。但是，对于子查询也有例外，因为这两个命令对待子查询的方式不同，例如

SELECT ... WHERE x IN (subq1) AND y IN (subq2)

如果相关的IN判断为false，则SELECT会在执行第一个子查询后终止（用AND连接条件，没必要继续查询了），因此我们不会看到subq2的跟踪信息，而EXPLAIN SELECT会分析所有子查询