知识篇 | 什么是NESTED-LOOP Semi Join

原创于 2025-06-10 15:20:39 发布 · 1k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #sql

今天来学习下数据库知识点：NESTED-LOOP Semi Join是个什么东东。

首先要知道：什么是NESTED-LOOP Semi Join？

1.什么是Semi Join？

SemiJoin是一种特殊的连接操作，它不返回驱动表（通常是小表或过滤后结果集小的表）的所有列，也不返回被驱动表（通常是大表）的任何列。它的核心目的是：检查驱动表中的每一行，在被驱动表中是否存在至少一行满足连接条件的匹配行。

它只关心“是否存在匹配”，而不关心匹配的具体内容或数量。找到第一个匹配项后即可停止搜索被驱动表的当前行。

结果集：只包含驱动表中那些在被驱动表中至少有一个匹配项的行（每行只出现一次，即使在被驱动表中有多个匹配）。

SQL表现形式：通常使用EXISTS或IN(当子查询返回单列时)子查询来实现。

SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.key=B.key);

SELECT * FROM A WHERE A.key IN(SELECT B.key FROM B);

2.NESTED-LOOPSemiJoin算法

这是实现SemiJoin最基础、最直观的算法。核心思想：

1)外层循环(OuterLoop):遍历驱动表(A)的每一行（称为当前行A_row）。
2)内层循环(InnerLoop):对于当前的A_row，遍历被驱动表(B)的每一行（称为B_row）
3)匹配检查：对于每一对(A_row,B_row)，检查连接条件（通常是A.key=B.key）是否成立。4)短路优化(Short-Circuiting):这是SemiJoin的关键优化点！一旦在内层循环中为当前的A_row找到了一个满足条件的B_row，就立即停止扫描被驱动表B的剩余行。因为SemiJoin只关心“是否存在”，找到一个就足够了。
5)结果判定：如果在内层循环中为A_row找到了至少一个匹配项，则将A_row加入到最终结果集中。如果扫描完整个B都没找到匹配项，则丢弃A_row。

伪代码:

result=empty_set

For eachrowa in A://OuterLoop(驱动表)

found=false

For eachrowb in B://InnerLoop(被驱动表)

if(join_condition(a,b)istrue):

found=true

break;//Short-circuit!Foundonematch,stopscanningBforthis'a'

if(found):

Add a to result

Return result

3.NESTED-LOOPSemiJoin的特点

优点:

1)实现简单直观。对驱动表大小非常敏感：当驱动表(A)非常小（例如，经过WHERE条件高度筛选后只有几行）时，效率可以很高。

2)对索引依赖性强：如果被驱动表(B)的连接列(B.key)上有索引（尤其是唯一索引或高选择性索引），内层循环的效率会极大提升。因为对于每个a.key，数据库可以直接通过索引快速定位到B中可能匹配的行（甚至直接判断是否存在），避免全表扫描B。

3)内存需求相对较低：不需要像HashJoin或Sort-MergeJoin那样在内存中构建大型数据结构。

缺点:

1)时间复杂度高：最坏情况下是O(|A|*|B|)。当驱动表(A)很大或被驱动表(B)很大且没有索引时，性能会急剧下降。

2)I/O开销大：如果B很大且不能完全放入内存缓存，内层循环的每次（部分）扫描都可能涉及磁盘I/O，特别是当A也很大时，I/O次数会非常惊人(|A|次访问B)。

4.适用场景

1)驱动表(A)非常小（例如，经过严格过滤后的结果集）。

2)被驱动表(B)的连接列(B.key)上存在高效的索引（最好是唯一索引或高选择性B-Tree索引）。

3)查询使用EXISTS或IN子查询。

4)当其他连接方式（如HashJoin）的内存需求无法满足时，NestedLoop可能是唯一的选择（尽管性能可能不是最优）。

接下来，举个优化示例理解下（从低效到高效）

场景设定

表结构:

orders(订单表):存储所有订单信息。假设有1,000,000行。

order_id(主键)

customer_id(外键，引用customers.customer_id)

order_date

status(订单状态)

...(其他列)

customers(客户表):存储客户信息。假设有50,000行。

customer_id(主键)

country

registration_date

...(其他列)

查询目标：找出所有在2024年注册的德国客户(customers)所下过的所有订单(orders)。

初始(低效)SQL写法(可能诱导NestedLoopSemiJoin):

SELECT o.*

FROM orderso

WHERE EXISTS(

SELECT 1

FROM customersc

WHERE c.customer_id=o.customer_id--连接条件

AND c.country='Germany'

AND c.registration_date>='2024-01-01'

AND c.registration_date<'2025-01-01'

);

分析初始查询的执行计划(假设没有合适的索引)

1.驱动表是谁？数据库优化器需要决定哪个表作为外层循环(A)。这里有两个可能：

以orders为驱动表(A):外层循环遍历1,000,000行订单(o)。对于每一行订单，内层循环需要执行子查询：在customers表(B)中查找是否存在一个客户(c)，满足c.customer_id=o.customer_idANDc.country='Germany'ANDc.registration_date在2024年。

