98%的人不知道的MySQL优化器原理

发布于 2022-4-22 14:56
浏览
0收藏

 

在日常运维中,相信不少人都收藏了很多关于查询优化的方法论和小技巧,但是仔细想想,你真的了解这些优化背后的原理吗?


查询优化器是专门负责优化查询语句的优化器模块,通过计算分析收集的各种系统统计信息,为查询给出最优的执行计划——最优的数据检索方式。


MySQL的优化器主要是将SQL经过语法解析/词法解析后得到的语法树,通过MySQL的数据字典和统计信息的内容,经过一系列运算,从而得出一个执行计划树的构成。之后MySQL按照执行树的要求,计算得出结果。也就是说优化器的输入是一个语法树,输出是一个执行树(也称为执行计划),SQL优化器的具体模块参考如下图:

98%的人不知道的MySQL优化器原理-开源基础软件社区

本篇文章就为大家具体讲解一下各模块具体的功能,让大家对MySQL优化器有更深入的理解。

 

准备阶段

 

第一个模块是准备阶段,准备阶段包括如下功能:

 

名称识别:主要包括将找到并补全对应语句的表名,库名等;

 

语义检查:通过数据字典如果找不到对应的表名,则直接返回报错;

 

初级语义变换:主要是根据语义规则,把一些外连接直接转成内连接,子查询EXIST转成IN,然后IN再转成SEMIJOIN等功能。

 

逻辑变换

 

逻辑变换也就是在关系代数基础上的变换,变换是为了化简,前后保证结果一致。主要包括:

 

否定消除:对于多个表达式的和取或析取范式前面有否定的情况,应将关系条件分解成一个一个的,将外面的NOT消除;

 

等值常量传递:利用了等值关系的传递特性,为了能够尽早执行下推运算(后面会讲到);

常量表达式计算:对于能够立刻计算出结果的表达式,直接计算结果,并将结果与其他条件尽量提前化简。

 

举例如下:

98%的人不知道的MySQL优化器原理-开源基础软件社区

代价优化准备

 

基于代价的优化主要是用来确定对于每个表,根据条件是否应用索引,应用哪个索引和确定多表连接的顺序等问题。为了能够进行代价优化,需要尝试各种肯能的方法,从而找到一个代价最小的方法。为了能够比较,就需要给定义一个量化指标。基于代价的优化,主要是为了确定采用如下哪一种方法(如果当前表存在该功能的条件下):

 

采用哪种索引:一个表可能有主键,也可能有外键,需要根据条件确定使用哪个索引;

 

确定JOIN顺序:不同的JOIN顺序对性能影响极大;

 

确定子查询的执行策略:MySQL执行子查询有相当多的方式,究竟哪一个方式更好?

 

下面介绍一下MySQL的代价模型。一个查询最基础的就是计算两个表JOIN的代价,代价模型就是根据已知信息(元信息,统计信息等)计算该JOIN的代价,即该运算的预估行数*单位代价。

98%的人不知道的MySQL优化器原理-开源基础软件社区

下面介绍代价量化方法。实际上,查询等待主要耗费在CPU和IO上,MySQL将随机读取一个page的消耗定义为1,其他操作的量化指标都是针对该值得对比。不同MySQL版本定义的指标已不尽相同。主要定义指标参考如下:

98%的人不知道的MySQL优化器原理-开源基础软件社区

从源代码里我们可以看到,比如执行一个查询表达式,MySQL实际上是并没有区分具体哪个运算,而是统一给了一个0.1的值,实际上也是不太科学的。

98%的人不知道的MySQL优化器原理-开源基础软件社区

对于范围查询,MySQL会采用如下代价公式,判断究竟是利用全表扫描还是利用索引。

98%的人不知道的MySQL优化器原理-开源基础软件社区

通过EXPLAIN,可以看到不同的条件下MySQL采用了不同的扫描方式,举例参考如下:

98%的人不知道的MySQL优化器原理-开源基础软件社区

确定JOIN顺序

 

对于有N个表JOIN的查询,实际有N!种可能,如何快速确定采用哪种方法最优,MySQL采用称之为“贪婪算法”的策略,尽可能找到最优路径:

 

将N个表按照数据量大小和索引有无指标综合排序,小的放前面。并将第一个作为初始表,开始试探;

 

按照深度遍历算法对后续表进行展开,记录当前最优路径的代价;

 

如果当前试探部分表的代价大于最优代价,则回溯当前节点,后续也就没必要计算了;

 

为了防止遍历过多,根据变量optimizer_prune_level,采用是否启用启发式剪枝,即是否需要随机跳过一些节点。

 

下面通过示例,可以看到确定顺序的流程,参考如下:

98%的人不知道的MySQL优化器原理-开源基础软件社区

索引条件下推

 

当查询条件都为索引列时,索引条件下推能够将索引条件直接作用于索引上,这样就不需要读取数据文件,将索引数据过滤后的数据读上来,再进行其他条件的过滤,这样能够大大降低非必要的IO操作。

 

下图展示了索引条件下推与不下推数据流程的不同,具备下推的能够极大减少存储层的IO。

98%的人不知道的MySQL优化器原理-开源基础软件社区

 

总结

 

从原来MySQL的只采用基于规则的优化器到目前的基于代价的优化器,实践证明对于大数据量复杂的查询,效果还是比较明显的。同时可也看到随着MySQL8.0的推出,MySQL也加入了一些新指标,比如直方图等,使得代价优化越来越准确,越来越好了。相信这篇文章后使用自己做的查询技巧的时候,也能对背后的原理有一个基本的认知,在工作中更加胸有成竹。

 

作者  梁东阳,数据库研发中心数据库内核工程师,负责腾讯云MySQL的内核开发。

 

文章转自公众号:腾讯云数据库

标签
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