印刻数据库

外观

TDSQL 分区表管理 ​

分区表的优势 ​

  • 提高查询性能：只扫描相关分区
  • 便于数据管理：按分区进行数据维护
  • 优化存储：不同分区可使用不同存储策略
  • 提高可用性：单个分区故障不影响整个表

分区表的适用场景 ​

  • 大表（通常超过1000万行）
  • 有明显时间或范围特征的数据
  • 需要定期归档或删除历史数据
  • 数据访问具有明显的局部性

分区类型 ​

1. 范围分区（Range Partitioning） ​

  • 根据列值的范围进行分区
  • 最常用的分区类型
  • 适合按时间、ID等连续值分区

2. 列表分区（List Partitioning） ​

  • 根据列值的离散列表进行分区
  • 适合按地区、状态等枚举值分区

3. 哈希分区（Hash Partitioning） ​

  • 根据列值的哈希值进行分区
  • 数据分布均匀
  • 适合无法预测数据分布的场景

4. 键分区（Key Partitioning） ​

  • 基于MySQL内部哈希函数
  • 适合主键或唯一键分区

5. 子分区（Subpartitioning） ​

  • 对已分区的表进行再次分区
  • 支持复合分区策略
  • 适合复杂数据分布场景

分区表创建 ​

1. 范围分区创建 ​

sql
-- 按时间范围分区
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    sales_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(sales_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 按ID范围分区
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (100000),
    PARTITION p2 VALUES LESS THAN (200000),
    PARTITION p3 VALUES LESS THAN (300000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

2. 列表分区创建 ​

sql
-- 按地区列表分区
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    region VARCHAR(20) NOT NULL,
    status INT NOT NULL
)
PARTITION BY LIST (region) (
    PARTITION pnorth VALUES IN ('北京', '天津', '河北', '山西', '内蒙古'),
    PARTITION peast VALUES IN ('上海', '江苏', '浙江', '安徽', '福建', '江西', '山东'),
    PARTITION psouth VALUES IN ('广东', '广西', '海南'),
    PARTITION pwest VALUES IN ('重庆', '四川', '贵州', '云南', '西藏')
);

3. 哈希分区创建 ​

sql
-- 按哈希分区
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at DATETIME NOT NULL
)
PARTITION BY HASH (id) PARTITIONS 8;

4. 子分区创建 ​

sql
-- 范围-哈希子分区
CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    log_date DATE NOT NULL,
    log_level VARCHAR(10) NOT NULL,
    message TEXT NOT NULL
)
PARTITION BY RANGE (YEAR(log_date))
SUBPARTITION BY HASH (MONTH(log_date))
SUBPARTITIONS 12 (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

分区表管理 ​

1. 分区查询 ​

sql
-- 查看表的分区信息
SHOW CREATE TABLE sales;

-- 查看分区详情
SELECT * FROM information_schema.partitions 
WHERE table_schema = 'database_name' AND table_name = 'sales';

-- 查询特定分区
SELECT * FROM sales PARTITION (p2024);

-- 查看分区使用情况
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sales_date >= '2024-01-01';

2. 分区维护 ​

添加分区 ​

sql
-- 为范围分区添加新分区
ALTER TABLE sales ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);

-- 为列表分区添加新分区
ALTER TABLE customers ADD PARTITION (
    PARTITION pcentral VALUES IN ('河南', '湖北', '湖南')
);

删除分区 ​

sql
-- 删除分区（同时删除分区数据）
ALTER TABLE sales DROP PARTITION p2023;

截断分区 ​

sql
-- 清空分区数据但保留分区结构
ALTER TABLE sales TRUNCATE PARTITION p2023;

合并分区 ​

sql
-- 合并多个分区
ALTER TABLE sales REORGANIZE PARTITION p2024, p2025 INTO (
    PARTITION p2024_2025 VALUES LESS THAN (2026)
);

拆分分区 ​

sql
-- 拆分分区
ALTER TABLE sales REORGANIZE PARTITION p2024_2025 INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

3. 分区交换 ​

sql
-- 创建与分区结构相同的临时表
CREATE TABLE sales_2023_temp LIKE sales;

-- 插入数据到临时表
INSERT INTO sales_2023_temp SELECT * FROM sales WHERE sales_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 交换分区（快速归档数据）
ALTER TABLE sales EXCHANGE PARTITION p2023 WITH TABLE sales_2023_temp;

分区表最佳实践 ​

1. 分区键选择 ​

  • 选择经常用于查询过滤的列
  • 选择具有明显范围或列表特征的列
  • 避免使用更新频繁的列
  • 考虑数据分布的均匀性

2. 分区数量规划 ​

  • 避免过多分区（建议不超过100个）
  • 根据服务器资源和查询需求调整
  • 考虑管理成本

3. 分区命名规范 ​

  • 范围分区：p+年份/月份（如p202401）
  • 列表分区：p+区域/状态（如pnorth）
  • 哈希分区：ph+序号（如ph0, ph1）

4. 性能优化 ​

  • 确保查询包含分区键
  • 避免跨分区查询
  • 合理设置分区大小（建议每个分区10-50GB）
  • 使用分区剪枝优化查询

5. 数据归档策略 ​

  • 定期将旧分区数据归档
  • 使用分区交换快速迁移数据
  • 建立完善的数据生命周期管理

分区表常见问题 ​

1. 分区表与索引 ​

  • 分区表的索引会自动分区
  • 主键和唯一键必须包含分区键
  • 全局索引和本地索引的选择

2. 分区表与备份 ​

  • 可以按分区进行备份
  • 增量备份需要考虑分区边界
  • 恢复时可以单独恢复特定分区

3. 分区表与性能 ​

  • 不恰当的分区策略会降低性能
  • 过多分区会增加优化器负担
  • 分区剪枝失效会导致全表扫描

常见问题（FAQ） ​

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

A1: 选择分区类型应根据数据特征和查询模式：

  • 按时间或连续值：使用范围分区
  • 按离散枚举值：使用列表分区
  • 数据分布均匀性要求高：使用哈希分区
  • 复杂数据分布：使用子分区

Q2: 分区表的主键有什么限制？ ​

A2: 分区表的主键和唯一键必须包含分区键列。这是因为MySQL需要确保主键唯一性，必须能确定数据属于哪个分区。

Q3: 如何判断分区表是否被正确使用？ ​

A3: 可以通过以下方式判断：

  • 使用EXPLAIN PARTITIONS查看查询是否使用了分区剪枝
  • 监控查询性能是否有所提升
  • 检查分区数据分布是否均匀
  • 观察维护操作是否更高效

Q4: 如何处理分区表的性能问题？ ​

A4: 处理分区表性能问题可以采取以下措施：

  • 调整分区策略，确保查询能使用分区剪枝
  • 优化查询语句，确保包含分区键
  • 调整分区数量和大小
  • 考虑使用更合适的分区类型

Q5: 分区表如何进行数据迁移？ ​

A5: 分区表数据迁移可以采用以下方法：

  • 使用分区交换快速迁移数据
  • 按分区导出导入数据
  • 使用第三方工具（如pt-archiver）进行归档

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

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

  • 使用CREATE TABLE ... PARTITION BY ... AS SELECT创建新表
  • 使用ALTER TABLE ... PARTITION BY ...直接转换（MySQL 5.6+）
  • 先创建分区表，然后导入数据

Q7: 分区表对应用层有什么影响？ ​

A7: 分区表对应用层的影响很小，应用程序可以像使用普通表一样使用分区表。但需要注意：

  • 尽量在查询中包含分区键
  • 了解分区边界，避免跨分区查询
  • 考虑分区维护操作对应用的影响