以子查询结果(符合条件的德国客户)为驱动表(A):外层循环遍历2024年注册的德国客户(假设筛选后有1,000行)。对于每一个这样的客户(c)，内层循环需要在orders表(B)中查找所有customer_id=c.customer_id的订单(o)。

2.问题所在(低效的NestedLoopSemiJoin):

如果优化器错误地选择了以orders为驱动表：

外层循环次数：1,000,000(巨大！)。
对于*每一笔订单*，内层循环都需要在50,000行的customers表上执行一次扫描（或通过索引查找）。即使customer_id是customers的主键（有索引），country和registration_date条件也需要在找到匹配customer_id后额外检查（可能需要回表）。如果customer_id没有索引，则每次都是全表扫描customers！
总成本≈|orders|*(查找单个customer_id的成本+检查country/date的成本)。这个成本非常非常高。
即使以子查询结果为驱动表，如果orders表的customer_id列没有索引，内层循环每次也要扫描1,000,000行的orders表1000次(假设有1000个目标客户)，成本=1000*1,000,000=1,000,000,000，同样灾难性。

优化策略：让NESTED-LOOPSemiJoin高效工作

核心原则是：选择小的结果集作为驱动表(A)，并确保被驱动表(B)的连接列上有高效索引。

1.优化SQL写法(通常不是必须，但有时有帮助)：

原SQL已经写得不错了(EXISTS)。也可以尝试IN或者显式INNERJOIN+DISTINCT(但DISTINCT可能有额外开销)。优化器通常能识别等价形式。更关键的是物理设计（索引）和引导优化器选择正确的驱动表。

2.创建必要的索引(很重要):

在customers表上创建高效筛选索引：目标是让子查询(EXISTS里面的部分)能快速找出所有符合条件的德国客户(A)。

CREATE INDEX idx_cust_country_regdate

ON customers(country,registration_date,customer_id);

--覆盖索引：包含查询所需的所有列(country,registration_date,customer_id)，避免回表。

--选择性：先按country筛选('Germany')，再按registration_date范围筛选(2024年)，最后customer_id用于连接。

在orders表上创建连接索引：目标是让内层循环能根据驱动表(A)提供的customer_id快速定位到orders中的相关行(B)。

CREATE INDEX idx_orders_customerid

ON orders(customer_id);

--允许通过customer_id快速查找订单。

--如果查询只选择orders的少量列，考虑创建覆盖索引(customer_id,order_date,status,...)以避免回表。

3.期望的执行计划(高效的NESTED-LOOPSemiJoin):

步骤1(驱动表A):数据库使用索引idx_cust_country_regdate快速扫描customers表，找出所有country='Germany'ANDregistration_dateBETWEEN'2024-01-01'AND'2024-12-31'的客户。假设结果集很小（比如1,000行）。这个小的结果集成为驱动表(A)。

步骤2(被驱动表B):对于驱动表(A)中的每一行(即每一个符合条件的德国客户，记其customer_id为X):

1)数据库利用orders表上的索引idx_orders_customerid，快速定位到orders表中所有customer_id=X的订单行。

2)由于这是SemiJoin（EXISTS），只要找到orders表中第一条customer_id=X的订单（证明该客户至少下过一个订单），数据库就可以停止扫描该customer_id对应的订单索引分支(Short-Circuiting)。*(注意：实际实现中，找到索引条目通常就能证明存在，可能不需要访问数据页本身就能确定EXISTS为真，如果索引是覆盖的)*

3)将驱动表行（客户）对应的订单信息（如果需要）或只是标记“存在”记录下来。

4)结果：最终输出所有在orders表中至少有一条订单的、2024年注册的德国客户的订单（由外层SELECTo.*决定）。

4.为什么优化后高效？

1)小的驱动表(A):外层循环只有约1,000次迭代（符合条件的德国客户数），而不是1,000,000次（所有订单）。

2)高效的被驱动表(B)访问:对于每个customer_id(X)，通过索引idx_orders_customerid查找订单，时间复杂度接近O(1)或O(log(|orders|))，并且利用SemiJoin的短路特性，通常只需访问索引的第一条匹配记录。

3)索引覆盖:理想情况下，idx_cust_country_regdate覆盖了子查询所需的所有列，idx_orders_customerid(或覆盖索引)覆盖了外层查询所需的orders列，避免了耗时的回表操作（访问数据页）。

4)总成本≈|A|*(通过索引在B中查找单个key的成本)。这个成本远低于优化前的方案。

那么，如何验证优化效果？

1.使用EXPLAIN/EXPLAINANALYZE：在SQL语句前加上EXPLAIN(或EXPLAINANALYZE获取实际执行信息)来查看数据库优化器选择的执行计划。关注：

1)驱动表(OuterTable):确认是否是筛选后的customers表（行数估计值很小）。

2)连接方式(JoinType):确认是否是NestedLoopSemiJoin或类似表述。

3)访问路径(AccessPaths):确认customers表是否使用了idx_cust_country_regdate(IndexScan/Seek)，orders表是否使用了idx_orders_customerid(IndexScan/Seek)。