PHP Index

Le partitionnement de table va permettre d'organiser et de stocker les données sur différents supports (fichiers ou disques).

La clause PARTITION BY s'applique sur CREATE TABLE et va permettre de définir les conditions de son partitionnement suivi de son type : RANGE, LIST, HASH, ou KEY.

• RANGE : les enregistrements sont répartis en fonction de la valeur d'un champ et de l'intervalle spécifié. Par exemple, les enregistrements dont l'identifiant est inférieur à 10000 sera stocké dans une partition, ceux compris entre 10000 et 19999 dans une autre, puis ceux entre 20000 et 29999 sur une troisième partition...
• LIST : tout comme le RANGE, la répartition se fait à partir d'une liste de valeurs à la place d'un intervalle.
• HASH : ce type de partitionnement vous permet de stocker de façon transparente les enregistrements sur les différentes partitions. Cette répartition se base sur une fonction de hachage.
• KEY : cette méthode de partitionnement est similaire à la précédente à l'exception que MySQL utilise ses propres fonctions de hashage.

Partitionnement par intervalle (RANGE) :

CREATE TABLE orders_range (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000),
    PARTITION p5 VALUES LESS THAN(MAXVALUE)
);

La clause PARTITION BY RANGE(id) permet de définir le type de partitionnement et sur quelle colonne il s'applique. Ensuite, on décrit les différentes partitions en leur spécifiant un nom et une définition. Dans notre exemple, la partition nommée p0 contiendra les enregistrements dont l'ID sera inférieure à 10000, la partition p1 ayant les ID entre 10000 et 19000... La partition p5 contiendra toutes les ID supérieur ou égal à 50000.
La partition p5 dont la valeur est MAXVALUE recevra tous les enregistrements supérieurs à la plus grande valeur que l'on a défini. Dans notre exemple, la partition p5 recevra tous les enregistrements dont l'ID est supérieur ou égal à 50000.
Tout ID de valeur NULL est considérée comme la plus petite valeur et sera donc stockée sur la partition p0.


Partitionnement par valeur (LIST) :

CREATE TABLE orders_list (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY LIST(store_id) (
    PARTITION p0 VALUES IN (1, 3, 4, 17),
    PARTITION p1 VALUES IN (2, 12, 14),
    PARTITION p2 VALUES IN (6, 8, 20),
    PARTITION p3 VALUES IN (5, 7, 9, 11, 16),
    PARTITION p4 VALUES IN (10, 13, 15, 18)
);

Si vous tentez d'insérer un enregistrement dont la valeur de store_id n'est définie dans aucune partition, une erreur sera retournée. Contrairement au partitionnement par RANGE, vous devez gérer la valeur NULL :

CREATE TABLE tn1 (c1 INT, c2 INT)
  PARTITION BY LIST(c1)  (
    PARTITION p0 VALUES IN(NULL),
    PARTITION p1 VALUES IN(0),
    PARTITION p2 VALUES IN(1, 3, 5),
    PARTITION p3 VALUES IN(2, 4)
  );

CREATE TABLE tn2 (c1 INT, c2 INT)
  PARTITION BY LIST(c1)  (
    PARTITION p0 VALUES IN(NULL, 1, 3, 5),
    PARTITION p1 VALUES IN(0, 2, 4)
  );

Partitionnement par hachage (HASH) :

CREATE TABLE orders_hash (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY HASH(id)
  PARTITIONS 4;

Cette méthode vous permet de ne pas avoir à spécifier l'emplacement de chaque enregistrement. MySQL détermine la partition sur laquelle seront stockées les enregistrements; le numéro de la partition est calculée par l'expression : IF(ISNULL(expr), 0, ABS(expr)) MOD num où expr correspond à la colonne sur laquelle sera effectué le calcul (id dans l'exemple) et num le nombre de partition. Nous avons défini 4 partitions qui seront numérotées de 0 à 3, l'ID #2030 sera stockée sur la partition #3 (10203 mod 4 vaut 3).


Partitionnement quasiment identique avec celui par clé (KEY) :

CREATE TABLE orders_key2 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY KEY(salesperson_id, customer_surname)
  PARTITIONS 4;

Le partionnement par KEY prend en compte zéro ou plusieurs colonnes de tout type.


Il est possible de répartir chaque partition sur différents disques tout en séparant les index et les données :

CREATE TABLE orders_hash2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY HASH(id) (
    PARTITION p0
      INDEX DIRECTORY = '/data0/orders/idx'
      DATA DIRECTORY = '/data0/orders/data',
    PARTITION p1
      INDEX DIRECTORY = '/data1/orders/idx'
      DATA DIRECTORY = '/data1/orders/data',
    PARTITION p2
      INDEX DIRECTORY = '/data2/orders/idx'
      DATA DIRECTORY = '/data2/orders/data',
    PARTITION p3
      INDEX DIRECTORY = '/data3/orders/idx'
      DATA DIRECTORY = '/data3/orders/data'
);

Le sous-partitionnement :

CREATE TABLE orders_range_hash (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY RANGE(id)
    SUBPARTITION BY HASH(store_id)
    SUBPARTITIONS 2  (
      PARTITION p0 VALUES LESS THAN(10000),
      PARTITION p1 VALUES LESS THAN(20000),
      PARTITION p2 VALUES LESS THAN(30000),
      PARTITION p3 VALUES LESS THAN(40000),
      PARTITION p4 VALUES LESS THAN(50000)
);

orders_range_hash est une table partitionnée par RANGE et sous-partitionnée par HASH. Chaque partition RANGE est divisée en sous-partition HASH pour un total de 5*2=10 sous-partitions.

Retrouvez l'article complet sur le site de MySQL AB avec les tests de performance.