ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

一、什么是 SQL 深分页

在 SQL 查询中，分页是常用的功能，通常通过 LIMIT offset, size（MySQL）等方式实现。当分页的 offset 值过大（例如 LIMIT 100000, 10）时，就属于「深分页」。

核心特征：查询需要跳过大量数据，只获取少量结果。表面上是获取第 100001-100010 条数据，但数据库需要先扫描前 100010 条数据，再丢弃前 100000 条，仅返回最后 10 条，这种无效扫描会导致性能急剧下降。

举个直观例子：当 offset=100000，size=10 时，MySQL 会扫描 100010 条数据，筛选后只返回 10 条；若 offset 达到 1000000，即使只取 10 条，也需要扫描百万级数据，查询耗时会呈指数级增长。

二、深分页的性能瓶颈成因

2.1 数据库执行逻辑导致的无效扫描

以 MySQL 的 LIMIT offset, size 为例，其执行流程如下：

1. 根据查询条件（若有 WHERE 子句）扫描符合条件的行，按排序规则（ORDER BY）排序；
2. 从排序后的结果中，跳过前 offset 条数据；
3. 取后续 size 条数据返回。

关键问题：offset 越大，需要跳过的数据越多，数据库扫描的总行数就越多，I/O 开销、CPU 开销会大幅增加——哪怕最终只返回少量数据，前期的跳过操作也需要完整扫描前 offset+size 条数据，属于典型的做无用功。

2.2 索引失效或索引利用率低

深分页场景中，若查询未命中合适的索引，会触发全表扫描，此时 offset 越大，全表扫描的耗时越长；即使命中索引，若排序字段与索引字段不一致，数据库会进行“回表查询”（从索引找到主键，再去主键索引查完整数据），大量回表操作也会加剧性能损耗。

补充：若查询中包含 SELECT *、非索引字段筛选、复杂排序（如多字段排序、非索引字段排序），都会进一步降低索引利用率，放大深分页的性能问题。

2.3 数据量与分页深度的正相关损耗

当表中数据量达到百万级、千万级时，深分页的性能损耗会更加明显：

• 百万级数据：offset=100000 时，查询耗时可能从几十毫秒飙升到几百毫秒；
• 千万级数据：offset=1000000 时，查询耗时可能达到几秒甚至十几秒，严重影响接口响应速度。

三、深分页常见优化方案

优化核心思路：减少数据库的无效扫描和回表操作，通过定位目标数据的起始位置，直接获取需要的结果，避免跳过大量数据。

3.1 方案一：基于主键/唯一索引的跳页优化（最常用、最高效）

核心原理

利用主键（或唯一索引）的有序性，通过上一页的最后一条数据的主键值，替代 offset，直接定位到目标数据的起始位置，避免扫描前 offset 条数据。

适用场景：分页排序字段为主键（如 id），或可通过主键关联排序的场景（如按创建时间排序，同时存储创建时间+主键，确保排序唯一）。

示例（MySQL）

传统深分页（低效）：

-- 取第 100001-100010 条数据，offset=100000，需要扫描 100010 条
SELECT id, name, create_time FROM user ORDER BY id ASC LIMIT 100000, 10;

优化后（高效）：

-- 假设上一页最后一条数据的 id=100000，直接定位到 id>100000 的数据，只扫描 10 条
SELECT id, name, create_time FROM user WHERE id > 100000 ORDER BY id ASC LIMIT 10;

优点与注意事项

• 优点：性能最优，扫描行数等于 size，无无效扫描；无需回表（若查询字段均在索引中，即覆盖索引）。
• 注意事项：需要记录上一页的最后一条数据的主键值（前端或后端缓存）；不支持“跳页”（如直接从第 1 页跳至第 1000 页），仅支持“上一页/下一页”的连续分页；排序字段需与主键（或唯一索引）关联，确保排序唯一（避免数据重复或遗漏）。

3.2 方案二：书签分页（适配非主键排序场景）

核心原理

当排序字段不是主键时，可将“排序字段+主键”作为“书签”，通过书签定位目标数据的起始位置，替代 offset。本质是方案一的延伸，解决非主键排序的深分页问题。

适用场景：按非主键字段排序（如 create_time、score），且排序字段可能重复（需结合主键确保唯一）。

示例（MySQL）

传统深分页（低效）：

-- 按 create_time 排序，取第 100001-100010 条，offset 过大，扫描量大
SELECT id, name, create_time FROM user ORDER BY create_time DESC LIMIT 100000, 10;

优化后（高效）：

-- 假设上一页最后一条数据的 create_time='2024-01-01 12:00:00'，id=100000
-- 利用 create_time 和 id 作为书签，直接定位，避免扫描前 100000 条
SELECT id, name, create_time 
FROM user 
WHERE create_time < '2024-01-01 12:00:00' 
   OR (create_time = '2024-01-01 12:00:00' AND id < 100000)
ORDER BY create_time DESC, id DESC 
LIMIT 10;

