MySQL 8.0窗口函数性能优化：从执行计划到内存控制

窗口函数的执行原理与开销来源

MySQL 8.0引入的窗口函数（Window Functions）大幅简化了排名、累计求和、同期对比等分析SQL的编写，但底层执行机制与传统聚合函数有本质区别。窗口函数不会减少结果行数，而是为每一行计算一个窗口范围内的聚合值，这意味着执行成本与数据量和窗口定义直接相关。

通过EXPLAIN ANALYZE可以观察窗口函数的实际执行开销：

EXPLAIN ANALYZE
SELECT
    department,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
    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;

-> Window aggregate: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
   -> Sort: department, salary DESC  (cost=12.5 rows=10000) (actual time=0.8..45.2 ms)
-> Window aggregate: SUM(salary) OVER (...)
   -> Sort: department, hire_date  (cost=8.3 rows=10000) (actual time=0.5..38.1 ms)

每个窗口函数独立触发一次排序操作。两个窗口函数使用不同的ORDER BY时，MySQL无法合并排序，会执行两次全表排序。这是窗口函数性能问题的最主要来源。

排序合并与窗口复用优化

当多个窗口函数共享相同的PARTITION BY和ORDER BY时，MySQL可以复用排序结果，只排序一次：

-- 可复用排序（PARTITION BY + ORDER BY相同）
SELECT
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rk
FROM employees;

-- 不可复用（ORDER BY不同，两次排序）
SELECT
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cum_sal
FROM employees;

优化策略：将ORDER BY不同的窗口函数拆到子查询中，减少单次排序的数据量：

-- 拆分子查询减少排序压力
SELECT t1.*, t2.cum_salary
FROM (
    SELECT department, employee_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
    WHERE year = 2026
) t1
JOIN LATERAL (
    SELECT SUM(salary) OVER (PARTITION BY department ORDER BY hire_date
                             ROWS UNBOUNDED PRECEDING) AS cum_salary
    FROM employees e2
    WHERE e2.department = t1.department AND e2.year = 2026
) t2;

ROWS与RANGE框架的选择

窗口框架定义直接影响计算复杂度。ROWS框架基于物理行号定位窗口范围，RANGE框架基于逻辑值定位：

-- ROWS框架：O(n)复杂度，逐行滑动窗口
SUM(amount) OVER (
    ORDER BY tx_date
    ROWS BETWEEN 30 PRECEDING AND CURRENT ROW
)

-- RANGE框架：O(n log n)复杂度，需二分查找窗口边界
SUM(amount) OVER (
    ORDER BY tx_date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
)

ROWS框架性能更好，但在有相同排序值时语义不同。如果业务允许用ROWS替代RANGE，优先使用ROWS。当ORDER BY列有重复值时，RANGE框架会将相同值的行归入同一窗口，ROWS则严格按行号划分，需根据业务需求选择。

内存控制与临时表溢出

窗口函数的排序和计算需要内存缓冲区。当数据量超过sort_buffer_size时，排序溢出到磁盘临时表，性能急剧下降。关键参数调优：

-- 查看当前sort_buffer_size（默认256KB，偏小）
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 针对窗口函数场景增大到8MB
SET SESSION sort_buffer_size = 8388608;

-- 监控排序溢出次数
SHOW STATUS LIKE 'Sort_merge_passes';

-- 如果Sort_merge_passes持续增长，继续增大sort_buffer_size
SET SESSION sort_buffer_size = 16777216; -- 16MB

也可以通过information_schema观察窗口函数的临时表使用情况：

SELECT * FROM performance_schema.memory_global_by_current_bytes
WHERE event_name LIKE '%sort%' OR event_name LIKE '%window%';

索引辅助窗口函数排序

如果窗口函数的PARTITION BY + ORDER BY与某个索引的列顺序匹配，MySQL可以利用索引的有序性避免额外排序：

-- 创建匹配窗口函数的复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

-- 这个查询可以直接利用索引，跳过filesort
SELECT department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
FROM employees;

EXPLAIN中出现”Using index for group-key”表示索引被窗口函数复用。注意索引列顺序必须与PARTITION BY + ORDER BY完全一致，且排序方向也要匹配。如果窗口函数是ORDER BY salary DESC，索引也必须定义为salary DESC（MySQL 8.0支持降序索引）。

分区表场景下的窗口函数

在分区表上使用窗口函数时，如果没有在WHERE条件中过滤分区，窗口函数会对所有分区执行全表扫描和排序。建议在查询前通过PARTITION子句限定分区：

-- 低效：扫描所有分区
SELECT order_id, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY order_date
                         ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM orders;

-- 高效：限定分区
SELECT order_id, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY order_date
                         ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM orders PARTITION(p2026q3);

窗口函数的性能优化需要从执行计划入手，识别排序溢出和重复排序两个核心问题，通过索引匹配、框架选择、内存参数调整三个维度综合施策。生产环境中建议将窗口函数查询的执行时间纳入慢查询监控，阈值可设为普通查询的3倍——窗口函数的执行开销本身较高，不应与简单查询使用同一阈值。