PolarDB:CREATE TABLE (mode AUTO)

PolarDB-X vous permet de créer des tables partitionnées afin de répondre à vos besoins métier en spécifiant une clause de partitionnement. PolarDB-X prend en charge les quatre types de partitionnement suivants :

• Partitionnement par hachage : cette stratégie achemine les données en appliquant un algorithme de hachage cohérent intégré à la valeur d'une colonne de partition ou à l'expression d'une fonction de partition. Le partitionnement par hachage se subdivise en deux stratégies, Key partitioning et Hash partitioning, selon qu'une expression de fonction de partition ou plusieurs colonnes de partition peuvent être utilisées comme clé de partition.

• Partitionnement par plage : cette stratégie achemine les données en comparant la valeur d'une colonne de partition ou d'une expression de fonction de partition avec des plages de partition prédéfinies. Le partitionnement par plage se subdivise en deux stratégies, Range Columns partitioning et Range partitioning, selon qu'une expression de fonction de partition ou plusieurs colonnes de partition peuvent être utilisées comme clé de partition.

• Partitionnement par liste : à l'instar du partitionnement par plage, cette stratégie achemine les données en vérifiant si la valeur d'une colonne de partition ou d'une expression de fonction de partition figure dans une liste de valeurs prédéfinie pour une partition. Le partitionnement par liste se subdivise en deux stratégies, List Columns partitioning et List partitioning, selon que plusieurs colonnes de partition sont utilisées comme clé de partition et selon la manière dont elles le sont.

• CoHash. Il s'agit d'une stratégie de partitionnement par hachage étendue proposée par PolarDB-X, conçue pour des scénarios d'application courants et spécifiques. Cette stratégie permet le partitionnement horizontal d'une table basé sur plusieurs colonnes de partition corrélées.

Type de hachage

Dans PolarDB-X, il existe deux types de partitionnement basés sur le hachage : le partitionnement par hachage et le partitionnement par clé. Tous deux font partie de la syntaxe standard de partitionnement dans MySQL natif. Pour activer des fonctionnalités flexibles de gestion des partitions, telles que le fractionnement, la fusion et la migration des partitions, ainsi que pour prendre en charge le fractionnement des points chauds pour les clés de partition vectorielles, PolarDB-X redéfinit le routage pour le partitionnement par hachage et par clé ; bien que la syntaxe reste compatible avec la syntaxe MySQL CREATE TABLE, l'implémentation sous-jacente du partition routing diffère de celle de MySQL. Le tableau suivant décrit les différences entre le partitionnement par clé et le partitionnement par hachage :

Exemple 1-1 : Partition Key

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;

## This query triggers partition pruning and scans only a single partition.
SELECT id from key_tbl where name='Jack';

Si la première colonne de partition, name, provoque un déséquilibre des données ou un point chaud de données, vous pouvez effectuer un fractionnement de partition en utilisant la prochaine colonne de partition, telle que id. Pour plus de détails, consultez Modifier une partition au niveau du groupe de tables (mode AUTO).

Si une clé de partition vectorielle avec N colonnes de partition utilise ses K premières colonnes pour le routage (1 ≤ K ≤ N), l'inclusion de conditions sur les colonnes de partition préfixées (les K premières colonnes) dans la clause WHERE permet l'élagage de partition.

Exemple 1-2 : Partitionnement Hash

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;

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;

Actuellement, PolarDB-X prend en charge les fonctions de partitionnement suivantes :

• YEAR

• MONTH

• DAYOFMONTH

• DAYOFWEEK

• DAYOFYEAR

• TO_DAYS

• TO_MONTHS

• TO_WEEKS

• TO_SECONDS

• UNIX_TIMESTAMP

• SUBSTR/SUBSTRING

Pour les fonctions SUBSTR/SUBSTRING, la clé de partition doit être de type chaîne ; pour toutes les autres fonctions de partition, elle doit être de type temporel (tel que DATE, DATETIME ou TIMESTAMP).

Exemple 1-3 : Extension du partitionnement Hash

