Mysql连接查询详解

原创

斗者_2013 2022-12-07 17:14:05 博主文章分类：跟着老万学mysql ©著作权

文章标签 MySQL JOIN 连接查询连接缓冲区数据 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者斗者_2013的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言

记得刚工作几年的时候，就写了一篇关于Mysql连接查询的博客文章，里面简单的认为先对关联表的记录进行笛卡尔积，然后再根据where条件过滤，现在看来有点贻笑大方了。正好最近看了《mysql是怎样运行的》这本书，所以对Mysql连接查询的原理和过程重新做下总结。

一、什么是连接查询

连接查询指的就是多表关联查询。

示例表和数据准备:

CREATE TABLE `t1` (
  `m1` int,
  `n1` char(1)
) ;

CREATE TABLE `t2` (
  `m2` int,
  `n2` char(1)
) ;

INSERT INTO t1 VALUES(1,'a'),(2,'b'),(1,'c');

INSERT INTO t2 VALUES(2,'b'),(3,'c'),(4,'d');

典型写法：

SELECT * FROM   t1  [INNER | LEFT | RIGHT]  JOIN  t2   
       ON  t1.m1 = t2.m2
WHERE t1.n1 = 'c';

其中，ON表示两表的关联条件，WHERE是查询结果的过滤条件。

如果不加任何关联条件和WHERE条件，结果就是两表的笛卡尔积。比如：

SELECT * from t1, t2

Mysql连接查询详解_MySQL

注意⚠️：
这里只是查询的结果集正好和两表记录进行笛卡尔积后的结果相同，查询过程并不会对两表记录进行笛卡尔积的操作。

二、连接查询的分类

1、inner join：内连接

最后返回的数据行数是在inner join前后两张表中同时存在的数据行数。任何一条只存在于某一张表中的数据，都不会返回，

SELECT * FROM   t1  INNER  JOIN  t2   ON  t1.m1 = t2.m2;
-- 等价于
SELECT * FROM   t1  ,  t2   WHERE  t1.m1 = t2.m2;

查询结果：

Mysql连接查询详解_连接查询_02

2、外连接查询

外连接查询又分为左外连接查询和右外连接查询。

left join：左连接，又称为left outer join，我们平时都把outer省略。简写为left join

left左边的表为主表，left右边的表为从表。返回结果行数以left左边的表的行数为最后的数据行，对于左表中有些数据行在右表中找不到它所匹配的数据行记录时候，返回结果的时候这些行后面通常会以null来填充。

-- 左外连接查询  ，以JOIN左侧的表t1为驱动表
SELECT * FROM   t1  LEFT  JOIN  t2   ON  t1.m1 = t2.m2;

查询结果：

Mysql连接查询详解_连接缓冲区_03

right join：右连接，又称为right outer join，我们平时都把outer省略。简写为right join
right右边的表为主表，right坐标的表为从表。返回结果行数以right右边的表的行数为左后的数据行，对于主表中有些数据行在从表中找不到它所匹配的数据行记录时候，返回结果的时候这些行后面通常会以null来填充。

-- 右外连接查询，  以JOIN右侧的表t2为驱动表
SELECT * FROM   t1  RIGHT  JOIN  t2   ON  t1.m1 = t2.m2;

查询结果：

Mysql连接查询详解_连接查询_04

三、驱动表和被驱动表

1、什么是驱动表，什么是被驱动表？

驱动表在SQL语句执行的过程中，总是先读取。而被驱动表在SQL语句执行的过程中，总是后读取。
在驱动表数据读取后，放入到join_buffer后，再去读取被驱动表中的数据，来和驱动表中的数据进行匹配。如果匹配上则作为结果集返回，否则丢弃。

2、如何区分驱动表和被驱动表？

我们对于一个已有的SQL语句，我们应该怎么判断这个SQL语句中哪个表示驱动表？哪个表示被驱动表呢？

可以使用explain命令查看一下SQL语句的执行计划。在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。

如下示例中，左外连接中，左表t1是驱动表。

Mysql连接查询详解_连接查询_05

3、mysql是如何选择驱动表？

left join左外连接查询中，左表为驱动表；
right join右外连接查询中，右表为驱动表；
inner join内连接查询中，小表为驱动表；

那所谓的小表是什么表？
是表记录更少的表，还是通过单表过滤条件后更少的表，或者返回字段更少的表？
这里对于大小的判断，是指真正参与关联查询的数据量所占用的join_buffer的大小来区分的，而不是根据表中所有的数据行数来判断的。
所以，不能盲目的认为参与关联查询的表的数据行数少的表就一定是驱动表、数据行数大的表就一定是被驱动表。

