Comment vérifier et optimiser les index MySQL : guide complet avec SHOW INDEX, EXPLAIN et l’optimisation des performances

1. Introduction

MySQL est une base de données relationnelle largement utilisée dans de nombreuses applications web et systèmes de gestion de données. Pour améliorer la vitesse de récupération des données, un mécanisme appelé « index » existe. Cependant, s’il n’est pas correctement géré, les index peuvent en réalité réduire les performances.

Pourquoi la vérification des index est‑elle importante ?

Un index dans une base de données est similaire à la section d’index d’un livre. Des index correctement conçus améliorent la vitesse d’exécution des requêtes de recherche. Cependant, les problèmes suivants peuvent survenir :

• Les index ne sont pas correctement créés → Peut entraîner des recherches lentes
• Des index inutiles existent → Conduit à des mises à jour et insertions plus lentes
• Incertainité quant aux index utilisés → Rend difficile la décision de supprimer les index inutilisés

Ce que vous apprendrez dans cet article

• Le mécanisme de base des index MySQL
• Comment vérifier les index actuels (à l’aide de commandes SQL)
• Comment gérer et optimiser les index
• Techniques pour analyser l’utilisation des index

Maintenant, apprenons systématiquement les index MySQL et utilisons ces connaissances pour améliorer les performances de la base de données.

2. What Is a MySQL Index?

Un index est une fonctionnalité essentielle pour améliorer les performances d’une base de données. Dans cette section, nous expliquons le concept de base, les types, les avantages et les inconvénients des index.

Basic Concept of Indexes

Un index de base de données est un mécanisme qui permet de rechercher rapidement des valeurs dans des colonnes spécifiques. Par exemple, lorsqu’on recherche un enregistrement précis dans une table contenant une grande quantité de données, sans index, la base de données doit parcourir tous les enregistrements (full table scan). L’application d’un index rend la récupération des données efficace et améliore considérablement la vitesse de traitement.

Types of MySQL Indexes

MySQL prend en charge plusieurs types d’index, chacun adapté à des cas d’utilisation spécifiques.

1. PRIMARY KEY (Primary Key Index)

• Ne peut être défini qu’une seule fois par table
• Garantit l’unicité des lignes de la table
• Fonctionne comme un index clusterisé

1. UNIQUE Index

• Assure que les valeurs dans la colonne spécifiée ne sont pas dupliquées
• Les valeurs NULL sont autorisées (plusieurs NULL sont permis)

1. INDEX (General Index)

• Utilisé pour accélérer les recherches
• Les données dupliquées sont autorisées

1. FULLTEXT Index (pour la recherche texte)

• Optimise la recherche de texte
• Utilisé en combinaison avec la syntaxe MATCH ... AGAINST

1. SPATIAL Index (pour les données géographiques)

• Conçu pour les données spatiales (SIG)

Advantages and Disadvantages of Indexes

Advantages

• Améliore la vitesse de recherche des requêtes
• Renforce les performances des opérations JOIN et des clauses WHERE
• Rend la récupération de données spécifiques plus efficace

Disadvantages

• Un nombre élevé d’index ralentit les opérations INSERT, UPDATE et DELETE
• Consomme de l’espace disque
• Des index mal conçus peuvent dégrader les performances

3. How to Check MySQL Indexes

Pour gérer correctement les index MySQL, il est important de vérifier quels index sont actuellement définis sur une table. Dans cette section, nous expliquons comment vérifier les index à l’aide de la commande SHOW INDEX, INFORMATION_SCHEMA.STATISTICS et de la commande mysqlshow.

SHOW INDEX Command (Basic Method)

Dans MySQL, vous pouvez utiliser la commande SHOW INDEX pour obtenir la liste des index définis sur une table spécifique. Cette commande vous permet de vérifier des détails tels que le nom de l’index, les colonnes couvertes par l’index et l’existence d’une contrainte d’unicité.

Basic Syntax

SHOW INDEX FROM table_name;

Example

Par exemple, pour vérifier les index définis sur la table users, exécutez la requête SQL suivante :

SHOW INDEX FROM users;

Example Output

Using INFORMATION_SCHEMA.STATISTICS (Get Detailed Info)

En utilisant la table système de MySQL INFORMATION_SCHEMA.STATISTICS, vous pouvez récupérer les mêmes informations que SHOW INDEX avec plus de flexibilité.

Check Indexes for a Specific Table

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Obtenir les index dans toute la base de données

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

Commande mysqlshow (Vérifier depuis la CLI)

Vous pouvez également récupérer les informations d’index en utilisant les outils en ligne de commande MySQL. Ceci est particulièrement utile lorsque vous travaillez via SSH sur un serveur MySQL.

Comment exécuter la commande

mysqlshow -u username -p password database_name table_name

Exemple

mysqlshow -u root -p my_database users

Que faire si aucun index n’existe

Si aucun index n’est affiché après l’exécution de SHOW INDEX ou l’interrogation de INFORMATION_SCHEMA.STATISTICS, la table peut ne pas avoir d’index appropriés. Dans ce cas, vous pouvez améliorer les performances de recherche en créant des index selon les besoins.

