本文主要介绍使用DDL语句创建分区表的语法、子句、参数和基本方式。本语法仅适用于AUTO模式数据库。

注意事项

  • 使用分区表语法之前，请务必确认创建当前的逻辑库时模式指定为自动分区模式（mode='auto' ），非自动分区模式不允许使用分区表的建表语法。您也可以通过SHOW CREATE DATBASE db_name语法查看当前逻辑库的建表模式。示例如下：

    CREATE DATABASE part_db mode='auto';
Query OK, 1 row affected (4.29 sec)

SHOW CREATE DATABASE part_db;
+----------+-----------------------------------------------+
| DATABASE | CREATE DATABASE                               |
+----------+-----------------------------------------------+
| part_db  | CREATE DATABASE `part_db` /* MODE = 'auto' */ |
+----------+-----------------------------------------------+
1 row in set (0.18 sec)

    创建数据库的语法详情，请参见CREATE DATABASE。

  • 如果分区表的主键不含分区键， 且不是自增主键，需要业务保证主键的唯一性。

语法

CREATE [PARTITION] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [table_partition_definition]
    [local_partition_definition]

create_definition:
    col_name column_definition
  | mysql_create_definition
  | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...)
      [global_secondary_index_option]
      [index_option] ...

index_sharding_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

# 全局二级索引相关
global_secondary_index_option:
    [COVERING (col_name,...)]
    [partition_options]
    [VISIBLE|INVISIBLE]

table_options:
    table_option [[,] table_option] ...

table_option: {
# 指定tablegroup
    TABLEGROUP [=] value,...,}

# 分区表类型定义
table_partition_definition:
        single
  |    broadcast
  | partition_options


# 分区策略定义
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)})
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)} }
    partition_list_spec

# 分区函数定义
partition_func：
    YEAR
  | TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING

# 三种分区类型定义
partition_list_spec:
        hash_partition_list
  | range_partition_list
  | list_partition_list

# Hash/Key分区定义
hash_partition_list:
    PARTITIONS partition_count

# Range/Range Columns分区定义
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]

# List/List Columns分区定义
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

partition_spec_options:
          [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [LOCALITY [=] locality_option]

table_option：
           [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [{CHARSET | CHARACTER SET} [=] charset]
        [COLLATE [=] collation]
        [TABLEGROUP [=] table_group_id]
        [LOCALITY [=] locality_option]

locality_option:
    'dn=storage_inst_id_list'

storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]

local_partition_definition:
        LOCAL PARTITION BY RANGE (column_name)
    [STARTWITH 'yyyy-MM-dd']
    INTERVAL interval_count [YEAR|MONTH|DAY]
    [EXPIRE AFTER expire_after_count]
    [PRE ALLOCATE pre_allocate_count]
    [PIVOTDATE pivotdate_func]
    [DISABLE SCHEDULE]

pivotdate_func:
        NOW()
  |    DATE_ADD(...)
  | DATE_SUB(...)
说明 PolarDB-X DDL语法基于MySQL语法，以上主要列出了差异部分，详细语法请参见MySQL 文档。

名词解释

  • 分区键：分区表中用于进行水平切分的一个列或多个列。
  • 分区列：水平切分后，参与分区路由及计算的列，它通常是分区键的一部分，一个分区键可以含有一个或多个分区列。
  • 向量分区键：由一个或多个分区列组成的分区键。
  • 单列分区键：由一个分区列组成的分区键。
  • 前缀分区列：若一个向量分区键由N（N>1）个分区列组成，它的前K（1<=K<=N）个分区列便组成这个向量分区键的前缀分区列。
  • 分区函数：将分区列作为一个函数的输入参数，并将该函数的输出结果作为原始值参与路由计算，该函数被称为分区函数。
  • 分区裁剪：根据分区定义及查询条件，最大限度地过滤不需要扫描的分区的查询优化手段。
  • 热点分裂：当向量分区键的前缀分区列存在访问热点或分布不均衡时，允许使用下一个分区列对热点分区进行分裂，以达到负载均衡效果。

参数说明

参数说明
CHARSET | CHARACTER SET指定表中列的默认字符集，可使用字符集如下：
  • utf8
  • utf8mb4
  • gbk
COLLATE指定表中列的默认字符序，可使用字符序如下：
  • utf8_bin
  • utf8_general_ci
  • utf8_unicode_ci
  • gbk_bin
  • gbk_chinese_ci
  • utf8mb4_general_ci
  • utf8mb4__general_cs
  • utf8mb4_bin
  • utf8mb4_unicode_ci
TABLEGROUP用于指定分区表所属于的表组。若不指定，会自动查找或创建与之分区方式完全一致的表组。
LOCALITY用于指定分区表的所在存储节点。

默认自动分区

  • 建表SQL在不指定分区键的情况下，PolarDB-X默认会按主键（如果表没有指定主键，则使用隐式主键）并使用KEY分区进行默认分区。

    默认分区的分区数目=实例创建时逻辑节点数×8。例如，PolarDB-X实例创建时，逻辑节点是2，那默认分区数目就是16。

  • 除了主表默认会按主键自动分区，主表中所有索引也会默认以索引列与主键列作为分区键并进行自动分区。

如下示例是标准的MySQL建表语法，主键是id，索引列为name：

CREATE TABLE auto_part_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 primary key(id),
 index idx_name (name)
);

