SQL 窗口函数与索引的协作关系_技术教程

窗口函数本身不直接使用索引，但优化器可借助PARTITION BY和ORDER BY列上的合适复合索引避免显式排序；需索引列顺序完全匹配，且仅ROW_NUMBER()等有序函数可能受益，无序聚合如SUM() OVER()基本无法利用索引。

窗口函数执行时会不会用到索引

不会自动使用索引，但可以被优化器间接利用。窗口函数本身不走索引查找路径（比如 INDEX RANGE SCAN），它依赖的是排序或哈希等执行计划阶段；如果 ORDER BY 或 PARTITION BY 的列上有合适索引，优化器可能选择 INDEX FULL SCAN 或 INDEX RANGE SCAN 来避免显式排序，从而降低 SORT 开销。

常见错误现象：明明在 ORDER BY col 上建了索引，ROW_NUMBER() OVER (ORDER BY col) 执行仍很慢——大概率是没走索引扫描，而是走了全表扫描 + 内存排序。

确保索引列顺序与窗口函数中 PARTITION BY 和 ORDER BY 完全一致（例如 PARTITION BY a ORDER BY b, c 需要复合索引 (a, b, c)）
Oracle 和 PostgreSQL 支持索引跳扫（INDEX SKIP SCAN）或索引覆盖，但 MySQL 8.0+ 才开始较好支持基于索引的窗口函数优化
执行计划里看到 WINDOW SORT 或 WINDOW NOSORT 是关键线索：NOSORT 表示优化器判断无需额外排序，通常意味着索引被有效利用

哪些窗口函数更容易触发索引优化

ROW_NUMBER()、RANK()、DENSE_RANK() 和 LAG()/LEAD() 在有明确 ORDER BY 且数据局部有序时最可能受益于索引。而 AVG() OVER ()、SUM() OVER () 这类无 ORDER BY 的全集聚合，基本无法利用 B-Tree 索引加速。

使用场景差异明显：按时间分页取 Top-N（如“每个用户最近 3 条订单”）适合用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) + 覆盖索引 (user_id, created_at)；但计算滚动平均值（AVG(amount) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)）即使有索引，也仍需逐行定位，索引作用有限。

COUNT() OVER (PARTITION BY x) 可能走索引快速分组计数，前提是 x 列有索引且统计信息准确
FIRST_VALUE() 和 LAST_VALUE() 是否走索引，取决于其 ORDER BY 子句是否匹配索引最左前缀
带 ROWS BETWEEN 的滑动窗口函数一般不减少 I/O，索引仅辅助定位起点，不改变整体扫描量

MySQL 8.0+ 与 PostgreSQL 中索引协作的实际差异

PostgreSQL 对窗口函数 + 索引的优化更激进：只要 ORDER BY 列有索引，即使没写 PARTITION BY，也可能用 Index Scan Backward 实现 ROW_NUMBER() 降序编号；而 MySQL 8.0 要求索引必须覆盖全部 PARTITION BY 和 ORDER BY 列，且只在 EXPLAIN 显示 Using filesort 消失时才算真正避开了排序。

一个典型陷阱：在 MySQL 中对 created_at 建单列索引，然后执行 ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at) —— 该索引完全无效，因为缺少 status 列，优化器仍会回表并排序。

PostgreSQL 可通过 CREATE INDEX idx ON t (status, created_at) 同时支撑 PARTITION BY status ORDER BY created_at 和后续 WHERE status = ? 查询
MySQL 8.0.22+ 支持函数索引，可对表达式建索引（如 JSON_EXTRACT(data, '$.type')），但窗口函数中不能直接引用这类函数索引列，除非表达式完全一致
两者都不支持在索引中直接“存储”窗口计算结果，所有值仍是运行时计算，索引只影响数据访问路径

什么时候该放弃靠索引优化窗口函数

当窗口范围过大（如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）、分区键基数极高（如每行 PARTITION BY uuid）、或结果集本身需要返回大量行时，索引带来的排序节省会被随机 I/O 或内存压力抵消。此时更应考虑物化中间结果、改用应用层分页，或预计算写入汇总表。

容易被忽略的一点：窗口函数常出现在报表 SQL 中，而这类查询往往还带多层嵌套、JOIN 和复杂 WHERE，此时索引是否生效，更多取决于驱动表选择和连接顺序，而非窗口子句本身。

若 EX PLAIN 显示主表已走索引，但窗口函数所在子查询仍显示 Using temporary; Using filesort，优先检查子查询是否隔离了索引上下文（如用了 SELECT * 导致无法覆盖索引）
分区表上使用 PARTITION BY 不等于自动按物理分区裁剪；只有分区键与窗口分区键完全一致，才可能触发分区裁剪 + 索引联合优化
统计信息过期会导致优化器误判索引有效性，执行 ANALYZE TABLE（MySQL）或 VACUUM ANALYZE（PG）后重看执行计划