TIDB 分区表使用实践
作者：TiDB 社区干货传送门
- 2024-03-22 北京
本文字数：5835 字
阅读完需：约 19 分钟
作者： paulli 原文来源：https://tidb.net/blog/0515deee
一、分区表类型和语法示例
当前支持的类型包括 Range 分区、Range COLUMNS 分区、Range INTERVAL 分区、List 分区、List COLUMNS 分区、Hash 分区和 Key 分区。常用使用如下：
Range 分区
一个表按 Range 分区是指，对于表的每个分区中包含的所有行，按分区表达式计算的值都落在给定的范围内。Range 必须是连续的，并且不能有重叠，通过使用 VALUES LESS THAN 进行定义。
CREATE TABLE test_part ( id int not null auto_increment, uid varchar(200) not null, type varchar(200) not null, dns text, ds date default '9999-12-31', store_id int, unique idx (id,uid,type,store_id)) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21))
复制代码
Range COLUMNS 分区
Range COLUMNS 分区是 Range 分区的一种变体。你可以使用一个或者多个列作为分区键，分区列的数据类型可以是整数 (integer)、字符串（CHAR/VARCHAR），DATE 和 DATETIME。不支持使用任何表达式。
CREATE TABLE test_part ( id int not null auto_increment, uid varchar(200) not null, type varchar(200) not null, dns text, ds date default '9999-12-31', store_id int, unique idx (id,uid,type,ds)) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4PARTITION BY RANGE COLUMNS(ds) ( PARTITION p_test_part_20240101 VALUES LESS THAN ('2024-01-01'), PARTITION p_test_part_20240102 VALUES LESS THAN ('2024-01-02'), PARTITION p_test_part_20240103 VALUES LESS THAN ('2024-01-03'), PARTITION p_test_part_max VALUES LESS THAN MAXVALUE);
复制代码
List 分区
List 分区和 Range 分区有很多相似的地方。不同之处主要在于 List 分区中，对于表的每个分区中包含的所有行，按分区表达式计算的值属于给定的数据集合。每个分区定义的数据集合有任意个值，但不能有重复的值，可通过 PARTITION … VALUES IN (…) 子句对值进行定义。
CREATE TABLE test_part ( id int not null auto_increment, uid varchar(200) not null, type varchar(200) not null, dns text, ds date default '9999-12-31', store_id int, unique idx (id,uid,type,store_id)) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4PARTITION BY LIST (store_id) ( PARTITION pNorth VALUES IN (1, 2, 3, 4, 5), PARTITION pEast VALUES IN (6, 7, 8, 9, 10), PARTITION pWest VALUES IN (11, 12, 13, 14, 15), PARTITION pCentral VALUES IN (16, 17, 18, 19, 20));
复制代码
List COLUMNS 分区
List COLUMNS 分区是 List 分区的一种变体，可以将多个列用作分区键，并且可以将整数类型以外的数据类型的列用作分区列。你还可以使用字符串类型、DATE 和 DATETIME 类型的列
CREATE TABLE test_part ( id int not null auto_increment, uid varchar(200) not null, type varchar(200) not null, dns text, ds date default '9999-12-31', store_id int, city varchar(20), unique idx (id,uid,type,city)) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN('beijing', 'hebei', 'shandong'), PARTITION pRegion_2 VALUES IN('jiangsu', 'shanghai', 'zhejiang'), PARTITION pRegion_3 VALUES IN('guangzhou', 'shenzhen', 'xiamen'), PARTITION pRegion_4 VALUES IN('chengdu', 'chongqing', 'xian'));
复制代码
Hash 分区
Hash 分区主要用于保证数据均匀地分散到一定数量的分区里面。在 Range 分区中你必须为每个分区指定值的范围；在 Hash 分区中，你只需要指定分区的数量
CREATE TABLE test_part6 ( id int not null auto_increment, uid varchar(200) not null, type varchar(200) not null, dns text, ds date default '9999-12-31', store_id int, unique idx (id,uid,type,store_id)) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4PARTITION BY HASH(store_id);
复制代码
二、分区表使用场景和注意事项
使用场景
Range 分区、Range COLUMNS 分区、List 分区和 List COLUMNS 优势场景如下：
删除旧数据。
使用包含时间或者日期的列，或者是其它按序生成的数据。
频繁查询分区使用的列。
Hash 分区和 Key 分区优势场景如下：
用于大量写入场景下的数据打散。
多列打散和非整数类型字段的打散。
注意事项
分区表的每个唯一键，必须包含分区表达式中用到的所有列
函数的分区限制，常规函数 DAY，YEAR 等
三、分区表执行计划
分区裁剪
分区裁剪需要使用分区表上面的查询条件，下面语句通过 ds 过滤分区，避免了所有分区扫描，常用分区裁剪支持的两个场景是等值查询和 IN 语句。
mysql> explain select * from test_part where id = '4566440' and uid = '5eff63cd38e69d8' and type = '99' and ds = date '2024-01-02';+-------------------------------+---------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------+| id | estRows | task | access object | operator info |+-------------------------------+---------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------+| IndexLookUp_7 | 1.00 | root | partition:p_test_part_20240103 | || ├─IndexRangeScan_5(Build) | 1.00 | cop[tikv] | table:test_part2, index:idx(id, uid, type, ds) | range:["4566440" "5eff63cd38e69d8" "99" 2024-01-02,"4566440" "5eff63cd38e69d8" "99" 2024-01-02], keep order:false || └─TableRowIDScan_6(Probe) | 1.00 | cop[tikv] | table:test_part2 | keep order:false |+-------------------------------+---------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------+3 rows in set (0.01 sec)
复制代码
指定分区
可以通过显示直接指定查询的分区。
mysql> explain select * from test_part2 partition(p_test_part_20240103);+-----------------------+------------+-----------+--------------------------------+----------------------+| id | estRows | task | access object | operator info |+-----------------------+------------+-----------+--------------------------------+----------------------+| TableReader_5 | 2340420.00 | root | partition:p_test_part_20240103 | data:TableFullScan_4 || └─TableFullScan_4 | 2340420.00 | cop[tikv] | table:test_part2 | keep order:false |+-----------------------+------------+-----------+--------------------------------+----------------------+2 rows in set (0.00 sec)
复制代码
四、分区表日常运维
添加分区
alter table test_part1 add partition (PARTITION p_test_part_20240104 VALUES LESS THAN ('2024-01-04'));
复制代码
删除分区
alter table test_part1 drop partition p_test_part_20240104 ;
复制代码
收集分区统计信息
ANALYZE table test_part1 PARTITION p_test_part_20240103
复制代码
查看分区表信息
mysql> SELECT * FROM information_schema.PARTITIONS WHERE table_schema='test' AND table_name='test_part';+---------------+--------------+------------+----------------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+-------------------+----------------------------+| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME | TIDB_PARTITION_ID | TIDB_PLACEMENT_POLICY_NAME |+---------------+--------------+------------+----------------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+-------------------+----------------------------+| def | test | test_part | p_test_part_20240101 | NULL | 1 | NULL | RANGE COLUMNS | NULL | ds | NULL | '2024-01-01' | 0 | 0 | 0 | 0 | 0 | 0 | 2024-03-13 14:35:16 | NULL | NULL | NULL | | NULL | NULL | 495 | NULL || def | test | test_part | p_test_part_20240102 | NULL | 2 | NULL | RANGE COLUMNS | NULL | ds | NULL | '2024-01-02' | 1240420 | 66 | 82349815 | 0 | 43896795 | 0 | 2024-03-13 14:35:16 | NULL | NULL | NULL | | NULL | NULL | 496 | NULL || def | test | test_part | p_test_part_20240103 | NULL | 3 | NULL | RANGE COLUMNS | NULL | ds | NULL | '2024-01-03' | 1100000 | 66 | 73027677 | 0 | 38927677 | 0 | 2024-03-13 14:35:16 | NULL | NULL | NULL | | NULL | NULL | 497 | NULL || def | test | test_part | p_test_part_max | NULL | 4 | NULL | RANGE COLUMNS | NULL | ds | NULL | MAXVALUE | 0 | 0 | 0 | 0 | 0 | 0 | 2024-03-13 14:35:16 | NULL | NULL | NULL | | NULL | NULL | 498 | NULL |+---------------+--------------+------------+----------------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+-------------------+----------------------------+4 rows in set (0.02 sec)
复制代码
查看健康度信息
mysql> SHOW STATS_HEALTHY where table_name = 'test_part';+---------+------------+----------------------+---------+| Db_name | Table_name | Partition_name | Healthy |+---------+------------+----------------------+---------+| test | test_part | global | 100 || test | test_part | p_test_part_20240101 | 100 || test | test_part | p_test_part_20240102 | 100 || test | test_part | p_test_part_20240103 | 100 || test | test_part | p_test_part_max | 100 |+---------+------------+----------------------+---------+5 rows in set (0.00 sec)
复制代码
分区表转非分区表
ALTER TABLE test_part REMOVE PARTITIONING
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/5ceef8c8076bc54629cabe166】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论