连接简介
关系型数据库一个至关重要的概念就是Join（连接）。
连接的本质
从本质上来说，连接就是把各个表中的记录都取出来进行依次匹配，并把匹配后的组合发送给客户端。把t1和t2两个表连接起来的过程如图所示。
这个过程看起来就是把t1表中的记录和t2表中的记录连起来组成一个新的更大的记录，所以这个查询过程称为连接查询。如果连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，那么这样的结果集就可以称为笛卡尔积。
连接过程简介
我们可以连接任意数量的表。但是如果不附加任何限制条件，这些表连接起来产生的笛卡尔积可能是非常巨大的。所以在连接时过滤掉特定的记录组合是有必要的。在连接查询中的过滤条件可以分成下面两种。
- 涉及单表的条件：这种只涉及单表的过滤条件已经在前文提到过了，我们之前也一直称为搜索条件。比如t1.m1 > 1是只针对t1表的过滤条件，t2.n2 < 'd’是只针对t2表的过滤条件。
- 涉及两表的条件：这种过滤条件我们之前没见过，比如t1.m1 = t2.m2、t1.n1 > t2.n2等。这些条件涉及了两个表，稍后我们会详细分析这种过滤条件是如何使用的。
下面我们看一下携带过滤条件的连接查询的大致过程，比如说下面这个查询语句：
SELECT * FROM t1,t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
在这个查询中，我们指明了3个过滤条件：
- t1.m1 > 1
- t1.m1 = t2.m2
- t2.n2 < ‘d’
这个连接查询的执行过程大致如下：
步骤1. 首先确定第一个需要查询的表，这个表称为驱动表。
怎样在单表中执行查询语句已经在前一章中说过了：只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从const、ref、ref_or_null、range、index merge、index、all这些执行方法中选取代价最小的去执行查询即可）。这里假设使用t1作为驱动表，那么就需要到t1表中查找满足t1.m1 > 1的记录。这里采用全表扫描的方式执行单表查询。
步骤2. 步骤1中从驱动表每获取到一条记录，都需要到t2表中查找匹配的记录。
所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以称为被驱动表。步骤1从驱动表中得到了2条记录，也就意味着需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2。
对于从t1表中查询得到的第一条记录，也就是当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2。所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd’这两个过滤条件，然后到t2表中执行单表查询。
对于从t1表中查询得到的第二条记录，也就是当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3。所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd’这两个过滤条件，然后到t2表中执行单表查询。
所以整个连接查询的执行过程如下图所示：
在两表的连接查询中，驱动表只需要访问一次，被驱动表可能需要访问多次。
这里需要强调一下，并不是将所有满足条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表中查询的，而是每获取到一条驱动表的记录，就立即到被驱动表中寻找匹配的记录。
内连接和外连接
针对驱动表中的某条记录，即使在被驱动表中没有找到与之匹配的记录，也仍然需要把该驱动表记录加入到结果集。为了解决这个问题，就有了内连接和外连接的概念。
- 对于内连接的两个表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集，前面提到的连接都是内连接。
- 对于外连接的两个表，即使驱动表中记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
在MySQL中，根据选取的驱动表的不同，外连接可以细分为2种。
- 左连接：选取左侧的表为驱动表
- 右连接：选取右侧的表为驱动表
- WHERE子句中的过滤条件 凡是不符合WHERE子句中过滤条件的记录都不会被加入到最后的结果集。
- ON子句中的过滤条件 对于外连接的驱动表中的记录来说，如果无法在被驱动表中找到匹配的ON子句中的过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段的使用NULL值填充。
需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时是否应该把驱动表记录加入到结果集中这个场景提出的。所以，如果把ON子句放到内连接中，MySQL会把它像WHERE子句一样对待。也就是说，内连接中的WHERE子句和ON子句是等价的。
1、左外连接的语法
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [普通的过滤条件]
其中，中括号里的OUTER单词是可以忽略的。对于LEFT JOIN类型的连接来说，我们把放在左边的表称为外表或者驱动表，放在右边的表称为内表或者被驱动表。所以上述查询语句中，t1就是外表或者驱动表，t2就是内表或者被驱动表。需要注意的是，对于左外连接和右外连接来说，必须使用ON子句来指出连接条件（内连接不必要包含ON子句）
2、右外连接的语法
与上类似
3、内连接语法
内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时，内连接不会把该记录加入到最后的结果集中。这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。
我们前面说过，连接就是把各个表中的记录都取出来依次进行匹配，并把匹配后的组合表送给客户端。无论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，凡是不符合ON子句或WHERE子句中条件的记录都会被过滤掉，也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录都踢出去。所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句连接条件的记录，也会被加入到结果集，此时驱动表和被驱动表的关系就很重要了。也就是说，左外连接和右外连接的驱动表和被驱动表不能轻易互换。
连接的原理
嵌套循环连接
对于两表连接来说，驱动表只会访问一遍，但被驱动表却要被访问好多遍；具体访问几遍取决于对驱动表执行单表查询后的结果集中有多少条记录。对于内连接来说，选取哪个表为驱动表都没关系；而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。
步骤1，选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
步骤2，对步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。
如果有三个表进行连接，那么步骤2中得到的结果集就是新的驱动表，然后第三个表就成为了被驱动表。
这种驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录，的连接执行方式称为嵌套循环连接，这是最简单也是最笨的一种连接算法。
需要注意的是，对于嵌套循环连接算法来说，每当我们从驱动表中得到了一条记录时，就根据这条记录立即到被驱动表中查询一次。如果得到了匹配的记录，就把组合后的记录发送给客户端，然后再到驱动表获取下一条记录；这个过程将重复进行。
使用索引加快连接速度
我们知道，在嵌套循环连接中可能需要访问多次被驱动表。如果访问被驱动表的方式都是全表扫描，那么会很影响性能。我们可以利用索引来加快查询速度。下面查询语句：
SELECT * FROM t1,t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
这个连接查询使用的其实是嵌套循环连接算法。
查询驱动表t1后的结果集中有2条记录，嵌套循环连接算法需要查询被驱动表2次：
- 当t1.m1=2时，查询一遍t2表，对t2表的查询语句相当于：
SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';
- 当t1.m1=3时，再去查询一遍t2表，此时对t2表的查询语句相当于：
SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd';
可以看到，原来的t1.m1=t2.m2这个涉及两个表的过滤条件在针对t2表进行查询时，关于t1表的条件就已经确定了，所以我们只需要优化针对t2表的查询即可。上述两个对t2表的查询语句中利用到的是m2列和n2列，我们可以进行如下尝试
- 在m2列上建立索引。因为针对m2列的条件是等值查找，比如t2.m2=2，t2.m2=3等，所以可能使用到ref访问方法。假设使用ref访问方法来执行对t2表的查询，需要在回表之后再判断t2.n2<d这个条件是否成立。
这里有一个比较特殊的情况，即假设m2列是t2表的主键，或者是不允许存储NULL值的唯一二级索引列，那么使用“t2.m2=常数值”这样的条件从t2表中查找记录时，代价就是常数级别的。我们知道，在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称为const，而在连接查询中对被驱动表的主键或者不允许存储NULL值的唯一二级索引进行等值查找使用的访问方法称为eq_ref。 - 在n2列上建立索引，涉及条件是t2.n2<‘d’，可能用到range访问方法。假设使用range访问方法对t2表进行查询，需要在回表之后再判断包含m2列的条件是否成立。
假设m2和n2列上都存在索引，那么就需要从这两个里面挑一个代价更低的索引来查询t2表。
另外，连接查询的查询列表和过滤条件中有时可能只涉及被驱动表的部分列，而这些列都是某个二级索引的一部分，在这种情况下即使不能使用eq_ref、ref、ref_or_null或者range等访问方法来查询被驱动表，也可以通过扫描全部二级索引记录（即使用index访问方法）来查询驱动表。所以建议最好不要使用* 作为查询列表，而是把真正用到的列作为查询列表。
基于块的嵌套循环连接
现实生活中的表可不像t1、t2这样只有3条记录，成千上万记录都是少的，几百万、几千万甚至几亿条的表到处都是。现在假设我们不能使用索引加快被驱动表的查询过程，所以对于驱动表结果集中的每一条记录，都需要对被驱动表执行全表扫描。这样在被驱动表进行全表扫描时，可能表前面的记录还在内存中，而表后面的记录还在磁盘上。而等到扫描表中后面的记录时，有可能由于内存不足，需要把表前面的记录从内存中释放掉给现在正在扫描的记录腾地方。我们前面强调过，在采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问很多次。如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读这个表好多次，这个IO代价就非常大了。所以我们得想办法，尽量减少被驱动表的访问次数。
通过上面的叙述我们了解到，驱动表结果集中有多少条记录，就可能把被驱动表从磁盘加载到内存中多少次，我们是否可以在把被驱动表中的记录加载到内存时，一次性地与驱动表中的多条记录进行匹配呢？这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以MySQL提出了一个名为Join Buffer（连接缓冲区）的概念。Join Buffer就是在执行连接查询前申请一块固定大小的内存。先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于匹配过程都是在内存中完成的，所以这样可以显著减少被驱动表的IO代价。
最好的情况是Join Buffer足够大，能容纳驱动表结果集中所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。MySQL把这种加入了Join Buffer的嵌套循环连接算法称为基于块的嵌套循环连接（Block Nested-Loop Join）算法。
这个Join Buffer的大小可以通过启动选项或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置128字节。当然，在我们优化对被驱动表的查询时，最好是为被驱动表加上高效率的索引。如果实在不能使用索引，并且自己机器的内存也比较大，则可以尝试调大join_buffer_size的值来对连接查询进行优化。
另外需要注意的是，Join Buffer中并不会存放被驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放到Join Buffer中，所以这也再次提醒我们，最好不要把 * 作为查询条件，只需要把关心的列放到查询列表就好了；这样还可以在Join Buffer中放置更多的记录。
总结
从本质上来说，连接就是把各个表中的记录都取出来依次进行匹配，并把匹配后的组合发送给客户端。如果不加任何过滤条件，产生的结果集就是笛卡尔积。
在MYSQL中，连接分为内连接和外连接，其中外连接又可以分为左外连接和右外连接。内连接和外连接的根本区别就是，在驱动表中的记录不符合ON子句中的连接条件时，内连接不会把该记录加入到最后的结果集中，而外连接会。
嵌套循环连接算法是指驱动表只访问一次，但被驱动表却可能访问多次，访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录，大致过程如下。
步骤1，选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
步骤2，对步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。
由于被驱动表可能会访问很多次，因此可以为被驱动表建立合适的索引以加快查询速度。
如果被驱动表非常大，多次访问被驱动表可能会导致很多次的磁盘IO，此时可以使用基于块的嵌套循环连接算法来缓解由此造成的性能损耗。