若使用SHOW CREATE TABLE语句查询该建表语句，显示标准的MySQL建表语法，自动隐藏所有分区信息：

show create table auto_part_tbl;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE         | CREATE TABLE                                                                                                                                                                                                                     |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_part_tbl | CREATE TABLE `auto_part_tbl` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `bid` int(11) DEFAULT NULL,
    `name` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

若使用SHOW FULL CREATE TABLE查询该建表语句，则会显示上述主表及其索引表的所有分区信息：

show full create table auto_part_tbl;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE         | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                              |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_part_tbl | CREATE PARTITION TABLE `auto_part_tbl` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `bid` int(11) DEFAULT NULL,
    `name` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`),
    GLOBAL INDEX /* idx_name_$a870 */ `idx_name` (`name`) PARTITION BY KEY (`name`, `id`) PARTITIONS 16,
    LOCAL KEY `_local_idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 16
/* tablegroup = `tg108` */ |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
从返回信息中可以看到：
  • 主表auto_part_tbl默认按ID进行了KEY分区，分区数是16；
  • 主表的索引idx_name默认使用了全局索引，全局索引的分区键是 `name`，`id`，分区数也是16。

单表与广播表

PolarDB-X允许创建表时通过指定关键字SINGLE来创建单表（不进行任何分区的表），示例如下：
CREATE TABLE single_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) SINGLE;
PolarDB-X允许创建表时通过指定关键字BROADCAST来创建广播表（该表将在所有DN节点上有一份数据完全相同的拷贝），示例如下：
CREATE TABLE broadcast_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) BROADCAST;

分区类型介绍

PolarDB-X允许创建表时通过指定分区子句的语法，来创建符合业务需求的分区表。PolarDB-X支持三大类型的分区：
  • Hash类型：基于用户指定的分区列或分区函数表达式的值，使用内置的一致性哈希算法计算其哈希值并进行分区路由的策略。按是否支持使用分区函数表达式或使用多个分区列作为分区键，Hash分区策略又可以细分为Key分区和Hash分区两种分区策略。
  • Range类型：基于用户指定的分区列或分区函数表达式的值，通过比较计算来确定数据位于哪些预定义分区的范围并进行分区路由的策略。按是否支持使用分区函数表达式或使用多个分区列作为分区键，Range分区策略又可以细分为Range Columns分区和Range分区两种分区策略。
  • List类型：与Range分区策略类似，基于用户指定的分区列或分区函数表达式的值，通过比较计算来确定数据位于哪些预定义分区的取值集合并进行分区路由的策略。按是否多个分区列作为分区键以及其使用方式的不同，List类型也分为List Columns分区和List分区两种分区策略。

Hash类型

PolarDB-X的Hash类型分区可细分为Hash分区与Key分区两种类型。Hash分区与Key分区是原生MySQL的标准分区语法之一，PolarDB-X为提供灵活强大的分区管理能力（例如分裂、合并与迁移等）以及考虑支持向量分区键下热点散裂，PolarDB-X不仅在语法上尽量兼容了MySQL的Hash分区与Key分区的建表语法（PolarDB-X仅兼容了语法，分区路由的实现与MySQL并非一致），但是对于Key分区与Hash分区的路由行为重新进行了定义。Key分区与Hash分区使用区别如下表所示：

