- 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