SQL Server 大数据量分页思路及实现

[TOC]

## 1. 大数据分页方案

在千万级甚至亿级数据量的场景下，分页的核心挑战是**避免全表扫描或大量前置数据的扫描**，传统依赖 `OFFSET` 或序号计算的方式会因需遍历大量无关数据而性能急剧下降。除了前文提到的“游标定位分页（Keyset Pagination）”外，还有几种专为超大规模数据设计的高效分页方式，核心思路是**利用数据的物理存储特性、索引结构或预处理逻辑，直接定位目标页数据**。

### 1.1. 基于“分片（Sharding）”的分页

#### 1.1.1. 原理与逻辑

将千万级大表**按规则水平分片**（拆分为多个子表），每个分片存储一部分数据（如按主键范围、时间区间或哈希值分片）。分页时先通过分片键计算目标数据所在的分片，再仅在该分片内执行分页查询，避免扫描全表。

- **核心思路**：大表拆分为小表 → 定位目标分片 → 分片内分页 → 合并结果（如需跨分片）。
- **依赖条件**：需提前规划分片规则（如 `id % 10` 分为10个分片），且分片键需与排序字段相关（如按 `id` 分片则按 `id` 排序）。

#### 1.1.2. 适用场景

- 数据量超千万级，且可按规则分片（如订单表按用户ID哈希分片、日志表按日期分片）。
- 分布式数据库环境（如MySQL分库分表、SQL Server分区表）。
- 分页查询条件与分片键相关（如按“近7天数据”分页，可直接定位到对应日期分片）。

#### 1.1.3. 案例示例（以SQL Server分区表为例）

假设 `order_tbl`（千万级订单表）按 `id` 范围分为3个分区：
- 分区1：`id <= 1000000`
- 分区2：`1000001 < id <= 5000000`
- 分区3：`id > 5000000`

需求：按 `id` 升序，获取第 200 页数据（每页 50 条，即 `id` 范围 `(200-1)*50+1=9951` 到 `200*50=10000`）。

```sql
-- 1. 计算目标id范围：9951-10000，落在分区1（id <= 1000000）
-- 2. 直接在分区1内查询，无需扫描其他分区
SELECT *
FROM order_tbl
WHERE $PARTITION.order_partition_func(id) = 1  -- 定位分区1
  AND id BETWEEN 9951 AND 10000
ORDER BY id ASC;
```

**优势**：仅扫描目标分片（数据量可能仅为原表的1/10或1/100），千万级数据可降至“十万级”查询，性能提升显著。

#### 1.1.4. 优缺点
- **优点**：彻底规避全表扫描，性能随分片数线性提升；适合分布式系统水平扩展。
- **缺点**：需提前设计分片规则，维护成本高；跨分片分页（如数据分布在多个分片）需合并结果，逻辑复杂。

### 1.2. 基于“预计算（物化视图/快照）”的分页

#### 1.2.1. 原理与逻辑

对高频分页的大表，**定期预计算并存储分页结果**（如按排序字段生成“每页起始值-结束值”映射表），查询时直接通过映射表定位目标页的起始位置，再查询原始表。

- **核心思路**：预处理生成分页索引 → 查询时通过索引定位 → 直接获取目标页数据。
- **依赖条件**：数据变更频率低（如每日更新一次），适合静态或准静态数据（如报表、历史日志）。

#### 1.2.2. 适用场景

- 数据不频繁变更（如每日增量更新的统计数据、历史订单归档表）。
- 分页排序字段固定（如固定按 `create_time` 或 `id` 排序）。
- 对查询延迟要求高，但可接受数据有一定时效性（如非实时报表）。

#### 1.2.3. 案例示例

假设 `statistic_tbl`（千万级日活统计数据）需按 `date` 升序分页（每页 100 条），预计算步骤：

1. **创建分页映射表**：
```sql
-- 存储每页的起始date和结束date
CREATE TABLE page_mapping (
    page_num INT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    row_count INT  -- 本页实际行数
);
```

2. **定时预计算（每日凌晨执行）**：
```sql
-- 按date排序，每100条划分为一页，记录每页的起止date
WITH ranked_data AS (
    SELECT date,
           ROW_NUMBER() OVER (ORDER BY date ASC) AS row_num
    FROM statistic_tbl
)
INSERT INTO page_mapping (page_num, start_date, end_date, row_count)
SELECT
    (row_num - 1) / 100 + 1 AS page_num,
    MIN(date) AS start_date,
    MAX(date) AS end_date,
    COUNT(*) AS row_count
FROM ranked_data
GROUP BY (row_num - 1) / 100 + 1;
```

3. **查询第 50 页数据**：
```sql
-- 1. 从映射表获取第50页的起止date
DECLARE @start_date DATE, @end_date DATE;
SELECT @start_date = start_date, @end_date = end_date
FROM page_mapping
WHERE page_num = 50;
-- 2. 直接按date范围查询原始表
SELECT *
FROM statistic_tbl
WHERE date BETWEEN @start_date AND @end_date
ORDER BY date ASC;
```