表 1. Key分区策略与Hash分区策略对比
分区策略分区键支持是否支持分区函数语法示例特点与限制路由描述（点查）
Key（默认的分区策略）单列分区键否PARTITION BY KEY(c1)
  • 不支持热点分裂；
  • 最多有1个分区列参与路由计算；
  1. 按c1列的值，使用一致性哈希算法计算其哈希c1_hash；
  2. 按哈希值 c1_hash进行分区路由。
向量分区键否PARTITION BY KEY(c1,c2,...,cn)
  • 支持热点分裂；
  • 建表后默认仅第1个分区列c1实际参与路由计算，热点分裂时可以使用c2,...,cn等更多的分区列；
  • 最多允许n个分区列同时参与路由计算；
  • 分区列数目n默认不能超过5个。
  1. 将(c1,c2,...,cn)这N个列的值组成一组分区键向量，并使用一致性哈希算法为向量的各个列的值计算其哈希值，从而将一个分区键向量映射成一个哈希值向量(c1_hash,c2_hash,...,cn_hash)；
  2. 将哈希值向量(c1_hash,c2_hash,...,cn_hash)按range路由并找到目标分区。
Hash单列分区键否PARTITION BY HASH(c1)
  • 不支持热点分裂；
  • 一个分区列参与路由。
  • 目前支持11个分区函数：
    • YEAR
    • MONTH
    • DAYOFMONTH
    • DAYOFWEEK
    • DAYOFYEAR
    • TO_DAYS
    • TO_MONTHS
    • TO_WEEKS
    • TO_SECONDS
    • UNIX_TIMESTAMP
    • SUBSTR/SUBSTRING
PARTITION BY HASH(c1)与PARTITION BY KEY(c1)完全等同 ，其路由算法与PARTITION BY KEY(c1)完全一致。
是PARTITION BY HASH(YEAR(c1))
  1. 按c1列的值，使用YEAR分区函数计算其对应的年份year；
  2. 将年份year使用一致性哈希算法计算其哈希值year_hash；
  3. 按哈希值year_hash进行分区路由。
向量分区键否PARTITIONBY HASH(c1,c2,...,cn)
  • 不支持热点分裂；
  • 建表后，n个分区列将同时与路由；
  • 分区列数目n默认不能超过5个。
  1. 将(c1,c2,...,cn)这N个分区列的值组成一组向量，并使用一致性哈希算法，计算该向量对应哈希值hashVal；
  2. 按哈希hashVal进行分区路由。
示例1-1：Key分区
Key分区也是PolarDB-X的默认分区方式。 Key分区支持向量分区键。例如，用户想按用户名字name列与用户ID两个列作为分区键进行分区，预建分区数目可以指定为8，可以使用以下的语法建表：
CREATE TABLE key_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(name, id)
PARTITIONS 8;
根据KEY分区的特点，如上所示向量分区键的分区表，当它进行路由计算时，默认只使用向量分区键的第1个分区列（name）进行路由。因此，业务查询SQL的WHERE条件表达式中，只需要含有第1个分区列的等值条件，即可命中分区裁剪的优化，如下所示：
##（命中分区裁剪，只需扫描一个分区）
SELECT id from key_tbl where name='Jack';

若第1个分区列name存在分布不均衡或者出现数据热点，您也可以通过分区分裂操作（详细请参考表组级分区变更语法（AUTO模式）），并使用下一个分区列（如id）进行分区分裂，从而解决数据不均衡的问题。

如果一个由N个分区列组成的向量分区键，若它实际路由使用到的分区列数目是前K个（1<=K<=N），则查询SQL的WHERE条件表达式只需要包含由这前K个分区列组成的前缀分区裂即可命中分区裁剪。

