深分页末日救星：MySQL延迟关联原理与实战手册

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);

性能对比：

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

方案 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)

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

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

五、实战排查工具

用 EXPLAIN 诊断扫描类型

EXPLAIN SELECT * FROM orders LIMIT 100000, 10;

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

总结：优化决策树

最终建议：

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

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

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