Créer un nouvel index

CREATE INDEX idx_column ON users (email);

Définir une clé primaire (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Supprimer les index inutiles

ALTER TABLE users DROP INDEX idx_column;

4. Comment vérifier l’utilisation des index

Vérifier si les index fonctionnent correctement est une étape cruciale dans l’optimisation des performances MySQL. Dans cette section, nous expliquons comment déterminer quels index une requête utilise en exploitant la commande EXPLAIN et le Performance Schema.

Analyse de requête avec EXPLAIN

La commande EXPLAIN est utilisée pour visualiser comment une requête SQL donnée sera exécutée. Elle vous aide à analyser les index utilisés, les méthodes d’accès et le plan d’exécution, ce qui la rend utile pour vérifier si les index fonctionnent comme prévu.

Syntaxe de base

EXPLAIN SELECT * FROM table_name WHERE condition;

Exemple

Par exemple, pour rechercher dans la table users en utilisant la colonne email comme condition :

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Exemple de sortie

Points clés

• Si type = ALL, la requête effectue un scan complet de la table, vous avez donc probablement besoin d’un index.
• Si un nom d’index apparaît dans key, cet index est utilisé.
• Si la valeur rows est trop élevée, une optimisation de la requête peut être nécessaire.

Utilisation du Performance Schema

Avec le performance_schema de MySQL, vous pouvez analyser en détail quels index sont utilisés et à quelle fréquence pendant l’exécution des requêtes.

Obtenir les statistiques d’exécution des requêtes

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Vérifier l’utilisation des index pour une table spécifique

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

Que faire si un index n’est pas utilisé

1. Examiner la requête

Si un index n’est pas utilisé, la structure de la requête peut être le problème. Par exemple, le modèle suivant peut empêcher l’utilisation d’un index.

❌ Exemple incorrect (La fonction désactive l’utilisation de l’index)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ En raison de LOWER(email), l’index sur email peut être ignoré.

✅ Exemple corrigé (Éviter d’utiliser une fonction)

SELECT * FROM users WHERE email = 'test@example.com';

2. Recréer l’index

Si un index existant ne fonctionne pas correctement, le supprimer et le recréer peut parfois améliorer les performances.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Mettre à jour les statistiques

Si les statistiques de la table deviennent obsolètes, MySQL peut ne pas utiliser les index de manière optimale. Vous pouvez rafraîchir les statistiques avec la commande suivante :

ANALYZE TABLE users;

5. Gestion des index

Les index MySQL sont essentiels pour améliorer les performances de récupération des données. Cependant, s’ils ne sont pas correctement gérés, ils peuvent dégrader les performances globales de la base de données. Dans cette section, nous expliquons en détail comment créer, supprimer et identifier les index inutiles.

Création des index

En créant des index appropriés, vous pouvez accélérer considérablement les recherches de données. Dans MySQL, vous pouvez ajouter des index en utilisant CREATE INDEX ou ALTER TABLE.

Syntaxe de base

CREATE INDEX index_name ON table_name(column_name);

Exemple

Pour ajouter un index à la colonne email dans la table users :

CREATE INDEX idx_email ON users(email);

Index multi-colonnes (Index composite)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Index unique

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Définition d’une clé primaire (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Suppression des index

Supprimer les index inutiles aide à réduire la surcharge de la base de données.

Syntaxe de base

ALTER TABLE table_name DROP INDEX index_name;

Exemple

Par exemple, pour supprimer un index nommé idx_email :

ALTER TABLE users DROP INDEX idx_email;

Identification et suppression des index inutiles

Vérification des index non utilisés

SELECT * FROM sys.schema_unused_indexes;

Vérification du statut de la table (Impact des index)

SHOW TABLE STATUS LIKE 'users';

Suppression des index inutiles

ALTER TABLE users DROP INDEX idx_unused;

Après la suppression, il est recommandé de mettre à jour les statistiques en utilisant ANALYZE TABLE.

ANALYZE TABLE users;

6. Optimisation des index (Amélioration des performances)

Une gestion appropriée des index peut améliorer considérablement les performances des requêtes MySQL. Cependant, créer simplement des index ne suffit pas — une conception, une gestion et une surveillance appropriées sont nécessaires pour maintenir des performances optimales.

Conception appropriée des index

Des index bien conçus peuvent améliorer de manière spectaculaire la vitesse de recherche dans MySQL.

Cas où les index doivent être appliqués

Cas où les index ne doivent pas être appliqués

Utilisation du journal des requêtes lentes

Le journal des requêtes lentes vous permet d’identifier les requêtes longues et d’analyser quels index ne sont pas appliqués.

Activation du journal des requêtes lentes

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Vérification du journal des requêtes lentes

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Analyse d’une requête lente

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Exemple d’application d’un index

CREATE INDEX idx_last_login ON users(last_login);

Mise à jour des statistiques (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Mise à jour des statistiques)

ANALYZE TABLE users;

OPTIMIZE TABLE (Défragmentation)

OPTIMIZE TABLE users;

7. FAQ sur les index (Questions fréquemment posées)

Les index MySQL sont un mécanisme essentiel pour améliorer les performances de la base de données. Cependant, s’ils ne sont pas correctement gérés, ils peuvent avoir l’effet inverse. Dans cette section, nous résumons les questions fréquemment posées (FAQ) et les réponses relatives aux index MySQL.

La vitesse de recherche s’améliore-t-elle en ajoutant plus d’index ?

R. Pas nécessairement.

Les index sont conçus pour améliorer les performances des requêtes, mais ajouter trop d’index peut en fait dégrader les performances de la base de données.

• Augmente la surcharge d’écriture (INSERT, UPDATE, DELETE)
• L’utilisation d’un index dépend de la requête
• Les index inutiles consomment de la mémoire

Quelles colonnes doivent avoir des index ?

R. Il est efficace d’appliquer des index aux types de colonnes suivants :

Les index sont-ils créés automatiquement ?

R. Certains index sont créés automatiquement, mais d’autres doivent être ajoutés manuellement.

Index Créés Automatiquement

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Index Créés Manuellement

CREATE INDEX idx_email ON users(email);

Comment choisir entre les index B-Tree, Hash et FULLTEXT ?

Comment vérifier la taille des index ?

SHOW TABLE STATUS LIKE 'users';

Comment vérifier si un index est utilisé ?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

Quand dois-je supprimer un index ?

Supprimer un Index Inutile

ALTER TABLE users DROP INDEX idx_unused;

8. Résumé

Dans cet article, nous avons couvert de manière exhaustive les index MySQL — des fondamentaux aux méthodes de vérification, à la gestion, à l’optimisation et aux questions fréquemment posées. Ici, nous passons en revue les points clés de chaque section et résumons les meilleures pratiques pour optimiser la gestion des index MySQL.

Points Clés à Retenir

Qu’est-ce qu’un Index MySQL ?

• Un index est un mécanisme qui accélère la récupération des données.
• Il existe plusieurs types, y compris les index B-Tree, Hash et FULLTEXT .
• Le plus efficace lorsqu’il est appliqué aux colonnes utilisées dans les clauses WHERE, JOIN et ORDER BY.

Comment Vérifier les Index MySQL

• Utilisez la commande SHOW INDEX pour afficher la liste des index.
• Utilisez INFORMATION_SCHEMA.STATISTICS pour récupérer des informations détaillées.

Comment Vérifier l’Utilisation des Index

• Utilisez EXPLAIN pour voir quels index une requête utilise.
• Utilisez Performance Schema pour analyser la fréquence d’utilisation des index.

Gestion des Index

• Utilisez CREATE INDEX pour appliquer des index aux colonnes appropriées.
• Utilisez ALTER TABLE DROP INDEX pour supprimer les index inutiles.
• Vérifiez la taille des index avec SHOW TABLE STATUS et optimisez si nécessaire.

Optimisation des Index (Amélioration des Performances)

• Appliquez des index aux colonnes WHERE, JOIN et ORDER BY fréquemment utilisées.
• Utilisez le Journal des Requêtes Lentes pour identifier et optimiser les requêtes lentes.
• Mettez à jour les statistiques avec ANALYZE TABLE et OPTIMIZE TABLE.

Meilleures Pratiques pour la Gestion des Index MySQL

1. Identifiez les goulots d’étranglement des requêtes avant d’appliquer des index.
2. Sélectionnez des index appropriés.

• Utilisez correctement les index sur une seule colonne et les index composites.
• Utilisez UNIQUE INDEX là où des contraintes d’unicité sont requises.

1. Supprimez régulièrement les index inutiles.

• Identifiez les index inutilisés en utilisant SHOW INDEX et schema_unused_indexes.

1. Mettez à jour les statistiques régulièrement.

• Mettez à jour les statistiques en utilisant ANALYZE TABLE.
• Exécutez OPTIMIZE TABLE pour résoudre la fragmentation causée par les suppressions et les mises à jour.

Étapes Suivantes

✅ Liste de Vérification Pratique
✅ Avez-vous vérifié les index actuels en utilisant SHOW INDEX ?
✅ Avez-vous activé le Journal des Requêtes Lentes et identifié les requêtes lentes ?
✅ Avez-vous analysé le plan d’exécution des requêtes en utilisant EXPLAIN ?
✅ Avez-vous supprimé les index inutiles et créé des index appropriés ?
✅ Avez-vous mis à jour les statistiques en utilisant ANALYZE TABLE ?

Résumé Final

• Une gestion appropriée des index MySQL améliore significativement les performances de recherche.
• Utilisez le Journal des Requêtes Lentes et EXPLAIN pour analyser l’efficacité des index et optimiser en conséquence.
• Maintenez les performances de la base de données en mettant à jour régulièrement les statistiques et en optimisant les tables.

Ceci conclut le guide complet sur la gestion des index MySQL.

Utilisez ces connaissances pour construire des systèmes de base de données plus rapides et plus efficaces. 💡🚀