共计 14939 个字符，预计需要花费 38 分钟才能阅读完成。
TIPS
本文基于 MySQL 8.0 编写，实践反对 MySQL 5.6 及更高版本。
OPTIMIZER_TRACE 是 MySQL 5.6 引入的一项跟踪性能，它能够跟踪优化器做出的各种决策（比方拜访表的办法、各种开销计算、各种转换等），并将跟踪后果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。此性能默认敞开，开启后，可剖析如下语句：
- SELECT
- INSERT
- REPLACE
- UPDATE
- DELETE
- EXPLAIN
- SET
- DECLARE
- CASE
- IF
- RETURN
- CALL
OPTIMIZER_TRACE 相干参数
TIPS
参考 https://dev.mysql.com/doc/int…
-
optimizer_trace
- optimizer_trace 总开关，默认值：
enabled=off,one_line=off - enabled：是否开启 optimizer_trace；on 示意开启，off 示意敞开。
- one_line：是否开启单行存储。on 示意开启；off 示意敞开，将会用规范的 JSON 格式化存储。设置成 on 将会有良好的格局，设置成 off 可节俭一些空间。
- optimizer_trace 总开关，默认值：
-
optimizer_trace_features
- 管制 optimizer_trace 跟踪的内容，默认值：
greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on，示意开启所有跟踪项。
- 管制 optimizer_trace 跟踪的内容，默认值：
-
greedy_search：是否跟踪贪婪搜寻，无关贪婪算法详见 https://blog.csdn.net/weixin_…
- range_optimizer：是否跟踪范畴优化器
-
dynamic_range：是否跟踪动静范畴优化
- repeated_subselect：是否跟踪子查问，如果设置成 off，只跟踪第一条 Item_subselect 的执行
- 详见 https://dev.mysql.com/doc/int…
- optimizer_trace_limit：管制 optimizer_trace 展现多少条后果，默认 1
- optimizer_trace_max_mem_size：optimizer_trace 堆栈信息容许的最大内存，默认 1048576
- optimizer_trace_offset：第一个要展现的 optimizer trace 的偏移量，默认 -1。
-
end_markers_in_json：如果 JSON 构造很大，则很难将右括号和左括号配对。为了帮忙读者浏览，可将其设置成 on，这样会在右括号左近加上正文，默认 off。
参考：https://dev.mysql.com/doc/int…
TIPS
以上参数可用 SET 语句操作，例如，用如下命令即可关上 OPTIMIZER TRACE
> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; >也可用 SET GLOBAL 全局开启。但即便全局开启 OPTIMIZER_TRACE，每个 Session 也只能跟踪它本人执行的语句：
> SET GLOBAL OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; >
optimizer_trace_limit 和 optimizer_trace_offset 这两个参数常常配合应用，例如：
> SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT> >这两个参数配合应用，有点相似 MySQL 外面的 limit 语句。
默认状况下，因为 optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条 SQL 语句，展现时，每次展现 1 条数据；
如果改成
SET optimizer_trace_offset=-2, optimizer_trace_limit=1，则会记录倒数第二条 SQL 语句；无关 optimizer_trace_offset、optimizer_trace_limit 更多细节，可参考 https://dev.mysql.com/doc/int…
OPTIMIZER_TRACE 应用
-
开启 OPTIMIZER_TRACE 性能，并设置要展现的数据条目数：
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; SET optimizer_trace_offset=-30, optimizer_trace_limit=30; -
发送你想要剖析的 SQL 语句，例如：
select * from salaries where from_date = '1986-06-26' and to_date = '1987-06-26'; -
应用如下语句剖析，即可取得相似如下的后果：
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G; *************************** 1. row *************************** QUERY: select * from salaries where from_date = '1986-06-26' and to_date = '1987-06-26' TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ {"expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` ='1986-06-26') and (`salaries`.`to_date` ='1987-06-26'))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`salaries`.`from_date` ='1986-06-26') and (`salaries`.`to_date` ='1987-06-26'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))" } ] /* steps */ } /* condition_processing */ }, {"substitute_generated_columns": {} /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`salaries`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] /* depends_on_map_bits */} ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`salaries`", "field": "from_date", "equals": "DATE'1986-06-26'","null_rejecting": false }, { "table": "`salaries`", "field": "to_date", "equals": "DATE'1987-06-26'","null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`salaries`", "range_analysis": { "table_scan": { "rows": 2838216, "cost": 286799 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "salaries_from_date_to_date_index", "usable": true, "key_parts": [ "from_date", "to_date", "emp_no" ] /* 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 */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "salaries_from_date_to_date_index", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "salaries_from_date_to_date_index", "ranges": ["0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 86, "cost": 50.909, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "salaries_from_date_to_date_index", "rows": 86, "ranges": ["0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"] /* ranges */ } /* range_access_plan */, "rows_for_plan": 86, "cost_for_plan": 50.909, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ {"plan_prefix": [] /* plan_prefix */, "table": "`salaries`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "salaries_from_date_to_date_index", "rows": 86, "cost": 50.412, "chosen": true }, { "access_type": "range", "range_details": {"used_index": "salaries_from_date_to_date_index"} /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 86, "cost_for_plan": 50.412, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": {"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`salaries`", "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`salaries`", "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))", "final_table_condition": null } ] /* finalizing_table_conditions */ }, { "refine_plan": [ {"table": "`salaries`"} ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [] /* steps */} /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) -
剖析实现，敞开 OPTIMIZER_TRACE
SET optimizer_trace="enabled=off";
OPTIMIZER_TRACE 后果剖析
由下面的后果可知，OPTIMIZER_TRACE 有四个字段：
- QUERY：查问语句
- TRACE：QUERY 字段对应语句的跟踪信息
- MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数。
- INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具备权限时，该列信息为 1 且 TRACE 字段为空，个别在调用带有 SQL SECURITY DEFINER 的视图或者是存储过程的状况下，会呈现此问题。
TIPS
参考：https://dev.mysql.com/doc/ref…
最外围的是 TRACE 字段的内容。咱们逐段剖析：
join_preparation
join_preparation 段落展现了筹备阶段的执行过程。
{
"join_preparation": {
"select#": 1,
"steps": [
{
-- 比照下原始语句，能够晓得，这一步做了个格式化。"expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` ='1986-06-26') and (`salaries`.`to_date` ='1987-06-26'))"
}
]
/* steps */
}
/* join_preparation */
}join_optimization
join_optimization 展现了优化阶段的执行过程，是剖析 OPTIMIZER TRACE 的重点。这段内容超级长，而且分了好多步骤，无妨依照步骤逐段剖析：
condition_processing
该段用来做条件解决，次要对 WHERE 条件进行优化解决。
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`salaries`.`from_date` ='1986-06-26') and (`salaries`.`to_date` ='1987-06-26'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
}
] /* steps */
} /* condition_processing */其中：
- condition：优化对象类型。WHERE 条件句或者是 HAVING 条件句
- original_condition：优化前的原始语句
-
steps：次要包含三步，别离是 quality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（有效条件移除的转换）
- transformation：转换类型句
- resulting_condition：转换之后的后果输入
substitute_generated_columns
substitute_generated_columns 用于替换虚构生成列
"substitute_generated_columns": {} /* substitute_generated_columns */table_dependencies
剖析表之间的依赖关系
{
"table_dependencies": [
{
"table": "`salaries`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [] /* depends_on_map_bits */}
] /* table_dependencies */
}其中：
- table：波及的表名，如果有别名，也会展现进去
- row_may_be_null：行是否可能为 NULL，这里是指 JOIN 操作之后，这张表里的数据是不是可能为 NULL。如果语句中应用了 LEFT JOIN，则后一张表的 row_may_be_null 会显示为 true
- map_bit：表的映射编号，从 0 开始递增
- depends_on_map_bits：依赖的映射表。次要是当应用 STRAIGHT_JOIN 强行管制连贯程序或者 LEFT JOIN/RIGHT JOIN 有程序差异时，会在 depends_on_map_bits 中展现前置表的 map_bit 值。
ref_optimizer_key_uses
列出所有可用的 ref 类型的索引。如果应用了组合索引的多个局部（例如本例，用到了 index(from_date, to_date) 的多列索引），则会在 ref_optimizer_key_uses 下列出多个元素，每个元素中会列出 ref 应用的索引及对应值。
{
"ref_optimizer_key_uses": [
{
"table": "`salaries`",
"field": "from_date",
"equals": "DATE'1986-06-26'","null_rejecting": false
},
{
"table": "`salaries`",
"field": "to_date",
"equals": "DATE'1987-06-26'","null_rejecting": false
}
] /* ref_optimizer_key_uses */
}rows_estimation
顾名思义，用于估算须要扫描的记录数。
{
"rows_estimation": [
{
"table": "`salaries`",
"range_analysis": {
"table_scan": {
"rows": 2838216,
"cost": 286799
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "salaries_from_date_to_date_index",
"usable": true,
"key_parts": [
"from_date",
"to_date",
"emp_no"
] /* 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 */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "salaries_from_date_to_date_index",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "salaries_from_date_to_date_index",
"ranges": ["0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 86,
"cost": 50.909,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "salaries_from_date_to_date_index",
"rows": 86,
"ranges": ["0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 86,
"cost_for_plan": 50.909,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
}其中：
- table：表名
-
range_analysis：
- table_scan：如果全表扫描的话，须要扫描多少行（row，2838216），以及须要的代价（cost，286799）
- 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
TIPS
skip_scan_range 是 MySQL 8.0 的新个性，感兴趣的可详见 https://blog.csdn.net/weixin_…
-
analyzing_range_alternatives：剖析各个索引的应用老本
-
range_scan_alternatives：range 扫描剖析
- index：索引名
- ranges：range 扫描的条件范畴
- index_dives_for_eq_ranges：是否应用了 index dive，该值会被参数 eq_range_index_dive_limit 变量值影响。
- rowid_ordered：该 range 扫描的后果集是否依据 PK 值进行排序
- using_mrr：是否应用了 mrr
- index_only：示意是否应用了笼罩索引
- rows：扫描的行数
- cost：索引的应用老本
- chosen：示意是否应用了该索引
- analyzing_roworder_intersect：剖析是否应用了索引合并（index merge），如果未应用，会在 cause 中展现起因；如果应用了索引合并，会在该局部展现索引合并的代价。
-
-
chosen_range_access_summary：在前一个步骤中剖析了各类索引应用的办法及代价，得出了肯定的两头后果之后，在 summary 阶段汇总前一阶段的两头后果确认最初的计划
-
range_access_plan：range 扫描最终抉择的执行打算。
- type：展现执行打算的 type，如果应用了索引合并，则会显示 index_roworder_intersect
- index：索引名
- rows：扫描的行数
- ranges：range 扫描的条件范畴
- rows_for_plan：该执行打算的扫描行数
- cost_for_plan：该执行打算的执行代价
- chosen：是否抉择该执行打算
-
considered_execution_plans
负责比照各可行打算的开销，并抉择绝对最优的执行打算。
{
"considered_execution_plans": [
{"plan_prefix": [] /* plan_prefix */,
"table": "`salaries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "salaries_from_date_to_date_index",
"rows": 86,
"cost": 50.412,
"chosen": true
},
{
"access_type": "range",
"range_details": {"used_index": "salaries_from_date_to_date_index"} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 86,
"cost_for_plan": 50.412,
"chosen": true
}
] /* considered_execution_plans */
}其中：
- plan_prefix：以后打算的前置执行打算。
- table：波及的表名，如果有别名，也会展现进去
-
best_access_path：通过比照 considered_access_paths，抉择一个最优的拜访门路
-
considered_access_paths：以后思考的拜访门路
- access_type：应用索引的形式，可参考 explain 中的 type 字段
- index：索引
- rows：行数
- cost：开销
- chosen：是否选用这种执行门路
-
- condition_filtering_pct：相似于 explain 的 filtered 列，是一个估算值
- rows_for_plan：执行打算最终的扫描行数，由 considered_access_paths.rows X condition_filtering_pct 计算取得。
- cost_for_plan：执行打算的代价，由 considered_access_paths.cost 相加取得
- chosen：是否抉择了该执行打算
attaching_conditions_to_tables
基于 considered_execution_plans 中抉择的执行打算，革新原有 where 条件，并针对表减少适当的附加条件，以便于单表数据的筛选。
TIPS
- 这部分条件的减少次要是为了便于 ICP（索引条件下推），但 ICP 是否开启并不影响这部分内容的结构。
- ICP 参考文档：https://www.cnblogs.com/Terry…
{
"attaching_conditions_to_tables": {"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
"attached_conditions_computation": [ ] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`salaries`",
"attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
}其中：
- original_condition：原始的条件语句
- attached_conditions_computation：应用启发式算法计算已应用的索引，如果已应用的索引的拜访类型是 ref，则计算用 range 是否应用组合索引中更多的列，如果能够，则用 range 的形式替换 ref。
-
attached_conditions_summary：附加之后的状况汇总
- table：表名
- attached：附加的条件或原语句中能间接下推给单表筛选的条件。
finalizing_table_conditions
最终的、通过优化后的表条件。
{
"finalizing_table_conditions": [
{
"table": "`salaries`",
"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
"final_table_condition": null
}
] /* finalizing_table_conditions */
}refine_plan
改善执行打算：
{
"refine_plan": [
{"table": "`salaries`"}
] /* refine_plan */
}其中：
- table：表名及别名
join_execution
join_execution 段落展现了执行阶段的执行过程。
"join_execution": {
"select#": 1,
"steps": [] /* steps */}参考文档
- Tracing the Optimizer
- 手把手教你意识 OPTIMIZER_TRACE
- MYSQL sql 执行过程的一些跟踪剖析 (二.mysql 优化器追踪剖析)
- 应用 Trace 进行执行打算剖析
相干文章
- 全网最全 | MySQL EXPLAIN 齐全解读
- MySQL 建表语句转 PostgreSQL 建表语句全纪录
本文由博客一文多发平台 OpenWrite 公布！
正文完
