Maîtrise de l'indexation MySQL pour des performances de requêtes plus rapides

L'indexation MySQL est généralement le premier endroit où je regarde lorsqu'une base de données semble lente, mais c'est aussi l'un des endroits les plus faciles pour commettre une erreur de confiance. Un index peut transformer un balayage de table en une recherche rapide. Il peut également ralentir les écritures, gaspiller de la mémoire et donner un faux sentiment de progression si le planificateur de requêtes ne l'utilise jamais.

La question pratique n'est pas « Cette colonne devrait-elle avoir un index ? » La meilleure question est : « Quelle requête j'essaie de rendre moins coûteuse, et comment vais-je prouver que l'index a aidé ? » Gardez cette question à l'esprit pendant votre lecture. Une bonne indexation commence par des requêtes réelles, pas par une liste de colonnes qui semblent importantes.

Qu'est-ce qu'un index MySQL ?

Un index MySQL est une structure de données qui améliore la vitesse des opérations de récupération de données sur une table de base de données. Considérez-le comme un index dans un livre : au lieu de lire tout le livre pour trouver un sujet spécifique, vous pouvez rechercher le sujet dans l'index, qui vous indiquera le numéro de page exact. De même, un index de base de données permet à MySQL de localiser rapidement les lignes qui correspondent à une condition de requête spécifique sans analyser l'intégralité de la table.

Lorsque vous interrogez une table, MySQL peut utiliser un index pour trouver les lignes pertinentes beaucoup plus rapidement qu'en examinant chaque ligne. Ceci est particulièrement bénéfique pour les tables avec un grand nombre de lignes ou pour les requêtes qui impliquent un filtrage (clauses WHERE), des jointures de tables (clauses JOIN) ou un tri (clauses ORDER BY).

Comment fonctionnent les index

MySQL utilise couramment des index B-tree pour les index InnoDB normaux. Un B-tree conserve les clés dans un ordre trié, ce qui le rend efficace pour les recherches d'égalité, les plages, les analyses ordonnées et de nombreuses jointures. Lorsque vous créez un index sur une ou plusieurs colonnes, MySQL construit une structure où :

