印刻数据库

外观

OceanBase 分区表设计 ​

分区类型 ​

1. 范围分区 ​

范围分区是最常用的分区类型，根据列值的范围将数据分配到不同的分区。适用于按时间、ID 等连续值进行分区的场景。

sql
-- 按时间范围分区示例
CREATE TABLE range_partition_table (
    id INT PRIMARY KEY,
    create_time DATETIME,
    name VARCHAR(50)
) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) (
    PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')),
    PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 按 ID 范围分区示例
CREATE TABLE id_range_partition (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (1000000),
    PARTITION p2 VALUES LESS THAN (2000000),
    PARTITION p3 VALUES LESS THAN (3000000),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

2. 哈希分区 ​

哈希分区根据列值的哈希值将数据均匀分配到不同的分区，适用于数据分布比较均匀、查询模式比较随机的场景。

sql
-- 按 ID 哈希分区示例
CREATE TABLE hash_partition_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) PARTITION BY HASH (id) PARTITIONS 8;

-- 按用户名哈希分区示例
CREATE TABLE user_hash_partition (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY HASH (username) PARTITIONS 16;

3. 列表分区 ​

列表分区根据列值的预定义列表将数据分配到不同的分区，适用于列值是离散值的场景。

sql
-- 按地区列表分区示例
CREATE TABLE list_partition_table (
    id INT PRIMARY KEY,
    region VARCHAR(20),
    name VARCHAR(50)
) PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北', '山西', '内蒙古'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江', '安徽', '福建', '江西', '山东'),
    PARTITION p_south VALUES IN ('广东', '广西', '海南'),
    PARTITION p_west VALUES IN ('重庆', '四川', '贵州', '云南', '西藏', '陕西', '甘肃', '青海', '宁夏', '新疆'),
    PARTITION p_other VALUES IN ('香港', '澳门', '台湾')
);

-- 按状态列表分区示例
CREATE TABLE status_list_partition (
    order_id INT PRIMARY KEY,
    order_status VARCHAR(20),
    order_time DATETIME
) PARTITION BY LIST (order_status) (
    PARTITION p_pending VALUES IN ('pending'),
    PARTITION p_processing VALUES IN ('processing'),
    PARTITION p_completed VALUES IN ('completed'),
    PARTITION p_cancelled VALUES IN ('cancelled'),
    PARTITION p_failed VALUES IN ('failed')
);

4. 复合分区 ​

复合分区是结合多种分区类型的分区方式，常见的复合分区包括：

  • 范围-哈希复合分区：先按范围分区，再按哈希分区
  • 范围-列表复合分区：先按范围分区，再按列表分区
  • 哈希-范围复合分区：先按哈希分区，再按范围分区
sql
-- 范围-哈希复合分区示例
CREATE TABLE range_hash_partition (
    id INT PRIMARY KEY,
    create_time DATETIME,
    region VARCHAR(20),
    name VARCHAR(50)
) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) SUBPARTITION BY HASH (region) SUBPARTITIONS 4 (
    PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')),
    PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 范围-列表复合分区示例
CREATE TABLE range_list_partition (
    id INT PRIMARY KEY,
    create_time DATETIME,
    status VARCHAR(20),
    name VARCHAR(50)
) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) SUBPARTITION BY LIST (status) (
    PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')) (
        SUBPARTITION p202401_active VALUES IN ('active'),
        SUBPARTITION p202401_inactive VALUES IN ('inactive'),
        SUBPARTITION p202401_deleted VALUES IN ('deleted')
    ),
    PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')) (
        SUBPARTITION p202402_active VALUES IN ('active'),
        SUBPARTITION p202402_inactive VALUES IN ('inactive'),
        SUBPARTITION p202402_deleted VALUES IN ('deleted')
    )
);

分区键选择 ​

