MySQL原理之查询篇

一、单表访问方法

对于我们这些MySQL的使用者来说，MySQL其实就是一个软件，平时用的最多的就是查询功能。DBA时不时丢过来一些慢查询语句让优化，我们如果连查询是怎么执行的都不清楚还优化个毛线，所以是时候掌握真正的技术了。我们在启蒙章的时候就曾说过，MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。不过查询优化这个主题有点儿大，在学会跑之前还得先学会走，所以本章先来瞅瞅MySQL怎么执行单表查询（就是FROM子句后边只有一个表，最简单的那种查询～）。

为了故事的顺利发展，我们先得有个表：

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

我们为这个single_table表建立了1个聚簇索引和4个二级索引，分别是：

• 为id列建立的聚簇索引。
• 为key1列建立的idx_key1二级索引。
• 为key2列建立的idx_key2二级索引，而且该索引是唯一二级索引。
• 为key3列建立的idx_key3二级索引。
• 为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是一个联合索引。

然后我们需要为这个表插入10000行记录，除id列外其余的列都插入随机值就好了，具体的插入语句我就不写了，自己写个程序插入吧（id列是自增主键列，不需要我们手动插入）。

我们平时所写的那些查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则，至于MySQL背地里是怎么把查询结果搞出来的那是MySQL自己的事儿。对于单个表的查询来说，设计MySQL的大叔把查询的执行方式大致分为下边两种：

• 使用全表扫描进行查询

  这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是啥查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。

• 使用索引进行查询

  因为直接使用全表扫描的方式执行查询要遍历好多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式五花八门，又可以细分为许多种类：

  • 针对主键或唯一二级索引的等值查询
  • 针对普通二级索引的等值查询
  • 针对索引列的范围查询
  • 直接扫描整个索引

设计MySQL的大叔把MySQL执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能差老鼻子远了。下边细细道来各种访问方法的具体内容。

1.1访问方法的分类

1.1.1const

有的时候我们可以通过主键列来定位一条记录，比方说这个查询：

SELECT * FROM single_table WHERE id = 1438;

MySQL会直接利用主键值在聚簇索引中定位对应的用户记录，就像这样：

原谅我把聚簇索引对应的复杂的B+树结构搞了一个极度精简版，为了突出重点，我们忽略掉了页的结构，直接把所有的叶子节点的记录都放在一起展示，而且记录中只展示我们关心的索引列，对于single_table表的聚簇索引来说，展示的就是id列。我们想突出的重点就是：B+树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树本来就是一个矮矮的大胖子，所以这样根据主键值定位一条记录的速度贼快。类似的，我们根据唯一二级索引列来定位一条记录的速度也是贼快的，比如下边这个查询：

SELECT * FROM single_table WHERE key2 = 3841;

可以看到这个查询的执行分两步，第一步先从idx_key2对应的B+树索引中根据key2列与常数的等值比较条件定位到一条二级索引记录，然后再根据该记录的id值到聚簇索引中获取到完整的用户记录。

设计MySQL的大叔认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是像坐火箭一样快的，所以他们把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（==这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录==）。

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，比如这样：

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说上边这个语句不可以使用const访问方法来执行（最多只能使用ref的访问方法）。

1.1.2ref

有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：

SELECT * FROM single_table WHERE key1 = 'abc';

对于这个查询，我们当然可以选择全表扫描来逐一对比搜索条件是否满足要求，我们也可以先使用二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于==等值匹配到的二级索引记录条数==。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。设计MySQL的大叔就把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。我们看一下采用ref访问方法执行查询的图示：

从图示中可以看出，对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const差了那么一丢丢，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了），跟坐高铁差不多。不过需要注意下边两种情况：

• 二级索引列值为NULL的情况

  不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

• 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法，比方说下边这几个查询：

  SELECT * FROM single_table WHERE key_part1 = 'god like';
  SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
  SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

  但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样(这个就可以看作是后面说的range访问方法了)：

  SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

1.1.3ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null，这个ref_or_null访问方法的执行过程如下：

可以看到，上边的查询相当于先分别从idx_key1索引对应的B+树中找出key1 IS NULL和key1 = 'abc'的两个连续的记录范围，然后根据这些二级索引记录中的id值再回表查找完整的用户记录。

