mysql join算法_MySQL优化 JOIN语法和JOIN算法

本文问题

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

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中的行数据是否匹配其他条件。