MySQL深分页性能崩坏的根本原因是必须扫描并丢弃前offset行，导致大量回表、I/O激增和排序开销；优化方案包括延迟关联（子查询先取ID再JOIN）和游标分页（WHERE id > ? ORDER BY id LIMIT ?）。
直接结论：用 LIMIT offset, size 查几百万行后的数据，性能必然崩。根本原因不是没加索引，而是 MySQL 必须扫描并丢弃前 offset 行——哪怕你只想要 10 条。
为什么 LIMIT 1000000, 20 比 LIMIT 0, 20 慢几十倍
MySQL 不会“跳”到第 1000001 行，它得从头开始数：先按 ORDER BY 排序（走索引也得遍历叶子节点），再逐行读、逐行计数，直到凑够 1000020 行，最后扔掉前 1000000 行。这个过程带来三重开销：
- 大量二级索引回表（如果
SELECT *或非索引列） - B+ 树叶子节点跨页遍历，I/O 次数激增
- 排序缓冲区（
sort_buffer_size）可能溢出，触发磁盘临时文件
实测中，570 万行的表，LIMIT 800000, 20 耗时 2.1 秒；优化后降到 0.3 秒——差距不在 SQL 写法“炫技”，而在是否绕开了偏移量扫描。
覆盖索引 + 子查询：最易落地的优化组合
核心思路是把“查全行 → 舍弃大部分”改成“先精准定位 ID → 再按 ID 拿数据”。前提是：排序字段和过滤条件能走索引，且主键是高效查找锚点（如自增 id）。
假设表结构为：
CREATE TABLE account ( id INT PRIMARY KEY, name VARCHAR(255), balance INT, update_time DATETIME, INDEX idx_update_time (update_time) );
原慢 SQL：
SELECT * FROM account WHERE update_time > '2020-09-19' ORDER BY update_time LIMIT 100000, 10;
优化写法（两步拆解）：
- 子查询只查
id，利用idx_update_time索引快速定位偏移位置（覆盖索引，不回表） - 外层用
JOIN关联主键，只回表 10 次，而非 100010 次
优化后 SQL：
SELECT t.* FROM account t INNER JOIN ( SELECT id FROM account WHERE update_time > '2020-09-19' ORDER BY update_time LIMIT 100000, 10 ) t1 ON t.id = t1.id;
注意：IN 子句不能用 LIMIT，所以必须用 JOIN；且子查询里 ORDER BY 不可省略，否则结果顺序不可控。
游标分页：适合“下一页”场景的真正替代方案
如果你的业务是滚动加载（如 APP 列表下拉）、不需要跳转任意页码，WHERE id > ? ORDER BY id LIMIT ? 是比所有 LIMIT offset 方案都快的解法。
关键点：
- 首次请求：取前 N 条，记下最大
id（比如id = 6800000） - 下一页请求：
SELECT * FROM account WHERE id > 6800000 ORDER BY id LIMIT 10 - 必须确保排序字段单调、无重复（自增
id最稳妥；时间戳需加id作为第二排序条件防歧义）
这种写法让 MySQL 直接定位 B+ 树叶子节点起始位置，跳过全部前置扫描——偏移量再大，执行计划也始终是 range 类型，不会退化成 index 或 ALL。
容易被忽略的陷阱
覆盖索引优化看似简单，但实际踩坑最多的是这几点：
-
ORDER BY字段没索引，或索引不匹配（比如ORDER BY update_time DESC，但索引是KEY idx_update_time (update_time)升序，MySQL 5.7 可能不走索引） - 子查询里漏了
WHERE条件，导致扫描全表 ID（必须内外条件一致） - 用
UUID或随机字符串做主键，WHERE id > ?失去顺序性，游标分页失效 - 业务要求“跳转第 100 页”，而游标分页无法支持——此时只能接受子查询方案，或改用 ES 分页
真正卡住性能的，往往不是技术选型，而是没意识到：偏移量本身就是一个反模式。只要还在拼 offset，就还没跳出旧分页的思维惯性。
