后台订单列表要加一列「客户名」。代码写得很快:先查 100 条订单,然后 foreach 里面拿 customer_id 再查一次客户表。上线后接口从 20ms 变成 180ms,数据库监控显示每秒查询数从 30 涨到 3000。这就是典型的 N+1 查询:1 次主查询加 N 次关联查询,N 是这一页的行数。

正确的做法是把关联交给数据库,用一条 JOIN 查出结果。但 JOIN 不是「写了就快」:条件放错位置会丢数据,一对多关联会让金额翻倍,被驱动表没索引会让数据库做几十万次全表扫描。这篇就把这三个坑一次讲透。

场景走查:从 101 次查询到 1 次

原始的列表逻辑用伪代码表示:

-- 第 1 次:取一页订单
SELECT id, customer_id, amount FROM orders ORDER BY created_at DESC LIMIT 20;

-- 然后对每一行订单,再查一次客户
SELECT name FROM customers WHERE id = ?;   -- 执行 20 次

一页 20 条就是 21 次查询,100 条就是 101 次。每次查询都有网络往返、解析、执行的开销,哪怕单次只要 1ms,累加起来也很可观。改成 JOIN:

SELECT o.id, o.amount, o.created_at, c.name, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC
LIMIT 20;

一次查询拿到全部字段。数据库可以用索引把两张表接起来,网络只往返一次。

本机实测这个差别:同样取 20 条订单和客户名,循环查客户要 2.31 ms(中位数 3.95 ms),一条 JOIN 只要 0.87 ms(中位数 1.56 ms)。注意这是 localhost,单次往返几乎为零;如果应用和数据库隔着机房,每次往返按 1 ms 算,20 条就是 20 ms 起步,100 条就是 100 ms。N+1 的可怕之处不在单次查询慢,而在往返次数随页大小线性增长。

JOIN 在概念上做的事
取驱动表的一行
按 ON 条件去另一张表找匹配
拼成一行输出

JOIN 的底层:先配对,再过滤

概念上,A JOIN B ON 条件 的过程是:拿 A 的每一行,去 B 里找满足条件的行,找到就拼成一行。如果没有任何条件限制,A 有 100 行、B 有 1000 行,配对结果就是 10 万行,这叫笛卡尔积。所以 ON 条件不是可选项,是必须项。

写 JOIN 不写 ON(或用 CROSS JOIN)在测试环境可能只是「结果多了点」,在生产就是灾难:两张百万行的表能配出万亿行,查询直接把磁盘和内存打满。任何一次「数据库突然卡死、临时目录暴涨」,都值得先看看有没有漏写关联条件。

关联条件不一定是单列。订单明细和商品可以用 ON i.product_id = p.id,也可以先按复合键匹配。MySQL 提供 USING (列名) 简写,要求两边列名相同,读起来短,但可读性不如显式 ON,团队里统一一种更好。

还有一种自连接:同一张表和自己 JOIN。比如员工表里存 manager_id,查「员工和直属上级」就要 e JOIN employees m ON m.id = e.manager_id。自连接必须给两边起不同别名,否则数据库不知道你在引用哪一边。关联的表越多,优化器要枚举的连接顺序就越多,这也能解释为什么「三表 JOIN 还能看懂、六表 JOIN 就没人敢动」。

连接顺序的枚举是指数级的:三张表有 6 种顺序,四张 24 种,六张 720 种。优化器不会真的全试,它用代价模型加剪枝,但表一多,估算误差也会放大。这就是「大 SQL 越写越慢、越改越不敢改」的根源,不是数据库笨,是搜索空间真的变大了。

四种 JOIN,差别在「没匹配上怎么办」

INNER JOIN:只保留两边都匹配上的行
LEFT JOIN:保留左表全部行,右表没匹配就补 NULL
RIGHT JOIN:保留右表全部行,左表没匹配就补 NULL
CROSS JOIN:不做匹配,直接两两组合

业务里最常用的是 INNER JOIN 和 LEFT JOIN。LEFT JOIN 的典型用途是「找出没有订单的客户」:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

RIGHT JOIN 几乎总能改写成 LEFT JOIN,只是把两张表换个位置。团队里统一用 LEFT JOIN,读起来更顺,也少一类「到底保留了哪边」的困惑。另外要记住:LEFT JOIN 的右表如果有重复行,左表的一行会被复制成多行,这和后面要讲的一对多金额放大是同一回事。

