Mysql连接的原理

表结构

t1

CREATE TABLE `t1` (
  `m1` int(11) DEFAULT NULL,
  `n1` char(1) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t1`(`m1`, `n1`) VALUES (1, 'a'),(2, 'b'),(3, 'c');

t2

CREATE TABLE `t2` (
  `m2` int(11) DEFAULT NULL,
  `n2` char(1) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t2`(`m2`, `n2`) VALUES (2, 'b'),(3, 'c'),(4, 'd');

嵌套循环连接

对于两张表的连接来说，驱动表只会被访问一遍，但被驱动表却要被访问好多遍；具体访问几遍取决于对驱动表执行单表查询后的结果集中有多少条记录。对于内连接来说选取哪个表作为驱动表都没关系；而外连接的驱动表是固定的。对于t1表和t2表执行内链接的大致过程如下：

• 步骤1. 选取驱动表，适用于驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
• 步骤2. 对步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配到记录。

如果有3个表进行连接，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，然后重复上面的过程。也就是针对步骤2中得到的结果集中的每一条记录都需要到t3表中找一找有没有匹配到记录。

这个过程就像是一个嵌套的循环，所以这种 “驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录” 的连接执行方式称为嵌套循环连接（Nested-Loop Join），这是最简单也是最笨拙的一种连接查询算法。

需要注意的是，对于嵌套循环连接算法来说，每当我们从驱动表中得到一条记录时，就根据这条记录立即到被驱动表中查询一次，如果得到了匹配到记录，就把组合后到记录发送给客户端，然后再到驱动表中获取下一条记录；这个过程将重复进行。上面说到的 “结果集“ 是一个抽象的概念，并不是把驱动表中所有的记录都先查出来放到某个地方（比如内存或者磁盘中），然后再遍历被驱动表。

使用索引加快连接速度

我们知道，在嵌套循环连接中肯能需要访问多次被驱动表。如果访问被驱动表的方式都是全表扫描，那得要扫描好多次！查询t2表其实就相当于一次单表查询，我们可以利用索引来加快查询速度。如下内链接的例子：

select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';

这个连接查询使用的其实是嵌套循环连接算法，首先查询驱动表t1后的结果集中有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条记录，成千上万条记录都是少的，几百万、几千万甚至几亿条记录到处都是。现在假设我们不能使用索引加快被驱动表的查询过程，所以对于驱动表结果集中的每一条记录，都需要对被驱动表执行全表扫描。这样在对被驱动表进行全表扫描时，可能前面的记录还在内存中，而表后面的记录还在磁盘上。而等到扫描表中后面的记录时，有可能由于内存不足，需要把表前面的记录从内存中释放掉给现在正在扫描的记录腾地方。前面强调过，在采用嵌套循环连接算法的两表连接过程中，被驱动表可是要访问好多次。如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读这个表好多次，这个I/O的代价就太大了。所以我们得想想办法，尽量减少被驱动表的访问次数。

通过上面的叙述我们了解到，驱动表结果集中有多少条记录，就可能把被驱动表从磁盘加载到内存多少次。我们是否可以在把被驱动表中的记录加载到内存时，一次性地与驱动表中的多条记录进行匹配呢？这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以设计MySql的大叔提出了一个名为Join Buffer（连接缓冲区）的概念。Join Buffer就是在执行连接查询前申请的一块固定大小的内存。先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动的表，每一条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。

最好的情况是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中放置更多的记录。

Join Buffer