如何利用MySQL的Optimizer Trace追踪锁优化策略并启动追踪功能？

本文共计834个文字，预计阅读时间需要4分钟。

执行完目标后，使用SQL查询`information_schema.optimizer_trace`后返回空结果，最常见的原因是：

确保以下三点再重试：

• 关闭查询缓存：SET SESSION query_cache_type = OFF
• 用带实际表访问的语句测试，例如 SELECT * FROM users WHERE id = 123，而非常量表达式
• 确认用户有对应表的 SELECT 权限，特别是跨库或含视图时

set optimizer_trace='enabled=on' 之后还要调哪些参数？

只开 enabled=on 很可能拿到截断或难读的输出。关键配套参数必须同步设置：

• end_markers_in_json=on：在 JSON 大括号后加注释（如 /* join_optimization */），否则嵌套深了根本找不到哪段对应哪个阶段
• optimizer_trace_max_mem_size=1000000：默认仅 1MB，复杂 JOIN 或多子查询极易触发 MISSING_BYTES_BEYOND_MAX_MEM_SIZE 非零值，意味着关键决策路径被砍掉
• optimizer_trace_features='greedy_search=on,range_optimizer=on,repeated_subselect=on'：默认虽全开，但显式指定可避免因版本差异或配置继承导致某项意外关闭

推荐一次性启用命令：
SET optimizer_trace='enabled=on', end_markers_in_json=on; SET optimizer_trace_max_mem_size=1000000;

optimizer_trace 能分析锁等待或死锁路径吗？

不能。optimizer_trace 只跟踪优化器决策过程（索引选择、JOIN 顺序、条件化简等），完全不涉及存储引擎层的锁行为。它输出的 TRACE 字段里不会有 lock、wait、transaction 等关键词。想定位锁问题，得换工具：

• 查 INFORMATION_SCHEMA.INNODB_TRX 和 INNODB_LOCK_WAITS 看实时阻塞链
• 开 innodb_status_output_locks=ON，然后执行 SHOW ENGINE INNODB STATUS 获取最近死锁详情
• 配合 performance_schema.data_locks 表抓取具体行锁记录

混淆这两者是常见误区：看到 join_optimization 阶段耗时长，就以为是锁卡住，其实只是优化器在穷举 JOIN 顺序——和锁毫无关系。

为什么 trace 里看不到 range 优化细节？

即使语句用了 WHERE age > 25 AND salary 这类典型范围条件，<code>TRACE 中仍可能缺失 range_optimizer 相关步骤。原因在于：range_optimizer 仅在优化器实际考虑并评估了多个 range 访问路径时才记录；如果唯一可用索引是全表扫描（type: ALL），或唯一有效索引只能做单点查找（type: ref），优化器压根不会进入 range 分析分支。

验证方法：

• 先用 EXPLAIN 确认 type 是 range 或 index
• 检查 possible_keys 是否包含可用于范围扫描的索引（如 KEY idx_age_salary (age, salary)）
• 若满足以上，再查 optimizer_trace，重点看 steps 数组中是否有 "range_analysis" 或 "analyzing_range_alternatives" 节点

容易被忽略的是：复合索引字段顺序必须匹配查询条件的最左前缀，否则 range_optimizer 根本不启动。