Cette rubrique explique la syntaxe DDL, les clauses et les paramètres pour la création de tables partitionnées dans les bases de données PolarDB-X en mode AUTO.

Instructions

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 options
global_secondary_index_option:
    [COVERING (col_name,...)]
    [partition_options]
    [VISIBLE|INVISIBLE]

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

table_option: {
# Specify a table group
    TABLEGROUP [=] value,...,}
    
# Partitioned table type definition
table_partition_definition:
		single
  |	broadcast
  | partition_options

# Partition definition
partition_options:
	partition_columns_definition
	[subpartition_columns_definition]
	[subpartition_specs_definition] /* Defines templated subpartitions. */ 
  partition_specs_definition 

# Partition key 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 key definition
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

# Partitioning functions
partition_func:
    YEAR
  | TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING
  | RIGHT
  | LEFT

# Partition specification
partition_specs_definition:
	hash_partition_list
  | range_partition_list
  | list_partition_list

# Subpartition specification
subpartition_specs_definition:
	hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

# HASH/KEY partition definition
hash_partition_list:
	  /* For HASH partitioning, individual partition definitions are optional. */
	| ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options] /* For partitioned tables that have no subpartitions or use templated subpartitions. */
  | PARTITION partition_name subpartitions_count [subpartition_specs_definition] /* Defines non-templated subpartitions within a partition. */

# HASH/KEY subpartition definition
hash_subpartition_list:
  | empty
  | ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
	SUBPARTITION subpartition_name [partition_spec_options]

# RANGE/RANGE COLUMNS partition definition
range_partition_list:
    ( range_partition [, range_partition, ... ] )

range_partition:
    	PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /* For partitioned tables that have no subpartitions or use templated subpartitions. */
    |	PARTITION partition_name VALUES LESS THAN (range_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /* Defines non-templated subpartitions within a partition. */
   
# RANGE/RANGE COLUMNS subpartition definition
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 /* Defines a MAXVALUE partition for RANGE partitioning. */
	| expr /* Boundary value for a single partition key */
	| value_list /* Boundary values for multiple partition keys */

# LIST/LIST COLUMNS partition definition
list_partition_list:
    (list_partition [, list_partition ...])

list_partition:
    	PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options] /* For partitioned tables that have no subpartitions or use templated subpartitions. */
    |	PARTITION partition_name VALUES IN (list_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /* Defines non-templated subpartitions within a partition. */

# LIST/LIST COLUMNS subpartition definition
list_subpartition_list:
	(list_subpartition [, list_subpartition ...])

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

list_bound_value:
		default  /* Defines a DEFAULT partition for LIST partitioning. */
	|	value_set

value_set:
	  value_list  /* Set of values for a single partition key */
	| (value_list) [, (value_list), ...] /* Sets of values for multiple partition keys */

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(...)

Exemple 3-1 : Partitionnement List Columns

Le list columns partitioning prend en charge les vector partition keys. Par exemple, le list columns partitioning vous permet de partitionner les commandes par pays et par ville. La table creation syntax est la suivante :

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','New York'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russia','Moscow'))
);

Exemple 3-2 : Partitionnement List

Bien que le list partitioning ne prenne en charge qu'une single-column partition key, vous pouvez utiliser des partition function expressions telles que YEAR, MONTH, DAYOFMONTH, TO_DAYS et TO_SECONDS pour convertir les valeurs d'une partition column de time-type en un integer type.

Par exemple, pour créer une list partition basée sur l'année de la colonne order_time, utilisez la syntaxe create table suivante :

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

Exemple 3-3 : Partitions List Columns et List avec une partition par défaut

PolarDB-X permet de créer des list columns partitions et des list partitions incluant une default partition vers laquelle sont routées les données non définies dans les regular partitions.

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','New York'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russia','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)
);

Limites

• Restrictions relatives aux types de données

  • Types entiers : BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT et TINYINT UNSIGNED.

  • Types temporels : DATETIME et DATE.

  • Types chaîne : CHAR et VARCHAR.

