MySQL JOIN算法总结

• Nested-Loop Join

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
       //内层表重复遍历寻找匹配的行
      if row satisfies join conditions, send to client
    }
  }
}

外层表t1,t2每次只读取一行传入内层表t2,t3,导致内层表访问重复次数；

• Block Nested-Loop Join（优化后的）

利用连接缓冲区批量处理

连接缓冲区有以下特点：

1.当join类型为all，index，range时（无可用索引开启全表扫描时），外层join应用buffer

2.不会为第一个非常量表创建连接缓冲区，即使join类型是all或者index

3.只有连接需要的列才会存进缓冲区，而不是全部列

4.join_buffer_size系统变量决定每一个查询所用的缓冲区大小

5.为每一个连接分配一个缓冲区，则查询可以使用多个缓冲区执行

6.连接缓冲区，在执行前分配执行结束后释放

for each row in t1 matching range {
  for each row in t2 matching reference key {
    //将组合的数据存储进缓冲区
    store used columns from t1, t2 in join buffer
    //缓冲区满时将缓冲区循环t3表匹配缓冲区中数据，t3遍历结束清空缓冲区
    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
    }
  }
}
//上面缓冲区未填满时循环t3匹配缓冲区中组合数据
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
    }
  }
}

参考：https://dev.mysql.com/doc/refman/5.7/en/nested-loop-joins.html