MySQL优化器原理
查询优化器是专门负责优化查询语句的优化器模块，通过计算分析收集的各种系统统计信息，为查询给出最优的执行计划——最优的数据检索方式。
MySQL的优化器主要是将SQL经过语法解析/词法解析后得到的语法树，通过MySQL的数据字典和统计信息的内容，经过一系列运算，从而得出一个执行计划树的构成。之后MySQL按照执行树的要求，计算得出结果。也就是说优化器的输入是一个语法树，输出是一个执行树（也称为执行计划）
前言
解析器做了什么？
解析器会做如下两件事情：
- 词法分析：根据输入的sql识别出关键字。例如sql
select username from userinfo，在进行词法分析之后，能够得到4个token，其中两个关键字，两个非关键字 - 语法分析：根据词法分析的结果，判断sql是否满足mysql语法，如果语法有问题就报错，如果没有问题就构建出语法树
准备阶段做了什么？
准备阶段，也可以称为预处理器，主要做了以下事情：
名称识别：找到并补全对应语句的表名，库名，字段名等（将select * 中的* 替换为具体的字段名）
语义检查：通过数据字典如果找不到对应的表或字段，则直接报错
权限验证：确保用户具有执行该查询所需的权限
初级语义变换：主要是根据语义规则，把一些外连接直接转成内连接，子查询EXIST转成IN，然后IN再转成SEMIJOIN等功能
优化器
经过前面的两个步骤，sql语句通过了检查，现在将构建出的语法树作为输入，交给优化器进行加工处理，最后得到执行计划
逻辑变换-化简
逻辑变换就是在关系代数基础上的变换，即化简，前后保证结果一致。主要包括：
否定消除：对于多个表达式的和取或析取范式前面有否定的情况，应将关系条件分解成一个一个的，将外面的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