• Les nœuds feuilles contiennent les pointeurs de données réels ou, dans le cas des index cluster (comme la clé primaire d'InnoDB), les lignes de données elles-mêmes.
• Les nœuds internes contiennent des clés qui aident à naviguer dans l'arbre pour trouver le nœud feuille correct.

Lorsqu'une requête peut utiliser le côté gauche de cet index, MySQL peut naviguer vers une partie étroite de l'arbre au lieu de lire toute la table. C'est le véritable avantage. L'index ne rend pas chaque requête rapide ; il rend certains modèles d'accès peu coûteux.

Types d'index MySQL

MySQL prend en charge différents types d'index, chacun avec ses propres forces et cas d'utilisation.

1. PRIMARY KEY

• Une contrainte PRIMARY KEY garantit que chaque valeur dans la ou les colonnes est unique et non NULL. Elle est implicitement indexée.
• Une table ne peut avoir qu'une seule PRIMARY KEY.
• Les tables InnoDB sont physiquement ordonnées par leur clé primaire (index cluster).

Exemple :

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. Index UNIQUE

• Un index UNIQUE impose que toutes les valeurs de la ou des colonnes indexées soient distinctes. Il autorise les valeurs NULL, mais plusieurs NULL sont autorisés (sauf si la colonne fait également partie d'une PRIMARY KEY ou d'une autre contrainte UNIQUE qui l'empêche).
• Utile pour garantir l'intégrité des données lorsqu'une colonne doit être unique mais n'est pas l'identifiant principal.

Exemple :

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (ou KEY)

• Un index standard, également appelé index non unique.
• Utilisé pour accélérer la récupération des données. N'impose pas l'unicité.

Exemple :

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. Index FULLTEXT

• Utilisé pour les recherches en texte intégral sur les colonnes CHAR, VARCHAR et TEXT.
• Permet les recherches par mots-clés dans de grands champs de texte.
• Pris en charge par InnoDB dans les versions modernes de MySQL. Les installations MySQL plus anciennes peuvent avoir des limites différentes, alors vérifiez votre version exacte avant de concevoir autour de cela.

Exemple :

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. Index SPATIAL

• Utilisé pour indexer les types de données spatiales tels que les points, les lignes et les polygones.
• Le comportement et les exigences varient selon la version de MySQL et le moteur de stockage. Testez la requête spatiale exacte que vous prévoyez d'exécuter ; ne supposez pas qu'un index spatial aidera chaque prédicat de type SIG.

6. Index HASH (Utilisation limitée)

• Le moteur de stockage MEMORY de MySQL prend en charge les index HASH. Ils sont conçus pour les recherches d'égalité, pas pour les analyses de plage ou le tri.
• Ce n'est pas un type d'index à usage général pour la plupart des scénarios courants.

Création et gestion des index

Comment créer un index

Vous pouvez créer des index soit lors de la création d'une table, soit en modifiant une table existante.

1. Lors de la création de la table :

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. Modification d'une table existante :

-- Ajouter un index à une seule colonne
ALTER TABLE customers
ADD INDEX idx_email (email);

-- Ajouter un index unique
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- Ajouter un index multi-colonnes (composite)
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

Comment supprimer un index

Si un index n'est plus nécessaire ou a un impact négatif sur les performances (par exemple, lors des écritures), vous pouvez le supprimer.

-- Supprimer un index standard
ALTER TABLE customers
DROP INDEX idx_email;

-- Supprimer un index unique
ALTER TABLE users
DROP INDEX uidx_username;

Index multi-colonnes (composites)

Les index composites sont créés sur deux colonnes ou plus. L'ordre des colonnes dans un index composite est crucial.

• Un index composite sur (col1, col2) peut être utilisé pour les requêtes filtrant sur col1 seule, ou sur col1 ET col2.
• Il n'est généralement pas utilisé pour les requêtes filtrant uniquement sur col2.

Exemple :

Considérez un index sur (customer_id, order_date). Cet index est le plus efficace pour les requêtes comme :

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

Il pourrait ne pas être très utile pour SELECT * FROM orders WHERE order_date = '2023-10-27';.

Meilleures pratiques pour l'indexation MySQL

1. Indexer les colonnes utilisées dans les clauses WHERE, JOIN et ORDER BY

Ce sont les endroits les plus courants où les index offrent des avantages significatifs en termes de performances.

• Clauses WHERE : Les conditions de filtrage sont le cas d'utilisation principal.
• Conditions JOIN : L'indexation des colonnes utilisées dans les clauses ON des instructions JOIN accélère considérablement les jointures de tables.
• Clauses ORDER BY et GROUP BY : Les index peuvent aider MySQL à éviter les opérations de tri.

2. Utiliser les index composites à bon escient

• L'ordre compte : Placez d'abord les colonnes qui correspondent à la forme de la requête. Les filtres d'égalité viennent généralement avant les filtres de plage. Les colonnes utilisées pour le tri peuvent aider après les colonnes de filtrage.
• Ne placez pas aveuglément la colonne la plus sélective en premier si vos requêtes réelles ne filtrent pas par celle-ci. Un index sur (status, created_at) peut être excellent pour WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50, même si status a une faible cardinalité, car il correspond au modèle d'accès.

3. Utiliser EXPLAIN avant et après

Ne jugez jamais un index par espoir. Exécutez EXPLAIN, ajoutez l'index dans un environnement de staging ou de maintenance sécurisé, puis exécutez EXPLAIN à nouveau.

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

Regardez type, key, rows et Extra. Si key est NULL, MySQL n'a pas choisi d'index. Si rows est encore proche de la taille de la table, l'index n'est peut-être pas assez sélectif pour cette requête. Si Extra indique Using filesort, ce n'est pas automatiquement mauvais, mais cela vous indique que MySQL n'a pas pu renvoyer les lignes dans l'ordre demandé à partir de l'index choisi.

Pour MySQL 8.0.18 et versions ultérieures, EXPLAIN ANALYZE peut être encore plus utile car il exécute la requête et rapporte les temps réels et les comptes de lignes :

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Utilisez-le avec précaution sur les systèmes de production car il exécute l'instruction.

4. Construire des index autour des workflows, pas des tables

Imaginez un écran d'administration qui liste les récents paiements échoués :

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

Un index sur status seul peut encore laisser MySQL trier un grand nombre de lignes échouées. Un index sur (status, created_at) est généralement une meilleure correspondance car MySQL peut trouver les lignes échouées et les lire dans l'ordre temporel. Si la requête ne renvoie que des colonnes dans l'index, vous pourriez envisager un index couvrant :

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

Cela peut être rapide, mais ce n'est pas gratuit. L'index est plus large, prend plus de stockage et coûte plus cher à chaque écriture. Je n'utilise les index couvrants que pour les requêtes chaudes qui s'exécutent assez souvent pour justifier le coût de maintenance supplémentaire.

5. Attention aux index qui semblent corrects mais ne font rien

Ce sont des pièges courants :

• Une fonction cache la valeur indexée : WHERE DATE(created_at) = '2025-01-01'.
• Un joker en tête empêche l'utilisation normale du B-tree : WHERE email LIKE '%@example.com'.
• Une incompatibilité de type force une conversion : comparer une colonne entière à une chaîne entre guillemets peut encore fonctionner, mais cela peut confondre les plans dans les schémas réels.
• L'index commence par la mauvaise colonne pour la requête : (created_at, customer_id) n'est pas la même chose que (customer_id, created_at).

Réécrivez le prédicat lorsque vous le pouvez :

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

Cette forme permet à MySQL d'utiliser une analyse de plage sur created_at.

6. Supprimer soigneusement les index redondants et inutilisés

Le sur-indexation est un problème de performance silencieux. Chaque index secondaire supplémentaire doit être maintenu lors des INSERT, UPDATE et DELETE. Sur une table avec beaucoup d'écritures, cinq index inutilisés peuvent avoir plus d'impact qu'un seul SELECT lent.

Dans MySQL 5.7 et 8.0, le schéma sys peut vous aider à trouver des candidats :

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

Considérez cette sortie comme une piste, pas un ordre. Un index peut sembler inutilisé parce que le serveur a redémarré récemment, parce qu'un rapport mensuel n'a pas encore été exécuté, ou parce que le trafic en staging ne correspond pas à la production. Avant de supprimer un index, vérifiez l'historique de déploiement, les tâches planifiées et les exigences de clés étrangères.

7. Ajouter des index en toute sécurité sur les grandes tables

Sur les petites tables, ALTER TABLE ... ADD INDEX est généralement sans incident. Sur une grande table de production, cela peut être une opération conséquente. Selon la version de MySQL, le moteur de stockage, la définition de la table et le DDL exact, l'ajout d'un index peut utiliser le DDL en ligne ou peut encore créer une pression via des verrous de métadonnées, de l'espace temporaire, la génération de redo et le retard de réplication.

Avant d'ajouter un index volumineux, vérifiez :

• La taille de la table et des index existants.
• Si les réplicas peuvent suivre.
• Si votre version de MySQL prend en charge l'algorithme en ligne que vous attendez.
• Si l'application peut tolérer un verrou de métadonnées si une longue transaction bloque le DDL.

Pour les systèmes sensibles, utilisez un outil de migration tel que pt-online-schema-change ou gh-ost, ou planifiez le DDL pendant une fenêtre à faible trafic.

Une routine pratique de révision des index

Lorsque je révise une requête MySQL lente, j'utilise cet ordre :

1. Capturez le SQL exact avec les valeurs de liaison réelles.
2. Exécutez EXPLAIN et, lorsque c'est sûr, EXPLAIN ANALYZE.
3. Vérifiez si les index existants correspondent au modèle WHERE, JOIN et ORDER BY.
4. Ajoutez le plus petit index composite utile en staging.
5. Comparez les lignes examinées, le temps de requête et l'impact sur les écritures.
6. Déployez soigneusement et surveillez les journaux de requêtes lentes et le retard de réplication.

Cette routine maintient l'indexation honnête. Vous n'essayez pas de collecter des index. Vous essayez de réduire la quantité de travail que MySQL doit effectuer pour les requêtes que votre application exécute réellement.