• Restrictions syntaxiques

  • Le partitionnement List Columns ne prend pas en charge le type de données TIMESTAMP.

  • Le partitionnement List prend uniquement en charge les types entiers.

  • Ni le partitionnement List Columns ni le partitionnement List ne prennent en charge le fractionnement des points chauds.

  • Par défaut, le nombre maximal de partitions est de 8 192.

  • Par défaut, le nombre maximal de colonnes de partition est de 5.

Type CoHash

La stratégie de partitionnement CoHash de PolarDB-X est spécifique à PolarDB-X.

Exigences de version

Utilisez la version 5.4.18-17047709 ou ultérieure.

Cas d'utilisation

Cette stratégie de partitionnement est couramment utilisée dans les scénarios métier suivants :

Si une table possède des colonnes corrélées, telles que c1 et c2 dont les quatre derniers caractères sont toujours identiques, vous pouvez effectuer un partitionnement horizontal sur ces deux colonnes afin d'acheminer les requêtes SQL comportant une condition d'égalité sur l'une ou l'autre colonne vers une seule partition.

Par conséquent, cette stratégie de partitionnement exige que les valeurs restent cohérentes entre plusieurs colonnes de partition au sein d'une même table partitionnée.

Exemple 4-1 : Définir la colocalisation avec des fonctions de partition indépendantes

Supposons que votre application dispose d'une table orders où les colonnes order_id et buyer_id partagent les six derniers chiffres identiques pour chaque ligne. Pour partitionner cette table selon ces chiffres partagés et garantir qu'une requête d'égalité sur l'une ou l'autre colonne soit routée vers la même partition, utilisez la syntaxe suivante :

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) /* The last 6 characters of order_id */,
 	RIGHT(`buyer_id`,6) /* The last 6 characters of buyer_id */
) 
PARTITIONS 8;

Exemple 4-2 : Utilisation du sucre syntaxique Range_Hash pour la migration des utilisateurs de la version 1.0 vers la 2.0

Pour une migration de la version 1.0 vers la 2.0, considérons une table orders fragmentée par base de données et par table à l'aide de range_hash, définie comme suit : DBPARTIITION BY RANGE_HASH(. Sa table partitionnée correspondante dans la base de données AUTO 2.0 est définie comme suit :

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 convertit automatiquement la syntaxe RANGE_HASH en une définition de partition CO_HASH. Par exemple, RANGE_HASH(order_id, buyer_Id, 6) devient la définition CO_HASH suivante :

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) /* Uses the last 6 characters of order_id */,
 	RIGHT(`buyer_id`,6) /* Uses the last 6 characters of buyer_id */
) 
PARTITIONS 8;

Différences clés par rapport au partitionnement hash/key

Étant donné que les stratégies de partitionnement CoHash et hash/key sont similaires, cette section décrit leurs principales similitudes et différences d'utilisation.

Considérations

• Votre service doit maintenir la relation coordonnée entre les valeurs des colonnes de partition ; PolarDB-X valide uniquement les résultats de routage. Lorsque vous utilisez le partitionnement CO_HASH, PolarDB-X vérifie que toutes les valeurs de colonne de partition spécifiées pour une ligne sont acheminées vers le même shard. Toutefois, il ne valide pas si les valeurs elles-mêmes respectent la relation coordonnée. Cette application relève de la responsabilité de votre service.

  Par exemple, supposons que votre service exige que les quatre derniers caractères des colonnes c1 et c2 soient identiques. Si une valeur pour c1, telle que 100234, et une valeur pour c2, telle que 1320, sont toutes deux acheminées vers le shard 0, PolarDB-X autorise l'opération insert (c1,c2) values (100234,1320) car le routage est cohérent. Cependant, cette opération viole la règle de service, car les quatre derniers caractères de c1 (0234) et de c2 (1320) ne sont pas les mêmes.

