为什么需要Optimizer Trace
MySQL的EXPLAIN命令可以展示查询的执行计划，但无法揭示优化器为何选择该计划。当一个查询走了全表扫描而不是索引扫描，EXPLAIN只告诉你结果，不告诉你原因——是因为索引成本估算过高？还是因为索引统计信息不准确？Optimizer Trace是MySQL 8.0提供的诊断工具，完整记录优化器的决策过程，包括成本计算、索引选择、JOIN顺序调整等中间步骤。掌握Optimizer Trace，相当于拿到了优化器的决策日志，可以精准定位慢查询根因。
开启Optimizer Trace并捕获执行计划
Optimizer Trace默认关闭，需在会话级别启用：
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576; -- 1MB，默认不够大
执行需要诊断的查询后，从information_schema.OPTIMIZER_TRACE表获取结果：
SELECT * FROM information_schema.OPTIMIZER_TRACE\\G
关闭Trace避免影响性能：
SET optimizer_trace = 'enabled=off';
重要：每次查询后会覆盖上一条Trace，如需保留多条Trace，设置SET optimizer_trace='enabled=on,one_line=off'并设置optimizer_trace_features控制输出范围。
Trace输出结构解读
Optimizer Trace的JSON输出包含三个核心阶段：
1. join_preparation：SQL预处理，包括视图展开、子查询扁平化
2. join_optimization：核心优化阶段，包含索引选择、成本计算、JOIN顺序决策
3. join_execution：执行阶段，临时表创建、文件排序等
最关键的是join_optimization中的rows_estimation和considered_execution_plans两段。前者展示每个表的行数估算，后者展示所有候选执行计划及其成本。
索引选择成本分析实战
以下是一个典型的索引选择困惑场景：
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid' AND created_at > '2026-01-01';
-- 结果：type=ALL, 全表扫描，扫描行数500万
user_id和status上都有索引，为什么优化器选择全表扫描？开启Trace：
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid' AND created_at > '2026-01-01';
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE INTO OUTFILE '/tmp/trace.json';
在Trace的rows_estimation中找到各索引的估算行数：
"index_ix_user_id": {
"rows": 8000,
"cost": 960.0
}
"index_ix_status": {
"rows": 200000,
"cost": 24000.0
}
"table_scan": {
"rows": 5000000,
"cost": 105000.0
}
但优化器最终选择了table_scan。继续查看considered_execution_plans，发现优化器在计算回表成本后，索引ix_user_id的总成本变成了120000（因为8000行回表随机IO开销高），而全表扫描的顺序IO成本为105000。顺序IO比随机IO效率高，当回表行数较多时，全表扫描反而更优。
解法：创建覆盖索引避免回表：
ALTER TABLE orders ADD INDEX ix_user_status_created (user_id, status, created_at);
ANALYZE TABLE orders;
新建的组合索引可以完全覆盖查询条件，无需回表，Trace中该索引成本将大幅下降。
统计信息不准确导致优化器误判
另一种常见情况是索引统计信息过时。MySQL的InnoDB通过采样估算索引基数（Cardinality），采样率默认为8个page，数据分布不均匀时估算偏差极大。
查看当前统计信息：
SHOW INDEX FROM orders;
-- Cardinality列显示索引基数估算值
手动更新统计信息：
ANALYZE TABLE orders;
对于大表（千万行以上），默认采样仍然不够精确。MySQL 8.0支持调整采样page数：
SET GLOBAL innodb_stats_persistent_sample_pages = 64;
ANALYZE TABLE orders;
采样64个page后，Cardinality更接近真实值。但采样越高，ANALYZE耗时越长，需在精度和耗时间取平衡。生产环境建议设置为16-64之间。
JOIN顺序优化分析
多表JOIN的执行顺序对性能影响极大。Optimizer Trace的considered_execution_plans记录了优化器评估的JOIN顺序方案：
"considered_execution_plans": [
{"plan_prefix": [], "table": "orders", "access_type": "ref"},
{"plan_prefix": ["orders"], "table": "users", "access_type": "eq_ref"},
{"plan_prefix": ["users"], "table": "payments", "access_type": "ref"}
]
如果优化器选择的驱动表行数过多，可以检查best_tracing字段中的成本对比。有时优化器无法感知应用层的数据分布特征。
此时可使用Optimizer Hint强制指定驱动表：
SELECT /*+ JOIN_ORDER(orders, users, payments) */ * FROM orders
JOIN users ON orders.user_id = users.id
JOIN payments ON orders.id = payments.order_id
WHERE orders.status = 'paid';
用Trace验证Hint生效后的成本变化，确认优化有效后再固化到代码中。
生产环境使用注意事项
1. 内存消耗：optimizer_trace_max_mem_size默认128KB，复杂查询的Trace可达数百KB。生产环境诊断时建议设为1MB
2. 性能影响：开启Trace会额外消耗5-15%的CPU，诊断完毕立即关闭
3. 权限控制：普通用户无法设置optimizer_trace变量，需SUPER或SESSION_VARIABLES_ADMIN权限
4. Trace不可跨会话：每个会话独立Trace，无法捕获其他会话的优化器决策
5. JSON解析：Trace输出为单行JSON，用JSON_PRETTY()格式化后更易读：SELECT JSON_PRETTY(TRACE) FROM information_schema.OPTIMIZER_TRACE\\G
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-cha-xun-zhi-xing-ji-hua-shen-du-jie-xi/