1. 分区键选择原则 ​

  • 查询相关性：分区键应与查询条件相关，以便查询时只访问相关分区
  • 数据分布均匀性：分区键应使数据在各个分区中分布均匀，避免数据倾斜
  • 更新频率：分区键应选择更新频率低的列，避免分区键更新导致的数据迁移
  • 业务含义：分区键应具有明确的业务含义，便于数据管理和维护
  • 基数：分区键的基数应适中，不宜过高或过低

2. 常见分区键选择 ​

业务场景推荐分区键分区类型优势
时序数据时间列（如 create_time）范围分区查询时可按时间范围过滤分区，提高查询性能
用户数据用户 ID、用户名哈希分区数据分布均匀，支持水平扩展
地域数据地区列列表分区便于按地区管理和查询数据
订单数据订单 ID、订单时间范围分区/复合分区支持按订单 ID 或时间查询，提高查询性能
日志数据日志时间、日志类型范围-列表复合分区便于按时间和类型管理日志数据

3. 分区键选择陷阱 ​

  • 避免使用低基数列：低基数列会导致数据分布不均，影响查询性能
  • 避免使用频繁更新的列：频繁更新分区键会导致数据在分区间迁移，影响性能
  • 避免使用 NULL 值较多的列：NULL 值会导致数据集中在一个分区，影响数据分布均匀性
  • 避免使用过长的字符串列：过长的字符串列会增加哈希计算开销，影响性能

分区表设计最佳实践 ​

1. 分区数量设计 ​

  • 合理控制分区数量：每个表的分区数量不宜过多，推荐控制在 1000 个以内
  • 考虑节点数量：分区数量应与节点数量相匹配，便于数据均匀分布
  • 考虑查询性能：过多的分区会增加查询计划生成时间，影响查询性能
  • 考虑维护成本：过多的分区会增加维护成本，如备份恢复时间

2. 分区表命名规范 ​

  • 分区表命名：分区表名应体现分区类型和业务含义，如 order_range_partition
  • 分区命名：分区名应体现分区范围或值，如 p202401（范围分区）、p_north（列表分区）
  • 子分区命名：子分区名应体现父分区和子分区的特征，如 p202401_active（复合分区）

3. 分区表索引设计 ​

  • 全局索引：全局索引适用于跨分区查询，索引数据分布在所有分区
  • 本地索引：本地索引适用于单分区查询，索引数据与分区数据存储在一起
  • 索引分区键：本地索引的分区键应与表的分区键一致
  • 唯一索引：唯一索引必须包含表的分区键
sql
-- 全局索引示例
CREATE GLOBAL INDEX idx_name ON range_partition_table (name);

-- 本地索引示例
CREATE LOCAL INDEX idx_create_time ON range_partition_table (create_time);

-- 唯一索引示例（必须包含分区键）
CREATE UNIQUE INDEX idx_id_create_time ON range_partition_table (id, UNIX_TIMESTAMP(create_time));

4. 分区表查询优化 ​

  • 使用分区裁剪：在查询条件中包含分区键，以便只访问相关分区
  • 避免全表扫描：通过合理的索引设计和查询条件，避免全表扫描
  • 使用分区并行查询：对于大规模查询，启用并行查询提高性能
  • 优化 join 操作：当 join 的表有相同的分区键时，使用分区 wise join 提高性能
sql
-- 分区裁剪示例
SELECT * FROM range_partition_table WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31';

-- 分区并行查询示例
ALTER SESSION SET parallel_degree_policy = 'auto';
SELECT /*+ parallel(8) */ * FROM range_partition_table WHERE create_time < '2024-02-01';

-- 分区 wise join 示例
SELECT t1.*, t2.* 
FROM range_partition_table t1 
JOIN range_partition_table t2 ON t1.id = t2.id AND t1.create_time = t2.create_time 
WHERE t1.create_time BETWEEN '2024-01-01' AND '2024-01-31';

