TIDB分区表应用指南
当前支持的类型包括 Range 分区、Range COLUMNS 分区、Range INTERVAL 分区、List 分区、List COLUMNS 分区、Hash 分区和 Key 分区。常用使用如下：
一个表按 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 = 4 PARTITION 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 分区的一种变体。你可以使用一个或者多个列作为分区键，分区列的数据类型可以是整数 (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 = 4
PARTITION 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 分区和 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 = 4 PARTITION 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 分区的一种变体，可以将多个列用作分区键，并且可以将整数类型以外的数据类型的列用作分区列。你还可以使用字符串类型、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 = 4
PARTITION 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 分区主要用于保证数据均匀地分散到一定数量的分区里面。在 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 = 4 PARTITION 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
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
