MySQL分页查询中，如何正确实现pageNum和pageSize逻辑？

Question

在使用 MySQL 进行分页查询时，如何正确实现 `pageNum`（当前页码）和 `pageSize`（每页条数）的逻辑是一个常见问题。很多开发者直接使用 `LIMIT offset, size`，但未正确计算偏移量，导致数据重复或遗漏。正确做法是：根据 `offset = (pageNum - 1) * pageSize` 计算起始位置，并结合 `LIMIT` 实现分页。此外，还需考虑排序稳定性、大数据量下的性能影响以及深分页问题。理解这些细节有助于构建高效、可靠的分页查询逻辑。

Answer 1

一、分页查询的基本逻辑

在使用 MySQL 进行数据分页时，常见的参数是 `pageNum`（当前页码）和 `pageSize`（每页条数）。开发者通常会直接使用 `LIMIT offset, size` 的方式来实现分页功能。然而，很多情况下由于对偏移量计算的理解错误，导致出现数据重复或遗漏的问题。

正确的偏移量计算公式应为：

offset = (pageNum - 1) * pageSize

例如，若当前页码为第3页，每页显示10条记录，则起始偏移量应为 (3-1)*10=20，表示从第21条记录开始取。

二、排序稳定性与分页一致性

在进行分页查询时，如果没有指定排序条件，MySQL 可能会返回不同顺序的数据，尤其是在多次查询之间数据发生变化的情况下。这会导致分页结果不稳定甚至数据错乱。

因此，推荐在使用 `LIMIT` 分页时，始终结合 `ORDER BY` 子句以保证结果的排序一致性。例如：

SELECT id, name FROM users ORDER BY created_at DESC LIMIT 20, 10;

这样可以确保每次请求第3页时，都基于相同的排序逻辑获取数据。

三、大数据量下的性能问题

当数据量较大时，使用 `LIMIT offset, size` 的方式会出现性能瓶颈，尤其是深分页（如第10000页）场景下。因为 MySQL 需要扫描大量行后丢弃，仅返回少数几条。

例如以下语句：

SELECT * FROM orders LIMIT 100000, 10;

此时 MySQL 实际上需要读取前100010条记录，并丢弃前面的100000条，效率极低。

四、深分页优化策略

为了解决深分页带来的性能问题，可以采用以下几种优化方式：

• 使用索引字段作为过滤条件，避免全表扫描。
• 通过子查询先获取主键ID，再关联原表查询详细信息。
• 使用游标分页（Cursor-based Pagination），利用上次查询的最后一个值继续查询。

例如，使用主键作为游标的方式如下：

SELECT id, name FROM users WHERE id > 1000 ORDER BY id ASC LIMIT 10;

该方式可以有效减少扫描行数，提升查询效率。

五、综合示例与流程图

假设我们有一个用户表 `users`，字段包括 `id`、`name` 和 `created_at`。我们要实现第3页、每页10条记录的分页查询，且按创建时间降序排列。

SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 20, 10;

对应的流程图如下：

graph TD
      A[开始] --> B[接收 pageNum 和 pageSize]
      B --> C[计算 offset = (pageNum - 1) * pageSize]
      C --> D[构造 SQL 查询语句]
      D --> E[添加 ORDER BY 保证排序一致]
      E --> F[执行查询并返回结果]