Partitionnement avec MySQL 5.1


1- Introduction

Après Oracle (le premier), puis Sybase 15.0, SQL Server 2005, MySQL 5.1 implémente également le partitionnement des données et indexes.

Cet article est un tour d'horizon du partitionnement avec MySQL 5.1 (types, gestion etc…).

2- Généralités sur la partitionnement avec MySQL 5.1

Le standard SQL ne fournit pas de fonctionnalités sur le stockage physique des données et pour cause, le langage SQL est conçu pour fonctionner indépendamment des structures de données et des supports de stockage pour les tables, les lignes et les colonnes.

La plupart des systèmes de gestion de bases de données avancés ont évolué pour contrôler la localisation physique des données (filesystems, hardware…).

Avec MySQL, bien avant le partitionnement, le moteur de stockage InnoDB supportait la notion de tablespace et la localisation physique des données pouvait être contrôlée grâce aux liens symboliques.

Le partitionnement avec MySQL 5.1 franchit une étape très importante dans le contrôle de la localisation logique et physique des données, autorisant la distribution de portions de tables sur différents supports de stockage.

La gestion des tables partitionnées est également bénéfique car seule une partie d'une table peut par exemple être sujette à une opération de maintenance (statistiques, réorganisation, archivage etc…), avantage majeur si la table présente une volumétrie importante.

Le partitionnement horizontal (partitionnement par lignes) est la grande nouveauté de MySQL 5.1. En revanche MySQL 5.1 ne supporte pas le partitionnement vertical (partitionnement par colonnes), ce type de partitionnement n'étant pas encore prévu au programme.

Le support du partitionnement est inclus dans les releases –max de MySQL 5.1 (les binaires –max 5.1 sont construits avec l'option –with-partition). Si le binaire MySQL est construit avec le support du partitionnement, il n'y a rien de spécial à configurer pour activer le partitionnement (par exemple, il n'y a pas d'entrées spéciales à écrire dans le fichier my.cnf).

La commande SHOW VARIABLES permet de déterminer si le serveur MySQL supporte le partitionnement :

mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

Si la variable have_partitioning n'est pas à YES, MySQL ne supporte pas le partitionnement. Avant la version MySQL 5.1.6, cette variable était appelée have_partition_engine.

Pour créer des tables partitionnées, la plupart des moteurs de stockage est supportée (MyISAM, InnoDB etc…), le moteur de partitionnement MySQL étant une couche séparée qui peut intéragir avec la plupart des moteurs de stockage de MySQL. Avec MySQL 5.1, toutes les partitions d'une même table partitionnée doivent avoir le même moteur de stockage : par exemple, il n'est pas possible d'utiliser le moteur de stockage MyISAM pour une partition et le moteur de stockage InnoDB pour une autre partition.

Le partitionnement MySQL ne peut être utilisé avec les moteurs de stockage MERGE ou CSV. Le partitionnement par clé (KEY partition) est supporté pour le moteur de stockage NDBCluster, mais pas les autres types de partitionnement.

Pour employer un moteur de stockage particulier pour une table partitionnée, il est seulement nécessaire d'utiliser l'option [STORAGE] ENGINE  comme cela serait fait une pour une table non partitionnée. Il faut garder à l'esprit que l'option [STORAGE] ENGINE doit être listée avant les options de partitionnement dans la création de la table avec la commande CREATE TABLE. Voici un exemple de création de table partitionée par hachâge et qui utilise le moteur de stockage InnoDB :

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;

La clause PARTITION peut inclure l'option [STORAGE] ENGINE, mais avec MySQL 5.1 cela n'a aucun effet, c'est le moteur de stockage de la table qui compte.

Le partitionnement s'applique à toutes les données et tous les indexes d'une table, il n'est pas possible de partitionner les données et pas les indexes, et vice-versa.

Les données et les indexes de chaque partition peuvent être physiquement contrôlés au niveau de leur localisation avec les options DATA DIRECTORY et INDEX DIRECTORY de la clause PARTITION dans la commande CREATE TABLE.

Par ailleurs, dans chaque partition, le nombre maximal et minimal de lignes peut être spécifié avec les options MAX_ROWS et MIN_ROWS de la clause PARTITION.

Le partitionnement inclut également les avantages ci-dessous :

3- Types de partitions

Cette section évoque les types de partitionnement disponibles avec MySQL 5.1. 4 types de partitionnement sont proposés :

Une utilisation courante du partitionnement dans les bases données consiste à ségréguer les données par date.

3-1- Partitionnement RANGE (partitionnement par intervalles)

Une table qui est partitionnée par intervalles est partitionnée de telle manière que chaque partition contient les lignes correspondant à un intervalle donné.

Les intervalles devraient être contigus mais ne doivent surtout pas se chevaucher. Ces intervalles sont données par l'opérateur VALUES LESS THAN.

Dans l'exemple qui suit, une table stocke les employés d'une chaîne de 20 Vidéo stores, chaque vidéo store étant numéroté de 1 à 20 (colonne store_id) :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
);

