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 之间的表关联是使用索引进行匹配的 为什么?