大家对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。

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