Cette table peut être partitionnée de différentes manières, dépendant des besoins. Une manière de partitionner pourrait concerner la colonne store_id. Si on décide de partitionner cette table en 4 partitions avec la clause PARTITION BY RANGE appliquée sur la colonne store_id :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
)
				
PARTITION BY RANGE (store_id) (
	PARTITION p0 VALUES LESS THAN (6),
	PARTITION p1 VALUES LESS THAN (11),
	PARTITION p2 VALUES LESS THAN (16),
	PARTITION p3 VALUES LESS THAN (21)
);

Dans ce schéma de partitionnement, les lignes correspondant aux employés travaillant dans les magasins 1 à 5 seront stockées dans la partition p0, les lignes de ceux travaillant dans les magasins 6 à 10 dans la partition p1 etc…

Chaque partition est définie en ordre croissant, du plus petit store_id au plus grand. C'est un prérequis de la syntaxe PARTITION BY RANGE.

Que se passe-t-il si un employé est inséré pour un identifiant store_id non défini dans les partitions (plus grand que 20 dans notre cas) ? Une erreur est alors générée, car dans le schéma de partitionnement, aucune règle n'est donnée au serveur pour la valeur 21. Cette erreur peut être évitée en utilisant la méthode « catchall » de la clause VALUES LESS THAN de la commande CREATE TABLE afin de gérer les valeurs plus hautes que les valeurs explicitement données pour les partitions.

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
        PARTITION p0 VALUES LESS THAN (6),
        PARTITION p1 VALUES LESS THAN (11),
        PARTITION p2 VALUES LESS THAN (16),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE représente la valeur entière la plus haute possible. La commande ALTER TABLE peut permettre d'ajouter de nouvelles partitions dans le futur pour les magasins 21-25, 26-30 etc…

Le partitionnement peut potentiellement être également positionné sur une des deux colonnes date de la table employees. Par exemple, on peut décider de partitionner sur l'année pour laquelle l'employé a quitter la société (colonne separated) :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
)
        
PARTITION BY RANGE (YEAR(separated)) (
	PARTITION p0 VALUES LESS THAN (1991),
	PARTITION p1 VALUES LESS THAN (1996),
	PARTITION p2 VALUES LESS THAN (2001),
	PARTITION p3 VALUES LESS THAN MAXVALUE
);

Dans ce schéma de partitionnement, les lignes des employés qui ont quitté la société avant 1991 seront dans la partition p0, entre 1991 et 1995, dans la partition p1 etc…

Le partitionnement par intervalles (RANGE) est très utile quand :

3-2- Partitionnement LIST (partitionnement par valeurs discrètes)

Le partitionnement par valeurs discrètes est très similaire au partitionnement par intervalles sur bien des points. Comme dans le partitionnement par intervalles, chaque partition est explicitement définie. Dans le partitionnement par valeurs discrètes, chaque partition est définie sur une liste figée de valeurs au lieu d'un intervalle.

Le partitionnement par valeurs discrètes est faite avec la clause PARTITION BY LIST(expr) dans la commande CREATE TABLE, avec pour chaque partition l'application de la clause VALUES IN (value_list) où value_list est une liste de valeurs entières séparées par des virgules.

Avec MySQL 5.1 : seule une liste d'entiers est possible (NULL est possible aussi).

Dans l'exemple de la table employees évoquée dans le partitionnement par intervalles, les magasins sont répartis géographiquement de la façon ci-dessous :

Région Numéro de magasin (store_id)
Nord 3,5,6,9,17
Est 1,2,10,11,19,20
Ouest 4,12,13,14,18
Centre 7,8,15,16

Pour partitionner la table employees en région géographique pour les magasins, un partitionnement par valeurs discrètes peut être appliqué :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
)

PARTITION BY LIST(store_id)) (
	PARTITION pNorth VALUES IN (3,5,6,9,17),
	PARTITION pEast VALUES IN (1,2,10,11,19,20),
	PARTITION pWest VALUES IN (4,12,13,14,18),
	PARTITION pCentral VALUES IN (7,8,15,16)
);

Que faire si la colonne sur laquelle le partitionnement est appliqué n'est pas de type integer ou null ? C'est également le cas pour le partitionnement par hâchage (partitionnement HASH) et le partitionnement par intervalles (partitionnement RANGE)

