Un index est une structure auxiliaire qui évite au moteur de parcourir toute une table pour retrouver des lignes. L'analogie est simple : c'est l'index en fin de livre, qui vous mène directement à la page utile au lieu de vous faire lire les 300 autres.
Le principe
- Création : lorsque vous indexez une ou plusieurs colonnes, le SGBD construit une structure séparée — le plus souvent un arbre B ou B-tree — qui associe chaque valeur à l'emplacement physique des lignes correspondantes.
- Recherche : à l'exécution d'une requête, l'optimiseur compare le coût d'un parcours séquentiel (full scan) à celui d'une recherche par index. Si l'index est sélectif, il l'utilise et accède directement aux lignes voulues.
- Gain : la complexité passe d'un balayage de N lignes à un parcours logarithmique. Sur une table de plusieurs millions d'enregistrements, l'écart se compte en secondes plutôt qu'en minutes.
Les principaux types
- Unique : garantit l'absence de doublons, typiquement sur une clé primaire.
- Non unique : autorise les valeurs répétées, utile sur une colonne de filtrage fréquent.
- Clusterisé : la table elle-même est physiquement ordonnée selon la clé d'index — une seule par table.
- Non clusterisé : structure distincte renvoyant aux lignes d'origine.
- Composite : plusieurs colonnes ordonnées ; l'ordre de définition détermine les requêtes qu'il sert.
- Partiel ou filtré, fonctionnel, couvrant : variantes qui réduisent la taille de l'index ou évitent l'accès à la table.
Avantages
- Requêtes de recherche nettement plus rapides.
- Réduction des lectures disque (opérations d'E/S).
- Tri et regroupement accélérés, ce qui bénéficie aux ORDER BY, GROUP BY et jointures.
- Contrôle d'intégrité via les contraintes d'unicité.
Coûts à connaître
- Espace disque supplémentaire.
- Écritures ralenties : chaque INSERT, UPDATE ou DELETE doit maintenir les index à jour.
- Maintenance : fragmentation, statistiques à rafraîchir, reconstruction périodique.
- Pièges de l'optimiseur : une fonction appliquée à une colonne indexée ou un typage implicite peut rendre l'index inutilisable.
Où placer les index ?
- Colonnes filtrées fréquemment dans les WHERE.
- Clés étrangères, pour accélérer les jointures.
- Toutes les grandes tables réellement sollicitées.
- Colonnes à forte sélectivité, plutôt que celles qui n'ont que trois valeurs distinctes.
En résumé : bien conçus, les index transforment les performances en lecture. Mal choisis ou trop nombreux, ils dégradent les écritures. La bonne méthode consiste à mesurer les requêtes lentes avec un plan d'exécution, puis à indexer en conséquence — jamais par principe.