Introduction au partionnement sous MySql

Avec le temps de nombreuses tables grossissent de manière assez forte. Cela entraine malgré des index correctement choisis des temps de recherche de plus en plus longs. Une solution à ce problème peut être d’utiliser le partitionnement.

Partitionnement introduction

Imaginez que la bibliothèque possède toutes les infos dans un seul et même livre, malgré un index dans celui-ci, chercher une information dedans est assez complexe. Maintenant si ce livre est découpé en plusieurs tomes et qu’il faut rechercher que dans un seul tome alors avec l’index cela sera beaucoup plus rapide. Le partitionnement c’est la même chose, mais pour une table. On parlera uniquement du partitionnement horizontal dans cet article, c’est-à-dire un partitionnement par ligne.

Est-ce utilisable ?

A priori oui, toutefois pour ne vérifier rien de plus simple

SHOW PLUGINS;

On doit avoir dans les résultats une ligne avec pour nom partition et status ACTIVE.

Certaines tables ne peuvent par contre pas être partitionnées :

• celle avec un index full text
• celle avec des clef etrangeres
• celle avec qui sont des references de clef etrangere d’autres tables

Contraintes sur la clef de partitionnement

La clef de partitionnement choisi devra obligatoirement :

• être numérique
• être comprise dans la clef primaire

Détails sur les types de partionnements

Range

Chaque ligne est insérée dans la bonne partition en fonction de sa valeur par rapport aux diverses zones prédéfinies. Ici on ne peut pas utiliser de date, toutefois rien n’empêche d’utiliser une fonction MYSQL comme YEAR.

CREATE TABLE example (
  id INT NOT NULL
)
PARTITION BY RANGE (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
  );

List

Si la valeur de la clef de partitionnement n’est présente dans aucune liste alors l’insertion est perdue. Il n’y a pas de valeur de catch.

CREATE TABLE example (
    id INT NOT NULL,
    store_id INT
)
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)
);

Hash

La valeur dans le HASH doit etre entière. On peut utiliser une fonction mysql mais pour une valeur précise la valeur de sortie doit etre identique.

La partition sélectionnée est égale a valeurDuHash % nombresDePartiontion.

CREATE TABLE example (
    id INT NOT NULL,
    example_date DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
)
PARTITION BY HASH(YEAR(example_date))
PARTITIONS 4;
);

Key

La clef n’est pas obligatoirement numérique, key utilise une fonction de hash propre a mysql (basé sur password).

CREATE TABLE example (
    id INT NOT NULL PRIMARY KEY,
    store_id INT
)
PARTITION BY KEY()
PARTITIONS 4;
);

Linear

Le calcul de la partition sélectionné change pour devenir :

    V = POWER(2, CEILING(LOG(2, num)))
    N = F(column_list) & (V - 1).

    While N >= num:
        Set V = CEIL(V / 2)
        Set N = N & (V - 1)
        

Ou :

• num : nombre de partitions
• F(column_list) : Valeur de la fonction de hashage
• N : partition selectionné

Contrairement a ce que dit le nom la répartition n’est pas linéaire

Pruning

Selon les critères de la requête, la lecture ne se fait que sur un sous-ensemble de partitions. Le temps d’exécution s’en trouve réduit de même que l’utilisation mémoire.

Actions sur les partitions

Optimisation

Lors suppression de ligne ou changement de taille des colonnes, il peut y avoir une fragmentation des données on peu alors lancer une optimisation par partition.

ALTER TABLE partionned_table 
OPTIMIZE PARTITION partition_name_to_optimize;

Réparation

En cas d’erreur sur les données ou les index on peut reparer par partion

ALTER TABLE partionned_table 
CHECK PARTITION p0,p1

Suppression de partition

Grâce au partitionnement on peut supprimer des partitions directement. Ainsi la suppression d’un enregistrement par rapport a la date peut se faire rapidement même sur des gros volumes de données.

ALTER TABLE partionned_table 
DROP PARTITION partition_name_to_drop;

Ajout de partition

On peut aussi ajouter des partitions

ALTER TABLE partionned_table 
ADD PARTITION partition_name (PARTITION_DESCRIPTION);

Note : Sur du partitionnement par range on ne peut pas ajouter de participation apres notre partition fourre-tout (celle en MAXVALUE ). Il faudra dont la supprimer au préalable, puis ajouter la nouvelle et la recréer. Bien sûr, il faut faire cela avec quelle ne commence a se remplir.

Contraintes mémoires

Dans le cas des tables MyISAM, chaque partition utilise deux file descriptor qui doivent être ouvertes pour accéder aux données, et ceci est bien sûr par partition.

Voici un petit aperçu du partitionnement, de son utilisation pour augmenter les performances sur des tables assez volumineuse.