Malheureusement, il faut trouver une expression de partitionnement qui retourne un entier unique pour chaque valeur possible de la colonne qui n'est pas de type integer ou NULL!

C'est le cas si l'on souhaite partitionner par valeurs discrètes la table employees en se basant sur la colonne job_code, colonne qui peut prendre les valeurs ci-dessous en fonction du type d'emploi :

Catégorie d'emploi Département de codes d'emploi
Management D,M,O,P
Ventes B,L,S
Administratifs K,N,Y
Techniciens A,E,G,I,T
Support C,F,J,R,V
Indéfini “Non défini” pour les valeurs NULL

Dans ce cas de figure, une « astuce » peut consister à utiliser l'expression ASCII ( ) sur la colonne job_code :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
)
        
PARTITION BY LIST(ASCII( UCASE(job_code) )) (
	PARTITION management VALUES IN (68, 77, 79, 80),
	PARTITION sales VALUES IN (66, 76, 83),
	PARTITION technical VALUES IN (65, 69, 71, 73, 84),
	PARTITION clerical VALUES IN (75, 78, 89),
	PARTITION support VALUES IN (67, 70, 74, 82, 86),
	PARTITION unassigned VALUES IN (NULL, 0, 32)
);

Important : il n'y a pas de définitions « catch-all » pour le partitionnement par valeurs discrètes comme la clause VALUES LESS THAN (MAXVALUE) pour le partitionnement par intervalles. Donc l'insertion d'une valeur Q pour une employé entraîne un échec d'insertion car aucune des partitions ne contient la valeurs ASCII(‘Q').

3-3- Partitionnement HASH (partitionnement par hachage)

Le partitionnement HASH est utilisé primairement pour assurer une distribution des données sur un nombre déterminé de partitions. Avec le partitionnement par intervalles ou par valeurs discrètes, l'utilisateur doit spécifier explicitement dans quelle partition une valeur d'une colonne donnée doit être stockée ; avec le partitionnement par hachage (HASH partitioning), MySQL se charge de déterminer dans quelle partition stocker la ligne, il suffit de simplement spécifier la colonne ou l'expression sur la colonne.

Pour partitionner une table en mode HASH, il faut ajouter à la commande CREATE TABLE la clause PARTITION BY HASH (expr) où expr est une expression qui retourne une valeur entière. La plupart du temps, la clause PARTITIONS num sera ajoutée pour spécifier le nombre de partitions, num étant un entier non négatif indiquant le nombre de partitions.

Par exemple : voici 2 exemples de partitionnement en 4 par hachage de la table employees, partitionnement fait sur la colonne store_id pour le premier exemple, le second sur l'expression YEAR(hired)

CREATE TABLE employees (
   id INT NOT NULL,
   fname VARCHAR(30),
   lname VARCHAR(30),
   hired DATE NOT NULL DEFAULT '1970-01-01',
   separated DATE NOT NULL DEFAULT '9999-12-31',
   job_code CHAR(1),
   store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE employees (
   id INT NOT NULL,
   fname VARCHAR(30),
   lname VARCHAR(30),
   hired DATE NOT NULL DEFAULT '1970-01-01',
   separated DATE NOT NULL DEFAULT '9999-12-31',
   job_code CHAR(1),
   store_id INT
)
PARTITION BY HASH(YEAR(hired))
PARTITIONS 4;

Si la clause PARTITIONS num n'est pas indiqué, le nombre de partitions par défaut est 1.

On peut utiliser n'importe quelle fonction pour une expression dans le partitionnement par hachage à partir du moment où cette fonction ne retourne pas une valeur constante ou une valeur aléatoire : en d'autres termes, la fonction peut retourner une valeur qui varie si la valeur de la colonne varie mais elle ne doit pas varier si la valeur de la colonne reste inchangée, elle doit rester déterministique.

Autre point en termes d'efficacité dans le partitionnement par hachage : on préférera toujours la fonction YEAR (colonne date) à TO_DAYS (colonne date) pour le partitionnement par hachage, en effet la modification d'une colonne date engendre systématiquement une modification de la valeur entière retournée par la fonction TO_DAYS, mais la modification est bien moins probable avec la fonction YEAR.

Pour des performances optimales dans le partitionnement par hachage, la fonction doit être le moins sensible aux modifications des valeurs dans les colonnes impliquées.

L'utilisation du partitionnement par hachage sur des expressions impliquant plusieurs colonnes est pour la même raison vivement déconseillée.

Dans la clause PARTITION BY HASH, pour déterminer dans quelle partition N la ligne va être stockée : la formule ci-dessous est utilisée

N = MOD(expr,num)

Dans l'exemple qui suit :

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
 PARTITION BY HASH( YEAR(col3) )
 PARTITIONS 4;

Si on insère la ligne (1,'a','2005-09-15'), cette ligne sera stockée dans la partition MOD(YEAR(‘2005-09-15'),4) = 1.

Dans le partitionnement par hachage linéaire (LINEAR HASH), l'algorithme est plus complexe.

3-3-1- Partitionnement LINEAR HASH

Le partitionnement par hachage linéaire diffère du partitionnement par hachage régulier vu précédemment simplement par l'algorithme qui détermine la partition dans laquelle la ligne va être stockée.

La syntaxe diffère simplement en spécifiant PARTITION BY LINEAR HASH :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separated DATE NOT NULL DEFAULT '9999-12-31',
	job_code CHAR(1),
	store_id INT NOT NULL
)
PARTITION BY LINEAR HASH(hired)) (
PARTITIONS 6 ;

L'algorithme pour déterminer la partition N qui va stocker la ligne est le suivant (num est le nombre de partitions, F la fonction appliquée, column_list la liste des colonnes sur lesquelles sont appliquées la fonction F) :

On détermine la prochaine puissance de 2 pour le nombre de partitions num :

V = POWER (2, CEILING( LOG(2,num) ) )

Ex. : pour num = 13, la prochaine puissance de 2 est V=16

      N = F(column_list) & (V – 1)
      WHILE N >= num
      Set V = CEIL( V /2 )
      Set N = N & ( V – 1 )

L'avantage du partitionnement par hachage linéaire est une réalisation plus rapide de l'ajout, la suppression, la fusion et la dissociation des partitions. Le désavantage est une moins bonne distribution potentielle des données dans les partitions par rapport au partitionnement par hachage régulier.

3-4- Partitionnement KEY (partitionnement par clé)

Le partitionnement par clé est similaire au partitionnement par hachage, sauf que dans le partitionnement par hachage une expression utilisateur est utilisée ( MOD, CEIL etc…), la fonction de hachage pour le partitionnement par clé est en revanche quant à elle fournie par le serveur MySQL.

MySQL Cluster utilise MD5 ( ) comme fonction de hachage, pour les autres moteurs de stockage, le serveur emploie sa propre fonction de hachage interne basée sur le même algorithme que la fonction PASSWORD ( ).

La syntaxe de partitionnement par clé est : CREATE TABLE ... PARTITION BY KEY

KEY accepte une liste d'une ou plusieurs colonnes. Lorsque les colonnes ne sont pas spécifiées dans la clé de partitionnement, la clé primaire ou l'index unique de la table est utilisée :

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY ,
    name VARCHAR(20))
PARTITION BY KEY()
PARTITIONS 2;
CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id))
PARTITION BY KEY()
PARTITIONS 2;

