本文问题

什么是外联接，什么是内联接？
MySQL中的JOIN语法与标准SQL语法有什么不同？
MySQL Join算法有几种？
NLJ算法的过程是怎样的？
BNL算法的过程是怎样的？

join语法

外联接和内联接

内联接：INNER JOIN是内联接
内连接中，结果中只包括同时符合WHERE条件及ON条件的行
外联接：LEFT JOIN和RIGHT JOIN都是外联接
外联接中，T2中的行可以不符合ON条件，如果T2中的没有符合ON条件的行，使用NULL代替T2中的列值

# 联接条件使用`ON`表示
# 外联接
SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.C2
# 内联接
SELECT * FROM T1 INNER JOIN T2 ON T1.C1=T2.C2

MySQL对JOIN语法的扩展

MySQL中，可以将多个表放在括号中，表示多表内联接：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
# 等同与
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在MySQL中，CROSS JOIN在语法上等同与INNER JOIN,他们可以互相替代。在标准SQL中，他们是不同的，INNER JOIN可以使用ON子句，CROSS JOIN不行。

Join算法

MySQL在表中执行join联结使用nested-loop算法或该算法的变体

Nested-Loop Join(NLJ)
Block Nested-Loop Join(BNL)

Nested-Loop Join算法

简单的nested-loop join(NLJ)算法是一次将第一个表中的一行读取到循环中，将每行放入到一个处理下一个join的表的嵌套循环中。如果仍有其他表加入，重复这个过程。
假设有一个在t1,t2,t3三个中的join操作，执行以下类型的join：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单NLJ算法，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
    }
  }
}

由于NLJ算法从外循环到内循环处理行数据，通常会多次读取内循环中的表。

Block Nested-Loop Join算法

Block Nested-Loop(BNL)算法缓冲在外循环中读取的行，来减少读取内循环表的次数。例如，如果将10行数据读取到缓冲区中，然后将缓冲区传入到内循环，内循环中读取的行可以一次与缓冲区中的10行数据进行对比，这可以减少读取内循环中的表的次数。

MySQL join缓冲区具有以下特征：

当join类型为ALL或index，或者为range时，可以使用join缓冲区。缓冲区也可以应用于外联接。
即使类型为ALL或index，join buffer不会分配给非常量表。
只将join相关的列存储到join buffer，不会将整行都存储到join buffer中
join_buffer_size决定了处理查询时每个join buffer的大小
为每个可以被缓冲的join分配缓冲区，所以一个查询在处理过程中可能会使用多个缓冲区
缓冲区在执行join前分配，在查询完成后释放

对于之前NLJ算法的示例，如果使用join buffer，过程如下：

# 就是每当join buffer满后，对内层的表进行一次扫描并返回结果，然后清空join buffer。
# 由于数据量不一定会总是填满join buffer。最后还要再检查一次join buffer，如果不为空的话，再对内层的表进行一次扫描

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
        }
      }  # for each row in t3 循环结束
      empty join buffer
    } #if buffer is full 循环结束
  } # for each row in t2 循环结束
} # for each row in t1 循环结束

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足够大，可以容纳所有的行组合，在这种情况下，增加缓冲区不能继续提升性能。

问题答案

什么是外联接，什么是内联接？

内联接：INNER JOIN是内联接
内连接中，结果中只包括同时符合WHERE条件及ON条件的行
外联接：LEFT JOIN和RIGHT JOIN都是外联接
外联接中，T2中的行可以不符合ON条件，如果T2中的没有符合ON条件的行，使用NULL代替T2中的列值

MySQL中的JOIN语法与标准SQL语法有什么不同？

MySQL可以将多个表放在括号中，表示多表内联接，如(T2,T3,T4)=(T2 CROSS JOIN T3 CROSS JOIN T3)。标准SQL不支持这么写
MySQL中的CROSS JOIN等同与INNER JOIN。标准SQL中，INNER JOIN可以包含ON条件，CROSS JOIN只能使用WHERE条件

MySQL Join算法有几种？区别是什么？
2种
Nested-Loop Join算法，简称NLJ
Block Nested-Loop Join算法，简称BNL
BNL算法使用缓冲区缓存外层表的数据，以减少内层表的循环查询过程。
NLJ算法的过程是怎样的？
读取第一个表中符合条件的行，将其放入到下层循环中，进入下一层循环处理（检查该行数据是否匹配其他条件），如果有其他要join的表，重复该过程。
BNL算法的过程是怎样的？
将第一个表中符合条件的行中于join相关的列放入到join buffer中
当join bufffer满后，将join buffer放入下层循环中，进入下一层循环处理（检查join buffer中的行数据是否匹配其他条件），如果有其他要join的表，重复该过程。在循环中，返回匹配的行。
处理结束后清空join buffer，对第一个表继续进行循环
循环结束后，检查join buffer是否为空，如果join buffer不为空，检查join buffer中的行数据是否匹配其他条件。

MySQL优化 JOIN语法和JOIN算法