三、查询优化器

对于一个 SQL 语句，查询优化器先看是不是能转换成 JOIN ，再将 JOIN 进行优化。

优化步骤：

1. 条件优化
2. 计算全表扫描成本
3. 找出所有能用到的索引
4. 针对每个索引计算不同的访问方式的成本
5. 选出成本最小的索引以及访问方式

开启查询优化器日志

-- 开启查询优化器日志
SET optimizer_trace="enabled=on";

-- 执行sql
SELECT * FROM table WHERE ......;

-- 查看优化器日志信息
SELECT * FROM information_schema.OPTIMIZER_TRACE;

-- 关闭查询优化器日志
SET optimizer_trace="enabled=off";

---

理解查询优化器日志

1. join_preparation 准备阶段

这步主要是进行准备工作，翻译语句工作，美化 SQL ，格式化等，如：将 SELECT * FROM t1; 转换为 SELECT t1.a, t1.b, ... 等。

2. join_optimization 优化阶段

最重要的一步，对 SQL 进行优化。

2.1 条件传递（condition_processing）

等值传递（equality_propagation）

优化相等变量的的值。

a = b AND b = c AND c = 5;

上面这个 SQL 可以转换为：

a = 5 AND b = 5 AND c = 5;

常量传递（constant_propagation）

优化常量，简化条件。

a = 1 AND b > a;

上面这个 SQL 可以转换为：

a = 1 AND b > 1;

移除无用的条件（trivial_condition_removal）

a = 1 AND 1 = 1;

上面这个 SQL 可以转换为：

a = 1;

2.2 替换虚拟生成列（substitute_generated_columns）

2.3 表依赖详情（table_dependencies）

分析表之间的依赖关系。

2.4 列出可用索引（ref_optimizer_key_uses）

列出可用的索引，包括主键聚簇索引、二级索引等。

2.5 行估算成本（rows_estimation）

一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。

I/O 成本

InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。

CPU 成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。

InnoDB 存储引擎规定 读取一个页花费的成本默认是 1.0，读取及检测一条记录是否符合搜索条件的成本默认是 0.2。

2.6 对比执行计划（considered_execution_plans）

对比各个执行计划的开销，选择最优的执行计划。

2.7 附加条件（attaching_conditions_to_tables）

基于 considered_execution_plans 中选择的最优执行计划，改造原有 WHERE 条件，并针对表增加适当的附加条件，以便于单表数据的筛选。

2.8 最终优化（finalizing_table_conditions）

2.9 改善执行计划（refine_plan）

3. join_explain 优化结果展示阶段

生成优化器优化后的结果。

---

优化步骤

在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，
这个成本最低的方案就是所谓的 执行计划，之后才会调用存储引擎提供的接口真正的执行查询。

下面以一个实例来分析这些优化步骤，单表查询语句如下:

SELECT * FROM employees.titles WHERE emp_no > '10101' AND emp_no < '20000' AND to_date = '1991-10-10';

1. 根据搜索条件，找出所有可能使用的索引

• emp_no > '10101' ，这个搜索条件可以使用主键索引 PRIMARY。
• to_date = '1991-10-10' ，这个搜索条件可以使用二级索引 idx_titles_to_date。

上边的查询语句可能用到的索引，也就是 possible keys 只有 PRIMARY 和 idx_titles_to_date。

2. 计算全表扫描的成本

对于 InnoDB 存储引擎来说，全表扫描就是把 聚簇索引 中的记录依次 与 给定的搜索条件 做比较，把符合搜索条件的记录加入到结果集，
所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。

查询成本 = I/O 成本 + CPU 成本

所以计算全表扫描的代价需要两个信息:

1. 聚簇索引占用的页面数
2. 该表中的记录数

MySQL 为每个表维护了一系列的统计信息，查看表的统计信息：

SHOW TABLE STATUS LIKE 'titles';

Rows

表中的记录条数

对于使用 MyISAM 存储引擎的表来说，该值是准确的。

对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。

Data_length

表占用的存储空间字节数

对于使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小。

对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

Data_length = 聚簇索引的页面数量 * 每个页面的大小

计算案例中的全表扫描成本

titles 使用默认 16KB 的页面大小，根据上边查询结果显示 Data_length 的值是 20512768，所以可以反向推导出聚簇索引的页面数量为：

聚簇索引的页面数量 = Data_length / 16 / 1024 = 20512768 / 16 / 1024 = 1252

现已得到聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。但是 MySQL 在真实计算成本时会进行一些微调。

I/O成本 = 1252 * 1 = 1252

1252 指的是聚簇索引占用的页面数，1.0 指的是加载一个页面的成本常数。

