LIMIT offset, size 在大数据量下变慢的根本原因是MySQL需扫描并跳过前offset行，即使只返回size行；应优先使用游标分页、延迟关联或分区/分表优化。
为什么 LIMIT offset, size 在大数据量下会变慢
MySQL 的分页查询性能骤降，根本原因不是 LIMIT 本身，而是当 offset 很大时（比如 LIMIT 1000000, 20），MySQL 仍需扫描并跳过前 100 万行——即使只返回 20 行。如果没走索引或走了索引但回表开销大，就会触发大量随机 I/O 和临时排序。
常见错误现象：EXPLAIN 显示 rows 值极大、Extra 出现 Using filesort 或 Using temporary；慢查询日志中该语句执行时间远超预期。
- 必须确保分页字段（如
id或create_time）上有有效索引，且是查询条件的最左前缀 - 避免在分页 SQL 中混用
ORDER BY字段和非索引字段（例如ORDER BY status, create_time但只有create_time有索引） - 复合索引顺序要匹配查询模式：若常按
status = ? ORDER BY create_time DESC分页，索引应为(status, create_time)
用游标分页（WHERE id > ? ORDER BY id LIMIT size）替代偏移分页
游标分页不依赖 OFFSET，而是基于上一页最后一条记录的主键值（或唯一有序字段）做条件过滤，每次只扫描目标范围内的数据，复杂度稳定在 O(log n + size)。
适用场景：用户“下一页”操作、消息流、订单列表等允许顺序浏览、不支持跳转到任意页的业务。
- 要求分页字段严格单调、无重复、非空（推荐用自增
id或带毫秒精度的create_time） - 首次请求用
WHERE id > 0 ORDER BY id LIMIT 20，后续请求传入上一页最大id，如WHERE id > 123456 ORDER BY id LIMIT 20 - 不能直接跳转第 100 页；若需跳页，可先用覆盖索引快速定位锚点（例如查第 99 页末尾
id，再以此游标查第 100 页） - 注意：
ORDER BY必须与游标字段一致，且方向固定（ASC或DESC），否则结果错乱
延迟关联（JOIN 子查询优化）减少回表开销
当需要分页返回宽表字段（如连了 3 张表），但 ORDER BY 和 WHERE 只涉及主表字段时，可先用子查询只查主键，再用主键 JOIN 补全字段——避免大偏移量下对所有字段反复回表。
SELECT a.*, b.name, c.status FROM article a INNER JOIN (SELECT id FROM article WHERE status = 1 ORDER BY id DESC LIMIT 100000, 20) AS tmp ON a.id = tmp.id LEFT JOIN author b ON a.author_id = b.id LEFT JOIN category c ON a.cat_id = c.id;
这个写法让 MySQL 先在索引中完成排序和截断（只操作 id），再通过主键精确匹配补全数据，大幅降低 I/O 和内存压力。
- 子查询必须只包含用于排序/过滤的字段（最好是主键），且不能有
SELECT * - 外层
JOIN的关联字段必须有索引（如author.id、category.id） - 不适用于需要按关联表字段排序的场景（例如
ORDER BY b.name）
物理分表 or 时间分区缓解单表压力
当单表超千万甚至亿级，且分页集中在近期数据时，靠 SQL 优化已触及瓶颈。此时应考虑数据层面拆分，让“大分页”变成“小分页”。
两种主流方式：
-
按时间分区：用
PARTITION BY RANGE (TO_DAYS(create_time))，把历史数据隔离到不同分区。配合WHERE create_time > '2024-01-01'查询，MySQL 自动裁剪分区，LIMIT实际只在活跃分区中执行 -
按业务逻辑分表：如订单按
user_id % 16拆成 16 张表，分页请求带分表键（如用户 ID），路由到对应子表查询，每张表数据量可控
注意：分表后跨表聚合分页（如“全站最新订单”）无法避免归并排序，需引入 Elasticsearch 或预计算汇总表来支撑。
真正难的不是选哪种方案，而是识别出哪类分页请求其实根本不需要“全量准确”——比如后台导出、监控看板，用近似采样或异步生成快照反而更稳。
