LIMIT OFFSET在千万级数据中必然性能断崖式下降，因MySQL需扫描offset+size行后丢弃前offset行；应改用延迟关联或游标分页。
LIMIT OFFSET 在千万级数据上基本不可用，不是“怎么优化”，而是“必须换方案”。 它的执行逻辑决定了 offset 越大，性能越断崖式下跌——MySQL 必须扫描 offset + size 行，再丢弃前 offset 行。1000 万数据查第 100 页（LIMIT 990, 10）可能毫秒级；查第 10 万页（LIMIT 999990, 10）就可能卡住几秒甚至几十秒，且伴随大量随机 IO 和内存压力。
为什么 LIMIT offset, size 在深度分页中必然变慢
这不是 MySQL 的 bug，是它执行模型的固有行为：
- MySQL 不会跳过前 offset 行，而是按索引顺序逐行读取，直到凑够 offset + size 行
- 如果
SELECT *且排序字段无覆盖索引，每读一行都可能触发一次回表（主键查找），产生大量随机磁盘 IO - offset 超过 10 万后，扫描行数远超返回行数，IO 和 CPU 成本被严重浪费
- ORDER BY 字段若无索引，还会触发
filesort，在千万级数据下极易写临时文件到磁盘
延迟关联（Deferred Join）：最小改动、最大收益的替代写法
核心是把“查全量数据”拆成两步：先用覆盖索引快速捞出目标页的主键 ID，再用这些 ID 精准回查。避免了绝大部分无效扫描和回表。
原始低效语句：
SELECT * FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 10;
优化后语句：
SELECT t1.* FROM t_order t1 INNER JOIN ( SELECT id FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 10 ) t2 ON t1.id = t2.id;
关键前提：
- 确保
status和create_time上有联合索引，例如idx_status_ctime - 子查询只查
id，能完全走索引，不回表 - 外层 JOIN 只对 10 个 ID 回表，IO 次数从 ~100010 次降到 10 次
游标分页（Seek Pagination）：适合无限滚动、彻底绕开 offset
如果你的前端是“下拉加载更多”，而非“跳转任意页码”，这是更优解。它用上一页最后一条记录的排序字段值作为边界条件，每次查询都是范围扫描，性能恒定。
第一页：
SELECT * FROM t_order WHERE status = 'PAID' ORDER BY create_time DESC, id DESC LIMIT 10;
第二页（假设上一页最后一条是 create_time = '2023-10-01 12:00:00', id = 12345）：
SELECT * FROM t_order
WHERE status = 'PAID'
AND (create_time < '2023-10-01 12:00:00'
OR (create_time = '2023-10-01 12:00:00' AND id < 12345))
ORDER BY create_time DESC, id DESC
LIMIT 10;注意点：
- ORDER BY 必须包含唯一性组合（如时间 + 主键），否则可能漏数据或重复
- WHERE 条件中的比较逻辑必须严格匹配排序方向（DESC 对应 ）
- 无法支持用户直接输入“跳转到第 5000 页”，需配合前端做状态管理
真正难的不是写出某条快 SQL，而是判断当前业务场景到底该用延迟关联还是游标分页——前者保跳页能力，后者保性能稳定。很多团队卡在“既要又要”，结果两条路都没走通。
