[MySQL] 求助一个关于 mysql join 的问题

[小天管理]

这几天为了面试在看 mysql 的官方文档，看到关于 Nested-Loop Join 算法的章节。 其中普通的 Nested-Loop Join 倒是好理解，就是类似 for 循环嵌套 for 循环，但是到了第二个 Blocked Nested-Loop Join 我就不太懂了，

A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. This reduces by an order of magnitude the number of times the inner table must be read.

伪代码：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

看代码描述就是先从 t1,t2 读取数据，然后存在 buffer 中，最后 t3 再与 buffer 循环判断，感觉好像也没减少数量级啊。 后来去网上查，又得到了不同的解释：一次性缓存多条数据中，并且 t3 是批量判断缓存中的行。其中《 MySQL 实战 45 讲》又提到

从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

那就意味着 t1,t2,t3 都是一行一行读取的？ 有点懵圈了，求大佬们看看