MySQL 深分页（如 LIMIT 100000, 10）本质是 高代价的偏移量扫描，可通过以下方案优化，附核心原理和实操示例：

---

一、深分页为什么慢？

SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 10;

执行过程：

1. 通过二级索引或全表扫描定位到第 1 行

2. 顺序扫描 100000 + 10 行

3. 丢弃前 100000 行，返回最后 10 行
   ⛔ 问题：扫描 100010 行但仅返回 10 行，I/O 和 CPU 浪费严重

---

二、优化方案详解

方案 1：延迟关联法（最优解）

原理：
先查主键，再用主键回表取数据，减少回表量

SELECT * FROM orders 
INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 10  -- 只取主键
) AS tmp USING(id);

性能对比：

原查询 (直接 LIMIT)	延迟关联法
1.2s	0.05s

✅ 适用场景：任何排序字段的深分页
📌 关键点：子查询必须使用覆盖索引

---

方案 2：游标分页（连续分页神器）

原理：
记录上一页最后一条记录的标识值，作为下一页起点

-- 第一页
SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 10;-- 下一页（假设上一页最后 id=10200）
SELECT * FROM orders 
WHERE id < 10200  -- 游标定位
ORDER BY id DESC 
LIMIT 10;

性能：
⏱️ 恒定为 10 行扫描，与页码无关

⚠️ 限制：

1. 只能连续页顺序访问（不可跳页）

2. 排序字段必须唯一（否则丢数据）

---

方案 3：范围分页（时间维度优化）

原理：
用时间范围缩小扫描区间

SELECT * FROM orders 
WHERE create_time < '2023-01-01'  -- 上页的时间边界
ORDER BY create_time DESC 
LIMIT 10;

性能：
🚀 扫描行数 = 目标数据行数 + 索引定位开销

✅ 适用场景：按时间排序的分页
📌 优化关键：在 create_time 上建索引

---

方案 4：业务层优化（终极方案）

1. 禁止跳页：
   只提供“上一页/下一页”按钮（本质是游标分页）

2. 数据归档：
   将历史数据迁移到归档表，减少主表数据量

   -- 迁移3个月前数据
   INSERT INTO orders_archive 
   SELECT * FROM orders WHERE create_time < NOW()-INTERVAL 3 MONTH;DELETE FROM orders WHERE create_time < NOW()-INTERVAL 3 MONTH;

3. 搜索引擎分流：
   将分页查询交给 Elasticsearch 等专用引擎

---

三、索引设计黄金法则

错误示例：

SELECT * FROM users ORDER BY name LIMIT 800000, 10;
-- 全表扫描 filesort

正确索引方案：

1. 覆盖索引加速：

   ALTER TABLE users ADD INDEX idx_cover(name, id, age);

2. 延迟关联写法：

   SELECT * FROM users 
   JOIN (SELECT id FROM users ORDER BY name LIMIT 800000, 10
   ) tmp USING(id)

---

四、各方案性能对比（百万数据）

方案	扫描行数	执行时间	适用场景
原生 LIMIT	800010	1.8s	前100页
延迟关联	800010 + 10行回表	0.15s	通用
游标分页	10	0.01s	连续分页
范围分页	依赖筛选区间	0.03s	按时间/数值排序
业务层禁止跳页	10	0.01s	C端用户场景

💡 延迟关联 是通用性最强的优化方案

---

五、实战排查工具

用 EXPLAIN 诊断扫描类型

EXPLAIN SELECT * FROM orders LIMIT 100000, 10;

• ⚠️ 危险信号：
  type: ALL（全表扫描）
  Extra: Using filesort（内存排序）

---

总结：优化决策树

最终建议：

1. 优先用 延迟关联 + 覆盖索引

2. 次选用 游标分页（体验牺牲换性能）

3. 终极方案 Elasticsearch 专库分页查询