MySQL 8.0窗口函数性能优化：排序与分组场景实战

窗口函数执行计划分析与瓶颈定位

MySQL 8.0引入的窗口函数（Window Functions）极大简化了排名、累计求和、环比分析等SQL编写，但不当使用会导致全表扫描和临时表排序的性能灾难。理解窗口函数的执行机制是优化的前提。

窗口函数的执行阶段在WHERE、GROUP BY、HAVING之后，ORDER BY之前。这意味着窗口函数无法利用WHERE条件减少中间结果集大小（除非窗口函数引用的列上有过滤条件），其计算量取决于进入窗口计算阶段的数据行数。

通过EXPLAIN ANALYZE查看窗口函数的实际执行情况：

EXPLAIN ANALYZE
SELECT
  department_id,
  employee_name,
  salary,
  RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees
WHERE hire_date >= "2024-01-01";

# 重点关注输出中的：
# -> Window aggregate with ranking: actual time=... rows=...
# -> Sort: actual time=... rows=...
# 若rows远大于过滤后的行数，说明排序阶段处理了过多数据

PARTITION BY与索引匹配策略

窗口函数性能的核心优化点是让PARTITION BY + ORDER BY匹配索引。MySQL在执行窗口函数时，如果PARTITION BY和ORDER BY列有对应的复合索引，可以避免额外排序操作。

索引设计原则：复合索引的列顺序 = PARTITION BY列 + ORDER BY列。例如：

-- 窗口函数查询
SELECT
  department_id,
  salary,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY hire_date
  ) AS cum_salary
FROM employees;

-- 对应的最优索引
CREATE INDEX idx_dept_hire
ON employees(department_id, hire_date);

-- 索引匹配验证
EXPLAIN SELECT ...
# Extra列出现 "Using index for group-by"
# 或无 "Using filesort" 表示索引有效

常见错误：将ORDER BY列放在索引前面，导致PARTITION BY无法利用索引排序。如INDEX(hire_date, department_id)对上述窗口函数无效，因为PARTITION BY要求按department_id分组，hire_date在前的索引无法提供分组有序的扫描。

大结果集场景的替代方案

当窗口函数处理的数据量超过百万行时，即使有索引，排序操作仍会消耗大量CPU和临时表空间。此时考虑以下替代方案：

方案一：分批次处理。将大查询拆分为按PARTITION BY值逐个分区查询，避免一次性对所有分区排序：

-- 原始写法：对所有部门一次性排名
SELECT
  employee_name,
  department_id,
  RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS rnk
FROM employees;

-- 优化写法：先获取分区列表，再逐个分区查询
-- 应用层循环每个department_id
SELECT
  employee_name,
  department_id,
  RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE department_id = ?;

方案二：物化中间结果。对于复杂分析（多级排名+累计+环比），先将基础查询结果写入临时表并建立索引，再对临时表执行窗口函数：

CREATE TEMPORARY TABLE tmp_dept_stats AS
SELECT department_id, employee_id, salary, hire_date
FROM employees
WHERE hire_date >= "2024-01-01";

CREATE INDEX idx_tmp
ON tmp_dept_stats(department_id, hire_date);

-- 对临时表执行窗口函数，数据量已缩减且有序
SELECT
  department_id,
  salary,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY hire_date
  ) AS cum_salary
FROM tmp_dept_stats;

ROWS/RANGE帧的精确控制

窗口帧（Window Frame）定义了窗口函数的计算范围，默认帧RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW在某些场景下会触发额外排序。显式指定ROWS帧可提升性能：

-- RANGE帧：需要处理peer值（相同ORDER BY值的行）
SUM(salary) OVER (
  PARTITION BY department_id
  ORDER BY hire_date
  RANGE BETWEEN UNBOUNDED PRECEDING
  AND CURRENT ROW
)

-- ROWS帧：严格按行号计算，性能更好
SUM(salary) OVER (
  PARTITION BY department_id
  ORDER BY hire_date
  ROWS BETWEEN UNBOUNDED PRECEDING
  AND CURRENT ROW
)

ROWS和RANGE的差异仅在ORDER BY列有重复值时才体现。RANGE模式下，相同hire_date的行会被视为同一个peer组，SUM计算包含整个peer组的值；ROWS模式下严格按行号计算。业务逻辑允许时优先用ROWS帧。

SQL查询优化：窗口函数与JOIN的交互

窗口函数与JOIN组合时，执行顺序直接影响性能。原则是先过滤（JOIN + WHERE）缩减数据量，再执行窗口函数：

-- 低效写法：先窗口函数再JOIN，窗口函数在全表上计算
SELECT e.employee_name, d.department_name, t.dept_rank
FROM (
  SELECT *,
    RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS dept_rank
  FROM employees
) t
JOIN departments d ON t.department_id = d.id
WHERE d.region = "East" AND t.dept_rank <= 3;

-- 高效写法：先JOIN过滤再窗口函数
SELECT e.employee_name, d.department_name, r.dept_rank
FROM (
  SELECT
    employee_name,
    department_id,
    salary,
    RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS dept_rank
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  WHERE d.region = "East"
) r
WHERE r.dept_rank <= 3;

第二种写法中，窗口函数只在region=East的员工子集上计算，排序量显著减少。实际测试中，100万行员工表+10个部门，region过滤后剩20万行，窗口函数排序时间从3.2秒降至0.6秒。

数据库高可用架构中的窗口函数注意事项

在主从复制架构中，大结果集的窗口函数查询可能导致从库延迟。建议将分析类窗口函数查询路由到只读从库，避免影响主库写入性能。通过ProxySQL或MySQL Router配置读写分离规则，将包含窗口函数的SELECT语句自动路由到从库。同时设置从库的long_query_time阈值，对超时的窗口函数查询记录慢日志，及时优化。