📖 数据密集型设计

数据分区策略与实践

深入探讨数据分区策略与实现方法

一、数据分区概述

数据分区是将大表拆分为多个小表的技术，能够提高查询性能、管理数据生命周期、均衡负载。数据分区是构建可扩展数据库架构的关键技术。

二、分区策略对比

2.1 分区策略对比表

策略 描述 优点 缺点 适用场景
范围分区 按范围划分数据 范围查询高效、数据冷热分离 热点数据问题 时间序列数据、日志
哈希分区 按哈希值划分数据 数据均匀分布、负载均衡 范围查询低效 用户数据、订单数据
列表分区 按列表值划分数据 灵活控制、业务对齐 维护复杂 地区数据、状态数据
复合分区 多种策略组合 兼顾各种需求 实现复杂 复杂业务场景

2.2 分区策略选择

flowchart TD A[选择分区策略] --> B{数据类型} B -->|时间序列| C[范围分区] B -->|用户数据| D[哈希分区] B -->|业务分类| E[列表分区] B -->|复杂场景| F[复合分区] C --> C1[按日期分区] D --> D1[按用户ID哈希] E --> E1[按地区/状态] F --> F1[多层分区]

三、范围分区

3.1 范围分区原理

范围分区按连续的范围划分数据：

graph TD A[orders表] --> B[2024-01分区] A --> C[2024-02分区] A --> D[2024-03分区] A --> E[2024-04分区] B --> B1[1月订单数据] C --> C1[2月订单数据] D --> D1[3月订单数据] E --> E1[4月订单数据]

3.2 MySQL范围分区

-- 创建范围分区表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询特定分区
SELECT * FROM orders PARTITION (p202401);

-- 删除旧分区
ALTER TABLE orders DROP PARTITION p202401;

-- 添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01'))
);

3.3 PostgreSQL范围分区

-- 创建父表
CREATE TABLE orders (
    id INT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- 创建子表（分区）
CREATE TABLE orders_202401 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_202402 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_202403 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- 查询特定分区
SELECT * FROM orders_202401;

-- 删除分区
DROP TABLE orders_202401;

3.4 范围分区管理

public class RangePartitionManager
{
    public async Task CreateMonthlyPartition(string tableName, DateTime month)
    {
        var startDate = new DateTime(month.Year, month.Month, 1);
        var endDate = startDate.AddMonths(1);
        
        var sql = $@"
            ALTER TABLE {tableName} ADD PARTITION (
                PARTITION p{month:yyyyMM} VALUES LESS THAN (TO_DAYS('{endDate:yyyy-MM-dd}'))
            );
        ";
        
        await _dbContext.Database.ExecuteSqlRawAsync(sql);
    }
    
    public async Task DropOldPartitions(string tableName, int monthsToKeep)
    {
        var cutoffDate = DateTime.Now.AddMonths(-monthsToKeep);
        
        var partitions = await GetPartitions(tableName);
        
        foreach (var partition in partitions)
        {
            if (partition.Date < cutoffDate)
            {
                var sql = $"ALTER TABLE {tableName} DROP PARTITION {partition.Name};";
                await _dbContext.Database.ExecuteSqlRawAsync(sql);
            }
        }
    }
    
    private async Task<List<PartitionInfo>> GetPartitions(string tableName)
    {
        var sql = "SHOW PARTITIONS FROM {tableName};";
        return await _dbContext.QueryAsync<PartitionInfo>(sql);
    }
}

public class PartitionInfo
{
    public string Name { get; set; }
    public DateTime Date { get; set; }
}

四、哈希分区

4.1 哈希分区原理

哈希分区按哈希值均匀分布数据：

graph TD A[用户数据] --> B{哈希(user_id)} B -->|0| C[分区0] B -->|1| D[分区1] B -->|2| E[分区2] B -->|3| F[分区3] C --> C1[user_id: 0,4,8...] D --> D1[user_id: 1,5,9...] E --> E1[user_id: 2,6,10...] F --> F1[user_id: 3,7,11...]

4.2 MySQL哈希分区

-- 创建哈希分区表
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    department_id INT
)
PARTITION BY HASH(id)
PARTITIONS 8;

