MySQL原理之查询优化篇

一、MySQL基于成本的优化

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：

• I/O成本

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

• CPU成本

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

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大叔规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，其余的成本常数我们后边再说哈。

需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

1.1单表查询的成本

为了故事的顺利发展，我们还得把之前用到的single_table表搬来，怕大家忘了这个表长啥样，再给大家抄一遍：

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;

还是假设这个表里边儿有10000条记录，除id列外其余的列都插入随机值。下边正式开始我们的表演。

1.2基于成本的优化步骤

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

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

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

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';

乍看上去有点儿复杂哦，我们一步一步分析一下。

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

我们前边说过，对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引，设计MySQL的大叔把一个查询中可能使用到的索引称之为possible keys。

我们分析一下上边查询中涉及到的几个搜索条件：

• 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'，由于该列上压根儿没有索引，所以不会用到索引。

综上所述，上边的查询语句可能用到的索引，也就是possible keys只有idx_key1和idx_key2。

1.2.2计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：

• 聚簇索引占用的页面数
• 该表中的记录数

这两个信息从哪来呢？设计MySQL的大叔为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的我们放在本章后边详细唠叨，现在看看怎么查看这些统计信息哈。设计MySQL的大叔给我们提供了SHOW TABLE STATUS语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的LIKE语句就好了，比方说我们要查看single_table这个表的统计信息可以这么写：

mysql> SHOW TABLE STATUS LIKE 'single_table'\G
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9693
 Avg_row_length: 163
    Data_length: 1589248
Max_data_length: 0
   Index_length: 2752512
      Data_free: 4194304
 Auto_increment: 10001
    Create_time: 2018-12-10 13:37:23
    Update_time: 2018-12-10 13:38:03
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

然出现了很多统计选项，但我们目前只关心两个：

• Rows

  本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的single_table表是使用InnoDB存储引擎的，所以虽然实际上表中有10000条记录，但是SHOW TABLE STATUS显示的Rows值只有9693条记录。

• Data_length

  本选项表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

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

  我们的single_table使用默认16KB的页面大小，而上边查询结果显示Data_length的值是1589248，所以我们可以反向来推导出聚簇索引的页面数量：

  聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97

我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了，但是设计MySQL的大叔在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，由于没有注释，我也不知道这些微调值是个啥子意思，但是由于这些微调的值十分的小，并不影响我们分析，所以我们也没有必要在这些微调值上纠结了。现在可以看一下全表扫描成本的计算过程：

• 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

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

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

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

从第1步分析我们得到，上述查询可能使用到idx_key1和idx_key2这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。这里需要提一点的是，MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，所以我们也先分析idx_key2的成本，然后再看使用idx_key1的成本。

（1）使用idx_key2执行查询的成本分析

idx_key2对应的搜索条件是：key2 > 10 AND key2 < 1000，也就是说对应的范围区间就是：(10, 1000)，使用idx_key2搜索的示意图就是这样子：

对于使用二级索引 + 回表方式的查询，设计MySQL的大叔计算这种查询的成本依赖两个方面的数据：

• 范围区间数量
  不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用idx_key2的范围区间只有一个：(10, 1000)，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：

  1 x 1.0 = 1.0

• 需要回表的记录数
  查询优化器需要计算二级索引的某个扫描区间到底包含多少条记录，对于本例来说就是要计算uk_key2再（10，1000）扫描区间中包含多少二级索引记录。

  • 步骤1：先根据key2 > 10这个条件访问一下idx_key2对应的B+树索引，找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在B+数树中定位一条记录的过程是贼快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的。

  • 步骤2：然后再根据key2 < 1000这个条件继续从idx_key2对应的B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。

  • 步骤3：如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来：

    

    如图，我们假设区间最左记录在页b中，区间最右记录在页c中，那么我们想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页c之间有多少页面，而每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们父节点（也就是页a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就贼小了。

    不过还有问题，如果页b和页c之间的页面实在太多，以至于页b和页c对应的目录项记录都不在一个页面中该咋办？继续递归啊，也就是再统计页b和页c对应的目录项记录所在页之间有多少个页面。之前我们说过一个B+树有4层高已经很了不得了，所以这个统计过程也不是很耗费性能。

  别忘了数据页中有一个Page Header部分，Page Header中有一个名为PAGE_N_RECS的属性，该属性代表了该页面中目前有多少条记录。所以，如果区间最左记录和区间最右记录所在的页面相隔不太远，我们可以直接遍历这些页面，把这些页面的PAGE_N_RECS属性值加起来就好了。

  知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题中来，根据上述算法测得idx_key2在区间(10, 1000)之间大约有95条记录。读取这95条二级索引记录需要付出的CPU成本就是：

  95 x 0.2 + 0.01 = 19.01

  其中95是需要读取的二级索引记录条数，0.2是读取一条记录成本常数，0.01是微调。

  在通过二级索引获取到记录之后，还需要干两件事儿：

  • 根据这些记录里的主键值到聚簇索引中做回表操作

    这里需要大家使劲儿睁大自己滴溜溜的大眼睛仔细瞧，设计MySQL的大叔评估回表操作的I/O成本依旧很豪放，他们认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。我们上边统计了使用idx_key2二级索引执行查询时，预计有95条二级索引记录需要进行回表操作，所以回表操作带来的I/O成本就是：

    95 x 1.0 = 95.0

    其中95是预计的二级索引记录数，1.0是一个页面的I/O成本常数。

  • 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

    回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2 > 10 AND key2 < 1000这个搜索条件以外的搜索条件是否成立。因为我们通过范围区间获取到二级索引记录共95条，也就对应着聚簇索引中95条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本如下：

    95 x 0.2 = 19.0

    其中95是待检测记录的条数，0.2是检测一条记录是否符合给定的搜索条件的成本常数。

所以本例中使用idx_key2执行查询的成本就如下所示：

• I/O成本：

  1.0 + 95 x 1.0 = 96.0 (范围区间的数量 + 预估的二级索引记录条数)

• CPU成本：

  95 x 0.2 + 0.01 + 95 x 0.2 = 38.01 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用idx_key2执行查询的总成本就是：

96.0 + 38.01 = 134.01

（2）使用idx_key1执行查询的成本分析

idx_key1对应的搜索条件是：key1 IN ('a', 'b', 'c')，也就是说相当于3个单点区间：

• ['a', 'a']
• ['b', 'b']
• ['c', 'c']

使用idx_key1搜索的示意图就是这样子：

与使用idx_key2的情况类似，我们也需要计算使用idx_key1时需要访问的范围区间数量以及需要回表的记录数：

• 范围区间数量

  使用idx_key1执行查询时很显然有3个单点区间，所以访问这3个范围区间的二级索引付出的I/O成本就是：

  3 x 1.0 = 3.0

• 需要回表的记录数

  由于使用idx_key1时有3个单点区间，所以每个单点区间都需要查找一遍对应的二级索引记录数：

  • 查找单点区间['a', 'a']对应的二级索引记录数

    计算单点区间对应的二级索引记录数和计算连续范围区间对应的二级索引记录数是一样的，都是先计算区间最左记录和区间最右记录，然后再计算它们之间的记录数，具体算法上边都唠叨过了，就不赘述了。最后计算得到单点区间['a', 'a']对应的二级索引记录数是：35。

  • 查找单点区间['b', 'b']对应的二级索引记录数

    与上同理，计算得到本单点区间对应的记录数是：44。

  • 查找单点区间['c', 'c']对应的二级索引记录数

    与上同理，计算得到本单点区间对应的记录数是：39。

  所以，这三个单点区间总共需要回表的记录数就是：

  35 + 44 + 39 = 118

  读取这些二级索引记录的CPU成本就是：

  118 x 0.2 + 0.01 = 23.61

  得到总共需要回表的记录数之后，就要考虑：

  • 根据这些记录里的主键值到聚簇索引中做回表操作

    所需的I/O成本就是：

    118 x 1.0 = 118.0

  • 回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立

    此步骤对应的CPU成本就是：

    118 x 0.2 = 23.6

所以本例中使用idx_key1执行查询的成本就如下所示：