示例1-2：Hash分区
如果您想使用用户ID作为分区键进行水平分区，可以使用Hash分区进行建表，分区数目可以指定为8，建表语法示例如下：
CREATE TABLE hash_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
partition by hash(id)
partitions 8;
Hash分区支持使用分区函数表达式（例如YEAR/TO_DAYS/...等）来将时间类型转换成整数类型。因此，如果您想按用户出生日期birthday列进行分区, 并且预建的Hash分区数目是8, 也可以使用如下语句建表：
CREATE TABLE hash_tbl_todays(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(TO_DAYS(birthday))
PARTITIONS 8;

目前PolarDB-X的分区函数仅支持以下的函数列表：

  • YEAR
  • MONTH
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • TO_DAYS
  • TO_MONTHS
  • TO_WEEKS
  • TO_SECONDS
  • UNIX_TIMESTAMP
  • SUBSTR/SUBSTRING

因此，除SUBSTR/SUBSTRING的分区键类型必须为字符串类型以外，其余分区函数的分区键的类型必须是时间类型（DATE/DATETIME/TIMESTAMP），其它类型不支持使用分区函数。

示例1-3：Hash分区扩展
PolarDB-X对Hash分区的语法进行扩展，让Hash分区支持使用向量分区键 （原生MySQL的标准分区语法，可使用如下语句：
CREATE TABLE hash_tbl2(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(name, birthday)
PARTITIONS 8;

与Key分区不同， Hash分区使用向量分区键，这样的分区表在分区路由计算时，是所有分区列同时参与哈希值计算与路由计算，所以，它会要求查询SQL的WHERE条件表达式必须要含包所有的分区列的等值条件才能命中分区裁剪，如下示例的SQL1可以命中hash_tbl2的分区裁剪，而SQL2则不能命中hash_tbl2的分区裁剪：

##SQL1（命中分区裁剪，只扫描一个分区）:
SELECT id from hash_tbl2 where name='Jack' and birthday='1990-11-11';

##SQL2（没命中分区裁剪，全分区扫描）:
SELECT id from hash_tbl2 where name='Jack';

Hash分区由于一开始就直接使用所有的分区键进行哈希值计算，所以理论上，它比使用向量分区键的Key分区会打散得更均衡，但它不再支持使用下一个列（因为已经没有列可用）进行热点散列。

相关限制
  • 数据类型限制
    • 整数类型： BIGINT/BIGINT UNSINGED/INT UNSINGED/INT/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED；
    • 时间类型：DATETIME/DATE/TIMESTAMP；
    • 字符串类型：CHAR/VARCHAR。
  • 语法限制
    • Hash分区的单列分区键支持使用分区函数，且分区键类型必须为时间类型；
    • Hash分区的向量分区键不允许使用分区函数，不支持热点分列；
    • 默认最大分区数目不允许超过8192个；
    • 默认最大分区列数目不允许超过5个。
数据均匀性
  • Key分区与Hash分区内置的一致性Hash散列算法是经过业界广泛测试的、冲突概率低且性能良好的散列算法 MurmurHash3。
  • 基于MurmurHash3的特性，一般情况下，当分区键不同取值的数目N大于3000时，Key分区与Hash分区的数据分布才会相对均衡，且N的值越大，数据分布也将越均衡。

Range类型

PolarDB-X的Range类型分区，可细分为Range分区与Range Columns分区两种。同样， Range分区和Range Columns分区属于原生MySQL的标准分区语法。这两种分区的使用区别如下表所示。

表 2. Range Columns分区策略与Range分区策略对比
分区策略分区键支持是否支持分区函数语法示例特点与限制路由描述（点查）
Range Columns单列分区键& 向量分区键否PARTITION BY RANGE COLUMNS (c1,c2,...,cn) ( PARTITION p1 VALUES LESS THAN (1,10,...,1000), PARTITION p2 VALUES LESS THAN (2,20,...,2000) ...)支持热点分裂（例如c1有热点值88，可以使用c2进行分区分裂解决热点）。
  1. 将(c1,c2,...,cn)这N个列的值组成一组分区键向量；
  2. 根据这个分区键向量(c1,c2,...,cn)按二分查找算法判断它属于哪个预定义分区，并最终路由到目标分区。
Range单列分区键是PARTITION BY RANGE(YEAR(c1)) ( PARTITION p1 VALUES LESS THAN (2019), PARTITION p2 VALUES LESS THAN (2021) ...)
  • 不支持热点分裂；
  • 目前支持的分区函数：
    • YEAR
    • MONTH
    • DAYOFMONTH
    • DAYOFWEEK
    • DAYOFYEAR
    • TO_DAYS
    • TO_MONTHS
    • TO_WEEKS
    • TO_SECONDS
    • UNIX_TIMESTAMP
    • SUBSTR/SUBSTRING
  1. 按c1列的值，使用YEAR分区函数计算其对应的年份year；
  2. 年份year按二分查找算法判断它属于哪个预定义分区，并路由到目标分区。
示例2-1：Range Columns分区

Range Columns分区支持使用向量分区键，但它不支持使用分区函数。例如，业务可以按订单ID与订单日期进行Range分区， 可以使用以下的建表语法：

CREATE TABLE orders(
 order_id int,
 order_time datetime not null)
PARTITION BY range columns(order_id,order_time)
(
  PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
  PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
  PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
  PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
  PARTITION p5 VALUES LESS THAN (50000,'2021-01-01'),
  PARTITION p6 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
重要 目前Range Columns分区不支持使用TIMESTAMP/TIME等与时区相关的类型作为分区键。
示例2-2：Range分区

Range分区仅支持单列分区健，但对于时间类型的分区列，它支持使用分区函数（例如YEAR/TO_DAYS/TO_SECONDS/MONTH等）来将时间类型转换成整数类型。

重要 Range分区是不支持直接使用字符串类型作为分区列。

例如，业务想按订单的日期order_time列进行Range分区，并且每个季度一个分区，建表语法如下所示：

CREATE TABLE orders_todays(
 id int,
 order_time datetime not null)
PARTITION BY RANGE(to_days(order_time))
(
  PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
  PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
  PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
  PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
  PARTITION p5 VALUES LESS THAN (to_days('2022-01-01')),
  PARTITION p6 VALUES LESS THAN (MAXVALUE)
);
重要 目前Range分区仅支持使用整数类型作为分区键，且分区键仅支持1个分区列。
相关限制
  • 数据类型限制
    • 整数类型： BIGINT/BIGINT UNSINGEDINT/INT/INT UNSINGED/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED；
    • 时间类型：DATETIME/DATE；
    • 字符串类型：CHAR/VARCHAR。
  • 语法限制
    • Range Columns分区与Range分区都不支持使用NULL值作为边界值；
    • Range Columns分区目前不支的使用TIMESTAMP类型；
    • Range分区仅支持整数类型，若分区键使用TIMESTAMP类型，必须配套使用分区函数UNIX_TIMESTAMP确保时区一致；
    • Range的分区不支持热点分列；
    • 查询时，NULL值查询会被当作最小值进行分区路由；
    • 默认最大分区数目不允许超过8192个；
    • 默认最大分区列数目不允许超过5个。

List类型

与Range类型类似，PolarDB-X将List分区策略进一步细分为List分区与List Columns分区两种类型。List分区与List Columns分区属于原生MySQL的标准分区语法。此外，PolarDB-X的List分区和List Columns分区还支持Default分区。List Columns分区与List分区的使用区别如下表所示：

表 3. List Columns分区策略与List分区策略对比
分区策略分区键支持是否支持分区函数语法示例特点与限制路由描述（点查）
List Columns单列分区键& 向量分区键否PARTITION BY LIST COLUMNS (c1,c2,...,cn) ( PARTITION p1 VALUES IN ((1,10,...,1000),(2,20,...,2000) ), PARTITION p2 VALUES IN ((3,30,...,3000),(3,30,...,3000) ), ...)不支持热点分裂
  1. 将(c1,c2,...,cn)这N个列的值组成一组分区键向量；
  2. 根据这个分区键向量(c1,c2,...,cn)按二分查找算法判断它属于哪个预定义分区，并最终路由到目标分区。
List单列分区键是PARTITION BY LIST(YEAR(c1)) ( PARTITION p1 VALUES IN (2018,2019), PARTITION p2 VALUES IN (2020,2021) ...)不支持热点分裂。
示例3-1：List Columns分区

List Columns分区支持使用向量分区键。例如，业务可以按订单的国家country与城市city进行List Columns分区， 建表语法如下所示：

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','NewYork'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russian','Moscow'))
);
重要 目前List Columns分区不支持使用TIMESTAMP/TIME等与时区相关的类型作为分区键。
示例3-2：List分区