优点与注意事项

• 优点：支持非主键排序，性能接近方案一，无无效扫描；
• 注意事项：需要记录上一页的“排序字段值+主键值”作为书签；排序条件需与 WHERE 条件中的书签逻辑一致，确保定位准确；建议给“排序字段+主键”建立联合索引，进一步提升查询效率（避免回表）。

3.3 方案三：延迟关联（减少回表开销）

核心原理

当查询字段较多（需回表）时，先通过索引查询出目标数据的主键，再通过主键关联原表获取完整数据——减少回表的行数（仅回表 size 条数据，而非 offset+size 条）。

适用场景：查询字段较多，无法使用覆盖索引，且必须支持跳页（如允许用户直接输入页码跳转）。

示例（MySQL）

传统深分页（低效，回表 100010 条）：

SELECT id, name, age, create_time FROM user WHERE status=1 ORDER BY create_time DESC LIMIT 100000, 10;

优化后（高效，回表 10 条）：

-- 第一步：通过索引查询目标数据的主键，仅扫描 100010 条（无法避免，但不回表）
-- 第二步：通过主键关联原表，仅回表 10 条数据
SELECT u.id, u.name, u.age, u.create_time 
FROM user u
INNER JOIN (
    SELECT id FROM user WHERE status=1 ORDER BY create_time DESC LIMIT 100000, 10
) t ON u.id = t.id;

优点与注意事项

• 优点：支持跳页，减少回表开销，性能比传统深分页提升明显；
• 注意事项：子查询需命中索引（如 status+create_time+id 的联合索引），否则子查询本身仍会低效；适用于 offset 不是特别大（如 10 万以内）的场景，若 offset 达到百万级，子查询的扫描开销仍较大。

3.4 方案四：数据预聚合/分区（海量数据场景）

核心原理

针对千万级、亿级海量数据，通过“预聚合”或“分区表”减少查询范围：

• 预聚合：按分页维度（如时间）提前聚合数据，例如按天、按月将数据存入不同的中间表，查询时先定位到对应中间表，再在小范围内分页；
• 分区表：将大表按主键或时间字段分区（如 MySQL 的分区表、PostgreSQL 的分区表），查询时仅扫描目标分区，减少扫描范围。

适用场景：数据量极大（千万级以上），且分页维度固定（如按时间分页）。

示例（MySQL 分区表）

-- 按 create_time 分区（按月分区）
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    create_time DATETIME
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    ...
);

-- 查询 2024-01 的数据，仅扫描 p202401 分区，减少扫描范围
SELECT id, name, create_time FROM user 
WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC LIMIT 100000, 10;

3.5 方案五：限制分页深度（业务层面优化）

核心原理

从业务层面限制分页深度，避免用户访问过深的页码（如限制最大页码为 1000 页，超过则提示“请缩小查询范围”），从源头减少深分页查询。

适用场景：业务上允许限制分页深度，且用户很少需要访问过深页码（如电商商品列表、新闻列表）。

补充建议

结合搜索功能：当用户需要查看大量数据时，引导用户使用搜索、筛选功能（如按关键词、时间范围筛选），缩小查询结果集，避免深分页。

3.6 其他方案（特定场景适用）

• 使用专门的分页组件：如 Elasticsearch 的 scroll 分页、cursor 分页，适用于全文检索场景下的深分页；
• 缓存热点分页：将高频访问的前几页数据缓存（如 Redis），减少数据库查询压力，深分页场景可缓存热门页码的结果；
• 读写分离：将分页查询引导至只读副本，避免影响主库性能（适用于主从架构）。

3.7 方案七：分布式系统SQL分页查询与深分页防止（新增）

3.7.1 分布式系统SQL分页查询核心难点

分布式系统中，数据通常按规则（如哈希、范围）分库分表（如 Sharding-JDBC 分库分表），分页查询的核心难点的是：数据分散在不同节点，无法直接进行全局排序和分页，若处理不当，会出现数据重复、数据遗漏、分页性能极差等问题，且深分页的性能损耗会比单机场景更严重（跨节点数据传输、节点间协同开销）。

分布式分页与单机分页的核心区别：单机分页可直接扫描本地数据排序分页，而分布式分页需协调多个节点，汇总各节点数据后再进行全局排序，或通过分片规则规避全局排序。

3.7.2 分布式系统SQL分页查询常用方法

核心思路：减少跨节点数据传输，尽量在分片节点本地完成分页筛选，再汇总结果，避免全量汇总后再分页（避免无效数据传输）。

方法1：全局排序分页（适用于一致性要求高、数据量适中场景）

核心原理：先让每个分片节点按分页条件（排序、筛选）查询出本地的前 N 条数据（N = offset + size），将所有节点的结果汇总到主节点，主节点对汇总数据进行全局排序，再执行 offset 和 size 分页，返回最终结果。

示例（Sharding-JDBC 分库分表场景）：

