Partitionnement avec MySQL 5.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.).

Généralités sur le 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 :

La possibilité de stocker précisément les données d’une table sur un disque ou une partition filesystem.
Une gestion plus simple de la suppression des données obsolètes d’une table en supprimant simplement une ou plusieurs partitions sans avoir recours à la commande DELETE.
Des requêtes optimisées en ne balayant que les partitions rentrant dans les conditions des clauses WHERE.
Une maintenance améliorée et plus efficace (statistiques, réorganisations, check, etc.) car cette maintenance peut ne s’appliquer que sur une partition d’une table.

Types de partitions

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

Partitionnement RANGE (partitionnement par intervalles) : applique les lignes dans les partitions en fonction des valeurs d’une colonne correspondant à un intervalle.
Partitionnement LIST (partitionnement par liste) : similaire au partitionnement par intervalles, sauf que les partitions ne sont plus créées sur des intervalles de valeurs mais des valeurs discrètes (liste de valeurs).
Partitionnement HASH (partitionnement par hâchage) : la partition correspond à une valeur retournée par une expression utilisateur appliquée sur les valeurs des colonnes des lignes insérées dans la table. Cette fonction peut être n’importe quelle expression valide MySQL qui retourne une valeur entière non négative.
Partitionnement KEY (partitionnement par clé ) : similaire au partitionnement par hachage, sauf qu’une ou plusieurs colonnes à évaluer sont fournies.

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

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 :

Il est nécessaire de supprimer les anciennes données, on peut en effet simplement utiliser la commande ALTER TABLE employees DROP PARTITION p0 par exemple, ce qui est incomparablement bien plus efficace qu’une commande DELETE sur une table immense (DELETE FROM employees WHERE YEAR(separated) <= 1990 sur 14 millions de lignes par exemple…).
Les requêtes fréquemment lancées sont du type SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id; dans ce cas en effet, le serveur MySQL scanne uniquement la partition p2 pour retrouver les enregistrements en accord avec la clause WHERE.

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

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.

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.

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;

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

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) est sous divisée 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'
		)
	);

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

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

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');

mysql> INSERT INTO t2 VALUES (NULL, 'mothra');

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 correspondant à 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;

mysql> ALTER TABLE t2 DROP PARTITION p0;

mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)

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

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

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

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.

Gestion des partitions

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.

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;

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.

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 !

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

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 autorisée :

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 :

Pour les tables partitionnées par intervalles (RANGE), on ne peut réorganiser que des partitions adjacentes ( ALTER TABLE members REORGANIZE PARTITION p0,P2 INTO est interdite car les partitions p0 et p2 ne sont pas adjacentes dans les intervalles).
La commande REORGANIZE PARTITION n’est pas applicable pour changer le schéma de partitionnement, changer l’expression de partitionnement. Pour changer l’expression de partitionnement sans dropper et recréer la table, une méthode consiste à utiliser la commande ALTER TABLE … PARTITION BY.

Gestion des partitions HASH et KEY

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.

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

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

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;

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 : REBUILD PARTITION 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 : ANALYZE PARTITION lit et stocke les distributions des clés pour les partitions.

ALTER TABLE t1 ANALYZE PARTITION p3;

Réparation des partitions : REPAIR PARTITION répare les partitions corrompues.

ALTER TABLE t1 REPAIR PARTITION p0,p1;

Vérification des partitions : CHECK PARTITION 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;

Obtention des informations sur les partitions

Pour obtenir des informations sur les partitions :

Commande SHOW CREATE TABLE pour voir les clauses de partitionnement.
Commande SHOW TABLE STATUS pour déterminer si une table est partitionnée.
Interrogation de la table INFORMATION_SCHEMA.PARTITIONS.
Commande EXPLAIN PARTITIONS SELECT pour voir quelles partitions sont balayées pour une commande SELECT donnée.

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)

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.

