MySQL-连接查询超全详解

1 作用

在数据库中 join 操作被称为连接，作用是能连接多个表的数据（通过连接条件），从多个表中获取数据合并在一起作为结果集返回给客户端。例如:

表 A：

表 B：

通过连接可以获取到合并两个表的数据：

select A.*,B.gender from A left join B on A.id=B.uid

2 连接关键字

连接两个表我们可以用两个关键字：on，using。on可以指定具体条件，using则指定 相同名字 和数据类型 的列作为等值判断的条件，多个则通过逗号隔开。
如下:

on: select * from A join B on A.id=B.id and B.name=''

using: select * from A join B using(id,name) = select * from A join B on A.id=B.id and A.name=B.name

3 连接类型

3.1 内连接

内连接和交叉连接

• 语法： A join | inner join | cross join B
• 表现：A 和 B 满足连接条件记录的交集，如果没有连接条件，则是 A 和 B 的笛卡尔积
• 特点：在 MySQL 中,cross join，inner join和 join 所实现的功能是一样的。因此在 MySQL 的官方文档中，指明了三者是等价的关系。

隐式连接

• 语法：from A,B,C
• 表现：相当于无法使用 on 和using的join
• 特点：逗号是隐式连接运算符。隐式连接是 SQL92 中的标准内容，而在 SQL99 中显式连接才是标准，虽然很多人还在用隐私连接，但是它已经从标准中被移除。从使用的角度来说，还是推荐使用显示连接，这样可以更清楚的显示出多个表之间的连接关系和连接依赖的属性。

3.2 外连接

左外连接

• 语法： A left join B
• 表现：左表的数据全部保留，右表满足连接条件的记录展示，不满足的条件的记录则全是null

右外连接

• 语法： A right join B
• 表现：右表的数据全部保留，左表满足连接条件的记录展示，不满足的条件的记录则全是null

全外连接

MySQL 不支持全外连接，只支持左外连接和右外连接。如果要获取全连接的数据，要可以通过合并左右外连接的数据获取到，如 select * from A left join B on A.name = B.name union select * from A right join B on B.name = B.name;。

这里 union 会自动去重，这样取到的就是全外连接的数据了。

3.3 自然连接

• 语法：A natural join B ==== A natural left join B ==== A natural right join B
• 表现：相当于不能指定连接条件的连接，MySQL 会使用左右表内相同名字和类型的字段作为连接条件。
• 特点：自然连接也分自然内连接，左外连接，右外连接，其表现和上面提到的一致，只是连接条件由 MySQL 自动判定。

4 执行顺序

在连接过程中，MySQL 各关键字执行的顺序如下：

from -> on|using -> where -> group by -> having -> select -> order by -> limit 

可以看到，连接的条件是先于 where 的，也就是先连接获得结果集后，才对结果集进行 where 筛选，所以 在使用 join 的时候，我们要尽可能提供连接的条件，而少用 where 的条件，这样才能提高查询性能。

5 连接算法

join有三种算法，分别是 Nested Loop Join，Hash join，Sort Merge Join。MySQL 官方文档中提到，MySQL 只支持Nested Loop Join 这一种算法。

具体来说 Nested Loop Join 又分三种细分的算法：

• SNLJ
• BNLJ
• INLJ

我们来看下对于连接语句select * from A left join B on A.id=B.tid，这三种算法是怎么连接的。

5.1 Simple Nested Loop Join（SNLJ）

SNLJ是在没有使用到索引的情况下，通过两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出。也就是让两张表做笛卡尔积进行扫描，是比较暴力的算法，会比较耗时。其过程如下：

for (a in A) {for (b in B) {if (a.id == b.tid) {output <a, b>;}
     }
 }

当然，MySQL 即使在无索引可用，或者判断全表扫描可能比使用索引更快的情况下，还是不会选择使用过于粗暴的 SNLJ 算法，而是采用下面的算法。

5.2 Block Nested Loop Join（BNLJ）

INLJ是 MySQL 无法使用索引的时候采用的 join 算法。会将外层循环的行分片存入 join buffer, 内层循环的每一行与整个buffer 中的记录做比较，从而减少内层循环的次数，具体逻辑如下:

for (blockA in A.blocks) {for (b in B) {if (b.tid in blockA.id) {output <a, b>;}
     }
 }

相比于 SNLJ 算法，BNLJ算法通过外层循环的结果集的分块，可以有效的减少内层循环的次数。

原理

