join关键字
当业务需要从多个数据表中读取数据时,可以使用SQL语句中的连接(JOIN),在两个或多个数据表中查询数据。
JOIN 按照功能可分为三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录;
- LEFT JOIN(左连接):获取左表中的所有记录,即使在右表没有对应匹配的记录;
- RIGHT JOIN(右连接):与 LEFT JOIN 相反,用于获取右表中的所有记录,即使左表没有对应匹配的记录。
但是当有多张表的时候,驱动表该如何选择?
首先建一张表用来演示。
CREATE TABLE `t2` (
`id` int(11) NOTNULL,
`a` int(11) DEFAULTNULL,
`b` int(11) DEFAULTNULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
CREATE TABLE `t1` LIKE `t2`;
假设插入的数据是一一对应的以方面计算。
提示:文中用到的straight_join完全等同于inner join,但是从左到右实现强制多表的载入顺序,不同于inner join会进行优化。
IndexNested-Loop Join
对于SQLselect * from t1 straight_join t2 on (t1.a=t2.a);
有以下执行流程:
- 从表t1中读入一行数据 R;
- 从数据行R中,取出a字段到表t2里去查找;
- 取出表t2中满足条件的行,跟R组成一行,作为结果集的一部分;
- 重复执行步骤1到3,直到表t1的末尾循环结束。
我们称之为IndexNested-Loop Join算法,简称NLJ。
在这个流程里:
- 对驱动表t1做了全表扫描,这个过程需要扫描N行;
- 对于每一行R,根据a字段去表t2查找,走的是树搜索过程。由于我们构造的数据都是一一对应的,因此每次的搜索过程都只扫描一行,也是总共扫描M行;
- 所以整个执行流程,总扫描行数是N+M。
假设被驱动表的行数是M。每次在被驱动表查一行数据,要先搜索索引a,再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数,记为log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。
当驱动表的行数是N,执行过程就要扫描驱动表N行,然后对于每一行,到被驱动表上匹配一次。因此整个执行过程,近似复杂度是 N+N2log M,很明显N对扫描行数的影响更大,因此应该让小表来做驱动表。(N扩大1000倍的话,扫描行数就会扩大1000倍;而M扩大1000倍,扫描行数扩大不到10倍。)
Block Nested-Loop Join
SQLselect * from t1 straight_join t2 on (t1.a=t2.b);
由于表t2的字段b上没有索引,因此搜索时,每次到t2去匹配的时候,就要做一次全表扫描。假设驱动表行数是N,被驱动表行数是M,那就需要扫描行数是N*M。
我们将这个算法称为Simple Nested-Loop Join,但是MySQL没有使用这个算法,而是使用了另一个叫作BlockNested-Loop Join的算法,简称BNL。
这时候,被驱动表上没有可用的索引,算法的流程是这样的:
- 把表t1的数据读入线程内存join_buffer中,由于我们这个语句中写的是select *,因此是把整个表t1放入了内存;
- 扫描表t2,把表t2中的每一行取出来,跟join_buffer中的数据做对比,满足join条件的,作为结果集的一部分返回。
假设小表的行数是N,大表的行数是M,在这个算法里:
- 两个表都做一次全表扫描,所以总的扫描行数是M+N;
- 内存中的判断次数是M*N。
看样子和Simple Nested-Loop Join是一样的,但是Block Nested-Loop Join算法的判断是内存操作,速度上会快很多,性能也更好。
这里涉及到一个参数是join_buffer_size
,join_buffer的大小是由join_buffer_size的大小控制的,默认256K。如果放不下的话,策略是分段放。
假设t1有100行,内存放不下,执行过程就变成了:
- 扫描表t1,顺序读取数据行放入join_buffer中,放完第80行join_buffer满了,继续第2步;
- 扫描表t2,把t2中的每一行取出来,跟join_buffer中的数据做对比,满足join条件的,作为结果集的一部分返回;
- 清空join_buffer;
- 继续扫描表t1,顺序读取最后的20行数据放入join_buffer中,继续执行第2步。
假设驱动表的数据行数是N,需要分K段才能完成算法流程,被驱动表的数据行数是M。这里的K不是常数,N越大K就会越大,因此把K表示为λ*N,λ的取值范围是(0,1)。
在这个算法的执行过程中:
- 扫描行数是
N+λ*N*M
; - 内存判断
N*M
次。
从内存判断次数看来不论哪个作为驱动表结果都是一样的,但是考虑到扫描行数,在M和N大小确定的情况下,N小一些,整个算式的结果会更小。所以使用小表作为驱动表会更好。
小表的概念
假设t1有100行,t2有1000行。
对于SQLselect * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;
join_buffer只需要放入t2的前50行。虽然t2比t1的行数多,但是这里“t2的前50行”是那个相对小的表,也就是“小表”。
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;
这两条SQL表t1只查字段b,因此如果把t1放到join_buffer中,则join_buffer中只需要放入b的值;表t2需要查所有的字段,因此如果把表t2放到join_buffer中的话,就需要放入三个字段id、a和b。
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表就是小表。
总结:
- 如果可以使用IndexNested-Loop Join算法,也就是说可以用上被驱动表上的索引,可以使用join连接,同时将小表作为驱动表。
- 如果使用Block Nested-Loop Join算法,扫描行数就会过多。尤其是在大表上的join操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。这种join连接要慎用。
- 不管使用哪一种,都尽量将小表作为驱动表!