1.6 MySQL查询优化器

1. 查询优化器是什么

• 把语法分析树变为查询树，而查询树有很多种，每种查询树对应一种查询的执行方式
• 查询优化器的作用就是找到其中最好的执行方式

2. 查询优化器怎么优化SQL

• 涉及两个优化：逻辑查询优化和物理查询优化

2.1. 逻辑查询优化

• 如何找出SQL语句等价的变换形式，使得SQL执行更高效

2.1.1. 基于规则的优化

MySQL会把一些糟糕的语句转换成高效的语句，这就叫查询重写

2.1.1.1. 条件化简

2.1.1.2. 外连接消除

2.1.1.3. 子查询优化

2.2. 物理查询优化

• 从可选的单表扫描方式中，挑选什么样的单表扫描方式是最优的？
• 对于两个表连接时，如何选择是最优的？
• 对多个表连接，连接顺序有多种组合，是否要对每种组合都探索？如果不全部探索，怎么找到最优的一种组合？

2.3. 基于成本的优化

2.3.1. 表的访问方法

• MySQL表访问方法.md

2.3.2. 单表查询成本

1. 根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
   • 每个索引都维护了一份统计数据MySQL统计数据.md
4. 对比各种执行方案的代价，找出成本最低的那一个

2.3.3. 连接查询成本

2.3.3.1. 两表

• 2*1=2种连接顺序
• 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
  • 对驱动表进行查询后得到的记录条数称之为驱动表的扇出

2.3.3.2. 多表

• n!种连接顺序

3. 查询优化器的决策过程

3.1. optimizer trace表

• explain只能看到优化器使用的执行计划，optimizer trace表则是分析MySQL怎么决策的

3.2. 使用

• 默认是关闭的：SHOW VARIABLES LIKE 'optimizer_trace';
• 开启：SET optimizer_trace="enabled=on";
• 输入查询语句并执行：select xxx
• 从OPTIMIZER_TRACE表中查看优化过程：SELECT * FROM information_schema.OPTIMIZER_TRACE;
• 关闭：SET optimizer_trace="enabled=off";
• 分析：
  • 优化过程大致分为了三个阶段：prepare 阶段、optimize 阶段、execute 阶段
  • 基于成本的优化主要集中在 optimize 阶段
    • 对于单表查询来说，我们主要关注 optimize 阶段的 “rows_estimation” 这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；
    • 对于多表连接查询来说，我们更多需要关注 “considered_execution_plans” 这个过程，这个过程里会写明各种不同的连接方式所对应的成本

4. 查询优化器的优化结果

4.1. 执行计划

MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是执行计划

• MySQL explain.md

5. 参考

• 技术分享 | MySQL 查询优化 - 知乎
• [玩转MySQL之六]MySQL查询优化器 - 知乎