MySQL分页查询LIMIT OFFSET在百万数据下的性能优化

当数据量达到百万级别时，直接使用 LIMIT offset, count 进行分页查询会导致数据库扫描大量无关数据行，随着偏移量（OFFSET）的增加，查询性能呈指数级下降。本文将提供三种经过验证的优化方案，帮助你彻底解决深分页性能瓶颈。

问题复现与分析

假设有一张用户表 users，主键为 id（自增），表中存有 500 万条数据。

1. 执行 以下低效的分页查询语句，获取第 100 万页的数据（每页 10 条）：

   SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

2. 分析 执行逻辑：
   数据库并非直接跳过前 100 万条记录，而是先在磁盘中读取前 1000010 条数据，然后丢弃前 100 万条，仅返回最后 10 条。当偏移量极大时，大量的磁盘 I/O 和数据丢弃操作会消耗数秒甚至数十秒的时间。

方案一：覆盖索引子查询优化（推荐通用场景）

利用 MySQL 的覆盖索引（Covering Index）特性，先在索引树上快速定位主键 ID，再根据 ID 回表查询完整数据。由于索引树比全表数据小得多，只查询 ID 的速度极快。

1. 重写 SQL 语句，将原本的 LIMIT 查询改为子查询形式。

   SELECT * FROM users a
   JOIN (
       SELECT id FROM users ORDER BY id LIMIT 1000000, 10
   ) b ON a.id = b.id;

2. 验证 执行计划。
   执行 EXPLAIN 命令查看新的查询计划：

   EXPLAIN SELECT * FROM users a JOIN (SELECT id FROM users ORDER BY id LIMIT 1000000, 10) b ON a.id = b.id;

   结果显示，子查询部分使用了 PRIMARY 索引，且 Extra 字段显示 Using index，表示只扫描索引树，无需回表读取数据行。外层查询通过主键关联，效率极高。

3. 适用 需要保留传统分页逻辑（如显示页码跳转），且数据库版本较低或不支持某些高级特性的场景。

方案二：游标分页法（性能最优）

放弃传统的 OFFSET 偏移量，改为记录上一页最后一条数据的 ID（或排序字段）。这种方式直接从上次结束的位置开始扫描，完全避免了扫描前 N 条数据的开销。

1. 获取 第一页数据。
   执行 查询获取前 10 条数据：

   SELECT * FROM users ORDER BY id LIMIT 10;

   假设最后一行数据的 id 为 10。

2. 查询 下一页数据。
   传入 上一页最后一条记录的 ID（即 10），执行 以下查询：

   SELECT * FROM users WHERE id > 10 ORDER BY id LIMIT 10;

   该语句利用主键索引直接定位到 ID 为 10 的位置，然后向后读取 10 条数据，无论数据量多大，耗时均为毫秒级。

3. 适用 移动端下拉刷新、无限滚动加载、“上一页/下一页”导航场景。此方法无法直接支持“跳转到第 N 页”的功能。

方案三：ID范围查询法（适用于连续ID）

如果主键 ID 是连续自增且中间没有断层（很少删除数据），可以通过计算 ID 范围来直接定位数据。

1. 计算 起始 ID。
   假设每页显示 10 条，要查看第 100 万页，起始 ID 约为 (1000000 - 1) * 10 + 1 = 10000001。

2. 构建 查询语句。
   使用 WHERE 子句替代 OFFSET：

   SELECT * FROM users WHERE id >= 10000001 LIMIT 10;

3. 适用 数据极少删除、ID 连续的历史数据表。如果 ID 不连续或存在大量删除记录，此方法会导致每页显示的数据条数不一致。

性能对比与方案选择

为了直观对比三种优化方案与原始方案的区别，请参考下表。

优先选择 游标分页法（方案二）以获得极致性能；若必须支持页码跳转，务必采用 覆盖索引子查询（方案一）替代原始写法。