List分区只支持单列分区健，但对于时间类型的分区列，它支持分区函数表达式（例如YEAR/MONTH/DAYOFMONTH/TO_DAYS/TO_SECONDS等）来将时间类型转换成整数类型。

例如，业务想按订单日期order_time的年份进行List分区, 则可以使用以下语法建表：

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
);
重要 目前List仅支持使用整数类型作为分区键，另外要值得注意的是，也不支持直接使用字符串类型作为分区列。
示例3-3：带Default的List Columns分区和List分区

PolarDB-X支持创建带default的List Columns分区和List分区，普通分区中未定义的数据将被路由至default分区。

重要 最多只允许定义一个default分区，且default分区只允许出现在最后一个分区位置。
CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','NewYork'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russian','Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019),
  PARTITION pd VALUES IN (DEFAULT)
);
相关限制
  • 数据类型限制
    • 整数类型： BIGINT/BIGINT UNSINGEDINT/INT/INT UNSINGED/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED；
    • 时间类型：DATETIME/DATE；
    • 字符串类型：CHAR/VARCHAR。
  • 语法限制
    • List Columns分区目前还不支的使用TIMESTAMP类型；
    • List分区仅支持整数类型；
    • List Columns分区与List分区均不支持热点分裂；
    • 默认最大分区数目不允许超过8192个；
    • 默认最大分区列数目不允许超过5个。

