MySQL join表共有三种匹配方式,以A left join B为例进行描述,A left join B是以A表为驱动表,B表为匹配表进行连接
一、Nested-Loop Join Algorithm(简单的嵌套循环连接)
一种简单的嵌套循环连接(NLJ)算法,循环从A表中读取每一行,然后将每一行与B表中的每一行进行匹配,其实就是两层for循环。
for each A_row in A {
for each B_row in B {
if (A_row.fid == B_row.fid)
//匹配成功的数据
}
}
二、Block Nested-Loop Join Algorithm(块嵌套循环连接)
块嵌套循环连接(BNL)算法,循环从A表中读取多行(buffer_size/row_size行),然后将与B表中的每一行进行匹配,相比第一种方式利用缓存减少了匹配表读取次数
for each A_row in A {
store used columns from A join buffer // 将A表中需要用的字段存入buffer
if buffer is full {
for each B_row in B { // 开始读取B表
for each buffer_row buffer { // 与buffer中的数据进行匹配
if (buffer_row.fid == B_row.fid)
// 匹配成功
}
}
empty join buffer // 清空buffer
}
}
}
// 如果buffer不为空再处理一次
if buffer is not empty {
//
}
这也是mysql默认使用的方式,
通过Show variables like 'optimizer_switc%'
查看缓存块是否开启block_nested_loop=on
,默认开启状态
Show variables like '%join_buffer%'
查看缓存快大小,默认256kb
所以在进行join操作时不要查询多余字段,使得缓存块可以缓存更多行。
三、Index Nested-Loop Join(索引嵌套循环连接)
原本需要对匹配表进行全表扫描,现在利用索引减少了匹配表IO以及匹配次数,前提是,关联字段在匹配表中必须有索引
for each A_row in A {
if(A_row.fid exist B_fid_index) {
//匹配成功的数据
}
}