• I/O成本：

  3.0 + 118 x 1.0 = 121.0 (范围区间的数量 + 预估的二级索引记录条数)

• CPU成本：

  118 x 0.2 + 0.01 + 118 x 0.2 = 47.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用idx_key1执行查询的总成本就是：

121.0 + 47.21 = 168.21

（3）是否有可能使用索引合并（Index Merge）

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

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

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：

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

很显然，使用idx_key2的成本最低，所以当然选择idx_key2来执行查询喽。

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

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

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

很显然，这个查询可能使用到的索引就是idx_key1，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。计算方式我们上边已经介绍过了，就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。设计MySQL的大叔把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

也就是说，在查询真正执行前的执行计划生成阶段，就可能少量地访问B+树中的数据。

有零星几个单点区间的话，使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题，可是你架不住有的孩子憋足了劲往IN语句里塞东西呀，我就见过有的同学写的IN语句里有20000个参数的🤣🤣，这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive操作，这性能损耗可就大了，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。设计MySQL的大叔们多聪明啊，他们当然考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，我们看一下在MySQL 5.7.21中这个系统变量的默认值：

mysql> SHOW VARIABLES LIKE '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.08 sec)

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。怎么个估算法？继续往下看。

像会为每个表维护一份统计数据一样，MySQL也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法，比如我们查看一下single_table的各个索引的统计数据可以这么写：

mysql> SHOW INDEX FROM single_table;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| single_table |          0 | PRIMARY      |            1 | id          | A         |       9693  |     NULL | NULL   |      | BTREE      |         |               |
| single_table |          0 | idx_key2     |            1 | key2        | A         |       9693  |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key1     |            1 | key1        | A         |        968 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key3     |            1 | key3        | A         |        799 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            1 | key_part1   | A         |        9673 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            2 | key_part2   | A         |        9999 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            3 | key_part3   | A         |       10000 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

哇唔，竟然有这么多属性，不过好在这些属性都不难理解，我们就都介绍一遍吧：

其实我们现在最在意的是Cardinality属性，Cardinality直译过来就是基数的意思，表示索引列中不重复值的个数。比如对于一个一万行记录的表来说，某个索引列的Cardinality属性是10000，那意味着该列中没有重复的值，如果Cardinality属性是1的话，就意味着该列的值全部是重复的。不过需要注意的是，对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。关于这个Cardinality属性的值是如何被计算出来的我们后边再说，先看看它有什么用途。

前边说道，当IN语句中的参数个数大于或等于系统变量eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指的是这两个值：

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

  这个统计数据我们在前边唠叨全表扫描成本的时候说过很多遍了，就不赘述了。

• 使用SHOW INDEX语句展示出的Cardinality属性。

  结合上一个Rows统计数据，我们可以针对索引列，计算出平均一个值重复多少次。

  一个值的重复次数 ≈ Rows ÷ Cardinality

以single_table表的idx_key1索引为例，它的Rows值是9693，它对应索引列key1的Cardinality值是968，所以我们可以计算key1列平均单个值的重复次数就是：

9693 ÷ 968 ≈ 10（条）

此时再看上边那条查询语句：

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

假设IN语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应10条记录，所以总共需要回表的记录数就是：

20000 x 10 = 200000

使用统计数据来计算单点区间对应的索引记录条数可比index dive的方式简单多了，但是它的致命弱点就是：不精确！使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

1.4连接查询的成本

连接查询至少是要有两个表的，只有一个single_table表是不够的，所以为了故事的顺利发展，我们直接构造一个和single_table表一模一样的single_table2表。为了简便起见，我们把single_table表称为s1表，把single_table2表称为s2表。

1.4.1Condition filtering介绍

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

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

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：

• 查询一：

  SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2;

  假设使用s1表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。我们前边说过，统计数据中s1表的记录行数是9693，也就是说优化器就直接会把9693当作在s1表的扇出值。

• 查询二：

  SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
  WHERE s1.key2 >10 AND s1.key2 < 1000;

  仍然假设s1表是驱动表的话，很显然对驱动表的单表查询可以使用idx_key2索引执行查询。此时idx_key2的范围区间(10, 1000)中有多少条记录，那么扇出值就是多少。我们前边计算过，满足idx_key2的范围区间(10, 1000)的记录数是95条，也就是说本查询中优化器会把95当作驱动表s1的扇出值。

事情当然不会总是一帆风顺的，要不然剧情就太平淡了。有的时候扇出值的计算就变得很棘手，比方说下边几个查询：

• 查询三：

  SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
      WHERE s1.common_field > 'xyz';

  本查询和查询一类似，只不过对于驱动表s1多了一个common_field > 'xyz'的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这9693记录里有多少条记录满足common_field > 'xyz'条件。

• 查询四：

  SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
      WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
            s1.common_field > 'xyz';

  本查询和查询二类似，只不过对于驱动表s1也多了一个common_field > 'xyz'的搜索条件。不过因为本查询可以使用idx_key2索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合common_field > 'xyz'条件，也就是只需要猜在95条记录中有多少符合common_field > 'xyz'条件。

• 查询五：

  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';

  本查询和查询二类似，不过在驱动表s1选取idx_key2索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：

  • key1 IN ('a', 'b', 'c')
  • common_field > 'xyz'

  也就是优化器需要猜在95条记录中有多少符合上述两个条件的。

说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：

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

设计MySQL的大叔把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是设计MySQL的大叔单纯的瞎猜，整个评估过程挺复杂的，再仔细的唠叨一遍可能引起大家的生理不适，所以我们就跳过了哈。

1.4.2两表连接的成本分析

连接查询的成本计算公式是这样的：

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

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要：

• 分别为驱动表和被驱动表选择成本最低的访问方法。

可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

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

很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。

左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询，例如查询语句中的Where条件中出现 ==被驱动表的列IS NOT NULL==。

比如对于下边这个查询来说：

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，也就是s1作为驱动表，s2作为被驱动表。
• s2连接s1，也就是s2作为驱动表，s1作为被驱动表。

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。我们分别来看一下（定性的分析一下，不像分析单表查询那样定量的分析了）：

• 使用s1作为驱动表的情况

  • 分析对于驱动表的成本最低的执行方案

    首先看一下涉及s1表单表的搜索条件有哪些：

    • s1.key2 > 10 AND s1.key2 < 1000

    所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，这个过程我们上边都唠叨过了，很显然使用idx_key2执行查询的成本更低些。

  • 然后分析对于被驱动表的成本最低的执行方案

    此时涉及被驱动表s2的搜索条件就是：

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

    很显然，第一个条件由于common_field没有用到索引，所以并没有什么卵用，此时访问s2表时可用的方案也是全表扫描和使用idx_key2两种，假设使用idx_key2的成本更小。

  所以此时使用s1作为驱动表时的总成本就是（暂时不考虑使用join buffer对成本的影响）：

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

• 使用s2作为驱动表的情况

  • 分析对于驱动表的成本最低的执行方案

    首先看一下涉及s2表单表的搜索条件有哪些：

    • s2.key2 > 1000 AND s2.key2 < 2000

    所以这个查询可能使用到idx_key2索引，从全表扫描和使用idx_key2这两个方案中选出成本最低的那个，假设使用idx_key2执行查询的成本更低些。

  • 然后分析对于被驱动表的成本最低的执行方案

    此时涉及被驱动表s1的搜索条件就是：

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

    这时就很有趣了，使用idx_key1可以进行ref方式的访问，使用idx_key2可以使用range方式的访问。这是优化器需要从全表扫描、使用idx_key1、使用idx_key2这几个方案里选出一个成本最低的方案。这里有个问题啊，因为idx_key2的范围区间是确定的：(10, 1000)，怎么计算使用idx_key2的成本我们上边已经说过了，可是在没有真正执行查询前，s1.key1 = 常数中的常数值我们是不知道的，怎么衡量使用idx_key1执行查询的成本呢？其实很简单，直接使用索引统计数据就好了（就是索引列平均一个值重复多少次）。一般情况下，ref的访问方式要比range成本更低，这里假设使用idx_key1进行对s1的访问。

  所以此时使用s2作为驱动表时的总成本就是：

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

最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程也可以看出来，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：

• 尽量减少驱动表的扇出

