共计 2394 个字符，预计需要花费 6 分钟才能阅读完成。
行业资讯
数据库
SQL 语句优化之 JOIN 和 LEFT JOIN 和 RIGHT JOIN 语句的示例分析
丸趣 TV 小编给大家分享一下 SQL 语句优化之 JOIN 和 LEFT JOIN 和 RIGHT JOIN 语句的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
在数据库的应用中，我们经常需要对数据库进行多表查询，然而当数据量非常大时多表查询会对执行效率产生非常大的影响，因此我们在使用 JOIN 和 LEFT JOIN 和 RIGHT JOIN 语句时要特别注意；
SQL 语句的 join 原理：
数据库中的 join 操作，实际上是对一个表和另一个表的关联，而很多错误理解为，先把这两个表来一个迪卡尔积，然后扔到内存，用 where 和 having 条件来慢慢筛选，其实数据库没那么笨的，那样会占用大量的内存，而且效率不高，比如，我们只需要的一个表的一些行和另一个表的一些行，如果全表都做迪卡尔积，这开销也太大了，真正的做法是，根据在每一个表上的条件，遍历一个表的同时，遍历其他表，找到满足最后的条件后，就发送到客户端，直到最后的数据全部查完，叫做嵌套循环查询。
1、LEFT JOIN 和 RIGHT JOIN 优化
在 MySQL 中，实现如 A LEFT JOIN B join_condition 如下:
1、表 B 依赖赖与表 A 及所有 A 依赖的表
2、表 A 依赖于所有的表，除了 LEFT JOIN 的表(B)
3、join_condition 决定了怎样来读取表 B，where 条件对 B 是没有用的
4、标准的 where 会和 LEFT JOIN 联合优化
5、如果在 A 中的一行满足 where 和 having 条件，B 中没有，会被填充 null
RIGHT JOIN 与 LEFT JOIN 类似，这个位置是可以互换的
LEFT JOIN 与 正常 JOIN 之间的转换原则上当 where 条件，对于生成的 null 行总返回 false 时，可以直接转化为正常的 join
如：
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;将被转换为：
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;注：因为设置了条件 t2.column2 = 5, 那么对于所有的生成的 t2 为 null 的行都是不成立的
这样的优化将非常快速，因为这样相当于把外连接转换为等值连接，少了很多行的扫描和判断。
嵌套循环 JOIN 算法 —-Nested-Loop Join
简单的嵌套循环算法就是从一个表开始，通过对表的条件找到一行，然后找下一个表的数据，找完后，又回到第一个表来寻找满足条件的行
例如，有三个表 t1, t2, t3，他们的 join 类型为：
Table Join Type
t1 range
t2 ref
t3 ALL最终生成的伪代码为
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
}
}
}即，t1 表通过范围扫描，t2 关联 t1，t3 为全表扫描
注：先根据对 t1 表的条件范围找到一行，和 t2 匹配，然后寻找 t3 的满足条件的行
块嵌套循环 JOIN 算法 —- Block Nested-Loop Join
这个算法的应用为：由于之前的嵌套算法每读一个表的一行后，就会读下表，这样内部的表会被读很多次，所以，数据库利用了 join 缓存 (join buffer) 来存储中间的结果，然后读取内部表的时候，找到一行，都和这个缓存中的数据比较，以此来提高效率。例如：一次从外表读 10 行，然后读内部表时，都和这 10 行数据进行比较。
MySQL 使用 join buffer 的条件为：
1、join_buffer_size 系统变量决定了每个 join 使用的 buffer 大小
2、join 类型为 index 或 all 时，join buffer 才能被使用
3、每一个 join 都会分配一个 join buffer，即一个 sql 可能使用多个 join buffer
4、join buffer 不会分配给第一个非常量表
5、只有需要引用的列会被放到 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
这里将 t1 和 t2 使用的列存到 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 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
}
}
}注：在第二个循环才把数据存在 join buffer 中，这正好印证了上面的第 4 点
看完了这篇文章，相信你对“SQL 语句优化之 JOIN 和 LEFT JOIN 和 RIGHT JOIN 语句的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
