limit 100000,20性能差的根源是mysql必须扫描并丢弃前100000行，导致扫描行数=offset+limit且伴随大量随机i/o或filesort；优化需用覆盖索引子查询或游标分页。
直接说结论：LIMIT 100000, 20 这类查询不是“取20条慢”，而是MySQL必须老老实实扫描并丢弃前100020行——其中100000次回表或排序才是真瓶颈。优化不是调参，是换思路。
为什么LIMIT偏移量一大就卡死
MySQL没有“跳到第N行”的物理能力。执行LIMIT 100000, 20时，它只能从索引头开始逐行读、逐行判断行号、逐行丢弃，直到数到第100001行才开始收集。关键代价藏在这三处：
- 扫描行数 =
offset + limit，线性增长，50万页就是500020行扫描 - 如果走二级索引（比如
idx_create_time），每读一条索引项就要回主键索引查一次完整行——100000次随机I/O - 若
ORDER BY字段无索引，还会触发Using filesort，全量排序后再丢弃，内存/磁盘开销爆炸
JOIN子查询 + 覆盖索引（最兼容现有分页逻辑）
不改前端传参方式（仍支持page=5000），只改SQL写法。核心是让内层只扫索引、不回表，外层再精准捞数据。
假设你按create_time DESC分页，且表有复合索引KEY idx_ct_id (create_time, id)：
SELECT t.* FROM orders t JOIN ( SELECT id FROM orders ORDER BY create_time DESC, id DESC LIMIT 100000, 20 ) AS tmp ON t.id = tmp.id;
这样做的实际效果：
- 子查询
SELECT id ...全程只走idx_ct_id索引，B+树里直接拿到20个id，毫秒级 - 外层
JOIN用这20个主键精确回表，最多20次随机I/O，而非100000次 - 必须确保
ORDER BY字段和SELECT的主键都在同一覆盖索引里，否则子查询仍会回表
游标分页（WHERE id > ?）——真正治本但需前端配合
放弃页码概念，改用“上一页最后一条的id”作为下一页起点。这是唯一让扫描行数恒定在limit级别的方案。
典型写法：
-- 第一页（无游标） SELECT id, order_no, create_time FROM orders ORDER BY id DESC LIMIT 20; <p>-- 第二页（假设上一页最后id=999999） SELECT id, order_no, create_time FROM orders WHERE id </p><p>注意点：</p>
- 排序字段必须单调唯一（推荐用自增
id；若用时间字段，必须加id做第二排序，避免同秒多条导致漏/重） - 不支持跳转任意页码（比如从第1页直接跳第100页），适合无限滚动场景
- 新插入记录可能插在旧页面中间，造成“漂移”——业务若强依赖严格顺序，得加事务快照或版本号控制
容易被忽略的硬伤：索引设计与SELECT *
所有优化都建立在索引有效基础上。常见翻车点：
- 只建了
KEY idx_create_time (create_time)，但查询SELECT *，MySQL仍要回表——必须把id也加入索引，变成(create_time, id) - 用
WHERE status = 1 ORDER BY create_time，却没建(status, create_time, id)联合索引，导致索引失效+filesort - 前端盲目
SELECT *，传输大量不用字段，放大网络和内存压力——明确只查需要的列
真正卡住你的往往不是“怎么选方案”，而是EXPLAIN里看到rows远超offset + limit，或者Extra里赫然写着Using temporary; Using filesort——先盯死这两点，再谈优化。
