SQL语句优化之JOIN和LEFTJOIN和RIGHTJOIN语句的优
化
在数据库的应⽤中，我们经常需要对数据库进⾏多表查询，然⽽当数据量⾮常⼤时多表查询会对执⾏效率产⽣⾮常⼤的影响，因此我们在使⽤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) lumn2=5;
将被转换为：
SELECT * FROM t1, t2 lumn2=5 lumn1;
注：因为设置了条件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 {join和in哪个查询更快
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
注：在第⼆个循环才把数据存在join buffer中，这正好印证了上⾯的第4点
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论