SQL Server 大数据量分库分页实现方案

[TOC]

## 1. 设计分库分表

在大规模数据场景中，分库分表是解决单库单表性能瓶颈的核心方案。下面结合 **C# 后台开发**详细说明分片的实现逻辑、分库分表的定位方式，以及 SQL Server 中 `$PARTITION` 函数的作用与定义方法。

### 1.1. 分片（分库分表）的核心实现逻辑

分片（Sharding）的本质是将大表/大库按规则拆分为多个小表/小库（称为“分片”），核心是**“分片键”**和**“分片策略”**。
- **分片键**：用于拆分数据的字段（如 `id`、`user_id`、`create_time`），需具备唯一性或规律性（如自增、哈希均匀）。
- **分片策略**：按分片键拆分的规则，常见有**范围分片**（如 `id 1-100万` 为分片1）、**哈希分片**（如 `user_id % 10` 分为10片）、**时间分片**（如按月份拆分）。

#### 1.1.1. 分库分表的定位逻辑（C# 实现）

在 C# 后台中，需通过代码**根据分片键计算目标分片**（即“该数据属于哪个库、哪个表”），再路由到对应的分片执行操作。

以**“用户表按 user_id 哈希分10个库，每个库分5个表”**为例（共50个分片），步骤如下：

##### 1.1.1.1. 定义分片策略配置

先在配置文件（如 `appsettings.json`）中定义分片规则，方便动态调整：
```json
// appsettings.json
"ShardingConfig": {
  "DbCount": 10,        // 分库数量
  "TableCountPerDb": 5, // 每个库的分表数量
  "ShardingKey": "user_id" // 分片键
}
```

##### 1.1.1.2. 封装分片计算工具类

在 C# 中创建 `ShardingManager` 类，负责根据分片键计算目标库和表：
```csharp
public class ShardingManager
{
    private readonly int _dbCount;
    private readonly int _tableCountPerDb;

// 从配置文件加载分片参数
    public ShardingManager(IConfiguration config)
    {
        _dbCount = config.GetValue<int>("ShardingConfig:DbCount");
        _tableCountPerDb = config.GetValue<int>("ShardingConfig:TableCountPerDb");
    }

/// <summary>
    /// 根据user_id计算目标分库和分表
    /// </summary>
    /// <param name="userId">分片键（user_id）</param>
    /// <returns>(分库索引, 分表名)</returns>
    public (int DbIndex, string TableName) GetShardingInfo(long userId)
    {
        // 1. 计算分库索引（哈希取模）
        int dbIndex = (int)(userId % _dbCount);

// 2. 计算分表索引（在当前库内的表索引）
        int totalTableCount = _dbCount * _tableCountPerDb; // 总表数
        int tableIndex = (int)(userId % totalTableCount / _dbCount);

// 3. 分表命名规则：原始表名_表索引（如 user_0, user_1...）
        string tableName = $"user_{tableIndex}";

return (dbIndex, tableName);
    }
}
```

##### 1.1.1.3. 在数据访问层（DAL）中路由到目标分片

使用计算出的分库索引和分表名，动态生成连接字符串和 SQL，访问目标分片：
```csharp
public class UserRepository
{
    private readonly ShardingManager _shardingManager;
    private readonly IConfiguration _config;

public UserRepository(ShardingManager shardingManager, IConfiguration config)
    {
        _shardingManager = shardingManager;
        _config = config;
    }

/// <summary>
    /// 根据user_id查询用户（自动路由到目标分片）
    /// </summary>
    public async Task<User> GetUserByIdAsync(long userId)
    {
        // 1. 计算目标分片
        var (dbIndex, tableName) = _shardingManager.GetShardingInfo(userId);

// 2. 获取目标分库的连接字符串（配置中定义了每个库的连接）
        string connStr = _config.GetConnectionString($"ShardDb_{dbIndex}");

// 3. 动态生成SQL（访问目标分表）
        string sql = $"SELECT * FROM {tableName} WHERE user_id = @UserId";

// 4. 执行查询（使用Dapper/EF Core等ORM）
        using (var conn = new SqlConnection(connStr))
        {
            return await conn.QueryFirstOrDefaultAsync<User>(sql, new { UserId = userId });
        }
    }
}
```

##### 1.1.1.4. 分页查询的分片路由（核心场景）

