分页是业务系统里最常见的需求之一，列表页、订单查询、后台管理系统都离不开它。表面上写一条limit语句就能搞定，可一旦表里的数据涨到几十万、上百万行，原本毫秒级的查询可能突然变成好几秒。问题的根源不在limit本身，而在于我们使用limit的方式。这篇文章就来把MySQL分页的底层逻辑讲清楚，并给出几种经过实践检验的高效写法。
一、limit偏移量分页为什么越翻越慢
最常见的分页写法是SELECT * FROM orders ORDER BY id LIMIT 100000, 20。这条语句的语义是跳过前100000行，返回之后的20行。MySQL并没有办法直接定位到第100001行，它的做法是：沿着排序结果依次扫描，把前面100000行都取出来丢弃，只保留最后20行。
也就是说，偏移量越大，需要扫描和丢弃的行数就越多，耗时自然线性增长。当用户翻到第一万页时，数据库实际上做了大量无用功。此外，如果排序字段上没有索引，还会触发文件排序（filesort），性能进一步恶化。可以通过EXPLAIN观察执行计划，出现Using filesort就是一个明显信号。
另一个容易被忽视的点是SELECT *。即便二级索引上已经排好了序，回表取全部字段的开销依然存在。扫描十万行就要回表十万次，这是深度分页慢的第二大元凶。
二、覆盖索引与延迟关联优化
第一种优化思路是减少回表次数，典型写法叫延迟关联：先用子查询在索引上定位到目标行的主键，再用主键回表取完整数据。
-- 原始写法，深度分页时很慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 延迟关联：先在索引上拿到主键，再回表
SELECT t.* FROM orders t
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON t.id = tmp.id;
这条改写为什么有效？关键在于子查询里的SELECT id。如果id本身就是主键，或者存在一个只包含排序字段的二级索引，那么子查询可以完全在索引上完成，不回表。扫完100020个索引条目后只对最终选出的20行做回表，磁盘IO大幅下降。
实测数据可以说明差距：在500万行的表上，直接limit 4000000,20大约需要3秒，而延迟关联写法通常能降到0.5秒以内。提升幅度取决于行宽，行越宽（字段越多、大字段越多），延迟关联的收益越明显。
三、书签方式：记住上次的位置
延迟关联虽然有效，但本质上还是要扫过所有被跳过的索引条目。如果业务允许，有一种更彻底的方案：不传页码，改传上一页最后一条记录的排序值，这就是所谓的书签分页或游标分页。
-- 第一页 SELECT * FROM orders ORDER BY id DESC LIMIT 20; -- 下一页：记住上一页最后的id SELECT * FROM orders WHERE id < 100981 ORDER BY id DESC LIMIT 20;
这种写法利用索引直接定位到起点，扫描的行数永远只有20行，无论翻到第几页耗时都恒定，时间复杂度是O(1)级别的。像朋友圈、微博时间线这类无限下拉的场景，用的就是这种方案。
书签方式的局限是只能顺序翻页，不能直接跳到第N页，而且排序字段如果有重复值，需要额外的字段组合来保证游标唯一，比如WHERE create_time < ? OR (create_time = ? AND id < ?)。对于后台管理这类必须支持跳页的场景，可以折中处理：前几页用limit，深页用延迟关联，或者干脆限制最大可访问页数。
四、其他辅助手段与选型建议
除了SQL层面的改写，还有一些配套措施值得做。首先是索引设计，排序字段和过滤条件要尽量组合成联合索引，让WHERE条件和ORDER BY都能走索引，避免filesort。其次，如果总量不需要精确，可以避免每次都执行COUNT(*)统计总数，改用估算值或者缓存结果。
对于数据量特别大的冷数据分页，还可以考虑把历史数据归档到单独的表或者搜索引擎（如Elasticsearch）中，主表只保留活跃数据，分页压力自然就小了。
选型上可以简单总结：中小数据量直接limit没问题；需要跳页的深度分页用延迟关联；App端的瀑布流场景优先书签分页；千万级以上的复杂检索交给搜索引擎。没有万能写法，理解每种方案背后的IO模型，才能对症下药。
五、总结
MySQL深度分页慢的本质是偏移量扫描加回表的双重开销。覆盖索引和延迟关联解决的是回表问题，书签分页解决的是重复扫描问题，索引优化解决的是排序问题。写分页SQL时养成看执行计划的习惯，关注扫描行数这个指标，比单纯记写法更有价值。把这几招用到位，百万级数据的分页查询稳定在百毫秒以内并不是难事。