只有在内连接查询中才需要根据大表小表来确定驱动表，而这个过程其实是由Mysql查询优化器确定的，并不需要用户去介入或指定。我们只需要大致知道是依据什么选择的驱动表就可以了。

具体可以参考这篇文章：MySQL中的驱动表和被驱动表

四、连接过程分析

用下面这个查询做为示例分析：

SELECT * from t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在这个查询中包含3个过滤条件:

t1.m1> 1;
t1.ml = t2.m2;
t2.n2 < ‘d’;

查询条件的分类：
1、单表过滤条件，其中t1.m1> 1、t2.n2 < 'd’就是单表过滤条件
2、关联过滤条件，t1.ml = t2.m2就是关联过滤条件

这个连接查询的执行过程大致如下.

步骤1、选取驱动表，根据驱动表涉及的所有单表过滤条件选取代价最小的方案执行单表查询。

单表查询：选取代价最小的那种访问方法去执行单表查询语句。
(就是说从const、 ref、 ref_or_null、 range、 index_merge、index、all 这些执行方法中选取代价最小的去执行查询即可).

t1的查询过程：

Mysql连接查询详解_连接查询_06

从t1中查出t1.m1> 1的记录有两条，分表是m1=2,m1=3;

步骤2、步骤1中从驱动表每获取到一条记录，都需要到t2表中查询匹配的记录。
所谓匹配的记录，指的是符合过滤条件的记录.
因为是根据 t1 表中的记录去找t2表中的记录，所以t2表也可以称为被驱动表.

将t1 表中查询记录根据关联条件t1.ml = t2.m2转为具体的查询条件，再结合t2所有的单表查询条件从t2表中查询匹配的记录。

对于从 t1 表中查询得到的第一条记录，也就是当t1.m1=2时，过滤条件 t1.m1 = t2m2就相当于
t2.m2=2，所以此时t2表相当于有了 t2.m2=2、t2.n2<‘d’ 这两个过滤条件，然后到t2表中执行单表查询；
对于从 t1 表中查询得到的第二条记录，也就是当t1.m1=3 时，过滤条件 t1. m1=t2.m2时就相
当于t2. 所以此时表相当于有了t2.m2=3 、t2.n2<‘d’ 这两个过滤条件，然后到表中连锺简介 181 11.1
执行单表查询。

整个连接查询的执行过程如下：

Mysql连接查询详解_连接缓冲区_07

注意⚠️：
这里需要强调一下，并不是将所有满足条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表中查询的。
大家可以试想一下，如果满足条件的驱动表记录非常多，那会需要很大一片存储空间，这显然是不合理的。
mysql在连接查询中，采用的是每获得一条驱动表记录就立即到被驱动表中寻找匹配的记录。

小结：
连接查询的过程：
步骤1、选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询.
步骤2、对步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录.

通同的两表关联查询如图所示：

Mysql连接查询详解_MySQL_08

如果有3个表进行连接，那么步骤2中得到的结果集就像是新的驱动表，然后第3个表就

成为了被驱动表，然后重复上面的过程。

五、ON和WHERE的区别

Where子句中的过滤条件
WHERE子句中的过滤条件不论是内连接还是外连接，凡是不符WHERE 子句中过滤条件的记录都不会被加入到最后的结果集。

SELECT * FROM   t1  LEFT  JOIN  t2   ON  t1.m1 = t2.m2  where t2.n2 < 'd' ;

查询结果：

由于t2.n2 < 'd’的查询条件在where子句中，查询结果必须满足

Mysql连接查询详解_数据_09

ON 子句中的过滤条件
对于外连接的驱动表中的记录来说，如果无法在被驱动表中找到匹配 ON 子句中过滤条件的记录。
那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL 值填充。

SELECT * FROM   t1  LEFT  JOIN  t2   ON  t1.m1 = t2.m2 AND t2.n2 < 'd' ;

Mysql连接查询详解_数据_10

ON子句中的单表过滤条件：
对驱动表的单表过滤条件放在ON子句中是无效的，只能放在WHERE子句中。
对被驱动表的单表过滤条件，放在ON子句中，即使被驱动表没有记录返回，也会采用NULL 值填充各个字段。

SELECT * FROM   t1  LEFT  JOIN  t2   ON  t1.m1 = t2.m2 AND t1.n1 < 'b' ;

根据查询结果发现，ON子句中针对驱动表t1的单表过滤条件失效。

Mysql连接查询详解_MySQL_11

SELECT * FROM   t1  LEFT  JOIN  t2   ON  t1.m1 = t2.m2 where t1.n1 < 'b' ;

针对驱动表的单表查询条件，需要放在where条件中才有效。

Mysql连接查询详解_JOIN_12

