2. 首页 > 国产数据库教程 > TiDB教程 > 正文

tidb教程FG117-分区表设计与优化生产实战

教程整理：风哥教程  |  更新时间：2026-04-04  |  教程分类：TiDB教程  |  文档学习：30
fgedu.net.cn

目录

一、基础概念

1.1 分区表定义

分区表是将一个大表按照特定规则分割成多个子表的技术，每个子表称为一个分区。TiDB支持三种分区类型：Range分区、Hash分区和List分区。

1.2 分区表优势

  • 提高查询性能：只扫描相关分区，减少数据扫描范围
  • 简化数据管理：可以单独对分区进行备份、恢复和维护
  • 改善数据生命周期管理：可以轻松删除过期数据
  • 提升并发性能：不同分区可以分布在不同的TiKV节点上

1.3 分区类型说明

分区类型 适用场景 示例
Range分区 按连续范围划分，如日期、数值范围 按月份分区销售数据
Hash分区 均匀分布数据，提高并发性能 按用户ID分区
List分区 按离散值划分，如地区、状态 按省份分区用户数据

二、规划建议

2.1 分区策略选择

Range分区建议：适用于时间序列数据，如日志、订单记录等。建议按天、周、月或季度进行分区。

Hash分区建议：适用于需要均匀分布数据的场景，如用户表、交易记录等。建议使用模数分区，分区数量一般为TiKV节点数的2-4倍。

List分区建议：适用于有明确分类的数据，如按地区、业务线等维度划分。

2.2 分区数量规划

  • 避免过多分区：一般不超过1000个分区
  • 合理设置分区大小：每个分区建议大小在10GB-100GB之间
  • 考虑未来扩展：预留足够的分区空间

2.3 分区键选择

  • 选择高频查询的列作为分区键
  • 避免使用随机值作为分区键
  • 考虑数据分布均匀性
  • 结合业务查询模式选择分区键

三、实施方案

3.1 Range分区实现

CREATE TABLE fgedudb.fgedu_orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(20) NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    create_time DATETIME NOT NULL,
    status TINYINT NOT NULL
) PARTITION BY RANGE (TO_DAYS(create_time)) (
    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 p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01'))
);

风哥提示：

3.2 Hash分区实现

CREATE TABLE fgedudb.fgedu_users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    created_at DATETIME NOT NULL
) PARTITION BY HASH (id) PARTITIONS 8;

3.3 List分区实现

CREATE TABLE fgedudb.fgedu_customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    address VARCHAR(200) NOT NULL
) PARTITION BY LIST COLUMNS (province) (
    PARTITION p_beijing VALUES IN ('北京市'),
    PARTITION p_shanghai VALUES IN ('上海市'),
    PARTITION p_guangdong VALUES IN ('广东省'),
    PARTITION p_other VALUES IN ('其他')
);

3.4 分区管理操作

添加分区

ALTER TABLE fgedudb.fgedu_orders ADD PARTITION (
    PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01'))
);

删除分区

ALTER TABLE fgedudb.fgedu_orders DROP PARTITION p202401;

查看分区信息