5. 分区表维护 ​

  • 定期清理历史数据：通过删除或合并旧分区，清理历史数据
  • 定期合并分区：对于范围分区，定期合并小分区，提高查询性能
  • 定期检查分区数据分布：检查数据分布是否均匀，避免数据倾斜
  • 备份分区数据：支持按分区备份，提高备份恢复效率
sql
-- 删除旧分区示例
ALTER TABLE range_partition_table DROP PARTITION p202401;

-- 合并分区示例
ALTER TABLE range_partition_table COALESCE PARTITION 2;

-- 检查分区数据分布示例
SELECT table_name, partition_name, table_rows 
FROM information_schema.partitions 
WHERE table_name = 'range_partition_table' 
ORDER BY partition_name;

分区表管理操作 ​

1. 创建分区表 ​

sql
-- 创建范围分区表
CREATE TABLE partition_table (
    id INT PRIMARY KEY,
    create_time DATETIME,
    name VARCHAR(50)
) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) (
    PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

2. 添加分区 ​

sql
-- 为范围分区表添加分区
ALTER TABLE range_partition_table ADD PARTITION (
    PARTITION p202404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01 00:00:00'))
);

-- 为列表分区表添加分区
ALTER TABLE list_partition_table ADD PARTITION (
    PARTITION p_new VALUES IN ('new_region1', 'new_region2')
);

-- 为哈希分区表添加分区（增加分区数量）
ALTER TABLE hash_partition_table ADD PARTITION PARTITIONS 4;

3. 删除分区 ​

sql
-- 删除范围分区
ALTER TABLE range_partition_table DROP PARTITION p202401;

-- 删除列表分区
ALTER TABLE list_partition_table DROP PARTITION p_other;

-- 删除哈希分区（减少分区数量）
ALTER TABLE hash_partition_table COALESCE PARTITION 2;

4. 拆分分区 ​

sql
-- 拆分范围分区
ALTER TABLE range_partition_table SPLIT PARTITION pmax AT (UNIX_TIMESTAMP('2024-06-01 00:00:00')) INTO (
    PARTITION p202405,
    PARTITION pmax
);

-- 拆分列表分区
ALTER TABLE list_partition_table SPLIT PARTITION p_north INTO (
    PARTITION p_north1 VALUES IN ('北京', '天津', '河北'),
    PARTITION p_north2 VALUES IN ('山西', '内蒙古')
);

5. 合并分区 ​

sql
-- 合并范围分区
ALTER TABLE range_partition_table MERGE PARTITIONS p202401, p202402 INTO PARTITION p2024q1;

-- 合并列表分区
ALTER TABLE list_partition_table MERGE PARTITIONS p_north1, p_north2 INTO PARTITION p_north;

6. 重命名分区 ​

sql
-- 重命名范围分区
ALTER TABLE range_partition_table RENAME PARTITION p2024q1 TO p202401_02;

-- 重命名列表分区
ALTER TABLE list_partition_table RENAME PARTITION p_north TO p_northern;

分区表性能优化 ​

1. 分区裁剪优化 ​

  • 确保查询条件包含分区键：查询条件中包含分区键，以便查询时只访问相关分区
  • 使用绑定变量：使用绑定变量可以提高查询计划缓存命中率，有利于分区裁剪
  • 避免使用函数包装分区键：函数包装分区键会导致分区裁剪失效，如 DATE_FORMAT(create_time, '%Y-%m') = '2024-01'
  • 使用明确的分区键值：使用明确的分区键值，避免使用范围过大的查询条件

2. 数据分布优化 ​

  • 选择合适的分区键：选择使数据分布均匀的分区键
  • 定期检查数据分布：定期检查各分区的数据分布情况，发现数据倾斜及时调整
  • 使用分区键直方图：收集分区键的直方图统计信息，优化查询计划
  • 调整分区数量：根据数据增长情况，调整分区数量，保持数据分布均匀

3. 索引优化 ​

  • 选择合适的索引类型：根据查询模式选择全局索引或本地索引
  • 优化索引列顺序：将查询频率高的列放在索引前面
  • 避免创建过多索引：过多的索引会影响写入性能
  • 定期重建索引：定期重建索引，提高索引性能

4. 写入性能优化 ​

  • 批量写入：使用批量写入减少分区锁竞争，提高写入性能
  • 避免热点分区：通过合理的分区键设计，避免写入时的热点分区
  • 调整分区事务隔离级别：根据业务需求调整分区事务隔离级别，提高并发写入性能
  • 使用并行写入：对于大规模数据导入，使用并行写入提高性能

常见问题（FAQ） ​

Q1: 如何选择合适的分区类型？ ​

A1: 选择合适的分区类型需要考虑业务场景和查询模式：

  • 对于时序数据，推荐使用范围分区
  • 对于用户数据，推荐使用哈希分区
  • 对于地域数据，推荐使用列表分区
  • 对于复杂的业务场景，推荐使用复合分区

Q2: 分区表的分区数量越多越好吗？ ​

A2: 不是。分区数量过多会导致以下问题：

  • 查询计划生成时间增加，影响查询性能
  • 元数据管理复杂度增加
  • 备份恢复时间增加
  • 内存占用增加

推荐每个表的分区数量控制在 1000 个以内。

Q3: 如何处理分区表的数据倾斜问题？ ​

A3: 处理分区表的数据倾斜问题可以采取以下措施：

  • 重新选择分区键，使数据分布均匀
  • 调整分区数量，增加分区粒度
  • 使用复合分区，结合多种分区类型
  • 对热点数据进行特殊处理，如拆分热点分区

Q4: 分区表可以修改分区键吗？ ​

A4: 不可以。分区键一旦确定，无法直接修改。如果需要修改分区键，需要重新创建表并迁移数据。

Q5: 如何查询分区表的分区信息？ ​

A5: 可以通过以下方式查询分区表的分区信息：

sql
-- 查询分区表的分区信息
SELECT * FROM information_schema.partitions WHERE table_name = 'partition_table';

-- 查询分区表的分区键信息
SELECT * FROM information_schema.key_column_usage WHERE table_name = 'partition_table' AND constraint_name = 'PRIMARY';

-- 查询 OceanBase 特有的分区信息
SELECT * FROM oceanbase.__all_virtual_partition_info WHERE table_name = 'partition_table';

Q6: 分区表的备份和恢复与普通表有什么区别？ ​

A6: 分区表的备份和恢复与普通表的主要区别：

  • 分区表支持按分区备份和恢复，可以只备份或恢复特定分区
  • 分区表的备份和恢复速度更快，因为可以并行处理多个分区
  • 分区表的备份和恢复灵活性更高，可以根据业务需求选择备份或恢复的分区

Q7: 如何将普通表转换为分区表？ ​

A7: 将普通表转换为分区表可以采取以下方法：

  1. 创建一个新的分区表，结构与原表相同
  2. 将原表的数据导入到新的分区表
  3. 重命名原表和新表，完成转换
sql
-- 创建分区表
CREATE TABLE new_partition_table LIKE old_table;
ALTER TABLE new_partition_table PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (1000000),
    PARTITION p2 VALUES LESS THAN (2000000),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 导入数据
INSERT INTO new_partition_table SELECT * FROM old_table;

-- 重命名表
RENAME TABLE old_table TO old_table_backup, new_partition_table TO old_table;

Q8: 分区表的性能一定比普通表好吗？ ​

A8: 不一定。分区表的性能取决于以下因素：

  • 分区键设计是否合理
  • 查询条件是否包含分区键
  • 数据分布是否均匀
  • 索引设计是否优化

如果分区表设计不合理，性能可能比普通表更差。因此，在设计分区表时，需要根据业务场景和查询模式进行合理设计。