MySQL 查询优化

Question

这部分其实就是搞懂对于我们写的 SQL 语句，MySQL 是怎么执行的，比如是会全表扫描，还是走聚簇索引，还是二级索引，等等。为了了解查询优化，首先我们需要来看两部分，在进行单表查询时有哪些方法，以及表连接是什么原理。

单表查询

也就是 select * from users where email = "XXX" 这样的语句，对一张表进行查询。有以下几种方式：

1. const：通过主键或者 unique 键与常数的等值比较来定位一条记录。这种访问方法是非常快的，因为可以直接通过索引B+树唯一确定一条记录（或者找不到记录）。
2. ref：对不是 unique 的二级索引进行等值比较（包括NULL），可能会定位到多条记录
3. ref_or_null：对某个二级索引，既有进行等值比较的条件，又有对其等于NULL的比较条件，这时会分别找出来id，再一起回表。比如 select * from users where email = "XXX" or email = NULL
4. range：对聚簇索引或者二级索引进行范围查找，比如 key1 in (?)，key1 > ? 这种。
5. index：不需要回表，直接在二级索引的 B+ 树上就能取到所需的信息。比如 select key1, key2 from users where key2 = ?（假设有联合索引 idx_key1_key2），这种情况下虽然key2不是索引，但仍然可以只扫描这个联合索引的 B+ 树，成本比全表扫描小的多
6. all：全表扫描

但是我觉得，上面这几种方式没必要记住，因为拿到一个简单的单表查询语句，我们一般情况下自己就能分析出它会怎么被执行。接下来可以看一下更复杂的情况，索引合并（index merge），也就是可以在一次查询中用到多个二级索引。索引合并有三种情况。

1. Intersection

第一种是Intersection，也就是求交集，比如where条件里用到了两个二级索引的等值匹配的查询条件，并且是用AND连接的，那么就可以分别在这两个索引的B+树上找到符合条件的主键值，然后计算这两组主键值的交集，最后进行回表。

比如 select * from users where key1 = ? and key2 = ?，这个语句就可以分别在key1和key2的索引B+树中过滤出符合条件的记录，然后计算出主键值的交集，再进行回表操作取出完整记录

对于Intersection方法，有两个地方需要注意，第一是，为什么不通过key1过滤出结果集，回表之后再根据key2的条件过滤呢？原因就在于成本，读取二级索引的操作是顺序IO，而回表操作是随机IO，如果根据key1过滤出的结果非常多，这时性能损耗就比较大了。

第二个需要注意的是，MySQL在求主键值的交集时，需要两部分（或者多部分）的主键值都是有序排列的，如果使用某个二级索引过滤出来的主键值不是有序排列的，也就意味着无法使用Intersection的方法（因为还需要先排序，性能降低）。比如说：

select * from users where key1 = ? and key2 > ?

这个语句通过key2过滤出来的主键值就不是排好序的，因此不能使用Intersection方法。

再比如如果有联合索引idx_part1_part2：

select * from users where key1 = ? and part1 = ?

根据part1过滤出来的条件也不是按照主键值排好序的，因为联合索引的叶子节点上，是先按照part1排序，再按照part2排序，最后按照主键值排序。所以这个语句也不能使用Intersection方法。

根据主键值必须是有序排列的原理，我们可以知道如果查询条件中对主键列进行范围匹配，还是可以用到Intersection方法：

select * from users where key1 = ? and id > ?

1. Union 和上面情况类似，不过这次是求并集，对应的是where查询条件用OR连接起来的情况。其他要求和Intersection是类似的。
2. Sort-Union Union的条件和Intersection一样苛刻，需要根据二级索引过滤出来的主键值是有序的。而Sort-Union就没那么苛刻，即使过滤出来的主键值不是有序的，也可以先对其进行排序，然后再求并集。

当然，只有Sort-Union方法而并没有Sort-Intersection方法。因为Union适用的场景是过滤出来的主键集合较小，因此排序也很快。而Intersection一般用于过滤出来的主键较多，求交集之后能有效减少回表的情况，这时如果加入Sort-Intersection，排序的开销也会很大。

以上就是索引合并的三种方法，当然，上面说的什么过滤出来的主键值有序之类的，只是使用索引合并的必要条件，不是充分条件，具体是否使用索引合并，还是看优化器判断如何执行成本更低。

表连接的原理

我们一般使用的都是内连接和外连接，比如：

select * from users inner join orders on users.id = orders.user_id where orders.pay_type = 2

select * from users left join orders on users.id = orders.user_id where orders.pay_type = 2

使用内连接，则只有两个表中都有符合条件的记录时，才会将记录加入到最后的结果集中；外连接分为左外连接和右外连接，左外连接会选择左侧的表为驱动表，这时即使在被驱动表中没有匹配的记录，这条记录也会加入结果集。

