别再傻傻分不清了！用MySQL 8.0实战演示row_number、rank、dense_rank到底怎么选

MySQL 8.0排名函数实战指南：row_number、rank、dense_rank的智能选择

每次面对需要排名的SQL查询时，你是否也在纠结该用哪个窗口函数？row_number、rank还是dense_rank？这三个看似相似的函数，在实际业务场景中却有着截然不同的表现。让我们通过一个电商平台的真实案例，彻底搞懂它们之间的区别和适用场景。

1. 电商排名场景搭建与基础概念

假设我们正在为某电商平台开发用户积分排行榜功能，数据表结构如下：

CREATE TABLE user_points ( user_id INT PRIMARY KEY, username VARCHAR(50) NOT NULL, points INT NOT NULL, registration_date DATE ); -- 插入测试数据 INSERT INTO user_points VALUES (101, '数码达人', 8500, '2023-01-15'), (102, '时尚买手', 9200, '2023-02-20'), (103, '家居专家', 9200, '2023-03-10'), (104, '美妆教主', 7800, '2023-01-05'), (105, '美食家', 9200, '2023-04-01'), (106, '旅行家', 8100, '2023-02-28'), (107, '图书爱好者', 7600, '2023-03-15'), (108, '运动健将', 8900, '2023-01-20');

1.1 窗口函数基础语法

所有三个排名函数都遵循相同的语法模式：

函数名() OVER ( [PARTITION BY 分组字段] ORDER BY 排序字段 [ASC|DESC] )

• PARTITION BY：可选，用于先分组再组内排序
• ORDER BY：必需，指定排序依据的字段和方向

提示：在MySQL 8.0+中，窗口函数性能已大幅优化，但大数据量时仍需注意合理使用索引。

2. 三大排名函数深度对比

2.1 row_number：严格的顺序编号

row_number()为每一行分配唯一的连续序号，即使排序值相同：

SELECT user_id, username, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM user_points;

执行结果：

核心特点：

• 绝对唯一的连续编号
• 相同points值的用户获得不同rank值
• 适合需要严格区分名次的场景

2.2 rank：允许并列的真实排名

rank()函数会在值相同时给相同排名，但会保留排名间的"空隙"：

SELECT user_id, username, points, RANK() OVER (ORDER BY points DESC) AS rank FROM user_points;

执行结果：

关键区别：

• 三个9200分的用户并列第1名
• 下一个用户直接跳到第4名（跳过了2、3）
• 符合传统体育比赛的排名方式

2.3 dense_rank：紧凑的连续排名

dense_rank()与rank()类似允许并列，但排名数字是连续的：

SELECT user_id, username, points, DENSE_RANK() OVER (ORDER BY points DESC) AS rank FROM user_points;

执行结果：

显著特征：

• 并列第一后，下一个是第二而非第四
• 排名数字连续无间隔
• 总排名数量最少

3. 业务场景选择指南

3.1 何时选择row_number？

适用场景：

1. 需要绝对唯一的标识符（如分页查询）
2. 生成连续的行号（如导出报表）
3. 不允许并列的严格排名（如某些竞赛规则）

电商案例：

-- 为每个用户生成唯一的会员编号（基于注册时间） SELECT user_id, username, ROW_NUMBER() OVER (ORDER BY registration_date) AS member_number FROM user_points;

3.2 何时选择rank？

适用场景：

1. 体育比赛排名（允许并列且保留名次间隔）
2. 需要显示实际排名位置的场景
3. 当业务逻辑要求反映"真实"排名时

电商案例：

-- 显示用户的实际排名（考虑并列情况） SELECT username, points, RANK() OVER (ORDER BY points DESC) AS global_rank FROM user_points WHERE RANK() OVER (ORDER BY points DESC) <= 10; -- 获取前10名

注意：直接在WHERE中使用窗口函数会报错，需要使用子查询或CTE。

3.3 何时选择dense_rank？

适用场景：

1. 需要紧凑连续的排名数字
2. 奖品分配按排名层级而非绝对位置
3. 当排名间隔可能引起误解时

电商案例：

-- 按排名层级发放不同级别的优惠券 SELECT username, points, DENSE_RANK() OVER (ORDER BY points DESC) AS tier FROM user_points WHERE DENSE_RANK() OVER (ORDER BY points DESC) <= 3; -- 获取前三档用户

4. 高级应用与性能优化

4.1 分组排名实战

窗口函数的真正威力在于分组排名：

-- 按注册月份分组后的积分排名 SELECT user_id, username, points, DATE_FORMAT(registration_date, '%Y-%m') AS reg_month, ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(registration_date, '%Y-%m') ORDER BY points DESC) AS month_rank FROM user_points;

4.2 多维度排序技巧

当主要排序字段相同时，可以添加次要排序条件：

-- 积分相同则按注册时间排序 SELECT user_id, username, points, registration_date, RANK() OVER (ORDER BY points DESC, registration_date) AS rank FROM user_points;

4.3 性能优化建议

1. 索引策略：

   • 为PARTITION BY和ORDER BY涉及的字段创建复合索引
   • 示例：CREATE INDEX idx_points_reg ON user_points(points DESC, registration_date)

2. 减少计算量：

   -- 先过滤再排名，提高性能 WITH filtered_users AS ( SELECT * FROM user_points WHERE points > 8000 ) SELECT user_id, username, points, RANK() OVER (ORDER BY points DESC) AS rank FROM filtered_users;

3. 避免重复计算：

   -- 使用CTE避免多次计算相同窗口 WITH ranked_users AS ( SELECT user_id, username, points, RANK() OVER (ORDER BY points DESC) AS rank, DENSE_RANK() OVER (ORDER BY points DESC) AS dense_rank FROM user_points ) SELECT * FROM ranked_users WHERE rank <= 5;

5. 决策树与常见误区

5.1 函数选择决策树

是否需要绝对唯一的序号？ ├── 是 → 使用row_number └── 否 → 是否允许排名并列？ ├── 否 → 使用row_number └── 是 → 是否需要紧凑连续的排名数字？ ├── 是 → 使用dense_rank └── 否 → 使用rank

5.2 常见错误与解决方法

问题1：在WHERE中直接使用窗口函数

-- 错误写法 SELECT * FROM user_points WHERE RANK() OVER (ORDER BY points DESC) <= 3; -- 正确写法 SELECT * FROM ( SELECT *, RANK() OVER (ORDER BY points DESC) AS rank FROM user_points ) AS ranked WHERE rank <= 3;

问题2：忽略NULL值的影响

-- NULL值默认会排在最后（ASC）或最前（DESC） -- 可以使用COALESCE处理 SELECT user_id, username, points, RANK() OVER (ORDER BY COALESCE(points, 0) DESC) AS rank FROM user_points;

问题3：性能问题大数据集

-- 对于大型表，考虑添加LIMIT SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM user_points -- 先限制数据量再排序 LIMIT 1000 ) AS ranked WHERE rank <= 100;

在实际项目中，我发现很多开发者会过度使用row_number，仅仅因为它是第一个学到的窗口函数。但理解这三个函数的本质区别后，你会发现每种业务场景都有最适合的选择。比如在做分页查询时row_number是必须的，但在展示排行榜时rank或dense_rank通常更符合用户预期。