-- 分库分表：user 表按 id 哈希分 4 个分片（ds0-ds3），查询第 10001-10010 条数据
-- 底层执行逻辑：每个分片查询本地前 10010 条（offset+size），汇总到主节点后全局排序，再取 offset=10000、size=10
SELECT id, name, create_time FROM user ORDER BY create_time DESC LIMIT 10000, 10;

优点与注意事项：

• 优点：数据一致性高，分页结果与单机分页完全一致；
• 注意事项：offset 越大，各节点需返回的数据越多，跨节点传输开销、主节点排序开销越大，深分页性能极差（offset 百万级时不可用）。

方法2：分片分页（适用于数据量大、一致性要求适中场景）

核心原理：利用分库分表的分片规则（如范围分片、哈希分片），先计算目标数据所在的分片节点，仅在目标分片内执行分页查询，避免跨节点汇总。需结合分片键和排序字段，确保分页逻辑与分片规则匹配。

示例（按 create_time 范围分片，每月一个分片）：

-- 分库分表：user 表按 create_time 范围分表，2024-01 数据在 user_202401 分片，2024-02 在 user_202402 分片
-- 查询 2024-01 期间的第 1001-1010 条数据，直接定位到 user_202401 分片，本地分页
SELECT id, name, create_time FROM user_202401 
WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC LIMIT 1000, 10;

优点与注意事项：

• 优点：无需跨节点汇总，性能接近单机分页，适合深分页场景；
• 注意事项：需依赖分片规则，仅适用于“按分片字段筛选”的分页场景；若查询无分片字段筛选，仍需全局汇总。

方法3：中间件协助分页（主流方案）

借助分布式数据库中间件（如 Sharding-JDBC、MyCat、OceanBase）的内置分页功能，自动处理跨节点分页逻辑，无需手动协调各分片。中间件会根据分页场景，自动选择“全局排序分页”或“分片分页”，并优化数据传输和排序效率。

关键优化：中间件可通过“流式排序”“分页缓存”减少跨节点数据传输，例如仅传输各分片的排序字段和主键，汇总后再回表查询完整数据（分布式场景下的延迟关联）。

3.7.3 分布式场景下防止深分页的策略

结合分布式系统特点，在单机防止深分页策略的基础上，补充以下针对性方法，从源头减少深分页开销：

1. 分片预过滤，缩小查询范围：利用分库分表的分片规则，强制分页查询必须携带分片字段（如时间、用户ID），将查询范围限制在单个或少数几个分片，避免全分片汇总分页；
2. 全局书签分页（分布式适配）：沿用单机书签分页思路，将“排序字段+主键+分片键”作为全局书签，记录上一页的书签信息，下一页查询时，各分片根据书签筛选本地数据，再汇总少量结果，避免全局排序；
3. 分布式数据预聚合：按分页维度（如时间）在各分片节点提前聚合数据，生成中间表，分页查询时直接查询中间表，减少各节点的计算和传输开销；
4. 限制全局分页深度：结合业务需求，限制分布式分页的最大页码（如最大 500 页），超过页码提示用户缩小筛选范围（如按时间、关键词筛选）；
5. 借助分布式缓存：将高频分页结果（如前 100 页）缓存到分布式缓存（如 Redis Cluster），深分页场景引导用户使用筛选功能，避免直接访问数据库；
6. 分片节点本地优化：给各分片节点的查询字段建立合适的联合索引（筛选字段+排序字段+主键），减少分片本地的扫描和回表开销，间接优化分布式分页性能。

四、优化方案对比与选择建议

五、注意事项与避坑点

1. 索引设计是基础：无论哪种优化方案，合适的索引都是关键——优先建立“筛选字段+排序字段+主键”的联合索引，避免索引失效；
2. 避免 SELECT *：查询时只取需要的字段，尽量使用覆盖索引，减少回表操作；
3. 排序字段需唯一：若排序字段可能重复（如 create_time），需结合主键排序，避免分页时数据重复或遗漏；
4. 分库分表场景：若数据分库分表，深分页需考虑跨库跨表的分页逻辑（如全局排序），可借助中间件（如 Sharding-JDBC）实现；
5. 性能测试：优化后需进行测试，对比不同 offset 下的查询耗时，确保优化效果（如 offset=10 万、100 万时的耗时变化）。

六、总结

SQL 深分页的核心问题是“无效扫描”和“回表开销”，优化的关键是“减少不必要的扫描和回表”。实际开发中，应根据业务场景（是否需要跳页、数据量、排序字段）选择合适的优化方案：

• 若支持连续分页（上/下一页），优先选择「主键/唯一索引跳页」或「书签分页」，性能最优；
• 若必须支持跳页（如用户输入页码），优先选择「延迟关联」，结合索引优化；
• 若数据量极大（千万级以上），需结合「数据预聚合/分区」，从存储层面减少查询范围；
• 业务层面可通过「限制分页深度」「引导搜索筛选」，从源头规避深分页问题。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花