Trace 工具简介
Trace 是 MySQL 5.6 版本后提供的 SQL 跟踪工具，用于了解优化器 (optimizer) 在选择执行计划时的决策过程，包括表访问方法、各种开销计算和转换等信息。
当启用 trace 工具时，可以将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中，支持分析以下语句：SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL。
注意：trace 功能默认关闭，启用 trace 工具会对 MySQL 性能产生影响，因此仅适用于临时分析 SQL 语句的使用，使用完毕后请立即关闭。
相关参数介绍
optimizer_traceoptimizer_trace总开关：开启或关闭 optimizer_trace，默认值为enabled=off,one_line=off。enabled：是否开启 optimizer_trace，取值为 on 表示开启，off 表示关闭。one_line：是否开启单行存储，取值为 on 表示开启，off 表示关闭，用 json 格式存储。单行模式可以减少存储空间。optimizer_trace_features控制 optimizer_trace 跟踪的内容，默认值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on，表示开启所有跟踪项。greedy_search：是否跟踪贪心搜索。range_optimizer：是否跟踪范围优化器。dynamic_range：是否跟踪动态范围优化。repeated_subselect：是否跟踪子查询，如果设置为 off，只跟踪第一条 Item_subselect 的执行。optimizer_trace_limit：控制展示多少条 optimizer_trace 结果，默认为 1。optimizer_trace_offset：设置展示 optimizer trace 的偏移量，默认为-1，表示展示最新的一条 SQL 语句。optimizer_trace_max_mem_size：定义 optimizer_trace 堆栈信息允许的最大内存，默认为 1048576。end_markers_in_json：如果 JSON 结构很大，很难将右括号和左括号配对。为了提高可读性，可以设置为 on，在右括号附近添加注释，默认为 off。
注意：
- 这些参数可以使用
set指令进行控制。例如，要开启 trace 工具，可以使用如下指令：set optimizer_trace="enabled=on",end_markers_in_json=on;。通过set global也可以将其设置为全局开启，即每个会话连接都能跟踪执行的 SQL语句。 optimizer_trace_limit和optimizer_trace_offset这两个参数经常一起使用。- 默认情况下，由于
optimizer_trace_offset=-1，optimizer_trace_limit=1，只记录最近的一条 SQL 语句，并且每次只展示一条数据。 - 若通过设置
SET optimizer_trace_offset=-2, optimizer_trace_limit=1，则可以记录倒数第二条 SQL 语句。
如何使用 trace 工具
开启trace 工具，并设置格式为JSON，设置trace的缓存大小，避免因为容量大小而不能显示完整的跟踪过程。
| --- 会话级别临时开启，只在当前会话生效，关闭当前会话后失效 | |
| set session optimizer_trace="enabled=on",end_markers_in_json=on; | |
| --- 此方式需要手动关闭，或者重启使其失效 | |
| set optimizer_trace="enabled=on"; |
执行需要分析的SQL语句，这里以 MySQL索引（四）常见的索引优化手段中示例表作为演示。小鱼这里分析下文中提到的几个sql 语句。
字符串范围查找
| SELECT * FROM employees WHERE name < 'Li'; | |
| SELECT * FROM employees WHERE name > 'Li'; |
查询 information_schema.optimizer_trace，就会以表格的格式输出跟踪记录，其中我们需要查看的信息在trace 字段中。
SELECT * FROM information_schema.OPTIMIZER_TRACE;
这里小鱼将信息摘出来作为示例，在信息上进行注释补充讲解，json 格式较长，可能会影响阅读体验。
json 格式是在 trace 字段，由于小鱼这里开启了 end_markers_in_json=on ，trace 字段会以json 格式展示。
分析 SELECT * FROM employees WHERE name < 'Li'; 语句的trace 字段。
| { | |
| "steps": [ | |
| { | |
| "join_preparation": { --- 第一阶段：sql的准备阶段，对sql进行格式化 | |
| "select#": 1, | |
| "steps": [ | |
| { | |
| "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` < 'Li')" | |
| } | |
| ] /* steps */ | |
| } /* join_preparation */ | |
| }, | |
| { | |
| "join_optimization": { --- 第二阶段：sql的优化阶段 | |
| "select#": 1, | |
| "steps": [ | |
| { | |
| "condition_processing": { --- 条件处理 | |
| "condition": "WHERE", | |
| "original_condition": "(`employees`.`name` < 'Li')", | |
| "steps": [ | |
| { | |
| "transformation": "equality_propagation", | |
| "resulting_condition": "(`employees`.`name` < 'Li')" | |
| }, | |
| { | |
| "transformation": "constant_propagation", | |
| "resulting_condition": "(`employees`.`name` < 'Li')" | |
| }, | |
| { | |
| "transformation": "trivial_condition_removal", | |
| "resulting_condition": "(`employees`.`name` < 'Li')" | |
| } | |
| ] /* steps */ | |
| } /* condition_processing */ | |
| }, | |
| { | |
| "substitute_generated_columns": { | |
| } /* substitute_generated_columns */ | |
| }, | |
| { | |
| "table_dependencies": [ --- 表的依赖情况 | |
| { | |
| "table": "`employees`", | |
| "row_may_be_null": false, | |
| "map_bit": 0, | |
| "depends_on_map_bits": [ | |
| ] /* depends_on_map_bits */ | |
| } | |
| ] /* table_dependencies */ | |
| }, | |
| { | |
| "ref_optimizer_key_uses": [ | |
| ] /* ref_optimizer_key_uses */ | |
| }, | |
| { | |
| "rows_estimation": [ --- 这里预估了表访问的成本 | |
| { | |
| "table": "`employees`", | |
| "range_analysis": { | |
| "table_scan": { --- 全表扫描成本 | |
| "rows": 100067, --- 扫描行数 | |
| "cost": 20304 --- 扫描成本 | |
| } /* table_scan */, | |
| "potential_range_indexes": [ --- 查询可能会使用到的索引 | |
| { | |
| "index": "PRIMARY", --- 主键索引 | |
| "usable": false, | |
| "cause": "not_applicable" | |
| }, | |
| { | |
| "index": "idx_name_age_position", --- 二级索引 | |
| "usable": true, | |
| "key_parts": [ | |
| "name", | |
| "age", | |
| "position", | |
| "id" | |
| ] /* key_parts */ | |
| } | |
| ] /* potential_range_indexes */, | |
| "setup_range_conditions": [ | |
| ] /* setup_range_conditions */, | |
| "group_index_range": { | |
| "chosen": false, | |
| "cause": "not_group_by_or_distinct" | |
| } /* group_index_range */, | |
| "analyzing_range_alternatives": { --- 分析各个索引使用成本 | |
| "range_scan_alternatives": [ | |
| { | |
| "index": "idx_name_age_position", | |
| "ranges": [ | |
| "name < Li" --- 索引的使用范围 | |
| ] /* ranges */, | |
| "index_dives_for_eq_ranges": true, | |
| "rowid_ordered": false, ---使用该索引获取的记录是否按照主键排序 | |
| "using_mrr": false, | |
| "index_only": false, --- 是否使用覆盖索引 | |
| "rows": 50033, --- 索引扫描行数 | |
| "cost": 60041, --- 索引使用成本 | |
| "chosen": false, --- 是否选择该索引 | |
| "cause": "cost" | |
| } | |
| ] /* range_scan_alternatives */, | |
| "analyzing_roworder_intersect": { | |
| "usable": false, | |
| "cause": "too_few_roworder_scans" | |
| } /* analyzing_roworder_intersect */ | |
| } /* analyzing_range_alternatives */ | |
| } /* range_analysis */ | |
| } | |
| ] /* rows_estimation */ | |
| }, | |
| { | |
| "considered_execution_plans": [ | |
| { | |
| "plan_prefix": [ | |
| ] /* plan_prefix */, | |
| "table": "`employees`", | |
| "best_access_path": { --- 最优访问路径 | |
| "considered_access_paths": [ --- 最终选择访问路径 | |
| { | |
| "rows_to_scan": 100067, | |
| "access_type": "scan", --- 访问类型（scan：全表扫描） | |
| "resulting_rows": 100067, | |
| "cost": 20302, | |
| "chosen": true --- 确认选择 | |
| } | |
| ] /* considered_access_paths */ | |
| } /* best_access_path */, | |
| "condition_filtering_pct": 100, | |
| "rows_for_plan": 100067, | |
| "cost_for_plan": 20302, | |
| "chosen": true | |
| } | |
| ] /* considered_execution_plans */ | |
| }, | |
| { | |
| "attaching_conditions_to_tables": { | |
| "original_condition": "(`employees`.`name` < 'Li')", | |
| "attached_conditions_computation": [ | |
| ] /* attached_conditions_computation */, | |
| "attached_conditions_summary": [ | |
| { | |
| "table": "`employees`", | |
| "attached": "(`employees`.`name` < 'Li')" | |
| } | |
| ] /* attached_conditions_summary */ | |
| } /* attaching_conditions_to_tables */ | |
| }, | |
| { | |
| "refine_plan": [ | |
| { | |
| "table": "`employees`" | |
| } | |
| ] /* refine_plan */ | |
| } | |
| ] /* steps */ | |
| } /* join_optimization */ | |
| }, | |
| { | |
| "join_execution": { --- 第三阶段：sql的执行阶段 | |
| "select#": 1, | |
| "steps": [ | |
| ] /* steps */ | |
| } /* join_execution */ | |
| } | |
| ] /* steps */ | |
| } |
在执行一下sql 语句 SELECT * FROM employees WHERE name > 'Li';，同样再查询一下 information_schema.OPTIMIZER_TRACE 信息。
| SELECT * FROM employees WHERE name > 'Li'; | |
| SELECT * FROM information_schema.OPTIMIZER_TRACE; |
由于json 数据较长，此部分不全部展示了。部分trace 字段的摘要如下：
| { | |
| "steps": [ | |
| { | |
| "join_optimization": { | |
| "steps": [ | |
| { | |
| "rows_estimation": [ --- 这里预估了表访问的成本 | |
| { | |
| "analyzing_range_alternatives": {--分析各个索引使用成本 | |
| "range_scan_alternatives": [ | |
| { | |
| "index": "idx_name_age_position", | |
| "ranges": [ | |
| "Li < name" | |
| ] /* ranges */, | |
| "index_dives_for_eq_ranges": true, | |
| "rowid_ordered": false, | |
| "using_mrr": false, | |
| "index_only": false, --- 是否使用覆盖索引 | |
| "rows": 2, --- 索引扫描行数 | |
| "cost": 3.41, --- 索引使用成本 | |
| "chosen": true --- 是否选择该索引 | |
| } | |
| ] | |
| } /* analyzing_range_alternatives */, | |
| } /* range_analysis */ | |
| } | |
| ] /* rows_estimation */ | |
| }, | |
| { | |
| "considered_execution_plans": [ | |
| { | |
| "plan_prefix": [ | |
| ] /* plan_prefix */, | |
| "table": "`employees`", | |
| "best_access_path": { | |
| "considered_access_paths": [ | |
| { | |
| "rows_to_scan": 2, | |
| "access_type": "range", ---访问类型（range：索引范围扫描） | |
| "range_details": { | |
| "used_index": "idx_name_age_position" | |
| } /* range_details */, | |
| "resulting_rows": 2, | |
| "cost": 3.81, | |
| "chosen": true | |
| } | |
| ] /* considered_access_paths */ | |
| } /* best_access_path */, | |
| "condition_filtering_pct": 100, | |
| "rows_for_plan": 2, | |
| "cost_for_plan": 3.81, | |
| "chosen": true | |
| } | |
| ] /* considered_execution_plans */ | |
| }, | |
| ] /* steps */ | |
| } /* join_optimization */ | |
| }, | |
| ] /* steps */ | |
| } |
用trace 工具对比执行这两个sql 语句，我们发现，第一个sql 语句中全表扫描的成本低于索引扫描，mysql最终选择全表扫描，而在第二个语句中索引扫描的成本低于全表扫描，mysql最终选择索引扫描。
关闭trace 工具
set session optimizer_trace="enabled=off";