MySQL调优之关联查询、子查询优化

我们准备如下两个表，并插入数据。

#分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

【1】左外连接

首先我们分析SQL如下，type为驱动表，book为被驱动表。

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

每次从type中获取一条数据然后后book中的数据进行对比（全表扫描），这个过程要要重复20次（type 表有20条数据）。

这里可以看到，type均为all。另外还可以看到MySQL帮我们做了一个优化，使用了join buffer进行缓存。

我们为被驱动表 book.card 添加索引优化

CREATE INDEX Y ON book(card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

这里能够看到，虽然type表仍旧是要处理20次，但是拿着type的数据去book中寻找时，走的是索引。对于B+树来讲，其时间复杂度为logN，相比前面的全表扫描要快很多。

也就是对于左外连接来讲，如果只能添加一个索引，那么一定添加到被驱动表上。

---

当然，给type的card页创建索引也是可以的。

CREATE INDEX X ON `type`(card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

---

如果索引只加在了驱动表（左表）呢？

DROP INDEX Y ON book;

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

可以看到，同样使用了join buffer。而对于驱动表来讲，即使用到了索引也要做一个整体的遍历（无非这时走的是索引文件）。而被驱动表没有索引，那么性能会相对较慢。

如下图所示，从其查询成本我们也可以看到显著区别。

结论： 左(外)连接时，索引加在右表的连接字段。left join用于确定如何从右表搜索行，左表一定都有。同理，右(外)连接时，索引创建在左表的连接字段。该连接字段在两个表中的数据类型保持一致。

此外，从上面Using where; Using join buffer (Block Nested Loop)我们也可以想到，如果服务器允许，那么join buffer给一个较大的容量是有助于提升性能的。

【2】内连接INNER JOIN

我们去掉索引，然后查看执行计划。

DROP INDEX X ON `type`;

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

我们给被驱动表 book.card 添加索引

CREATE INDEX Y ON book(card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

---

我们再给驱动表type添加索引

CREATE INDEX X ON `type`(card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

可以看到这里二者均用到了索引。需要说明的是，这时type和book上下次序可能转换，也就是说 对于inner join来讲，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的 。

---

那如果book.card没有索引，type.card 有索引呢？

DROP INDEX Y ON book;

EXPLAIN  SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

可以看到book作为了驱动表，type作为了被驱动表。即，对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。

---

如果两个表数据量不一致呢？比如这里我们type为40条，book为20条。

EXPLAIN  SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

结论： 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，即“小表驱动大表”。

---

【3】join的底层原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

① 驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表。一个很明确的判断依据就是explain的执行计划，如下所示，type在上方，那么type为驱动表，book在下方， book为被驱动表。

对于内连接来说select * from A join B on ...，A不一定为驱动表。优化器会根据查询语句进行优化，决定先查哪张表。先查询的表就是驱动表，反之就是被驱动表。

对于外连接来说，select * from A left join B on ... 。通常大家会认为A就是驱动表，B就是被驱动表。不过这不是一定的，我们可以验证。

准备如下数据：

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;

INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

测试1

EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) 
WHERE (a.f2=b.f2);

如上图所示，A有索引，B没有索引。查询优化器决定以B表驱动A表。

---

测试2

EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) 
AND (a.f2=b.f2);

没有用到索引，且A表驱动B表。

---

测试3

EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) 
WHERE (a.f2=b.f2);

可以看到，其和测试1效果是一样的。

② Simple Nested-Loop Join(简单嵌套循环连接)

该算法相当简单，从表A中取出一条数据，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断。

可以看到这种方式效率是非常低的，以上述表A数量100条，表B数据1000条计算，则A*B=10万次。开销统计如下：

开销统计	SNLJ
外表扫描次数	1
内表扫描次数	A
读取记录数	A+B*A
JOIN比较次数	B*A
回表读取记录次数	0

当前MySQL肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nesed-Loop Join优化算法。

③ Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故MySQL优化器都倾向于使用记录数少的表作为驱动表（外表）。

开销统计	SNLJ	INLJ
外表扫描次数	1	1
内表扫描次数	A	0
读取记录数	A+B*A	A+B(match)
JOIN比较次数	B*A	A*Index(Height)
回表读取记录次数	0	B(match)(if possible)

如果被驱动表加索引，效率是非常高的。但如果索引不是主键索引，还需要进行一次回表查询。所以如果被驱动表的索引是主键索引，效率会更高。

④ Block Nested-Loop Join(块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join。如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中。然后再从驱动表中取一条与其匹配，匹配结束后清除内存。然后再从驱动表中加载一条记录，然后再把被驱动表的记录加载到内存中进行匹配…这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区。将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表。被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

注意，这里换成的不只是关联表的列，select后面的列也会缓存起来。在一个有N个join关联的SQL中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数	1	1	1
内表扫描次数	A	0	A*used_column_size/join_buffer_size+1
读取记录数	A+B*A	A+B(match)	A+B*(A*used_column_size/join_buffer_size)
JOIN比较次数	B*A	A*Index(Height)	B*A
回表读取记录次数	0	B(match)(if possible)	0

⑤ 参数设置

block_nested_loop

SHOW VARIABLES LIKE '%optimizer_switch%';

如下所示，这里我们可以看到block_nested_loop=on,也就是默认是开启的。

index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on

---

join_buffer_size

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下是256K。

SHOW VARIABLES LIKE '%join_buffer_size%';

⑥ Join小结

1.整体效率比较：INLJ > BNLJ > SNLJ
2.永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是：表行数*每行大小）。

# 推荐
select t1.b,t2.* from t1 straight_join t2 on(t1.b=t2.b) 
where t2.id<=100;

#不推荐
select t1.b,t2.* from t2 straight_join t1 on(t1.b=t2.b)
where t2.id<=100;

3.为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
4.增大join_buffer_size的大小（一次缓存的数据越多，那么内存表的扫描次数就越少）。
5.减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）。

【4】Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join，默认都会使用hash join 。

Nested Loop

对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。

Hash Join 是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用 join key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与hash表匹配的行。

• 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
• 在表很大的情况下并不能完全放入内存，这时优化器会将它分隔成若干不同的分区。不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高IO的性能。
• 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是join的重型升降机。Hash join只能应用于等值连接，这是由Hash的特点决定的。

	Nested Loop	Hash Join
使用条件	任何条件	等值连接(=)
资源	CPU 磁盘IO	内存 临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果	当缺乏索引或者索引提交模糊时，Hash Join比Neste Loop有效。在数据仓库环境下，如果表的记录数多，效率高
缺点	当索引丢失或者查询条件限制不够时，效率很低。当表的记录数多时，效率低。	为建立哈希表，需要大量内存，第一次的结果返回较慢。

【5】子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行select语句的嵌套查询。即一个select查询的结果作为另一个select语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是子查询的执行效率不高。原因如下：

• 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
• 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
• 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（join）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

① 查询学生表中是班长的学生信息

使用子查询

#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询班长的信息
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

使用多表查询：推荐

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

对比二者的执行计划，可以看到使用子查询明显建立了临时表。故整体成本关联查询是优于子查询的。

② 查询不为班长的学生信息

使用子查询

EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
			SELECT monitor FROM class b 
			WHERE monitor IS NOT NULL) 

使用多表查询

EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

对比二者的执行计划，子查询采用了SUBQUERY,而关联查询均是SIMPLE。整体成本关联查询是优于子查询的。

结论：尽量不要使用not int或者not exists，用left join XXX on XX where XX is null替代。

注意：这里说的整体成本是考虑了临时表的创建、回收中消耗的CPU、IO资源。如果单纯从查询时间上来讲，不同数量级二者效果是不一样的。并没有说多表关联查询在查询时间上一定快于子查询。