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，表示开启所有跟踪项。
• 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的缓存大小，避免因为容量大小而不能显示完整的跟踪过程。

执行需要分析的SQL语句，这里以 MySQL索引（四）常见的索引优化手段中示例表作为演示。小鱼这里分析下文中提到的几个sql 语句。

字符串范围查找

查询 information_schema.optimizer_trace，就会以表格的格式输出跟踪记录，其中我们需要查看的信息在trace 字段中。

这里小鱼将信息摘出来作为示例，在信息上进行注释补充讲解，json 格式较长，可能会影响阅读体验。

json 格式是在 trace 字段，由于小鱼这里开启了 end_markers_in_json=on ，trace 字段会以json 格式展示。

分析 SELECT * FROM employees WHERE name < 'Li'; 语句的trace 字段。

在执行一下sql 语句 SELECT * FROM employees WHERE name > 'Li';，同样再查询一下 information_schema.OPTIMIZER_TRACE 信息。

由于json 数据较长，此部分不全部展示了。部分trace 字段的摘要如下：

用trace 工具对比执行这两个sql 语句，我们发现，第一个sql 语句中全表扫描的成本低于索引扫描，mysql最终选择全表扫描，而在第二个语句中索引扫描的成本低于全表扫描，mysql最终选择索引扫描。

关闭trace 工具

参考

• MySQL :: MySQL 8.0 Reference Manual :: 7.9 Debugging MySQL