MySQL 8.0窗口函数性能优化与索引策略实战

MySQL窗口函数执行原理与性能瓶颈

MySQL 8.0引入的窗口函数(Window Functions)极大简化了排名、累计求和、移动平均等分析查询的编写。但窗口函数的执行机制与普通聚合函数有本质区别:聚合函数将每组折叠为一行,窗口函数保留每一行并附加计算结果。这意味着执行器需要在排序后维护一个滑动窗口帧,内存开销和CPU消耗远高于普通聚合。

EXPLAIN分析窗口函数查询时,执行计划中通常会出现Using temporary和Using filesort。这是因为窗口函数依赖分区和排序——PARTITION BY需要分组,ORDER BY需要排序,两者都会产生临时表和排序操作。

-- 典型窗口函数查询
SELECT 
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_salary
FROM employees
WHERE year = 2026;

这条查询包含两个窗口函数,它们的PARTITION BY和ORDER BY不同,MySQL需要执行两次排序。执行器先按(department, salary DESC)排序计算ROW_NUMBER,再按(department, hire_date)排序计算SUM。两次排序的开销是O(2 * N * logN)。

多窗口函数排序合并优化

当查询包含多个窗口函数时,如果它们的PARTITION BY和ORDER BY相同,MySQL可以合并为一次排序。这是最有效的优化手段之一:

-- 可合并:两个窗口函数分区和排序键一致
SELECT 
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;

-- 不可合并:排序键不同,需要两次排序
SELECT 
    employee_name,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank,
    ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees;

第一个查询中三个窗口函数共享(department, salary DESC)排序,只产生一次排序操作。第二个查询两个窗口函数排序键不同,MySQL必须执行两次独立排序。设计查询时应尽量合并窗口函数的分区和排序键,必要时通过调整业务逻辑使排序方向一致。

窗口函数索引策略与覆盖索引设计

窗口函数的排序和分区操作可以受益于合适的索引。索引设计与窗口函数的PARTITION BY和ORDER BY子句直接相关:

-- 窗口函数: PARTITION BY department ORDER BY salary DESC
-- 最优索引:分区列在前,排序列在后
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

-- 窗口函数: PARTITION BY department, year ORDER BY hire_date
-- 复合索引
CREATE INDEX idx_dept_year_hire ON employees(department, year, hire_date);

当索引列与窗口函数的分区+排序列完全匹配时,MySQL可以直接利用索引的有序性避免额外排序操作。EXPLAIN中filesort标记消失,Using index表示覆盖索引生效。对于大表(百万行以上),这个优化可以将查询时间从秒级降到百毫秒级。

覆盖索引(covering index)还需要包含SELECT列表中的其他列。如果查询只需要department, salary, employee_name三列,将employee_name追加到索引末尾可以避免回表:

-- 覆盖索引:包含查询所需的所有列
CREATE INDEX idx_dept_salary_cover ON employees(department, salary DESC, employee_name);

ROWS帧与RANGE帧性能差异

窗口帧(Window Frame)定义了窗口函数的计算范围。ROWS帧基于物理行号,RANGE帧基于逻辑偏移。两者的性能差异巨大:

-- ROWS帧:基于行号,O(1)滑动窗口计算
SUM(amount) OVER (ORDER BY tx_date 
                  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d

-- RANGE帧:基于值范围,每行需要重新扫描
SUM(amount) OVER (ORDER BY tx_date 
                  RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW) AS range_30d

ROWS帧的滑动窗口计算是增量式的:窗口向前滑动一行,减去离开窗口的行,加上进入窗口的行,时间复杂度O(1)每行。RANGE帧的INTERVAL范围需要在每行重新定位边界(因为tx_date可能不连续),时间复杂度O(N)每行,总计O(N^2)。

在交易日期连续的场景下(如股票每日行情),ROWS帧和RANGE帧结果相同,应优先使用ROWS帧。如果日期有间断(如工作日跳过周末),RANGE帧结果更准确但性能更差。折中方案是用日历辅助表补齐缺失日期,然后用ROWS帧计算。

大表窗口函数分页查询优化

对大表做窗口函数计算后再分页,传统LIMIT OFFSET方式性能极差,因为每次翻页都需要重新计算所有窗口函数。优化方案是将窗口函数结果写入临时表或物化CTE:

-- 优化:CTE物化 + 键集分页
WITH ranked_employees AS (
    SELECT 
        id,
        department,
        employee_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
    WHERE year = 2026
)
SELECT * FROM ranked_employees
WHERE dept_rank <= 10
  AND (department > 'Engineering' OR 
       (department = 'Engineering' AND id > 12345))
ORDER BY department, id
LIMIT 20;

键集分页(Keyset Pagination)通过记录上一页最后一个键值(department, id)作为下一页的起点,避免OFFSET扫描。窗口函数计算一次后,分页查询直接在结果集上做范围扫描,响应时间稳定在毫秒级,与页码无关。