为什么深分页慢？

LIMIT 100000, 10 触发全索引扫描 100010 行，再抛弃前 10 万行。数据量达百万级时，响应时间呈线性增长，甚至阻塞主库。

五大优化策略实测对比

策略一：子查询锚定主键（推荐通用）

-- 原始：3.21s
SELECT * FROM wl_tagindex WHERE byname='f' ORDER BY id LIMIT 300000, 10;

-- 优化：0.11s
SELECT * FROM (
    SELECT id FROM wl_tagindex WHERE byname='f' ORDER BY id LIMIT 300000, 10
) a
LEFT JOIN wl_tagindex b ON a.id = b.id;

关键：联合索引 (byname, id) 覆盖子查询，仅扫描索引叶子节点。

策略二：游标分页（ID 单调递增场景）

-- 首页
SELECT id, name FROM users ORDER BY id LIMIT 20;

-- 翻页（记录上页最大 id=100073）
SELECT id, name FROM users WHERE id > 100073 ORDER BY id LIMIT 20;

无 OFFSET，性能恒定，适合无限滚动、API 分页。

策略三：反向查找（偏移量 > 总行数 50%）

-- 正向：2.65s
SELECT * FROM abc WHERE BatchID=123 LIMIT 1199960, 40;

-- 反向：1.00s
SELECT * FROM abc WHERE BatchID=123 ORDER BY InputDate DESC LIMIT 428775, 40;

需知总行数，结果倒序，前端再反转。

策略四：限制最大翻页深度

阿里巴巴 DBA 实践：offset > 5000 直接返回空或引导用户用搜索/筛选。保护数据库，改善用户体验。

策略五：只查主键再回表

SELECT id, title, content FROM items
WHERE id IN (SELECT id FROM items ORDER BY id LIMIT 900000, 10);

适用于带大字段（TEXT/BLOB）的表，减少回表数据量。

实测数据（169 万行 Member 表）

mysql> SET profiling=1;

-- 基准
SELECT * FROM Member LIMIT 100000, 100;                    -- 0.0996s

-- 子查询优化
SELECT * FROM Member WHERE MemberID >= (
    SELECT MemberID FROM Member LIMIT 100000, 1
) LIMIT 100;                                               -- 0.0245s

-- 结论：偏移量 ≥ 1000 时子查询显著提速

选型决策树

IF 业务允许游标分页 (ID 单增、无删或删不影响)
    → 策略二（最优）
ELSE IF 有 WHERE 条件且可建联合索引
    → 策略一（子查询锚定）
ELSE IF offset > 总行数/2
    → 策略三（反向查找）
ELSE
    → 策略四（限制深度）或 策略五（只查主键）

避坑提醒

• 子查询法要求数据物理顺序连续，WHERE 过滤会打破连续性导致结果偏移。
• 反向查找依赖 ORDER BY 列有索引，否则全表排序更慢。
• 所有优化建立在正确索引基础上，无索引一切白搭。