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

 

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

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

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

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

 

准备阶段

 

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

 

名称识别：主要包括将找到并补全对应语句的表名，库名等；

 

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

 

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

 

逻辑变换

 

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

 

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

 

等值常量传递：利用了等值关系的传递特性，为了能够尽早执行下推运算（后面会讲到）；

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

 

举例如下：

代价优化准备

 

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

 

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

 

确定JOIN顺序：不同的JOIN顺序对性能影响极大；

 

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

 

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

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

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

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

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

确定JOIN顺序

 

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

 

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

 

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

 

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

 

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

 

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

索引条件下推

 

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

 

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

 

总结

 

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

 

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

 

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