-- 按列哈希分区
CREATE TABLE orders (
    id INT,
    user_id INT,
    total_amount DECIMAL(10,2)
)
PARTITION BY HASH(user_id)
PARTITIONS 16;

-- 线性哈希分区（更均匀）
CREATE TABLE logs (
    id INT,
    message TEXT,
    created_at DATETIME
)
PARTITION BY LINEAR HASH(id)
PARTITIONS 32;

4.3 自定义哈希分区

public class HashPartitionService
{
    private readonly int _partitionCount;
    
    public HashPartitionService(int partitionCount)
    {
        _partitionCount = partitionCount;
    }
    
    public int GetPartitionId(int userId)
    {
        return Math.Abs(userId.GetHashCode()) % _partitionCount;
    }
    
    public int GetPartitionId(string key)
    {
        int hash = 0;
        foreach (char c in key)
        {
            hash = (hash * 31) + c;
        }
        return Math.Abs(hash) % _partitionCount;
    }
    
    public string GetPartitionName(int partitionId)
    {
        return $"p{partitionId:D2}";
    }
    
    public string GetTableName(string baseTableName, int userId)
    {
        var partitionId = GetPartitionId(userId);
        return $"{baseTableName}_{partitionId:D2}";
    }
}

五、列表分区

5.1 列表分区原理

列表分区按离散的值列表划分数据：

graph TD A[订单数据] --> B{地区} B -->|北京,天津,河北| C[华北分区] B -->|上海,江苏,浙江| D[华东分区] B -->|广东,广西,海南| E[华南分区] B -->|其他| F[其他分区]

5.2 MySQL列表分区

-- 创建列表分区表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    region VARCHAR(20),
    total_amount DECIMAL(10,2)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_south VALUES IN ('广东', '广西', '海南'),
    PARTITION p_other VALUES IN ('其他')
);

-- 添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p_west VALUES IN ('四川', '重庆', '云南')
);

-- 修改分区
ALTER TABLE orders REORGANIZE PARTITION p_other INTO (
    PARTITION p_west VALUES IN ('四川', '重庆', '云南'),
    PARTITION p_other VALUES IN ('其他')
);

5.3 PostgreSQL列表分区

-- 创建父表
CREATE TABLE orders (
    id INT,
    user_id INT,
    region VARCHAR(20),
    total_amount DECIMAL(10,2)
) PARTITION BY LIST (region);

-- 创建子表
CREATE TABLE orders_north PARTITION OF orders
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE orders_east PARTITION OF orders
    FOR VALUES IN ('上海', '江苏', '浙江');

CREATE TABLE orders_south PARTITION OF orders
    FOR VALUES IN ('广东', '广西', '海南');

-- 插入数据（自动路由到对应分区）
INSERT INTO orders VALUES (1, 100, '北京', 100.00);
INSERT INTO orders VALUES (2, 101, '上海', 200.00);

六、复合分区

6.1 复合分区原理

复合分区组合多种分区策略：

graph TD A[订单数据] --> B[范围分区: 日期] B --> C[2024-01] B --> D[2024-02] C --> E[哈希分区: 用户ID] D --> F[哈希分区: 用户ID] E --> E1[分区0: 用户0-3] E --> E2[分区1: 用户4-7] F --> F1[分区0: 用户0-3] F --> F2[分区1: 用户4-7]

6.2 MySQL复合分区

-- 范围+哈希复合分区
CREATE TABLE orders (
    id INT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
)
PARTITION BY RANGE (TO_DAYS(order_date))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01'))
);

-- 范围+列表复合分区
CREATE TABLE logs (
    id INT,
    region VARCHAR(20),
    log_date DATE,
    message TEXT
)
PARTITION BY RANGE (TO_DAYS(log_date))
SUBPARTITION BY LIST COLUMNS(region) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')) (
        SUBPARTITION p202401_north VALUES IN ('北京', '天津'),
        SUBPARTITION p202401_east VALUES IN ('上海', '江苏')
    ),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')) (
        SUBPARTITION p202402_north VALUES IN ('北京', '天津'),
        SUBPARTITION p202402_east VALUES IN ('上海', '江苏')
    )
);

6.3 PostgreSQL复合分区

