【mysql优化 3】嵌套循环连接算法

原文地址：Nested-Loop Join Algorithms

mysql在表之间执行连接操作，包括了使用循环嵌套算法或者其他在此基础上的变形。

 

循环嵌套连接算法：

一个简单的嵌套循环连接（NLJ:nested-loop jon）算法，每一次运用一个循环从第一个表里读取行，通过每一行去嵌套循环连接第二个表。这个过程被重复了多次，因为还有剩余的待连接的表。

假设使用以下连接类型来执行三个表t1，t2和t3之间的连接：

Table Join Type

t1 range

t2 ref

t3 ALL

 

如果使用一个简单的NL算法，那么连接过程如下：

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
    }
  }
}

因为NLJ算法一次将一行从外部循环传递到内部循环，所以通常会在内部循环中多次读取处理的表。

 

块循环嵌套连接算法：

块嵌套循环（BNL）连接算法使用在外部循环中读取行的缓冲来减少内部循环中的表必须被读取的次数。例如：如果有10行被读取到了缓冲区，并将缓冲区传递到下一个内循环，则可以将内循环中读取的每行与缓冲区中的所有10行进行比较。这将减少内表必须读取的次数

Mysql的连接缓冲区有以下特点：

1，连接缓存可以被使用，当join的类型为：All、index、range。在外连接中使用缓冲区，也被描述在：Block Nested-Loop and Batched Key Access Joins

2，绝不会为第一个非常数表分配一个缓冲区，尽管它是All或者index类型

3，仅会把连接中必要的列存入它的连接缓存，而不是整行数据

4，连接缓存的大小的系统变量定义了每一个被用于查询的连接缓存的大小

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
    }
  }

}

 

如果，S是每一个t1的存储大小，t2是连接缓存的组合，C是在缓存中组合的数量，t3扫描的次数是：

(S * C)/join_buffer_size + 1

 

随着join_buffer_size的值增加，t3扫描的数量减少，直到join_buffer_size足够大以容纳所有以前的行组合。但是，尽管join_buffer_size足够大，但是它并没有变得更快！