Mysql深度讲解 – Join语句

前言

到目前为止已经基本上介绍了InnoDB的原理，索引的原理，查询优化器的各种操作和内部逻辑。后面这部分就是针对各种语句的讲解和优化策略，本篇如名字一样将会侧重于Join语句的介绍于优化。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

Join语法

简单来说Join一共有三种连接形式：内连接、左连接和右连接，其中左连接和右连接可以合并称为外连接。

内连接(inner join)有三种写法：
select * from t1 join t2 on t1.a = t2.a;
select * from t1 inner join t2 on t1.a = t2.a;
select * from t1 cross join t2 on t1.a = t2.a;
左连接：
select * from t1 left join t2 on t1.a = t2.a;
右连接
select * from t1 right join t2 on t1.a = t2.a;

Join的原理

不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表。对于内连接来说，驱动表和被驱动表，而外连接的驱动表是固定的，也就是说左连接的驱动表就是左边的表，右连接的驱动表就是右边的表。
所以连接的大致原理是：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

如果用java的伪码来表示两个表Join的话：
//遍历table1中满足条件的结果集中的所有记录
foreach row in table1{ 
	//提取出table1里的某条记录，然后遍历table2中满足条件的结果集中的所有记录
	foreach row in table2{
		//判断是否符合Join条件
	}
}

嵌套连接

上面的伪码就是一个嵌套循环，类似于Java中嵌套遍历Collection集合，然后对比外部集合每一条数据和内部集合的每一条数据。驱动表就属于最外层的集合，被驱动表会被访问多次，而访问的次数取决于对启动表执行单表查询后产生的结果集中的记录数。这种方式被称为嵌套循环连接(Nested-Loop Join)，嵌套的层次越多查询效率越低，同时也是最简单的一种查询算法。
比如对于sql语句：

select * from t1 join t2 on t1.a = t2.a where t1.b in (1,2);

会被拆分并首先执行下面这条语句：

select * from t1 where t1.b in (1,2);  -- 假设查出来的结果如下

a	b	c	d	e
4	2	1	7	g
9	1	1	1	c
12	1	2	4	a
15	2	2	5	e

得到第一层的结果集以后，再分别执行下面的sql语句。

select * from t2 where t2.a = 4;
select * from t2 where t2.a = 9;
select * from t2 where t2.a = 12;
select * from t2 where t2.a = 15;

但是考虑到这些步骤会被转化为单表查询，其实这些都是可以利用索引的，因此索引也是帮助Join优化查询效率的好东西。

嵌套连接的缺点

上面所说的join原理当驱动表查询的结果集并不是很大的时候是比较有效的。一旦驱动表查询的结果集很大，就会对被驱动表进行大量的访问，因为被驱动表相当于内层循环，到底要循环多少次是根据外层循环的数据量决定的。理论上外层有1万条数据就得循环1万次；有10万条数据就得循环10万次。

现实往往更加残酷，因为扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。如果表过大，内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以不得不把前边的记录从内存中释放掉。

然而当我们使用嵌套循环连接算法连接两表的过程中，被驱动表会被访问很多次。如果被驱动表中的数据也非常多并且无法使用索引，或者索引效率不高，就意味着需要从磁盘上多次读取被驱动表。这样做IO的代价就很不可以接受了，所以要想办法减少被驱动表的访问次数。有一个显而易见的办法就是基于一块一块的读取对比而不是一条条的对比。

基于块的嵌套循环连接

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录 只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记 录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从 磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中 的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

Mysql中有一个叫做join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条 驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O 代价。

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成 连接操作了。这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接(Block Nested-Loop Join)算法。这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也 就是256KB），最小可以设置为128 Byte。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高 的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的 列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样可以在join buffer中放置更多的记录。

外连接消除

上面已经说过所谓的外连接，就是左连接或者右连接。这样的sql有没有办法优化呢？其实是可以的，其优化的方式就是想办法优化为内连接。因为内连接的驱动表和被驱动表的位置可以相互转换，而左连接和右连接的驱动表和被驱动表是固定的。这就导致内 连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。也就是说当两个表要进行join语句操作的时候，Mysql查询优化器会帮助用户去选择哪个表更适合作为驱动表，哪个表更适合作为被驱动表。所以对于外连接，要想办法优化为内连接让Mysql帮助我们得到一个最优的查询语句。

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

比如下面这样一个sql语句，如何去优化呢？

select * from t1 left join t2 on t1.a = t2.a where t1.a in (1,10);

a	b	c	d	e	a	b	c	d	e
1	6	7	4	d	1	2	2	2	d
10	5	5	5	ss	NULL	NULL	NULL	NULL	NULL

可以看到当前这个查询导致了表里很多数据为NULL，这个结果是由于t1中有a=10这个数据，但是t2中没有，因此第二行相当于垃圾数据。那么inner join是什么结果呢？

select * from t1 join t2 on t1.a = t2.a where t1.a in (1, 10);

a	b	c	d	e	a	b	c	d	e
1	6	7	4	d	1	2	2	2	d

那么优化的逻辑就是把NULL值全部移除掉就可以了。

select * from t1 left join t2 on t1.a = t2.a where t1.a in (1, 10) and t2.b is not null;

a	b	c	d	e	a	b	c	d	e
1	6	7	4	d	1	2	2	2	d

可以用explain语句对比一下优化前和优化后的不同之处。

explain select * from t1 left join t2 on t1.a = t2.a where t1.a in (1, 10)。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY	PRIMARY	4	NULL	2	100	Using where
1	SIMPLE	t2	NULL	eq_ref	PRIMARY	PRIMARY	4	world.t1.a	1	100	NULL

explain select * from t1 left join t2 on t1.a = t2.a where t1.a in (1, 10) and t2.b is not null;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY	PRIMARY	4	NULL	2	100	Using where
1	SIMPLE	t2	NULL	eq_ref	PRIMARY	PRIMARY	4	world.t1.a	1	87.5	Using where

虽然驱动表没有变，但是过滤内容的确实减少了一些。但是其实如果查询优化器探测到t2比t1更适合做驱动表的话，是会将二者交换的，因此无论如何执行都会得到一样的结果。基本上谁是驱动表会遵循一个原则，那就是：小表驱动大表。也就是数据行较少的表作为驱动表，数据行多的表作为被驱动表，用来减少循环的次数。当然这里只是给大家普及一下外连接消除的知识点，说明Mysql的查询优化器有这样一个功能，实际的业务还需要参考实际的情况来分析。

总结

基本上到此，内连接和外连接的内容就告一段落了。本篇主要讲解了Join的原理，嵌套链接，块嵌套连接是如何做的，以及如何消除外连接进行一个Join的优化。