相关子查询在sql server中特别慢，因为每行外层数据都要触发一次内层扫描，优化器难以重写或并行化；改用avg() over(partition by dept_id)可将10万次扫描降为单次遍历，性能提升显著。
相关子查询为什么在SQL Server里特别慢
因为每行外层数据都要触发一次内层扫描，SQL Server 优化器很难重写或并行化这类嵌套逻辑。比如查“每个部门工资高于本部门平均值的员工”，WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) 在 10 万行数据上可能扫表 10 万次，执行计划里常出现重复的 Clustered Index Scan 和高 Estimated Number of Executions。
用AVG() OVER 替代子查询的写法要点
核心是把“对每行都算一遍平均”变成“一次分组排序+单趟遍历”。关键不在于函数名，而在于分区与排序是否对齐业务逻辑：
-
AVG(salary) OVER (PARTITION BY dept_id)就是每个部门的平均工资，直接跟原行 salary 比较即可 - 必须显式写
PARTITION BY dept_id，漏掉就变成全表平均，结果完全错 - 如果 dept_id 有 NULL，这部分行会被单独分到一个分区，需提前
WHERE dept_id IS NOT NULL过滤，否则 AVG 结果可能意外包含 NULL 分区 - 索引要建为
INDEX(dept_id, salary)，让 SQL Server 能跳过排序步骤；否则执行计划会出现Sort操作符，内存压力陡增
ROW_NUMBER() 替代“取最新一条”的典型陷阱
很多人用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) 后加 WHERE rn = 1，但上线后发现偶尔漏数据或结果不稳定：
- created_at 字段如果有毫秒级重复（尤其批量导入时），SQL Server 8.0+ 默认不保证相同排序键下的行序，
rn = 1可能每次返回不同记录 - 必须补二级排序字段，比如
ORDER BY created_at DESC, id DESC，且id必须是唯一、非空、有索引的列 - 索引要匹配：
INDEX(user_id, created_at DESC, id DESC)，否则PARTITION BY + ORDER BY无法走索引覆盖，仍会触发 Sort - 别用
RANK()或DENSE_RANK()替代ROW_NUMBER()—— 前两者在并列时会给相同排名，rn = 1可能返回多行，破坏“只取一条”的语义
性能差异有多大？看执行计划里的硬指标
在 SQL Server Management Studio 中按 Ctrl+M 打开实际执行计划，重点关注三处：
- 原相关子查询：
Estimated Subtree Cost通常 > 5，Number of Executions等于外层行数，Logical Reads是线性增长 - 改用窗口函数后：
Estimated Subtree Cost降到 0.3–1.2，Number of Executions固定为 1，Logical Reads几乎不变（只扫一次表） - 如果看到
Window Spool操作符，说明 SQL Server 正在内存中构建窗口帧——这是正常行为；但若旁边标着Warning: Memory Grant Warning，就得检查分区大小，避免单个 dept_id 数据过多导致溢写到 tempdb
真正容易被忽略的是：窗口函数本身不解决数据倾斜。如果某个 dept_id 占全表 80% 行数，哪怕加了索引，PARTITION BY dept_id 仍会让那个分区的排序耗尽内存。这时候得先用 WHERE dept_id IN (...) 缩小范围，再上窗口函数。