数据类型说明

表 4. 各分区策略分区列数据类型的支持情况
数据类型Hash类型Range类型List类型
HASHKeyRangeRange ColumnsListList Columns
单区分列多区分列
数值类型TINYINT正确支持正确支持正确支持正确支持正确支持正确支持正确支持
TINYINT UNSIGNED正确支持正确支持正确支持正确支持正确支持正确支持正确支持
SMALLINT正确支持正确支持正确支持正确支持正确支持正确支持正确支持
SMALLINT UNSIGNED正确支持正确支持正确支持正确支持正确支持正确支持正确支持
MEDIUMINT正确支持正确支持正确支持正确支持正确支持正确支持正确支持
MEDIUMINT UNSIGNED正确支持正确支持正确支持正确支持正确支持正确支持正确支持
INT正确支持正确支持正确支持正确支持正确支持正确支持正确支持
INT UNSIGNED正确支持正确支持正确支持正确支持正确支持正确支持正确支持
BIGINT正确支持正确支持正确支持正确支持正确支持正确支持正确支持
BIGINT UNSIGNED正确支持正确支持正确支持正确支持正确支持正确支持正确支持
时间类型DATE正确（该类型的分区列支持使用分区函数）正确支持正确支持正确（该类型的分区列支持使用分区函数）正确支持正确（该类型的分区列支持使用分区函数）正确支持
DATETIME正确（该类型的分区列支持使用分区函数）正确支持正确支持正确（该类型的分区列支持使用分区函数）正确支持正确（该类型的分区列支持使用分区函数）正确支持
TIMESTAMP正确（该类型的分区列支持使用分区函数）正确支持正确支持错误不支持错误不支持错误不支持错误不支持
字符串类型CHAR正确支持正确支持正确支持错误不支持正确支持错误不支持正确支持
VARCHAR正确支持正确支持正确支持错误不支持正确支持错误不支持正确支持

关于分区列的数据类型与路由计算的说明

分区表的路由计算是直接依赖于分区列的数据类型的，特别是Key分区与Hash分区。因此，使用不同数据类型的分区列，其哈希值算法或比较算法（比如，大小写是否敏感）的实现不一样，会产生不一样的路由行为（MySQL的分区路由算法也是类型强相关的）。

如下所示，假如tbl_int表是分区列，类型是int且分区数是1024, 而tbl_bigint分区列类型是bigint且分区数目也是1024。它们虽然都是整数类型，但是由于数据类型不一样，对于同一个查询值（12345678）的路由结果也不一样：

