MySQL 中 ROW_NUMBER() 函数最佳实践

目录

• mysql 中 ROW_NUMBER() 函数详解
• 一、基础语法
• 二、核心特点
• 三、典型应用场景
• 1. 数据分页查询
• 2. 删除重复数据
• 3. 分组取Top N记录
• 4. 生成唯一流水号
• 四、与其他排序函数对比
• 五、性能优化技巧
• 1. 索引设计
• 2. 减少计算范围
• 3. 避免嵌套查询
• 六、MySQL低版本兼容方案（5.7及以下）
• 七、常见错误与排查
• 八、最佳实践

MySQL 中 ROW_NUMBER() 函数详解

ROW_NUMBER() 是 SQL 窗口函数中的一种，用于为查询结果集中的每一行分配一个​​唯一的连续序号​​。与 RANK() 和 DENSE_RANK() 不同，ROW_NUMBER() 不会处理重复值，即使排序字段值相同，也会严格按行顺序递增编号。

一、基础语法

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

• ​​PARTITION BY​​：按指定字段分组，每组内重新从1开始编号。
• ​​ORDER BY​​：决定排序逻辑，影响行号的分配顺序。

二、核心特点

三、典型应用场景

1. 数据分页查询

-- 查询第3页数据（每页10条）
WITH paged_data AS (
    SELECT 
        id, name, 
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users
)
SELECT * 
FROM paged_data 
WHERE row_num BETWEEN 21 AND 30;

2. 删除重复数据

-- 保留最新记录（假设 create_time 为时间戳）
DELETE FROM orders
WHERE (i编程d, product_id) IN (
    SELECT id, product_id FROM (
        SELECT 
            id, product_id,
            ROW_NUMBER() OVER (
                PARTITION BY product_id 
                ORDER BY create_time DESC
            ) AS rn
        FROM orders
    ) t 
    WHERE rn > 1  -- 删除重复项，保留最新一条
);

3. 分组取Top N记录

-- 获取每个部门薪资前3名
SELECT *
FROM (
    SELECT 
        name, department, salary,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS dept编程客栈_rank
    FROM employees
) ranked
WHERE dept_rank <= 3;

4. 生成唯一流水号

-- 按日期生成订单流水号（格式：YYYYMMDD-0001）
SELECT 
    order_id,
    CONCAT(
        DATE_FORMAT(create_time, '%Y%m%d编程客栈'), 
        '-', 
     http://www.devze.com   LPAD(ROW_NUMBER() OVER (
            PARTITION BY DATE(create_time) 
            ORDER BY create_time
        ), 4, '0')
    ) AS serial_num
FROM orders;

四、与其他排序函数对比

-- 对比三种函数
SELECT 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_scores;

五、性能优化技巧

1. 索引设计

为 PARTITION BY 和 ORDER BY 涉及的字段创建联合索引：

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

2. 减少计算范围

-- 仅处理2023年数据
SELECT *
FROM (
    SELECT 
        order_id, amount,
        ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM orders
    WHERE YEAR(order_date) = 2023  -- 先过滤再排序
) t
WHERE rn <= 100;

3. 避免嵌套查询

-- 优化前（性能差）
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (...) AS rn
    FROM large_table
) t WHERE rn <= 100;
-- 优化后（直接使用LIMIT，若逻辑允许）
SELECT *, ROW_NUMBER() OVER (...) AS rn
FROM large_table
ORDER BY ...
LIMIT 100;

六、MySQL低版本兼容方案（5.7及以下）

使用会话变量模拟 ROW_NUMBER()

-- 按部门分组排序
SELECT 
    department, name, salary,
    @row_num := IF(
        @current_dept = department, 
        @row_num + 1, 
        1
    ) AS row_num,
    @current_dept := department AS dummy
FROM employees
ORDER BY department, salary DESC;

七、常见错误与排查

1. 错误：序号不符合预期

• ​​原因​​：未正确指定 ORDER BY 或 PARTITION BY
• ​​解决​​：检查排序字段是否明确，分组条件是否合理

2. 错误：性能低下

• ​​原因​​：未使用索引导致全表扫描
• ​​解决​​：使用 EXPLAIN 分析执行计划，添加必要索引

3. 错误：结果集为空

• ​​原因​​：外层查询条件与子查询中的 WHERE 冲突
• ​​解决​​：验证过滤条件逻辑

八、最佳实践

• ​​明确排序规则​​：始终显式指定 ORDER BY 的排序方向（ASC/Dhttp://www.devze.comESC）
• ​​慎用全局排序​​：避免无 PARTITION BY 的大数据集操作
• ​​监控内存使用​​：窗口函数可能消耗大量临时内存
• ​​版本验证​​：生产环境确认 MySQL 版本 >= 8.0
• ​​结合 CTE 使用​​：提高复杂查询的可读性

WITH ranked_products AS (
    SELECT 
        product_id,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
)
SELECT * FROM ranked_products WHERE rn = 1;

​​总结​​：ROW_NUMBER() 是处理行级序号分配的利器，特别适合需要精确控制行顺序的场景。合理使用可显著简化分页、去重、Top N查询等操作，但需注意其对性能的影响，尤其在处理海量数据时需结合索引优化。

到此这篇关于MySQL 中 ROW_NUMBER() 函数详解的文章就介绍到这了,更多相关mysql row_number()函数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！