「找出没有订单的客户」是反连接(anti-join)的典型。除了 LEFT JOIN ... WHERE 右表.id IS NULL,也可以写 NOT EXISTS。MySQL 8.0 对两种写法都可能改写成反连接,具体哪个快,看 EXPLAIN,别凭感觉。

选 INNER 还是 LEFT,先看业务问题的答案是不是「可能为零」。如果答案是「没有也要显示 0」,就必须 LEFT JOIN 且把条件放 ON;如果答案是「只关心有的」,INNER 更简单也更快,优化器不用为「补 NULL 的行」多留一条路径。

条件放 ON 还是 WHERE,结果差 5 倍

这是 JOIN 最容易踩的坑。需求是「每个客户和他们的已支付订单数,包括没有已支付订单的客户」。直觉写法:

SELECT c.id, COUNT(o.id) AS paid_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id;

这段 SQL 把 o.status = 'paid' 放在 WHERE 里。对于没有已支付订单的客户,LEFT JOIN 先把 o.status 补成 NULL,紧接着 WHERE 判断 NULL 不等于 'paid',整行被过滤掉。LEFT JOIN 实际上退化成了 INNER JOIN,那些「零订单客户」全没了。

在本机的演示数据里,客户表 101 行(其中 1 个客户没有任何订单),只有 20 个客户有已支付订单。两种写法的结果:

写法返回客户数说明
条件放 ON101保留全部客户,没匹配的计数为 0
条件放 WHERE20LEFT JOIN 退化成 INNER JOIN

正确写法是把过滤条件放进 ON:

SELECT c.id, COUNT(o.id) AS paid_count
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id;

规则可以记成一句话:LEFT JOIN 中,只影响「右表哪些行参与匹配」的条件放 ON;要过滤「最终结果」的条件才放 WHERE。拿不准时,把条件挪到 ON 里跑一次,对比行数就知道了。

这个坑在代码评审里极难看出来,因为两种写法语法都合法、测试数据里如果每个客户都有订单也看不出差异。写 LEFT JOIN 时,先问一句:左表里那些「没有右表记录」的行,应不应该出现在结果里?

一对多:JOIN 之后金额为什么翻倍

订单表 orders 和明细表 order_items 是一对多:一张订单有多个商品。有人统计「所有订单的总金额」,写成了:

SELECT SUM(o.amount) FROM orders o
JOIN order_items i ON i.order_id = o.id;

结果比真实金额大。实测数据:真实的订单金额合计是 136951.72,JOIN 之后算出来是 205427.58。原因很直接:订单 1 有 3 条明细,JOIN 之后订单 1 的那一行出现了 3 次,amount 也被加了 3 次。明细越多,放大越厉害。

这不是数据库算错,是你让它按「明细行」的粒度求和,却用了「订单级」的金额。修法有两种。第一种是先把一边聚合成一行,再 JOIN:

