LIMIT offset, size 在大数据量下变慢是因为MySQL需扫描前offset+size行再丢弃前offset行，扫描成本随offset线性增长；即使有索引也无法跳过中间结果集。
为什么 LIMIT offset, size 在大数据量下会越来越慢
因为 MySQL 执行 LIMIT 1000000, 20 时，并不是跳过前 100 万行直接取 20 行，而是先扫描并暂存前 1000020 行，再丢弃前 100 万行——扫描成本随 offset 线性增长。索引能加速定位，但无法跳过中间结果集。
常见错误现象：EXPLAIN 显示 rows 值巨大；监控看到 Handler_read_next 暴涨；分页越往后，响应时间从毫秒级升到秒级甚至超时。
- 适用场景：用户手动翻页（如后台管理页）、导出分批拉取、非实时报表
- 不适用场景：无限滚动（应改用游标分页）、强实时排序结果（如热搜榜）
- 关键影响：即使有覆盖索引，
offset大了依然要遍历 B+ 树叶子节点链表
用主键/唯一有序字段替代 OFFSET（游标分页）
核心思路是“记住上一页最后一条的 ID”，下一页查询改为 WHERE id > ? ORDER BY id LIMIT 20，完全避开 OFFSET。
实操建议：
- 必须确保排序字段（如
id或created_at）有索引，且值严格单调（推荐用自增id） - 如果排序含多个字段（如
ORDER BY status, created_at），WHERE 条件需复合判断：WHERE (status, created_at) > (?, ?) - 前端需传回上一页末尾的完整排序值（不只是 ID），否则跨状态/时间点会漏数据
- 注意 NULL 值：若
created_at可空，WHERE created_at > ?会跳过所有 NULL 行，需提前约定或补默认值
示例：SELECT * FROM orders WHERE id > 1234567 ORDER BY id LIMIT 20 —— 比 LIMIT 1234567, 20 快数倍
当必须用 OFFSET 时，如何减轻伤害
有些场景绕不开（如第三方接口要求传 page=1000），这时只能“止损”，而非根治。
- 加硬限制：在应用层拦截
offset > 10000的请求，返回 400 或引导用户搜/筛 - 用延迟关联（Deferred Join）：先查主键，再连表取字段，减少单次扫描的数据量
SELECT t.* FROM (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) AS tmp JOIN orders AS t ON t.id = tmp.id - 避免
SELECT *：只查必要字段，尤其避开大文本（TEXT、BLOB），减少 I/O 和内存拷贝 - 检查
innodb_buffer_pool_size是否足够：若热数据无法常驻内存，磁盘随机读会雪上加霜
为什么不能只靠加索引或升级硬件解决
索引能加速单条记录查找，但对 OFFSET 无效——B+ 树索引不支持“跳过 N 个节点”。升级 CPU 或 SSD 只是缓解扫描开销，无法改变线性复杂度本质。
容易被忽略的点：
- 分页深度和数据增长速度正相关：今天
OFFSET 10w还可接受，半年后可能就卡死 - 业务逻辑里隐藏的分页：比如
COUNT(*)配合LIMIT做总页数计算，这个COUNT本身在无条件时就是全表扫 - ORM 自动生成的分页语句（如 Django 的
paginator、MyBatis 的RowBounds）默认走LIMIT offset, size，需主动替换为游标逻辑
真正要动的是查询模型，不是调优参数
