JZTXT

MySQL学习(7)连接的原理

发布时间 2023-10-21 11:41:54作者: 哪过晓得

什么是连接

连接就是把各个表中的记录都取出来进行依次匹配。若无过滤条件，连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，这样的结果集称为笛卡尔积。

测试数据：

CREATE TABLE t1(m1 INT, n1 char(1));
CREATE TABLE t2(m2 INT, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

 

两表连接示意图

连接的过程

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

 

这个查询中有3个过滤条件，分别是：

  • t1.m1 > 1

  • t1.m1 = t2.m2

  • t2.n2 < 'd'

连接查询中的过滤条件分为两种：

  • 单表条件：t1.m1 >1是t1表的过滤条件，t2.m2 < 'd'是t2表的过滤条件。

  • 两表条件：t1.m1 = t2.m2是两个表的过滤条件。

连接查询的过程如下：

  1. 首先确定第一个需要查询的表，称之为驱动表。

  2. 驱动表中美获取到一条记录，都需要到另一个表查找匹配到记录，这个表称之为被驱动表。

注意：驱动表只会被访问一次，每获得一条驱动表记录，就立即到被驱动表中寻找匹配记录，驱动表经过过滤条件后有多少条记录，就要访问多少次被驱动表。

内连接

对于内连接的两个表，若驱动表中的记录在被驱动表中找不到匹配记录，也就是不符合ON子句中的连接条件时，则该记录不会被加入到结果集。

SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;

 

内连接中不强制要求使用ON子句，因为此时ON和WHERE没啥区别，且驱动表和被驱动表可互换。

外连接

对于外连接的两个表，即使驱动表中的记录在被驱动表中没有找到匹配记录，也会加入到结果集，查询列表涉及到的被驱动的列显示NULL。

外连接分为左外连接与右外连接，必须使用ON子句之处连接条件，不可以省略。其中左外连接使用LEFT JOIN连接两表，左边为驱动表，右边为被驱动表；右外连接使用RIGHT JOIN连接两表，左边为被驱动表，右边为驱动表。

# 左外连接
SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2; 

image-20231020231901547

# 右外连接
SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2; 

image-20231020231946768

左外连接和右外连接的驱动表和被驱动表不能互换，LEFT JOIN规定左边为驱动表，RIGHT JOIN规定右边为驱动表。

连接的原理

嵌套循环连接

驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录，这种连接执行方式称为嵌套循环连接（Nested-Loop Join）。

注意：从驱动表中得到了一条记录，就立即到被驱动表进行查询，如果得到了匹配到记录，就把组合后的记录发送给客户端。

在嵌套循环连接中，驱动表和被驱动表都要各自执行单表查询，这样可以利用索引提高查询效率。特别是被驱动表，若每次执行全表扫描，会非常慢。

基于块的嵌套循环连接

MySQL在执行连接查询前申请了一块固定大小的内存，叫做Join Buffer（连接缓冲区）。先把若干条驱动表结果集中的记录装在Join Buffer中，然后开始扫描被驱动表，每一条驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于匹配过程全都是在内存中完成的，这样显著减少了I/O代价。加入了Join Buffer的嵌套循环连接算法称为基于块的嵌套循环连接算法。

Join Buffer的大小通过系统变量join_buffer_size设置，默认大小256KB。最好是为被驱动表加上合适的索引，如如果不能使用索引，并且机器的内存较大，可以调整join_buffer_size大小来优化连接查询性能。Join Buffer不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件的列才会被放到Join Buffer中，所以尽量不要将无用的列加入到查询列表，这样会浪费Join Buffer可用内存。

 

阅读自《MySQL是怎样运行的》小孩子4919