Nested Loop Join入门

Nested Loop Join 之间的表关联是使用索引进行匹配的，假设表 R 和 S 进行连接，其算法伪代码大致如下：

for each row r in R with matching condition:
    lookup index idx_s on S where index_key = r
    if (found)
      send to client

在上述算法中，表 R 被称为驱动表，表 R 中通过 WHERE 条件过滤出的数据会在表 S 对应的索引上进行一一查询。如果驱动表 R 的数据量不大，上述算法非常高效。

接着，我们看一下，以下三种 JOIN 类型，驱动表各是哪张表：

SELECT ... FROM R LEFT JOIN S ON R.x = S.x WEHRE ...

SELECT ... FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...

SELECT ... FROM R INNER JOIN S ON R.x = S.x WEHRE ...

对于上述 Left Join 来说，驱动表就是左表 R；Right Join中，驱动表就是右表 S。这是 JOIN 类型决定左表或右表的数据一定要进行查询。但对于 INNER JOIN，驱动表可能是表 R，也可能是表 S。

在这种场景下，谁需要查询的数据量越少，谁就是驱动表。 我们来看下面的例子：

SELECT ... FROM R INNER JOIN S 
ON R.x = S.x 
WHERE R.y = ? AND S.z = ?

上面这条 SQL 语句是对表 R 和表 S 进行 INNER JOIN，其中关联的列是 x，WHERE 过滤条件分别过滤表 R 中的列 y 和表 S 中的列 z。那么这种情况下可以有以下两种选择：

优化器一般认为，通过索引进行查询的效率都一样，所以 Nested Loop Join 算法主要要求驱动表的数量要尽可能少。

所以，如果 WHERE R.y = ?过滤出的数据少，那么这条 SQL 语句会先使用表 R 上列 y 上的索引，筛选出数据，然后再使用表 S 上列 x 的索引进行关联，最后再通过 WHERE S.z = ?过滤出最后数据

为了深入理解优化器驱动表的选择，咱们先来看下面这条 SQL：

SELECT COUNT(1)
FROM orders
         INNER JOIN lineitem
                    ON orders.o_orderkey = lineitem.l_orderkey
WHERE orders.o_orderdate >= '1994-02-01'
  AND orders.o_orderdate < '1994-03-01'

上面的表 orders 你比较熟悉，类似于电商中的订单表，在我们的示例数据库中记录总量有 600万条记录。

表 lineitem 是订单明细表，比如一个订单可以包含三件商品，这三件商品的具体价格、数量、商品供应商等详细信息，记录数约 2400 万。

上述 SQL 语句表示查询日期为 1994 年 2 月购买的商品数量总和，你通过命令 EXPLAIN 查看得到执行计划如下所示：

EXPLAIN: -> Aggregate: count(1)
 -> Nested loop inner join  (cost=115366.81 rows=549152)
     -> Filter: ((orders.O_ORDERDATE >= DATE'1994-02-01') and (orders.O_ORDERDATE < DATE'1994-03-01'))  (cost=26837.49 rows=133612)
         -> Index range scan on orders using idx_orderdate  (cost=26837.49 rows=133612)
     -> Index lookup on lineitem using PRIMARY (l_orderkey=orders.o_orderkey)  (cost=0.25 rows=4)

上面的执行计划步骤如下，表 orders 是驱动表，它的选择过程如下所示：

Index range scan on orders using idx_orderdate：使用索引 idx_orderdata 过滤出1994 年 2 月的订单数据，预估记录数超过 13 万。

Index lookup on lineitem using PRIMARY：将第一步扫描的结果作为驱动表，然后将驱动表中的每行数据的 o_orderkey 值，在 lineitem 的主键索引中进行查找。

Nested loop inner join：进行 JOIN 连接，匹配得到的输出结果。

Aggregate: count(1)：统计得到最终的商品数量。

但若执行的是下面这条 SQL，则执行计划就有了改变：

EXPLAIN FORMAT=tree
SELECT COUNT(1) 
FROM orders
INNER JOIN lineitem
  ON orders.o_orderkey = lineitem.l_orderkey 
    WHERE orders.o_orderdate >= '1994-02-01' 
      AND  orders.o_orderdate < '1994-03-01'
      AND lineitem.l_partkey = 620758


EXPLAIN: -> Aggregate: count(1)
-> Nested loop inner join  (cost=17.37 rows=2)
    -> Index lookup on lineitem using lineitem_fk2 (L_PARTKEY=620758)  (cost=4.07 rows=38)
    -> Filter: ((orders.O_ORDERDATE >= DATE'1994-02-01') and (orders.O_ORDERDATE < DATE'1994-03-01'))  (cost=0.25 rows=0)
        -> Single-row index lookup on orders using PRIMARY (o_orderkey=lineitem.l_orderkey)  (cost=0.25 rows=1)

上述 SQL 只是新增了一个条件 lineitem.l_partkey =620758，即查询 1994 年 2 月，商品编号为 620758 的商品购买量。

这时若仔细查看执行计划，会发现通过过滤条件 l_partkey = 620758 找到的记录大约只有 38 条，因此这时优化器选择表 lineitem 为驱动表。

总结

• mysql会自动选小表作为驱动表

思考

• 为什么选小表作为驱动表?
• Nested Loop Join 之间的表关联是使用索引进行匹配的 为什么?