深入解析MySQL Optimizer Trace：揭秘SQL执行计划的优化决策过程

1. 什么是MySQL Optimizer Trace当你发现某个SQL查询慢得像蜗牛爬行时第一反应可能是用EXPLAIN看看执行计划。但EXPLAIN只能告诉你优化器最终选择了什么计划却无法揭示它为什么做出这个选择。这时候就该Optimizer Trace登场了——它是MySQL 5.6版本后内置的优化器思维记录仪。简单来说Optimizer Trace会记录优化器在生成执行计划时的完整决策过程包括考虑了哪些索引每个候选索引的成本估算为什么最终选择/放弃某个执行计划排序、分组等操作的实现方式举个例子当你的查询突然从毫秒级变成秒级响应时通过Trace可以清晰看到啊原来优化器误判了数据分布选择了全表扫描而不是索引2. 快速上手Trace工具2.1 开启Trace功能Trace默认是关闭的因为它会产生额外开销。临时分析时可以这样开启-- 会话级开启推荐 SET SESSION optimizer_traceenabledon, end_markers_in_jsonon; -- 全局开启生产环境慎用 SET GLOBAL optimizer_traceenabledon;注意Trace会占用内存可通过optimizer_trace_max_mem_size参数控制默认1MB。分析复杂SQL时可能需要调大。2.2 执行并查看Trace执行你的SQL后立即查询Trace结果-- 执行你的查询 SELECT * FROM orders WHERE user_id 100 AND create_time 2023-01-01; -- 查看Trace SELECT * FROM information_schema.OPTIMIZER_TRACE\G2.3 关闭TraceSET SESSION optimizer_traceenabledoff;3. 解读Trace输出的关键信息Trace输出是一个复杂的JSON结构主要分为三个阶段3.1 准备阶段join_preparation优化器首先会对SQL进行翻译join_preparation: { select#: 1, steps: [ { expanded_query: /* select#1 */ SELECT orders.id...格式化后的SQL } ] }这个阶段会展开视图和派生表处理子查询转换消除常量表达式3.2 优化阶段join_optimization这是最核心的部分包含多个子步骤3.2.1 条件处理condition_processing: { original_condition: ((orders.user_id 100) and (orders.create_time 2023-01-01)), steps: [ { transformation: equality_propagation, resulting_condition: ...优化后的条件 } ] }优化器会重写查询条件比如将a b and b c优化为a b and b c and a c3.2.2 索引分析这部分会列出所有可能使用的索引及其成本估算range_analysis: { table_scan: { rows: 1000000, -- 全表扫描需要读取的行数 cost: 203000 -- 全表扫描的成本 }, potential_range_indexes: [ { index: idx_user, usable: true, key_parts: [user_id] }, { index: idx_create_time, usable: true, key_parts: [create_time] } ], analyzing_range_alternatives: { range_scan_alternatives: [ { index: idx_user, ranges: [100 user_id 100], index_dives_for_eq_ranges: true, rows: 500, cost: 601, chosen: false, cause: cost } ] } }关键指标rows预估需要扫描的行数cost执行成本值越小越好chosen是否被选中cause选择/放弃的原因3.2.3 执行计划选择considered_execution_plans: [ { plan_prefix: [], table: orders, best_access_path: { considered_access_paths: [ { access_type: ref, index: idx_user_create_time, rows: 200, cost: 240, chosen: true } ] } } ]3.3 执行阶段join_execution记录实际执行时的细节join_execution: { select#: 1, steps: [ { filesort_summary: { rows: 200, examined_rows: 200, number_of_tmp_files: 0, sort_mode: sort_key, additional_fields } } ] }4. 实战案例为什么不用我的索引假设有一个用户订单表CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, create_time datetime NOT NULL, status varchar(20) NOT NULL, PRIMARY KEY (id), KEY idx_user (user_id), KEY idx_create_time (create_time), KEY idx_user_create_time (user_id,create_time) ) ENGINEInnoDB;执行一个看似简单的查询SELECT * FROM orders WHERE user_id 100 AND create_time 2023-01-01 ORDER BY create_time DESC;EXPLAIN显示使用了idx_user而不是联合索引idx_user_create_time。通过Trace我们发现analyzing_range_alternatives: { range_scan_alternatives: [ { index: idx_user, ranges: [100 user_id 100], rows: 500, cost: 601, chosen: true }, { index: idx_user_create_time, ranges: [100 user_id 100 AND create_time 2023-01-01], rows: 200, cost: 241, chosen: false, cause: cost } ] }明明联合索引的成本更低241 601为什么没被选中继续往下看attaching_conditions_to_tables: { original_condition: ((orders.user_id 100) and (orders.create_time 2023-01-01)), attached_conditions: { idx_user: (orders.user_id 100), idx_user_create_time: (orders.user_id 100) and (orders.create_time 2023-01-01) } }问题出在索引条件下推(ICP)——优化器认为使用idx_user后可以在存储引擎层直接过滤create_time条件避免了回表。而联合索引需要扫描更多范围。解决方案是使用FORCE INDEXSELECT * FROM orders FORCE INDEX(idx_user_create_time) WHERE user_id 100 AND create_time 2023-01-01 ORDER BY create_time DESC;5. 高级技巧Trace的深度用法5.1 跟踪多语句事务SET optimizer_traceenabledon; START TRANSACTION; -- 执行多个SQL COMMIT; SELECT * FROM information_schema.OPTIMIZER_TRACE;5.2 分析UPDATE/DELETESET optimizer_traceenabledon; UPDATE orders SET status completed WHERE create_time 2022-01-01; SELECT * FROM information_schema.OPTIMIZER_TRACE;5.3 对比不同优化器选择-- 第一次执行 SET optimizer_traceenabledon; SELECT * FROM orders WHERE user_id 100; SELECT * FROM information_schema.OPTIMIZER_TRACE INTO OUTFILE /tmp/trace1.json; -- 修改参数后第二次执行 SET optimizer_switchindex_mergeoff; SET optimizer_traceenabledon; SELECT * FROM orders WHERE user_id 100; SELECT * FROM information_schema.OPTIMIZER_TRACE INTO OUTFILE /tmp/trace2.json; -- 用diff工具比较两个trace文件6. 常见问题排查指南6.1 为什么选择了全表扫描检查Trace中的table_scan部分table_scan: { rows: 1000000, cost: 203000, chosen: true, cause: no better index }可能原因确实没有合适的索引索引统计信息过期执行ANALYZE TABLE查询条件使用了函数导致索引失效6.2 为什么索引合并(Index Merge)没生效在optimizer_switch中查看相关参数SHOW VARIABLES LIKE optimizer_switch;确保包含index_mergeon。在Trace中查找index_merge: { possible_keys: [idx_a, idx_b], cause: too_few_roworder_scans }6.3 排序操作为什么这么慢检查filesort_summaryfilesort_summary: { rows: 10000, examined_rows: 10000, number_of_tmp_files: 5, sort_buffer_size: 262144, sort_mode: sort_key, packed_additional_fields }如果number_of_tmp_files很大说明排序使用了磁盘临时文件考虑增加sort_buffer_size添加合适的索引避免排序7. 性能分析与优化建议7.1 Trace性能影响测试在测试环境模拟-- 开启前 SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM orders WHERE user_id 100)); -- 开启Trace后 SET SESSION optimizer_traceenabledon; SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM orders WHERE user_id 100)); SET SESSION optimizer_traceenabledoff;实测发现开启Trace会使查询性能下降约5-15%因此生产环境应谨慎使用。7.2 最佳实践只在会话级别开启避免影响其他连接限制Trace内存SET optimizer_trace_max_mem_size1048576;1MB结合EXPLAIN使用先用EXPLAIN定位问题再用Trace深入分析定期清理TraceSET optimizer_trace_offset-1;清除历史记录7.3 可视化分析工具对于复杂的Trace输出推荐使用MySQL Workbench的Visual ExplainPercona的pt-visual-explain在线JSON格式化工具如jsonformatter.org比如把这个Trace片段{rows_estimation:[{table:orders,range_analysis:{table_scan:{rows:1000000,cost:203000},potential_range_indexes:[{index:PRIMARY,usable:false,cause:not_applicable},{index:idx_user,usable:true,key_parts:[user_id]}]}}]}格式化后可以清晰看到成本对比表扫描 行数1,000,000 成本203,000 可能使用的索引 - PRIMARY不可用原因不适用 - idx_user可用键部分user_id8. 真实生产案例某电商平台发现订单查询接口偶尔变慢Trace日志显示analyzing_range_alternatives: { range_scan_alternatives: [ { index: idx_user_status, rows: 1500, cost: 1801, chosen: true }, { index: idx_user_create_time, rows: 300, cost: 361, chosen: false, cause: covering_index_not_considered } ] }根本原因是查询同时使用了user_id和status条件优化器认为idx_user_status能覆盖更多条件但实际上status的过滤性很差90%订单都是已完成解决方案-- 创建更合适的联合索引 ALTER TABLE orders ADD INDEX idx_user_time_status(user_id, create_time, status); -- 使用FORCE INDEX引导优化器 SELECT * FROM orders FORCE INDEX(idx_user_time_status) WHERE user_id 100 AND create_time 2023-01-01 ORDER BY create_time DESC;优化后查询耗时从1200ms降至80ms。这个案例展示了如何通过Trace发现优化器的误判并通过索引优化解决问题。