A la différence des autres types de partitionnement (RANGE, LIST, HASH), la clé KEY du partitionnement n'est pas restreinte à des valeurs INT ou NULL.

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

Note  :  la commande ALTER TABLE DROP PRIMARY KEY ne peut pas être exécutée sur une table partitionée par clé pour tous les moteurs de stockage (sauf le moteur NDB Cluster qui créé une clé primaire caché par défaut).

Comme pour le partitionnement par hachage, il existe le partitionnement par clé linéaire ( LINEAR KEY ).

CREATE TABLE tk (
	col1 INT NOT NULL,
	col2 CHAR(5),
	col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

et ce qui différencie le partitionnement par clé régulier et le partitionnement par clé linéaire demeure également dans l'algorithme ( MOD pour le premier, Puissance de 2 pour le second).

3-5- Sous-Partitionnement (subpartitioning)

Le sous partitionnement – également connu sous le nom de partitionnement composite – est une sous division de chaque partition d'une table partitionnée.

Exemple :

CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE( YEAR(purchased) )
        SUBPARTITION BY HASH( TO_DAYS(purchased) )
        SUBPARTITIONS 2 (
           PARTITION p0 VALUES LESS THAN (1990),
           PARTITION p1 VALUES LESS THAN (2000),
           PARTITION p2 VALUES LESS THAN MAXVALUE
      );

La table ts a 3 partitions par intervalle. Chacune de ces 3 partitions (p0, p1 et p2) sont sous divisés en 2 sous partitions. La table est donc divisée en 6 partitions. De par la clause PARTITION BY RANGE, les 2 premières sous-partitions ne stockent que les enregistrements pour lesquels l'année purchased est inférieure à 1990.

Avec MySQL 5.1, il est possible de sous-partitionner des tables qui sont partitionnées par intervalles (RANGE) ou par valeurs discrètes (LIST). Ces sous partitions peuvent être de type HASH ou KEY.

Il est également possible d'explicitement nommer ces sous partitions :

CREATE TABLE ts (id INT, purchased DATE)
   PARTITION BY RANGE( YEAR(purchased) )
   SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
	PARTITION p0 VALUES LESS THAN (1990) (
		SUBPARTITION s0,
		SUBPARTITION s1
	),
        PARTITION p1 VALUES LESS THAN (2000) (
		SUBPARTITION s2,
		SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
               SUBPARTITION s4,
               SUBPARTITION s5
        )
   );

