标量子查询慢是因为每行主表数据都会触发一次子表独立扫描，导致i/o和cpu开销随主表行数线性增长；窗口函数通过一次全表扫描+partition by实现聚合，显著提升性能。
直接用窗口函数替代标量子查询，能避免同一张表被反复扫描多次。
标量子查询为什么慢？
像 SELECT id, name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u 这种写法，每行 u 都会触发一次对 orders 表的独立扫描。10 万用户 = 10 万次全表或索引扫描，I/O 和 CPU 开销爆炸式增长。
常见错误现象包括：执行时间随主表行数线性增长、EXPLAIN 显示多次 Subquery 扫描、监控里看到大量重复的 orders 表读取。
- 即使
orders.user_id有索引，优化器也常无法复用扫描结果 - 子查询返回
NULL时，某些数据库（如 PostgreSQL）还会引入额外的三值逻辑判断开销 - 多个标量子查询并存时（比如同时查订单数、最近下单时间、总金额），问题成倍放大
窗口函数怎么改写？
把聚合逻辑从“每行算一次”变成“整表扫一遍，一次算完”，核心是用 OVER(PARTITION BY ...) 替代关联条件。
上面的例子可改写为：
SELECT DISTINCT u.id, u.name, COUNT(o.id) OVER(PARTITION BY u.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id;
注意点：
-
DISTINCT是必须的，否则LEFT JOIN会导致用户行重复（一个用户多笔订单就多几行） - 如果只要用户维度结果，不关心订单明细，
LEFT JOIN+OVER比GROUP BY更安全——不会意外丢掉零订单用户 - PostgreSQL 和 MySQL 8.0+ 均支持；Oracle 全版本支持；SQL Server 2005+ 支持
什么时候不能用窗口函数？
窗口函数只适用于“主表驱动、聚合目标明确”的场景。以下情况需另寻方案：
- 子查询带复杂过滤（如
WHERE status IN ('paid', 'shipped') AND created_at > '2025-01-01'），且该过滤无法下推到JOIN条件中 → 改用 CTE 预计算 - 需要聚合结果参与
WHERE过滤（如 “只查订单数 > 5 的用户”）→ 必须用GROUP BY+HAVING，窗口函数无法在WHERE中引用 - 跨多表、多层级聚合（如“每个部门平均薪资 vs 公司平均薪资”）→ 用 CTE 分层计算，避免在单个
OVER里硬塞多个PARTITION BY
真正容易被忽略的是：窗口函数虽快，但它的执行依赖于排序和内存缓冲。如果 PARTITION BY 字段基数极高（比如按毫秒级时间戳分组），或者数据量远超 work_mem（PostgreSQL）或 sort_buffer_size（MySQL），反而可能触发磁盘临时文件，比标量子查询更慢。上线前务必用真实数据量跑 EXPLAIN ANALYZE 看实际执行计划。