举例来说，外层循环的结果集是 100 行，使用 SNLJ 算法需要扫描内部表 100 次，如果使用 BNLJ 算法，假设每次分片的数量是 10，则会先把对 Outer Loop 表(外部表)每次读取的 10 行记录放到 join buffer, 然后在InnerLoop 表(内部表)中每次循环都直接匹配这 10 行数据，这样内层循环只需要 10 次，对内部表的扫描减少了 9 /10，所以 BNLJ 算法就能够显著减少内层循环表扫描的次数。

当然这里，不管 SNLJ 还是 BNLJ 算法，他们总的比较次数都是一样的，都是要拿外层循环的每一行与内层循环的每一行进行比较。

BNLJ算法减少的是总的扫描行数，SNLJ算法是外层循环要一行行扫描 A 表的数据，然后取 A.id 去表 B 一行行扫描看是否匹配。而 BNLJ 算法则是外层循环要一行行扫描 A 表的数据，然后放到内存分块里，然后去表 B 一行行扫描，扫描出来的 B 的一行数据与内存分块里的 A 的数据块进行比较。这里可以一次就是很多行 A 的数据与 B 的数据进行比较，而且是在内存中进行比较，速度更加快了。

影响因素

这里 BNLJ 算法总的扫描行数是由外层循环的数据量 N，和分块数量K 还有内层循环的数据量 M 决定的。其中分块数量 K 与外层循环的数据量 N 又是息息相关的，我们可以表示为 λN，其中λ 取值为(0~1)。则总扫描次数C=N+λNM。

可以看出，在这个式子里，N和 λ 的大小都会影响扫描行数，但是 λ 才是影响扫描行数的关键因素，这个值越小越好（除非 N 和M的差值非常大，这时候 N 才会成为关键影响因素）。

那什么会影响 λ 的大小呢？那就是 MySQL 的 join_buffer_size 设置项的大小了。λ和 join_buffer_size 成倒数关系,join_buffer_size越大，分块越大，λ越小，分块数量也就越少，也就是外层循环的次数也越少。所以在使用不上索引的时候，我们要优先考虑扩大 join_buffer_size 的大小，这样优化效果会更明显。而在能使用上索引的时候，MySQL 会使用以下算法来进行join。

5.3 Index Nested Loop Join（INLJ）

INLJ 是 MySQL 判断能使用到被驱动表的索引的情况下采用的算法。假设 A 表的数据行为 10，B表的数据行为 100，且 B.tid 建立了索引，则对于select * from A left join B on A.id=B.tid，MySQL 会采用Index Nested Loop Join。其过程如下：

for (a in A) {if (a.id in B.tid.Index) {output <a, tid.Index 所在行 >;}
 }

总共需要循环 10 次 A，每次循环的时候通过索引查询一次B 的数据。而如果我们反过来是 B left join A 的话，总共要循环 100 次 B，由此可见 如果使用 join 的话，需要让小表做驱动表，这样才能有效减少循环次数。但是需要注意的是，这个结论的前提是可以使用被驱动表的索引。

INLJ 内层循环读取的是索引，可以减少内存循环的次数，提高 join 效率，但是也有缺点的，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的 I / O 操作。例如上面在索引里匹配到了 tid，还要去找tid 所在的行在磁盘所在的位置，具体可以见我以前的文章：MySQL 索引详解之索引的存储方式。

6 注意点

• 尽量增加连接条件，减少 join 后数据集的大小
• 用小结果集驱动大结果集，将筛选结果小的表首先连接，再去连接结果集比较大的表
• 被驱动表的被 join 的字段要建立索引，且使用上索引。使用上索引包括使用该字段，且不会有索引失效的情况出现
• 设置足够大的join_buffer_size

7 外连接常见问题

Q：如果想筛选驱动表的数据，例如左连接筛选左表的数据，该在连接条件还是 where 筛选？
A：要通过 where 筛选，连接条件只影响连接过程，不影响连接返回的结果数（某些情况下连接条件会影响连接返回的结果数，例如左连接中，右侧匹配的数据不唯一的时候）

Q：被驱动表匹配的数据行不唯一导致最终连接数据超过驱动表数据量该怎么办？例如对于左连接，右表匹配的数据行不唯一。
A：join之前先对被驱动表去重，例如通过 group by 去重：A lef join (select * from B group by name)。

8 参考资料

https://www.jianshu.com/p/686…
https://www.cnblogs.com/blueo…
https://leokongwq.github.io/2…
https://zhuanlan.zhihu.com/p/…

版权声明

转载请注明作者和文章出处
作者: X 先生
https://segmentfault.com/a/1190000023086991