在大数据量场景下（如电商订单表），传统的深度分页查询方式会导致 I/O 资源的巨大浪费，甚至引发关键业务查询的链式阻塞。本文将深入探讨深度分页的技术挑战，并提供经过实战检验的 优化方案，帮助你应对这一难题。

---

🌟 问题背景

假设我们有一个电商平台的订单表，存储了 2000 万条记录，表结构如下：

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT, -- 自增主键
  `user_id` int DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP, -- 创建时间默认为当前时间
  PRIMARY KEY (`id`),
  KEY `idx_userid_create_time` (`user_id`, `create_time`) -- (user_id + create_time)联合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

分页查询示例

通常我们会使用以下 SQL 进行分页查询：

SELECT * FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 0, 20; -- 第一页

当用户查询第 1000 页 的订单（每页 20 条）时，常见的分页写法如下：

SELECT * FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 19980, 20; -- 查询第 1000 页的数据

执行流程解析

1. 使用联合索引 idx_userid_create_time 读取 19980 + 20 条数据。
2. 利用索引在内存中排序。
3. 丢弃前 19980 条数据，返回剩下的 20 条。

随着页码增加，需要处理的数据量会线性增长。当 OFFSET 达到 10w 时，查询耗时显著增加；达到 100w 时，查询可能需要数秒甚至更多。

深度分页的性能瓶颈

传统深度分页的方式存在以下几个问题：

• I/O 资源浪费：即使只返回少量数据，数据库仍需扫描大量无关记录。
• 查询效率低下：随着偏移量增大，查询时间呈线性增长。
• 链式阻塞：高并发场景下，深度分页可能导致关键业务查询被阻塞。

深度分页优化方案

1️⃣ 游标分页（Cursor-based Pagination）

适用场景

• 支持连续分页（如无限滚动加载）
• 不适合随机跳页（如直接跳转到第 1000 页）

实现原理

基于有序且唯一的字段（如自增主键 ID），通过记录上一页最后一条记录的标识（如主键 ID），结合 WHERE 条件与索引，跳过已查询数据。

示例：

-- 第一页
SELECT *
FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

-- 后续页（记录上一页查询得到的 id，id=1000）
SELECT id, user_id, amount
FROM orders
WHERE id > 1000 AND user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

优势

• 完全避免 OFFSET 扫描，时间复杂度从 O(N) 降为 O(1)
• 天然支持顺序分页场景（如无限滚动加载）

限制

• 不支持随机跳页
• 需保证排序字段唯一且有序

---

2️⃣ 延迟关联（Deferred Join）

实现原理

通过子查询先获取主键范围，再关联主表获取完整数据，减少回表次数，利用覆盖索引优化性能。

示例：

SELECT t1.*
FROM orders t1
INNER JOIN (
    SELECT id
    FROM orders
    WHERE user_id = 'Chaya'
    ORDER BY create_time DESC
    LIMIT 1000000, 20
) t2 ON t1.id = t2.id;

优势

• 子查询仅扫描索引树，避免回表开销
• 主查询通过主键精确匹配，效率极高
• 性能提升可达 10 倍以上（实测从 1.2 秒降至 0.05 秒）

---

3️⃣ 覆盖索引优化

实现原理

创建包含查询字段的联合索引，避免回表操作。例如索引设计为 (user_id, id, create_time, amount)。

示例：

ALTER TABLE orders ADD INDEX idx_cover (user_id, id, create_time, amount);

SELECT id, user_id, amount, create_time
FROM orders USE INDEX (idx_cover)
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 1000000, 20;

优势

• 减少回表操作，提高查询效率
• 特别适用于查询多个字段的情况

---

4️⃣ 数据预取与缓存

实现原理

提前加载部分热门数据到缓存中，减少数据库查询压力。

优势

• 提高响应速度
• 减轻数据库负担

💬 订单详情查询

Q：订单有很多字段，我想查看更多订单细节怎么办？

针对这种情况，可以设计订单列表和详情页：

• 订单列表：通过上述优化方案进行分页查询，展示简要信息。

-- 订单列表分页查询
SELECT id, user_id, amount, create_time
FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

-- 订单详情查询
SELECT *
FROM orders
WHERE id = 1000; -- 使用订单 ID 查询详细信息

对于列表的详情建议使用IN 

场景	推荐方案
ID 数量 < 1000	直接用 IN
ID 数量  >1000	分批查询 【拆in的条件，将in的过多条件在代码里循环；或force index】
高频查询	考虑缓存或异步预加载