MySQL 8.0窗口函数实战：从分析查询到性能调优全流程

MySQL 8.0窗口函数实战：从分析查询到性能调优

数据库运维中，MySQL 8.0引入的窗口函数（Window Functions）彻底改变了复杂分析查询的写法。以往需要子查询嵌套、临时表、用户变量hack的SQL，现在用窗口函数一行搞定。但窗口函数的执行计划与普通查询差异巨大，不做调优可能导致全表扫描和排序溢出。本文从实战出发，拆解窗口函数的用法和性能优化策略。

SQL查询优化：窗口函数的基础语法与常见模式

窗口函数的核心语法：函数名() OVER (PARTITION BY ... ORDER BY ... frame_clause)。常见业务模式：

模式一：排名
ROW_NUMBER、RANK、DENSE_RANK是窗口函数最常用的场景。三者区别在并列处理：

-- 每个部门的员工薪资排名
-- ROW_NUMBER: 1,2,3,4 (不并列)
-- RANK: 1,1,3,4 (并列跳号)
-- DENSE_RANK: 1,1,2,3 (并列不跳号)
SELECT
    dept_id,
    emp_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank_val
FROM employees
WHERE dept_id IN (1, 2, 3)
ORDER BY dept_id, rank_val;

模式二：累计与滑动窗口
SUM/COUNT/AVG配合frame子句实现累计统计：

-- 每日销售额与7日滑动平均
SELECT
    order_date,
    daily_amount,
    SUM(daily_amount) OVER (ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_sum,
    AVG(daily_amount) OVER (ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM (
    SELECT
        DATE(created_at) AS order_date,
        SUM(amount) AS daily_amount
    FROM orders
    GROUP BY DATE(created_at)
) daily
ORDER BY order_date;

模式三：取每组N条
每个分类取最新3条记录，传统写法需要 correlated subquery，窗口函数方案简洁得多：

-- 每个商品分类取销量前3的商品
SELECT * FROM (
    SELECT
        p.category_id,
        p.product_name,
        p.sales_count,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.sales_count DESC) AS rn
    FROM products p
) ranked
WHERE rn <= 3;

数据库高可用架构下窗口函数的执行计划分析

窗口函数的执行计划有几个关键特征。用EXPLAIN ANALYZE查看真实执行成本：

EXPLAIN ANALYZE
SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount
FROM orders
WHERE order_date >= '2026-01-01';

-- 关注以下指标：
-- 1. type列：如果是ALL，说明缺少索引
-- 2. Extra列出现 "Using temporary; Using filesort"：正常，窗口函数需要排序缓冲
-- 3. actual_time：窗口函数步骤的实际耗时
-- 4. rows_examined_per_scan：扫描行数，如果远大于返回行数，索引有问题

窗口函数执行计划的典型问题：

• 全表排序：PARTITION BY和ORDER BY的列没有联合索引，MySQL对全表做filesort
• 临时表溢出：排序缓冲区(sort_buffer_size)不够大，溢出到磁盘临时文件
• 分区裁剪失效：WHERE条件无法在窗口函数之前过滤，导致全量数据参与窗口计算

MySQL性能调优：窗口函数的索引设计

窗口函数的性能90%取决于索引设计。核心原则：为PARTITION BY + ORDER BY列创建联合索引。

-- 原始查询
SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount
FROM orders
WHERE order_date >= '2026-01-01';

-- 最优索引：PARTITION BY列在前，ORDER BY列在后
CREATE INDEX idx_user_date ON orders (user_id, order_date);

-- 如果查询有WHERE条件，将WHERE列加入索引最左侧
CREATE INDEX idx_date_user ON orders (order_date, user_id);

-- 复合场景：WHERE + PARTITION BY + ORDER BY
-- 索引顺序：WHERE列 → PARTITION BY列 → ORDER BY列
CREATE INDEX idx_date_user ON orders (order_date, user_id, amount);

索引选择不是无脑建。实际测评：

-- 对比不同索引的执行时间
SET profiling = 1;

-- 测试1：无索引
SELECT ... ;  -- 预期：full table scan + filesort, 2-5秒(百万行表)

-- 测试2：索引 (user_id, order_date)
SELECT ... ;  -- 预期：index scan, 0.1-0.5秒

-- 测试3：索引 (order_date, user_id, amount)
SELECT ... ;  -- 预期：index scan + partition prune, 0.05-0.2秒

SHOW PROFILE;

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

窗口函数在分库分表环境下无法直接使用——跨分片的排序和分组必须在应用层或中间件完成。ShardingSphere的处理方式：

-- ShardingSphere 5.x 对窗口函数的改写策略
-- 原始SQL
SELECT user_id, amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;

-- ShardingSphere改写为：
-- Step 1: 发送到各分片执行
SELECT user_id, amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders_0
UNION ALL
SELECT user_id, amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders_1;

-- Step 2: 中间件层合并结果并重新排名
-- 代价：内存消耗 = 所有分片结果集大小之和

-- 替代方案：避免跨分片窗口函数
-- 1. 按PARTITION BY列分片，确保同一分区内数据在同一个分片
-- 2. 使用冗余表：将需要窗口函数的查询场景用物化视图预计算

数据备份恢复场景中的窗口函数应用

窗口函数在数据校验场景中也有实用价值。比对备份前后的数据一致性：

-- 检测数据迁移前后的行数差异和校验和
SELECT
    'source' AS side,
    table_name,
    COUNT(*) AS row_count,
    MD5(GROUP_CONCAT(checksum ORDER BY id)) AS data_hash
FROM source_tables
GROUP BY table_name

UNION ALL

SELECT
    'target' AS side,
    table_name,
    COUNT(*) AS row_count,
    MD5(GROUP_CONCAT(checksum ORDER BY id)) AS data_hash
FROM target_tables
GROUP BY table_name;

-- 使用窗口函数找出不一致的表
WITH both_sides AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY side) AS rn
    FROM comparison
)
SELECT a.table_name, a.row_count AS src_count, b.row_count AS tgt_count
FROM both_sides a
JOIN both_sides b ON a.table_name = b.table_name AND a.rn = 1 AND b.rn = 2
WHERE a.row_count != b.row_count OR a.data_hash != b.data_hash;

窗口函数不是万能解药。对于数据量超过千万行的分析查询，MySQL的排序性能远不如ClickHouse等列式存储。在OLAP场景，考虑将窗口函数逻辑迁移到ClickHouse，MySQL只承担OLTP职责。但在OLTP场景下的中量级分析查询，窗口函数配合正确索引，性能完全够用。