• 对被驱动表的访问成本尽量低

  这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

1.4.3多表连接的成本分析

首先要考虑一下多表连接时可能产生出多少种连接顺序：

• 对于两表连接，比如表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!。

有n个表进行连接，MySQL查询优化器要每一种连接顺序的成本都计算一遍么？那可是n!种连接顺序呀。其实真的是要都算一遍，不过设计MySQL的大叔们想了很多办法减少计算非常多种连接顺序的成本的方法：

• 提前结束某种顺序的成本评估

  MySQL在计算各种连接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说A、B、C三个表进行连接，已经得到连接顺序ABC是当前的最小连接成本，比方说10.0，在计算连接顺序BCA时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析BCA这个连接顺序的成本了。

• 系统变量optimizer_search_depth

  为了防止无穷无尽的分析各种连接顺序的成本，设计MySQL的大叔们提出了optimizer_search_depth系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。

• 根据某些规则压根儿就不考虑某些连接顺序

  即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以设计MySQL的大叔干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制到底是不是用这些启发式规则。

1.5调节成本常数

我们前边已经介绍了两个成本常数：

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

其实除了这两个成本常数，MySQL还支持好多呢，它们被存储到了mysql数据库（这是一个系统数据库，我们之前介绍过）的两个表中：

mysql> SHOW TABLES FROM mysql LIKE '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+
2 rows in set (0.00 sec)

我们在第一章中就说过，一条语句的执行其实是分为两层的：

• server层
• 存储引擎层

在server层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在server层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了server_cost表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了engine_cost表中。

1.5.1mysql.server_cost表

server_cost表中在server层进行的一些操作对应的成本常数，具体内容如下：

mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2018-01-20 12:03:21 | NULL    |
| disk_temptable_row_cost      |       NULL | 2018-01-20 12:03:21 | NULL    |
| key_compare_cost             |       NULL | 2018-01-20 12:03:21 | NULL    |
| memory_temptable_create_cost |       NULL | 2018-01-20 12:03:21 | NULL    |
| memory_temptable_row_cost    |       NULL | 2018-01-20 12:03:21 | NULL    |
| row_evaluate_cost            |       NULL | 2018-01-20 12:03:21 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.05 sec)

MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询（比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎。

这些成本常数在server_cost中的初始值都是NULL，意味着优化器会使用它们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需要做两个步骤：

• 对我们感兴趣的成本常数做更新操作

  比方说我们想把检测一条记录是否符合搜索条件的成本增大到0.4，那么就可以这样写更新语句：

  UPDATE mysql.server_cost 
  SET cost_value = 0.4
  WHERE cost_name = 'row_evaluate_cost';

• 让系统重新加载这个表的值。

  FLUSH OPTIMIZER_COSTS;

当然，在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把cost_value的值设置为NULL，再使用FLUSH OPTIMIZER_COSTS语句让系统重新加载它就好了。

1.5.2mysql.engine_cost表

engine_cost表表中在存储引擎层进行的一些操作对应的成本常数，具体内容如下：

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost     |       NULL | 2018-01-20 12:03:21 | NULL    |
| default     |           0 | memory_block_read_cost |       NULL | 2018-01-20 12:03:21 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+
2 rows in set (0.05 sec)

与server_cost相比，engine_cost多了两个列：

• engine_name列

  指成本常数适用的存储引擎名称。如果该值为default，意味着对应的成本常数适用于所有的存储引擎。

• device_type列

  指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在MySQL 5.7.21这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是0。

我们从engine_cost表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：

大家看完这两个成本常数的默认值是不是有些疑惑，怎么从内存中和从磁盘上读取一个块的默认成本是一样的，脑子瓦特了？这主要是因为在MySQL目前的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上，所以设计MySQL的大叔们很粗暴的认为不管这个块有没有加载到内存中，使用的成本都是1.0，不过随着MySQL的发展，等到可以准确预测哪些块在磁盘上，那些块在内存中的那一天，这两个成本常数的默认值可能会改一改吧。

在MySQL8中，io_block_read_cost的值和memory_block_read_cost的值已经不同了。

与更新server_cost表中的记录一样，我们也可以通过更新engine_cost表中的记录来更改关于存储引擎的成本常数，我们也可以通过为engine_cost表插入新记录的方式来添加只针对某种存储引擎的成本常数：

• 插入针对某个存储引擎的成本常数

  比如我们想增大InnoDB存储引擎页面I/O的成本，书写正常的插入语句即可：

  INSERT INTO mysql.engine_cost
  VALUES ('InnoDB', 0, 'io_block_read_cost', 2.0,
  CURRENT_TIMESTAMP, 'increase Innodb I/O cost');

• 让系统重新加载这个表的值。

  FLUSH OPTIMIZER_COSTS;

二、InnoDB的统计数据收集策略

我们前边唠叨查询成本的时候经常用到一些统计数据，比如通过SHOW TABLE STATUS可以看到关于表的统计数据，通过SHOW INDEX可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？本章将聚焦于InnoDB存储引擎的统计数据收集策略，看完本章大家就会明白为啥前边老说InnoDB的统计信息是不精确的估计值了。

2.1两种不同的统计数据存储方式

InnoDB提供了两种存储统计数据的方式：

• 永久性的统计数据

  这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

• 非永久性的统计数据

  这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

设计MySQL的大叔们给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。

不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

当STATS_PERSISTENT=1时，表明我们想把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明我们想把该表的统计数据临时的存储到内存中。如果我们在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值。

2.2基于磁盘的永久性统计数据

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：

mysql> SHOW TABLES FROM mysql LIKE 'innodb%stats';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.01 sec)

可以看到，这两个表都位于mysql系统数据库下边，其中：

• innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
• innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

我们下边的任务就是看一下这两个表里边都有什么以及表里的数据是如何生成的。

2.2.1innodb_table_stats

直接看一下这个innodb_table_stats表中的各个列都是干嘛的：

注意这个表的主键是(database_name,table_name)，也就是innodb_table_stats表的每条记录代表着一个表的统计信息。我们直接看一下这个表里的内容：

可以看到我们熟悉的single_table表的统计信息就对应着mysql.innodb_table_stats的第三条记录。几个重要统计信息项的值如下：

• n_rows的值是9693，表明single_table表中大约有9693条记录，注意这个数据是估计值。
• clustered_index_size的值是97，表明single_table表的聚簇索引占用97个页面，这个值是也是一个估计值。
• sum_of_other_index_sizes的值是175，表明single_table表的其他索引一共占用175个页面，这个值是也是一个估计值。

n_rows统计项的收集

为啥老强调n_rows这个统计项的值是估计值呢？现在就来揭晓答案。InnoDB统计一个表中有多少行记录的套路是这样的：

• 按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。

  可以看出来这个n_rows值精确与否取决于统计时采样的页面数量，设计MySQL的大叔很贴心的为我们准备了一个名为innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是20。

  

  我们前边说过，不过InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式：

  CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
  ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

  如果我们在创建表的语句中并没有指定STATS_SAMPLE_PAGES属性的话，将默认使用系统变量innodb_stats_persistent_sample_pages的值作为该属性的值。

clustered_index_size统计项的收集/sum_of_other_index_sizes统计项的收集

这两个统计项的收集过程如下：

• 从数据字典里找到表的各个索引对应的根页面位置。

  系统表SYS_INDEXES里存储了各个索引对应的根页面信息。

• 从根页面的Page Header里找到叶子节点段和非叶子节点段对应的Segment Header。

  在每个索引的根页面的Page Header部分都有两个字段：

  • PAGE_BTR_SEG_LEAF：表示B+树叶子段的Segment Header信息。
  • PAGE_BTR_SEG_TOP：表示B+树非叶子段的Segment Header信息。

• 从叶子节点段和非叶子节点段的Segment Header中找到这两个段对应的INODE Entry结构。

  这个是Segment Header结构：

  

• 从对应的INODE Entry结构中可以找到该段对应所有零散的页面地址以及FREE、NOT_FULL、FULL链表的基节点。

  这个是INODE Entry结构：

  