• Restrictions DML sur la modification des colonnes de partition Étant donné que le partitionnement CO_HASH met en corrélation les valeurs de plusieurs colonnes de partition, PolarDB-X applique les restrictions suivantes aux opérations DML qui modifient ces colonnes afin d'éviter une distribution incorrecte des données :

  • Pour les instructions insert et replace, si les valeurs des différentes colonnes de partition dans une seule ligne de la clause VALUES résolvent vers des partitions différentes, PolarDB-X rejette l'opération.

  • Pour les instructions update et upsert, vous devez modifier toutes les colonnes de partition simultanément dans la clause SET. Par exemple, si c1 et c2 sont des colonnes de partition, vous devez utiliser une instruction telle que UPDATE t1 SET c1='xx',c2='yy' WHERE id=1. Si les nouvelles valeurs des différentes colonnes de partition dans une seule ligne résolvent vers des partitions différentes, PolarDB-X rejette l'instruction.

  • Lorsque CO_HASH est la stratégie de partitionnement d'un index secondaire global (GSI), PolarDB-X rejette également toute opération DML sur la table principale qui entraîne la résolution des valeurs des différentes colonnes de partition dans une seule ligne GSI vers des partitions différentes.

• Gestion des zéros non significatifs pour les types entiers. Étant donné que les partition columns dans une stratégie CO_HASH sont corrélées, vous les définissez souvent à l'aide d'une partition function telle que SUBSTR, LEFT ou RIGHT. Ce processus peut entraîner l'apparition de zéros non significatifs lors de la troncature des entiers. Par exemple, supposons que votre logique métier exige que les quatre derniers caractères de c1 et c2 soient identiques. Si c1 vaut 1000034, les quatre derniers caractères correspondent à la string '0034'. Pour une partition column de integer type, CO_HASH convertit automatiquement toute valeur tronquée vers le integer type de cette colonne avant le routing. Ainsi, CO_HASH transforme la string '0034' en l'entier 34, calcule la hash value et achemine la ligne. Ce processus gère automatiquement les zéros non significatifs.

Limitations des fonctions de partitionnement

• RIGHT

• LEFT

• SUBSTR

Restrictions relatives aux types de données

• types entiers : BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT et TINYINT UNSIGNED

• type à virgule fixe : DECIMAL (avec une échelle de 0)

• types temporels : non pris en charge

• types chaîne : CHAR et VARCHAR

Restrictions syntaxiques

• Vous ne pouvez pas utiliser de fonctions de partitionnement imbriquées, telles que SUBSTR(SUBSTR(c1,-6),-4), sur une colonne de partitionnement.

• Lorsque vous utilisez le sucre syntaxique RANGE_HASH, le paramètre de longueur ne doit pas être négatif.

• Toutes les colonnes de partitionnement doivent avoir exactement le même type de données, y compris les propriétés suivantes :

  • Jeu de caractères et interclassement

  • Longueur et précision

• Par défaut, le nombre maximal de partitions est de 8 192.

• Par défaut, le nombre maximal de colonnes de partitionnement est de 5.

Sous-partitionnement

À l'instar de MySQL, PolarDB-X prend en charge la syntaxe de sous-partitionnement pour créer des tables partitionnées avec des sous-partitions. Le sous-partitionnement divise davantage chaque partition en fonction d'une colonne de partitionnement et d'une stratégie de partitionnement spécifiées.

• Dans une table partitionnée à deux niveaux, chaque partition de premier niveau est une partition logique qui correspond à un ensemble de partitions de second niveau.

• Chaque partition de second niveau est une partition physique qui correspond à un shard de table physique spécifique sur un nœud DN.

Modèles avec et sans gabarit

Les partitions de second niveau de PolarDB-X se divisent en deux catégories principales : avec gabarit (templated) et sans gabarit (non-templated) :

• sous-partition avec gabarit : le nombre de sous-partitions et leurs valeurs limites de partition sont identiques pour chaque partition.

• sous-partition sans gabarit : le nombre de sous-partitions et leurs valeurs limites de partition peuvent différer d'une partition à l'autre.

Restrictions syntaxiques

• Par défaut, une table partitionnée utilisant le sous-partitionnement ne peut pas comporter plus de 8 192 partitions physiques.

• Lorsque vous utilisez des partitions sans gabarit, tous les noms de sous-partitions doivent être uniques et ne doivent correspondre à aucun nom de partition.

