SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化
在数据库的应用中，我们经常需要对数据库进行多表查询，然而当数据量非常大时多表查询会对执行效率产生非常大的影响，因此我们在使用JOIN和LEFTJOIN和RIGHTJOIN语句时要特别注意；
SQL语句的join原理：
数据库中的join操作，实际上是对一个表和另一个表的关联，而很多错误理解为，先把这两个表来一个迪卡尔积，然后扔到内存，用where和having条件来慢慢筛选，其实数据库没那么笨的，那样会占用大量的内存，而且效率不高，比如，我们只需要的一个表的一些行和另一个表的一些行，如果全表都做迪卡尔积，这开销也太大了，真正的做法是，根据在每一个表上的条件，遍历一个表的同时，遍历其他表，找到满足最后的条件后，就发送到客户端，直到最后的数据全部查完，叫做嵌套循环查询。
1、LEFTJOIN和RIGHTJOIN优化
在MySQL中，实现如ALEFTJOINBjoin_condition如下:
1、表B依赖赖与表A及所有A依赖的表
2、表A依赖于所有的表，除了LEFTJOIN的表(B)
3、join_condition决定了怎样来读取表B，where条件对B是没有用的
4、标准的where会和LEFTJOIN联合优化
5、如果在A中的一行满足where和having条件，B中没有，会被填充null
RIGHTJOIN与LEFTJOIN类似，这个位置是可以互换的
LEFTJOIN与正常JOIN之间的转换原则上当where条件，对于生成的null行总返回false时，可以直接转化为正常的join
如：
SELECT*FROMt1LEFTJOINt2ON(column1)WHEREt2.column2=5;
将被转换为：
SELECT*FROMt1,t2WHEREt2.column2=5ANDt1.column1=t2.column1;
注：因为设置了条件t2.column2= 5,那么对于所有的生成的t2为null的行都是不成立的
这样的优化将非常快速，因为这样相当于把外连接转换为等值连接，少了很多行的扫描和判断。
嵌套循环JOIN算法----Nested-LoopJoin
简单的嵌套循环算法就是从一个表开始，通过对表的条件找到一行，然后找下一个表的数据，找完后，又回到第一个表来寻找满足条件的行
例如，有三个表t1,t2,t3，他们的join类型为：
TableJoinType t1range t2ref t3ALL
最终生成的伪代码为
foreachrowint1matchingrange{
foreachrowint2matchingreferencekey{
foreachrowint3{
ifrowsatisfiesjoinconditions,
sendtoclient
}
}
}
即，t1表通过范围扫描，t2关联t1，t3为全表扫描
注：先根据对t1表的条件范围找到一行，和t2匹配，然后寻找t3的满足条件的行
块嵌套循环JOIN算法----BlockNested-LoopJoin
这个算法的应用为：由于之前的嵌套算法每读一个表的一行后，就会读下表，这样内部的表会被读很多次，所以，数据库利用了join缓存(joinbuffer)来存储中间的结果，然后读取内部表的时候，找到一行，都和这个缓存中的数据比较，以此来提高效率。例如：一次从外表读10行，然后读内部表时，都和这10行数据进行比较。
MySQL使用joinbuffer的条件为：
1、join_buffer_size系统变量决定了每个join使用的buffer大小
2、join类型为index或all时，joinbuffer才能被使用
3、每一个join都会分配一个joinbuffer，即一个sql可能使用多个joinbuffer
4、joinbuffer不会分配给第一个非常量表
5、只有需要引用的列会被放到joinbuffer中，不是整行
最终生成伪代码为：
foreachrowint1matchingrange{
foreachrowint2matchingreferencekey{
storeusedcolumnsfromt1,t2injoinbuffer
这里将t1和t2使用的列存到joinbuffer中
ifbufferisfull{
foreachrowint3{
foreacht1,t2combinationinjoinbuffer{
ifrowsatisfiesjoinconditions,
sendtoclient
}
}
emptybuffer
}
}
}
ifbufferisnotempty{
foreachrowint3{
foreacht1,t2combinationinjoinbuffer{
ifrowsatisfiesjoinconditions,
sendtoclient
}
}
}
注：在第二个循环才把数据存在joinbuffer中，这正好印证了上面的第4点