需要注意的是，这个ON子句是专门为"外连接驱动表中的记录在被驱动表找不到匹配记录时是否应该把该驱动表记录加入结果集中"这个场景提出的。所以，如果把 ON 子句放到内连接中，
MySQL 会把它像 WHERE 子句一样对待，也就是说内连接中的 WHERE 子句和 ON 子句是等价的。

注意⚠️：
ON子句中除了两表的关联字段配置，还能对添加查询条件。
对驱动表来说，ON中的查询条件除了关联关系，其他针对驱动表的单表查询条件都无效（基于Mysql8.0验证）。
对被驱动表来说，ON中的条件没有匹配上也能返回，对应的被驱动表记录的各个字段使用NULL 值填充。

推荐用法：
1、ON子句中尽量只配置关联条件；
2、驱动表的单表查询条件只在WHERE子句中配置。
3、被驱动表的单表查询条件，如果希望在没匹配上也能返回NULL值，那么添加到ON子句中；否则统一配置在WHERE子句中。

六、内连接和外连接的转换

由于凡是不符合WHERE子句中条件的记录都不会参与连接。所以只要在where子句的搜索条件中指定“被驱动表的列不为NULL”的搜索条件，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就从最后的结果集中被排除了。

--内连接的简写，全是where条件
SELECT * from t1,t2 WHERE t1.m1 = t2.m2
--内连接标准写法，on表示关联条件，where表示查询条件
SELECT * from t1 INNER JOIN t2 ON t1.m1 = t2.m2
--外连接查询通过限制连接条件不为空，实现内连接的等价写法
SELECT * from t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 IS NOT NULL;

注意⚠️：
如果ON连接条件中驱动表中t1.m1可以为null，那么该写法就不成立了。

也可以不用显示指定被驱动表的某个列符合 IS NOT NULL搜索条件，只要隐含地包含这个限制就可以了。
比如下面的2个sql是等价的。由于在WHERE子句中指定了被驱动表t2的m2 = 2，已经限制了返回结果中t2.m2不为空，所以和内连接查询是等价的。

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
-- 等价于
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

转换规则：
只需要外连接查询的where条件中指定被驱动表中一个非null的列不为空值，就等价于内连接查询了。而不需要一定是ON条件中的关联字段不为null。

这种在外连接查询中，指定的where子句中包含被驱动表中的列不为null值的条件称为空值拒绝（reject-NULL）。
在被驱动表的WHERE子句符合空值拒绝条件后，外连接和内连接可以相互转换。
这种转换带来的好处是优化器可以通过评估表的不同连接顺序的成本，选出成本最低的连接顺序来执行查询。

七、基于块的嵌套循环连接

上文中介绍的这种“驱动表只访问1次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录"的连接执行方式称为嵌套循环连接 Nest Loop ，这是最简单也是最笨拙的一种连接查询算法。

嵌套循环连接 Nest Loop可以分为一下三类：

Index Nested-Loop Join：索引嵌套循环连接
Simple Nested-Loop Join：简单嵌套循环连接
Block Nexted-Loop Join：基于块的嵌套循环连接

Index Nested-Loop Join：索引嵌套循环连接

说明：去被驱动表中取数的时候，使用了被驱动表的索引，而不是把被驱动表中的所有数据都扫描一遍。

采用如下SQL来解释Index Nested-Loop Join这种关联查询的执行过程

explain select * from A as a inner join B as b on a.id = b.id

Mysql连接查询详解_连接缓冲区_13

基于上面的查询计划，我们可以看出B表是驱动表，A表是被驱动表。接下来我们详细说明一下这个关联链接的具体执行过程是怎样的。

1、获取B表中的第一行数据，然后从这行中获取出该行的id的值。
2、拿着id的值去A表中去查找满足该id值的行，此时使用了A表中的主键索引。找到后把A表中的这个行和B表中的行拼接在一起，作为最后的结果集，返回给客户端。
这样就完成了B表中第一行数据和A表的inner join过程。
3、重复1、2、3步骤，直道把B表的所有行都遍历完成，就完成了此次的join过程。

以上的步骤就是Index Nested-Loop Join执行的过程。

注意：
去被驱动表A中获取数据的时候，使用到了A表中的索引，并不是把A表中的所有数据都扫描一遍，再去和B表中的数据去匹配。

Simple Nested-Loop Join：简单嵌套循环连接

说明：去被驱动表中查询时，没有使用索引，而是采用全表查询。

select * from A as a inner join B as b on a.code = b.code;

假设A表是小表也就是驱动表，B表是大表也就是被驱动表