值为NULL的记录会被放在索引的最左边，ref_or_null访问方法只比ref访问方法多扫描了一些值为NULL的二级索引记录。

1.1.4range

我们之前介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到（ref_or_null比较奇特，还计算了值为NULL的情况），但是有时候我们面对的搜索条件更复杂，比如下边这个查询：

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

如果使用idx_key2执行该查询，那么对应的扫描区间就是[1438,1438]、[6328,6328]以及[38,79]，设计MySQL的大叔把这种“使用索引查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间”的访问方法称之为range（仅包含一个单点扫描区间的访问方法不能成为range访问方法，扫描区间为[-∞，+∞]的访问方法也不能成为range访问方法）。

1.1.5index

如果我们使用不了上面说的访问方法，但是SQL语句比较特殊，其查询列表和过滤条件中涉及的列都是某个索引的一部分，看下边这个查询：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：

• 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
• 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之为：index。

1.1.6all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为：all。

1.2重温二级索引+回表

一般情况下只能利用单个二级索引执行查询，比方说下边的这个查询：

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查询优化器会识别到这个查询中的两个搜索条件：

• key1 = 'abc'
• key2 > 1000

优化器一般会根据single_table表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。一般来说，等值查找比范围查找需要扫描的行数更少（也就是ref的访问方法一般比range好，但这也不总是一定的，也可能采用ref访问方法的那个索引列的值为特定值的行数特别多），所以这里假设优化器决定使用idx_key1索引进行查询，那么整个查询过程可以分为两个步骤：

• 步骤1：使用二级索引定位记录的阶段，也就是根据条件key1 = 'abc'从idx_key1索引代表的B+树中找到对应的二级索引记录。
• 步骤2：回表阶段，也就是根据上一步骤中找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2 > 1000到完整的用户记录继续过滤，将最终符合过滤条件的记录返回给用户。
• 步骤3：再根据该记录所在的单向链表找到下一条二级索引记录，重复步骤2的操作，直到某条二级索引记录不满足key1=”abc”条件为止

需要注意的是，我们说一般情况下执行一个查询只会用到单个二级索引，不过还是有特殊情况的例如索引合并。从上文可以看出，每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间的二级索引记录的主键值是无序的，也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。每次执行回表操作时都相当于随机读取一个聚簇索引页面，而这些随机IO带来的开销很大。于是设计MySQL的大叔提出了一种名为Disk-Sweep Multi-Range Read（MRR，多范围读取）的优化措施，即先读取一部分二级索引，将它们的主键值排好序后再统一执行回表操作。相对于原来每读取一条二级索引记录就立即执行回表操作，这样会节省一些IO开销，当然使用这个MRR优化措施的条件比较苛刻。

1.3明确range访问方法使用的范围区间

其实对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的区间。

LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引，具体原因我们在前边的章节中唠叨过了，这里就不赘述了。
IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的：

SELECT * FROM single_table WHERE key2 IN (1438, 6328);

SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

不过在日常的工作中，一个查询的WHERE子句可能有很多个小的搜索条件，这些搜索条件需要使用AND或者OR操作符连接起来，当我们想使用range访问方法来执行一个查询语句时，重点就是找出该查询可用的索引以及这些索引对应的范围区间。下边分两种情况看一下怎么从由AND或OR组成的复杂搜索条件中提取出正确的范围区间。

1.3.1所有搜索条件都可以使用某个索引的情况

有时候每个搜索条件都可以使用到某个索引，比如下边这个查询语句：

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

这个查询中的搜索条件都可以使用到key2，也就是说每个搜索条件都对应着一个idx_key2的范围区间。这两个小的搜索条件使用AND连接起来，也就是要取两个范围区间的交集，在我们使用range访问方法执行查询时，使用的idx_key2索引的范围区间的确定过程就如下图所示：

key2 > 100和key2 > 200交集当然就是key2 > 200了，也就是说上边这个查询使用idx_key2的范围区间就是(200, +∞)。这东西小学都学过吧，再不济初中肯定都学过。我们再看一下使用OR将多个搜索条件连接在一起的情况：

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

OR意味着需要取各个范围区间的并集，所以上边这个查询在我们使用range访问方法执行查询时，使用的idx_key2索引的范围区间的确定过程就如下图所示：

