Indexation en MySQL

Qu'est-ce que l'indexation ?

L'indexation dans MySQL peut être comparée à un système de raccourcis permettant de retrouver rapidement des informations dans une base de données. De la même façon qu'un index dans un livre aide à localiser des sujets sans devoir feuilleter toutes les pages, un index de base de données optimise le processus pour que MySQL trouve des lignes sans avoir à parcourir l'intégralité de la table. Cette technique améliore considérablement la rapidité des requêtes de recherche, rendant l'extraction de données beaucoup plus efficace.

Fonctionnement des Index

Les index sont générés sur des colonnes spécifiques d'une table. Lorsque l'on crée un index sur une colonne, MySQL organise et conserve les informations relatives à celle-ci de manière triée, généralement en utilisant une structure dénommée B-tree. Cela permet à MySQL d'identifier rapidement les lignes en fonction des valeurs de la colonne indexée.

Les types d’index les plus utilisés comprennent :

• Index de clé primaire : Créé automatiquement lorsqu'une clé primaire est définie.
• Index unique : Garantit l'unicité de toutes les valeurs dans une colonne.
• Index ordinaire : Accélère les récupérations mais permet des valeurs en double.
• Index de texte intégral : Utilisé pour les recherches en texte intégral.
• Index composite : Optimise les requêtes impliquant plusieurs colonnes.

Création d'Index

Les index peuvent être établis lors de la création initiale de la table via la commande CREATE TABLE, ou peuvent être ajoutés ultérieurement en recourant à la commande CREATE INDEX.

Création d'Index lors de la création d'une table
Dans cet exemple, un index est créé sur la colonne nom_de_famille.

CREATE TABLE employés (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prénom VARCHAR(50),
    nom_de_famille VARCHAR(50),
    email VARCHAR(100),
    INDEX (nom_de_famille)
);

Ajout d'Index sur une table existante
Cette requête ajoute un index à la colonne nom_de_famille de la table employés.

CREATE INDEX idx_nomfamille ON employés(nom_de_famille);

Utilisation des Index

Lorsque vous exécutez une requête, l'optimiseur de requêtes de MySQL détermine s'il doit utiliser un index. Voici des illustrations de comment l'indexation peut optimiser les performances :

Exemple sans index
Sans index sur nom_de_famille, MySQL doit scanner chaque ligne de la table employés pour retrouver celles où nom_de_famille est ‘Dupont’.

SELECT * FROM employés WHERE nom_de_famille = 'Dupont';

Exemple avec index
Avec un index sur nom_de_famille, MySQL réussit à localiser rapidement toutes les lignes où nom_de_famille est ‘Dupont’.

CREATE INDEX idx_nomfamille ON employés(nom_de_famille);
SELECT * FROM employés WHERE nom_de_famille = 'Dupont';

Visualiser les Index

Tous les index d'une table peuvent être visualisés grâce à la commande SHOW INDEX :

SHOW INDEX FROM employés;

Supprimer des Index

Pour retirer un index, la commande DROP INDEX est utilisée :

DROP INDEX idx_nomfamille ON employés;

Considérations concernant l'utilisation des Index

• Amélioration des performances : Les index peuvent considérablement accélérer les performances des requêtes.
• Surcoût de stockage : Les index nécessitent un espace de stockage supplémentaire.
• Performance d'insertion/mise à jour : Les index peuvent ralentir les opérations d'insertion et de mise à jour car l'index doit être actualisé.
• Choix des colonnes : Tous les colonnes ne nécessitent pas d'index. Il est préférable d'indexer les colonnes fréquemment recherchées ou utilisées dans les opérations de jointure.

Exemple d'Index Composite

Un index composite est un index portant sur plusieurs colonnes.

CREATE INDEX idx_nom_email ON employés(nom_de_famille, email);

Cet index composite peut être utilisé pour des requêtes filtrant uniquement par nom_de_famille, à la fois nom_de_famille et email, ou email seul (avec certaines limitations).

Exemple pratique

Voici un exemple pratique plus détaillé :
Création de la table

CREATE TABLE produits (
    id_produit INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100),
    catégorie VARCHAR(50),
    prix DECIMAL(10, 2),
    stock INT
);
CREATE INDEX idx_catégorie_prix ON produits(catégorie, prix);

Insertion de données

INSERT INTO produits (nom, catégorie, prix, stock) VALUES
('Ordinateur Portable', 'Électronique', 999.99, 50),
('Smartphone', 'Électronique', 699.99, 100),
('Bureau', 'Mobilier', 199.99, 20),
('Tablette', 'Électronique', 299.99, 75),
('Casque', 'Électronique', 199.99, 150),
('Moniteur', 'Électronique', 179.99, 80),
('Canapé', 'Mobilier', 499.99, 10);

Requête avec index

SELECT * FROM produits WHERE catégorie = 'Électronique' AND prix < 800;

Résultat attendu :
En fonction des données insérées, la requête restituera les lignes où la catégorie est ‘Électronique’ et le prix inférieur à 800.

Conclusion

Les index en MySQL sont des outils puissants pour accroître l'efficacité des opérations de récupération de données. Ils s'avèrent particulièrement utiles pour accélérer les requêtes de recherche, améliorer les opérations de jointure, optimiser les performances de tri, imposer des contraintes d'unicité et permettre des recherches en texte intégral efficaces. Cependant, leur utilisation engendre des compromis, notamment en termes de stockage, d'impact sur les performances d'écriture, et la nécessité d'une gestion attentive. Des index bien conçus et bien implémentés peuvent améliorer de manière significative la performance d'une base de données, mais il est crucial de prendre en compte leurs limitations et de les utiliser avec discernement.