如何使用DDL語句建立分區表 - PolarDB

本文主要介紹使用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。

如果分區表的主鍵不含分區鍵，且不是自增主鍵，需要業務保證主鍵的唯一性。
若建立表時要使用二級分區的相關功能，執行個體版本必須為5.4.17-16952556及以上。

文法

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_columns_definition
	[subpartition_columns_definition]
	[subpartition_specs_definition]/*用於定義模板化二級分區*/ 
  partition_specs_definition 

# 一級分區的分區列定義
partition_columns_definition:
		PARTITION BY
          HASH({column_name | partition_func(column_name)}) partitions_count
        | KEY(column_list) partitions_count
        | RANGE ({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)
        | LIST ({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list) 
        | CO_HASH({column_expr_list}) partitions_count 

# 二級分區的分區列定義
subpartition_columns_definition:
		SUBPARTITION BY
          HASH({column_name | partition_func(column_name)}) subpartitions_count
        | KEY(column_list) subpartitions_count
        | RANGE ({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)
        | LIST ({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)
        | CO_HASH({column_expr_list}) partitions_count

column_expr_list:
	{column_name | partition_func(column_name)},{column_name | partition_func(column_name)}[,{column_name | partition_func(column_name)},...]

partitions_count:
   PARTITIONS partition_count

subpartitions_count:
   SUBPARTITIONS partition_count

# 分區函數定義
partition_func：
    YEAR
  | TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING
  | RIGHT
  | LEFT


# 一級分區的三種分區類型定義
partition_specs_definition:
	hash_partition_list
  | range_partition_list
  | list_partition_list

# 二級分區的三種分區類型定義
subpartition_specs_definition:
	hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

# 一級分區的Hash/Key分區定義
hash_partition_list:
	  /*hash允許不指定各個具體分區定義*/
	| ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options] /*適用於純一級分區或使用模板化子分區*/
  | PARTITION partition_name subpartitions_count [subpartition_specs_definition] /*適用於定義一級分區下的非模板化子分區*/

# 二級級分區的Hash/Key分區定義
hash_subpartition_list:
  | empty
  | ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
	SUBPARTITION subpartition_name [partition_spec_options]

# 一級分區的Range/Range Columns分區定義
range_partition_list:
    ( range_partition [, range_partition, ... ] )

range_partition:
    	PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /*適用於純一級分區或使用模板化子分區*/
    |	PARTITION partition_name VALUES LESS THAN (range_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /*適用於定義一級分區下的非模板化子分區*/
   
# 二級分區的Range/Range Columns分區定義
range_subpartition_list:
	( range_subpartition [, range_subpartition, ... ] )

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]
  
range_bound_value:
	  maxvalue /*適用於定義range的maxvalue分區*/
	| expr /*適用於使用單個分區列時range邊界值*/
	| value_list /*適用於使用多個分區列時range邊界值*/

# 一級分區的List/List Columns分區定義
list_partition_list:
    (list_partition [, list_partition ...])

list_partition:
    	PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options] /*適用於純一級分區或使用模板化子分區*/
    |	PARTITION partition_name VALUES IN (list_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /*適用於定義一級分區下的非模板化子分區*/

# 二級分區的List/List Columns分區定義
list_subpartition_list:
	(list_subpartition [, list_subpartition ...])

list_subpartition:
	SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]

list_bound_value:
		default  /*適用於定義list的default分區*/
	|	value_set

value_set:
	  value_list  /*適用於使用單個分區列時values集合*/
	| (value_list) [, (value_list), ...] /*適用於使用多個分區列時的values集合*/

value_list:
	value [, value, ...]

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）個分區列便組成這個向量分區鍵的首碼分區列。
分區函數：將分區列作為一個函數的輸入參數，並將該函數的輸出結果作為原始值參與路由計算，該函數被稱為分區函數。
分區裁剪：根據分區定義及查詢條件，最大限度地過濾不需要掃描的分區的查詢最佳化手段。
熱點分裂：當向量分區鍵的首碼分區列存在訪問熱點或分布不均衡時，允許使用下一個分區列對熱點分區進行分裂，以達到負載平衡效果。
物理分區：在DN節點有一個物理分表與之相對應的分區，物理分區與物理分表一一對應。
邏輯分區：對應一個或多個物理分區的虛擬分區，相當於一個邏輯概念。例如，當使用二級分區構建分區表時，它的一級分區就是邏輯分區。

參數說明

參數	說明
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	用於指定分區表的所在儲存節點。

單表

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分區兩種分區策略。
CoHash類型。PolarDB-X還針對比較常見的特定的應用情境新擴充了一種新的名為CoHash的雜湊分割策略，該策略可有效解決一個表需要同時按多個不同的相互有協同關係的分區列進行水平資料分割的問題。

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個分區列參與路由計算；	按c1列的值，使用一致性雜湊演算法計算其雜湊c1_hash；按雜湊值 c1_hash進行分區路由。
Key（預設的分區策略）	向量分區鍵	否	PARTITION BY KEY(c1,c2,...,cn)	支援熱點分裂；建表後預設僅第1個分區列c1實際參與路由計算，熱點分裂時可以使用c2,...,cn等更多的分區列；最多允許n個分區列同時參與路由計算；分區列數目n預設不能超過5個。	將`(c1,c2,...,cn)`這N個列的值組成一組分區鍵向量，並使用一致性雜湊演算法為向量的各個列的值計算其雜湊值，從而將一個分區鍵向量映射成一個雜湊值向量`(c1_hash,c2_hash,...,cn_hash)`；將雜湊值向量`(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))		按c1列的值，使用YEAR分區Function Compute其對應的年份year；將年份year使用一致性雜湊演算法計算其雜湊值year_hash；按雜湊值year_hash進行分區路由。
	向量分區鍵	否	PARTITIONBY HASH(c1,c2,...,cn)	不支援熱點分裂；建表後，n個分區列將同時與路由；分區列數目n預設不能超過5個。	將`(c1,c2,...,cn)`這N個分區列的值組成一組向量，並使用一致性雜湊演算法，計算該向量對應雜湊值hashVal；按雜湊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進行分區分裂解決熱點）。	將`(c1,c2,...,cn)`這N個列的值組成一組分區鍵向量；根據這個分區鍵向量`(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	按c1列的值，使用YEAR分區Function Compute其對應的年份year；年份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) ), ...)	不支援熱點分裂	將`(c1,c2,...,cn)`這N個列的值組成一組分區鍵向量；根據這個分區鍵向量`(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個。

CoHash類型

PolarDB-X的CoHash類型分區策略是PolarDB-X所特有的分區策略。

版本要求

版本必須是5.4.18-17047709及以上。

適用情境

該分區策略常用於解決類似以下的業務情境：

使用者的業務表有一個或多個有協同關係的列（例如c1列與c2列，它們的後4位字元總是相同），使用者希望將該表同時按照c1列與c2列進行水平資料分割，並期望業務SQL查詢帶上c1列或c2列的等值條件，均能路由到相同的單個分區。

因此，使用該分區策略有個必要前提：使用者需要自我維護好分區表中同一個的多個分區列的取值上的協同關係。

樣本4-1 ：CoHash各個分區列獨立使用分區函數定義協同關係

假如業務有一張訂單表orders，它的每一行錄的order_id與buyer_id的後6位的數字總是相同的。那麼，如果使用者想對訂單表orders同時按order_id與buyer_id兩個列的後6位元字進行分區，並期望同一行order_id與buyer_id這兩個列的等值查詢條件均能路由到同一個分區的話，可以使用如下的文法定義：

CREATE TABLE t_orders(
 id bigint not null auto_increment, 
 order_id bigint, 
 buyer_id bigint,
 order_time datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT(`order_id`,6) /*取c1列的後6位字元*/,
 	RIGHT(`buyer_id`,6) /*取c2列的後6位字元*/
) 
PARTITIONS 8;

樣本4-2 ：使用Range_Hash文法糖，適配使用者1.0遷移2.0

假如業務想從1.0遷移至2.0，原來1.0有一張訂單表orders表並使用了range_hash分庫分表，其定義如下：DBPARTIITION BY RANGE_HASH(`order_id`,`buyer_id`, 6) ，那麼，該訂單表orders表在2.0的AUTO庫的對應分區表定義如下：

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY RANGE_HASH(order_id, buyer_Id,6) 
PARTITIONS 8;

PolarDB-X支援將RANGE_HASH文法自動轉換為對應的CO_HASH的分區定義，比如上述的SQL的RANGE_HASH(order_id, buyer_Id,6) 實質上會自動被轉換為以下的使用CO_HASH的定義：

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT(`order_id`,6) /*取c1列的後6位字元*/,
 	RIGHT(`buyer_id`,6) /*取c2列的後6位字元*/
) 
PARTITIONS 8;

與Hash/Key分區策略的主要區別

由於CoHash分區策略與前邊的Hash/Key分區策略有些類似，以下是它們的一些主要用法異同的對比。

主要區別點	CO_HASH	KEY	Hash
文法樣本	PARTITION BY CO_HASH(c1, c2) PARTITOINS 8	PARTITION BY KEY(c1, c2) PARTITOINS 8	PARTITION BY HASH(c1, c2) PARTITOINS 8
單列分區鍵	不支援	支援	支援
向量分區鍵	支援	支援。	支援
向量分區列是否允許使用分區函數	允許。例如PARTITION BY CO_HASH( /取c1列的後4位字元/ RIGHT(c1, 4), /取c2列的後4位字元/ RIGHT(c2, 4) ) PARTITOINS 8	不允許	不允許
分區列之間的關係	協同關係。同一個的分區列取值的協同關係由業務提供並負責維護。例如： c1 與 c2 的取值總是相同適合使用 CO_HASH(c1, c2) c1 與 c2 的後4位字元總是相同適合使用 CO_HASH(RIGHT(c1,4), RIGHT(c2,4))）	類似聯合索引的首碼關係。	類似聯合索引的首碼關係。
首碼列等值查詢分區裁剪及樣本	支援。例如： c1='x': 支援分區裁剪並路由到單個分區； c1='x' and c2='y': 支援分區裁剪並路由到一個或0個分區（如果 c1='x'與c2='y'的路由分區不一樣，則返回0個分區）。	支援。例如： c1='x'：支援分區裁剪並路由到一個或多個分區（如果x值進行了熱點分列，會返回多個分區）； c1='x' and c2='y'：支援分區裁剪並路由到單個分區。	不支援，必須帶上全分區列等值條件才支援分區裁剪。例如： c1='x': 無法裁剪並全分區掃描； c1='x' and c2='y': 支援分區裁剪並路由到單個分區。
非首碼列等查詢分區裁剪及樣本	支援。所有分區列的等值條件均支援獨立的分區裁剪。例如： c2='x': 支援分區裁剪並路由到單個分區； c1='x' or c2='y': 支援分區裁剪並路由到1個或2個分區（如果 c1='x'與c2='y'的路由分區是一樣，則返回1個分區，否則掃描2個分區）。	不支援。非首碼分區等值條件必須全分區掃描。例如： c2='x': 全分區掃描； c1='x' or c2='y': 全分區掃描。	不支援。非首碼分區等值條件必須全分區掃描。例如： c2='x': 全分區掃描； c1='x' or c2='y': 全分區掃描。
範圍查詢	不支援。全分區掃描。	不支援。全分區掃描。	不支援。全分區掃描。
路由描述（點查）	提取c1列（其他的分區列與之一樣）的等值查詢的原始值 v1 ；如果c1列使用了分區函數，則計算v1列的分區函數取值，f1 = partFunc(v1)，否則取 f1 = v1; 使用一致性雜湊演算法計算 f1 的雜湊值 c1_hash（long類型的整數）；按雜湊值c1_hash 進行分區路由。	可參考前邊的“Key分區與Hash 分區”的描述，此處忽略。	可參考前邊的“Key分區與 Hash分區”的描述，此處忽略。
熱點分裂	不支援。無法對某個具體的熱點值（比如c1='88'）進行進一步熱點分裂	支援	不支援
分區管理（常見的分區分裂、合并與遷移等）	支援	支援	支援
二級分區	支援	支援	支援

注意事項

分區列取值的協同關係必須由業務保證，分區表僅校正路由結果。CO_HASH由於多個分區列之間的取值存在著由業務維護的協同的關係。因此，對於CO_HASH分區表的每一行記錄的插入，不同分區列的值的分區路由結果要求必須是一致的。但是，即使同一行的記錄不同的分區列的取值的分區路由結果完全一致，也不一定能保證這些分區列的協同關係不被破壞。因此，分區列之間的協同關係必須由使用者自行保證，PolarDB-X只負責檢驗路由結果，不負責校正資料本身的協同關係。
例如業務定義c1與c2的後4位字元是相同的，現在假如c1=1001234與c2=1320都能路由分區0，那 insert (c1,c2) values (100234,1320)是允許的，但此時c1與c2的後4位並不相同。

DML修改分區列限制。由於CO_HASH的多個分區列之間的取值存在協同的關係，為防止資料分布錯誤，PolarDB-X對於DML關於CO_HASH分區列的值的修改有如下限制：
- 對於INSERT/REPLCAE語句，VALUES子句中同一行的不同分區列的值在路由計算後，如果其分區結果不一致，將被禁止插入並報錯。
- 對於UPDATE及UPSERT語句，SET子句在修改分區列的取值時，必須要對所有分區列同時進行修改。例如c1與c2是分區列，那麼應該是UPDATE t1 SET c1='xx',c2='yy' WHERE id=1 。如果SET子句在修改分區列後的值，並且會導致同一行的不同分區列的取值產生不同的分區路由結果的話，該UPDATE語句或UPSERT語句將被禁止並報錯。
- 如果使用了CO_HASH作為GSI的分區策略，那麼所有對主表的INSERT/UPDATE等操作，若該DML操作會導致主表的GSI表的同一行資料的不同分區列的取值產生不同的分區路由結果，那麼，該DML操作也將被禁止並報錯。

關於整數類型首碼0的說明。CO_HASH的分區列之間的有協同關係，所以它的分區列通常需要藉助使用SUBSTR/LEFT/RIGHT等分區函數進行定義。因此，一些整數類型的數字被截取後，容易出現首碼為0的情況。例如業務定義c1與c2的後4位字元是相同的，現在假如c1=1000034與c2=34， c1的後4位字元是'0034'。CO_HASH對於類型是整數類型的分區列，所有原始的數字被截取後，都會統一自動轉為分區列對應的整數類型再進行路由。因此，對於'0034' 的字串，它實際會被轉為整數34 再進行雜湊值計算並路由分區，從而自動處理首碼0。

分區函數使用限制

RIGHT
LEFT
SUBSTR

資料類型限制

整數類型： BIGINT/BIGINT UNSINGEDINT/INT/INT UNSINGED/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED
定點類型：DECIMAL（小數部分的有效位元必須為0）
時間類型：不支援
字串類型：CHAR/VARCHR

文法限制

分區列使用分區函數時，不允許嵌套多層的分區函數。例如使用類似SUBSTR(SUBSTR(c1,-6),-4)的定義。

使用RANGE_HASH文法糖時，最後的長度數字不能是負數。
所有分區列的類型必須完全一致，包括：
- 分區列類型的charset與collation；
- 分區列類型的長度定義或精度定義等。
預設最大分區數目不允許超過8192。
預設最大分區列數目不允許超過5個。

二級分區

與MySQL類似，PolarDB-X支援使用二級分區文法建立包含二級分區的分區表。二級分區就是允許對所有一級分區按指定分區列及分區策略繼續進行二次分區。

二級分區的每個一級分區實際變成一個邏輯分區，對應著一組二級分區的集合；
二級分區的每個二級分區實際上變成一個物理分區，對應著DN節點上的一個具體的物理分表。

模板化與非模板化

從整體功能上看，PolarDB-X二級分區分可為兩大類：模板化二級分區與非模板化二級分區：

模板化二級分區：各個一級分區之下的二級分區的分區數目及其分區邊界值始終一致；
非模板化二級分區：各個一級分區之下的二級分的分區數目及其分區邊界值允許不一致。

文法限制

使用二級分區的分區表，它的物理分區數目預設不允許超過8192；
使用非模板化一級分區，所有二級分區的分區名不允許重複，也不允許與一級分區名字有重複；
使用模板化一級分區，所有模板化的二級分區的分區名不允許重複，也不允許與一級分區名字有重複。
使用二級分區後，分區表的二級分區數目是所有的一級分區下的二級分區數目之和。因此，分區表的分區數將呈倍級上升。因此，請謹慎控制一級分區與二級分區的各自的分區數目，避免過度分區產生副作用，或超出分區總數限制而報錯。

樣本5-1：模板化二級分區

/*
 * 定義LIST-KEY的模板化子分區，
 * 一級分區按 LIST COLUMNS 分為3個分區，
 * 每個一級分區繼續按 KEY 策略分為4個二級分區，
 * 因此，總共會有 12個 物理分區
*/
CREATE TABLE sp_tbl_list_key_tp(
 id int, 
 country varchar(64),
 city varchar(64),
 order_time datetime not null,
 PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country,city) 
SUBPARTITION BY KEY(id) SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('China','Hangzhou')),
  PARTITION p2 VALUES IN (('Russian','Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

樣本5-2：非模板化二級分區

/*
 * 定義LIST-KEY的非模板化子分區，
 * 一級分區按 LIST COLUMNS 分為3個分區，
 * 每個一級分區繼續按 KEY 策略進行二級分區，
 * 各一級分區的二級分區數目分別定義為 2，3，4，
 * 因此，總共會有 9 個 物理分區
*/
CREATE TABLE sp_tbl_list_key_ntp(
 id int, 
 country varchar(64),
 city varchar(64),
 order_time datetime not null,
 PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country,city) 
SUBPARTITION BY KEY(id)
(
  PARTITION p1 VALUES IN (('China','Hangzhou')) SUBPARTITIONS 2,
  PARTITION p2 VALUES IN (('Russian','Moscow')) SUBPARTITIONS 3,
  PARTITION pd VALUES IN (DEFAULT) SUBPARTITIONS 4
);

預設自動分區

建表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。

手動分區

通過在代碼中指定分區列、分區函數、分區類型等建立的表為手動分區表，詳細類型請參見手動建立分區表（AUTO模式）。

資料類型說明

表 4. 各分區策略分區列資料類型的支援情況

資料類型		Hash類型			Range類型		List類型
		HASH		Key	Range	Range Columns	List	List Columns
		單區分列	多區分列	Key	Range	Range Columns	List	List Columns
數實值型別	TINYINT	支援	支援	支援	支援	支援	支援	支援
	TINYINT UNSIGNED	支援	支援	支援	支援	支援	支援	支援
	SMALLINT	支援	支援	支援	支援	支援	支援	支援
	SMALLINT UNSIGNED	支援	支援	支援	支援	支援	支援	支援
	MEDIUMINT	支援	支援	支援	支援	支援	支援	支援
	MEDIUMINT UNSIGNED	支援	支援	支援	支援	支援	支援	支援
	INT	支援	支援	支援	支援	支援	支援	支援
	INT UNSIGNED	支援	支援	支援	支援	支援	支援	支援
	BIGINT	支援	支援	支援	支援	支援	支援	支援
	BIGINT UNSIGNED	支援	支援	支援	支援	支援	支援	支援
定點類型	DECIMAL	支援（該類型作為分區列支援不能使用分區函數）	支援	支援	不支援	支援（該類型作為RangeColumns列時，小數位元必須為0）	不支援l	支援（該類型作為RangeColumns列時，小數位元必須為0
時間類型	DATE	支援（該類型的分區列支援使用分區函數）	支援	支援	支援（該類型分區列必須配套使用分區函數）	支援	支援（該類型分區列必須配套使用分區函數）	支援
	DATETIME	支援（該類型的分區列支援使用分區函數）	支援	支援	支援（該類型分區列必須配套使用分區函數）	支援	支援（該類型分區列必須配套使用分區函數）	支援
	TIMESTAMP	支援（該類型的分區列支援使用分區函數）	支援	支援	不支援	不支援	不支援	不支援
字串類型	CHAR	支援（該類型作為分區列支援不能使用分區函數）	支援	支援	不支援	支援	不支援	支援
字串類型	VARCHAR	支援（該類型作為分區列支援不能使用分區函數）	支援	支援	不支援	支援	不支援	支援
二進位類型	BINARY	支援（該類型作為分區列支援不能使用分區函數）	支援	支援	不支援	不支援	不支援	不支援
二進位類型	VARBINARY	支援（該類型作為分區列支援不能使用分區函數）	支援	支援	不支援	不支援	不支援	不支援

關於分區列的資料類型與路由計算的說明

分區表的路由計算是直接依賴於分區列的資料類型的，特別是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分區	路由演算法：按分區數目模數；不支援多分區列；支援LINEAR HASH分區；單列分區鍵下，Hash(col)與Key(col)的路由演算法不同。	路由演算法：一致性雜湊演算法；支援多分區列（擴充文法）；不支援LINEAR HASH分區；單列分區鍵下，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
二級分區	支援。	支援。