也就是说上边这个查询使用idx_key2的范围区间就是(100， +∞)。

1.3.2有的搜索条件无法使用索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

请注意，这个查询语句中能利用的索引只有idx_key2一个，而idx_key2这个二级索引的记录中又不包含common_field这个字段，所以在使用二级索引idx_key2定位记录的阶段用不到common_field = 'abc'这个条件，这个条件是在回表获取了完整的用户记录后才使用的，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑common_field = 'abc'这个条件，我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。

之所以把用不到索引的搜索条件替换为TRUE，是因为我们不打算使用这些条件进行在该索引上进行过滤，所以不管索引的记录满不满足这些条件，我们都把它们选取出来，待到之后回表的时候再使用它们过滤。

我们把上边的查询中用不到idx_key2的搜索条件替换后就是这样：

SELECT * FROM single_table WHERE key2 > 100 AND TRUE;

化简之后就是这样：

SELECT * FROM single_table WHERE key2 > 100;

也就是说最上边那个查询使用idx_key2的范围区间就是：(100, +∞)。

再来看一下使用OR的情况：

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

同理，我们把使用不到idx_key2索引的搜索条件替换为TRUE：

SELECT * FROM single_table WHERE key2 > 100 OR TRUE;

接着化简：

SELECT * FROM single_table WHERE TRUE;

额，这也就说说明如果我们强制使用idx_key2执行查询的话，对应的范围区间就是(-∞, +∞)，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。

1.3.3复杂搜索条件下找出范围匹配的区间

有的查询的搜索条件可能特别复杂，光是找出范围匹配的各个区间就挺烦的，比方说下边这个：

SELECT * FROM single_table WHERE 
        (key1 > 'xyz' AND key2 = 748 ) OR
        (key1 < 'abc' AND key1 > 'lmn') OR
        (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

我滴个神，这个搜索条件真是绝了，不过大家不要被复杂的表象迷住了双眼，按着下边这个套路分析一下：

• 首先查看WHERE子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引。

  这个查询的搜索条件涉及到了key1、key2、common_field这3个列，然后key1列有普通的二级索引idx_key1，key2列有唯一二级索引idx_key2。

• 对于那些可能用到的索引，分析它们的范围区间。

  • 假设我们使用idx_key1执行查询

    • 我们需要把那些用不到该索引的搜索条件暂时移除掉，移除方法也简单，直接把它们替换为TRUE就好了。上边的查询中除了有关key2和common_field列不能使用到idx_key1索引外，key1 LIKE '%suf'也使用不到索引，所以把这些搜索条件替换为TRUE之后的样子就是这样：

      (key1 > 'xyz' AND TRUE ) OR
      (key1 < 'abc' AND key1 > 'lmn') OR
      (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))

      化简一下上边的搜索条件就是下边这样：

      (key1 > 'xyz') OR
      (key1 < 'abc' AND key1 > 'lmn') OR
      (key1 > 'zzz')

    • 替换掉永远为TRUE或FALSE的条件

      因为符合key1 < 'abc' AND key1 > 'lmn'永远为FALSE，所以上边的搜索条件可以被写成这样：

      (key1 > 'xyz') OR (key1 > 'zzz')

    • 继续化简区间

      key1 > 'xyz'和key1 > 'zzz'之间使用OR操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是：key1 > xyz。也就是说：上边那个有一坨搜索条件的查询语句如果使用 idx_key1 索引执行查询的话，需要把满足key1 > xyz的二级索引记录都取出来，然后拿着这些记录的id再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。

  • 假设我们使用idx_key2执行查询

    • 我们需要把那些用不到该索引的搜索条件暂时使用TRUE条件替换掉，其中有关key1和common_field的搜索条件都需要被替换掉，替换结果就是：

      (TRUE AND key2 = 748 ) OR
      (TRUE AND TRUE) OR
      (TRUE AND TRUE AND (key2 < 8000 OR TRUE))

      哎呀呀，key2 < 8000 OR TRUE的结果肯定是TRUE呀，也就是说化简之后的搜索条件成这样了：

      key2 = 748 OR TRUE

      这个化简之后的结果就更简单了：

      TRUE

      这个结果也就意味着如果我们要使用idx_key2索引执行查询语句的话，需要扫描idx_key2二级索引的所有记录，然后再回表，这不是得不偿失么，所以这种情况下不会使用idx_key2索引的。

