1. 问题背景：为什么深度分页会成为性能杀手？
第一次被面试官问到"MySQL 深度分页如何优化"时，我下意识回答"用 LIMIT offset, size 不就行了？"结果当场被追问："那翻到第10000页怎么办？"这个场景成了我职业生涯的转折点。让我们从一个真实案例开始：某电商平台商品列表页，当用户翻到第500页（每页20条）时，页面加载时间从最初的200ms飙升到12秒——这正是典型的深度分页问题。
MySQL 的 LIMIT 分页在底层执行时，会先读取 offset + size 条记录，然后丢弃前 offset 条。这意味着查询LIMIT 10000, 20实际需要读取10020行数据（即便最终只返回20条）。我曾在生产环境用 EXPLAIN 分析这类查询，发现随着 offset 增大，执行时间呈线性增长：
# 测试表含200万条商品数据 SELECT * FROM products LIMIT 100, 20; -- 0.002s SELECT * FROM products LIMIT 10000, 20; -- 0.12s SELECT * FROM products LIMIT 100000, 20; -- 1.3s SELECT * FROM products LIMIT 500000, 20; -- 6.8s这种性能劣化的根本原因在于：MySQL 的查询执行流程中，WHERE 条件过滤、JOIN 操作等步骤完成后，才会应用 LIMIT 子句。也就是说，引擎必须先处理满足条件的所有数据，再截取指定片段。
关键理解：LIMIT 的 offset 本质上是一种"先取后舍"的操作，而非直接定位。这与书籍的页码完全不同——翻到第100页时，人类可以直接打开对应位置，但数据库必须逐页"数"过去。
2. 基础优化方案：延迟关联与索引覆盖
2.1 延迟关联（Deferred Join）
这是我解决该问题的首选方案。核心思路是：先通过覆盖索引快速定位到需要的主键，再通过主键关联获取完整数据。改写后的查询如下：
SELECT t.* FROM products t JOIN ( SELECT id FROM products ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id = tmp.id;在我的压力测试中，该方案将LIMIT 100000, 20的查询从1.3s降到0.15s。原理在于：
- 子查询只需读取 id（通常已在索引中），避免回表操作
- 外层查询通过主键精确匹配，效率极高
2.2 索引覆盖优化
如果查询字段全部包含在某个索引中，可以直接使用该索引避免回表：
-- 假设有联合索引 (category_id, status, create_time) SELECT id, category_id, status, create_time FROM products WHERE category_id = 5 AND status = 1 ORDER BY create_time DESC LIMIT 100000, 20;实测中，这种方案比原始查询快10倍以上。但有两个限制：
- 只能返回索引包含的字段
- 需要提前规划合适的联合索引
实战经验：我曾遇到一个案例，某报表系统需要分页展示 create_time、price、sales 三个字段。通过创建 (create_time, price, sales) 的联合索引，将5秒的查询优化到0.3秒——这正是索引覆盖的威力。
3. 高级优化方案：游标分页与预计算
3.1 游标分页（Cursor-based Pagination）
这是现代应用中更推荐的方式。基本模式是：
-- 第一页 SELECT * FROM products WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 20; -- 后续页（假设上一页最后一条记录的create_time='2023-06-15 08:00:00', id=12345） SELECT * FROM products WHERE status = 1 AND (create_time < '2023-06-15 08:00:00' OR (create_time = '2023-06-15 08:00:00' AND id < 12345)) ORDER BY create_time DESC, id DESC LIMIT 20;这种方案的优势在于：
- 完全避免 offset，性能恒定
- 适合无限滚动加载场景
- 不受新增/删除数据影响（传统分页会导致数据重复或遗漏）
我在社交APP的feed流中应用此方案后，第1000页的加载时间与第1页保持一致（约200ms）。
3.2 预计算分页
对于复杂聚合查询，可以采用预计算策略：
- 创建分页结果缓存表
- 通过定时任务或触发器维护数据
- 查询时直接从缓存表获取
例如电商平台的商品筛选页：
-- 预计算表 CREATE TABLE product_list_cache ( page_num INT UNSIGNED, position INT UNSIGNED, product_id BIGINT, PRIMARY KEY (page_num, position) ); -- 查询时 SELECT p.* FROM products p JOIN product_list_cache c ON p.id = c.product_id WHERE c.page_num = 1000 ORDER BY c.position;某跨境电商平台采用此方案后，将分页查询的TP99从8秒降到50毫秒。代价是需要额外的存储空间和维护成本。
4. 特殊场景下的优化技巧
4.1 倒序分页的优化
许多列表页需要同时支持正序和倒序分页。对于倒序分页，可以结合正序查询和程序处理：
-- 原始倒序查询（性能差） SELECT * FROM products ORDER BY create_time ASC LIMIT 100000, 20; -- 优化方案 SELECT * FROM ( SELECT * FROM products ORDER BY create_time DESC LIMIT 0, 100020 ) t ORDER BY create_time ASC LIMIT 100000, 20;虽然看起来绕，但在我的测试中，200万数据量下，这种方案比直接倒序分页快6倍。
4.2 分布式ID场景
在使用雪花ID等分布式ID时，可以利用ID的时间有序性优化分页：
-- 传统方式 SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20; -- 优化方式（假设id包含时间戳） SELECT * FROM orders WHERE id < last_page_min_id ORDER BY id DESC LIMIT 20;某金融系统采用此方案后，分页查询性能提升20倍。
5. 实战中的避坑指南
5.1 联合索引的顺序陷阱
我曾踩过一个坑：为商品表创建了 (status, category_id, create_time) 的索引，但以下查询仍然很慢：
SELECT * FROM products WHERE category_id = 5 AND status = 1 ORDER BY create_time DESC LIMIT 100000, 20;问题在于：联合索引的最左前缀原则。正确的索引顺序应该是 (category_id, status, create_time)。
5.2 游标分页的边界条件
实现游标分页时，必须处理字段值重复的情况。例如：
-- 错误示例（可能导致漏数据） SELECT * FROM products WHERE create_time < '2023-06-15' ORDER BY create_time DESC LIMIT 20; -- 正确做法（添加唯一键作为tie-breaker） SELECT * FROM products WHERE create_time < '2023-06-15' OR (create_time = '2023-06-15' AND id < 12345) ORDER BY create_time DESC, id DESC LIMIT 20;5.3 分页与事务隔离级别
在RR隔离级别下，分页查询可能出现幻读。解决方案：
- 使用游标分页
- 添加 FOR UPDATE 或 LOCK IN SHARE MODE
- 降低隔离级别到RC
某交易系统曾因这个问题导致分页结果重复，最终采用方案1解决。
6. 性能对比与方案选型
通过sysbench构造1000万条测试数据，各方案性能对比：
| 方案 | offset=1万耗时 | offset=10万耗时 | offset=100万耗时 |
|---|---|---|---|
| 原生LIMIT | 120ms | 1100ms | 11s |
| 延迟关联 | 15ms | 130ms | 1.2s |
| 索引覆盖 | 8ms | 75ms | 800ms |
| 游标分页 | 5ms | 5ms | 5ms |
| 预计算 | 2ms | 2ms | 2ms |
选型建议：
- 简单场景：优先尝试延迟关联
- 只读列表：游标分页是最佳选择
- 复杂聚合：考虑预计算方案
- 管理后台：可接受延迟关联+索引覆盖的组合
7. 真实案例：某社交平台的分页改造
某社交平台的消息中心原有分页实现：
SELECT * FROM user_messages WHERE user_id = 12345 AND is_deleted = 0 ORDER BY create_time DESC LIMIT ?, 20;当用户消息超过10万条时，翻页体验急剧下降。我们分三个阶段进行优化：
阶段一：延迟关联+索引优化
SELECT m.* FROM user_messages m JOIN ( SELECT id FROM user_messages WHERE user_id = 12345 AND is_deleted = 0 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON m.id = tmp.id;创建 (user_id, is_deleted, create_time, id) 的联合索引，性能提升8倍。
阶段二：引入游标分页
SELECT * FROM user_messages WHERE user_id = 12345 AND is_deleted = 0 AND (create_time < ? OR (create_time = ? AND id < ?)) ORDER BY create_time DESC LIMIT 20;前端记录每页最后一条的 create_time 和 id，性能提升40倍。
阶段三：热点用户特殊处理对活跃用户（粉丝>1万）的消息列表进行预计算，夜间跑批处理：
INSERT INTO user_message_cache (user_id, page_num, position, message_id) SELECT 12345 AS user_id, FLOOR((COUNT(*) OVER() - ROW_NUMBER() OVER(ORDER BY create_time DESC)) / 20) + 1 AS page_num, ...最终实现：普通用户TP99=50ms，大V用户TP99=20ms。