Chaque partition doit avoir le même nombre de sous partitions et les noms des sous partitions doivent être uniques sur l'intégralité de la table.

Il n'est bien entendu pas possible de combiner la clause subpartitions num et des sous partitions explicitement nommées.

Voici un exemple concret dans lequel les partitions et sous partitions ainsi que les indexes associés sont explicitement localisés grâce aux clauses DATA DIRECTORY et INDEX DIRECTORY

CREATE TABLE ts (id INT, purchased DATE)
	PARTITION BY RANGE( YEAR(purchased) )
	SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
		PARTITION p0 VALUES LESS THAN (1990) (
			SUBPARTITION s0
				DATA DIRECTORY = '/disk0/data'
				INDEX DIRECTORY = '/disk0/idx',
			SUBPARTITION s1
				DATA DIRECTORY = '/disk1/data'
				INDEX DIRECTORY = '/disk1/idx'
		),
		PARTITION p1 VALUES LESS THAN (2000) (
			SUBPARTITION s2
				DATA DIRECTORY = '/disk2/data'
				INDEX DIRECTORY = '/disk2/idx',
			SUBPARTITION s3
				DATA DIRECTORY = '/disk3/data'
				INDEX DIRECTORY = '/disk3/idx'
		),
		PARTITION p2 VALUES LESS THAN MAXVALUE (
			SUBPARTITION s4
				DATA DIRECTORY = '/disk4/data'
				INDEX DIRECTORY = '/disk4/idx',
			SUBPARTITION s5
				DATA DIRECTORY = '/disk5/data'
				INDEX DIRECTORY = '/disk5/idx'
		)
	);

3-6- Gestion des valeurs NULL dans le partitionnement

Le partitionnement dans MySQL ne fait rien pour ne pas autoriser les valeurs NULL, qu'il s'agisse de la valeur d'une colonne ou du résultat d'une expression utilisateur. Même si il est permis d'utiliser NULL, NULL n'est cependant pas un nombre. A partir de la version 5.1.8, MySQL traite les valeurs NULL comme étant inférieures à n'importe quelle valeur non nulle, comme la clause ORDER BY le fait.

Le traitement de la valeur NULL diffère en fonction du type de partitionnement, le comportement n'est pas le même entre le partitionnement par intervalles (RANGE) et le partitionnement par valeurs discrètes (LIST), ce paragraphe montre comment les valeurs NULL sont gérées en fonction du type de partitionnement.

3-6-1- Valeurs NULL dans le partitionnement RANGE

Pour l'exemple avec le partitionnement par intervalles, considérons les 2 exemples de tables ci-dessous :

mysql> CREATE TABLE t1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (0),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

Il est possible de voir dans quelles partitions sont stockées les lignes en inspectant le filesystem et en comparant les fichiers .MYD des partitions ( les fichiers des partitions sont nomenclaturés table_name#P#partition_name.extension) :

/var/lib/mysql/test> ls -l *.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 03:27 t1#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t1#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t1#P#p2.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 03:27 t2#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p2.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 03:17 t2#P#p3.MYD

Les lignes contenant la valeur NULL pour la colonne c1 sont toujours stockées dans la partition p0 correspond à l'intervalle le plus bas.

Il faut donc être vigilant lors de la suppression de la partition p0 pour ces deux tables, car les lignes pour lesquelles la colonne c1 vaut NULL seront également supprimées !

mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)

3-6-2- Valeurs NULL dans le partitionnement LIST

Une table partitionnée par valeurs discrètes est plus rigoureuse et n'admet les valeurs NULL que si une des partitions est définie avec une liste de valeurs contenant la valeur NULL. Dans le cas contraire la ligne est rejetée avec l'erreur 1504.

mysql> CREATE TABLE ts1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL

Lorsque la valeur NULL est spécifiée pour une partition :

 mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL),
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

La ligne est bien insérée dans la partition p3 :

/var/lib/mysql/test> ls -l ts2*.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p0.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p1.MYD
-rw-rw---- 1 mysql mysql 0 2006-03-10 10:35 ts2#P#p2.MYD
-rw-rw---- 1 mysql mysql 20 2006-03-10 10:35 ts2#P#p3.MYD

