MySQL是怎样运行的：从根儿上理解MySQL | 查询优化器（一）：单表查询方法、基于成本的优化

谁最便宜就选谁-MySQL基于成本的优化

0.单表访问方法

• 前情回顾——查询优化器和执行计划

MySQL Server 有一个称为 查询优化器 的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的 执行计划 ，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来
真正的执行查询，并将查询结果返回给用户。

• 准备一个表

下面有5个索引

• 为 id 列建立的聚簇索引。
• 为 key1 列建立的 idx_key1 二级索引。
• 为 key2 列建立的 idx_key2 二级索引，而且该索引是唯一二级索引。
• 为 key3 列建立的 idx_key3 二级索引。
• 为 key_part1 、 key_part2 、 key_part3 列建立的 idx_key_part 二级索引，这也是一个联合索引。

CREATE TABLE single_table (
	 id INT NOT NULL AUTO_INCREMENT,
	 key1 VARCHAR(100),
	 key2 INT,
	 key3 VARCHAR(100),
	 key_part1 VARCHAR(100),
	 key_part2 VARCHAR(100),
	 key_part3 VARCHAR(100),
	 common_field VARCHAR(100),
	 PRIMARY KEY (id),
	 KEY idx_key1 (key1),
	 UNIQUE KEY idx_key2 (key2),
	 KEY idx_key3 (key3),
	 KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

1.访问方法（access method）的概念

• 执行方式的分类

这个访问方法就与导航中不同的路线交通工具一个道理，我们人的选择就对应优化器的选择，对于单个表的查询来说，MySQL把查询的执行方式大致分为下边两种:

• 使用全表扫描进行查询：这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是啥查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。
• 使用索引进行查询：因为直接使用全表扫描的方式执行查询要遍历好多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。

2.const

• 概述

根据const这个名字就可以得知，速度最快达到常数级别就是这种访问方式，一般有两种

1. 通过主键访问记录，这是最快的，B+ 树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的 B+ 树叶子节点中的记录就是按照主键列排序的。
2. 通过唯一索引访问记录，这个跟前者一样速度也是十分的快，但是会有一个回表的操作（具体看你查的列）

• 注意

这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const 访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

• 例外

对于唯一索引，这面的不会使用到const，具体什么下面会说

SELECT * FROM single_table WHERE key2 IS NULL;

3.ref

• 概述

对于大多数不唯一的二级索引都会使用这个，因为通过等值判断出来的二级索引记录数不唯一，存在重复的。如果重复的多那么查询的代价就全在回表上面了

对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种 ref 访问方法比 const 差了那么一丢丢，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的

• 注意

• 二级索引列值为 NULL 的情况：无论是唯一二级索引还是普通的二级索引，都会使用到ref去访问
• 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用 ref的访问方法，例如下面的语句

# 前三个使用ref
 SELECT * FROM single_table WHERE key_part1 = 'god like';
 SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
 SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary'
AND key_part3 = 'penta kill';

# 不适用ref
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

4.ref_or_null

• 概述

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来，所以当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

5.range

• 概述

上面可以看到几乎触发条件都是等值匹配，那么如果有模糊查询的范围匹配呢？同理有全表扫描或者二级索引+回表的方式。MySQL中利用索引（聚簇索引或非聚簇索引都可）进行范围匹配的访问方法称之为：range。

• 例子

• 如果采用后者，那么就会在二级索引中匹配三个范围内的记录：key2 的值是 1438、key2 的值是 6328和key2 的值在 38 和 79 之间。

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

• 注意

我们要明确range访问方法使用的范围区间，其实对于 B+ 树索引来说，只要索引列和常数使用= 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、 > 、 < 、 >= 、 <= 、 BETWEEN 、 !=（不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以产生一个所谓的 区间 。

IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间

• 查询优化器如何找到很多不同索引中的最优区间呢？

因为一般情况下在多个索引中只能利用单个二级索引执行查询，所以要分析执行成本，成本主要的因素就取决于扫描成本，扫描的行数少当然执行的快，所以选取索引时会分下面的几种情况，其实就是分析找出范围匹配，看哪个覆盖的最多

1. 所有搜索条件都可以使用某个索引的情况
   SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

2. 有的搜索条件无法使用索引的情况
   SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';，此时就会通过索引下推，虽然使用二级索引 idx_key2 定位记录的阶段用不到 common_field = ‘abc’ 这个条件，那么我们也使用索引去提前匹配key2 > 100一个范围区间，然后使用不到的条件就直接用true去替代，代表以后在过滤

3. 复杂搜索条件下找出范围匹配的区间：这个主要就是分析那些可能用到的索引，得到它们的范围区间。并且进行简化区间

6.index

• 例子

根据下面这个语句，由于 key_part2 并不是联合索引 idx_key_part 最左索引列，所以我们无法使用 ref 或者 range 访问方法来执行这个语句。

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

• 查询方式

根据上面的语句发现符合两个条件：

1. 它的查询列表只有3个列：key_part1 , key_part2 , key_part3，而索引 idx_key_part 又包含这三个列。
2. 搜索条件中只有 key_part2 列。这个列也包含在索引 idx_key_part 中。

根据联合索引的存储方式，我们可以遍历这个二级索引去检索符合key_part2 = 'abc'这个条件的记录，然后直接进行返回

这种采用遍历二级索引记录的执行方式称之为： index 。

7.all

• 概述

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于 InnoDB 表来说也就是直接扫描聚簇索引，这种使用全表扫描执行查询的方式称之为： all 。

8.索引合并

• 概述

我们前边说过 MySQL 在一般情况下执行一个查询时最多只会用到单个二级索引，但不是还有特殊情况么，在这些特殊情况下也可能在一个查询中使用到多个二级索引

8.1 Intersection合并

• 概述

例如SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';，这个语句我们就可以分别使用两个索引值匹配到记录，然后取两个结果集的交集；当然也可以单个索引+回表匹配过滤；主要就是要分析两个执行的成本

• 成本

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是 顺序I/O ，而回表操作是 随机I/O ，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为 回表 而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

• 能够使用的情况

• 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
• 情况二：主键列可以是范围匹配

8.2 Union合并

• 概述

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是 OR 关系。有时候 OR 关系的不同搜索条件会使用到不同的索引。所以上面是交集，现在是并集

• 能够使用的情况

情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

• 情况二：主键列可以是范围匹配
• 情况三：使用 Intersection 索引合并的搜索条件，就是搜索条件的某些部分使用 Intersection 索引合并的方式得到的主键集合和其他方式得到的主键集合取交集

8.3 Sort-Union合并

• 概述

上面Union 索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到

• 例子

比方说下边这个查询就无法使用到 Union 索引合并SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'，这是因为因为两个索引查询出来的记录都不是排好序的，我们无法进行大量的随机IO转化成顺序IO，那么我们就可以提前根据主键id进行排序啊

• 总结

很显然，这种 Sort-Union 索引合并比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。

1.什么是成本

• 概述

在每执行一条查询语句时都会有不同的执行方案，所有方案都能进行量化成成本，存储引擎会选用最低的那种方案，执行成本由下面两个方面组成：

1. I/O成本：将数据和索引从磁盘中加载到内存的过程称为IO成本
2. CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。

• 成本常数

• 规定读取一个页面花费的成本默认是 1.0
• 读取以及检测一条记录是否符合搜索条件的成本默认是 0.2 。

2.单表查询的成本

2.1 准备工作

• 建表

使用的表还是上面的，默认里面有10000条记录

2.2 基于成本的优化步骤（❤）

• 概述

在一条语句正在执行前，MySQL的查询优化器会找出执行该语句的所有方案，然后找出成本最低的方案，后续你可以通过查看执行计划（Explain）看到这个最低成本的方案

• 过程总结

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

• 例子

下面我们将抛出一个复杂的SQL，我们根据步骤，逐步的分析

SELECT * FROM single_table WHERE 
	 key1 IN ('a', 'b', 'c') AND 
	 key2 > 10 AND key2 < 1000 AND 
	 key3 > key2 AND 
	 key_part1 LIKE '%hello%' AND
	 common_field = '123';

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

对于B+树索引，索引列和常数使用了= 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、 > 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以过滤掉一些记录生成一个范围区间，而可能被使用到的索引也会被划分为possible keys

所以我们分析以下上面的语句，得出结论可能使用到的索引为： idx_key1 和 idx_key2 。

• key1 IN ('a', 'b', 'c') ，这个搜索条件可以使用二级索引 idx_key1 。
• key2 > 10 AND key2 < 1000 ，这个搜索条件可以使用二级索引 idx_key2 。
• key3 > key2 ，这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引。
• key_part1 LIKE '%hello%' ， key_part1 通过 LIKE 操作符和以通配符开头的字符串做比较，不可以适用索引。
• common_field = '123' ，由于该列上压根儿没有索引，所以不会用到索引。

计算全表扫描的代价

在InnoDB中全表扫描意味着需要把聚簇索引中的每一条记录都在对比，那么就会把索引对应的页面都加在到内存，上面说到查询成本=IO+CPU成本，那么这个全表扫描的代价取决于：

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

我们如何得到这两个信息?

每个表都会有统计信息，这个统计信息是通过磁盘或者内存的方式进行收集，我们可以通过下面的语句查看统计信息，里面有很多信息，我们只关心两个：

• Rows：本选项表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的 single_table 表是使用
  InnoDB 存储引擎的，所以虽然实际上表中有10000条记录，但是 SHOW TABLE STATUS 显示的 Rows 值只有9693条记录（不准确的原因是性能受限，当然可以通过调整一个参数去设置统计的精度）
• Data_Length：本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小（聚簇索引的页面数量 x 每个页面的大小），也就是说可以这样计算该值的
  大小

SHOW TABLE STATUS LIKE 'single_table'\G

• 现在来计算扫描全表的成本

算式中会有编入编码层的微调值，我们无序在意

I/O成本

97 x 1.0 + 1.1 = 98.1，97 指的是聚簇索引占用的页面数， 1.0 指的是加载一个页面的成本常数，后边的 1.1 是一个微调值，我们不用在意。

CPU 成本

9693 x 0.2 + 1.0 = 1939.6，9693 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估计值， 0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值，我们不用在意。

总成本

98.1 + 1939.6 = 2037.7

计算使用不同索引执行查询的代价

在第一步中查询到会使用 idx_key1 和 idx_key2 这两个索引，查询优化器会优先分析使用唯一二级索引的成本，再分析使用普通索引的成本，所以我们先分析后者

• 使用idx_key2执行查询的成本分析

根据查询语句key2 > 10 AND key2 < 1000可知道字段对应的范围区间就是： (10, 1000)，所以会先定位到这个区间，找到每个记录对应的主键值，然后回表找到完整的记录，那么根据过程成本的计算就会依据下面两个方面

1. 范围区间数量：这个很好理解，询优化器粗暴的认为读取索引的一个范围区间的 I/O成本和读取一个页面是相同的，所以I/O成本就是：1 x 1.0 = 1.0
2. 需要回表的记录数：优化器会在这个二级索引内统计这个区间内到底有多少条记录：首先会找到最左和最后边符合条件的记录，如果在二级索引中两个记录中间的间隔不大于10个页面就能精确的统计出记录数；否则会让最左记录向右读10个页面，然后算每个页面的记录平均值去乘最左/右记录之间的页数（通过目录项）即可，然后测得这个记录数为95条，CPU成本就为：95 x 0.2 + 0.01 = 19.01

但是如果两个记录中间太多页导致最左最右的目录项都在不同的节点中，此时只能继续递归寻找然后接着还要做两件事：

3. 根据这些记录里的主键值到聚簇索引中做回表操作，评估回表操作的 I/O 成本认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面 I/O 。所以回表I/O成本为：95 x 1.0 = 95.0
4. 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立，我们会拿到这些符合主键的95条记录，然后对比其他搜索条件，读取并检测这些完整的用户记录是否符合其余的搜索条件的 CPU 成本如下：95 x 0.2 = 19.0

• 总结idx_key2

至此本例中使用 idx_key2 执行查询的成本如下，总成本为：96.0 + 38.01 = 134.01

• I/O 成本：1.0 + 95 x 1.0 = 96.0 (范围区间的数量 + 预估的二级索引记录条数)
• CPU 成本：95 x 0.2 + 0.01 + 95 x 0.2 = 38.01 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

• 使用idx_key1执行查询的成本分析

idx_key1 对应的搜索条件是： key1 IN ('a', 'b', 'c') ，也就是说相当于3个单点区间：['a', 'a']、['b', 'b']和['c', 'c']，大致的过程就是在二级索引中寻找符合三个区间的记录，找到对应的主键值，然后进行回表。所以我们同样需要两个数据：

1. 范围区间数量： 3 x 1.0 = 3.0
2. 需要回表的记录数：由于使用 idx_key1 时有3个单点区间，所以每个单点区间都需要查找一遍对应的二级索引记录数，找的方法同样是找每个区间的最左最右点，然后统计记录数假设这三个单点区间总共需要回表的记录数就是：35 + 44 + 39 = 118，那么CPU成本就是：118 x 0.2 + 0.01 = 23.61
3. 回表的IO成本：118 x 1.0 = 118.0
4. 得到完成记录：118 x 0.2 = 23.6

• 总结idx_key1

总成本为：121.0 + 47.21 = 168.21

• I/O 成本：3.0 + 118 x 1.0 = 121.0 (范围区间的数量 + 预估的二级索引记录条数)
• CPU 成本：118 x 0.2 + 0.01 + 118 x 0.2 = 47.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

• 两个索引会用到索引合并吗？

本例中有关 key1 和 key2 的搜索条件是使用AND连接起来的，而对于 idx_key1 和 idx_key2 都是范围查询，也就是说查找到的二级索引记录并不是按照主键值进行排序的，所以不会触发合并

对比各种执行方案的代价，找出成本最低的那一个

我们分析三个步骤后有三个方案，很显然，使用 idx_key2 的成本最低，所以当然选择 idx_key2 来执行查询

• 全表扫描的成本： 2037.7
• 使用 idx_key2 的成本： 134.01
• 使用 idx_key1 的成本： 168.21

2.3 基于索引统计数据的成本计算

• 概述

有时候使用索引执行查询时会有许多单点区间，比如使用 IN 语句就很容易产生非常多的单点区间，比如下边这个查询（下边查询语句中的 … 表示还有很多参数）：

SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

• index dive

可以看到上面的语句肯定使用了idx_key1，但是key1列不是唯一列，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，统计方法就是上面介绍的最左最右点。即通过直接访问索引对应的 B+ 树来计算某个范围区间对应的索引记录条数的方式称之为 index dive

• 注意

如果in里面的参数过于多，频繁的触发index dive可能最后导致使用索引的代价比全表扫描还要高，为此提供了一个系统变量eq_range_index_dive_limit（默认为200），去区分in参数的数量何时使用，如果大于200统计记录的方式会进行估算

• 记录数估算——Cardinality属性（基数）

该属性是存储索引统计数据中的某个属性，你可以通过 SHOW INDEX FROM 表名 的语法来查看某个表的各个索引的统计数据，这个属性值表达索引列中不重复值的数量，对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的

• 统计方式

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

此时就要用到两个属性来估算此次的成本，可以计算出一个值的重复次数 ≈ Rows ÷ Cardinality，这个值在Oracle中称为选择性（selectivity），但是计算的方式是两者反过来进行相除

• 使用 SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。
• 使用 SHOW INDEX 语句展示出的 Cardinality 属性。

• 以上面语句为例

计算 key1 列平均单个值的重复次数就是：9693 ÷ 968 ≈ 10（条），那么假设in的参数有20000个，所以总共需要回表的记录数就是：20000 x 10 = 200000

3.连接查询的成本

3.1 准备工作

• 概述

准备一个和上面那个表一模一样的S2，前者为S1

3.2 Condition filtering介绍

• 概述

MySQL采用的连接查询算法是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

• 单次查询驱动表的成本
• 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

• 扇出

我们把对驱动表进行查询后得到的记录条数称之为驱动表的 扇出 （英文名： fanout ）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。

• 关于计算扇出值的三个例子

我们把目光看向最后一个sql，可以看到优化器需要猜在 95 条记录中有多少符合上述两个条件的

# 使用全表扫描，s1作为驱动表，前面讲到过s1的记录数为9693 ，
# 也就是说优化器就直接会把 9693 当作在 s1 表的扇出值。
SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2;


# 使用到idx_key2 索引执行查询，区间有多少记录扇出就为多少
# idx_key2 的范围区间 (10, 1000) 的记录数是95条，
# 也就是说本查询中优化器会把 95 当作驱动表 s1 的扇出值。
 SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
 WHERE s1.key2 >10 AND s1.key2 < 1000;


# 此时回先用在驱动表 s1 选取 idx_key2 索引执行查询后，
# 优化器需要从符合二级索引范围区间的记录中猜有多少条记录（95条）符合下边后两个条件
 SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
 WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
 s1.key1 IN ('a', 'b', 'c') AND
 s1.common_field > 'xyz';

• 什么时候会发生这种猜——condition filtering

• 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
• 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

3.3 两表连接的成本分析

• 公式

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

• 对于外连接

左右外连接的驱动表都是固定的，所以想要得到最优的查询方案只需要：分别为驱动表和被驱动表选择成本最低的访问方法。

• 对于内连接

驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

• 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
• 然后分别为驱动表和被驱动表选择成本最低的访问方法。

• 例子分析

通过观察可以选择的连接顺序有两种，查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划

• s1 连接 s2 ，也就是 s1 作为驱动表， s2 作为被驱动表。
• s2 连接 s1 ，也就是 s2 作为驱动表， s1 作为被驱动表。

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
 ON s1.key1 = s2.common_field 
 WHERE s1.key2 > 10 AND s1.key2 < 1000 AND 
 		s2.key2 > 1000 AND s2.key2 < 2000;

s1作为驱动表，s2作为被驱动表

1. 分析驱动表的成本最低方案：涉及s1的条件是 s1.key2 > 10 AND s1.key2 < 1000，key2是有索引的肯定比全表扫描要来的快
2. 被驱动表的最低方案：分别有两个个搜索方案，肯定选用后者使用索引的方案

• s2.common_field = 常数 （这是因为对驱动表 s1 结果集中的每一条记录，都需要进行一次被驱动表 s2 的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表 s2 了。）
• s2.key2 > 1000 AND s2.key2 < 2000

• 总结

使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本

s2作为驱动表，s1作为被驱动表

1. 分析驱动表的成本最低方案：涉及s2的条件是 s2.key2 > 1000 AND s2.key2 < 2000，key2是有索引的肯定比全表扫描要来的快
2. 被驱动表的最低方案：分别有两个个搜索方案，可以发现两者都是有索引的，前者是ref，后者是range，对于后者的计算成本方式前面说到过，前者的成本计算方式是：直接使用索引统计数据就好了（就是索引列平均一个值重复多少次），一般ref是比range是要优秀一些的，所以我们选用前者

• s1.key1 = 常数
• s1.key2 > 1000 AND s1.key2 < 2000

• 总结

使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本

比较两者

可以看到成本占大头的是 驱动表扇出数 x 单次访问被驱动表的成本 ，所以我们的优化重点其实是下边这两个部分：

• 尽量减少驱动表的扇出
• 对被驱动表的访问成本尽量低

总结

我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用 ref 访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

3.4 多表连接的成本分析

• 多表连接顺序

• 对于两表连接，比如表A和表B连接，只有 AB、BA这两种连接顺序。其实相当于 2 × 1 = 2 种连接顺序。
• 对于三表连接，比如表A、表B、表C进行连接有ABC、ACB、BAC、BCA、CAB、CBA这么6种连接顺序。其实相当于 3 × 2 × 1 = 6 种连接顺序。
• 对于四表连接的话，则会有 4 × 3 × 2 × 1 = 24 种连接顺序。
  对于 n 表连接的话，则有n × (n-1) × (n-2) × ··· × 1种连接顺序，就是n的阶乘种连接顺序，也就是 n! 。

• 解决方案

可以看到，如果表非常多，多表连接的成本估算几乎是不可能，所以我们大致有三种解决方案

• 提前结束某种顺序的成本评估：通过唯一个全局变量，保存内容是最小连接查询成本，当分析每个方案时，如果分析到中间已经超过这个变量，那就直接换下一个
• 系统变量 optimizer_search_depth：这个变量就是控制查询方案的估算数量，如果表的数量小于就敬职敬责的完成任务；如果大于，无论大多少都会让使用这个变量的值去决定估算数量
• 根据某些规则压根儿就不考虑某些连接顺序：就是老马识途，可通过遍历决定是否开启这项功能