MySQL七种join详解

MySQL七种join详解

关系数据库的join理论

在关系型数据库中，要进行多表查询，一般都会使用join连接。join连接的本质其实类似与集合的交并操作，常见的join连接如下图。

下面先对join连接的原理进行分析，只有在理解了join的原理才能更透彻的明白这其中join的过程，以及如何mysql如何高效的进行join操作。

Join算法原理

MySQL的join算法原理基于嵌套循环查询。但是由于简单的嵌套循环查询存在效率低下，频繁与磁盘进行I/O的问题，故MySQL会分别从减少内层循环次数，以及减少I/O次数两个层面对join的简单嵌套循环查询进行优化。

从本质上来说，MySQL的join算法基于简单嵌套循环查询，即外层表的记录作为条件，循环遍历内层表进行查询，返回内层循环中满足条件的记录。对于SQL语句: select * from c left join b on c.id=b.id。

 

for(i from 外层表){
  for(j from 内层表){
    if(i.id = j.id){
       return;
}
}
}

但是简单的嵌套循环查询效率比较低，本质上是因为每次查询时都是从外层表取出一次记录，即进行一个I/O，并且对内层表的查询是全表扫描，故可以从这两个角度对简单嵌套循环查询进行优化。

内层循环的查询可以通过索引进行优化，通过索引进行查找的次数只为索引B树的高度。这就是索引嵌套循环连接。对于上述的SQL语句:select * from c left join b on c.id=b.id，如果在b表对id字段建立了索引，则外层的循环查询在内层循环中会使用到索引，从而减少了查询的次数。

另外一种优化的方法就是从优化I/O的角度，每次取外层表的多条记录进行缓存，多条记录同时在内层循环中进行查找。

 

七种Join的详解

为了更好的展示下面的例子，下面有两个测试表。

左连接

左连接将左表作为主表，右表作为从表，左表作为外层循环，在右表中进行匹配，如果左表的记录在右表中没有匹配，则将该左表记录的右表项补空值。

该图示用MySQL实现为: select * from class c left join book b on c.id=b.cid;

右连接

右连接将右表作为主表，左表作为从表，右表作为外层循环，在左表中进行匹配，如果右表的记录在左表中没有匹配，则将该右表记录的左表项补空值。

 

该图示用MySQL实现为:select * from class c right join book b on b.cid=b.id;

内连接

内连接将左表和右表对于条件相匹配的项进行组合，在结果中只会出现同时在左表和右表出现的项。

该图示用MySQL实现为:select * from class c inner join book b on c.id=b.cid;

左外连接

左外连接选择将左表作为主表，右表作为从表，循环遍历右表，查找与条件满足的项，如果在右表中没有匹配的项，则补空值，并且在结果集中选择只在左表中存在的数据。

该图示用MySQL实现为:select * from class c left join book b on c.id=b.cid where b.cid is null;

右外连接

右外连接选择将右表作为主表，左表作为从表，循环遍历左表，查找与join条件满足的项，如果在左表中没有匹配的项，则补空值，并且在结果集中选择只在右表中存在的数据。

该图示用MySQL实现为:select * from class c right join book b on b.cid=c.id where c.id is null;

全连接

全外连接将左表和右表的所有记录进行匹配，如果在另外表项中不存在记录，则补空值。

该图示用MySQL实现为:select * from class c left join book b on c.id=b.cid

union

select * from class c right join book b on b.cid=c.id;

全外连接

全外连接将全连接中左右表相交的部分排除。

该图示用MySQL实现为:select * from class c left join book b on c.id=b.cid where b.cid is null

union

select * from class c right join book b on b.cid=c.id where c.id is null;