3-6-3- Valeurs NULL dans le partitionnement KEY ou HASH

La valeur NULL est traitée différemment pour le partitionnement HASH ou KEY. Dans ces cas, la valeur NULL est traitée comme si elle valait 0. Compte tenu des algorithmes de placement, les valeurs NULL sont donc placées dans les partitions pour une expression qui vaut 0.

Par exemple : dans l'algorithme du partionnement HASH normal => N= MOD ( 0, num) où num est le nombre de partitions.

4- Gestion des partitions

4-1- Suppression du partitionnement (REMOVE PARTITIONING)

Pour supprimer le partitionnement pour une table :

ALTER TABLE ... REMOVE PARTITIONING

Il faut également garder à l'esprit que toutes les partitions d'une table partitionnée doit avoir le même nombre de sous partitions et qu'il est impossible de supprimer le sous partitionnement une fois que la table est créée.

4-2- Changement du schéma de partitionnement (ALTER TABLE...)

Pour changer le schéma de partitionnement d'une table, la simple commande ALTER TABLE avec les options de partitionnement est suffisante. Les options de partitionnement pour la commande ALTER TABLE sont les mêmes que celles de la commande CREATE TABLE.

Par exemple si une table est créée avec un partitionnement par intervalles :

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
	PARTITION BY RANGE( YEAR(purchased) ) (
		PARTITION p0 VALUES LESS THAN (1990),
		PARTITION p1 VALUES LESS THAN (1995),
		PARTITION p2 VALUES LESS THAN (2000),
		PARTITION p3 VALUES LESS THAN (2005)
	);

Pour repartitionner cette table en mode KEY sur 2 partitions :

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

4-3- Gestion des partitions RANGE et LIST

Les partitions de type LIST et RANGE sont gérées de la même manière pour ce qui concerne l'ajout et la suppression de partitions. Supprimer une partition de type RANGE ou LIST est bien plus simple que l'ajout d'une partition.

4-3-1- Suppression des partitions

Dans l'exemple, la table tr est créée avec 4 partitions :

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );

Pour supprimer une partition :

ALTER TABLE <tblname> DROP PARTITION <partition_name>

Ainsi pour supprimer la partition p2 de la table tr :

ALTER TABLE tr DROP PARTITION p2

Supprimer une partition, supprime également toutes les données appartenant à la partition, les données ne sont pas redistribuées vers les autres partitions !

A cause de ce phénomène, le privilège DROP TABLE est maintenant pris en compte pour autoriser la commande ALTER TABLE … DROP PARTITION.

La commande DROP PARTITION ne reporte pas le nombre de lignes supprimées comme le ferait la commande DELETE.

Pour le partitionnement par valeurs discrètes (LIST), supprimer une partition implique qu'il ne sera plus possible d'insérer des lignes rentrant dans les critères VALUES IN de la partition supprimée.

Pour changer le partitionnement d'une table (suppression de partitions LIST et HASH) sans perdre de données, la commande ALTER TABLE ... REORGANIZE PARTITION est utilisée, commande qui est décrite dans le paragraphe qui suit.

4-3-2- Ajout de partitions (ADD PARTITION, REORGANIZE PARTITION)

Pour ajouter une nouvelle partition par intervalles ou par valeurs discrètes, utiliser la commande ALTER TABLE ... ADD PARTITION lorsque c'est possible.

Cette commande ADD PARTITION est utilisable pour les tables partitionnées par intervalles (RANGE), afin d'ajouter un nouvel intervalle à la fin de la liste des partitions existantes.

Exemple :

CREATE TABLE members (
	id INT,
	fname VARCHAR(25),
	lname VARCHAR(25),
	dob DATE
	)
PARTITION BY RANGE( YEAR(dob) ) (
	PARTITION p0 VALUES LESS THAN (1970),
	PARTITION p1 VALUES LESS THAN (1980),
	PARTITION p2 VALUES LESS THAN (1990)
);

ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

En revanche, la tentative d'ajout d'une partition avec ADD PARTITION pour un intervalle avant les intervalles des partitions existantes génère une erreur :

mysql> ALTER TABLE members> ADD PARTITION (
-> PARTITION p3 VALUES LESS THAN (1960));

ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
increasing for each partition

De la même manière, il est possible d'ajouter des partitions par valeurs discrètes (LIST) avec la commande ADD PARTITION si une des valeurs n'est pas déjà définie dans une des partitions, dans le cas contraire une erreur est générée.

Exemple :

