罗罗编程学习记录
本页内容 展开查看

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)。

原理流程

  1. 第一页正常查询,记录最后一行的排序键值 last_seen_key。
  2. 下一页:WHERE sort_key > last_seen_key ORDER BY sort_key LIMIT 20。
  3. 不支持跳页,只支持"下一页"。

代码 / 示例

-- 慢: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 的写法