MySQL 优化器追踪

排查 MySQL 查询性能问题时通常会用 EXPLAIN 来查看执行计划,但是 EXPLAIN 提供的信息十分有限,如果想知道 MySQL 的查询优化器做了些什么,为什么最终选择了当前的执行计划就需要开启优化器追踪了(需要 MySQL 版本 >= 5.6),具体方法如下:

我们就使用 MySQL 官方文档提供的示例来做演示和讲解:https://dev.mysql.com/doc/internals/en/tracing-example.html

1. 查看优化器追踪是否开启

2. 开启优化器追踪

注:优化器追踪最大可用内存默认较小,不调大可能会导致追踪结果返回不全。

开启优化器追踪会消耗系统资源,不建议在生产环境使用,不过我们也可以在会话级别临时开启:

3. 执行需要追踪分析的 SQL

4. 查询优化器追踪结果

MySQL 会将最新执行的 SQL 的优化器追踪结果保存在information_schema.OPTIMIZER_TRACE这张表里:

information_schema.OPTIMIZER_TRACE各字段含义如下:

字段含义
QUERY追踪的 SQL 语句
TRACE追踪结果
MISSING_BYTES_BEYOND_MAX_MEM_SIZE追踪信息过长时,被截断的追踪信息的字节数
INSUFFICIENT_PRIVILEGES执行跟踪语句的用户是否有查看对象的权限。当不具有权限时,该列信息为1且 TRACE 字段为空

5. 分析追踪结果

追踪结果以 JSON 格式展示,它把优化器执行的详细步骤都记录了下来,每一步的含义我都通过查询资料在注释中做了简要说明:

总结

这个优化过程虽然看上去又长又乱,但实际上条理还是很清晰的,我总结了一下大概分为以下几个步骤:

image-20200814094715129

希望能帮助大家理解 MySQL 优化器,更好的解决查询性能的问题。