此时在去A表被驱动表中查找数据的时候，就不能通过A表中的索引来获取数据了。那么就需要把A表中所有的数据都扫描一遍，然后再和B表中的code值进行匹配。这样每处理B表中的一行，A表中的数据都要全部扫描一次。这样的效率比前面我们提到的Index Nested-Loop Join慢了很多。

但是，MySQL在这种情况下并没有采用这种慢的方式，而是采用了下面我们将要说的Block Nexted-Loop Join的关联方式。这就是为什么我们在上面的查询计划中没有看到Simple Nested-Loop Join的原因。

Block Nexted-Loop Join：基于块的嵌套循环连接

说明：通过连接缓冲区（Join Buffer）缓存驱动表数据，批量和被驱动表进行匹配。

在采用嵌套循环连接算法的两表连接过程中，被驱动表要被访问多次。
如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读这
个表好多次，这个I/O 的代价就非常大了。所以需要想办法尽量减少被驱动表的访问次数。

前面说过，驱动表结果集中有多少条记录，就可能把被驱动表从磁盘加载到内存中多少次。
我们是否可以在把被驱动表中的记录加载到内存时，一次性地与驱动表中的多条记录进行匹配呢?
这样就可以大大减少重复从磁盘上加载被驱动表的代价了。
所以Mysql设计了 Join Buffer (连接缓冲区）的概念。

还是以这个查询为例：

SELECT * from t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

没有采用Join Buffer (连接缓冲区）前，查询被驱动表：

-- 第1次扫描驱动表
SELECT * from t2 WHERE  t2.n2 < 'd'  AND  t2.m2 = 2;

-- 第2次扫描驱动表
SELECT * from t2 WHERE  t2.n2 < 'd'  AND  t2.m2 = 3;

采用Join Buffer (连接缓冲区）后，查询被驱动表：

SELECT * from t2 WHERE  t2.n2 < 'd'  AND  t2.m2   in  （2,3）;

添加Join Buffer (连接缓冲区）后，不是每获取到一条驱动表的记录就立刻去查询被驱动表中匹配的记录。而是将驱动表的查询结果分批加载到Join Buffer (连接缓冲区）后，再去被驱动表中查询。

Join Buffer 就是在执行连接查询前申请的一块固定大小的内存。先把若干条驱动表结果集中的记录装在这个 Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与 Join Buffer 中的多条驱动表记录进行匹配，由于匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O代价。

最好的情况是Join Buffer足够大，能容纳驱动表结果集中的所有记录。这样只需访问一次被驱动表就可以完成连接操作。

MySQL中把这种加入了 Join Buffer 的嵌套循环连接算法称为基于块的嵌套循环连接 (Block Nested-Loop Join)算法。
此时的关联过程是这样的：
1、首选会把满足条件的驱动表B的数据分批全部加载到join buffer中（因为驱动表一般是小表，更省空间）。
2、扫描被驱动表A，把表中的行依次取出来和join buffer中的驱动表B中的数据进行匹配查询。匹配上则保留为左后的结果，否则丢弃。

Mysql连接查询详解_JOIN_14

MySQL会把驱动表B中的数据，分批次的放入到join buffer中。每次在放置新的数据进入join buffer之前，会清空上一次的数据。并且，每次在放入新的数据后，都会再次把被驱动表A中的数据扫描一遍和join buffer中数据进行关联匹配。

此时，如果适当的增加join buffer的大小，则可以减少分批次放入的次数，也就可以减少对被驱动表扫描的次数。所以在某些情况下，适当的增加join_buffer_size的值，可以提供join查询的效率。

八、连接查询的优化

1、内连接查询可以通过驱动表的选取优化

内连接的驱动表和被驱动表的位置可以相互转换，而左(外)连接和右(外)连接的驱动表与被驱动表是固定。
这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本而外连接却无法优化表的连接顺序。
一般mysql的查询优化器会自动选择内连接查询中的小表做为驱动表。

2、通过对被驱动表的查询条件上建立索引优化

1、针对驱动表的查询优化
由于步骤1中针对t1的过滤条件是t1.m1> 1，所以可以在m1字段上建立索引。
t1.m1> 1。

2、针对被驱动表的查询优化
针对被驱动表t2的查询条件，包含2个字段 t2.m2=2、t2.n2<‘d’
可以建联合索引，也可以单独建立索引，单索引优先建立在等值查询的条件上。

注意⚠️：
这里存在一个存在一个很老的认知误区，简单的认为针对关联查询的查询优化，需要对关联条件中的字段建立索引来提高查询效率。
不能说是完全错误，但是本质上是对Join查询条件的认知一般。
针对被驱动表的查询，只需要使用到索引就能提高查询效率，至于是不是关联条件中的字段并没有强行的要求。