1.4索引合并

我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但不是还有特殊情况么，在这些特殊情况下也可能在一个查询中使用到多个二级索引，设计MySQL的大叔把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

1.4.1Intersection合并

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

假设这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：

• 从idx_key1二级索引对应的B+树中取出key1 = 'a'的相关记录。
• 从idx_key3二级索引对应的B+树中取出key3 = 'b'的相关记录。
• 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。
• 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

这里有同学会思考：为啥不直接使用idx_key1或者idx_key3只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？这里要分析一下两种查询执行方式之间需要的成本代价。

只读取一个二级索引的成本：

• 按照某个搜索条件读取一个二级索引
• 根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：

• 按照不同的搜索条件分别读取不同的二级索引
• 将从多个二级索引得到的主键值取交集，然后进行回表操作

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

• 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

  比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Intersection索引合并的操作：

  SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

  而下边这两个查询就不能进行Intersection索引合并：

  SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
  SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

  第一个查询是因为对key1进行了范围匹配，第二个查询是因为联合索引idx_key_part中的key_part2和key_part3列并没有出现在搜索条件中，所以这两个查询不能进行Intersection索引合并。

• 情况二：主键列可以是范围匹配

  比方说下边这个查询可能用到主键和idx_key1进行Intersection索引合并的操作：

  SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

  为啥呢？凭啥呀？突然冒出这么两个规定让大家一脸懵逼，下边我们慢慢品一品这里头的玄机。这话还得从InnoDB的索引结构说起，你要是记不清麻烦再回头看看。对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

  so？还是没看懂根据二级索引查询出的结果集是按照主键值排序的对使用Intersection索引合并有啥好处？小伙子，别忘了Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很easy啦。假设某个查询使用Intersection索引合并的方式从idx_key1和idx_key2这两个二级索引中获取到的主键值分别是：

  • 从idx_key1中获取到已经排好序的主键值：1、3、5
  • 从idx_key2中获取到已经排好序的主键值：2、3、4

  那么求交集的过程就是这样：逐个取出这两个结果集中最小的主键值，如果两个值相等，则加入最后的交集结果中，否则丢弃当前较小的主键值，再取该丢弃的主键值所在结果集的后一个主键值来比较，直到某个结果集中的主键值用完了，如果还是觉得不太明白那继续往下看：

  • 先取出这两个结果集中较小的主键值做比较，因为1 < 2，所以把idx_key1的结果集的主键值1丢弃，取出后边的3来比较。
  • 因为3 > 2，所以把idx_key2的结果集的主键值2丢弃，取出后边的3来比较。
  • 因为3 = 3，所以把3加入到最后的交集结果中，继续两个结果集后边的主键值来比较。
  • 后边的主键值也不相等，所以最后的交集结果中只包含主键值3。

  别看我们写的啰嗦，这个过程其实可快了，时间复杂度是O(n)，但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就比较耗时了。

  按照有序的主键值去回表取记录有个专有名词儿，叫：Rowid Ordered Retrieval，简称ROR，以后大家在某些地方见到这个名词儿就眼熟了。

  另外，不仅是多个二级索引之间可以采用Intersection索引合并，索引合并也可以有聚簇索引参加，也就是我们上边写的情况二：在搜索条件中有主键的范围匹配的情况下也可以使用Intersection索引合并索引合并。为啥主键这就可以范围匹配了？还是得回到应用场景里，比如看下边这个查询：

  SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;

  假设这个查询可以采用Intersection索引合并，我们理所当然的以为这个查询会分别按照id > 100这个条件从聚簇索引中获取一些记录，在通过key1 = 'a'这个条件从idx_key1二级索引中获取一些记录，然后再求交集，其实这样就把问题复杂化了，没必要从聚簇索引中获取一次记录。别忘了二级索引的记录中都带有主键值的，所以可以在从idx_key1中获取到的主键值上直接运用条件id > 100过滤就行了，这样多简单。所以涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录罢了，是不是等值匹配不重要。

当然，上边说的情况一和情况二只是发生Intersection索引合并的必要条件，不是充分条件。也就是说即使情况一、情况二成立，也不一定发生Intersection索引合并，这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

