OFFSET越大查询越慢，因MySQL需真实扫描并丢弃前N行，导致I/O、排序缓冲区压力线性上升；超10万后响应常指数增长，应改用游标分页或覆盖索引+延迟关联优化。
为什么 OFFSET 越大，LIMIT 查询越慢
因为 MySQL 在执行 SELECT * FROM t ORDER BY id LIMIT 1000000, 20 时，并不会跳过前 100 万行再取 20 行；它会真实扫描并丢弃前 100 万行——哪怕你只想要最后 20 条。磁盘 I/O、排序缓冲区压力、临时表生成都会随 OFFSET 增大线性恶化。
常见错误现象：Query took 8.234 sec（OFFSET=50w 时），Lock wait timeout exceeded（高并发下锁等待加剧）。
- 使用场景：后台管理分页、数据导出、ES 同步拉取等需遍历全量的逻辑
- 性能影响：OFFSET > 10w 后，响应时间通常呈指数上升，不是线性
- 兼容性无问题，但所有 MySQL 版本都逃不开这个执行模型缺陷
用主键/索引列做游标分页（cursor-based pagination）
替代 OFFSET 的核心思路：不“跳过 N 行”，而是“从上次查到的最后一条继续往后查”。前提是排序字段必须有唯一、非空、有索引的列（如自增 id 或带索引的 created_at）。
示例：第一页查完得到最后一条 id = 10042，第二页就写成：
SELECT * FROM orders WHERE id > 10042 ORDER BY id LIMIT 20
- 必须确保
ORDER BY字段和WHERE条件字段一致且有联合索引（如INDEX idx_id (id)） - 不能混用
ORDER BY created_at DESC+WHERE id > ?，方向错会导致漏数据 - 如果排序字段可能重复（如多个订单同秒创建），必须补上唯一列去重：
ORDER BY created_at DESC, id DESC - 前端需传递上一页末尾的游标值（如
cursor=10042），而不是页码
覆盖索引 + 延迟关联优化大偏移查询
当无法改造成游标分页（比如必须支持“跳转到第 500 页”这种随机页码），可先用覆盖索引快速定位主键，再回表查字段，避免全字段扫描。
假设表有 id（主键）、status、user_id，常用查询是 WHERE status = 'paid' ORDER BY id：
SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1000000, 20 ) AS tmp ON t.id = tmp.id;
- 子查询只走
INDEX(status, id)，不读其他列，速度快得多 - 外层关联才是真正的回表，只查 20 行，成本可控
- 关键点：子查询里不能有
SELECT *，也不能有GROUP BY/HAVING等触发临时表的操作 - MySQL 5.7+ 效果明显；8.0 中优化器有时会自动改写，但别依赖
count(*) 百万级总数怎么不拖垮查询
直接 SELECT COUNT(*) FROM orders WHERE status = 'paid' 在无合适索引时会扫全表，比 LIMIT 分页还慢。多数业务其实不需要精确总数。
- 用近似值：查
SHOW TABLE STATUS LIKE 'orders'得到Rows字段（InnoDB 估算，误差 ±10%） - 加缓存：首次查总数后存 Redis，超时或写入时异步更新（如监听 binlog）
- 聚合表：单独建
orders_summary表，按状态/日期维度预统计，用定时任务或触发器维护 - 绝对不要在分页接口里每次连带查
COUNT(*)，前端显示“共约 120 万条”比卡死强
真正难的不是写对 SQL，而是让业务接受“页码不可靠、总数不精确、跳页要受限”——这些限制背后全是磁盘和索引结构决定的物理事实。
