MySQL 8.0窗口函数性能优化：执行计划分析与索引策略

窗口函数为什么容易成为查询瓶颈

MySQL 8.0引入窗口函数（Window Functions）后，排名、累计求和、同组Top-N等场景的SQL写法大幅简化。但窗口函数的执行机制和普通聚合查询有本质区别——它需要对分区内的所有行排序后再逐行计算，排序开销往往远超预期。一条写法简洁的窗口函数查询，实际执行计划可能触发全表扫描和filesort，在大表上跑出分钟级的延迟。

理解窗口函数的执行流程是优化的前提：MySQL先按分区键分组，再按排序键排序，最后逐行应用窗口函数逻辑。三个步骤中排序是性能大头。

EXPLAIN分析窗口函数执行计划

用一条典型查询演示：

SELECT
department,
employee_id,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
WHERE hire_date >= '2024-01-01';

执行EXPLAIN：

EXPLAIN FORMAT=JSON
SELECT ... \G

关注三个字段：

1. type：如果是ALL（全表扫描），说明WHERE条件和索引不匹配。
2. Extra：出现”Using filesort”表示排序未走索引，需要额外排序步骤。窗口函数场景下filesort几乎必然出现，因为排序键和分区键通常不同于索引顺序。
3. windowing：JSON格式EXPLAIN会显示窗口函数的具体执行方式，关注”sorting”步骤的rows估算值。

索引策略：覆盖分区键和排序键

窗口函数优化的核心思路是让MySQL直接从索引中获取有序数据，避免filesort。索引设计原则：

规则一：复合索引按(partition_key, order_key)创建。

上面查询需要的索引：

CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

这样MySQL按department分区后，salary已经有序，窗口函数排序步骤直接跳过。

规则二：WHERE条件字段放在最左前缀。

查询带hire_date过滤时：

CREATE INDEX idx_hire_dept_salary ON employees(hire_date, department, salary DESC);

MySQL先通过hire_date过滤行，再从索引中按(department, salary)顺序读取，窗口函数无需额外排序。

验证效果：EXPLAIN的Extra列不再有”Using filesort”，type变为ref或range。

ROWS/RANGE帧的性能差异

窗口函数的帧定义（Frame Specification）影响计算方式：

-- ROWS帧：按物理行号计算，O(1)每行
SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- RANGE帧：按逻辑值计算，O(log N)每行
SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

ROWS帧在有序数据上可以用滑动窗口算法，每行O(1)累加。RANGE帧需要二分查找确定帧边界，每行O(log N)。当数据量超过百万行时，RANGE帧的累计求和可能比ROWS慢3-5倍。

优化建议：如果业务语义允许，优先用ROWS帧。RANGE帧只在需要处理同值行聚合时才必要（例如相同hire_date的员工合计归入同一组）。

分库分表场景下窗口函数的替代方案

窗口函数在分库分表架构中无法跨分片执行。一个分页排名查询需要所有分片数据汇总后排序，这在中间件层（ShardingSphere等）实现代价极高。实际替代方案：

1. 应用层聚合：从各分片取Top-N，在应用层归并排序。适用于排名查询。
2. 汇总表：用定时任务将排名结果写入汇总表，查询直接读汇总表。适用于实时性要求不高的场景。
3. Redis ZSET：实时维护部门薪资排名，ZREVRANGEBYSCORE返回Top-N。适用于高频查询的热数据。

窗口函数不是万能工具，在分布式事务和分库分表方案中要清醒认识到它的边界，选择合适的替代实现。