tidb教程FG106-TiDB分区表设计与管理生产实战

本文档详细介绍TiDB分区表设计与管理方法，包括分区类型、设计原则、创建维护、优化技巧等内容。风哥教程参考TiDB官方文档分区表指南、性能调优手册等内容，适合DBA进行TiDB分区表管理。

Part01-基础概念与理论知识

1.1 TiDB分区表概述

TiDB分区表将大表数据水平分割成多个分区，每个分区独立存储。分区表可以提高查询性能、简化数据维护、支持快速数据清理。

分区表优势：

查询性能提升：分区裁剪减少扫描数据量
数据维护简化：按分区进行备份、恢复、删除
快速数据清理：直接删除分区比DELETE快得多
并行处理：不同分区可以并行查询

1.2 分区类型详解

# TiDB支持的分区类型

# 1. Range分区
# 按范围分区，适合时间序列数据
CREATE TABLE fgedu_logs (
id BIGINT,
log_time DATETIME,
message VARCHAR(255)
) PARTITION BY RANGE (YEAR(log_time)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);

# 2. Hash分区
# 按哈希值分区，数据均匀分布
CREATE TABLE fgedu_users (
id BIGINT PRIMARY KEY,
username VARCHAR(50)
) PARTITION BY HASH(id) PARTITIONS 4;

# 3. List分区
# 按离散值分区，适合状态、类型等字段
CREATE TABLE fgedu_orders (
order_id BIGINT,
region VARCHAR(20),
amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region) (
PARTITION p_east VALUES IN (‘北京’, ‘上海’, ‘广州’),
PARTITION p_west VALUES IN (‘成都’, ‘西安’, ‘重庆’),
PARTITION p_north VALUES IN (‘哈尔滨’, ‘长春’, ‘沈阳’),
PARTITION p_south VALUES IN (‘深圳’, ‘珠海’, ‘厦门’)
);

# 4. Range Columns分区
# 支持多列范围分区
CREATE TABLE fgedu_sales (
id BIGINT,
sale_date DATE,
region VARCHAR(20)
) PARTITION BY RANGE COLUMNS(sale_date) (
PARTITION p2023_q1 VALUES LESS THAN (‘2023-04-01’),
PARTITION p2023_q2 VALUES LESS THAN (‘2023-07-01’),
PARTITION p2023_q3 VALUES LESS THAN (‘2023-10-01’),
PARTITION p2023_q4 VALUES LESS THAN (‘2024-01-01’)
);

1.3 分区裁剪原理

分区裁剪是分区表的核心优化机制。当查询条件包含分区键时，优化器可以跳过不满足条件的分区，只扫描相关分区。

风哥提示：分区裁剪需要查询条件与分区键直接匹配。如果查询条件使用函数处理分区键，可能导致裁剪失效。更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 分区设计原则

# 分区设计核心原则

# 原则1：选择合适的分区键
# – 分区键应该是查询条件中频繁使用的字段
# – 分区键应该具有较高的区分度
# – 避免频繁更新的字段作为分区键

# 原则2：控制分区数量
# – 建议单表分区数不超过128个
# – 过多分区会增加元数据管理开销
# – 过少分区无法发挥分区优势

# 原则3：预估数据增长
# – 根据数据增长趋势设计分区范围
# – 预留足够的未来分区
# – 定期添加新分区

# 原则4：考虑数据分布
# – Range分区：注意数据热点问题
# – Hash分区：数据分布均匀
# – List分区：适合离散值分布

# 原则5：结合业务场景
# – 日志数据：按时间Range分区
# – 用户数据：按ID Hash分区
# – 订单数据：按地区List分区

2.2 分区策略选择

# 分区策略选择指南

# 场景1：时间序列数据（日志、订单、交易）
# 推荐：Range分区
# 原因：便于按时间查询和清理历史数据
CREATE TABLE fgedu_transactions (
id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(10,2),
created_at TIMESTAMP
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-02-01’)),
PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-03-01’)),
PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-04-01’)),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);

# 场景2：用户数据、账户数据
# 推荐：Hash分区
# 原因：数据均匀分布，避免热点
CREATE TABLE fgedu_accounts (
account_id BIGINT PRIMARY KEY,
user_id BIGINT,
balance DECIMAL(15,2)
) PARTITION BY HASH(account_id) PARTITIONS 16;

# 场景3：地区分布数据
# 推荐：List分区
# 原因：按地区查询和统计方便
CREATE TABLE fgedu_regional_sales (
sale_id BIGINT,
region_code VARCHAR(10),
amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region_code) (
PARTITION p_north VALUES IN (‘BJ’, ‘TJ’, ‘HEB’),风哥提示：
PARTITION p_east VALUES IN (‘SH’, ‘JS’, ‘ZJ’),
PARTITION p_south VALUES IN (‘GD’, ‘FJ’, ‘HN’),
PARTITION p_west VALUES IN (‘SC’, ‘CQ’, ‘SN’)
);

# 场景4：复合场景
# 推荐：Range + Hash二级分区
CREATE TABLE fgedu_large_logs (
id BIGINT,
log_date DATE,
user_id BIGINT,
message TEXT
) PARTITION BY RANGE (YEAR(log_date))
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 4 (
PARTITION p2023 VALUES LESS THAN (2024) (
SUBPARTITION s0,
SUBPARTITION s1,
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2024 VALUES LESS THAN (2025) (
SUBPARTITION s0,
SUBPARTITION s1,
SUBPARTITION s2,
SUBPARTITION s3
)
);

2.3 分区维护规划

# 分区维护规划

# 1. 定期添加新分区
# 建议每月或每季度添加新分区

# 2. 历史分区归档
# 将过期分区数据归档到冷存储

# 3. 分区数据清理
# 直接删除过期分区，比DELETE快得多

# 4. 分区重新组织
# 合并小分区或拆分大分区

# 5. 分区统计信息更新
# 定期ANALYZE分区表

# 维护脚本示例
#!/bin/bash
# tidb-partition-maintenance.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 每月1号执行：添加新分区
echo “添加新分区…”
mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
ALTER TABLE fgedudb.fgedu_logs ADD PARTITION (
PARTITION p$(date +%Y%m) VALUES LESS THAN (UNIX_TIMESTAMP(‘$(date -d ‘+1 month’ +%Y-%m-01)’))
);
”

# 每季度执行：删除过期分区
echo “删除过期分区…”
# 保留最近2年的分区

# 每周执行：更新统计信息
echo “更新统计信息…”
mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
ANALYZE TABLE fgedudb.fgedu_logs;
“

生产环境建议：分区表需要定期维护，建议制定分区管理规范，自动化添加和清理分区。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 分区表创建实战

3.1.1 创建Range分区表

# 创建时间Range分区表

mysql> CREATE TABLE fgedu_order_logs (
-> log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
-> order_id BIGINT NOT NULL,
-> user_id BIGINT NOT NULL,
-> action VARCHAR(50) NOT NULL,
-> log_time DATETIME NOT NULL,
-> details JSON,
-> INDEX idx_order_id (order_id),
-> INDEX idx_log_time (log_time)
-> ) PARTITION BY RANGE (YEAR(log_time)) (
-> PARTITION p2022 VALUES LESS THAN (2023),
-> PARTITION p2023 VALUES LESS THAN (2024),
-> PARTITION p2024 VALUES LESS THAN (2025),
-> PARTITION pfuture VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.45 sec)

# 查看分区信息
mysql> SHOW CREATE TABLE fgedu_order_logs;
+—————+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html