Les Index en SQL

Qu'est-ce qu'un index en SQL ?

Les index sont des structures de données spécialisées qui permettent d'organiser une table afin d'accélérer les recherches dans une base de données. Ils se basent sur une structure d'arbre équilibré, ce qui permet au serveur de cibler seulement un sous-ensemble de lignes pertinentes au lieu de parcourir chaque ligne de manière séquentielle.

Comment choisir une colonne pour l'index ?

Bien qu'un index structure les valeurs d'une table entière, l'organisation se fait en fonction d'une colonne spécifique. Idéalement, cette colonne devrait être fréquemment consultée et contenir une variété de données. Les colonnes rarement utilisées dans des requêtes devraient être exclues, tout comme celles avec beaucoup de valeurs identiques ou un grand nombre de valeurs NULL.

Scénarios d'utilisation des index

Les index sont particulièrement utiles pour les grandes tables où un petit sous-ensemble de données est fréquemment recherché. Si en revanche toutes les lignes sont souvent consultées (comme pour calculer un total), l'indexation peut apporter peu de bénéfices. Dans le cas de tables plus petites, les gains de vitesse sont moins significatifs car la plupart des lignes doivent de toute façon être examinées.

Entretien des Index

Un inconvénient à garder à l'esprit est que les index doivent être mis à jour à chaque opération INSERT, UPDATE ou DELETE. Cela peut rendre ces opérations plus lentes. Par conséquent, il serait sage d'éviter les index sur des tables où les écritures et les mises à jour sont fréquentes.

Fragmentation des index

Avec le temps, les données d'un index peuvent se fragmenter sur le disque, ce qui peut nuire aux performances. Pour y remédier, il est possible de supprimer et de recréer l'index, bien que la méthode puisse varier selon la version de la base de données.

Syntaxe des commandes d'indexation

On peut créer des index lors de la création d'une table comme suit :

CREATE TABLE table_name (
  column_name datatype,
  column_name datatype,
  ...
  INDEX index_name (index_column_name)
);

Il est également possible d'ajouter un index à une table existante :

CREATE INDEX index_name ON table_name (column_name);

Exemples pratiques

Pour créer un index nommé id_index sur la colonne student_id de la table students, utilisez la commande suivante :

CREATE INDEX id_index ON students (student_id);

Création d'un index unique

Les index fonctionnent mieux sur des ensembles de données variés. Il est donc recommandé d'utiliser des indexes uniques pour empêcher les valeurs dupliquées, ce qui est particulièrement pertinent pour les colonnes qui sont des clés primaires. Pour créer un index unique sur student_id, utilisez la requête suivante :

CREATE UNIQUE INDEX unique_id_index ON students (student_id);

Suppression d'un index

Pour supprimer l'index créé sur student_id, on peut utiliser :

DROP INDEX id_index;