05.7.4 复杂查询改写
所属:全栈开发面试题库 / 05 数据库与存储 / 05.7 SQL 语言与查询技巧
覆盖范围
- 子查询展开与 EXISTS/IN/JOIN 改写
- 行转列(PIVOT)与列转行(UNPIVOT)
- NULL 语义、三值逻辑、NOT IN 陷阱
- 分页与深翻页优化(游标分页/seek)
- 相关子查询与非相关子查询的代价
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.7.4-001 | EXISTS 和 IN 哪个性能好 | 进阶 | ★★★★★ |
| 05.7.4-002 | 行转列怎么写 | 进阶 | ★★★★ |
| 05.7.4-003 | 列转行怎么写 | 进阶 | ★★★ |
| 05.7.4-004 | NOT IN 为什么会踩 NULL 坑 | 高级 | ★★★★★ |
| 05.7.4-005 | 深翻页 LIMIT 100000,20 为什么慢 | 进阶 | ★★★★★ |
| 05.7.4-006 | 相关子查询为什么慢 | 进阶 | ★★★★ |
| 05.7.4-007 | COUNT 去重与 GROUP BY 的取舍 | 基础 | ★★★ |
05.7.4-001 EXISTS 和 IN 哪个性能好?
频率 ★★★★★ | 难度 进阶
一句话结论
没有绝对优劣:IN 适合子查询结果集小、外表大且用半连接优化;EXISTS 适合子查询有索引、外表小逐行探测;现代优化器会把半连接互相改写,实际看执行计划,不要凭直觉写。
概念解释
- IN:外表值匹配子查询结果集,优化器可转成半连接(semi-join)。
- EXISTS:相关子查询,找到一条匹配即停止。
- 半连接优化:MySQL/PG 会自动把 IN 改写成 EXISTS 或反连接。
代码 / 示例
-- 写法一:IN
SELECT * FROM orders o WHERE o.cid IN (SELECT id FROM customers WHERE city='北京');
-- 写法二:EXISTS(相关子查询)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id=o.cid AND c.city='北京');
-- 写法三:JOIN DISTINCT(避免重复)
SELECT DISTINCT o.* FROM orders o JOIN customers c ON o.cid=c.id WHERE c.city='北京';
常见误区
- 误区:EXISTS 一定比 IN 快。正解:取决于驱动表与索引,执行计划说了算。
- 误区:IN 子查询返回大集合时一定慢。正解:优化器可建临时哈希表,反而是半连接。
追问
- 问:为什么用 SELECT 1 而不是 SELECT *?答:EXISTS 不关心列,SELECT 1 语义清晰且避免读列。
05.7.4-002 行转列怎么写?
频率 ★★★★ | 难度 进阶
一句话结论
行转列用 CASE WHEN 列=值 THEN 另一列 END 配合 GROUP BY,把分类行折叠成多列;数据库也提供 PIVOT 语法,但跨库兼容性差,生产常用 CASE 写法。
代码 / 示例
-- 原始:每个部门每个月一行
-- dept month amount
-- A 1月 100
-- A 2月 200
-- 目标:每个部门一行,各月一列
SELECT dept_id,
SUM(CASE WHEN month='1月' THEN amount END) AS m1,
SUM(CASE WHEN month='2月' THEN amount END) AS m2,
SUM(CASE WHEN month='3月' THEN amount END) AS m3
FROM sales
GROUP BY dept_id;
-- SQL Server 专用 PIVOT:
SELECT dept_id, [1月],[2月],[3月]
FROM sales PIVOT (SUM(amount) FOR month IN ([1月],[2月],[3月])) p;
常见误区
- 误区:行转列用 MAX 还是 SUM。正解:一列对应一个值时用 MAX 或 SUM 都行;多行累加时用 SUM。
- 误区:动态列。正解:列名必须在 SQL 里写死,动态行转列需用存储过程拼 SQL 或在应用层做。
05.7.4-003 列转行怎么写?
频率 ★★★ | 难度 进阶
一句话结论
列转行用 UNION ALL 把多个列纵向拼回多行;PG 用 LATERAL + VALUES,SQL Server 用 UNPIVOT,MySQL 用 UNION ALL 最通用。
代码 / 示例
-- 原始:dept_id, m1, m2, m3
-- 目标:dept_id, month, amount
SELECT dept_id, '1月' AS month, m1 AS amount FROM sales_wide
UNION ALL
SELECT dept_id, '2月', m2 FROM sales_wide
UNION ALL
SELECT dept_id, '3月', m3 FROM sales_wide;
-- PG 简洁写法:
SELECT s.dept_id, v.month, v.amount
FROM sales_wide s
CROSS JOIN LATERAL (VALUES ('1月',m1),('2月',m2),('3月',m3)) AS v(month, amount);
常见误区
- 误区:列转行用 UNION。正解:UNION 会去重,性能差且可能误删合法重复行;列转行用 UNION ALL。
05.7.4-004 NOT IN 为什么会踩 NULL 坑?
频率 ★★★★★ | 难度 高级
一句话结论
NOT IN (子查询) 中如果子查询结果包含任何 NULL,整个表达式结果为 NULL(三值逻辑),外层 WHERE 过滤后一条都不返回,导致"查不到数据";用 NOT EXISTS 可避免。
概念解释
- 三值逻辑:SQL 的布尔值除 TRUE/FALSE 外还有 UNKNOWN。
- x NOT IN (a,b,NULL) 等价
x<>a AND x<>b AND x<>NULL,其中x<>NULL是 UNKNOWN,整体为 UNKNOWN,被 WHERE 丢弃。 - NOT EXISTS:相关子查询只返回 TRUE/FALSE,不引入 UNKNOWN。
代码 / 示例
-- 危险:orders.cid 或 customers.id 含 NULL 时返回空
SELECT * FROM orders
WHERE cid NOT IN (SELECT id FROM customers);
-- 安全:
SELECT * FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.cid);
-- 或显式过滤 NULL:
WHERE cid NOT IN (SELECT id FROM customers WHERE id IS NOT NULL);
常见误区
- 误区:NOT IN 和 NOT EXISTS 语义等价。正解:结果集含 NULL 时二者不等价。
- 误区:NULL = NULL 是 TRUE。正解:NULL 与任何值比较都是 UNKNOWN,要用 IS NULL。
追问
- 问:开发中怎么避免?答:子查询列尽量 NOT NULL,或统一用 NOT EXISTS 改写。
05.7.4-005 深翻页 LIMIT 100000,20 为什么慢?怎么优化?
频率 ★★★★★ | 难度 进阶
一句话结论
LIMIT offset, N 数据库要先扫描并排序前 offset+N 行再丢弃 offset 行,offset 越大扫描越多;改用"游标分页/seek 分页"(记住上一页最后一条的排序键值,下一页用 WHERE key > last_key ORDER BY key LIMIT N)可避免扫描。
概念解释
- offset 成本:数据库必须先定位并丢弃前 offset 行。
- seek 分页:利用有序索引直接定位,只取 N 行。
- 适用条件:排序列有唯一索引且翻页方向固定。
代码 / 示例
-- 慢:
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 快:上一页最后一条 id 是 123456
SELECT * FROM orders WHERE id > 123456 ORDER BY id LIMIT 20;
-- 非唯一排序键要带联合条件:
WHERE (create_time, id) > (last_time, last_id)
ORDER BY create_time, id LIMIT 20;
常见误区
- 误区:加索引就能深翻页。正解:普通 B+ 树索引仍需从 offset 起点扫;seek 分页才利用索引定位。
- 误区:前端跳页到第 1000 页还能用 seek。正解:seek 适合"下一页",跳页场景仍需 offset 或缓存页码映射。
05.7.4-006 相关子查询为什么慢?怎么改写?
频率 ★★★★ | 难度 进阶
一句话结论
相关子查询对外层每一行都执行一次内层查询(嵌套循环),外层大时呈 O(N*M);改写为 JOIN + GROUP BY 或半连接,让数据库一次扫描即可。
代码 / 示例
-- 慢:每行都执行一次子查询
SELECT o.*,
(SELECT COUNT(*) FROM order_items i WHERE i.order_id=o.id) AS item_cnt
FROM orders o;
-- 改写:JOIN 预聚合
SELECT o.*, COALESCE(i.cnt,0) AS item_cnt
FROM orders o
LEFT JOIN (SELECT order_id, COUNT(*) AS cnt FROM order_items GROUP BY order_id) i
ON i.order_id = o.id;
常见误区
- 误区:子查询一定慢。正解:非相关子查询只执行一次;相关子查询才逐行执行。
05.7.4-007 COUNT(DISTINCT 列) 和 GROUP BY 去重怎么选?
频率 ★★★ | 难度 基础
一句话结论
只需要去重数量用 COUNT(DISTINCT col);需要按维度分组统计去重数用 GROUP BY dim, COUNT(DISTINCT col);超大基数去重在 OLAP 场景近似精确(如 HyperLogLog)。
代码 / 示例
-- 去重计数
SELECT COUNT(DISTINCT user_id) FROM events;
-- 按维度去重
SELECT day, COUNT(DISTINCT user_id) FROM events GROUP BY day;
常见误区
- 误区:COUNT(DISTINCT) 走索引就快。正解:高基数去重仍需排序或哈希,大数据量要近似算法。
05.7.4-008 EXISTS 和 JOIN 哪个好?
频率 ★★ | 难度 进阶
一句话结论
只判断存在性用 EXISTS(半连接,不产生重复行);需要取关联表列用 JOIN;用 JOIN 时加 DISTINCT 去重。
05.7.4-009 分页深翻页优化方案?
频率 ★★ | 难度 进阶
一句话结论
seek 分页(记住最后一条排序键)是最优;前端不支持跳页到第 1000 页,用"加载更多"替代;跳页场景用缓存页码映射。
05.7.4-010 NULL 排序默认行为差异?
频率 ★★ | 难度 进阶
一句话结论
PG 默认 NULL 最大,MySQL 默认 NULL 最小;跨库用 NULLS FIRST/LAST 显式指定。
面试高频追问
问:NOT IN 为什么会踩 NULL 坑?
答:x NOT IN (a,b,NULL) 等价 x<>a AND x<>b AND x<>NULL,其中 x<>NULL 是 UNKNOWN,整体为 UNKNOWN,被 WHERE 丢弃。
问:深翻页 LIMIT 100000,20 为什么慢?
答:数据库要先扫描并排序前 100020 行再丢弃前 100000 行;用 seek 分页(WHERE key > last_key)优化。
问:相关子查询为什么慢?
答:对外层每一行都执行一次内层查询,O(N*M);改写为 JOIN + GROUP BY 或半连接。
问:行转列怎么动态生成列?
答:列名必须在 SQL 里写死,动态行转列需存储过程拼 SQL 或在应用层做。
核心要点速记
| 场景 | 改写方案 |
|---|---|
| NOT IN 陷阱 | 改用 NOT EXISTS |
| 深翻页 | seek 分页(WHERE key > last_key) |
| 相关子查询 | 改 JOIN + GROUP BY |
| 行转列 | CASE WHEN + GROUP BY |
| 列转行 | UNION ALL |
一句话记忆:NULL 三值逻辑是头号坑;深翻页用 seek 替代 offset。
05.7.4-009 深翻页(LIMIT 100000,20)怎么优化?
频率 ★★★★ | 难度 进阶
一句话结论
用 seek 分页(记住上一页最后一条排序键值)替代 OFFSET 分页,避免数据库扫描并丢弃前 N 行。
概念解释
- OFFSET 分页:LIMIT 100000,20 要扫描前 100020 行再丢弃 100000 行,O(N)。
- seek 分页:WHERE sort_key > last_seen_key ORDER BY sort_key LIMIT 20,直接定位,O(log N)。
原理流程
- 第一页正常查询,记录最后一行的排序键值 last_seen_key。
- 下一页:WHERE sort_key > last_seen_key ORDER BY sort_key LIMIT 20。
- 不支持跳页,只支持"下一页"。
代码 / 示例
-- 慢:OFFSET 100000
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 快:seek 分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
常见误区
- 误区:seek 分页用户可以跳页。正解:seek 只支持顺序翻页;跳页场景用缓存页码映射。
追问延伸
- 排序键必须唯一或有唯一约束,否则可能漏行或重复行。
- 前端交互:用"加载更多"替代页码跳转。
待整理清单
- [ ] EXISTS/IN/JOIN 在 MySQL/PG/SQL Server 的半连接优化差异
- [ ] 窗口函数改写相关子查询的更多场景
- [ ] 近似去重(HLL)在 ClickHouse/Doris 的写法