mysql5.7官网直译SQL语句优化--嵌套循环连接算法

1.6Nested-Loop Join Algorithms(嵌套循环连接算法)
mysql通过使用一个嵌套循环算法或者是它的演变来执行表之间的连接。
>嵌套循环连接算法
一个简单的循环嵌套连接(NLJ)算法一次循环读取一行数据在第一张表中，通过每一行都嵌套循环处理与下一张表连接。这个过程被重复多次直到其他的多有表都被连接。
假设一个涉及到三张表t1,t2,t3的连接被执行通过如下的连接类型：
Table   Join Type
t1      range
t2      ref
t3      ALL
如果一个简单的NLJ算法被使用，这个连接过程可以类似如下：
for each row in t1 matching range { //遍历t1中的每一行范围匹配
  for each row in t2 matching reference key {//遍历t2中与key引用匹配的行
    for each row in t3 {
      if row satisfies join conditions, send to client
       //遍历t3中的行查找满足条件的行发送到客户端。
    }
  }
}
因为NLJ算法通过行数一次处理一行从最外层到内层的循环，这会使得内部表中的数据被循环多去很多次。
>block嵌套循环连接算法。
一个block循环嵌套(BNL)连接算法使用缓存行在外层循环读取的从而减少内部循环表中的必须读取的次数。例如，如果10行被读取到缓存，并且缓存被通过到下一层循环中，则每一行内部循环中的读取能够和缓存中的10行比较。这大量的减少了内部表必须读取的次数。
mysql连接缓存有如下的特性：
1）当连接类型是ALL或者是index或者是range时，可以使用连接缓存(换句话说，当没有可能的keys被使用，一个全表扫描被使用，要么是全表数据，要么是全表索引行)。缓存使用也可以用在外层连接中，具体请看8.2.1.11的块嵌套循环和批量处理的连接。
2）一个连接缓存永远不可能被使用对于第一个非固定的表，即使是使用了类型为All或者是index.
3）只有连接感兴趣的列才会被缓存到它的连接缓存中，并不是完整的列。
4）系统变量join_buffer_size定义了每一个连接缓存的大小，在一个查询中使用的缓存。
5）一个缓存可以被使用在每一个能够被缓存的连接中，所以对于给出的连接可能通过处理多个连接缓存来完成。
6）一个连接缓存被允许优先使用在执行一个连接并且在查询完成后释放掉。
之前给出的例子关于上述描述的NLJ算法(没有缓存)，如下这样的连接会使用连接缓存来完成：
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结合的大小是S在连接缓存中，并且C是结合后再缓存中的数量，则t3表被扫描的次数：(S * C)/join_buffer_size + 1 //也就是填写满缓存的次数+1
t3表的扫描次数随着join_buffer_size的增加而减少，当join_buffer_size大到足以放下所有之前匹配的行，那么查询的速度不会比这个更快了。
到此嵌套循环优化查询就结束了，接下来我们要说明的是1.7Nested Join Optimization;