show create table tbl_int;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_int | CREATE TABLE `tbl_int` (
    `a` int(11) NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 1024 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

show create table tbl_bigint;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE      | CREATE TABLE                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_bigint | CREATE TABLE `tbl_bigint` (
    `a` bigint(20) NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 1024 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)mysql> create table if not exists tbl_bigint(a bigint not null)
    -> partition by key(a) partitions 1024;
Query OK, 0 rows affected (28.41 sec)

explain select * from tbl_int where a=12345678;
+---------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                             |
+---------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_int[p260]", sql="SELECT `a` FROM `tbl_int` AS `tbl_int` WHERE (`a` = ?)") |
| HitCache:false                                                                                    |
| Source:PLAN_CACHE                                                                                 |
| TemplateId: c90af636                                                                              |
+---------------------------------------------------------------------------------------------------+
4 rows in set (0.45 sec)

explain select * from tbl_bigint where a=12345678;
+------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                      |
+------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_bigint[p477]", sql="SELECT `a` FROM `tbl_bigint` AS `tbl_bigint` WHERE (`a` = ?)") |
| HitCache:false                                                                                             |
| Source:PLAN_CACHE                                                                                          |
| TemplateId: 9b2fa47c                                                                                       |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
分区列大小写、字符集及校验集的说明
说明 分区列的字符集（charset）及校验集（collation）对分区表的路由算法会产生直接影响。比如，指定分区路由是否需要忽略大小写。如果分区表的校验集是区分大小写的，则分区路由在哈希与比较过程中便会区分大小写；如果分区表的校验集不区分大小写，则分区路由在哈希与比较过程中不会区分大小写。默认情况下，字符类型的分区列会使用字符集utf8及不区分区大小写的校验集 utf8_general_ci。
示例1
如果用户需要让分区表在路由时区分分区列的大小写，在建表时将分区表的校验集设置为区分大小写的校验集即可（如utf8_bin）。如下所示，分区表tbl_varchar_cs的分区表采用了CHARACTER SET utf8 COLLATE utf8_bin ，所以对于大小写不同的两个字符串 'AbcD' 与 'abcd' ，分区表会将它们路由不同的分区：
show create table tbl_varchar_cs;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE          | CREATE TABLE                                                                                                                                                                                                          |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_varchar_cs | CREATE TABLE `tbl_varchar_cs` (
    `a` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`a`)
PARTITIONS 64 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

explain select a from tbl_varchar_cs where a in ('AbcD');
+-------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_cs[p29]", sql="SELECT `a` FROM `tbl_varchar_cs` AS `tbl_varchar_cs` WHERE (`a` IN(?))") |
| HitCache:false                                                                                                          |
| Source:PLAN_CACHE                                                                                                       |
| TemplateId: 2c49c244                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.11 sec)

explain select a from tbl_varchar_cs where a in ('abcd');
+-------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_cs[p11]", sql="SELECT `a` FROM `tbl_varchar_cs` AS `tbl_varchar_cs` WHERE (`a` IN(?))") |
| HitCache:true                                                                                                           |
| Source:PLAN_CACHE                                                                                                       |
| TemplateId: 2c49c244                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
示例2
如果需要让分区表在路由时忽略分区列的大小写，在建表时将分区表的校验集设置为不区分大小写的校验集即可（如utf8_general_ci）。如下所示，分区表tbl_varchar_ci的分区表采用了CHARACTER SET utf8 COLLATE utf8_general_ci ，所以对于大小写不同的两个字符串 'AbcD' 与 'abcd' ，分区表则会将它们路由到同样的分区：
show create table tbl_varchar_ci;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE          | CREATE TABLE                                                                                                                                                                      |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_varchar_ci | CREATE TABLE `tbl_varchar_ci` (
    `a` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`a`)
PARTITIONS 64 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

explain select a from tbl_varchar_ci where a in ('AbcD');
+------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_ci[p4]", sql="SELECT `a` FROM `tbl_varchar_ci` AS `tbl_varchar_ci` WHERE (`a` IN(?))") |
| HitCache:false                                                                                                         |
| Source:PLAN_CACHE                                                                                                      |
| TemplateId: 5c97178e                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.15 sec)

explain select a from tbl_varchar_ci where a in ('abcd');
+------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_ci[p4]", sql="SELECT `a` FROM `tbl_varchar_ci` AS `tbl_varchar_ci` WHERE (`a` IN(?))") |
| HitCache:true                                                                                                          |
| Source:PLAN_CACHE                                                                                                      |
| TemplateId: 5c97178e                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
分区列字符集与校验集的变更

由于分区表的路由算法与数据类型是相关的，如果分区列的字符集与校验集被修改，将会导致全表所有数据的重分布。所以请慎重地修改分区列的数据类型。

关于分区列的类型截断与类型转换的说明

分区列类型截断

