[MySQL 8.0] 通过 Optimizer Trace 概览查询优化

[MySQL 8.0] 通过 Optimizer Trace 概览查询优化

MySQL 查询优化过程可以通过 Optimizer Trace 概览，本文以 TPC-H Q4 为例，系统性总结 MySQL 8.0.31 查询优化，便于了解 MySQL 查询优化细节。
TPC-H Q4 包含分组聚合和子查询，执行计划先进行 nested loop semi join，接着分组聚合，最后排序输出结果。
关注两个问题：1. 优化器如何生成执行计划？2. 执行计划的生成过程是如何进行的？
使用 optimizer trace 功能，首先打开当前 session 的 optimizer_trace，执行 SQL 后获取 trace 信息。
Optimizer Trace 的工作原理是通过在关键代码路径中埋点，收集需要跟踪的信息，如通过 Opt_trace_object、Opt_trace_array 添加到 trace 结果的 json 对象中。
Optimizer trace 结果展示了 SQL 在 MySQL 中的优化过程，大致经历了三个阶段：join_preparation、join_optimization、join_execution。
阶段 1：join_preparation - 包括解析 SQL 子句、优化子查询、常量消除、冗余表达式消除等。
阶段 2：join_optimization - 通过查询重写、基于成本的连接优化和规则驱动的访问路径选择等步骤生成查询执行计划。
阶段 3：join_execution - 负责实际查询执行。
具体优化步骤涉及 SQL 解析、子查询转换、本地优化、连接顺序优化、执行计划评估、条件优化、中间结果过滤、最后的执行计划调整和临时表分配。
通过 optimizer trace，我们可以深入了解 MySQL 的查询优化过程，包括 join_preparation、join_optimization 和 join_execution 的详细步骤，从而更系统地理解查询优化细节。2024-09-05