Lors de la création de tables avec un nombre très important de partitions, l’erreur 24 peut tomber. Dans ce cas de figure augmenter le paramètre open_files_limit du serveur.
Les tables partitionnées ne supportent pas les clés étrangères (moteur InnoDB).
Les tables partitionnées ne supportent pas les indexesFULLTEXT.
Les tables partitionnées ne supportent pas les colonnes GEOMETRY.
Les moteurs de stockage MERGE et CSV ne supportent pas le partitionnement de tables.
Le partitionnement par KEY (ou LINEAR KEY) est le seul type de partitionnement supporté par le moteur de stockage NDB.
Lors d’un upgrade, les tables partitionnées de type KEY et utilisant un moteur de stockage autre que NDBCLUSTER doivent être déchargées et rechargées.
Toutes les partitions et sous partitions doivent utiliser le même moteur de stockage.
Une clé de partitionnement doit être un entier ou une expression qui retourne un entier. La colonne ou l’expression peut avoir des valeurs NULL. La seule exception est le partitionnement par KEY ou LINEAR KEY pour lequel les autres types de colonnes qu’un entier sont autorisés. Les types de colonnes BLOB et TEXT ne sont pas autorisés cependant.
Une clé de partitionnement ne peut être une sous requête, même si cette dernière retourne une valeur entière.

Toutes les colonnes utilisées dans l’expression de partitionnement doivent faire partie d’une clé unique ou de la clé primaire que la table peut comprendre.

Commandes invalides Commandes valides

Commandes invalides	Commandes valides
CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2)) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t2 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1), UNIQUE KEY (col3)) PARTITION BY HASH(col1 + col3) PARTITIONS 4; CREATE TABLE t3( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2), UNIQUE KEY (col3)) PARTITION BY HASH(col1 + col3) PARTITIONS 4;	CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2,col3)) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t2 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col3)) PARTITION BY HASH(col1 + col3) PARTITIONS 4; CREATE TABLE t3 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2, col3), UNIQUE KEY (col3)) PARTITION BY HASH(col3) PARTITIONS 4;


CREATE TABLE t1 (
  col1 INT NOT NULL,
  col2 DATE NOT NULL,
  col3 INT NOT NULL,
  col4 INT NOT NULL,
  UNIQUE KEY (col1, col2))
   PARTITION BY HASH(col3)
   PARTITIONS 4;

CREATE TABLE t2 (
  col1 INT NOT NULL,
  col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3))
  PARTITION BY HASH(col1 + col3)
  PARTITIONS 4;

CREATE TABLE t3(
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2),
UNIQUE KEY (col3))
 PARTITION BY HASH(col1 + col3)
  PARTITIONS 4;


CREATE TABLE t1 (
  col1 INT NOT NULL,
  col2 DATE NOT NULL,
  col3 INT NOT NULL,
  col4 INT NOT NULL,
  UNIQUE KEY (col1, col2,col3))
   PARTITION BY HASH(col3)
   PARTITIONS 4;

CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3))

  PARTITION BY HASH(col1 + col3)
  PARTITIONS 4;

CREATE TABLE t3 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3),
UNIQUE KEY (col3))
  PARTITION BY HASH(col3)
  PARTITIONS 4;

Si la table ne contient pas d’indexes uniques ou de clés primaires, cette restriction ne s’applique pas, une ou toutes les colonnes peuvent être utilisées dans l’expression de partitionnement à partir du moment que le type est compatible avec le type de partitionnement.

Pour la même raison, on ne peut ajouter un index ou une clé unique sur une table déjà partitionnée à moins que la clé n’inclut toutes les colonnes utilisées dans l’expression de partitionnement.

Exemple :

CREATE TABLE t_no_pk (c1 INT, c2 INT)
PARTITION BY RANGE(c1) (
	PARTITION p0 VALUES LESS THAN (10),
	PARTITION p1 VALUES LESS THAN (20),
	PARTITION p2 VALUES LESS THAN (30),
	PARTITION p3 VALUES LESS THAN (40)
);

La commande ALTER TABLE t_no_pk ADD PRIMARY KEY (c1) est possible mais la commande ALTER TABLE t_no_pk ADD PRIMARY KEY (c1,c2) est refusée car la colonne c2 ne fait pas partie de l’expression de partitionnement BY RANGE(c1).