• 直接统计零散的页面有多少个，然后从那三个链表的List Length字段中读出该段占用的区的大小，每个区占用64个页，所以就可以统计出整个段占用的页面。

  这个是链表基节点的示意图：

  

• 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数，它们的和就是clustered_index_size的值，按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是sum_of_other_index_sizes的值。

这里需要大家注意一个问题，我们说一个段的数据在非常多时（超过32个页面），会以区为单位来申请空间，这里头的问题是以区为单位申请空间中有一些页可能并没有使用，但是在统计clustered_index_size和sum_of_other_index_sizes时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。

2.2.2innodb_index_stats

直接看一下这个innodb_index_stats表中的各个列都是干嘛的：

注意这个表的主键是(database_name,table_name,index_name,stat_name)，其中的stat_name是指统计项的名称，也就是说innodb_index_stats表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于single_table表的索引统计数据都有些什么：

这个结果有点儿多，正确查看这个结果的方式是这样的：

• 先查看index_name列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY索引（也就是主键）占了3条记录，idx_key_part索引占了6条记录。

• 针对index_name列相同的记录，stat_name表示针对该索引的统计项名称，stat_value展示的是该索引在该统计项上的值，stat_description指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项：

  • n_leaf_pages：表示该索引的叶子节点占用多少页面。

  • size：表示该索引共占用多少页面。

  • n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的NN长得有点儿怪呀，啥意思呢？

    其实NN可以被替换为01、02、03… 这样的数字。比如对于idx_key_part来说：

    • n_diff_pfx01表示的是统计key_part1这单单一个列不重复的值有多少。
    • n_diff_pfx02表示的是统计key_part1、key_part2这两个列组合起来不重复的值有多少。
    • n_diff_pfx03表示的是统计key_part1、key_part2、key_part3这三个列组合起来不重复的值有多少。
    • n_diff_pfx04表示的是统计key_part1、key_part2、key_part3、id这四个列组合起来不重复的值有多少。

    这里需要注意的是，对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于idx_key1来说，key1列就可能有很多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。比如上边的idx_key1有n_diff_pfx01、n_diff_pfx02两个统计项，而idx_key2却只有n_diff_pfx01一个统计项。

• 在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size列就表明了采样的页面数量是多少。

  对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数。当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对应的size列的值可能是不同的。

  • 本案例中主键id列采样的页面数量是20*1=20<聚簇索引的叶子节点数量91，因此sample_size=20，选取20个叶子节点采样页面

  • 本案例中key1列（普通二级索引）采样的页面数量是20*2=40>二级索引的叶子节点数量28，因此sample_size=28，直接采用全表扫描

  • 本案例中key2列（唯一二级索引）采样的页面数量是20*1=20>二级索引的叶子节点数量16，因此sample_size=16，直接采用全表扫描

  • 本案例中key3列（普通二级索引）采样的页面数量是20*2=40>二级索引的叶子节点数量31，因此sample_size=31，直接采用全表扫描

  • 本案例中key_part1、key_part2、keypart3列（联合索引）采样的页面数量是20*4=80>二级索引的叶子节点数量64，因此sample_size=64，直接采用全表扫描

2.2.3定期更新统计数据

随着我们不断的对表进行增删改操作，表中的数据也一直在变化，innodb_table_stats和innodb_index_stats表里的统计数据是不是也应该跟着变一变了？当然要变了，不变的话MySQL查询优化器计算的成本可就差老鼻子远了。设计MySQL的大叔提供了如下两种更新统计数据的方式：

• 开启innodb_stats_auto_recalc。

  系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据，它的默认值是ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新innodb_table_stats和innodb_index_stats表。不过自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数超过了10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。

  再一次强调，InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC属性来指明该表的统计数据存储方式：

  CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
  ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

  当STATS_AUTO_RECALC=1时，表明我们想让该表自动重新计算统计数据，当STATS_AUTO_RECALC=0时，表明不想让该表自动重新计算统计数据。如果我们在创建表时未指定STATS_AUTO_RECALC属性，那默认采用系统变量innodb_stats_auto_recalc的值作为该属性的值。

• 手动调用ANALYZE TABLE语句来更新统计信息

  如果innodb_stats_auto_recalc系统变量的值为OFF的话，我们也可以手动调用ANALYZE TABLE语句来重新计算统计数据，比如我们可以这样更新关于single_table表的统计数据：

  mysql> ANALYZE TABLE single_table;
  +------------------------+---------+----------+----------+
  | Table                  | Op      | Msg_type | Msg_text |
  +------------------------+---------+----------+----------+
  | xiaohaizi.single_table | analyze | status   | OK       |
  +------------------------+---------+----------+----------+
  1 row in set (0.08 sec)

  需要注意的是，ANALYZE TABLE语句会立即重新计算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢，请不要没事儿就运行一下ANALYZE TABLE语句，最好在业务不是很繁忙的时候再运行。

2.2.4手动更新innodb_table_stats和innodb_index_stats表

其实innodb_table_stats和innodb_index_stats表就相当于一个普通的表一样，我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。比如说我们想把single_table表关于行数的统计数据更改一下可以这么做：

• 步骤一：更新innodb_table_stats表。

  UPDATE innodb_table_stats 
      SET n_rows = 1
      WHERE table_name = 'single_table';

• 步骤二：让MySQL查询优化器重新加载我们更改过的数据。

  更新完innodb_table_stats只是单纯的修改了一个表的数据，需要让MySQL查询优化器重新加载我们更改过的数据，运行下边的命令就可以了：

  FLUSH TABLE single_table;

之后我们使用SHOW TABLE STATUS语句查看表的统计数据时就看到Rows行变为了1。

2.3基于内存的非永久性统计数据

当我们把系统变量innodb_stats_persistent的值设置为OFF时，之后创建的表的统计数据默认就都是非永久性的了，或者我们直接在创建表或修改表时设置STATS_PERSISTENT属性的值为0，那么该表的统计数据就是非永久性的了。

与永久性的统计数据不同，非永久性的统计数据采样的页面数量是由innodb_stats_transient_sample_pages控制的，这个系统变量的默认值是8。

另外，由于非永久性的统计数据经常更新，所以导致MySQL查询优化器计算查询成本的时候依赖的是经常变化的统计数据，也就会生成经常变化的执行计划，这个可能让大家有些懵逼。不过最近的MySQL版本都不咋用这种基于内存的非永久性统计数据了，所以我们也就不深入唠叨它了。

2.4innodb_stats_method的使用

我们知道索引列不重复的值的数量这个统计数据对于MySQL查询优化器十分重要，因为通过它可以计算出在索引列中平均一个值重复多少行，它的应用场景主要有两个：

• 单表查询中单点区间太多，比方说这样：

  SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');

  当IN里的参数数量过多时，采用index dive的方式直接访问B+树索引去统计每个单点区间对应的记录的数量就太耗费性能了，所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

• 连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用ref访问方法来对被驱动表进行查询，比方说这样：

  SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...;

  在真正执行对t2表的查询前，t1.comumn的值是不确定的，所以我们也不能通过index dive的方式直接访问B+树索引去统计每个单点区间对应的记录的数量，所以也只能依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

在统计索引列不重复的值的数量时，有一个比较烦的问题就是索引列中出现NULL值怎么办，比方说某个索引列的内容是这样：

+------+
| col  |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
+------+

此时计算这个col列中不重复的值的数量就有下边的分歧：

• 有的人认为NULL值代表一个未确定的值，所以设计MySQL的大叔才认为任何和NULL值做比较的表达式的值都为NULL，就是这样：

  mysql> SELECT 1 = NULL;
  +----------+
  | 1 = NULL |
  +----------+
  |     NULL |
  +----------+
  1 row in set (0.00 sec)
  
  mysql> SELECT 1 != NULL;
  +-----------+
  | 1 != NULL |
  +-----------+
  |      NULL |
  +-----------+
  1 row in set (0.00 sec)
  
  mysql> SELECT NULL = NULL;
  +-------------+
  | NULL = NULL |
  +-------------+
  |        NULL |
  +-------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT NULL != NULL;
  +--------------+
  | NULL != NULL |
  +--------------+
  |         NULL |
  +--------------+
  1 row in set (0.00 sec)

  所以每一个NULL值都是独一无二的，也就是说统计索引列不重复的值的数量时，应该把NULL值当作一个独立的值，所以col列的不重复的值的数量就是：4（分别是1、2、NULL、NULL这四个值）。