SELECT SUM(o.amount)
FROM orders o
JOIN (
  SELECT order_id, SUM(qty * price) AS item_total
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.id;

第二种是只在需要明细粒度时用 JOIN,统计订单金额就老老实实从 orders 单表求和:

SELECT SUM(amount) FROM orders;

同理,COUNT(*) 在 JOIN 之后数的是「配对后的行数」,不是订单数。要数订单,应该 COUNT(DISTINCT o.id),但更好的做法是先想清楚统计粒度,再决定要不要 JOIN。

连接顺序与被驱动表的索引

两张表 JOIN,数据库要选一张当驱动表(先读它),另一张当被驱动表(按条件去里面找)。理想情况是:驱动表小,被驱动表的关联列有索引。这样驱动表每出一行,被驱动表一次索引查找就能定位,复杂度接近「驱动表行数 × log(被驱动表行数)」。

在本机跑这条查询:

EXPLAIN SELECT o.id FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = '北京';

实测计划里 customers 是驱动表(type=ALL,扫 101 行,filtered=10.00),orders 是被驱动表(type=ref,走 idx_customer_created,rows=20)。优化器判断「先筛出北京的客户,再按 customer_id 找订单」更划算。如果 orders.customer_id 没有索引,被驱动表就只能全表扫描,101 个客户各扫 2000 行,等于 20 万次读取。

MySQL 8.0.18 之后还支持 Hash Join:没有可用索引时,把较小的表放进内存建哈希表,再扫另一张表去探测,比朴素嵌套循环快很多。8.0.18 之前的版本在没有索引时更依赖 Block Nested Loop,遇到大表会明显变慢。本机是 8.0.12,实测计划里看不到 hash join,这一点要按版本区分,别把新版本的结论套到老环境。

驱动表不是「写在 FROM 后面第一张」决定的。优化器会估算两边行数和过滤后的规模,选成本更低的一边先读。上面这条查询里,先筛出北京客户再找订单,比先扫全量订单再过滤客户更划算,所以 customers 成了驱动表。如果估算错了,可以用 STRAIGHT_JOIN 强制顺序,但这应该是临时手段,不是常规写法。

被驱动表的关联列一定要有索引。没有索引时,每一行驱动数据都要扫一遍被驱动表;有了索引,才是一次定位。JOIN 慢的排查顺序通常是:先看被驱动表的 type 是不是 ALL,再看 rows,最后才考虑改写 SQL。

关联列有索引还要看索引「够不够用」。如果 JOIN 之后还要按状态过滤,单列索引 (customer_id) 只能定位到客户,状态判断仍要回表逐行做;把索引改成 (customer_id, status),状态判断就能在索引里完成,回表次数明显减少。这和单表查询的联合索引是同一个道理,只是在 JOIN 里更容易被忽略,因为大家往往只检查「关联列有没有索引」,不检查索引后面还带了什么。

翻车现场:少一个索引,62 ms 和 4 ms

某次报表加需求:「统计北京客户的订单数」。订单表已经扩到 19.98 万行(orders_big),关联列 customer_id 上没有索引,只有主键和 created_at 索引。查询是:

SELECT COUNT(*)
FROM orders_big o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = '北京';

实测最快要 62.27 ms、中位数 66.90 ms,听着不算离谱,但表再大十倍就是秒级。执行计划给出了原因:orders_big 成了驱动表,type=ALL、rows=193495;customers 虽然走主键 eq_ref,但要为每一行订单查一次。19 万行驱动数据,就是 19 万次查找。

给关联列加上索引 (customer_id, created_at) 后,计划翻了过来:customers 先按 city='北京' 筛出 101 行里的 10 行当驱动表,orders_big 变成 type=ref、key=idx_customer_created、rows=1915、Extra=Using where; Using index。同样五次取最优,耗时 3.93 ms,中位数 4.23 ms,相差约 16 倍。

版本驱动表被驱动表 type被驱动表 rows最优耗时
无关联索引orders_big(ALL,193495 行)eq_ref162.27 ms
有 (customer_id, created_at)customers(ALL,101 行,filtered 10)ref19153.93 ms

更阴的一点是:同样这条查询加 LIMIT 20,没有索引时也只要 0.16 ms。因为驱动表扫到第 20 个匹配就提前结束了,问题被 LIMIT 完全掩盖。压测时如果只测「首页 20 条」,会得出「一切正常」的结论;一到导出、报表或翻到深处,才原形毕露。所以压测分页接口,至少要有「不带 LIMIT 的计数」和「最后一页」两个场景。

修复本身不复杂,难的是发现。JOIN 慢的排查顺序应该固定下来:先看两边的 type,被驱动表是 ALL 就先补索引;再看 rows 估算是否离谱;最后才考虑改写 SQL 或强制连接顺序。索引加上之后,rows 从 193495 降到 1915,这个变化比任何「感觉快了」都可靠。

不要用「加了 LIMIT 挺快的」来证明 JOIN 没问题。LIMIT 只是让数据库提前收工,扫描成本并没有消失;报表和导出通常不带 LIMIT,它们才是这类查询的真实负载。

现象对照

现象真实原因第一步查什么
接口随列表条数线性变慢N+1 查询,每行都单独查一次关联表数一下一次请求打了几条 SQL,改成 JOIN 或批量查
LEFT JOIN 后「零记录」的行消失右表的过滤条件写进了 WHERE把条件挪到 ON,对比返回行数
统计金额比实际大几倍一对多 JOIN 放大了行数,同一笔金额被重复累加先确认统计粒度,看 COUNT(*) 和 COUNT(DISTINCT id) 的差异
JOIN 查询特别慢,CPU 高被驱动表的关联列没有索引,退化成全表扫描EXPLAIN 看被驱动表的 type 是否为 ALL
结果行数暴涨到无法返回漏写 ON 条件,产生笛卡尔积检查 SQL 里有没有孤立的多表 FROM,或 ON 1=1
分页 JOIN 后每页条数不对一对多 JOIN 后再 LIMIT,截断的是明细行不是主表行先分页主表,再按主键批量取关联数据
加 LIMIT 很快,报表或导出慢几十倍LIMIT 让扫描提前结束,真实负载并没有 LIMIT再跑一次不带 LIMIT 的 COUNT 对比
JOIN 之后只有某一张表 rows 特别大那张表是被驱动表,关联列没索引或索引前缀不够逐表看 type、key、rows,补关联列索引

边界与代价

JOIN 不是越多越好。每多关联一张表,优化器可选计划就多一层,执行时也多一次索引查找;关联五六张表之后,排查性能问题会变得非常困难。能在应用层用一次批量查询(WHERE id IN (...))解决的,不必硬塞进一条 SQL。

分页和 JOIN 一起用要特别小心。一对多关联后 LIMIT 20 截的是「配平行」,一张订单有三条明细就会占三个名额。正确顺序是先分页主表拿到 20 个 id,再用 WHERE order_id IN (...) 取明细,在应用层拼装。

「先分页主表,再批量取关联数据」在代码里通常长这样:先查 20 个订单 id,再用 SELECT ... FROM order_items WHERE order_id IN (?, ...) 一次取回,最后按 order_id 分组拼装。它把 N+1 压成 2 次查询,又不会像大 JOIN 那样让分页粒度错乱。代价是应用层要多写一段拼装逻辑,但换来的是可控的查询次数和清晰的执行计划,大多数列表页都值得这么做。

另外,批量查的 IN 列表不要一次塞几千个 id。分批到几百个一轮,既避免 SQL 过长,也让每次查询都能走索引区间。

ON 条件里也别放函数。ON DATE(o.created_at) = DATE(c.created_at) 这种写法会让索引失效,和单表查询里「函数包住索引列」是同一个问题。关联键两边的类型也要一致,VARCHAR 对 INT 的隐式转换会让被驱动表退化成全表扫描,计划里表现为 key=NULL 或 type=ALL。

JOIN 数量也要克制。三张表以内的关联通常还能看清,五六张表叠在一起,执行计划会变成一团,任何一个关联列缺索引都可能拖垮整条查询。能拆成两次查询、在应用层用 id 批量拼装的,不必强行写成一条大 SQL。

如果被驱动表确实没有索引,MySQL 会用连接缓冲区(Using join buffer)批量匹配,8.0.18 之后还可能用 Hash Join。它比逐行全表扫快,但仍然要扫大量数据,不能替代索引。看到 Using join buffer,先想「能不能补索引」,而不是「调大缓冲区」。

8.0.18 之前的版本只有 Block Nested Loop,本篇的 62.27 ms 就是这种执行方式下的结果。如果你在 8.0.18 及以上想验证 Hash Join 的影响,可以把 orders_big 的关联索引设为不可见,再跑同一条 COUNT,EXPLAIN 的 Extra 里应出现 Using join buffer (hash join),被驱动表不再逐行 eq_ref。具体耗时和输出本文未在本机验证(本机是 8.0.12),标记为待确认;结论不变——Hash Join 只是没有索引时的补救,有索引仍然优先走索引。

NULL 在 JOIN 里也有坑。ON c.id = o.customer_id 中只要有一边是 NULL,比较结果就是未知,这一行不会匹配。外键列如果允许 NULL,关联不上的行会静默消失,LEFT JOIN 也救不回来。关联列尽量 NOT NULL。

MySQL 的 JOIN 没有全外连接(FULL OUTER JOIN),需要时用 LEFT JOIN ... UNION ... RIGHT JOIN 模拟;PostgreSQL 原生支持。这些方言差异在跨库迁移时要提前确认。

小结

JOIN 的本质是按条件配对两张表:INNER 只留匹配的,LEFT 保留左表全部;过滤右表的条件放 ON,过滤结果的条件放 WHERE;一对多关联会放大行数,统计前先想清楚粒度;被驱动表的关联列必须有索引。

当天可以自己验证的练习:

  1. 造一个没有任何订单的客户,用 LEFT JOIN 分别把条件放 ON 和 WHERE,比较返回行数。
  2. 用 SUM(orders.amount) 直接统计,再 JOIN 明细表统计,记录两个数字的倍数关系。
  3. 把 orders.customer_id 的索引删掉,跑一次 JOIN,观察 EXPLAIN 和实际耗时。
  4. 写一条三表 JOIN(客户、订单、明细),先分页订单再关联,确认每页订单数正确。
  5. 故意写一条不带 ON 的两表查询(小表上),看结果行数怎么变成两表行数之积。

下一篇讲聚合、分组与子查询。JOIN 解决「把数据拼起来」,接下来要解决「把数据汇总成报表」:COUNT 数错了、GROUP BY 被 ONLY_FULL_GROUP_BY 拦下、子查询写得让数据库每行都重算一遍,都是报表需求里最常见的翻车点。