Les sous partitions sont limitées au partitionnement de type [LINEAR] HASH et [LINEAR] KEY.

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.

TABLE_CATALOG : cette colonne vaut toujours NULL.
TABLE_SCHEMA : nom de la base de données à laquelle appartient la table appartient.
TABLE_NAME : nom de la table contenant la partition.
PARTITION_NAME : nom de la partition.
SUBPARTITION_NAME : cette colonne contient le nom de la sous partition si il s’agit d’une sous partition, sinon cette colonne vaut NULL pour une partition.
PARTITION_ORDINAL_POSITION : toutes les partitions sont indexées dans un ordre identique à l’ordre appliqué lors de la définition des partitions, 1 étant le numéro assignée à la première partition. L’indexation change avec l’ajout, la suppression et la réorganisation des partitions. Le numéro dans cette colonne reflète l’ordre courant prenant en compte tout changement d’indexation.
SUBPARTITION_ORDINAL_POSITION : les sous partitions d’une partition sont également indexées de la même manière que les partitions sont indexées dans une table.
PARTITION_METHOD : méthode de partitionnement de la table partitionnée RANGE, LIST, HASH, LINEAR HASH, KEY ou LINEAR KEY
SUBPARTITION_METHOD : méthode de partitionnement de la sous partition si il s’agit d’une sous partition HASH, LINEAR HASH, KEY ou LINEAR KEY.

PARTITION_EXPRESSION : expression de la function de partitionnement utilisée lors de la création ou de l’altération de la table.

Exemple :

CREATE TABLE tp (
	c1 INT,
	c2 INT,
	c3 VARCHAR(25)
)
PARTITION BY HASH(c1 + c2)
PARTITIONS 4;

mysql> SELECT DISTINCT PARTITION_EXPRESSION
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';
              
+----------------------+
| PARTITION_EXPRESSION |
+----------------------+
| c1 + c2              |
+----------------------+
1 row in set (0.09 sec)

SUBPARTITION_EXPRESSION : expression de partitionnement utilisée lors du sous partitionnement si il y a sous partitionnement.
PARTITION_DESCRIPTION : cette colonne est utilisée pour les partitions RANGE et LIST. Pour le partitionnement RANGE, cette colonne contient la valeur de la clause VALUES LESS THAN (MAXVALUE ou l’entier appliqué pour la partition…). Pour le partitionnement LIST, cette colonne contient la clause VALUES IN, c’est-à-dire la liste des valeurs entières séparées par des virgules associée à la partition. Pour les autres méthodes de partitionnement, cette colonne retourne NULL.
TABLE_ROWS : nombre de lignes dans la partition.
AVG_ROW_LENGTH : taille moyenne en octets des lignes stockées dans la partition ou la sous partition. C’est le nombre donné par DATA_LENGTH divisé par TABLE_ROWS.
DATA_LENGTH : taille totale en octet de toutes les lignes stockées dans la partition ou la sous partition.
MAX_DATA_LENGTH : taille maximale en octets qui peut être stockée dans la partition ou la sous partition.
INDEX_LENGTH : taille en octets du fichier d’index pour la partition ou la sous partition.
DATA_FREE : taille des octets alloués pour la partition ou la sous partition mais non utilisés.
CREATE_TIME : date et heure de la création de la partition ou sous partition.
UPDATE_TIME : date et heure de la dernière modification de la partition ou sous partition.
CHECK_TIME : date et heure de la dernière vérification de la partition ou sous partition. Quelques moteurs de stockage ne mettent pas à jour cette information et la colonne peut être à NULL.
CHECKSUM : valeur checksum pour la partition ou la sous partition.
PARTITION_COMMENT : commentaire appliqué sur la partition. Par défaut, la colonne est une chaîne vide.
NODEGROUP : nœud auquel appartient la partition ou sous partition. Cette information est pertinente pour les tables partitionnées MySQL Cluster. Elle vaut 0 pour les autres moteurs de stockage.
TABLESPACE_NAME : nom du tablespace auquel appartient la partition ou la sous partition.

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