High Performance MySQL [Ch. 7 : Fonctionnalités avancées de MySQL]

Tables partitionnées

Une table partitionnée est une table logique stockée sous forme de plusieurs tables physiques. Chaque partition a son propre handler sous le serveur ; une fine couche de partitionnement achemine chaque instruction vers les bonnes partitions, si bien que le moteur de stockage ne voit que des tables ordinaires. Chaque index de la table existe séparément dans chaque partition, et toutes les partitions utilisent le même moteur.

Il est utile dans quelques situations précises :

• Des tables énormes avec une extrémité chaude. Les lignes récentes sont lues en permanence, les anciennes rarement ; les partitions gardent la partie chaude petite.
• Des suppressions massives peu coûteuses. Supprimer une partition entière contenant les données de l'an dernier est une opération sur les métadonnées, pas des millions de suppressions de lignes.
• Une maintenance par partition : reconstruire, vérifier ou restaurer une seule tranche au lieu de la table entière.

Comment les instructions sont acheminées

Le verrouillage au sein d'une partition suit les règles habituelles d'InnoDB (verrous de ligne, pas de partition).

Les schémas courants sont RANGE (par plages de dates ou d'ids, le choix habituel pour les séries temporelles), LIST (ensembles de valeurs explicites) et HASH ou KEY (répartir les lignes uniformément pour réduire les points chauds) :

CREATE TABLE bug_events (
  id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

MySQL exige que les colonnes de partitionnement fassent partie de chaque clé unique : c'est pourquoi created_at rejoint la clé primaire ci-dessus.

Ce qui tourne mal avec les partitions

• Les requêtes qui ne filtrent pas sur la clé visitent toutes les partitions, en cherchant dans la copie de l'index de chacune. Cela peut être plus lent que pas de partitionnement du tout.
• Les valeurs de partitionnement NULL atterrissent dans la première partition, si bien que les recherches peuvent devoir la consulter en plus de celle attendue. Gardez l'expression non nullable.
• Trop de partitions rendent coûteux l'acheminement et l'ouverture des tables. Quelques dizaines, c'est bien ; des milliers, c'est mauvais signe.
• L'élagage exige un prédicat reconnaissable. Filtrez directement sur la colonne (created_at >= '2025-01-01') et vérifiez la colonne partitions de EXPLAIN pour confirmer que seules les partitions attendues apparaissent.

Vues

Une vue est une requête nommée ; elle ne stocke aucune donnée. MySQL l'exécute de l'une de deux façons. Avec l'algorithme MERGE, la définition de la vue est intégrée à votre requête : les filtres se combinent et les index restent utilisables ; c'est la voie efficace. Avec TEMPTABLE, le résultat de la vue est d'abord matérialisé dans une table temporaire, que votre requête lit ensuite. TEMPTABLE est imposé dès que la vue utilise GROUP BY, DISTINCT, des agrégats, UNION ou certaines sous-requêtes, et cela signifie que les filtres extérieurs ne peuvent pas atteindre les index de la table de base.

Les vues simples peuvent être modifiables, et WITH CHECK OPTION rejette les écritures à travers la vue qui produiraient des lignes que la vue elle-même n'afficherait pas.

Les vues accélèrent rarement les requêtes. Leur intérêt pratique tient au contrôle d'accès et à la simplicité : exposez une vue sans les colonnes sensibles et n'accordez SELECT que sur la vue. MySQL n'a pas de vues matérialisées ; les tables de cache et de synthèse du chapitre 4, rafraîchies par une tâche ou un événement, en tiennent lieu.

Triggers

Les triggers exécutent du code BEFORE ou AFTER un INSERT, un UPDATE ou un DELETE, dans la même transaction que l'instruction. Cette atomicité les rend pratiques pour garder synchronisés des colonnes dénormalisées ou des compteurs de synthèse sans allers-retours supplémentaires. MySQL 8 autorise plusieurs triggers par événement et par table.

Les coûts sont réels :

• Ligne par ligne. Les triggers MySQL sont toujours FOR EACH ROW ; une instruction qui touche un million de lignes les déclenche un million de fois.
• Un travail invisible. Une instruction d'une ligne peut faire beaucoup en coulisses, ce qui fausse le profilage et surprend le développeur suivant.
• Verrous et échecs. Les triggers peuvent provoquer des attentes de verrous et des deadlocks inattendus, et leurs erreurs font échouer l'instruction d'origine avec un message qui ne pointe vers rien d'évident.

Événements

Les événements sont du SQL planifié, un cron qui vit dans le serveur : exécution unique à une heure donnée ou à intervalle régulier. Gardez le corps de l'événement minimal et placez la logique dans une procédure stockée. MySQL n'empêche pas une nouvelle exécution de démarrer tant que la précédente n'est pas terminée : protégez donc les tâches longues avec un verrou nommé :

CREATE EVENT purge_old_bug_events
ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
    IF GET_LOCK('purge_old_bug_events', 0) THEN
      CALL purge_old_bug_events();
      DO RELEASE_LOCK('purge_old_bug_events');
    END IF;
  END

Le planificateur d'événements doit être activé (event_scheduler=ON, la valeur par défaut dans MySQL 8), et les événements ne s'exécutent que sur le serveur où ils sont définis, ce qui compte avec des réplicas.

Là où s'arrête cette série

La suite du livre passe de la requête à la machine : configuration du serveur, système d'exploitation et matériel, réplication, montée en charge, haute disponibilité, sauvegarde et restauration. Ces sujets comptent, mais relèvent surtout de l'exploitation, et les services MySQL managés en prennent une grande partie en charge pour les équipes applicatives. Les chapitres 1 à 7 couvrent ce que vous maîtrisez depuis l'éditeur de requêtes.

Questions fréquentes