-- 创建多级分区
CREATE TABLE orders (
    id INT,
    user_id INT,
    order_date DATE,
    region VARCHAR(20),
    total_amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- 创建二级分区
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (region);

-- 创建三级分区
CREATE TABLE orders_2024_north PARTITION OF orders_2024
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE orders_2024_east PARTITION OF orders_2024
    FOR VALUES IN ('上海', '江苏', '浙江');

七、分区最佳实践

7.1 分区键选择

场景 推荐分区键 分区策略
日志表 时间戳 范围分区
用户表 用户ID 哈希分区
订单表 日期+用户ID 复合分区
地区数据 地区代码 列表分区

7.2 分区数量

  • 范围分区：按时间周期（月/周）创建
  • 哈希分区：根据数据量和服务器数量确定
  • 列表分区：根据业务分类数量确定
  • 复合分区：外层粗粒度，内层细粒度

7.3 分区管理

public class PartitionManagementService
{
    public async Task ManagePartitions(string tableName, PartitionConfig config)
    {
        await CreateNewPartitions(tableName, config);
        await DropOldPartitions(tableName, config);
        await OptimizePartitions(tableName, config);
    }
    
    private async Task CreateNewPartitions(string tableName, PartitionConfig config)
    {
        var futurePartitions = CalculateFuturePartitions(config);
        
        foreach (var partition in futurePartitions)
        {
            if (!await PartitionExists(tableName, partition))
            {
                await CreatePartition(tableName, partition);
            }
        }
    }
    
    private async Task DropOldPartitions(string tableName, PartitionConfig config)
    {
        var oldPartitions = await GetOldPartitions(tableName, config);
        
        foreach (var partition in oldPartitions)
        {
            await DropPartition(tableName, partition);
        }
    }
    
    private async Task OptimizePartitions(string tableName, PartitionConfig config)
    {
        var partitions = await GetPartitions(tableName);
        
        foreach (var partition in partitions)
        {
            await AnalyzePartition(tableName, partition);
        }
    }
}

public class PartitionConfig
{
    public int FuturePartitions { get; set; } = 3;
    public int RetentionPeriodDays { get; set; } = 90;
    public PartitionType Type { get; set; }
}

public enum PartitionType { Range, Hash, List, Composite }

八、分区性能优化

8.1 查询优化

-- 好的查询：使用分区键过滤
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

-- 好的查询：使用分区键+其他条件
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND user_id = 100;

-- 差的查询：不使用分区键
SELECT * FROM orders WHERE user_id = 100;

-- 差的查询：跨多个分区
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);

8.2 索引优化

-- 在分区表上创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_total_amount ON orders(total_amount);

-- 在特定分区上创建索引
CREATE INDEX idx_orders_202401_user_id ON orders PARTITION (p202401)(user_id);

-- 删除旧分区的索引（随分区删除自动删除）

8.3 分区剪枝

-- 确保查询能够触发分区剪枝
EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

-- 检查是否只扫描了一个分区
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date = '2024-01-15';

九、分区监控

9.1 分区监控指标

public class PartitionMetrics
{
    public string TableName { get; set; }
    public int TotalPartitions { get; set; }
    public long TotalRows { get; set; }
    public Dictionary<string, PartitionInfo> PartitionDetails { get; set; }
}

public class PartitionMonitor
{
    public async Task<PartitionMetrics> GetMetrics(string tableName)
    {
        var metrics = new PartitionMetrics
        {
            TableName = tableName,
            PartitionDetails = new Dictionary<string, PartitionInfo>()
        };
        
        var partitions = await _dbContext.QueryAsync<PartitionInfo>(
            $"SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = '{tableName}'"
        );
        
        metrics.TotalPartitions = partitions.Count();
        metrics.TotalRows = partitions.Sum(p => p.Rows);
        
        foreach (var partition in partitions)
        {
            metrics.PartitionDetails[partition.Name] = partition;
        }
        
        return metrics;
    }
}

十、总结

数据分区是构建可扩展数据库架构的关键技术。范围分区适合时间序列数据，哈希分区适合均匀分布数据，列表分区适合业务分类数据，复合分区适合复杂场景。合理选择分区策略、管理分区生命周期、优化查询性能，能够构建高性能、可扩展的数据存储系统。