当前位置：首页 > news >正文

多表查询~

news 2025/7/12 8:11:00

导读

多表查询就是将多张表连接之后进行查询，而多表之间连接的过程就是在做笛卡尔积。

当我们进行两表之间的连接查询时，总是想为一张表中的所有记录在另一张表中找到与之匹配的记录，此时，我们称前者为驱动表，也称其为主表；而后者则被称为被驱动表。

以两表查询为例，驱动表只会被访问一次，而被驱动表则可能会被多次访问，具体访问次数取决于驱动表中符合查询条件的记录个数。访问驱动表产生的结果集要想与被驱动表中的记录产生关联，需拿其每一条记录分别到被驱动表中进行匹配，类似一个嵌套循环，也就是下面将要提到的“嵌套循环连接”。

对于单纯的嵌套循环连接来说，显然效率不高，MySQL 对其进行了一定的优化，即“基于块的嵌套循环连接”。

一、连接的类型

内连接：驱动表中的记录如果在被驱动表中找不到与之匹配的记录，则该记录不会加入到结果集中。
外连接
- 左外连接：以左表为驱动表，驱动表中的记录即使在被驱动表中找不到与之匹配的记录，该记录也会加入到结果集中。
- 右外连接：以右表为驱动表，驱动表中的记录即使在被驱动表中找不到与之匹配的记录，该记录也会加入到结果集中。

二、连接的原理

1、嵌套循环连接（Nested-Loop Join）

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录，通常借助索引加快连接查询的速度。

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

Block Nested-Loop Join，缩写 BNL，执行连接查询前会先申请的一块固定大小的内存，MySQL 中称其为 join buffer。先把若干条驱动表结果集中的记录装在 join buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和区域中的多条驱动表记录做匹配，可以有效减少访问被驱动表的次数。这种加入了 join buffer 的嵌套循环连接算法被称为基于块的嵌套连接算法。

join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进行配置，默认大小为262144 字节（也就是 256KB），最小可以设置为 128 字节。

需要注意的是，驱动表的记录并不是所有列都会被放到 join buffer 中，只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以最好不要把 * 作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在 join buffer 中放置更多的记录。

MySQL使用Join Buffer有以下要点

1. join_buffer_size 变量决定 buffer 大小。

2. 只有在 join 类型为 all, index, range 的时候才可以使用 join buffer。

3. 能够被 buffer 的每一个join都会分配一个 buffer, 也就是说一个 query 最终可能会使用多个 join buffer。

4. 第一个 nonconst table 不会分配 join buffer, 即便其扫描类型是 all 或者 index。

5. 在join之前就会分配 join buffer, 在 query 执行完毕即释放。

6. join buffer 中只会保存参与 join 的列, 并非整个数据行。

三、总结

1、BNL 的算法思想

扫描表的过程就是通过磁盘 I/O 将表的数据加载到内存，然后从内存中比较匹配条件是否满足，因此使用嵌套循环连接算法时，可能需要多次磁盘 I/O 读取被驱动表，势必会造成性能上的损耗。而要想减轻这方面的代价，我们可以通过尽量减少访问被驱动表的次数。

在 BNL 中，通过加入一块 join buffer 区域，用于存放若干条驱动表的结果集，然后将 join buffer 区域中的记录与被驱动表记录进行匹配，这样的话，就可以使一条被驱动表的记录一次同时与多条驱动表的记录进行匹配（反入为主）。也就是说，通过一次磁盘 I/O 将被驱动表加载到内存后，将这些数据在内存中遍历一次即可完成与驱动表多条记录的匹配，有效减少了磁盘 I/O 的次数。

BNL 算法本质上其实就是将嵌套循环进行了拆解，使用字典集（Map）将嵌套循环拆成了单层循环，而 join buffer 就相当于 BNL 中的字典集，以此大大提升了查询效率。

2、慎重使用 select *

一则，如果 select 的列能被索引列覆盖，可以避免回表开支；

二则，减少被驱动表 I/O 次数。被驱动表 I/O 次数 = 驱动表记录 size / join_buffer_size,其中单条驱动表记录 size = select 列 size + 过滤条件 size，因此 select col 越精确，驱动表记录 size 就越小，被驱动表 I/O 次数就越少。