CREATE TABLE tt (
	id INT,
	data INT
)
PARTITION BY LIST(data) (
	PARTITION p0 VALUES IN (5, 10, 15),
	PARTITION p1 VALUES IN (6, 12, 18)
);

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

mysql> ALTER TABLE tt ADD PARTITION
-> (PARTITION np VALUES IN (4, 8, 12));

ERROR 1465 (HY000): Multiple definition of same constant » in list partitioning

La dernière commande est rejetée car la valeur 12 est déjà définie dans la partition p1.

L'ajout multiple de partitions dans une seule commande ALTER TABLE ... ADD PARTITION est possible :

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(50) NOT NULL,
	lname VARCHAR(50) NOT NULL,
	hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
	PARTITION p1 VALUES LESS THAN (1991),
	PARTITION p2 VALUES LESS THAN (1996),
	PARTITION p3 VALUES LESS THAN (2001),
	PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
	PARTITION p5 VALUES LESS THAN (2010),
	PARTITION p6 VALUES LESS THAN MAXVALUE
);

Heureusement, le partitionnement MySQL offre des méthodes de redéfinition des partitions sans perte de données et si la commande ADD PARTITION est inutilisable (intervalle déjà défini pour une partition, valeur discrète dans une liste déjà définie pour une partition). C'est la commande REORGANIZE PARTITION qui offre cette fonctionnalité.

Pour résoudre l'erreur

mysql> ALTER TABLE members
-> ADD PARTITION (
-> PARTITION p3 VALUES LESS THAN (1960));

ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
      increasing for each partition

On peut utiliser la commande ALTER TABLE ... REORGANIZE PARTITION

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
	PARTITION s0 VALUES LESS THAN (1960),
	PARTITION s1 VALUES LESS THAN (1970)
);

Dans l'exemple ci-dessus, une partition est scindée en deux partitions, mais une fusion de deux partitions en une seule peut également être réalisée avec la commande  ALTER TABLE ... REORGANIZE PARTITION:

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
	PARTITION p0 VALUES LESS THAN (1970)
);

La syntaxe générale REORGANIZE PARTITON est la suivante :

ALTER TABLE tbl_name
	REORGANIZE PARTITION partition_list
	INTO (partition_definitions);

Toutes les options sont possibles sur les scindages et fusions de partitions, exemple :

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
	PARTITION m0 VALUES LESS THAN (1980),
	PARTITION m1 VALUES LESS THAN (2000)
);

La commande ALTER TABLE … REORGANIZE PARTITION est applicable de la même manière sur les partitionnements par valeurs discrètes (LIST).

Par exemple, pour résoudre le problème précédent

mysql> ALTER TABLE tt ADD PARTITION
-> (PARTITION np VALUES IN (4, 8, 12));

ERROR 1465 (HY000): Multiple definition of same constant »
in list partitioning

On peut ajouter une partition contenant les valeurs ne générant pas de conflit et réorganiser la nouvelle partition avec la partition existante :

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
	PARTITION p1 VALUES IN (6, 18),
	PARTITION np VALUES in (4, 8, 12)
);

Notes importantes :

4-4- Gestion des partitions HASH et KEY

4-4-1- Suppressions de partitions (COALESCE)

Il n'est pas possible de supprimer des partitions pour des tables partitionnées par hachage (HASH) ou par clé (KEY) de la même façon que les tables partitionnées RANGE et LIST.

En revanche, on peut fusionner des partitions HASH et KEY avec la commande ALTER TABLE ... COALESCE PARTITION.

A titre d'exemple, on souhaite réduire le nombre de partitions de 12 à 8 pour une table partitionnée par hachage :

CREATE TABLE clients (
	id INT,
	fname VARCHAR(30),
	lname VARCHAR(30),
	signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

Pour réduire le nombre de partitions de 12 à 4 :

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)

La fonction COALESCE fonctionne sur les partitionnements HASH, KEY, LINEAR HASH et LINEAR KEY.

Le nombre suivant le mot clé COALESCE PARTITION est le nombre de partitions à supprimer pour une table.

Si on essaie de supprimer plus de partitions avec COALESCE que la table n'en a, une erreur est soulevée :

mysql> ALTER TABLE clients COALESCE PARTITION 18;

ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

4-4-2- Ajout de partitions

Pour ajouter des partitions à des tables partitionnées en mode [LINEAR] HASH, [LINEAR] KEY

ALTER TABLE ...ADD PARTITION PARTITIONS <num>;

Pour augmenter le nombre de partitions de la table clients de 12 à 18 :

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

4-5- Maintenance des partitions (REBUILD, CHECK, OPTIMIZE, REPAIR, ANALYZE)

