连接查询就MySQL而言，其连接查询主要包括内连接查询和外连接查询。具体来说，外连接查询可以分为左外连接查询和右外连接查询。这里，为了方便介绍各种连接查询的语法和功能。我们先创建一个学生信息表stu_info，并往里面插入一些数据供我们后面的演示--创建一个学生信息表createtablestu_info(idintnotnullcomment'studentnumber',namevarchar(30)nullcomment'name',majorvarchar(30)nullcomment'专业',primarykey(id))comment='学生信息';--插入记录insertintostu_info(id,name,major)value(1,'小明','计算机'),(2,'大王','计算机'),(3,'张飞','公共行政'),(4,'顾佳','化学');然后，建A学生成绩表stu_score，插入记录数据createtablestu_score(idintcomment'学号',coursevarchar(255)comment'course',scoreintnullcomment'score',primarykey(id,course))comment='学生成绩';插入stu_score(id,course,score)value(2,'C语言',98),(2,'Java语言',100),(2,'编译原理',55),(4,'有机化学',41),(4,'无机化学',44),(8,'量子力学',38);至此两个表中的数据如下。可以看到两个表中的记录是通过学号字段连接起来的，关联innerjoin内连接查询的SQL语法如下。它可以在on子句中指定两张表的连接条件，在where子句中设置查询条件。之所以叫内连接查询，是因为它只会查询在两个表中都能找到的记录，连接条件是select<要查询的字段名>from<表1的表名>[inner]joinon[connectioncondition]where[querycondition]现在，我们希望查询学生信息和对应的科目成绩。那么SQL语句如下--Innerjoinqueryselect*fromstu_infoinnerjoinstu_scoreonstu_info.id=stu_score.id;查询结果如下。可以看到信息表stu_info中的小明（学号1）和张飞（学号3）的信息都没有被查询到。同样，结果表stu_score中id号为8的学生信息也没有查到。因为上面的id学号在另外一张表中没有对应的记录。所谓innerjoin其实就相当于查询两个表的交集。同时可以在where子句中进一步过滤join查询结果select*fromstu_infoinnerjoinstu_scoreonstu_info.id=stu_score.idwherestu_info.id>2;查询结果如下左（外）连接左外连接，又称左连接。其SQL语法如下所示。它与innerjoin的区别在于，对于左表（即表1），即使根据join条件在右表（即表2）中没有找到匹配的记录，也会进行查询，而在result表2涉及的字段值将被NULL值填充select<要查询的字段名>from<表1的表名>leftjoin<表2的表名>on[连接条件]where[querycondition]下面我们来实际验证查询SQL是这样的--leftouterjoinqueryselect*fromstu_infoleftjoinstu_scoreonstu_info.id=stu_score.id;查询结果如下，可以看到小明（学号为1）在stu_score中没有对应的分数记录，但是查询结果还是会显示的，只是stu_score表的字段填充了NULL值。对于where子句，其作用与内连接无异，用于对连接查询的结果进行进一步过滤select*fromstu_infoleftjoinstu_scoreonstu_info.id=stu_score.idwherestu_info.id>2;查询结果如下，符合预期。外连接是相似的。对于右表（即表2），即使根据join条件在左表（即表1）中没有找到匹配的记录，也会进行查询，但是涉及表1的字段值结果中会用NULL值来填充。也就是说，方向相反。可以看出，左连接查询和右连接查询是可以相互转化的，只是表名的位置刚好相反。它的SQL语法如下：select<要查询的字段名>from<表1的表名>rightjoin<表2的表名>on[连接条件]where[查询条件]下面我们来实际验证一下，查询SQL如下--rightouterjoinqueryselect*fromstu_inforightjoinstu_scoreonstu_info.id=stu_score.id;查询结果如下，可以看到右边stu_score表中id为学号8的记录虽然不能在左边表中找到对应的学生信息记录，但是最终还是会显示出来查询结果。现在让我们看一下where子句的过滤功能。SQL查询语句如下：select*fromstu_inforightjoinstu_scoreonstu_info.id=stu_score.idwherestu_score.id>2;查询结果如下，符合预期的查询算法Nested-LoopJoin(NLJ)嵌套循环连接算法对于连接查询，两张表的角色分别是驱动表和被驱动表。具体来说，对于外连接（左外连接查询，右外连接查询），表的作用是固定的。即左外连接查询下的驱动表为左表，右外连接查询下的驱动表为右表。对于内连接查询，表的作用不固定，驱动表可以是左表也可以是右表。对于join查询，最基本的实现算法就是所谓的NLJnestedloopjoin。算法的基本流程如下：确定驱动表，只使用与驱动表相关的查询条件，选择合适的单表访问方式对其进行单表查询。第一步，每次从驱动表中获取一条数据。记录，会选择合适的单表访问方式结合被驱动表相关的查询条件，对被驱动表进行单表查询使用伪代码描述如下，这也是该算法命名为“循环”的原因嵌套"t1匹配范围内每一行的原因{fort2匹配范围内每一行{如果行满足连接条件，发送给客户端}}下面我们分析一下外连接查询的具体SQLselect*fromstu_infoleftjoinstu_scoreonstu_info.id=stu_score.idwherestu_info.id>1andstu_score.score<90本次连接查询，以学生信息表stu_info为驱动表，以学生成绩表stu_score为从动表。我们先对驱动表stu_info进行单表查询。涉及到的查询条件只有stu_info.id>1。单表查询的结果如下图所示。每当从stu_info表中查到一条数据记录，即则去stu_score表中进行单表查询。具体来说，这里我们以从stu_info表中获取到King（学号id字段为2）的数据记录为例进行分析说明。此时与驱动表相关的查询条件为stu_score.score<90。由于king学号为2，连接条件stu_info.id=stu_score.id进一步修改为stu_score.id=2，驱动表单表查询结果如下图所示。对于学号分别为3和4的张飞和顾佳，驱动表下的单表查询过程类似。我不会在这里详细介绍。可以看出，对于NLJ嵌套循环连接算法，虽然对驱动表只有一次查询，但是会对驱动表进行多次单表查询，所以算法的效率比较高。Lowindex-basedqueryoptimization我们前面提到，对于NLJ嵌套循环连接算法，它会以表为驱动，进行多个单表查询。一个比较容易想到的优化方案是在驱动表上建立相关索引，避免单表查询使用allaccess方式扫描全表。值得一提的是，当驱动表使用聚簇索引或唯一二级索引进行非NULL值的等价查询时，这种访问方式称为eq_ref，而不是我们以前在单表查询中引入的constBlockNested-LoopJoin(BNL)block-basednestedloopjoin前面我们提到，我们需要对驱动表进行多个单表查询。在单表查询过程中，如果被驱动表的记录数过多，无法一次性将硬盘中的所有记录加载到内存中，则需要经过硬盘加载和查询匹配过程多次。仍然以下面的左外连接查询为例，这里我们假设stu_info和stu_score中有很多数据。对driver表进行单表查询时，假设有10条满足查询条件的学生信息数据记录。stu_score一共有100条数据记录。由于内存不足，一次只能从硬盘加载25个学生成绩数据到内存。那么如果使用NLJ算法，对于从动表的记录来说，将从动表的记录从硬盘加载到内存中大约需要10*(100/25)=40次select*fromstu_infoleftjoinstu_scoreonstu_info.id=stu_score.idwherestu_info.id>1andstu_score.score<90为了解决从动表单表查询过程中的NLJ算法，如果从动表记录过多，则需要频繁读取硬盘和加载数据造成的性能损失。MySQL提供了一种基于块的嵌套循环连接（BlockNested-LoopJoin，BNL）算法。基本思路也很简单，先在内存中申请一块区域，叫做joinbuffer。算法流程如下：将从驱动表中查询到的数据记录依次存储在joinbuffer中，直到内存区满为止。对于从动表中的每条数据记录，一次与joinbuffer中的多个驱动表合并。数据记录匹配查询。重复上述步骤1~2的过程，直到处理完驱动表中查询到的所有数据记录。可以看出，BNL算法可以大大减少被驱动表重复加载到内存的次数，提高连接性。查询效率。这里还是用上面的案例来进行量化分析，假设当前的joinbuffermemory一次最多可以存储5条driver表的记录数据，即学生信息记录。但是stu_score中还有100条数据记录，目前只能从硬盘加载到内存中的学生成绩数据只有25条。那么，在使用BNL算法的情况下，将被驱动表的记录从硬盘加载到内存大约需要(10/5)*(100/25)=8次。因此，在被驱动表记录数较多且机器内存允许的情况下，可以通过BNL算法优化连接查询的效率。具体的，可以通过系统变量join_buffer_size（单位：Byte）来调整joinbuffer内存区域的大小。