对于千万级数据分页，需先定位目标分片，再在分片内执行分页，避免跨分片扫描：
```csharp
/// <summary>
/// 分页查询用户（按user_id升序，每页20条）
/// </summary>
public async Task<List<User>> GetUsersByPageAsync(int pageNum, int pageSize)
{
    // 注意：哈希分片下分页需聚合所有分片结果（复杂），此处以范围分片为例
    // 范围分片：user_id 1-200万在分片0，200万-400万在分片1...
    long startId = (pageNum - 1) * (long)pageSize;
    long endId = pageNum * (long)pageSize;

// 1. 计算startId和endId所在的分片范围（假设每个分片包含200万数据）
    int startDbIndex = (int)(startId / 2000000);
    int endDbIndex = (int)(endId / 2000000);

var result = new List<User>();
    // 2. 仅查询涉及的分片（避免全部分片扫描）
    for (int dbIndex = startDbIndex; dbIndex <= endDbIndex; dbIndex++)
    {
        string connStr = _config.GetConnectionString($"ShardDb_{dbIndex}");
        string sql = $@"
            SELECT * FROM user_{dbIndex} 
            WHERE user_id >= @StartId AND user_id < @EndId 
            ORDER BY user_id ASC 
            OFFSET 0 ROWS FETCH NEXT @PageSize ROWS ONLY";

using (var conn = new SqlConnection(connStr))
        {
            var users = await conn.QueryAsync<User>(sql, new
            {
                StartId = startId,
                EndId = endId,
                PageSize = pageSize
            });
            result.AddRange(users);
        }
    }

// 3. 合并分片结果并按分页截取（范围分片下可能跨分片，需二次排序）
    return result.OrderBy(u => u.UserId).Skip((pageNum - 1) * pageSize).Take(pageSize).ToList();
}
```

## 2. 分库分片计算逻辑详解

在分片计算工具类中，`GetShardingInfo` 方法的核心目标是**让数据均匀分布到各个分库分表中**，同时保证通过 `user_id` 能快速定位到唯一的库和表。下面结合具体场景（10个分库，每个分库5个分表，共50个分表），用“分步拆解+实例验证”的方式详细讲解设计逻辑。

### 2.1. 设计前提：为什么这样拆分？

先明确场景：
- 总数据量：假设千万级用户（如5000万），需要拆分到多个库和表中。
- 拆分目标：10个分库（`ShardDb_0` 到 `ShardDb_9`），每个分库包含5个分表（`user_0` 到 `user_4`），共 `10×5=50` 个分表。
- 核心要求：
  1. 数据均匀分布（每个分表约100万数据，避免某表过大）；
  2. 通过 `user_id` 能唯一确定目标库和表（查询时无需扫描全部分片）。

### 2.2. 分库索引计算：如何确定在哪个库？

分库的核心是**用 `user_id` 对“总库数”取模**，确保数据均匀分散到10个库中。

#### 2.2.1. 计算逻辑：
```csharp
int dbIndex = (int)(userId % _dbCount);  // _dbCount=10（总库数）
```

#### 2.2.2. 原理：

- 取模运算（`%`）的结果范围是 `0 到 (除数-1)`，因此 `user_id % 10` 的结果只能是 `0-9`，正好对应10个分库的索引（`ShardDb_0` 到 `ShardDb_9`）。
- 哈希取模能保证数据均匀分布（前提是 `user_id` 是随机或自增的，无明显规律）。

#### 2.2.3. 实例验证：

假设 `user_id` 分别为 `123456`、`789012`、`999999`：
- `123456 % 10 = 6` → 对应 `ShardDb_6`
- `789012 % 10 = 2` → 对应 `ShardDb_2`
- `999999 % 10 = 9` → 对应 `ShardDb_9`
结果均在 `0-9` 之间，符合分库索引范围。

### 2.3. 分表索引计算：如何确定在当前库的哪个表？

分表的核心是**先确定总表数，再用 `user_id` 对“总表数”取模，最后映射到当前库内的表索引**。

#### 2.3.1. 计算逻辑（分步拆解）：

1. 计算总表数：`总表数 = 分库数 × 每个库的表数 = 10 × 5 = 50`
```csharp
int totalTableCount = _dbCount * _tableCountPerDb;  // 10×5=50
```