1.4.2Union合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比方说这样：

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：

• 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

  比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作：

  SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

  而下边这两个查询就不能进行Union索引合并：

  SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');
  SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

  第一个查询是因为对key1进行了范围匹配，第二个查询是因为联合索引idx_key_part中的key_part2和key_part3列并没有出现在搜索条件中，所以这两个查询不能进行Union索引合并。

• 情况二：主键列可以是范围匹配

• 情况三：使用Intersection索引合并的搜索条件

  这种情况其实也挺好理解，就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取并集，比方说这个查询：

  SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

  优化器可能采用这样的方式来执行这个查询：

  • 先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。
  • 再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引idx_key_part中得到另一个主键集合。
  • 采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

当然，查询条件符合了这些情况也不一定就会采用Union索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行回表访问的代价比全表扫描更小时才会使用Union索引合并。

1.4.3Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a'从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > 'z'从idx_key3索引中获取的二级索引记录的主键值也不是排好序的，但是key1 < 'a'和key3 > 'z'这两个条件又特别让我们动心，所以我们可以这样：

• 先根据key1 < 'a'条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序
• 再根据key3 > 'z'条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序
• 因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

我们把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？是的，的确没有Sort-Intersection索引合并这么一说， Sort-Union索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高。
而Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，但是如果加入Sort-Intersection后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入Sort-Intersection这个玩意儿。

1.4.4联合索引替代Intersection索引合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_key1和idx_key3是两个单独的B+树索引，你要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢，就像这样：

ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);

这样我们把没用的idx_key1、idx_key3都干掉，再添加一个联合索引idx_key1_key3，使用这个联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果，何乐而不为？

二、多表连接原理

2.1连接的本质

为了故事的顺利发展，我们先建立两个简单的表并给它们填充一点数据：

mysql> CREATE TABLE t1 (m1 int, n1 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

我们成功建立了t1、t2两个表，这两个表都有两个列，一个是INT类型的，一个是CHAR(1)类型的，填充好数据的两个表长这样：

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。所以我们把t1和t2两个表连接起来的过程如下图所示：

这个过程看起来就是把t1表的记录和t2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。因为表t1中有3条记录，表t2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9行记录。

2.2连接过程简介

如果我们乐意，我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特定记录组合是有必要的，在连接查询中的过滤条件可以分成两种：

• 涉及单表的条件

  这种只涉及单表的过滤条件我们之前都提到过一万遍了，我们之前也一直称为搜索条件，比如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';

在这个查询中我们指明了这三个过滤条件：

• t1.m1 > 1
• t1.m1 = t2.m2
• t2.n2 < 'd'

那么这个连接查询的大致执行过程如下：

1. 首先确定第一个需要查询的表，这个表称之为驱动表。怎样在单表中执行查询语句我们在前一章都唠叨过了，只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询）。此处假设使用t1作为驱动表，那么就需要到t1表中找满足t1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询t1表的访问方法就设定为all吧，也就是采用全表扫描的方式执行单表查询。关于如何提升连接查询的性能我们之后再说，现在先把基本概念捋清楚哈。所以查询过程就如下图所示：

   

   针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2就派上用场了：

   • 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。
   • 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

   所以整个连接查询的执行过程就如下图所示：

   

   也就是说整个连接查询最后的结果只有两条符合过滤条件的记录：

   +------+------+------+------+
   | m1   | n1   | m2   | n2   |
   +------+------+------+------+
   |    2 | b    |    2 | b    |
   |    3 | c    |    3 | c    |
   +------+------+------+------+

   从上边两个步骤可以看出来，我们上边唠叨的这个两表连接查询共需要查询1次t1表，2次t2表。当然这是在特定的过滤条件下的结果，如果我们把t1.m1 > 1这个条件去掉，那么从t1表中查出的记录就有3条，就需要查询3次t2表了。也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

2.3内连接和外连接

为了大家更好理解后边内容，我们先创建两个有现实意义的表

CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';

CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, subject)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

我们新建了一个学生信息表，一个学生成绩表，然后我们向上述两个表中插入一些数据，为节省篇幅，具体插入过程就不唠叨了，插入后两表中的数据如下：