• 有的人认为其实NULL值在业务上就是代表没有，所有的NULL值代表的意义是一样的，所以col列不重复的值的数量就是：3（分别是1、2、NULL这三个值）。

• 有的人认为这NULL完全没有意义嘛，所以在统计索引列不重复的值的数量时压根儿不能把它们算进来，所以col列不重复的值的数量就是：2（分别是1、2这两个值）。

设计MySQL的大叔蛮贴心的，他们提供了一个名为innodb_stats_method的系统变量，相当于在计算某个索引列不重复值的数量时如何对待NULL值这个锅甩给了用户，这个系统变量有三个候选值：

• nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。

  如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。

• nulls_unequal：认为所有NULL值都是不相等的。

  如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。

• nulls_ignored：直接把NULL值忽略掉。

反正这个锅是甩给用户了，当你选定了innodb_stats_method值之后，优化器即使选择了不是最优的执行计划，那也跟设计MySQL的大叔们没关系了哈～ 当然对于用户的我们来说，最好不在索引列中存放NULL值才是正解。

总结：InnoDB以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。innodb_stats_persistent控制着服务器使用永久性统计数据还是非永久性统计数据，innodb_stats_persistent_sample_pages控制着永久性统计数据的采样页面数量，innodb_stats_transient_sample_pages控制着非永久性统计数据的采样页面数量，innodb_stats_auto_recalc控制着是否自动重新计算统计数据，innodb_stats_method控制着在统计某个索引列中不重复的值的数量时如何对待NULL值。

三、MySQL基于规则的优化

大家别忘了MySQL本质上是一个软件，设计MySQL的大叔并不能要求使用这个软件的人个个都是数据库高高手，就像我写这本书的时候并不能要求各位在学之前就会了里边儿的知识。

也就是说我们无法避免某些同学写一些执行起来十分耗费性能的语句。即使是这样，设计MySQL的大叔还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写（就是人家觉得你写的语句不好，自己再重写一遍）。下面详细唠叨一下一些比较重要的重写规则。

3.1条件化简

我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。为了方便大家理解，我们后边举例子的时候都使用诸如a、b、c之类的简单字母代表某个表的列名。

3.1.1移除不必要的括号

有时候表达式里有许多无用的括号，比如这样：

((a = 5 AND b = c) OR ((a > c) AND (c < 5)))

看着就很烦，优化器会把那些用不到的括号给干掉，就是这样：

(a = 5 and b = c) OR (a > c AND c < 5)

3.1.2常量传递constant_propagation

有时候某个表达式是某个列和某个常量做等值匹配，比如这样：

a = 5

当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中的a的值替换为5，比如这样：

a = 5 AND b > a

就可以被转换为：

a = 5 AND b > 5

3.1.3等值传递equality_propagation

有时候多个列之间存在等值匹配的关系，比如这样：

a = b and b = c and c = 5

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

3.1.4移除没用的条件trivial_condition_removal

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们，比如这个表达式：

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以继续被简化为

a < 1 OR a = 6

3.1.5表达式计算

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如这个：

a = 5 + 1

因为5 + 1这个表达式只包含常量，所以就会被化简成：

a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：

ABS(a) > 5

优化器是不会尝试对这些表达式进行化简的。我们前边说过只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。

3.1.6HAVING子句和WHERE子句的合并

如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。

3.1.7常量表检测

设计MySQL的大叔觉得下边这两种查询运行的特别快：

• 查询的表中一条记录没有，或者只有一条记录。

  大家有没有觉得这一条有点儿不对劲，我还没开始查表呢咋就知道这表里边有几条记录呢？哈哈，这个其实依靠的是统计数据。不过我们说过InnoDB的统计数据数据不准确，所以这一条不能用于使用InnoDB作为存储引擎的表，只能适用于使用Memory或者MyISAM存储引擎的表。

• 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。

设计MySQL的大叔觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表（英文名：constant tables）。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本，比方说这个查询语句：

SELECT * FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2 
WHERE table1.primary_key = 1;

很明显，这个查询可以使用主键和常量值的等值匹配来查询table1表，也就是在这个查询中table1表相当于常量表，在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉，也就是上边的语句会被转换成这样：

SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 
 ON table1表column1列的常量值 = table2.column2;

3.2外连接消除

我们前边说过，内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。为了故事的顺利发展，我们还是把之前介绍连接原理时用过的t1和t2表请出来，为了防止大家早就忘掉了，我们再看一下这两个表的结构：

CREATE TABLE t1 (
    m1 int, 
    n1 char(1)
) Engine=InnoDB, CHARSET=utf8;

CREATE TABLE t2 (
    m2 int, 
    n2 char(1)
) Engine=InnoDB, CHARSET=utf8;

为了唤醒大家的记忆，我们再把这两个表中的数据给展示一下：

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

我们之前说过，外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。查询效果就是这样：

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

对于上边例子中的（左）外连接来说，由于驱动表t1中m1=1, n1='a'的记录无法在被驱动表t2中找到符合ON子句条件t1.m1 = t2.m2的记录，所以就直接把这条记录加入到结果集，对应的t2表的m2和n2列的值都设置为NULL。

右（外）连接和左（外）连接其实只在驱动表的选取方式上是不同的，其余方面都是一样的，所以优化器会首先把右（外）连接查询转换成左（外）连接查询。我们后边就不再唠叨右（外）连接了。

我们知道WHERE子句的杀伤力比较大，凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了！比方说这个查询：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.01 sec)

由于指定了被驱动表t2的n2列不允许为NULL，所以上边的t1和t2表的左（外）连接查询和内连接查询是一样一样的。当然，我们也可以不用显式的指定被驱动表的某个列IS NOT NULL，只要隐含的有这个意思就行了，比方说这样：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.00 sec)

在这个例子中，我们在WHERE子句中指定了被驱动表t2的m2列等于2，也就相当于间接的指定了m2列不为NULL值，所以上边的这个左（外）连接查询其实和下边这个内连接查询是等价的：

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.00 sec)

我们把这种在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

3.3子查询优化

3.3.1子查询语法

想必大家都是妈妈生下来的吧，连孙猴子都有妈妈——石头人。怀孕妈妈肚子里的那个东东就是她的孩子，类似的，在一个查询语句里的某个位置也可以有另一个查询语句，这个出现在某个查询语句的某个位置中的查询就被称为子查询（我们也可以称它为宝宝查询哈哈），那个充当“妈妈”角色的查询也被称之为外层查询。不像人们怀孕时宝宝们都只在肚子里，子查询可以在一个外层查询的各种位置出现，比如：

• SELECT子句中

  也就是我们平时说的查询列表中，比如这样：

  mysql> SELECT (SELECT m1 FROM t1 LIMIT 1);
  +-----------------------------+
  | (SELECT m1 FROM t1 LIMIT 1) |
  +-----------------------------+
  |                           1 |
  +-----------------------------+
  1 row in set (0.00 sec)

  其中的(SELECT m1 FROM t1 LIMIT 1)就是我们唠叨的所谓的子查询。

• FROM子句中

  SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;
  +------+------+
  | m    | n    |
  +------+------+
  |    4 | c    |
  |    5 | d    |
  +------+------+
  2 rows in set (0.00 sec)

  这个例子中的子查询是：(SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2)，很特别的地方是它出现在了FROM子句中。FROM子句里边儿不是存放我们要查询的表的名称么，这里放进来一个子查询是个什么鬼？其实这里我们可以把子查询的查询结果当作是一个表，子查询后边的AS t表明这个子查询的结果就相当于一个名称为t的表，这个名叫t的表的列就是子查询结果中的列，比如例子中表t就有两个列：m列和n列。这个放在FROM子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，设计MySQL的大叔把这种由子查询结果集组成的表称之为派生表。

