SQL 窗口函数性能调优：ROWS 和 RANGE 的执行代价差异

SQL 窗口函数性能调优ROWS 和 RANGE 的执行代价差异一、同一个窗口5倍性能差距从哪来先说一个真实案例。我们在 ClickHouse 上跑一个交易日报需要计算每支股票过去 30 个交易日的滚动平均成交量-- 写法ARANGE BETWEEN SELECT trade_date, stock_code, volume, AVG(volume) OVER ( PARTITION BY stock_code ORDER BY trade_date RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW ) AS avg_volume_30d FROM stock_daily WHERE trade_date 2026-01-01;5000 支股票 × 6 个月数据跑了89 秒。然后把RANGE改成ROWS同样数据只跑了17 秒。同样的逻辑同样的结果因为没有重复日期性能差 5 倍。到底差在哪里这篇我们就来把窗口函数的执行代价掰开揉碎讲清楚。flowchart LR subgraph RANGE模式 A[排序数据] -- B[计算每行对应的br/值范围区间] B -- C[在值范围内br/找到所有行] C -- D[聚合计算] end subgraph ROWS模式 E[排序数据] -- F[根据行偏移量br/直接定位] F -- G[聚合计算] end二、ROWS 和 RANGE 的本质区别ROWS 按物理行号确定窗口范围。比如ROWS BETWEEN 5 PRECEDING AND CURRENT ROW意思就是从当前行往上数 5 行。这是一个纯粹的行定位操作排序后直接就能做。RANGE 按列值确定窗口范围。比如RANGE BETWEEN 5 PRECEDING AND CURRENT ROW意思是 ORDER BY 列的值在当前值减 5 到当前值之间的所有行。这里的关键在于值范围而不是行数。举个例子-- 原始数据按 score 排序 -- 行号 score -- 1 60 -- 2 75 -- 3 85 -- 4 85 -- 5 85 -- 6 90 -- 7 99 -- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 第4行(score85)的窗口行2、行3、行4 (score: 75, 85, 85) -- 窗口固定 3 行 -- RANGE BETWEEN 5 PRECEDING AND CURRENT ROW -- 第4行(score85)的窗口score 在 80~85 之间的行 -- 行3、行4、行5 (score 都是 85在范围内) -- 窗口大小取决于值分布可能 3 行也可能 10 行对于日期列来说就更明显RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW需要数据库检查每一行是否在你这个日期往前 30 天的范围内——不是数行数而是比较值的大小。三、为什么 RANGE 这么慢RANGE 的慢根子在于三个地方1. 无法直接定位窗口边界。ROWS 模式下当前行往前 N 行的位置就是排序序列的第 (row_number - N) 行这是 O(1) 的查找。但 RANGE 模式下你得找到ORDER BY 列值 (当前值 - 偏移量)的位置这通常需要额外的查找或二分搜索。-- 用 EXPLAIN 观察两种写法的执行计划差异 -- ROWS 模式快 EXPLAIN SELECT AVG(amount) OVER ( ORDER BY trade_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) FROM transactions; -- RANGE 模式慢 EXPLAIN SELECT AVG(amount) OVER ( ORDER BY trade_date RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW ) FROM transactions;在 MySQL 8.0 的 EXPLAIN 输出中ROWS 模式通常只显示Window aggregate with buffering: using temporary table而 RANGE 模式会多出一步range checked for each record——这就是它逐个比较值的证据。2. 窗口大小不可预测。ROWS 模式窗口大小是固定的就是 N 行数据库可以提前分配固定大小的缓冲区。RANGE 模式的窗口大小取决于数据分布-- 电商订单表工作日每天 5000 订单周末每天 1000 订单 -- RANGE BETWEEN 7 DAY PRECEDING ... -- 窗口大小工作日 ~35000 行周末 ~26000 行忽大忽小 -- 内存管理、缓冲区分配都很难优化3. 同等 ORDER BY 值的行需要特殊处理。这是 RANGE 最容易被忽略的性能杀手。假设有一万行交易记录发生在同一天完全相同 trade_date那么这一万行的窗口范围是完全一样的窗口也完全一样——但数据库仍然要逐行计算一遍。-- 优化技巧如果 ORDER BY 列有大量重复值 -- 可以先用子查询去重再做窗口计算 WITH dedup AS ( SELECT trade_date, stock_code, SUM(volume) AS total_volume -- 先按日期聚合 FROM stock_daily GROUP BY trade_date, stock_code ) SELECT trade_date, stock_code, total_volume, AVG(total_volume) OVER ( PARTITION BY stock_code ORDER BY trade_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS avg_volume_30d FROM dedup;四、怎么选、怎么优化选择原则很简单场景推荐原因日期连续无缺失不需要精确时间范围ROWS5~10 倍性能提升日期有大量缺失必须做时间范围RANGE 日期维度表补全先补全再 ROWSORDER BY 列是整数/数值需要值范围逻辑RANGE唯一选择无法用 ROWS 替代计算移动平均、累计值等纯行偏移逻辑ROWS百分百正确优化技巧一用日期维度表把 RANGE 转成 ROWS。-- 创建一个连续的日期维度表 CREATE TABLE dim_date ( date_id INT PRIMARY KEY, -- 自增编号连续无间隔 actual_date DATE NOT NULL -- 实际日期 ); -- 查询时 JOIN 维度表用连续编号做 ROWS 窗口 WITH t AS ( SELECT s.trade_date, s.stock_code, s.volume, d.date_id -- 用连续编号替代日期 FROM stock_daily s JOIN dim_date d ON s.trade_date d.actual_date ) SELECT trade_date, stock_code, volume, AVG(volume) OVER ( PARTITION BY stock_code ORDER BY date_id -- 用连续编号排序 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW -- 固定 30 行 ) AS avg_volume_30d FROM t;即使有非交易日的数据缺失ROWS 29 PRECEDING仍然取的是最近 30 个交易日因为有记录的交易日是连续的和业务语义吻合。这个方法在我们在ClickHouse的财务报表中验证过30日滚动平均从原有的89秒优化到了5秒以内效果非常显著。优化技巧二利用UNBOUNDED PRECEDING的累积优化。-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 数据库可以用累积算法 O(n) 一次遍历完成无需每行重新扫描 -- 这是窗口函数最快的模式 SELECT trade_date, amount, SUM(amount) OVER ( ORDER BY trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_sum FROM transactions;优化技巧三避免在 WHERE 后用窗口函数做筛选。窗口函数在 WHERE 之前计算如果你的 WHERE 条件依赖窗口结果要先放到子查询里-- ❌ 错误WHERE 不能直接引用窗口函数结果 -- SELECT * FROM t WHERE ROW_NUMBER() OVER(...) 1 -- ✅ 正确用子查询 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt DESC) AS rn FROM t ) sub WHERE rn 1;五、总结ROWS 和 RANGE 性能差异的根源在于ROWS 按物理位置直接定位O(1) 查找RANGE 按值范围逐个比较O(log N) 到 O(N) 不等。日常分析中能用 ROWS 就别用 RANGE除非你真的需要值范围语义。如果不得不做时间范围的窗口计算先用日期维度表把不连续的日期映射成连续的行号再用 ROWS 替代 RANGE收益非常可观。另外记得给你的窗口函数列建好排序索引——这又是一个很多人忽略但效果立竿见影的优化点。最后提醒一点这个方案在上生产之前建议先用灰度流量验证一周确认资源消耗在预期范围内再全量推送。我们在实际项目中因为跳过了这步有一次把缓存集群打挂了教训深刻。