MySQL查询优化器浅析
MySQL的查询优化器是其能够高效处理SQL查询的关键所在。本文将详细剖析优化器的工作原理,以及执行计划生成和代价评估的实现方法。
在MySQL中,一个查询需要经过如下几个阶段才能最终被执行:
- 客户端发送SQL语句到MySQL服务器
- 查询解析器进行语法解析,检查查询的正确性
- 优化器生成查询执行计划
- 执行引擎根据计划执行查询
- 返回结果给客户端
其中,查询优化器在第3步发挥重要作用,它根据SQL语句和数据库统计信息,选出最优的执行计划。
当接收到查询解析树后,优化器主要进行以下工作:
- 收集数据库元信息,包括表、索引、分区等统计信息
- 根据查询语句,生成所有可能的执行计划
- 根据代价模型评估每种执行计划的成本
- 选择成本最低的执行计划作为最优计划
- 根据最优执行计划生成可执行的运算操作
- 将最优计划传递给执行引擎
其中,产生执行计划和评估代价是最为核心和复杂的过程。
MySQL生成执行计划主要有两种方法:
1. 迭代法
迭代法从一个基本的partial plan开始,通过迭代添加表,应用转换规则,最终生成一个完整的执行计划。
具体流程如下:
(1) 从一个表开始作为partial plan
(2)考察下一个表,判断是否可以应用优化规则并加入partial plan
(3)重复步骤2,直到所有相关表都加入plan
(4)应用优化规则,转换和改进partial plan
(5)当涵盖所有表后,得到完整的执行计划
迭代法逐步扩展计划空间,可以快速形成初始解,但无法探索全部可能的计划。
2. 搜索法
搜索法会先生成所有可能的执行计划,然后使用代价模型评估每个计划,选择最优解。
常见的搜索策略有:
(1)动态规划:从简单计划开始,逐步组合成复杂计划
(2)随机搜索:使用随机技术搜索全局最优解
(3)基于图的搜索:将计划空间表示为图,使用最短路径等算法搜索最优解
搜索法可以提供更全面的计划选择,但需要耗费更高的计算资源。
对生成的执行计划进行代价评估,是选择最优计划的关键步骤。
MySQL使用的代价模型主要有:
1. 单位代价模型
给每个查询操作定义一个默认的代价值,计算总代价时直接乘以操作次数后求和:
例如,全表扫描的默认单位代价是10000,排序的默认单位代价是2。
单位代价模型简单易计算,但无法反映实际情况。
2. 统计信息模型
利用表的统计信息,按行数计算每个操作的实际代价:
这需要收集各表的准确行数统计,但可以得出更接近实际的计划代价。
3. 成本组合模型
综合考虑多项因素,构建一个复杂的代价计算公式:
每个组成部分还要考虑并发、缓存命中等情况,可以更精确地评估计划代价。
MySQL8.0引入的成本模型就是这种组合计算方法。
除了执行计划的生成和代价评估,优化器还会应用各种优化规则来改进计划:
- 扫描方式优化:选择索引扫描或全表扫描
- Join优化:改变Join顺序、选择Join方法(Nested Loop,Hash Join等)
- 子查询优化:子查询提前或子查询去关联化
- 排序优化:通过索引避免排序、排序顺序优化等
这些规则可以在迭代法或搜索法产生计划时增量引用,进一步优化计划。
MySQL查询优化器通过执行计划生成和代价评估产生最优执行计划,并运用各种优化规则不断优化,是MySQL能够高效查询的关键所在。本文详细剖析了其工作原理,希望能帮助读者全面了解MySQL优化器。