• WHERE或ON子句中

  把子查询放在外层查询的WHERE子句或者ON子句中可能是我们最常用的一种使用子查询的方式了，比如这样：

  mysql> SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
  +------+------+
  | m1   | n1   |
  +------+------+
  |    2 | b    |
  |    3 | c    |
  +------+------+
  2 rows in set (0.00 sec)

  这个查询表明我们想要将(SELECT m2 FROM t2)这个子查询的结果作为外层查询的IN语句参数，整个查询语句的意思就是我们想找t1表中的某些记录，这些记录的m1列的值能在t2表的m2列找到匹配的值。

• ORDER BY子句中

  虽然语法支持，但没啥子意义，不唠叨这种情况了。

• GROUP BY子句中

  同上～

按返回的结果集区分子查询

因为子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型：

• 标量子查询

  那些只返回一个单一值的子查询称之为标量子查询，比如这样：

  SELECT (SELECT m1 FROM t1 LIMIT 1);
  SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);

  这两个查询语句中的子查询都返回一个单一的值，也就是一个标量。这些标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。

• 行子查询

  顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样：

  SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

  其中的(SELECT m2, n2 FROM t2 LIMIT 1)就是一个行子查询，整条语句的含义就是要从t1表中找一些记录，这些记录的m1和n1列分别等于子查询结果中的m2和n2列。

• 列子查询

  列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如这样：

  SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

  其中的(SELECT m2 FROM t2)就是一个列子查询，表明查询出t2表的m2列的值作为外层查询IN语句的参数。

• 表子查询

  顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样：

  SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

  其中的(SELECT m2, n2 FROM t2)就是一个表子查询，这里需要和行子查询对比一下，行子查询中我们用了LIMIT 1来保证子查询的结果只有一条记录，表子查询中不需要这个限制。

按与外层查询关系来区分子查询

• 不相关子查询

  如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不相关子查询，所以也就不举例子了哈。

• 相关子查询

  如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。比如：

  SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

  例子中的子查询是(SELECT m2 FROM t2 WHERE n1 = n2)，可是这个查询中有一个搜索条件是n1 = n2，别忘了n1是表t1的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询。

子查询在布尔表达式中的使用

我们平时用子查询最多的地方就是把它作为布尔表达式的一部分来作为搜索条件用在WHERE子句或者ON子句里。所以我们这里来总结一下子查询在布尔表达式中的使用场景。

• 使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符

  我们就把这些操作符称为comparison_operator吧，所以子查询组成的布尔表达式就长这样：

  操作数 comparison_operator (子查询)

  这里的操作数可以是某个列名，或者是一个常量，或者是一个更复杂的表达式，甚至可以是另一个子查询。但是需要注意的是，这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录。比如这样：

  SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);
  SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

• [NOT] IN/ANY/SOME/ALL子查询

  对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用comparison_operator来组成布尔表达式了，MySQL通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式：

  • IN或者NOT IN

    操作数 [NOT] IN (子查询)

    这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中，比如下边的查询的意思是找出t1表中的某些记录，这些记录存在于子查询的结果集中：

    SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

  • ANY/SOME（ANY和SOME是同义词）

    操作数 comparison_operator ANY/SOME(子查询)

    这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做comparison_operator比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。比方说下边这个查询：

    SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);

    这个查询的意思就是对于t1表的某条记录的m1列的值来说，如果子查询(SELECT m2 FROM t2)的结果集中存在一个小于m1列的值，那么整个布尔表达式的值就是TRUE，否则为FALSE，也就是说只要m1列的值大于子查询结果集中最小的值，整个表达式的结果就是TRUE，所以上边的查询本质上等价于这个查询：

    SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);

    另外，=ANY相当于判断子查询结果集中是否存在某个值和给定的操作数相等，它的含义和IN是相同的。

  • ALL

    操作数 comparison_operator ALL(子查询)

    这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做comparison_operator比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。比方说下边这个查询：

    SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);

    这个查询的意思就是对于t1表的某条记录的m1列的值来说，如果子查询(SELECT m2 FROM t2)的结果集中的所有值都小于m1列的值，那么整个布尔表达式的值就是TRUE，否则为FALSE，也就是说只要m1列的值大于子查询结果集中最大的值，整个表达式的结果就是TRUE，所以上边的查询本质上等价于这个查询：

    SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2);

• EXISTS子查询

  有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个啥，可以使用把EXISTS或者NOT EXISTS放在子查询语句前边，就像这样：

  [NOT] EXISTS (子查询)

  我们举一个例子啊：

  SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

  对于子查询(SELECT 1 FROM t2)来说，我们并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填*、某个列名，或者其他啥东西都无所谓，我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM t2)这个查询中有记录，那么整个EXISTS表达式的结果就为TRUE。

子查询语法注意事项

• 子查询必须用小括号扩起来。

  不扩起来的子查询是非法的，比如这样：

  mysql> SELECT SELECT m1 FROM t1;
  
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT m1 FROM t1' at line 1

• 在SELECT子句中的子查询必须是标量子查询。

  如果子查询结果集中有多个列或者多个行，都不允许放在SELECT子句中，也就是查询列表中，比如这样就是非法的：

  mysql> SELECT (SELECT m1, n1 FROM t1);
  
  ERROR 1241 (21000): Operand should contain 1 column(s)

• 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量。

• 对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句。

  比如这样是非法的：

  mysql> SELECT * FROM t1 WHERE m1 IN (SELECT * FROM t2 LIMIT 2);
  
  ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

  为啥不合法？人家就这么规定的，不解释～ 可能以后的版本会支持吧。

• ORDER BY子句

  子查询的结果其实就相当于一个集合，集合里的值排不排序一点儿都不重要，比如下边这个语句中的ORDER BY子句简直就是画蛇添足：

  SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 ORDER BY m2);

• DISTINCT语句

  集合里的值去不去重也没啥意义，比如这样：

  SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2);

• 没有聚集函数以及HAVING子句的GROUP BY子句。

  在没有聚集函数以及HAVING子句时，GROUP BY子句就是个摆设，比如这样：

  SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2);

  对于这些冗余的语句，查询优化器在一开始就把它们给干掉了。

• 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。

  mysql> DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);
  
  ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

3.3.2子查询在MySQL中是怎么执行的

好了，关于子查询的基础语法我们用最快的速度温习了一遍，如果想了解更多语法细节，大家可以去查看一下MySQL的文档哈，现在我们就假设各位都懂了啥是个子查询了喔，接下来就要唠叨具体某种类型的子查询在MySQL中是怎么执行的了，想想就有点儿小激动呢～ 当然，为了故事的顺利发展，我们的例子也需要跟随形势鸟枪换炮，还是要祭出我们用了n遍的single_table表：

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;

为了方便，我们假设有两个表s1、s2与这个single_table表的构造是相同的，而且这两个表里边儿有10000条记录，除id列外其余的列都插入随机值。下边正式开始我们的表演。

小白们眼中子查询的执行方式

在我还是一个单纯无知的少年时，觉得子查询的执行方式是这样的：

• 如果该子查询是不相关子查询，比如下边这个查询：

  SELECT * FROM s1 
      WHERE key1 IN (SELECT common_field FROM s2);

  我年少时觉得这个查询是的执行方式是这样的：

  • 先单独执行(SELECT common_field FROM s2)这个子查询。
  • 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 IN (...)。

• 如果该子查询是相关子查询，比如下边这个查询：

  SELECT * FROM s1 
      WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);

  这个查询中的子查询中出现了s1.key2 = s2.key2这样的条件，意味着该子查询的执行依赖着外层查询的值，所以我年少时觉得这个查询的执行方式是这样的：

  • 先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。
  • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key2列的值，然后执行子查询。
  • 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
  • 再次执行第一步，获取第二条外层查询中的记录，依次类推～

其实设计MySQL的大叔想了一系列的办法来优化子查询的执行，大部分情况下这些优化措施其实挺有效的，但是保不齐有的时候马失前蹄，下边我们详细唠叨各种不同类型的子查询具体是怎么执行的。

我们下边即将唠叨的关于MySQL优化子查询的执行方式的事儿都是基于MySQL5.7这个版本的，以后版本可能有更新的优化策略！

标量子查询、行子查询的执行方式

我们经常在下边两个场景中使用到标量子查询或者行子查询：

• SELECT子句中，我们前边说过的在查询列表中的子查询必须是标量子查询。
• 子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。

