DBA笔记-第十一部分（join 算法）

DDDDDBBBBBBBAAAAA

于 2024-08-01 16:28:58 发布

阅读量575

点赞数 19

文章标签： dba 笔记数据库

本文链接：https://blog.csdn.net/etinemark/article/details/140850079

版权

假设有两个表 a表三条数据， b表四条数据：

表a 表 b

id id

1 1

2 2

3 3

执行以下语句

SELECT a.id AS a_id, b.id AS b_id
FROM a
JOIN b ON a.id = b.id;

一.简单嵌套循环（SNLJ）

这个算法一定不要用，垃圾，除非数据量很小，简单来说，就是取a表每一条数据都循环和b表进行比较，a表扫描1次，b表扫描3次，共计比较12次，注意是比较几次，不是扫描几次

执行步骤

选择外表：从一个表（通常称为外表）中取出一行记录。
遍历内表：对内表的所有记录进行扫描，检查这些记录是否满足与外表当前记录的连接条件。
连接条件匹配：对于每一对记录，如果它们符合连接条件（如某个字段的值相等），则将它们组合在一起。
输出结果：将所有符合条件的记录对输出为查询结果。

执行过程

外循环：
- 取表 a 中的第一行：a.id = 1。
内循环：
- 对表 b 中的每一行进行扫描：
  - b.id = 1（符合条件），记录对 (1, 1) 。
  - b.id = 2（不符合条件）。
  - b.id = 3（不符合条件）。
  - b.id = 4（不符合条件）。
继续外循环：
- 取表 a 中的第二行：a.id = 2。
内循环：
- 对表 b 中的每一行进行扫描：
  - b.id = 1（不符合条件）。
  - b.id = 2（符合条件），记录对 (2, 2) 。
  - b.id = 3（不符合条件）。
  - b.id = 4（不符合条件）。
继续外循环：
- 取表 a 中的第三行：a.id = 3。
内循环：
- 对表 b 中的每一行进行扫描：
  - b.id = 1（不符合条件）。
  - b.id = 2（不符合条件）。
  - b.id = 3（符合条件），记录对 (3, 3) 。
  - b.id = 4（不符合条件）。

二.索引嵌套循环（INLJ）

简单来说就是选择小表或无索引表作为外表，也可以理解为先取数据的基础表，根据从表a拿出的数据值，使用索引在b表中查找，由于b表使用了索引，所以能直接定位数据位置，所以速度很快。

执行步骤

外层表扫描：遍历外层表的每一行记录。
索引查找：对每一行记录，利用内层表的索引进行查找。这通常涉及以下步骤：
- 根据外层表的记录值，使用索引快速定位内层表中的相关记录。
- 通过索引进行高效的查找，而不是全表扫描。
结果生成：将匹配的记录对输出为查询结果。

执行过程

外层表：选择表 a 作为外层表。
外循环：
- 取表 a 中的第一行记录 a.id = 1。
索引查找：
- 使用表 b 的索引查找 b.id = 1。
- 如果存在匹配的记录，则将 (1, 1) 作为结果。
继续外循环：
- 取表 a 中的第二行记录 a.id = 2。
索引查找：
- 使用表 b 的索引查找 b.id = 2。
- 如果存在匹配的记录，则将 (2, 2) 作为结果。
继续外循环：
- 取表 a 中的第三行记录 a.id = 3。
索引查找：
- 使用表 b 的索引查找 b.id = 3。
- 如果存在匹配的记录，则将 (3, 3) 作为结果。

三.块嵌套循环（BNLJ）

简单来说就是根据你内存设置的大小（join_buffer_size 的值一般配置默认256k），将表a数据读到内存中，扫描a表一次，将内存中的记录和表b中的数据每一行进行比较，此时表b也只需要扫描一次，但是比较次数还是和简单嵌套循环中一样是12次（此处不好理解，就是每次拿三个a表数据和b表每行做比较，比较次数不变，但是只需要扫描一次），原则上来说，只要join_buffer_size的值够大两个表就都只需要扫描一次。join_buffer 只会读取需要比较的列，所以对较小字节可以存很多的数据。

查看join_buffer_size 的语句

(root@localhost) [(none)]> show variables like 'join%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

执行步骤

分块：
- 将外层表的数据分成多个块（通常是内存能容纳的大小）。
- 块的大小通常取决于内存的容量，以确保每次处理的块可以完全加载到内存中。
外层块扫描：
- 对每一个外层块进行扫描，取出块中的所有记录。
内层表扫描：
- 对每个外层块中的记录，遍历内层表的所有记录。
- 查找符合连接条件的记录并进行匹配。
生成结果：
- 将每个块中的记录与内层表的匹配记录组合起来，并输出结果。

执行过程

分块：
- 将表 a 分成若干个块，例如，每个块包含 1000 行记录。
处理每个块：
- 对第一个块（包含 a 中的 1000 行记录）进行处理。
- 对于块中的每一行记录，扫描整个表 b，查找匹配记录。
- 将匹配的记录对生成结果集。
继续处理下一个块：
- 取 a 中的下一个块，重复上述过程。
完成连接：
- 所有块都处理完成后，生成最终的连接结果集。