MySQL查询优化之二：连接原理（Joins）

        在关系型数据系统（RDBMS）中，数据被存储分布在大量不同的表中。但查询的时候，我们经常需要将多个数据源合并成一个结果集，这个合并的过程，即是连接。

通常情况下，连接会提供一个连接条件（join condition），用于匹配两表中的数据。如果两表的连接没有条件，则表中的每一条记录都会与另一个表中的每条记录进行匹配，产生的结果即称为笛卡尔积。两个100条记录的表，笛卡尔积即100*100=10000条，如果三张100条记录的表这个结果将达到100万，很明显这通常不是我们想要的结果。

对于连接的方法，除了上面提到的笛卡尔积，常见的还有连接类型还有：

• 嵌套循环连接（Nested Loop Joins）
• 哈希连接（Hash Joins）
• 排序合并连接（Sort Merge Joins）

由于目前MySQL只支持嵌套循环连接，我们重点关注嵌套循环连接在MySQL中的实现。

目录

一、连接方法（Joins Methods）

1. 嵌套循环连接（Nested Loop Joins）

2. 块嵌套循环连接（Block Nested Loop Join）

二、连接类型（Join Types）

1.内连接（Inner join）

2.外连接（Outer join）

3.半连接（Semijoin）

4.反连接（Antijoin）

---

一、连接方法（Joins Methods）

1. 嵌套循环连接（Nested Loop Joins）

嵌套循环连接（Nested Loop Joins）的连接方式是：

1.先选取一张表（驱动表），取出一条记录（外层for循环）。

2.将这条记录与第二张表（被驱动表）中的所有记录进行匹配（内层for循环）。

3.如果满足连接条件，则将数据放入结果集中。

4.回到外层for循环，取驱动表中的第二条记录，继续同样的操作，直至结束。

连接的伪代码及图示如下（一个双层嵌套循环）：

FOR erow IN outer_table LOOP
  FOR edrow IN inner_table LOOP
    output values from erow and drow
  END LOOP
END LOOP

通过嵌套循环的执行过程，我们可以发现如下特点：

• 驱动表的数据只需访问一次，而被驱动表需要多次访问。
• 驱动表的记录数决定了嵌套循环的次数。
• 如果多次连接，需要将前面连接产生的结果集与后面的表连接。

由于被驱动表需要多次读取，因此被驱动表的访问方法（Access Paths）直接关系连接的效率，而驱动表的记录数决定了循环的次数。因此嵌套循环最适合的场景是驱动表记录很少（或者通过where条件过滤后只有很少的记录），被驱动表的访问效率很高。我们在优化SQL时可以顺着这两个思路：

• 减少驱动表记录数（选择更优的连接顺序 / 尽早通过where条件减少驱动表记录数）
• 提高被驱动表访问效率（更高效的访问方法，减少单次循环成本）。

2. 块嵌套循环连接（Block Nested Loop Join）

MySQL块嵌套循环连接（Block Nested Loop Join）在嵌套循环连接的基础上进行改进。在连接前会提前分配一块连接缓冲区（由参数join_buffer_size控制），将外层循环的 列/结果集 缓存一部分至缓冲区（只缓存需要连接的列的数据，不会缓存整行），然后批量在内层循环中判断。连接缓存是针对单次连接的，因此一个SQL可能会分配多个连接缓存，在查询完成后，缓存会释放。

假设一次性缓存了10条 记录/结果集，这样在内层循环时，被驱动表的一次读取，可以同时和10条记录/结果集进行匹配，从而将总体循环的次数降低至原先的1/10，大幅减少了被驱动表需要读取的次数。块嵌套循环连接只有在访问方法为all（全表扫描），index（索引全扫描），range（范围扫描）的时候才会使用。

块嵌套循环的伪代码如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {

    store used columns from t1, t2 in join buffer  -- 部分结果集先加入连接缓存

    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {  -- t3在一次循环中与缓存中所有数据比较，而不是普通嵌套循环的一次循环只比较一行。
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

二、连接类型（Join Types）

连接类型指的是相同的连接条件（join condition），根据连接类型返回的结果集也不同。

1.内连接（Inner join）

在MySQL中，inner join, join, cross join 是同义词，可以互相替换。内连接（inner join）就是返回的结果集中，参与连接的表的记录必须都满足连接条件。内连接的连接顺序是不固定的，优化器会预估不同连接顺序的成本，选择成本最低连接顺序。

2.外连接（Outer join）

外连接又分left outer join（左外连接）、right outer join（右外连接）、full outer join （全外连接/MySQL不支持）。外连接除了返回满足连接条件的记录，还会返回某张表中部分或全部不满足条件的记录，而其对应连接表中的属性，全部用null替代。

以左外连接（left outer join）举例，结果集会包含驱动表中不满足连接条件的记录，同时被驱动表的属性将以null进行填充。与内连接不同，左外连接的顺序是严格固定的，最左边的表会作为驱动表，然后依次进行连接，所以驱动表的选择优化就要由我们自己判断。

3.半连接（Semijoin）

半连接是处理子查询的一类算法，其并没有明确的语法（没有semi join语句）。半连接在处理两个结果集的连接时，只需要返回第一个数据集中的记录，但要求是在第二个数据集中有匹配的项（并不关心有多少条匹配，通常是in或exists子查询）。在连接时，当在子查询中发现第一个匹配的结果时，即会停止处理并开始匹配下一条记录，因此第二个结果集中的多条匹配数据并不会导致第一个结果集返回多条数据（这与inner join 和outer join不同）。

半连接的伪代码如下（ds1为第一个数据集，ds2为子查询数据集）：

FOR ds1_row IN ds1 LOOP  -- 取ds1一条记录,在内层子查询中循环，查找匹配项
  match := false;
  FOR ds2_row IN ds2_subquery LOOP
    IF (ds1_row matches ds2_row) THEN
      match := true;
      EXIT -- 当发现第一条匹配的ds2_row时，立刻退出内层循环
    END IF
  END LOOP
  IF (match = true) THEN 
    RETURN ds1_row  -- 返回ds1的记录，但是只有1条（即使内层有多条匹配）
  END IF
END LOOP

在MySQL中，当使用半连接时，会从下面几类实现策略中，评估并采取成本最低的策略：

• Table pullout
• Duplicate weedout
• FirstMatch
• LooseScan
• Materialize

4.反连接（Antijoin）

反连接是两个数据集连接时，返回第一个数据集中的结果，但要求其在第二个结果集中没有匹配项（和半连接相反）。和半连接类似，反连接在第二个数据集中找到第一个匹配项时即会停止处理（结果为False）。和半连接不同的是，最终返回的是在第二个结果集中没有匹配项的数据。

半连接和反连接在处理特定的需求时，可以节省大量的不必要的循环次数，提升性能。

了解表的原理后我们即可以在优化SQL的过程中，可以通过调整连接顺序，提高被驱动表的访问效率等方式来提高SQL性能。