对于上述两种场景中的不相关标量子查询或者行子查询来说，它们的执行方式是简单的，比方说下边这个查询语句：

SELECT * FROM s1 
    WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

它的执行方式和年少的我想的一样：

• 先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。
• 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = ...。

也就是说，对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。

对于相关的标量子查询或者行子查询来说，比如下边这个查询：

SELECT * FROM s1 WHERE 
    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

事情也和年少的我想的一样，它的执行方式就是这样的：

• 先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。
• 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询。
• 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
• 再次执行第一步，获取第二条外层查询中的记录，依次类推～

也就是说对于一开始唠叨的两种使用标量子查询以及行子查询的场景中，MySQL优化器的执行方式并没有什么新鲜的。

IN子查询优化

对于不相关的IN子查询，比如这样：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

我们最开始的感觉就是这种不相关的IN子查询和不相关的标量子查询或者行子查询是一样一样的，都是把外层查询和子查询当作两个独立的单表查询来对待，可是很遗憾的是设计MySQL的大叔为了优化IN子查询倾注了太多心血（毕竟IN子查询是我们日常生活中最常用的子查询类型），所以整个执行过程并不像我们想象的那么简单(>_<)。

其实说句老实话，对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

• 结果集太多，可能内存中都放不下～

• 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：

  • 无法有效的使用索引，只能对外层查询进行全表扫描。

  • 在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

    比如说IN子句中的参数只有两个：

    SELECT * FROM tbl_name WHERE column IN (a, b);

    这样相当于需要对tbl_name表中的每条记录判断一下它的column列是否符合column = a OR column = b。在IN子句中的参数比较少时这并不是什么问题，如果IN子句中的参数比较多时，比如这样：

    SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);

    那么这样每条记录需要判断一下它的column列是否符合column = a OR column = b OR column = c OR ...，这样性能耗费可就多了。

于是乎设计MySQL的大叔想了一个招：不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

• 该临时表的列就是子查询结果集中的列。
• 写入临时表的记录会被去重。

我们说IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个IN语句的结果并没有啥子关系，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小，更省地方～

临时表如何对记录进行去重？这不是小意思嘛，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引就好了嘛～

• 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。

  IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是超级快的。

  如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

设计MySQL的大叔把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

物化表转连接

事情到这就完了？我们还得重新审视一下最开始的那个查询语句：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询其实可以从下边两种角度来看待：

• 从表s1的角度来看待，整个查询的意思其实是：对于s1表中的每条记录来说，如果该记录的key1列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。画个图表示一下就是这样：

  

• 从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1表中找到对应的key1列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。画个图表示一下就是这样：

  

  也就是说其实上边的查询就相当于表s1和子查询物化表materialized_table进行内连接：

  SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

  转化成内连接之后就有意思了，查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。我们分析一下上述查询中使用外层查询的表s1和物化表materialized_table进行内连接的成本都是由哪几部分组成的：

  • 如果使用s1表作为驱动表的话，总查询成本由下边几个部分组成：
    • 物化子查询时需要的成本
    • 扫描s1表时的成本
    • s1表中的记录数量 × 通过m_val = xxx对materialized_table表进行单表访问的成本（我们前边说过物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。
  • 如果使用materialized_table表作为驱动表的话，总查询成本由下边几个部分组成：
    • 物化子查询时需要的成本
    • 扫描物化表时的成本
    • 物化表中的记录数量 × 通过key1 = xxx对s1表进行单表访问的成本（非常庆幸key1列上建立了索引，所以这个步骤是非常快的）。

  MySQL查询优化器会通过运算来选择上述成本更低的方案来执行查询。

将子查询转换为semi-join

虽然将子查询进行物化之后再执行查询都会有建立临时表的成本，但是不管怎么说，我们见识到了将子查询转换为连接的强大作用，设计MySQL的大叔继续开脑洞：能不能不进行物化操作直接把子查询转换为连接呢？让我们重新审视一下上边的查询语句：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

我们可以把这个查询理解成：对于s1表中的某条记录，如果我们能在s2表（准确的说是执行完WHERE s2.key3 = 'a'之后的结果集）中找到一条或多条记录，这些记录的common_field的值等于s1表记录的key1列的值，那么该条s1表的记录就会被加入到最终的结果集。这个过程其实和把s1和s2两个表连接起来的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 
    ON s1.key1 = s2.common_field 
    WHERE s2.key3 = 'a';

只不过我们不能保证对于s1表的某条记录来说，在s2表（准确的说是执行完WHERE s2.key3 = 'a'之后的结果集）中有多少条记录满足s1.key1 = s2.common_field这个条件，不过我们可以分三种情况讨论：

• 情况一：对于s1表的某条记录来说，s2表中没有任何记录满足s1.key1 = s2.common_field这个条件，那么该记录自然也不会加入到最后的结果集。
• 情况二：对于s1表的某条记录来说，s2表中有且只有1条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被加入最终的结果集。
• 情况三：对于s1表的某条记录来说，s2表中至少有2条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被多次加入最终的结果集。

对于s1表的某条记录来说，由于我们只关心s2表中是否存在记录满足s1.key1 = s2.common_field这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，我们上边所说的IN子查询和两表连接之间并不完全等价。但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以设计MySQL的大叔在这里提出了一个新概念 — 半连接（英文名：semi-join）。将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。为了让大家有更直观的感受，我们假设MySQL内部是这么改写上边的子查询的：

SELECT s1.* FROM s1 SEMI JOIN s2
    ON s1.key1 = s2.common_field
    WHERE key3 = 'a';

semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法，所以我们不需要，也不能尝试把上边这个语句放到黑框框里运行，我只是想说明一下上边的子查询在MySQL内部会被转换为类似上边语句的半连接～

概念是有了，怎么实现这种所谓的半连接呢？设计MySQL的大叔准备了好几种办法。

• Table pullout （子查询中的表上拉）

  当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如这个

  SELECT * FROM s1 
      WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');

  由于key2列是s2表的唯一二级索引列，所以我们可以直接把s2表上拉到外层查询的FROM子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：

  SELECT s1.* FROM s1 INNER JOIN s2 
      ON s1.key2 = s2.key2 
      WHERE s2.key3 = 'a';

  为啥当子查询的查询列表处只有主键或者唯一索引列时，就可以直接将子查询转换为连接查询呢？哎呀，主键或者唯一索引列中的数据本身就是不重复的嘛！所以对于同一条s1表中的记录，你不可能找到两条以上的符合s1.key2 = s2.key2的记录呀～

• DuplicateWeedout execution strategy （重复值消除）

  对于这个查询来说：

  SELECT * FROM s1 
      WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

  转换为半连接查询后，s1表中的某条记录可能在s2表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，比方说这个临时表长这样：

  CREATE TABLE tmp (
      id PRIMARY KEY
  );

  这样在执行连接查询的过程中，每当某条s1表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里，如果添加成功，说明之前这条s1表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明之前这条s1表中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除semi-join结果集中的重复值的方式称之为DuplicateWeedout。

• LooseScan execution strategy （松散扫描）

  大家看这个查询：

  SELECT * FROM s1 
      WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

  在子查询中，对于s2表的访问可以使用到key1列的索引，而恰好子查询的查询列表处就是key1列，这样在将该查询转换为半连接查询后，如果将s2作为驱动表执行查询的话，那么执行过程就是这样：

  

  如图所示，在s2表的idx_key1索引中，值为'aa'的二级索引记录一共有3条，那么只需要取第一条的值到s1表中查找s1.key3 = 'aa'的记录，如果能在s1表中找到对应的记录，那么就把对应的记录加入到结果集。依此类推，其他值相同的二级索引记录，也只需要取第一条记录的值到s1表中找匹配的记录，这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散扫描。

• Semi-join Materialization execution strategy

  我们之前介绍的先把外层查询的IN子句中的不相关子查询进行物化，然后再进行外层查询的表和物化表的连接本质上也算是一种semi-join，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询。

• FirstMatch execution strategy （首次匹配）

  FirstMatch是一种最原始的半连接执行方式，跟我们年少时认为的相关子查询的执行方式是一样一样的，就是说先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。

