MySQL分页查询优化教程：LIMIT性能问题与游标分页方案

分页查询是 MySQL 中非常常见的需求。后台列表、文章列表、订单记录、日志查询都离不开分页。数据量小时，LIMIT offset, size 写起来简单也够用；但数据量变大后，深分页性能问题会越来越明显。

本文围绕 MySQL 分页查询优化展开，先讲普通 LIMIT 分页为什么会慢，再介绍索引优化、延迟关联和游标分页方案，帮助你根据业务场景选择更合适的分页方式。

普通 LIMIT 分页

最常见的分页写法是使用 LIMIT 和偏移量。比如查询第 1001 页，每页 20 条。

SELECT * FROM articles
ORDER BY id DESC
LIMIT 20000, 20;

这条 SQL 看起来只返回 20 条，但 MySQL 往往需要先扫描或跳过前面 20000 条，再取后面的 20 条。偏移量越大，成本越高。

深分页为什么慢

深分页慢的根源在于 offset 很大时，数据库不能直接“跳到”目标位置返回结果。它需要按排序规则找到前面大量记录，再丢弃不需要的部分。

如果查询还涉及回表、文件排序或复杂条件，性能会进一步下降。用户越往后翻页，响应越慢，这就是典型深分页问题。

先保证排序稳定

分页查询必须有稳定排序。不要在没有 ORDER BY 的情况下分页，否则不同请求可能返回重复或遗漏数据。

ORDER BY created_at DESC, id DESC

如果创建时间可能重复，加上唯一 ID 作为第二排序字段，可以让分页结果更稳定。

索引优化

分页查询的排序字段和过滤条件应该尽量匹配索引。比如按状态筛选并按 ID 倒序，可以考虑联合索引。

CREATE INDEX idx_status_id ON articles(status, id);

查询时让 WHERE 和 ORDER BY 尽量走索引，能减少扫描和排序成本。

SELECT id, title FROM articles
WHERE status = 'published'
ORDER BY id DESC
LIMIT 20;

减少返回字段

分页列表不一定需要返回所有字段。尤其是正文、大文本、JSON 字段、图片详情等内容，不应该在列表页一次性查出来。

SELECT id, title, created_at FROM articles
ORDER BY id DESC
LIMIT 20;

减少字段可以降低 IO 和网络传输成本，也更容易利用覆盖索引。

延迟关联

对于深分页，可以先用索引查出目标页的 ID，再回表查询完整数据。这种方式有时比直接查询所有字段更快。

SELECT a.* FROM articles a
JOIN (
  SELECT id FROM articles
  ORDER BY id DESC
  LIMIT 20000, 20
) t ON a.id = t.id
ORDER BY a.id DESC;

这种方案适合仍然需要页码跳转，但深分页偶尔较慢的场景。它不能彻底消除 offset 成本，但能减少回表数据量。

游标分页

游标分页也叫 keyset pagination。它不使用大 offset，而是根据上一页最后一条记录继续向后查。

SELECT id, title FROM articles
WHERE id < 10000
ORDER BY id DESC
LIMIT 20;

这里的 10000 是上一页最后一条记录的 ID。数据库可以利用索引快速定位范围，性能通常比深 offset 更稳定。

时间游标

如果按创建时间排序，可以使用时间加 ID 的组合游标，避免时间重复导致顺序不稳定。

WHERE (created_at < :created_at)
   OR (created_at = :created_at AND id < :id)
ORDER BY created_at DESC, id DESC
LIMIT 20

这种写法适合信息流、日志列表、消息列表等按时间倒序加载的场景。

游标分页优缺点

游标分页性能稳定，适合“下一页”“加载更多”“无限滚动”。缺点是不适合直接跳到第 100 页，也不容易显示完整页码列表。

因此后台管理系统如果必须支持任意跳页，可以继续使用页码分页并做索引优化；前台信息流更适合游标分页。

总数统计问题

传统分页通常需要查询总条数。数据量大、条件复杂时，COUNT(*) 也可能很慢。

SELECT COUNT(*) FROM articles WHERE status = 'published';

如果业务不强依赖准确总页数，可以考虑只判断是否有下一页，或者使用缓存、异步统计、近似数量等方式。

只判断下一页

加载更多场景中，可以多查一条记录来判断是否还有下一页。

SELECT id, title FROM articles
WHERE id < :last_id
ORDER BY id DESC
LIMIT 21;

如果返回 21 条，就说明还有下一页，前端展示前 20 条即可。这可以避免每次都做总数统计。

EXPLAIN 分析

分页优化一定要结合 EXPLAIN。重点看是否使用索引、扫描行数、是否出现 filesort。

EXPLAIN SELECT id, title FROM articles
WHERE status = 'published'
ORDER BY id DESC
LIMIT 20;

不要只凭感觉优化。执行计划能告诉你数据库实际怎么查。

常见错误

第一种错误是大 offset 分页不做任何优化。第二种错误是列表页查询所有字段。第三种错误是分页没有稳定排序。第四种错误是每次都精确 COUNT，却没有业务必要。第五种错误是该用游标分页的场景仍然强行页码分页。

实践建议

如果是后台列表，页码分页更符合用户习惯，可以用索引、减少字段、延迟关联优化。若是前台信息流、日志流、消息列表，优先考虑游标分页。数据量较小时，普通 LIMIT 足够；数据量大后，再根据慢查询和 EXPLAIN 定向优化。

分页优化没有万能方案。核心是看业务是否需要跳页、是否需要总数、数据量多大、排序字段是什么。选对分页模式，比单纯调 SQL 更重要。