• Lorsque vous utilisez des partitions avec gabarit, tous les noms de sous-partitions dans le gabarit doivent être uniques et ne doivent correspondre à aucun nom de partition.

• Avec le sous-partitionnement, le nombre total de partitions physiques dans une table correspond au nombre total de sous-partitions across toutes les partitions. Ce nombre peut augmenter rapidement. Par conséquent, gérez avec soin le nombre de partitions et de sous-partitions afin d'éviter une dégradation des performances due à un sur-partitionnement ou des erreurs liées au dépassement de la limite totale de partitions.

Exemple 5-1 : Partition secondaire avec gabarit

/*
 * This example defines templated subpartitions by using a LIST-KEY composite strategy.
 * The table is first partitioned by LIST COLUMNS into three partitions.
 * Each partition is then subpartitioned by KEY into four subpartitions, resulting in a total of 12 physical partitions.
*/
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)
);

Exemple 5-2 : Sous-partition sans gabarit

/*
 * This example uses a LIST-KEY composite strategy to define non-templated subpartitions.
 * The table is partitioned by LIST COLUMNS into three first-level partitions, each of which is then subpartitioned by KEY.
 * The first-level partitions have 2, 3, and 4 subpartitions, respectively.
 * This results in a total of 9 physical partitions (2 + 3 + 4).
*/
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 (('Russia','Moscow')) SUBPARTITIONS 3,
  PARTITION pd VALUES IN (DEFAULT) SUBPARTITIONS 4
);

Partitionnement automatique

SET GLOBAL AUTO_PARTITION=true;

Lorsque le partitionnement automatique est activé :

• Lorsque vous créez une table sans spécifier de clé de partitionnement, PolarDB-X partitionne la table par défaut en utilisant sa clé primaire. Si la table ne possède pas de clé primaire, il utilise la clé primaire implicite. Ce processus utilise le partitionnement KEY, ce qui donne une table partitionnée de niveau 1.

  Par défaut, le nombre de partitions est huit fois supérieur au nombre de nœuds logiques dans l'instance. Par exemple, si une instance PolarDB-X est créée avec deux nœuds logiques, le nombre de partitions par défaut est de 16.

• En plus du partitionnement de la table principale, PolarDB-X partitionne également tous ses index par défaut. La clé de partitionnement d'un index est constituée des colonnes d'index et des colonnes de clé primaire de la table.

L'exemple suivant illustre la syntaxe MySQL standard pour la création d'une table, où id est la clé primaire et name est la colonne indexée :

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

L'exécution de l'instruction SHOW CREATE TABLE affiche la syntaxe standard de création de table MySQL et masque automatiquement toutes les informations de partitionnement :

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)

L'exécution de l'instruction SHOW FULL CREATE TABLE affiche toutes les informations de partitionnement pour la table principale et ses tables d'index :

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
/* table group = `tg108` */ |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

CREATE TABLE single_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) SINGLE;

CREATE TABLE broadcast_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) BROADCAST;

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;

Contrairement au partitionnement par clé, le partitionnement par hachage utilise une clé de partition vectorielle, où toutes les colonnes de partition contribuent aux calculs de hachage et de routage lors du routage des partitions. Par conséquent, pour activer l'élimination des partitions (partition pruning), la clause WHERE d'une requête SQL doit spécifier des conditions d'égalité pour toutes les colonnes de partition. Par exemple, SQL1 peut déclencher l'élimination des partitions sur la table hash_tbl2, mais pas SQL2 :

##SQL1 (This query triggers partition pruning, scanning only a single partition)
SELECT id from hash_tbl2 where name='Jack' and birthday='1990-11-11';

##SQL2 (This query does not trigger partition pruning, resulting in a full partition scan)
SELECT id from hash_tbl2 where name='Jack';

Étant donné que le partitionnement par hachage utilise l'intégralité de la clé de partition pour calculer la valeur de hachage en amont, il distribue théoriquement les données de manière plus uniforme que le partitionnement par clé utilisant une clé de partitionnement vectorielle. Toutefois, il ne prend pas en charge le fractionnement des points chauds (hotspot splitting), car aucune colonne supplémentaire n'est disponible pour diviser une partition chaude.