CPU成本 = 442070 * 0.2 = 88414

442070 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估计值，0.2 指的是访问一条记录所需的成本常数。

总成本 = 1252 + 88414 = 89666

综上所述，对于 titles 的全表扫描所需的总成本就是 89666。

表中的记录其实都存储在聚簇索引对应 B+树的叶子节点中，所以只要通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的 B+树内节点是不需要访问的，但是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O 成本的依据，是不区分内节点和叶子节点的。

3. 计算 PRIMARY 需要的成本

计算 PRIMARY 需要多少成本的关键问题是：需要预估出根据对应的 WHERE 条件在主键索引 B+树中存在多少条符合条件的记录。

范围区间数

当从索引中查询记录时，不管是 =、in、>、< 这些操作都需要从索引中确定一个范围，不论这个范围区间的索引占用了多少页面，查询优化器都粗略的认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。
本例中使用 PRIMARY 的范围区间只有一个：(10101, 20000)，所以相当于访问这个范围区间的索引付出的 I/O 成本就是：

1 * 1.0 = 1.0

预估范围内的记录数

优化器需要计算索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 PRIMARY 在 (10101, 20000) 这个范围区间中包含多少条数据记录，计算过程：

1. 先根据 emp_no > 10101 这个条件访问一下 PRIMARY 对应的 B+树索引，找到满足 emp_no > 10101 这个条件的第一条记录，把这条记录称为 区间最左记录。
2. 再根据 emp_no < 20000 这个条件继续从 PRIMARY 对应的 B+树索引中找出第一条满足这个条件的记录，把这条记录称为 区间最右记录。
3. 如果 区间最左记录 和 区间最右记录 相隔不太远(只要相隔不大于 10 个页面即可)，那就可以精确统计出满足 emp_no > '10101' AND emp_no < '20000' 条件的记录条数。
   否则只沿着 区间最左记录 向右读 10 个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以 区间最左记录 和 区间最右记录 之间的页面数量就可以了。

怎么估计 区间最左记录 和 区间最右记录 之间有多少个页面呢？

计算它们父节点中对应的目录项记录之间隔着几条记录就可以了。

根据上面的步骤可以算出来 PRIMARY 索引的记录条数，所以读取记录的 CPU 成本：

CPU成本 = 26808 * 0.2 = 5361.6

26808 是预估的需要读取的数据记录条数，0.2 是读取一条记录成本常数。

总成本

确定访问的IO成本 + 过滤数据的CPU成本 = 1.0 + 5361.6 = 5362.6

4. 计算二级索引需要的成本

因为通过二级索引查询需要 回表 ，所以计算二级索引需要的成本时还要加上回表的成本，而回表的成本就相当于下面这个 SQL 执行：

SELECT * FROM employees.titles WHERE 主键 IN (主键值1, 主键值2, 主键值3, ...);

所以 二级索引的成本 = 辅助索引的查询成本 + 回表查询的成本

5. 比较并选出最优

选择成本最小的索引。

6. NULL 值的处理

对于 NULL 值，有三种理解：

1. NULL 值代表一个不确定的值，每个 NULL 值都是独一无二的，在统计列不重复的时候应该都当做是独立的。
2. NULL 值在业务上就是代表没有，所有的 NULL 值代表的意义是一样的，所以所有的 NULL 值都一样，在统计列不重复值的时候应该只算一个。
3. NULL 完全没有意义，在统计列不重复值的时候应该忽略 NULL。

InnoDB 提供了一个系统变量:

SHOW GLOBAL variables LIKE '';

这个变量有三个值：

1. nulls_equal：认为所有 NULL 值都是相等的。这个值也是 innodb_stats_method 的默认值。如果某个索引列中 NULL 值特别多的话，
   这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
2. nulls_unequal：认为所有 NULL 值都是不相等的。如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
3. nulls_ignored：直接把 NULL 值忽略掉。

最好不在索引列中存放 NULL 值才是正解。

7. 控制执行计划

Index Hints

• USE INDEX：限制索引的使用范围，数据表里有很多索引，当 MySQL 对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望 MySQL 只考虑几个索引，而不是全部的索引，
  这就需要用到 USE INDEX 对查询语句进行设置。
• IGNORE INDEX：限制不使用索引的范围。
• FORCE INDEX：我们希望 MySQL 必须使用某一个索引(由于 MySQL 在查询时只能使用一个索引，因此只能强迫 MySQL 使用一个索引)。这就需要使用 FORCE INDEX 来完成这个功能。

基本语法格式:

SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index, col2_index) WHERE col1 = 1 AND col2 = 2 AND col3 = 3;