MySQL ne supporte pas les commandes CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE ou REPAIR TABLE pour les tables partitionnées. C'est la commande ALTER TABLE qui permet d'effectuer ces tâches de maintenance sur les partitions.

Reconstruction des partitions : reconstruit la ou les partitions, cette opération est équivalent à supprimer tous les enregistrements et à réinsérer ces derniers. Très utile pour la défragmentation des partitions.

ALTER TABLE t1 REBUILD PARTITION p0, p1;

Optimisation des partitions : Lorsque de nombreuses suppressions de lignes ont été faites dans une partition ou lorsque des mises à jour conséquentes ont été faites sur des lignes contenant des colonnes de longueur variable dans une partition (VARCHAR, BLOB, TEXT), on peut utiliser la commande ALTER TABLE ... OPTIMIZE PARTITION pour récupérer l'espace inutilisé et défragmenter la partition.

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

Utiliser la commande OPTIMIZE PARTITION équivaut à exécuter CHECK PARTITION, ANALYZE PARTITION et REPAIR PARTITION sur la partition.

Analyse de partitions : lit et stocke les distributions des clés pour les partitions (ALTER TABLE ... ANALYZE PARTITION).

ALTER TABLE t1 ANALYZE PARTITION p3;

Réparation des partitions : répare les partitions corrompues (ALTER TABLE ... REPAIR PARTITION )

ALTER TABLE t1 REPAIR PARTITION p0,p1;

Vérification des partitions : vérifie les partitions pour détecter les erreurs ou corruptions, corruptions que l'on corrige avec la commande ALTER TABLE ... REPAIR PARTITION

ALTER TABLE trb3 CHECK PARTITION p1;

4-6- Obtention des informations sur les partitions

Pour obtenir des informations sur les partitions :

4-6-1- SHOW CREATE TABLE

La commande SHOW CREATE TABLE renvoie dans sa sortie la clause PARTITION BY utilisée lors de la création de la table.

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
Table: trb3
Create Table: CREATE TABLE `trb3` (
	`id` int(11) default NULL,
	`name` varchar(50) default NULL,
	`purchased` date default NULL
	) ENGINE=MyISAM DEFAULT CHARSET=latin1
	PARTITION BY RANGE (YEAR(purchased)) (
	PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
	PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
	PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
	PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
	)
1 row in set (0.00 sec)

4-6-2- EXPLAIN PARTITIONS SELECT

La commande EXPLAIN PARTITIONS SELECT permet de voir le plan d'exécution sur les partitions pour une commande SELECT donnée, et plus particulièrement analyser les partitions qui sont balayées durant la sélection.

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(id)
(
	PARTITION p0 VALUES LESS THAN (3),
	PARTITION p1 VALUES LESS THAN (7),
	PARTITION p2 VALUES LESS THAN (9),
	PARTITION p3 VALUES LESS THAN (11)
);

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G
*************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: trb1
      partitions: p0,p1,p2,p3
      type: ALL
      possible_keys: NULL
      key: NULL
      key_len: NULL
      ref: NULL
      rows: 10
      Extra: Using filesort

Dans ce cas de figure, toutes les partitions sont balayées (partitions: p0,p1,p2,p3)

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: trb1
      partitions: p0,p1
      type: ALL
      possible_keys: NULL
      key: NULL
      key_len: NULL
      ref: NULL
      rows: 10
      Extra: Using where

La commande EXPLAIN PARTITIONS SELECT indique également les clés et clés possibles dans le balayage des partitions comme la commande EXPLAIN SELECT standard :

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: trb1
      partitions: p0,p1
      type: range
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 4
      ref: NULL
      rows: 7
      Extra: Using where

Note : les mots clés PARTITIONS et EXTENDED ne peuvent pas être combinés dans la même commande EXPLAIN.

Si la commande EXPLAIN PARTITIONS est utilisée sur une table non partitionnée, aucune erreur n'est générée, mais la valeur de la colonne partitions dans la sortie est toujours NULL.

Limitations et restrictions

Ce paragraphe expose les limitations et restrictions concernant le partitionnement.

6- La table INFORMATION_SCHEMA.PARTITIONS

La table PARTITIONS fournit les informations sur les partitions des tables.

Chaque enregistrement dans cette table correspond à une partition individuelle ou une sous partition d'une table partitionnée.

Une table non partitionnée a toujours une ligne dans la table INFORMATION_SCHEMA.PARTITIONS, avec des valeurs à NULL pour toutes les informations propres aux partitions (PARTITION_METHOD, PARTITION_NAME…).


Annexe

Historique

Version Date Commentaires
1.0 12/2006 Version initiale

Liens

MySQL Books OnLine : Partitioning