Limitations

• Limitations relatives aux types de données

  • Types entiers : BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT et TINYINT UNSIGNED.

  • Types temporels : DATETIME, DATE et TIMESTAMP.

  • Types chaîne : CHAR et VARCHAR.

• Limitations syntaxiques

  • Lors de l'utilisation d'une fonction de partition avec une clé de partition monocolumnne pour un partitionnement par hachage, la clé doit être de type temporel.

  • Une clé de partition vectorielle pour un partitionnement par hachage ne prend pas en charge les fonctions de partition ni le fractionnement des points chauds.

  • Par défaut, le nombre maximal de partitions est de 8 192.

  • Par défaut, le nombre maximal de colonnes de partition est de 5.

Uniformité des données

• L'algorithme de hachage cohérent intégré pour le partitionnement par clé et le partitionnement par hachage est MurmurHash3, un algorithme haute performance présentant une faible probabilité de collision.

• Avec MurmurHash3, la distribution des données pour le partitionnement par clé et le partitionnement par hachage devient généralement équilibrée uniquement lorsque le nombre de valeurs distinctes de la clé de partition (N) dépasse 3 000. Plus la valeur de N est élevée, plus la distribution des données est équilibrée.

Type Range

Dans PolarDB-X, il existe deux types de partitionnement par plage : le partitionnement par plage (range partition) et le partitionnement par colonnes de plage (range columns partition). Tous deux font partie de la syntaxe standard de partitionnement de MySQL natif. Le tableau suivant compare ces deux types de partitionnement.

Exemple 2-1 : Partitionnement par colonnes de plage (Range Columns)

Le partitionnement Range Columns prend en charge une clé de partition vectorielle, mais pas les fonctions de partition. Par exemple, pour partitionner une table selon l'ID de commande et la date de commande, spécifiez la syntaxe CREATE TABLE suivante :

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

Exemple 2-2 : Partitionnement par plage (Range)

Le partitionnement Range ne prend en charge qu'une clé de partition à colonne unique. Toutefois, pour une colonne de partition de type datetime, vous pouvez utiliser des fonctions de partition telles que YEAR, TO_DAYS, TO_SECONDS ou MONTH afin de convertir ses valeurs en entiers.

Par exemple, pour créer des partitions trimestrielles sur la colonne order_time à l'aide du partitionnement Range, utilisez la syntaxe suivante :

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

Limites

• Restrictions relatives aux types de données

  • Types d'entiers : BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT et TINYINT UNSIGNED.

  • Types datetime : DATETIME et DATE.

  • Types de chaînes : CHAR et VARCHAR.

• Restrictions syntaxiques

  • Le partitionnement RANGE COLUMNS et le partitionnement RANGE ne prennent pas en charge l'utilisation d'une valeur NULL comme valeur limite.

  • Le partitionnement RANGE COLUMNS ne prend pas en charge le type de données TIMESTAMP.

  • Le partitionnement RANGE ne prend en charge que les types d'entiers. Si la clé de partition utilise le type TIMESTAMP, vous devez utiliser la fonction UNIX_TIMESTAMP pour garantir la cohérence du fuseau horaire.

  • Le partitionnement RANGE ne prend pas en charge le fractionnement des points chauds.

  • Lors des requêtes, les valeurs NULL sont traitées comme la valeur minimale pour le routage des partitions.

  • Par défaut, le nombre maximal de partitions est de 8 192.

  • Par défaut, le nombre maximal de colonnes de partitionnement est de 5.

Type List

À l'instar du range type, PolarDB-X subdivise davantage la list partitioning policy en deux types : list partitioning et list columns partitioning. Ces deux types utilisent la syntaxe de partitionnement standard de MySQL natif. De plus, PolarDB-X prend en charge une default partition pour le list partitioning et le list columns partitioning. Ce tableau compare ces deux types :

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)
            

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)

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)

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)