文章目录
-
- SNLJ
- INLJ
- BNLJ
- 比较
MySQL 使用 NLJ（Nested-Loop Join） 算法实现 join 联表查询。
NLJ 一共有三种实现 SNLJ、INLJ 和 BNLJ。后面两种是对 SNLJ 的优化。
SNLJ
SNLJ，Simple Nested-Loop Join，简单嵌套循环。。
假设 A 是驱动表，B 是被驱动表。
扫描 A 表，记录一行一行取出比较，每一次比较都会扫描一次 B 表。
伪代码如下：
For each row a in A do
For each row b in B do
If a and b satify the join condition
Then output the tuple
假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：
- A 表扫描 1 次。
- B 表扫描 N 次。
- 一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次。有就是比较了 N * M 次。
- 总共读取记录数： N + N * M 。
- 回表读取记录数：0。
INLJ
INLJ，Index Nested-Loop Join，索引嵌套循环。
和 SNLJ 的区别在于 join 使用的字段已经建立索引，利用索引提高效率。
我们假设 A 是驱动表，B 是被驱动表。
使用聚簇索引：
没有使用聚簇索引，需要增加回表操作：
扫描 A 表，还是一行一行记录取出比较。不一样地方在于不用扫描 B 表，而是查询 B 表的索引，嵌套循环的内循环被大幅度优化。
伪代码如下：
For each row a in A do
lookup b in B index
if found b == a
Then ouput the tuple
假设 A 表 N 行，B 表 M 行，索引 B+ 树的高度为 IndexHeight。
- A 表扫描 1 次。
- B 表扫描 0 次。因为使用了索引查询。
- 一共有 N 个内循环，每个内循环遍历次数为索引树的高度，为 IndexHeight 次，一共有内循环 N * IndexHeight 次。也就是比较了 N * IndexHeight 次。
- 总共读取的记录数：N + M(match)。M(match) 为索引返回的记录数。
- 回表次数：主键的聚簇索引为，非聚簇索引为 M(match)，即每个记录还需要进行一次回表。
这里有个回表问题需要关注一下。
如果要查询的字段为 B 表的主键，使用了主键的聚簇索引，可以直接拿到记录。
如果要查询的字段不是 B 表的主键，使用的不是主键的聚簇索引，而是辅助索引，还需要进行一次回表查主键的聚簇索引。这里的性能会很有很大的下降。
BNLJ
BNLJ，Block Nested-Loop Join，块嵌套循环。
在 join 的字段没有加索引的情况下，会使用 join buffer 进行优化。就是 BNLJ 算法。
假设 A 为驱动表，B 为被驱动表：
MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个 join buffer。
A 表扫描一次，需要 join 联表查询的字段缓存到 buffer 中。然后扫描 B 表，把 B 表的每一行和 buffer 中的字段进行批量比较。
如果我们把 buffer 的空间开得很大，可以容纳下 A 表的所有记录，那么 B 表也只需要访问访问一次。
伪代码如下：
For each tuple a In A do
store used column as p from A in join buffer
for each tuple b In B do
If p and b satisfy the join condition
Then ouput the tuple
假设 A 表 N 个记录，B 表 M 个记录。开销如下：
- A 表扫描 1 次。
- B 表扫描的次数和 buffer 的大小有关，
N * (used_column_size)/join_buffer_size + 1
used_column_size
join_buffer_size
- 虽然加了 buffer，但实际上 A 表的每个记录和 B 表的每个记录都进行了比较，有 N * M 次比较。
- 总共读取的记录数： 设置 B 表扫码次数为 H，则这里记录数 = M * H。
- 回表次数：0。不走索引，不存在回表。
比较
以上就是嵌套循环算法的三种实现。
假设有这样的数据：
- 驱动表为 A，记录数 N；被驱动表为 B，记录数 M。
- 如果 join 字段使用索引，B+ 树的深度为 IndexHeight。匹配的记录数为 M(match)。
- 如果 join 字段不使用索引，使用的 buffer 大小为
join_buffer_size
used_column_size
三种实现的效率比较如下：
| SNLJ | INLJ | BNLJ | |
|---|---|---|---|
| 驱动表扫描次数 | 1 | 1 | 1 |
| 被驱动表扫描次数 | N | N * (used_column_size) / join_buffer_size + 1 | |
| join 比较次数 | N * M | N + M(match) | N * M |
| 回表次数 | M(match) |
总的来说，应用 join 需要注意：
- 用来进行 join 的字段要加索引，会触发 INLJ 算法，如果是主键的聚簇索引，性能最优。
- 如果无法使用索引，那么注意调整 join buffer 大小，适当调大些。
- 小结果集驱动大结果集。用数据量小的表去驱动数据量大的表，这样可以减少内循环个数，也就是被驱动表的扫描次数。