MySQL中join详解

大家对join应该都不会陌生，join可以将两个表连接起来。

join流程详解

join 是指 将两个表连接起来，两个表分别为 驱动表 和 被驱动表。

我们拿下面的这个sql举例，

select t1.id,t2.id  from t1 inner join t2 on t1.id = t2.id  where t1.id > 10;

t1和t2 都对 id 建立了索引，我们假设 t1 是驱动表，t2是被驱动表。

join流程如下：
1、MySQL每次从t1中读取一行满足过滤条件t1.id>10的记录，如果有索引的话，就利用索引快速定位到表t1中符合t1.id>10的记录。如果没有索引的话，就从头到尾遍历，慢慢的查出表t1中符合t1.id>10的记录。

2、当从t1中读取到一条记录r1后，就根据连接的条件，也就是 on关键字后的条件，去t2中对应的t2中的记录，将t1的记录和t2的记录组合返回给客户端.

具体的说就是，当从t1中得到一条记录r1的时候，需要根据连接条件 t1.id = t2.id，也就是去t2中查询是否存在记录r2 ，其中r2.id = r1.id。
也就是执行 select t2.id from t2 where t2.id = r1.id

如果有t2的id有索引的话，那么查询速度是很快的，否则就只能全盘扫描了。

3、就这样，依次的读取t1中符合条件的记录，然后查询t2，直到t1中的记录读取完毕

从上述流程可以看出，join的大致过程就是从驱动表中挨个读取符合条件的记录，然后根据连接条件到被驱动表中找出符合条件的记录，将其组合到一起返回给客户端。

驱动表和被驱动表的选择

驱动表和被驱动表的选择关乎到整个join的查询效率，如何正确的选取驱动表和被驱动表呢？

其实MySQL会自动优化，选出合适的驱动表和被驱动表，我们看一下MySQL的选取规则。

1、Index Nested-Lopp Join

优先选取对连接字段有索引的表作为被驱动表。

因为驱动表中的记录需要到被驱动表中查询，被驱动表中关于连接条件的字段最好有索引。

有索引的查询叫做 Index Nested-Loop Join,简称NLJ。

如果被驱动表没有索引的话，会对被驱动表做全盘扫描，全盘扫描效率很低。

如果被驱动表有索引的话，可以通过索引快速定位到对应的记录。

比如拿下面的查询举例：

select t1.id,t2.id  from t1 inner join t2 on t1.id = t2.id  where t1.id > 10;

假设t1为驱动表，t2是被驱动表，当从t1中得到一条记录r1的时候，需要根据连接条件 t1.id = t2.id，也就是去t2中查询是否存在记录r2 ，其中r2.id = r1.id。
也就是执行 select t2.id from t2 where t2.id = r1.id

如果有t2的id有索引的话，那么查询速度是很快的，否则就只能全盘扫描了。

2、如果两个表都有连接字段的索引，那么就让小表当驱动表，大表当被驱动表

这里的表的规模不是指的是表的原生规模，而是满足过滤条件后的表的规模，

因为被驱动表可以通过索引查询，相当于只扫描一行记录。

假如现在有两个表t1和t2，t1的记录数为100，t2为1000。

如果t1当驱动表，那么只要扫描100 + 100 = 200；
如果t2当驱动表，那么需要扫描1000 + 1000 = 2000

所以驱动表应该选择小表。主观的解释就是大表中的若干数据在小表中是没有对应的数据的。但是大表无法判断出这些数据具体的信息，还是需要一行一行的将数据取出，然后到小表中查询。所以那些在大表中存在，但是小表中不存在的数据，被取出然后到小表中查询的时候是无法从小表中得到任何的数据的，只会白白的浪费内存和比较次数。

3、Block Nested-Loop Join

如果两个表关于连接字段都没有索引，那么MySQL会将驱动表中的数据全部读取到内存中，然后依次读取被驱动表中的记录，将被驱动表中的记录去和内存中的驱动表做比对，如果满足连接条件，就将对应的记录拼接返回。

这种join叫做 Block Nested-Loop Join ，简称BNL

为什么要将驱动表中的数据读取到内存呢？

因为磁盘IO是个非常低效的操作，所以将对应的驱动表的所有数据都读取到内存中，直接和内存比对。

我们来实际举个例子，假如t1有100条记录，t2有1000条记录。

如果每次都读取磁盘，需要对t1先后读取100次，对于t1中的每条记录都需要遍历t2中的所有记录，也就是1000条，总共需要100*1000 = 10W次磁盘IO。

如果将t1的记录放到内存中，将t2中的1000条依次读取出来，然后和内存中的t1数据比对，只需要100+1000 = 1100次磁盘IO。

如果内存太小，放不下驱动表的数据呢？

当被驱动表无法使用索引的时候，MySQL中会为每个线程分配一个join_buffer ,用来存放驱动表的数据，如果驱动表的数据量超过join_buffer呢？

MySQL会将驱动表中的数据分为多段依次放到内存中，这就是为什么叫做Block Nested-Loop Join的原因

将被驱动表中的数据 逐行取出，和内存中的驱动表的不完整的一段数据进行比对。

当被驱动表中的数据比对完毕后，就将内存清空，然后将驱动表中的下一段数据取出重新放到内存中，重新比对。

被驱动表的数据要 和 内存中的驱动表的内存端 比对多次，需要先后多次的重复 将 被驱动表中的数据从磁盘上读取到内存中，虽然比较的次数没变，但是多了重复的磁盘IO。

所以，一定要将 小表 作为驱动表，这样就能减少分段的次数，甚至不分段。

NLJ优化

Multi-Range Read
如果一条记录符合连接条件后，如果select 需要查询*或者很多字段的时候，就需要回表查询了。

一般来说，当查询到一个符合的id后，就会回表，然后将数据返回给客户段。

但是这样前后回表 对应的id不一定是有序的，因为是根据索引查询的，这时只有连接字段是有序的，id是乱序的。

这样就是对磁盘进行随机IO，磁盘随机IO的性能是比较差的，所以Multi-Range Read 会将若干个id进行缓存到join_buffer中，对id进行排序，进行顺序的磁盘IO，提高查询效率。

BNL优化

对BNL查询，可以对表加索引来解决，将BNL转化为NLJ的查询方法。

但是，如果一个表有这些特征的话，就不适合建立索引
1、表的数据规模很大
2、查询的次数很少
3、过滤后的join数据很少

如果为这样的表建立索引，是比较浪费空间，但是每次查询的时候等待的时间又很长。

怎么办呢？

嘿嘿，利用临时表
1、将表中满足过滤条件的数据放到一个临时表中
2、对临时表建立索引
3、将驱动表和临时表进行join

这样的话，就可以提高BNL的join效率了。

总结

1、MySQL优先会将索引表当作被驱动表
2、如果都两个表都有索引，会将小表当作驱动表
3、如果两个表都没有索引，会将小表当作驱动表，将驱动表的数据全部加载到内存中，然后一行一行的读取被驱动表的数据，和内存中的驱动表数据进行比对
4、如果驱动表的数据量超过了join_buffer的大小，会将驱动表分为多个段，一次只加载一段，然后一行一行的读取被驱动表的数据，进行比对。
一个段比对完后，将join_buffer清空，继续下一个段的比对，但是还是要重新的从磁盘中加载被驱动表，虽然比较的次数没变，却重复的将被驱动表的数据从磁盘中读取到内存中，多了很多磁盘IO。

无论什么情况，都会将小表当作驱动表，小表指的是满足过滤条件后的表的数量，而不是表的原始规模。