mysql join原理

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) {
    //匹配成功的数据
  }
}