对于某些使用IN语句的相关子查询，比方这个查询：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3);

它也可以很方便的转为半连接，转换后的语句类似这样：

SELECT s1.* FROM s1 SEMI JOIN s2 
    ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;

然后就可以使用我们上边介绍过的DuplicateWeedout、LooseScan、FirstMatch等半连接执行策略来执行查询，当然，如果子查询的查询列表处只有主键或者唯一二级索引列，还可以直接使用table pullout的策略来执行查询，但是需要大家注意的是，由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。

semi-join的适用条件

当然，并不是所有包含IN子查询的查询语句都可以转换为semi-join，只有形如这样的查询才可以被转换为semi-join：

SELECT ... FROM outer_tables 
    WHERE expr IN (SELECT ... FROM inner_tables ...) AND ..

用文字总结一下，只有符合下边这些条件的子查询才可以被转换为semi-join：

• 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。
• 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
• 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
• 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。
• … 还有一些条件比较少见，就不唠叨啦～

不适用于semi-join的情况

对于一些不能将子查询转位semi-join的情况，典型的比如下边这几种：

• 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来

  SELECT * FROM s1 
      WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
          OR key2 > 100;

• 使用NOT IN而不是IN的情况

  SELECT * FROM s1 
      WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')

• 在SELECT子句中的IN子查询的情况

  SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1 ;

• 子查询中包含GROUP BY、HAVING或者聚集函数的情况

  SELECT * FROM s1 
      WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);

• 子查询中包含UNION的情况

  SELECT * FROM s1 WHERE key1 IN (
      SELECT common_field FROM s2 WHERE key3 = 'a' 
      UNION
      SELECT common_field FROM s2 WHERE key3 = 'b'
  );

MySQL仍然留了两手绝活来优化不能转为semi-join查询的子查询，那就是：

• 对于不相关子查询来说，可以尝试把它们物化之后再参与查询

  比如我们上边提到的这个查询：

  SELECT * FROM s1 
      WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')

  先将子查询物化，然后再判断key1是否在物化表的结果集中可以加快查询执行的速度。

  请注意这里将子查询物化之后不能转为和外层查询的表的连接，只能是先扫描s1表，然后对s1表的某条记录来说，判断该记录的key1值在不在物化表中。

• 不管子查询是相关的还是不相关的，都可以把IN子查询尝试转为EXISTS子查询

  其实对于任意一个IN子查询来说，都可以被转为EXISTS子查询，通用的例子如下：

  outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

  可以被转换为：

  EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)

  当然这个过程中有一些特殊情况，比如在outer_expr或者inner_expr值为NULL的情况下就比较特殊。但是幸运的是，我们大部分使用IN子查询的场景是把它放在WHERE或者ON子句中，而WHERE或者ON子句是不区分NULL和FALSE的，比方说：

  mysql> SELECT 1 FROM s1 WHERE NULL;
  Empty set (0.00 sec)
  
  mysql> SELECT 1 FROM s1 WHERE FALSE;
  Empty set (0.00 sec)

  所以只要我们的IN子查询是放在WHERE或者ON子句中的，那么IN -> EXISTS的转换就是没问题的。说了这么多，为啥要转换呢？这是因为不转换的话可能用不到索引，比方说下边这个查询：

  SELECT * FROM s1
      WHERE key1 IN (SELECT key3 FROM s2 where s1.common_field = s2.common_field) 
          OR key2 > 1000;

  这个查询中的子查询是一个相关子查询，而且子查询执行的时候不能使用到索引，但是将它转为EXISTS子查询后却可以使用到索引：

  SELECT * FROM s1
      WHERE EXISTS (SELECT 1 FROM s2 where s1.common_field = s2.common_field AND s2.key3 = s1.key1) 
          OR key2 > 1000;

  转为EXISTS子查询时便可以使用到s2表的idx_key3索引了。

  需要注意的是，如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为EXISTS查询。

  在MySQL5.5以及之前的版本没有引进semi-join和物化的方式优化子查询时，优化器都会把IN子查询转换为EXISTS子查询，好多同学就惊呼我明明写的是一个不相关子查询，为啥要按照执行相关子查询的方式来执行呢？所以当时好多声音都是建议大家把子查询转为连接，不过随着MySQL的发展，最近的版本中引入了非常多的子查询优化策略，大家可以稍微放心的使用子查询了，内部的转换工作优化器会为大家自动实现。

ANY/ALL子查询优化

如果ANY/ALL子查询是不相关子查询的话，它们在很多场合都能转换成我们熟悉的方式去执行，比方说：

[NOT] EXISTS子查询的执行

如果[NOT] EXISTS子查询是不相关子查询，可以先执行子查询，得出该[NOT] EXISTS子查询的结果是TRUE还是FALSE，并重写原先的查询语句，比如对这个查询来说：

SELECT * FROM s1 
    WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a') 
        OR key2 > 100;

因为这个语句里的子查询是不相关子查询，所以优化器会首先执行该子查询，假设该EXISTS子查询的结果为TRUE，那么接着优化器会重写查询为：

SELECT * FROM s1 
    WHERE TRUE OR key2 > 100;

进一步简化后就变成了：

SELECT * FROM s1 
    WHERE TRUE;

对于相关的[NOT] EXISTS子查询来说，比如这个查询：

SELECT * FROM s1 
    WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);

很不幸，这个查询只能按照我们年少时的那种执行相关子查询的方式来执行。不过如果[NOT] EXISTS子查询中如果可以使用索引的话，那查询速度也会加快不少，比如：

SELECT * FROM s1 
    WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.key1);

上边这个EXISTS子查询中可以使用idx_key1来加快查询速度。

对于派生表的优化

我们前边说过把子查询放在外层查询的FROM子句后，那么这个子查询的结果相当于一个派生表，比如下边这个查询：

SELECT * FROM  (
        SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a'
    ) AS derived_s1 WHERE d_key3 = 'a';

子查询( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')的结果就相当于一个派生表，这个表的名称是derived_s1，该表有两个列，分别是d_id和d_key3。

对于含有派生表的查询，MySQL提供了两种执行策略：

• 最容易想到的就是把派生表物化。

  我们可以将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。当然，在对派生表进行物化时，设计MySQL的大叔使用了一种称为延迟物化的策略，也就是在查询中真正使用到派生表时才回去尝试物化派生表，而不是还没开始执行查询呢就把派生表物化掉。比方说对于下边这个含有派生表的查询来说：

  SELECT * FROM (
          SELECT * FROM s1 WHERE key1 = 'a'
      ) AS derived_s1 INNER JOIN s2
      ON derived_s1.key1 = s2.key1
      WHERE s2.key2 = 1;

  如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到s2表中找出满足s2.key2 = 1的记录，如果压根儿找不到，说明参与连接的s2表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派生表了。

• 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

  我们来看这个贼简单的包含派生表的查询：

  SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;

  这个查询本质上就是想查看s1表中满足key1 = 'a'条件的的全部记录，所以和下边这个语句是等价的：

  SELECT * FROM s1 WHERE key1 = 'a';

  对于一些稍微复杂的包含派生表的语句，比如我们上边提到的那个：

  SELECT * FROM (
          SELECT * FROM s1 WHERE key1 = 'a'
      ) AS derived_s1 INNER JOIN s2
      ON derived_s1.key1 = s2.key1
      WHERE s2.key2 = 1;

  我们可以将派生表与外层查询的表合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，就像这样：

  SELECT * FROM s1 INNER JOIN s2 
      ON s1.key1 = s2.key1
      WHERE s1.key1 = 'a' AND s2.key2 = 1;

  这样通过将外层查询和派生表合并的方式成功的消除了派生表，也就意味着我们没必要再付出创建和访问临时表的成本了。可是并不是所有带有派生表的查询都能被成功的和外层查询合并，当派生表中有这些语句就不可以和外层查询合并：

  • 聚集函数，比如MAX()、MIN()、SUM()啥的
  • DISTINCT
  • GROUP BY
  • HAVING
  • LIMIT
  • UNION 或者 UNION ALL
  • 派生表对应的子查询的SELECT子句中含有另一个子查询
  • … 还有些不常用的情况就不多说了哈～

所以MySQL在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。