mysql-join原理

基础概念

外表,也叫驱动表,一般为小表,不仅相对其他表数据量小,而且记录的绝对值也较小,不要求有索引。

内表,也叫被驱动表,一般为大表,要求关联字段带索引。

驱动表:

在NLJ中,最先获取数据的表,并以此为表数据为基础,逐步获取其他表的数据,直到查询出所有满足条件的数据。

NLJ算法

Nested-Loop Join,扫描外表,每读到一条外表的记录,就根据关联字段去另外一张内表中查找,若连接字段没有索引,查询优化器一般不会选择 Nested Loop。外表返回的每一行都要在内表中检索它匹配的每一行,因此整个查询返回的结果集不能太大。

如果有三个及以上的表,则会先使用 NLJ 算法得到一、二个表的结果集,并将该结果集作为外层数据,遍历结果集到后第三个表中查询数据。

Simple Nested-Loop Join

如下图,r为驱动表,s为匹配表,可以看到从r中分别取出r1、r2、……、rn去匹配s表的左右列,然后再合并数据,对s表进行了rn次访问,对数据库开销大。

一个简单的嵌套循环联接(NLJ)算法,循环从第一个表中依次读取行,取到每行再到联接的下一个表中循环匹配。

如果使用的是简单NLJ算法,那么连接的过程像这样:

1
2
3
4
5
6
7
8
for each row in t1 matching range {
for each row in t2 matching reference_key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}

因为NLJ算法是通过外循环的行去匹配内循环的行,所以内循环的表会被扫描多次。

Index Nested-Loop Join(索引嵌套)

这个要求非驱动表(匹配表s)上有索引,可以通过索引来减少比较,加速查询。
在查询时,驱动表(r)会根据关联字段的索引进行查找,挡在索引上找到符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表(s)的关联健是主键的话,性能会非常高,如果不是主键,要进行多次回表查询,先关联索引,然后根据二级索引的主键ID进行回表操作,性能上比索引是主键要慢。

BLJ

即Block Nested-Loop Join,是MySQL自己创建的方式。

如果有索引,会选取第二种方式进行join,但如果join列没有索引,就会采用Block Nested-Loop Join。

可以看到中间有个join buffer缓冲区,是将驱动表的所有join相关的列都先缓存到join buffer中,然后批量与匹配表进行匹配,将第一种多次比较合并为一次,降低了非驱动表(s)的访问频率。

默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

当 Join Buffer太小,MySQL不会将该 Buffer存入磁盘文件,而是先将Join Buffer中的结果集与需要 Join 的表进行 Join操作,然后清空 Join Buffer中的数据,继续将剩余的结果集写入此 Buffer中,如此往复。这势必会造成被驱动表需要被多次读取,成倍增加 IO访问,降低效率。

实例

问题

  1. join中on条件是用来做关联的,而不是用来过滤

  2. 为什么要小表驱动大表,小结果集驱动大结果集?

    多表连接查询时,驱动表定义为:指定了连接条件,满足查询条件的记录行数少的表为驱动表,未指定连接条件时,行数少的表为驱动表。

    多表关联查询时,MySQL使用NLJ算法进行查询的,优化的目的是尽可能的减少JOIN中Nested Loop的循环次数。

0%