Partitionnement MySQL : Optimiser les performances des tables

December 31, 2018

Avec le temps, de nombreuses tables MySQL deviennent volumineuses, ce qui rallonge les temps de recherche, même avec des index bien choisis. Une solution efficace à ce problème est le partitionnement.

Partitionnement MySQL : Qu’est-ce que c’est ?

Imaginez une bibliothèque avec toutes les informations dans un seul livre. Malgré un bon index, la recherche reste lente.

Si le livre est divisé en plusieurs tomes et que vous ne cherchez que dans un seul, l’index devient beaucoup plus rapide. Le partitionnement MySQL fonctionne de la même manière, mais pour les tables.

Cet article se concentre sur le partitionnement horizontal (par ligne).

Quand et comment appliquer le partitionnement à vos tables ?

Pour vérifier si le partitionnement est activé :

SHOW PLUGINS;

Vous devez voir “partition” avec le statut ACTIVE. Certaines tables ne peuvent pas être partitionnées :

Tables avec un index full text
Tables avec des clés étrangères
Tables qui sont des références de clés étrangères pour d’autres tables

Choisir la clé de partitionnement

La clé de partitionnement doit respecter ces conditions :

Être numérique
Faire partie de la clé primaire

Types de Partitionnement MySQL

Partitionnement par Range

Les lignes sont réparties selon des zones définies. On ne peut pas utiliser directement de dates, mais des fonctions MySQL comme YEAR sont autorisées.

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

Partitionnement par List

Les valeurs de la clé de partitionnement doivent correspondre à une des valeurs définies dans la liste. Si la valeur n’est présente dans aucune liste, alors l’insertion est perdue.

Il n’y a pas de valeur de catch !

Cette methode s’adapte bien pour des clefs fixes et bien définies.

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

Partitionnement par 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 être identique.

L’expression de la partition est calculée par valeurDuHash % nombreDePartitions.

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

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

Partitionnement Linear

Le calcul de la partition se fait différemment via la fonction suivante :

    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 des partitions

Le pruning des partitions permet à MySQL d’ignorer certaines partitions lors de l’exécution d’une requête, réduisant ainsi le volume de données à traiter. Seulement les partitions utiles seront lues. Cela se traduit par un temps d’exécution plus rapide et une utilisation mémoire optimisée.

Actions sur les partitions

Optimisation des partitions

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 my_table_partitioned 
OPTIMIZE PARTITION partition_name_to_optimize;

Réparation des partitions

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

ALTER TABLE my_table_partitioned 
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 my_table_partitioned 
DROP PARTITION partition_name_to_drop;

Ajout de partition

On peut aussi ajouter des partitions

ALTER TABLE my_table_partitioned 
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.