前序文章
- MySQL架构（一）SQL 查询语句是如何执行的？
- MySQL架构（二）SQL 更新语句是如何执行的？
- MySQL架构（三）mysql的两阶段提交
- MySQL索引（一）底层的数据结构
- MySQl索引（二）如何看懂explain工具信息，使用explain工具来分析索引
- MySQL索引（三）explain实践，优化 MySQL 数据库查询性能
- MySQL索引（四）常见的索引优化手段
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_trace-
optimizer_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，表示开启所有跟踪项。
- 控制 optimizer_trace 跟踪的内容，默认值：
-
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";