根本原因是数据库未将WHERE条件下推至窗口计算前，导致对海量分区数据全量扫描排序；须用CTE或子查询显式前置过滤，确保窗口仅作用于已压缩数据集。

窗口函数在超大规模分区上变慢，根本原因不是“计算太重”，而是数据库没能把 WHERE 条件下推到窗口执行前，导致它被迫对几百万甚至上千万行做完整分区扫描和排序——哪怕你最后只想要其中 10 行。

为什么 WHERE 条件没在窗口前生效？

SQL 逻辑执行顺序里，WHERE 确实在窗口函数之前，但很多数据库（尤其是 PostgreSQL 14 以前、MySQL 8.0 默认行为、Oracle 旧版本）在遇到 OVER (PARTITION BY x ORDER BY y) 时，会把整个分区视为不可拆分的计算单元。只要分区键 x 的某个值对应了海量数据（比如 user_id = 123456789 对应 500 万行），优化器就倾向于先拉出全部这 500 万行，再排序、再算 ROW_NUMBER()，而不是尝试用索引定位该用户+时间范围的子集。

常见错误现象：

• SELECT * FROM orders WHERE user_id = 123 AND order_date >= '2024-01-01' ORDER BY order_date LIMIT 10 很快，但套上 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) 就卡住
• EXPLAIN 显示 WindowAgg 节点的 rows 预估远高于实际过滤后结果（比如预估 800 万，实际只有 1.2 万）
• 执行计划里出现 Materialize 或大量 Buffers: shared read=xxx，说明在落盘排序

如何强制谓词下推？用 CTE 或子查询封住边界

不能依赖优化器自动识别“这个 WHERE 可以剪掉分区”，得手动把它变成不可绕过的前置步骤。

正确做法是：把强过滤条件（user_id、时间范围、状态码等）放在最内层子查询或 WITH CTE 中，确保窗口只作用于已压缩的数据集。

• ✅ 推荐写法（CTE 显式隔离）：

  WITH filtered AS (
    SELECT user_id, order_date, amount
    FROM orders
    WHERE user_id = 123 AND order_date >= '2024-01-01'
  )
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
  FROM filtered;

• ✅ 等效子查询写法（MySQL 兼容性更好）：

  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
  FROM (
    SELECT user_id, order_date, amount
    FROM orders
    WHERE user_id = 123 AND order_date >= '2024-01-01'
  ) t;

• ❌ 错误写法（WHERE 在外层，无效）：

  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
  FROM orders
  WHERE user_id = 123 AND order_date >= '2024-01-01';

  ——多数引擎仍会先算完所有用户的窗口值，再过滤

分区键本身是否支持修剪？检查字段基数与索引覆盖

即使你用了 CTE，如果 PARTITION BY 字段本身不具备可剪枝性（比如用 uuid、毫秒级 created_at、或未建索引的 tenant_id），数据库依然无法快速定位目标分区的数据块。

• 高基数但无索引的分区键（如 user_id）：必须为 (user_id, order_date) 建联合索引，否则 CTE 里的 WHERE 只能靠顺序扫描
• 低选择性分区键（如 status IN ('pending', 'done')）：会导致单个分区过大，即使有索引也难逃排序压力；应换用更细粒度但业务稳定的字段，比如 DATE(order_date)
• 表达式分区（如 PARTITION BY EXTRACT(YEAR FROM order_date)）：无法利用普通索引，需函数索引且慎用；优先改用生成列 + 普通索引

物化中间结果是否值得？看更新频率与延迟容忍度

当 CTE/子查询仍不够快（比如单次过滤后仍有几十万行），且该窗口逻辑被高频复用（如日报表、BI 看板），就得考虑把“过滤 + 窗口”结果固化下来。

• PostgreSQL：用 CREATE MATERIALIZED VIEW，配合 REFRESH CONCURRENTLY（需唯一索引）
• MySQL：用 CREATE TABLE AS SELECT ... + 定时任务，但注意锁表风险
• SQL Server：不能在索引视图中用窗口函数（Msg 10127），只能走临时表或应用层缓存
• 关键判断点：如果基表每分钟都在写入，而报表只需小时级新鲜度，物化就是性价比最高的解；反之，实时性要求毫秒级，就别碰物化，老老实实压索引和调 CTE

真正卡住你的，从来不是 ROW_NUMBER() 这个函数，而是它背后那张没被裁剪干净的中间结果集。每次写窗口前，先问一句：我能不能用一层子查询把它框死？