在连接的原理中，首先有一个驱动表和被驱动表的概念。对于内连接，MySQL可以根据执行的效率选择任一张表作为驱动表。对于左外连接，则是左侧的表为驱动表。

MySQL会首先根据前面刚介绍过的单表访问方法，确定对驱动表的访问方法，查询出驱动表的结果集。第二步，针对从驱动表中查询出的每一条记录，到被驱动表中去查找匹配的记录。也就是说，在连接的查询中，驱动表只需要访问一次，被驱动表可能需要访问多次。

上面提到的连接执行方式称为嵌套循环连接（Nested-Loop Join），也就是查询被驱动表的结果集类似一个循环，只能根据驱动表查询出的结果集一条一条记录的到被驱动表中进行匹配。当然，如果驱动表查询出的结果集只有几条记录，这样做也没问题，但现实生活中一般都有很多记录，因此这种执行方式需要进行优化。

优化方式就是，不要一次只在被驱动表中匹配一条记录，而是一次匹配一批。所以MySQL的设计者提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。这种方式称为使用基于块的嵌套循环连接（Block Nested-Loop Join，BNJ）。

可以看出BNJ算法还是不够优化，如果被驱动表的连接条件使用到了被驱动表的索引，那么MySQL会使用 Batched Key Access Joins（BKA），BKA算法也是使用join buffer，不过会一次性将join buffer中的记录作为查询条件传入被驱动表进行查询。可以参考官方文档

子查询的执行方式

这里主要介绍IN子查询的执行方式，比如：

select * from users where id in (select user_id from orders where pay_type = 2)

我们可以感觉得到，这种写法和连接的写法非常像：

select users.* from users inner join orders on users.id = orders.user_id where orders.pay_type = 2

当然，两者的区别就在于，在内连接的情况下，如果对于 users 表中的某个 id，orders 表中有多条 user_id 记录，那么 users 表中的这个记录会被多次加入结果集；而在子查询的写法中这个记录本来应该只在结果集中出现一次。

虽然不能直接转换为连接，但是这种转换为连接的想法又非常诱人。试想一下，如果直接执行上面的子查询，我们只能先单独执行子查询的部分，得到结果集，然后再执行外层查询，这样的问题是如果子查询的结果集太大，就会有下面的问题：

• 子查询的临时结果集太大，内存都放不下
• 外层查询IN中包含的参数过多，性能降低

因此，MySQL针对子查询，提出了 semi-join（半连接）的概念，将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录（也就是转换为连接的同时，避免了上面提到的多次加入结果集的情况）。将子查询转换为半连接之后就类似于下面的语句（当然是不能真正执行的）：

select users.* from users semi join orders on users.id = orders.user_id where orders.pay_type = 2

就像单表查询一样，半连接的执行也分为一些不同的情况，下面一一来看下，参考子查询官方文档：

Table Pullout（表上拉）

这种情况适用于子查询中的表在连接条件处的列是该表的唯一索引或者主键，这样一来就避免了上面提到的外层查询中的表的某个记录被重复加入结果集的情况。这样查询语句相当于变为inner join

DuplicateWeedout execution strategy （重复值消除）

那对于一般情况下，子查询中的表确实可能有多条记录，导致外层查询中的表的某个记录会被重复加入结果集的时候，MySQL会使用 DuplicateWeedout，也就是为了消除重复，建立一个临时表，用来存放外层查询的主键，这样便可以使用临时表的主键唯一的特性进行去重了。

LooseScan execution strategy （松散扫描）（略）

Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.

Semi-join Materialization execution strategy

先将子查询的结果集进行物化（Materialization），也就是写入一个临时表里，该临时表的列就是子查询的查询结果中的列，并且会被去重；然后再将整个子查询语句转换为连接查询。临时表一般比较小，所以会使用基于内存的 Memory 引擎，如果超过了某个系统变量，则会转为基于磁盘的存储引擎。

FirstMatch execution strategy （首次匹配）

也就是先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。

在MySQL中，要将子查询转为 semi-join，必须满足一定条件，而下面这些情况下都不能转为semi-join：

• 外层查询的where条件中有其他条件与IN子查询用OR进行连接
• 使用 not in 子查询而不是 in 子查询
• 子查询中包含了 union
• 子查询中有 group by 或者 having 子句

而对于不能使用 semi-join 的情况，MySQL 仍然会进行优化，有两种方法：

• 进行 IN 到 EXIST 转换（略）
• 对于不相关子查询，先进行物化之后再查询。但这时需要注意的是查询方式，比如 SELECT * FROM s1
WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a') 这个语句，只能是先扫描s1表，然后对s1表的某条记录来说，判断该记录的 key1 值在不在物化表中。