MySQL分页：ROW_NUMBER() vs LIMIT

在 MySQL 中，实现数据分页的方法主要有两种：使用 ROW_NUMBER() 窗口函数和使用 LIMIT 子句。本文将探讨这两种方法的优劣，帮助您选择最适合您的场景。

1. ROW_NUMBER() 分页

ROW_NUMBER() 是 MySQL 8.0 及以上版本支持的窗口函数，它为结果集中的每一行分配一个唯一的行号，通常用于复杂查询和需要灵活分页的场景。

优点：

• 灵活性：可以处理复杂的排序和分组需求。例如，在一个包含子查询、聚合函数和复杂过滤条件的查询中，ROW_NUMBER() 可以为结果集中的每一行分配一个唯一的行号，然后在外层查询中进行分页。
• 可读性：查询逻辑清晰，尤其是在处理需要分组或分区的情况下，能够轻松地按特定条件分页。

缺点：

• 性能开销：相比 LIMIT，ROW_NUMBER() 可能需要更多的计算资源，因为它需要对整个结果集进行排序并计算行号，尤其在大数据量的情况下。
• 复杂度：对于简单的分页需求，使用 ROW_NUMBER() 可能显得过于复杂。

示例：

WITH RankedProducts AS (
    SELECT
        id,
        name,
        price,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM
        products
)
SELECT
    id,
    name,
    price
FROM
    RankedProducts
WHERE
    row_num BETWEEN 4 AND 6;

2. LIMIT 分页

LIMIT 是一种传统的分页方法，通过限制返回的记录数和偏移量来实现分页。它在 MySQL 的所有版本中都支持，通常用于简单的分页需求。

优点：

• 性能高效：对于简单查询，LIMIT 通常比 ROW_NUMBER() 更高效，因为它只需要处理指定范围内的数据，而不需要为整个结果集分配行号。
• 简单易用：语法简单，适用于大多数基本分页需求。

缺点：

• 不适用复杂查询：在需要复杂排序、分组或子查询的情况下，LIMIT 的功能有限，难以处理复杂的分页需求。
• 结果不稳定：在大数据集和频繁更新的表中，分页结果可能不稳定，因为 LIMIT 分页依赖于数据的顺序，而数据顺序可能随时间变化。

示例：

SELECT
    id,
    name,
    price
FROM
    products
ORDER BY
    id
LIMIT 3 OFFSET 3;

 

性能比较

1. 小数据集：
   • 对于小数据集，两者性能差异不大，选择 LIMIT 更为简单直接。
2. 大数据集：
   • 在大数据集下，LIMIT 的性能更优，因为 ROW_NUMBER() 需要对整个结果集进行排序和计算行号。
3. 复杂查询：
   • 对于复杂查询，ROW_NUMBER() 提供了更高的灵活性和可读性，尽管可能需要优化以提高性能。

实际应用场景

1. 简单分页：
   • 使用 LIMIT，如展示前端页面上的简单数据列表。
2. 复杂业务逻辑分页：
   • 使用 ROW_NUMBER()，如需要根据特定业务规则进行排序和过滤的分页需求。

结论

选择 ROW_NUMBER() 还是 LIMIT 进行分页，取决于您的具体需求和数据规模：

• 对于简单、高效的分页需求，尤其是在大数据集下，推荐使用 LIMIT。
• 对于复杂的分页需求，需要灵活排序和分组时，推荐使用 ROW_NUMBER()。

通过理解这两种分页方法的特点和适用场景，您可以在不同的业务需求中做出最佳选择。