2. 计算全局表索引：用 `user_id` 对总表数取模（结果范围 `0-49`，对应50个分表）
```csharp
int globalTableIndex = (int)(userId % totalTableCount);  // 范围0-49
```

3. 映射到当前库内的表索引：全局表索引除以分库数（10），得到当前库内的表索引（范围 `0-4`，对应每个库的5个表）
```csharp
int tableIndex = globalTableIndex / _dbCount;  // 0-49 ÷10 → 0-4
```

#### 2.3.2. 原理：

- 步骤1：总表数是50，确保 `user_id` 能均匀映射到50个表中（避免表数据不均）。
- 步骤2：`user_id % 50` 得到全局表索引（0-49），每个索引对应唯一的分表。
- 步骤3：因为每个库有5个表，所以将全局表索引按“分库数”（10）分组，每组5个索引（如 `0-4` 对应表0，`5-9` 对应表1…），除以10后得到当前库内的表索引（0-4）。

#### 2.3.3. 实例验证：

以 `user_id=123456` 为例（已确定分库索引为6）：
1. 总表数 `totalTableCount=50`
2. 全局表索引：`123456 % 50 = 123456 - (50×2469) = 123456-123450=6` → `globalTableIndex=6`
3. 当前库内表索引：`6 ÷ 10 = 0`（整数除法，舍去小数） → `tableIndex=0`
→ 最终表名为 `user_0`，即 `ShardDb_6` 库中的 `user_0` 表。

再验证一个例子：`user_id=789012`（分库索引=2）：
1. 全局表索引：`789012 % 50 = 789012 - (50×15780)=789012-789000=12` → `globalTableIndex=12`
2. 当前库内表索引：`12 ÷10=1` → `tableIndex=1`
→ 对应 `ShardDb_2` 库中的 `user_1` 表。

### 2.4. 为什么这样设计？核心优势解析

1. **数据均匀分布**：
   哈希取模确保 `user_id` 随机分散到50个分表中，每个分表数据量接近（5000万 ÷50=100万/表），避免单表过大。

2. **定位唯一且高效**：
   给定 `user_id` 后，通过2次取模+1次除法即可定位目标库表，计算复杂度为 O(1)，无需额外查询，适合高频访问。

3. **分库与分表解耦**：
   分库和分表的计算逻辑独立，后续可单独调整分库数或分表数（只需同步修改配置）。例如：
   - 若分库数改为20，分表数改为3，总表数=60，计算逻辑只需调整参数，无需修改核心算法。

4. **表名规则统一**：
   所有分表命名为 `user_0` 到 `user_4`，同一库内表名不重复，不同库内表名可复用（如 `ShardDb_0.user_0` 和 `ShardDb_1.user_0` 是不同表），方便维护。

### 2.5. 可能的疑问与扩展

#### 2.5.1. 如果 `user_id` 是字符串（如UUID），如何计算？

答：需先将字符串转为哈希值（如通过 `GetHashCode()` 或MD5哈希），再对哈希值取模。例如：

```csharp
// 字符串user_id转为哈希值
int hash = userIdStr.GetHashCode();
// 取绝对值（避免负哈希）
long hashLong = Math.Abs((long)hash);
// 后续计算同数字user_id
int dbIndex = (int)(hashLong % _dbCount);
```

#### 2.5.2. 如何保证新增分库分表时数据不迁移？

答：可采用“一致性哈希”算法（而非简单取模），但实现复杂。简单场景下，建议初期规划足够的分库分表数量（如按未来3年数据量设计），避免中途扩容。

#### 2.5.3. 分表索引为什么不直接用 `user_id % 每个库的表数`？

答：若直接用 `user_id % 5`，会导致所有库的表索引分布完全一致（如 `user_id=1` 在所有库的表1中），可能出现“同表索引的数据集中在某几个库”的问题，破坏均匀性。而通过“全局表索引映射”能避免这个问题。

### 2.6. 总结
`GetShardingInfo` 方法的设计核心是**“哈希取模+分层映射”**：
1. 先通过 `user_id % 分库数` 确定分库索引（0-9）；
2. 再通过 `user_id % 总表数 → 全局表索引 → 全局表索引 ÷ 分库数` 确定当前库内的表索引（0-4）；
3. 最终生成唯一的库索引和表名，确保数据均匀分布且定位高效。

这种设计简单、高效、可扩展，是分库分表场景中最常用的分片策略之一。