mysql limit偏移量大怎么优化_mysql分页性能提升技巧

LIMIT 100000,20 慢因需扫描跳过前10万行；应改用游标分页（如WHERE id

为什么 LIMIT 100000, 20 会慢得离谱

因为 mysql 在执行这类语句时，必须先扫描并跳过前 100000 行（即使不返回），再取后续 20 行。如果没走覆盖索引，还会回表查完整记录；若 ORDER BY 字段无索引，甚至要额外排序临时结果集。本质是“全扫+丢弃”，不是“直接定位”。

用游标分页替代 LIMIT offset, size

核心思路：不依赖行号偏移，改用上一页最后一条记录的排序字段值作为查询起点。要求排序字段（如 id 或 created_at）有唯一性或配合其他字段构成唯一约束。

示例（按自增 id 降序分页）：

select * FROM orders WHERE id < 123456 ORDER BY id DESC LIMIT 20;

下一页就用刚查出的最小 id 值继续缩小范围。这种方式避免了跳过大量数据，索引可高效定位起点。

• 必须有合适的索引支持，例如 INDEX (id) 或 INDEX (status, created_at, id)
• 不能跳页（比如直接跳到第 100 页），只适合“下一页/上一页”场景
• 若排序字段非唯一（如多个记录 created_at 相同），需补上主键去重：WHERE (created_at, id)

延迟关联（deferred join）减少回表开销

当分页字段和查询字段不在同一索引中时，LIMIT 后仍需回表取数据，大偏移量下回表次数爆炸。延迟关联先把主键捞出来，再用主键二次查详情，让回表次数固定为 LIMIT size 次，而非 offset + size 次。

示例：

SELECT t1.* FROM orders t1 INNER JOIN (     SELECT id FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 100000, 20 ) t2 ON t1.id = t2.id;

内层子查询只走索引（假设 INDEX(status, created_at, id)），外层用主键精准回查。

• 需要复合索引覆盖 WHERE 条件、ORDER BY 和主键（否则子查询仍可能慢）
• 对 SELECT * 场景效果明显；若只需几个字段，优先建覆盖索引更简单
• MySQL 5.6+ 对这种写法优化较好，老版本注意检查执行计划是否走了 using index

物理分表 or 时间分区缓解单表压力

当单表超千万级且分页请求集中在最新数据时，老数据拖慢整体查询。这时靠 SQL 优化已触及瓶颈，得从结构入手。

• 按月/季度分表（如 orders_202401），查询时明确指定表名，避开历史数据扫描
• 用 PARTITION BY RANGE (TO_DAYS(created_at)) 分区，配合 WHERE created_at >= '2024-01-01' 可自动裁剪分区
• 注意：分表后 count(*) 和跨时间范围的分页会变复杂，需应用层聚合

真正的大偏移量问题，往往不是 SQL 写得不够巧，而是数据模型没跟上访问模式——游标分页解决“怎么查快”，而分表/分区决定“查哪些”。