逐行求值的相关子查询
相关子查询的特点是引用了外层查询的列，逻辑上要对每一行外层结果都执行一次内层查询。这让它写起来很直观，代价却随外层行数线性增长：外层一万行就要探测内层一万次。能不能接受，取决于内层连接列上有没有索引——有索引时每次探测是几次页访问，没有索引时每次都是全表扫描，差距可以达到几个数量级。
基本信息
| 适用平台 | MySQL 8.0 / PostgreSQL 12 及以上 |
|---|---|
| 识别特征 | 内层查询引用了外层的列 |
| 执行方式 | 逻辑上按外层每行执行一次内层查询 |
| 性能前提 | 内层连接列上必须有索引 |
| 改写方向 | 左连接加分组聚合，或改用窗口函数一次算完 |
典型使用场景
- 给每一行附加统计值：例如每个用户的历史订单数
- 取每组极值：每人最近一次登录时间
- 存在性判断：确认某个对象有没有关联记录
- 嵌套口径报表：外层筛选后逐行补充明细指标
- 把相关子查询改写成连接后对比性能
写法与改写
写法与改写
-- 相关子查询：内层引用了外层的 u.id
SELECT u.id, u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt
FROM users u
WHERE u.status = 1;
-- 前提：内层的连接列必须有索引
-- ALTER TABLE orders ADD INDEX idx_user_id (user_id);
-- 改写一：左连接加分组聚合，无匹配的用户保留并计为零
SELECT u.id, u.name, COUNT(o.id) AS order_cnt
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 1
GROUP BY u.id, u.name;
-- 改写二：窗口函数一次算完，避免重复扫描
SELECT id, name, order_cnt
FROM (
SELECT u.id, u.name,
COUNT(o.id) OVER (PARTITION BY u.id) AS order_cnt
FROM users u LEFT JOIN orders o ON o.user_id = u.id
) t
WHERE order_cnt >= 0;写法对照
| 写法 | 特点 | 注意事项 |
|---|---|---|
| 相关子查询 | 写法直观，逐行求值 | 依赖内层索引，外层行数敏感 |
| 左连接加分组 | 一次扫描，适合统计 | 无匹配行的计数要用非空列 |
| 窗口函数 | 一次扫描，可同时算多个指标 | 需要较新版本支持 |
| 存在性判断 | 只判断有无，不产生重复行 | 取反时注意空值陷阱 |
改写成左连接时口径会变：原本没有匹配记录的用户在子查询写法下计为零，连接写法里若用计算所有行的方式就会统计成一，必须改成计算非空的连接列才能保持一致。
先把外层结果缩小，再执行相关子查询，能显著减少探测次数。把筛选条件写在最外层，比在子查询里堆条件更有效。