TIDB 分区表使用实践

一、分区表类型和语法示例

当前支持的类型包括 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 = 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 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 分区

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 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 分区

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   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