文章目录
前言
为什么要研究关联查询的原理呢?有以下两个原因:
- 关联查询几乎每个项目中都会碰到,但是我们在编写时面对 INNER JOIN、LEFT JOIN、RIGHT JOIN 等关联脑中是否有该选择哪个的清晰概念呢?
- MySQL 认为任何一个查询都是一次 “关联”,就算是单表查询也是"关联"。
前文讲过 mysql 的逻辑执行结构,优化器给出执行计划,然后执行引擎根据计划来执行。也就是优化器给出代码,执行引擎运行代码。所以,这里的关联查询底层原理实际就是看 优化器
给出的执行计划是什么。
最后一个点就是随着 mysql 版本的更新,执行计划也在优化改变,我们随着计划的迭代来依次看看。
mysql5.6之前是嵌套循环连接(nested-loop join)和 索引嵌套循环连接(indexed nested-loop join),mysql5.6引入重大的改变,即一个优化和两个重要的连接,5.7 同 5.6。
一个优化是:Multi-Range Read Optimization
两个连接是:块嵌套循环连接(Block Nested-Loop join)和 BKA连接(Batched Key Access Join)。
下面,我们就依次看看这些连接的原理,最后通过实验来验证对这些原理的理解是否正确。
连接的分类
我们就看常用的,简单分成:
- 内连接:即 INNER JOIN
- 外连接:即 OUTER JOIN
- 左连接:即 LEFT JOIN
- 右连接:即 RIGHT JOIN。实际优化器处理中,会将右连接转为左连接。
所以对我们来说,还要搞清楚内连接和外连接的区别到底是什么,下文会给出答案。下面,我们就依次来看看连接算法。
嵌套循环连接(nested-loop join)
嵌套循环连接(NLJ )实际上就相当于两层 for 循环,外层是 驱动表,内层是 被驱动表。也就是依次从驱动表中取出符合条件的行,然后用该行数据去执行内层for循环,即依次去匹配被驱动表的每一行。
内连接示例
SELECT tb1.co1, tb2.co2 FROM tb1 INNER JOIN tb2 ON tb1.co3 = tb2.co3 WHERE tb1.co1 IN(5,6)
示例流程图如下:
![](https://i-blog.csdnimg.cn/blog_migrate/5c4d2f4d2258f68280b84a3021a676c7.png)
泳道图示意如下:
![](https://i-blog.csdnimg.cn/blog_migrate/272c7d8c4c66a1d8e40b785a05ca39d0.png)
外连接示例
SELECT tb1.co1, tb2.co2 FROM tb1 LEFT JOIN tb2 ON tb1.co3 = tb2.co3 WHERE tb1.co1 IN(5,6)
和内连接的唯一区别,示意图如下,其中标红的就是区别点:
![](https://i-blog.csdnimg.cn/blog_migrate/513138cb798e346b564e21838aeaa5dd.png)
泳道示意图如下,假设表tb2中co3列的值没有等于2的:
![](https://i-blog.csdnimg.cn/blog_migrate/3ef508bde1cbc4e71a08e820cfb1be78.png)
通过对比可以看出,外连接会在tb2中的匹配行不存在时对 tb2 的 co2 字段补充 null值。
小结
不论是内外哪种连接,当 tb1有100行符合条件的数据,而 tb2 有1000行数据,则需要对tb2执行全表扫描100次,总共需要读取tb2表10万数据行。
如果简单的把每次读取数据行都看成一次磁盘I/O操作,则需要10万次磁盘I/O,这个访问次数很可怕了。虽然可以通过各种缓存或者顺序读等操作提速降低I/O,但是整体性能还是非常差了。
伪代码形式
有了前面的铺垫,现在我们结合官方文档来看看它是怎么解释嵌套循环连接,怎么对比内连接和外连接的,以及WHERE语句作用于内外连接时的不同表现。
内连接
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3)
WHERE P(T1,T2,T3)
其中,P(T1,T2,T3)表示这三个表的连接条件,P1(T1,T2)也同理;即举例 P1(T1, T2)可看作是: ON T1.a = T2.b
。那么,用伪代码表示这个三表内连接的嵌套连接算法为:
FOR each row t1 in T1 {
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
其中,t:=t1||t2||t3
表示 t 就是由 t1,t2,t3这三行数据中的各自列拼凑成的结果行。
可以看出,就是一层层的嵌套轮询,将最终的结果行输出。那么内连接的真正执行顺序只能是从 t1到t2到t3依次内循环吗?
答案是否定的,顺序可能会改变,主要取决于驱动表的大小,这个我们后面还会提及,现在就简单看下另外的可能顺序:
FOR each row t3 in T3 {
FOR each row t2 in T2 such that P2(t2,t3) {
FOR each row t1 in T1 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
最后,我们再来看下假如 WHERE 条件是单独作用于多张表的情况下,内连接是如何处理的?
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
,其中 C1(T1)表示单独对于T1表的条件,比如T1.a=1。
则,伪处理代码为:
FOR each row t1 in T1 such that C1(t1) {
FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
可以看到条件每个 WHERE 条件都下沉到了各循环中,那么每个循环中查询出来的行数肯定减少了,总体性能也提高了。
外连接
外连接的伪代码形式要稍微复杂一些。
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3)
对于上面的SQL语句,其伪代码表示如下:
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
流程仍然类似,关键点在于两个 BOOL
类型的标志位:f1和f2。当第三层的 t3 没有找到数据时,!f2就是 TRUE,t3就表示 NULL(即 这行的值全为NULL),则当 P(t1,t2,NULL) 为真时,输出结果为:t:=t1||t2||NULL;
f1也同理。
和内连接对比理解可得区别。
那么,外连接的情况下循环的嵌套顺序是否可以改变?答案是不可以。只有内连接其嵌套顺序才可以改变。也就是说,
SELECT * FROM T1 LEFT JOIN
(T2 INNER JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3)
这个SQL的执行顺序可以等价于:
SELECT * FROM T1 LEFT JOIN
(T3 INNER JOIN T2 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3)
当WHERE条件单独作用于每个表时,我们看看外连接的变化。
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3)
其中,P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
。则伪代码为:
FOR each row t1 in T1 such that C1(t1) {
BOOL f1:=FALSE;
FOR each row t2 in T2
such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
BOOL f2:=FALSE;
FOR each row t3 in T3
such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1 && P(t1,NULL,NULL)) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
先假定每条关联记录都能找到数据,则伪代码可以简化为:
FOR each row t1 in T1 such that C1(t1) {
BOOL f1:=FALSE;
FOR each row t2 in T2
such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
BOOL f2:=FALSE;
FOR each row t3 in T3
such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
// 这里往后的代码去掉了
}
我们来推演一下过程:
- row t1 有数据,f1 是 FALSE
- row t2 查询时,因为 f1 为 FALSE,所以
P1(t1,t2) AND (f1?C2(t2):TRUE)
变成了P1(t1,t2) AND TRUE
。 注意,这里首先执行了 P1条件的判断,但是没有执行 C2(t2) 条件的判断,并且此时 f2 是 FALSE; - row t3 查询时,因为 f1和 f2 都是 FALSE,所以
P2(t2,t3) AND (f1&&f2?C3(t3):TRUE)
变成了P2(t2,t3) AND TRUE
。注意,这里执行了 P2条件的判断,但是没有执行 C3(t3) 条件的判断,此时 f1和 f2 都是 FALSE。 - 执行
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3)))
这个语句时,因为f1和 f2 都是 FALSE,所以对 C2(t2) 和 C3(t3) 执行了判断,这里就把 2,3 步中没执行的判断补上了。 - 当
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3)))
最终判断为 TRUE时,则执行t:=t1||t2||t3; OUTPUT t;
,即输出 结果 t 值。 - 将 f1和 f2 设置成了 TRUE。
- 假如最内层的循环还有数据,即下一个 row t3 还有数据。则判断条件
P2(t2,t3) AND (f1&&f2?C3(t3):TRUE)
就变成了P2(t2,t3) AND (C3(t3))
,所以这里先执行 P2条件的判断,然后执行了 C3(t3) 的判断。 - 类推到 第二层循环,也会执行 C2(t2) 的判断。
那么去掉后的代码就是保证当某个内层循环没数据的时候,在最终的结果集中补充 NULL值。
所以,对于外连接的 WHERE 条件,只有当关联有记录时,才会执行对应的 WHERE 条件。
索引嵌套循环连接(indexed nested-loop join)
那么,假如我们优化一下 SELECT tb1.co1, tb2.co2 FROM tb1 INNER JOIN tb2 ON tb1.co3 = tb2.co3 WHERE tb1.co1 IN(5,6)
,给tb2的 co3 加上索引呢?
加上索引以后,内层的for循环就不需要全表扫描了,可以根据索引直接定位到数据行(索引我们还没涉及,暂时先这样简单理解,没影响)。假设 tb2 的 co3 是唯一索引,则整体流程并没改变,只是tb2的查询处底层走了索引,过程仍然和没加索引的 INNER JOIN 一样,如图,图中省略了许多步骤:
![](https://i-blog.csdnimg.cn/blog_migrate/1443b375b7dbdb62396ca8e422eef147.png)
即红色框内的查询不再是全表扫描,而是根据索引直接定位到数据。
那么,当 tb1有100行符合条件的数据,而 tb2 有1000行数据,则需要对tb2执行100次索引查找,先认为每次索引查找只扫描一行,则总共需要只读取tb2表100个数据行。大大提升性能。
块嵌套循环连接(Block Nested-Loop Join)
对于没有索引的情况,mysql5.6开始增加了块嵌套循环连接(BNL),也就是把嵌套循环连接这个算法替换成了块嵌套循环连接。
该算法的关键是引入了 join buffer
内存缓存块,通过将数据存入 join buffer,然后循环的时候拿其他表的记录来依次匹配这个 Join buffer中的数据。
比如当两表t1和 t2 连接时,可以把 t1 结果集读入 join buffer,然后扫描 t2表,从 t2 中取出一行来和 join buffer 中的数据行依次进行匹配,再将最终的结果集输出。
虽然总的遍历次数相同,但是由于大量的遍历操作是落在 join buffer 这个缓冲区的,所以速度会提高很多。
我们再结合官方文档来看看,假如三个表如下:
Table Join Type
t1 range 条件判断
t2 ref 索引
t3 ALL 全表扫描
当 t1,t2,t3 三表关联时,NLJ算法伪代码如下:
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
}
}
}
BNL算法伪代码如下:
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer // 将t1t2中用的列放入 join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer { // 遍历 join buffer
if row satisfies join conditions, send to client
}
}
empty join buffer // 清空 join buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}
这里有几个重点问题需要明确:
- 多表关联时,每个表的数据都放到同一个 join buffer 中吗?
答:不是的,One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.
即一个查询可能有多个 join buffer
- 假如表中数据不能一次全部读入 join buffer 怎么办?
答:这一点已经在伪代码展示了,join buffer 会清空后再次读入后续的数据
- join buffer 中存的列是表中的所有 column 吗?
答:Only columns of interest to a join are stored in its join buffer, not whole rows.
只会缓存需要的列。
本文提到的三种查询算法,其实还有很多需要讨论的,比如缓存空间等,这些留到后面再返回来研究。下一篇文章,我们就看看 Multi-Range Read Optimization
和 BKA 算法的原理。