mysql> SELECT * FROM student;
+----------+-----------+--------------------------+
| number   | name      | major                    |
+----------+-----------+--------------------------+
| 20180101 | 杜子腾    | 软件学院                 |
| 20180102 | 范统      | 计算机科学与工程         |
| 20180103 | 史珍香    | 计算机科学与工程         |
+----------+-----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM score;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母猪的产后护理              |    78 |
| 20180101 | 论萨达姆的战争准备          |    88 |
| 20180102 | 论萨达姆的战争准备          |    98 |
| 20180102 | 母猪的产后护理              |   100 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)

现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了（因为score中没有姓名信息，所以不能单纯只查询score表）。连接过程就是从student表中取出记录，在score表中查找number相同的成绩记录，所以过滤条件就是student.number = socre.number，整个查询语句就是这样：

mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number;
+----------+-----------+-----------------------------+-------+
| number   | name      | subject                     | score |
+----------+-----------+-----------------------------+-------+
| 20180101 | 杜子腾    | 母猪的产后护理              |    78 |
| 20180101 | 杜子腾    | 论萨达姆的战争准备          |    88 |
| 20180102 | 范统      | 论萨达姆的战争准备          |    98 |
| 20180102 | 范统      | 母猪的产后护理              |   100 |
+----------+-----------+-----------------------------+-------+
4 rows in set (0.00 sec)

从上述查询结果中我们可以看到，各个同学对应的各科成绩就都被查出来了，可是有个问题，史珍香同学，也就是学号为20180103的同学因为某些原因没有参加考试，所以在score表中没有对应的成绩记录。那如果老师想查看所有同学的考试成绩，即使是缺考的同学也应该展示出来，但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一下这个需求，其本质是想：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。为了解决这个问题，就有了内连接和外连接的概念：

• 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

• 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

  在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

  • 左外连接:选取左侧的表为驱动表。
  • 右外连接:选取右侧的表为驱动表。

可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了，有时候匹配失败要加入结果集，有时候又不要加入结果集，这咋办，有点儿愁啊。。。噫，把过滤条件分为两种不就解决了这个问题了么，所以放在不同地方的过滤条件是有不同语义的：

• WHERE子句中的过滤条件

  WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

• ON子句中的过滤条件

  对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

  需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

2.4连接的原理

2.4.1嵌套循环连接（Nested-Loop Join）

我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。我们上边已经大致介绍过t1表和t2表执行内连接查询的大致过程，我们温习一下：

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

通用的两表连接过程如下图所示：

如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上边过程，也就是步骤2中得到的结果集中的每一条记录都需要到t3表中找一找有没有匹配的记录，用伪代码表示一下这个过程就是这样：

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录
    
        for each row in t3 {   #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询
            if row satisfies join conditions, send to client
        }
    }
}

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

需要注意的是，对于嵌套循环连接算法来说，每当我们从驱动表中得到了一条记录时，就根据这条记录立即到被驱动表中查询一次。如果得到了匹配的记录，就把组合后的记录发送给客户端，然后再到驱动表中获取下一条记录；

2.4.2使用索引加快连接速度

我们知道在嵌套循环连接的步骤2中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，妈呀，那得要扫描好多次呀～～～ 但是别忘了，查询t2表其实就相当于一次单表扫描，我们可以利用索引来加快查询速度哦。回顾一下最开始介绍的t1表和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表的主键或者唯一二级索引列，那么使用t2.m2 = 常数值这样的条件从t2表中查找记录的过程的代价就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称之为const，而设计MySQL的大叔把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。

• 在n2列上建立索引，涉及到的条件是t2.n2 < 'd'，可能用到range的访问方法，假设使用range的访问方法对t2表的查询的话，需要回表之后再判断在m2列上的条件是否成立。

假设m2和n2列上都存在索引的话，那么就需要从这两个里边儿挑一个代价更低的去执行对t2表的查询。当然，建立了索引不一定使用索引，只有在二级索引 + 回表的代价比全表扫描的代价更低时才会使用索引。

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index的访问方法来查询被驱动表。所以我们建议在真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。

2.4.3基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。现实生活中的表可不像t1、t2这种只有3条记录，成千上万条记录都是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：==尽量减少访问被驱动表的次数。==

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以设计MySQL的大叔提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

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