**优势**：预计算将分页查询转化为“范围查询”，避开实时计算序号的开销，千万级数据查询可降至毫秒级。

#### 1.2.4. 优缺点

- **优点**：查询性能极致优化，适合高频访问的静态数据；避免实时扫描大量数据。
- **缺点**：数据有延迟（依赖预计算周期）；不适合实时变更的数据；维护预计算任务增加系统复杂度。

### 1.3. 基于“索引覆盖扫描”的分页

#### 1.3.1. 原理与逻辑

通过创建**包含排序字段和查询所需所有字段的复合索引**，使分页查询仅需扫描索引（“索引覆盖扫描”），无需回表访问原始数据，大幅减少IO开销（索引通常比表小得多，且有序存储）。

- **核心思路**：索引包含所有查询字段 → 分页查询仅扫描索引 → 避免回表IO。
- **依赖条件**：查询字段固定且较少（否则索引过大）；排序字段需在索引最左侧（符合索引有序性）。

#### 1.3.2. 适用场景

- 查询字段固定（如仅需 `id`、`name`、`create_time` 三个字段）。
- 排序字段明确（如固定按 `create_time DESC` 排序）。
- 千万级数据但索引可完全加载到内存（或索引碎片少）。

#### 1.3.3. 案例示例

假设 `user_log_tbl`（千万级用户操作日志）需按 `create_time DESC` 分页，每次查询 `id`、`user_id`、`action`、`create_time` 四个字段。

1. **创建覆盖索引**：
```sql
-- 索引包含排序字段（create_time）和所有查询字段，确保无需回表
CREATE NONCLUSTERED INDEX idx_cover_log 
ON user_log_tbl (create_time DESC)
INCLUDE (id, user_id, action);  -- 包含其他查询字段
```

2. **分页查询第 100 页（每页 20 条）**：
```sql
-- 利用索引覆盖扫描，仅扫描索引即可获取数据
SELECT id, user_id, action, create_time
FROM user_log_tbl
ORDER BY create_time DESC
OFFSET (100-1)*20 ROWS FETCH NEXT 20 ROWS ONLY;
```

**优势**：索引通常比表小50%以上，且有序存储，扫描效率远高于全表扫描；避免回表操作，IO成本降低80%以上。

#### 1.3.4. 优缺点
- **优点**：无需改变分页逻辑，仅通过索引优化即可提升性能；适用于大多数需要保留“跳页”功能的场景。  
- **缺点**：索引维护成本高（写入时需更新索引）；查询字段变化时需重新设计索引；超深分页（如第10万页）仍有性能损耗。

### 1.4. 基于“时间范围”的分页（特殊场景优化）

#### 1.4.1. 原理与逻辑

对按时间排序的流水数据（如日志、订单、消息），**用时间区间代替“页码+行数”计算**。例如，“第1页”对应“最近1小时数据”，“第2页”对应“1-2小时前数据”，通过时间范围直接筛选，避免按行数计算偏移量。

- **核心思路**：时间有序 → 用时间区间划分“页” → 直接按时间筛选。
- **依赖条件**：数据严格按时间增长（如创建时间 `create_time` 单调递增）；每页数据量可接受一定波动（如1小时可能有10-20条）。

#### 1.4.2. 适用场景

- 时序数据（如系统日志、用户行为轨迹、交易流水）。
- 用户对“页码精确性”要求低，更关注“时间范围”（如“查看昨天的日志”）。
- 数据按时间天然分区（如按天分区的表）。

#### 1.4.3. 案例示例（日志表分页）

`system_log_tbl`（千万级系统日志）按 `create_time DESC` 排序，需求：分页查看日志，每页对应“1小时内的数据”。

```sql
-- 第1页：最近1小时
SELECT *
FROM system_log_tbl
WHERE create_time >= DATEADD(HOUR, -1, GETDATE())
ORDER BY create_time DESC;

-- 第2页：1-2小时前
SELECT *
FROM system_log_tbl
WHERE create_time BETWEEN DATEADD(HOUR, -2, GETDATE()) 
                      AND DATEADD(HOUR, -1, GETDATE())
ORDER BY create_time DESC;
```

**优势**：时间范围查询可利用 `create_time` 索引快速定位，无需计算偏移量，千万级数据查询效率与“查询单条数据”接近。

#### 1.4.4. 优缺点
- **优点**：性能极致，完全基于索引范围扫描；逻辑简单，无需处理页码计算。
- **缺点**：仅适用于时间有序数据；每页行数不固定（可能因高峰期数据量激增而变多）；不支持“跳至第N页”（需转化为“N小时前”）。

### 1.5. 总结：千万级数据分页的选择策略

千万级数据分页的核心是**“避免扫描无关数据”**，上述方法各有侧重，选择时需结合场景：

**最终建议**：

- 若数据有唯一有序字段（如自增主键），优先用**游标定位分页**（平衡性能与功能）；
- 若数据量超亿级或分布式存储，必选**分片分页**；
- 若为时序数据，直接用**时间范围分页**（最简单高效）；
- 若需保留“跳页”功能且查询字段固定，用**索引覆盖分页**优化。