mysql深分页慢的根本原因是其必须扫描并丢弃前offset行，导致io和cpu开销随偏移量线性增长；优化核心是绕开limit offset, size的物理扫描逻辑，采用延迟关联（先查id再join）或游标分页（基于有序字段范围查询）。
深分页慢不是SQL写得不对，而是MySQL必须跳过大量行再回表——优化核心是绕开LIMIT offset, size的物理扫描逻辑。
为什么LIMIT 1000000, 10比LIMIT 0, 10慢几十倍
MySQL执行LIMIT offset, size时，并不会“直接定位到第1000001行”，而是：先按WHERE + ORDER BY条件取出全部匹配行（可能上百万），再逐行计数跳过前offset条，最后取size条。即使有索引，只要SELECT *或非覆盖字段，就必然触发大量回表（从二级索引查出主键，再回聚簇索引捞整行）。
常见错误现象：EXPLAIN显示rows高达百万级，Extra里出现Using filesort或Using temporary；实际执行时间随offset线性增长。
- 偏移量越大，MySQL扫描并丢弃的行越多，IO和CPU开销越重
- 如果ORDER BY字段没走索引，还会额外排序；即使走了索引，若不是覆盖查询，仍要回表
-
SELECT *几乎一定导致回表，除非主键就是唯一查询条件且无其他字段
用JOIN子查询替代LIMIT直接跳过回表冗余
核心思路：先用轻量查询（只查主键）拿到目标页的ID列表，再用这些ID精准拉取完整行。这样避免了跳过过程中的无效回表。
正确写法：
SELECT t.* FROM t_coupon_task_fail t INNER JOIN ( SELECT id FROM t_coupon_task_fail WHERE batch_id = '1830889785603571980' ORDER BY id LIMIT 4999990, 10 ) t1 ON t.id = t1.id;
关键点：
- 子查询必须
ORDER BY id且id上有索引（最好是主键），否则无法保证顺序和性能 - 子查询里只选
id，确保走覆盖索引，不回表 - 外层
JOIN利用主键等值匹配，走const或eq_ref，极快 - 不能用
IN (SELECT ...)——MySQL 8.0之前不支持子查询带LIMIT，会报错
用游标分页（where + 排序字段）彻底规避offset
适用于“下一页”类场景（如无限滚动），不再依赖页码，而是记住上一页最后一条的排序值。
例如上一页最后一条id = 5000000，下一页SQL为：
SELECT * FROM t_coupon_task_fail WHERE batch_id = '1830889785603571980' AND id > 5000000 ORDER BY id LIMIT 10;
优势与限制：
- 执行计划稳定走
range，rows仅约10–20，耗时恒定在毫秒级 - 要求
ORDER BY字段（这里是id）有索引，且该字段值严格单调、无重复 - 无法跳转任意页码（比如直接翻到第500页），只适合顺序加载
- 如果业务允许，这是最推荐的方案——简单、高效、可扩展
索引设计必须匹配查询模式
没有合适的索引，以上优化都白搭。重点不是“有没有索引”，而是“索引是否覆盖查询路径”。
针对WHERE batch_id = ? ORDER BY id这类查询，应建复合索引：
CREATE INDEX idx_batch_id_id ON t_coupon_task_fail(batch_id, id);
为什么这个顺序不能颠倒？
-
batch_id在前：用于快速过滤出目标批次的数据（高区分度字段优先） -
id在后：让ORDER BY能直接利用索引有序性，避免Using filesort - 该索引天然支持覆盖查询（SELECT id …），子查询场景下完全不用回表
- 如果经常查
json_object等大字段，它们不会被包含进二级索引，所以外层JOIN仍是必要环节
容易被忽略的是：当batch_id区分度极低（比如只有几个固定值），这个索引效果会打折扣——此时需结合业务评估是否分表或引入分区。