SQL在查询或插入时，若指定分区列的常量表达式超过了分区列类型所能表达的范围，PolarDB-X会先产生类型截断，然后再使用类型截断后的值进行路由计算。

例如：tbl_smallint表分区列类型是smallint，smallint的正常取值范围是[-32768, 32767]。因此，如果insert的值超过smallint的范围（例如，12745678或-12345678），则会被截断类型的最大值或最小值（32767或-32768），如下所示 。 

show create table tbl_smallint;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE        | CREATE TABLE                                                                                                                                                                        |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_smallint | CREATE TABLE `tbl_smallint` (
    `a` smallint(6) NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 128 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

insert into tbl_smallint values (12345678),(-12345678);
Query OK, 2 rows affected (0.07 sec)

select * from tbl_smallint;
+--------+
| a      |
+--------+
| -32768 |
|  32767 |
+--------+
2 rows in set (3.51 sec)

explain select * from tbl_smallint where a=12345678;
+------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                            |
+------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_smallint[p117]", sql="SELECT `a` FROM `tbl_smallint` AS `tbl_smallint` WHERE (`a` = ?)") |
| HitCache:false                                                                                                   |
| Source:PLAN_CACHE                                                                                                |
| TemplateId: afb464d5                                                                                             |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.16 sec)

explain select * from tbl_smallint where a=32767;
+------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                            |
+------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_smallint[p117]", sql="SELECT `a` FROM `tbl_smallint` AS `tbl_smallint` WHERE (`a` = ?)") |
| HitCache:true                                                                                                    |
| Source:PLAN_CACHE                                                                                                |
| TemplateId: afb464d5                                                                                             |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.03 sec)

同理，如果查询的常量值超过类型范围，也会按截断后的值进行路由。因此，对于tbl_smallint表，a=12345678与a=32767的分区路由结果是完全相同的。

分区列类型转换

SQL在查询或插入时，若指定分区列的常量表达式与分区列的类型不一致，PolarDB-X会先对常量表达式进行隐式类型转换，然后再使用类型转换后的值进行路由计算。但是，类型转换也有可能会出现转换失败的情况，例如，字符串abc无法转换为整型。

对于分区列出现类型转换及其失败的情况，PolarDB-X按DQL、DML、DDL会有不同的行为：

  • DQL（特指WHERE条件表达式中的分区列的类型转换）
    • 类型转换成功：按类型转换后的值进行分区路由；
    • 类型转换失败：该分区列条件会被直接忽略，走全表扫描。
  • DML（特指Insert或Replace）
    • 类型转换成功：按类型转换后的值进行分区路由；
    • 类型转换失败：直接报错，拒绝执行。
  • DDL（特指分区表相关的DDL，如建表、分裂等）
    • 类型转换成功：直接报错，拒绝执行，DDL不允许出现类型转换；
    • 类型转换失败：直接报错，拒绝执行。

与MySQL分区表的语法差异

差异点MySQL PolarDB-X
分区键包含主键强制要求。不要求。
Key分区路由算法：按分区数目取模。路由算法：一致性哈希算法。
Hash分区
  1. 路由算法：按分区数目取模；
  2. 不支持多分区列；
  3. 支持LINEAR HASH分区；
  4. 单列分区键下，Hash(col)与Key(col)的路由算法不同。
  1. 路由算法：一致性哈希算法；
  2. 支持多分区列（扩展语法）；
  3. 不支持LINEAR HASH分区；
  4. 单列分区键下，Hash(col)与Key(col)的路由算法相同。
分区函数支持。PARTITION BY HASH(expr(col)) ..., expr可以是常见的计算表达式，如YEAR(col) + 1。有限地支持。PARTITION BY HASH(expr(col))，expr仅限于以下函数，且不允许表达式中出现其它计算操作（如 +,-,*,/ 等）：
  • YEAR
  • MONTH
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • TO_DAYS
  • TO_MONTHS
  • TO_WEEKS
  • TO_SECONDS
  • UNIX_TIMESTAMP
  • SUBSTR/SUBSTRING
分区列类型Key分区支持所有数据类型。Key分区共支持整数类型、时间类型与字符类型三类。
分区列字符集支持所有常见字符集。仅支持3种字符集：
  • utf8
  • utf8mb4
  • gbk
二级分区支持。暂不支持。