2026-10-30 JOIN 的执行方式：一次连接到底扫了多少遍

本机没装 MySQL，所以这篇用的是 Python 自带的 SQLite。它是个真数据库，有真优化器，能看到真的查询计划。两者优化器不同，但 JOIN 的底层是同一件事，后面会说清楚哪部分是 SQLite 特有的。

一、JOIN 的本质

不管哪种连法，骨架都是同一个：

对左表的每一行：
    去右表里找到匹配的行
    拼起来输出

差别全在「去右表里找」这一步怎么找。

有索引，是拿键值直接定位，复杂度 O(log m)；没索引，就是从头到尾扫一遍，O(m)。外面再套一层 O(n)，就变成 O(n×m)。

所以 JOIN 慢不慢，八成不在 JOIN 本身，在内层那一步有没有路可走。

二、跑起来是这样

两张表：users 500 行，orders 50000 行，每个用户 100 单。

关键设计：users.uid 我故意不设主键。如果它是 INTEGER PRIMARY KEY，SQLite 会拿它当 rowid，任何按它查都会自动走 rowid 定位，那就看不到"没索引时多惨"了。

第一组：什么都不做，直接查

计划：SCAN u
计划：BLOOM FILTER ON o (user_id=?)
计划：SEARCH o USING AUTOMATIC COVERING INDEX (user_id=?)
耗时：7.2 ms

注意第三行那个 AUTOMATIC COVERING INDEX——SQLite 发现内层没索引可用，自己临时造了一个。

第二组：把这个自救机制关掉，看真正的双层循环

计划：SCAN u
计划：SCAN o
耗时：608.9 ms

SCAN o，就是外层每走一行，把 orders 老老实实从头扫到尾。

第三组：建一个真索引

建索引耗时：5.8 ms
计划：SCAN u
计划：SEARCH o USING COVERING INDEX idx_orders_user (user_id=?)
耗时：1.1 ms

608.9 ms 降到 1.1 ms。553 倍。

三、那个 608 毫秒是怎么来的

500 × 50000 = 2500 万次比较。

这个数是可以算出来的，不是玄学。而建了索引之后，每行只需要一次树查找，500 次查找就完事，剩下的是读数据的开销。

顺带说一句：这还只是 500 行对 5 万行。真实业务里两张百万行的表 JOIN，无索引就是 10¹² 量级——那不是慢，是跑不完。

四、引擎不会真的傻跑

上面第一组那个 AUTOMATIC COVERING INDEX 值得单独说。

SQLite 在发现内层没有索引时，会临时建一个索引，用完就丢。这就是为什么第一组只用了 7.2 毫秒——它其实也做了索引查找，只是索引是现造的。

MySQL 8.0.18 之后用的是另一条路：哈希连接。把较小的那张表读进内存，按连接键建哈希表，然后扫另一张表去探。复杂度 O(n+m)，一次遍历。

我没有 MySQL 可以实测这一条（本机真没装），但它的道理和下面对比实验里那个「哈希连接」是一回事。

五、把 SQL 写反会怎样

我试了一下：

-- 写法 A
SELECT count(*) FROM users u JOIN orders o ON o.user_id = u.uid
-- 写法 B
SELECT count(*) FROM orders o JOIN users u ON o.user_id = u.uid

两边的执行计划一模一样：

计划：SCAN u
计划：SEARCH o USING COVERING INDEX idx_orders_user (user_id=?)
耗时：1.0 ms

谁当外表、谁当内表，是优化器根据统计信息决定的，不是你写 SQL 的顺序决定的。所以「小表驱动大表」这种口诀，在现代数据库上基本轮不到你操心——除非统计信息过期了，优化器判断失误。

六、手写两种算法对一下

为了把复杂度这件事看见，我在 Python 里手写了两种：

嵌套循环：   503.61 ms  匹配 50000 行
哈希连接：     2.56 ms  匹配 50000 行
结果一致：True
快了 197 倍

嵌套循环是 O(n×m)，就是 500 × 50000 次比较。哈希连接先把 orders 按 user_id 分桶（O(m)），然后每个 user 去桶里取（O(n)），总共 O(n+m)。

有趣的是它和数据库那组数字对得上：608.9ms 对 503.6ms，同一个量级——因为底层做的都是同一件事。

七、结论

JOIN 的执行方式，说到底就是三种：

嵌套循环，外层每行去内层找。内层有索引就是 O(n log m)，没有就是 O(n×m)。这是最基本的形式，MySQL 的 Index Nested-Loop 就是它带索引的版本。

哈希连接，把一边装进哈希表再扫另一边，O(n+m)。没有可用索引时的默认选择。

排序归并，两边各自按连接键排序，再一次扫过去。适合两边都已经有序、或者要输出有序结果的情况。

选择哪一种，不是你能直接指定的，是优化器根据「有没有索引、表多大、要什么顺序」算的。你能控制的只有一件事：该建索引的地方建索引。

上面那 608.9ms 和 1.1ms 之间，差的不是 SQL 写法，是一个索引。

附：验证代码

import sqlite3, time

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (uid INTEGER, name TEXT)")          # uid 故意不做主键
con.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER)")

# ... 灌入 500 / 50000 行 ...

SQL = "SELECT count(*) FROM users u JOIN orders o ON o.user_id = u.uid"

def plan(sql):
    return [r[3] for r in con.execute("EXPLAIN QUERY PLAN " + sql)]

print(plan(SQL))                          # 看优化器怎么选的

con.execute("PRAGMA automatic_index = OFF")   # 关掉自动索引，才看得到真·嵌套循环
print(plan(SQL))                          # SCAN u / SCAN o

con.execute("PRAGMA automatic_index = ON")
con.execute("CREATE INDEX idx_orders_user ON orders(user_id)")
print(plan(SQL))                          # SEARCH o USING COVERING INDEX

PRAGMA automatic_index 是 SQLite 特有的开关。MySQL 没有这个，
它用哈希连接解决同一个问题。所以第一组那个 7.2ms 是 SQLite 的行为，不是 MySQL 的。