MySql联接算法

联接算法是MySql数据库用于处理联接的物理策略。在MySql 5.5版本仅支持Nested-Loops Join算法，如果联接表上有索引时，Nested-Loops Join是非常高效的算法。如果有索引时间复杂度为O(N)，若没有索引，则可视为最坏的情况，时间复杂度为O(N²)。MySql根据不同的使用场景，支持两种Nested-Loops Join算法，一种是Simple Nested-Loops Join算法，另外一种是Block Nested-Loops Join算法。

Simple Nested-Loops Join算法

从一张表中每次读取一条记录，然后将记录与嵌套表中的记录进行比较。算法如下：

For each row r in R do
    For each row s in S do
        If r and s satisfy the join condition
            Then output the tuple <r, s>

假设在两张表R和S上进行联接的列都不含有索引，算法的扫描次数为：R+RxS，扫描成本为O(RxS)。

假设t1，t2和t3三张表执行INNER JOIN查询，并且每张表使用的联接类型如下：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用了Simple Nested-Loops Join算法，则算法实现的伪代码如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
}

但是当内部表对所联接的列含有索引时，Simple Nested-Loops Join算法可以利用索引的特性来进行快速匹配，此时的算法调整为如下：

For each row r in R do
    lookup r in S index
        If find s == r
           Then output the tuple <r, s>

对于联接的列含有索引的情况，外部表的每条记录不再需要扫描整张内部表，只需要扫描内部表上的索引即可得到联接的判断结果。

在INNER JOIN中，两张联接表的顺序是可以变换的，根据前面描述的Simple Nested-Loops Join算法，优化器在一般情况下总是选择将联接列含有索引的表作为内表。如果两张表R和S在联接列上都有索引，并且索引的高度相同，那么优化器会选择记录数少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关。
下面这条SQL语句：

SELECT * FROM driver join user on driver.driver_id = user.uid;

其执行计划如下：

可以看到SQL先查询user表，然后将表driver上的索引和表user上的列uid进行匹配。

这里为什么首先使用user表，因为user表的联接列uid并没有索引，而driver表的联接列driver_id有索引，所以Simple Nested-Loops Join算法将driver表作为内部表。

注意：最终优化器确定联接表的顺序只会按照确切的扫描成本来确定，即：M(外表)+M(外表)*N(内表)；这里的外表和内表分别指的是外表和内表的扫描次数，如果含有索引，就是索引B+树的高度，其他一般都是表的记录数。

Block Nested-Loops Join算法

如果联接表没有索引时，Simple Nested-Loops Join算法扫描内部表很多次，执行效率会非常差。而Block Nested-Loops Join算法就是针对没有索引的联接情况设计的，其使用Join Buffer（联接缓存）来减少内部循环取表的次数。

MySql数据库使用Join Buffer的原则如下：

• 系统变量Join_buffer_size决定了Join Buffer的大小。
• Join Buffer可被用于联接是ALL、index、和range的类型。
• 每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer。
• Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放。
• Join Buffer只存储要进行查询操作的相关列数据，而不是整行的记录。

对于上面提到的三个表进行联接操作，如果使用Join Buffer，则算法的伪代码如下：

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 {
          if row satisfies join conditions,
          send to client
        }
      }
      empty buffer
    }
  }
}
if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions,
      send to client
    }
  }
}

举一个例子，把driver表的_create_date列和user表的create_date列的索引删除，进行联接查询，执行下面的SQL语句：

select _create_date FROM driver join user on driver._create_date = user.create_time;

再次查看SQL执行计划如下：

可以看到，SQL执行计划的Extra列中提示Using join buffer，这就代表使用了Block Nested-Loops Join算法。MySql 5.6会在Extra列显示更为详细的信息，如下面所示：

注意点：在MySql 5.5版本中，Join Buffer只能在INNER JOIN中使用，在OUTER JOIN中则不能使用，即Block Nested-Loops Join算法不支持OUTER JOIN。下面的left join语句：

select _create_date FROM driver left join user on driver._create_date = user.create_time;

在MySql 5.5中的执行计划如下：

可以看到并没有Using join buffer提示，这就意味着没有使用Block Nested-Loops Join算法，但是在MySql 5.6以后开始支持，上面的SQL语句在MySql 5.6中的执行计划如下：

对于上面的SQL语句，使用Block Nested-Loops Join算法需要的时间3.84秒，而不使用的时间是11.93秒。可以看出Block Nested-Loops Join算法对性能提示很多。

Batched Key Access Joins算法

MySql 5.6开始支持Batched Key Access Joins算法（简称BKA），该算法的思想是结合索引和group前面两种方法来提高（search for match）查询比较的操作，以此加快执行效率。

MySQL 5.6.3 implements a method of joining tables called the Batched Key Access (BKA) join algorithm. BKA can be applied when there is an index access to the table produced by the second join operand. Like the BNL join algorithm, the BKA join algorithm employs a join buffer to accumulate the interesting columns of the rows produced by the first operand of the join operation. Then the BKA algorithm builds keys to access the table to be joined for all rows in the buffer and submits these keys in a batch to the database engine for index lookups. The keys are submitted to the engine through the Multi-Range Read (MRR) interface. After submission of the keys, the MRR engine functions perform lookups in the index in an optimal way, fetching the rows of the joined table found by these keys, and starts feeding the BKA join algorithm with matching rows. Each matching row is coupled with a reference to a row in the join buffer.

Batched Key Access Join算法的工作步骤如下：

1. 将外部表中相关的列放入Join Buffer中。
2. 批量的将Key（索引键值）发送到Multi-Range Read（MRR）接口。
3. Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。
4. 返回结果集给客户端。

Batched Key Access Join算法的本质上来说还是Simple Nested-Loops Join算法，其发生的条件为内部表上有索引，并且该索引为非主键，并且联接需要访问内部表主键上的索引。这时Batched Key Access Join算法会调用Multi-Range Read（MRR）接口，批量的进行索引键的匹配和主键索引上获取数据的操作，以此来提高联接的执行效率。

对于Multi-Range Read（MRR）的介绍属于MySql索引的内容，这里简单说明：MySQL 5.6的新特性MRR。这个特性根据rowid顺序地，批量地读取记录，从而提升数据库的整体性能。在MySQL中默认关闭的，如果需要开启：

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

总结

MySql 5.6以后越来越多的算法和策略的支持，让联接查询的操作效率越来越快，在学习的时候了解了这些优化效果，更重要的是在实践中明白SQL优化器的工作原理，善于用EXPLAIN等SQL分析命令，对MySql查询有更好的了解。

参考

有不对的地方希望大家多交流，谢谢。

《MySql技术内幕：SQL编程》

http://dev.mysql.com/doc/refman/5.6/en/nested-loop-joins.html

转载请注明出处。
作者：wuxiwei
出处：http://www.cnblogs.com/wxw16/p/6116613.html