SHOW CREATE TABLE fgedudb.fgedu_orders;
CREATE TABLE `fgedu_orders` (
  `id` int(11) NOT NULL,
  `order_no` varchar(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `create_time` datetime NOT NULL,
  `status` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (to_days(`create_time`)) (
  PARTITION `p202402` VALUES LESS THAN (739306),
  PARTITION `p202403` VALUES LESS THAN (739337),
  PARTITION `p202404` VALUES LESS THAN (739367),
  PARTITION `p202405` VALUES LESS THAN (739398),
  PARTITION `p202406` VALUES LESS THAN (739428),
  PARTITION `p202407` VALUES LESS THAN (739459)
)

四、实战案例

4.1 时间序列数据分区案例

场景：电商平台订单数据，需要按月份分区，方便数据管理和查询优化。

步骤1：创建分区表

CREATE TABLE fgedudb.fgedu_orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    create_time DATETIME NOT NULL,
    status TINYINT NOT NULL,
    INDEX idx_create_time (create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
    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 p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

步骤2：插入测试数据

INSERT INTO fgedudb.fgedu_orders (id, order_no, user_id, total_amount, create_time, status)
VALUES
(1, 'ORD202401010001', 1001, 199.99, '2024-01-01 10:00:00', 1),
(2, 'ORD202401020001', 1002, 299.99, '2024-01-02 11:00:00', 1),
(3, 'ORD202402010001', 1003, 399.99, '2024-02-01 12:00:00', 1),
(4, 'ORD202403010001', 1004, 499.99, '2024-03-01 13:00:00', 1),
(5, 'ORD202404010001', 1005, 599.99, '2024-04-01 14:00:00', 1);

学习交流加群风哥QQ113257174

步骤3：分区查询优化

EXPLAIN SELECT * FROM fgedudb.fgedu_orders WHERE create_time BETWEEN '2024-02-01' AND '2024-02-29';
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| id                      | estRows  | task      | access object          | operator info                         |
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| TableReader_7           | 10.00    | root      |                        | data:Selection_6                      |
| └─Selection_6           | 10.00    | cop[tikv] |                        | where:create_time between ...         |
|   └─TableScan_5         | 10000.00 | cop[tikv] | table:fgedu_orders(p202402) | range:[-inf,+inf], keep order:false   |
+-------------------------+----------+-----------+------------------------+---------------------------------------+

步骤4：分区维护

-- 添加新分区
ALTER TABLE fgedudb.fgedu_orders REORGANIZE PARTITION p_future INTO (
    PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
    PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

4.2 Hash分区性能优化案例

场景：用户表数据量大，需要通过Hash分区提高查询性能和并发度。

步骤1：创建Hash分区表

CREATE TABLE fgedudb.fgedu_users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_created_at (created_at)
) PARTITION BY HASH (id) PARTITIONS 16;

步骤2：批量插入数据

-- 使用存储过程批量插入数据
DELIMITER //
CREATE PROCEDURE insert_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10000 DO
        INSERT INTO fgedudb.fgedu_users (id, username, email, password, age, created_at)
        VALUES (i, CONCAT('user', i), CONCAT('user', i, '@example.com'), 'password123', 20 + MOD(i, 30), NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_users();

步骤3：查询性能测试

-- 测试单用户查询
EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE id = 5000;
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| id                      | estRows  | task      | access object          | operator info                         |
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| TableReader_7           | 1.00     | root      |                        | data:Selection_6                      |
| └─Selection_6           | 1.00     | cop[tikv] |                        | where:id = 5000                       |
|   └─TableScan_5         | 1.00     | cop[tikv] | table:fgedu_users(p7)  | range:[5000,5000], keep order:false   |
+-------------------------+----------+-----------+------------------------+---------------------------------------+

步骤4：查看分区分布

SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions WHERE TABLE_SCHEMA = 'fgedudb' AND TABLE_NAME = 'fgedu_users';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             | 625        |
| p1             | 625        |
| p2             | 625        |
| p3             | 625        |
| p4             | 625        |
| p5             | 625        |
| p6             | 625        |
| p7             | 625        |
| p8             | 625        |
| p9             | 625        |
| p10            | 625        |
| p11            | 625        |
| p12            | 625        |
| p13            | 625        |
| p14            | 625        |
| p15            | 625        |
+----------------+------------+

五、经验总结

5.1 分区表最佳实践

  • 合理选择分区类型：根据业务场景选择合适的分区策略
  • 避免过度分区：分区数量不宜过多，一般不超过1000个
  • 定期维护分区：及时添加新分区，清理过期分区
  • 结合索引使用：在分区表上创建合适的索引以提高查询性能
  • 注意分区键选择：选择高频查询的列作为分区键

5.2 性能优化建议

  • 查询优化：尽量在查询中包含分区键条件，触发分区裁剪
  • 批量操作：对分区表进行批量操作时，考虑分区并行处理
  • 备份策略：可以针对特定分区进行备份，提高备份效率
  • 监控分区大小：定期监控各分区大小，及时调整分区策略

5.3 常见问题与解决方案

问题 原因 解决方案
分区表查询性能差 查询未使用分区键，导致全表扫描 修改查询语句，包含分区键条件
分区数据分布不均匀 分区键选择不当 重新选择合适的分区键或调整分区策略
添加分区失败 分区范围重叠或语法错误 检查分区定义，确保范围不重叠
分区表维护困难 分区数量过多或命名不规范 制定分区命名规范，定期清理过期分区

5.4 分区表使用注意事项

  • 分区表不支持外键约束
  • 分区键不能为NULL值
  • 分区表的主键必须包含分区键
  • 修改分区策略可能需要重建表
  • 分区表的统计信息需要定期更新

更多视频教程www.fgedu.net.cn

© 2024 TiDB数据库培训文档

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

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息