在大表上进行分页查询，最核心的策略是避免在 OFFSET
上使用过大的值。传统的
LIMIT offset, limit模式在
offset巨大时会强制数据库扫描并丢弃大量数据，导致性能急剧下降。高效的查询通常会利用索引，通过定位“下一页”的起始点来避免这种全表扫描。
解决方案
当我们需要从一个拥有数百万甚至上亿行记录的MySQL大表中高效地获取分页数据时，绕开
LIMIT offset, limit的固有缺陷是关键。这里有几种行之有效的方法，各有侧重和适用场景。
1. 基于主键（或唯一索引）的“游标”式分页
这是最常见也最推荐的方法之一，尤其适用于向前翻页的场景。它的原理很简单：记住上一页最后一条记录的主键ID（或其他唯一索引列的值），然后查询大于这个ID的下一批记录。
-- 假设我们已经获取了上一页最后一条记录的ID，例如是 12345 SELECT * FROM your_large_table WHERE id > 12345 ORDER BY id ASC LIMIT 20; -- 每页显示20条
优点：
极度高效，因为它直接利用了id列上的索引（通常是主键），查询优化器能快速定位到
id > 12345的起始位置，然后只扫描20条记录。 查询时间基本与
offset大小无关，只取决于
limit大小和索引查找速度。
缺点：
只能向前翻页。如果需要“上一页”或跳转到任意页，这种方法就不太直接了。 要求ORDER BY的列是唯一且连续递增的（或至少能保证顺序）。
2. 复合索引的“游标”式分页（适用于复杂排序）
当你的排序条件不是简单的主键ID，而是其他列，甚至是多列组合时，我们可以构建一个更复杂的
WHERE子句来模拟游标。
假设你需要按
creation_time降序，然后按
id降序排序：
-- 假设上一页最后一条记录是 (creation_time = '2023-10-26 10:00:00', id = 54321) SELECT * FROM your_large_table WHERE (creation_time < '2023-10-26 10:00:00') OR (creation_time = '2023-10-26 10:00:00' AND id < 54321) ORDER BY creation_time DESC, id DESC LIMIT 20;
优点：
非常灵活，可以应对各种复杂的排序需求。 同样高效，只要(creation_time, id)上有合适的复合索引，查询就能快速定位。
缺点：
WHERE子句相对复杂，需要客户端正确传递上一页最后一条记录的所有排序字段的值。 对索引设计要求高，复合索引的列顺序必须与
ORDER BY和
WHERE子句的逻辑相匹配。
3. 子查询优化（适用于特定场景的 OFFSET
优化）
虽然我们尽量避免大的
OFFSET，但在某些情况下，比如需要跳到“中间”某页，或者
ORDER BY的列没有唯一性，我们可能仍需结合
OFFSET。这时，可以考虑用子查询来优化：
SELECT t1.*
FROM your_large_table AS t1
JOIN (
SELECT id
FROM your_large_table
WHERE some_condition -- 如果有筛选条件
ORDER BY some_column -- 你的排序字段
LIMIT 100000, 20 -- 假设要获取第5001页的20条记录
) AS t2 ON t1.id = t2.id;优点：
外层查询只通过主键id去检索完整的行数据，这通常非常快。 内层子查询虽然仍有
OFFSET，但它只查询了
id列，如果
some_column有索引，并且
id是主键，那么内层查询的数据量会小很多，网络传输和内存开销也相对较小。
缺点：
仍然存在OFFSET带来的性能瓶颈，只是将这个瓶颈限制在只获取
id的子查询中。 如果
some_column没有索引，或者
some_condition筛选性很差，子查询的性能依然会很糟糕。
选择哪种方案，很大程度上取决于你的业务需求和数据特性。大多数情况下，如果能用游标式分页，那绝对是首选。
为什么传统的 OFFSET
+ LIMIT
在大表上会变得如此缓慢？
这确实是一个让我深感头疼的问题，尤其是在面对那些“朴素”地使用
SELECT * FROM table LIMIT 1000000, 10的代码时。你可能会觉得，我明明只想要10条数据，数据库为什么会耗费那么长时间？
原因其实很简单，但也很残酷：MySQL（以及很多其他关系型数据库）在处理 LIMIT offset, limit
时，即使你只想要最后 limit
条，它也必须先扫描、处理，并最终丢弃掉 offset
数量的记录。
想象一下，你有一本厚达一百万页的书，而你想要看第999999页的文字。你不能直接翻到那一页，你得从头开始，一页一页地翻，直到翻到第999999页，然后才能开始阅读那里的内容。数据库的工作方式与此类似：
-
全量扫描（或索引扫描）到
offset处： 即使你的
ORDER BY列有索引，数据库也需要沿着索引树找到第一条记录，然后“遍历”
offset次，才能找到你真正想要的起始点。在这个遍历过程中，它可能会读取大量的数据块，这些数据块可能散布在磁盘的各个位置，导致大量的随机I/O。 数据丢弃： 每扫描一条记录，它都需要判断这条记录是否在
offset范围之内。如果在，就丢弃；直到达到
offset的数量。这个丢弃过程本身也消耗CPU资源。 内存与CPU开销： 随着
offset增大，数据库需要处理的数据量也随之增加，这会占用更多的内存（缓冲区）和CPU时间。当数据量大到无法完全载入内存时，性能瓶颈就会从CPU转移到磁盘I/O。
所以，当
offset达到几十万、上百万时，这种“先跑后丢”的策略就成了性能的杀手。它不仅仅是慢一点，而是随着
offset的增长，性能会呈现指数级的下降趋势，直到让你无法忍受。这就像是每次都要从头开始重新计算斐波那契数列一样，效率极低。
如何选择最适合我的大表分页策略？ID、游标还是其他？
选择一个合适的分页策略，真的不是一道“非黑即白”的选择题，它更像是在权衡业务需求、数据特性和技术实现成本。我个人觉得，你需要先问自己几个问题：
-
你的分页需求是怎样的？ 仅仅是“下一页”？还是需要支持“上一页”、“跳页”（比如跳到第50页）？
你的排序条件是什么？ 总是按主键ID排序？还是按时间、状态、名称等其他字段排序？
你的数据特性如何？ ID是否连续？是否存在大量相同排序字段值的记录？
基于这些问题，我们可以这样思考：
ID-based Pagination (主键游标式)：
适用场景： 如果你的分页主要是向前翻页，并且排序条件是主键ID（或任何唯一且连续递增的索引列），那么这种方法几乎是完美的选择。例如，一个新闻列表，总是显示最新的20条，然后用户点击“加载更多”来获取更早的20条。 优点： 实现简单，性能卓越，几乎不受数据量影响。 缺点： 无法直接支持“上一页”或“跳页”。如果你需要“上一页”，你得记住前一页的起始ID，然后反向查询。 我的看法： 这是我的首选，只要业务允许，我都会尽量引导产品设计向这种模式靠拢。它能带来最直接的性能提升。Composite Index Cursor-based Pagination (复合索引游标式)：
适用场景： 当你的排序条件是非唯一列，或者多列组合时，比如按create_time DESC, id DESC。它同样适用于“下一页”的场景。 优点： 灵活性高，能满足复杂的排序需求，并且性能同样出色，只要索引设计得当。 缺点： 实现起来相对复杂，需要客户端维护上一页最后一条记录的多个字段值。对索引的依赖性更强，索引设计错误会导致性能急剧下降。 我的看法： 这是 ID-based 的自然延伸，当排序需求复杂时，它是最佳的替代方案。但需要团队对索引和SQL有更深的理解。
Subquery Optimization (子查询优化式)：
适用场景： 当你真的无法避免OFFSET，比如产品经理坚持要支持“跳页”功能，或者排序字段没有唯一性，且
OFFSET依然不小。 优点： 在一定程度上缓解了传统
OFFSET的性能问题，尤其是在
ORDER BY列有索引且内层查询只获取ID时。 缺点： 性能提升有限，
OFFSET的本质问题依然存在。如果
OFFSET真的非常大，它依然会很慢。 我的看法： 这更像是一种“退而求其次”的方案。如果业务上实在无法放弃
OFFSET，我会考虑这种方式，但同时也会和产品团队沟通，看看能否通过UI/UX设计来规避大
OFFSET的场景。
总结一下我的选择逻辑：
-
优先考虑 ID-based 或 Composite Index Cursor-based。 如果业务允许，这是性能的保证。
如果必须支持跳页，并且
OFFSET不会特别大（比如几千到一两万），可以尝试子查询优化。 同时，我会确保
ORDER BY的列有索引。 如果
OFFSET巨大且必须跳页，那么可能需要重新审视产品需求，或者考虑引入全文搜索（Elasticsearch等）或更高级的缓存策略。 数据库本身在大
OFFSET上的性能瓶颈是难以彻底绕过的。
实现高效分页时，索引设计有哪些关键考量？
索引设计对于高效分页来说，简直是灵魂般的存在。没有合适的索引，再巧妙的SQL技巧也可能只是空中楼阁。我在这方面吃过不少亏，所以每次遇到分页性能问题，我都会第一时间去检查索引。
以下是我在实践中总结的一些关键考量：
排序字段必须在索引中，且顺序要匹配：
这是最基本的要求。如果你ORDER BY create_time DESC, id DESC，那么你至少应该有一个
(create_time, id)的复合索引。 索引的顺序至关重要。
(create_time, id)和
(id, create_time)是完全不同的。如果你的
ORDER BY是
create_time, id，那么索引就应该是
(create_time, id)。 方向也要匹配。 虽然MySQL在某些情况下可以反向扫描索引，但如果你的
ORDER BY是
DESC，而索引是
ASC，可能会有额外的开销。理想情况下，索引的排序方向应该与
ORDER BY匹配。
WHERE 子句中的筛选字段也应包含在索引中：
如果你在分页查询前有WHERE status = 'active'这样的筛选条件，那么这个
status字段也应该考虑放在索引的最前面。 例如，
WHERE status = 'active' ORDER BY create_time DESC, id DESC，那么一个
(status, create_time, id)的复合索引会是理想选择。这样，MySQL可以先通过
status快速过滤掉不相关的记录，然后在剩下的记录中进行排序和分页。
考虑“覆盖索引”（Covering Index）：
如果你的SELECT列表中只包含索引中的列，那么这个索引就称为覆盖索引。 例如，
SELECT id, create_time FROM your_table WHERE create_time > '...' ORDER BY create_time LIMIT 20;如果
(create_time, id)是一个索引，那么MySQL可以直接从索引中获取所有需要的数据，而无需回表（即不再去查找实际的数据行）。 好处： 极大地减少了磁盘I/O，因为不需要读取数据行，只读取索引页，速度飞快。 我的经验： 在设计报表或某些只需要少量字段的列表时，我会特意去设计覆盖索引，性能提升非常显著。
主键的特殊性（InnoDB）：
在InnoDB存储引擎中，主键就是聚簇索引。这意味着数据行本身就是按照主键的顺序存储的。 因此，基于主键的查询（如WHERE id > last_id）效率极高，因为数据物理上就是有序的，数据库可以直接跳到目标位置开始读取。 这也是为什么 ID-based 分页如此高效的原因之一。
利用 EXPLAIN
分析查询计划：
EXPLAIN来查看MySQL的查询计划。
EXPLAIN SELECT * FROM your_large_table WHERE id > 12345 ORDER BY id ASC LIMIT 20;关注
type字段（
range是好的，
ALL是糟糕的）、
key字段（是否使用了正确的索引）、
rows字段（预估扫描行数，越小越好）。 我的习惯： 每次调整SQL或索引后，我都会跑一遍
EXPLAIN，确保我的改动确实让查询走上了正确的索引路径。
索引的维护成本：
索引不是越多越好。每个索引都会占用磁盘空间，并且在数据插入、更新、删除时，索引也需要同步更新，这会增加写操作的开销。 所以，索引设计需要权衡读写性能。对于读多写少的表，可以适当多加索引；对于写操作频繁的表，则需要更加谨慎。总之，高效分页的核心在于让数据库能够快速定位到你想要的数据块，而不是从头开始扫描。而索引，就是实现这种“快速定位”的魔法。
