Leçon 10.4 · Temps de lecture : ~10 min

Cette leçon introduit les index SQL et leur rôle dans la performance des requêtes. Vous apprendrez ce qu'est un index, comment il aide à trouver des données plus vite, et pourquoi il peut parfois ralentir les opérations de modification. Nous verrons des exemples de base de création et de vérification d'index sur les tables Sakila. À la fin de la leçon, vous pourrez utiliser les index de façon plus consciente pour accélérer vos requêtes.

Introduction aux index SQL

Dans la leçon précédente, nous avons appris à lire les plans d'exécution avec EXPLAIN et à trouver les goulots d'étranglement. L'étape suivante est de comprendre le mécanisme principal d'accélération des recherches : les index.

Les index sont directement liés à la façon dont le SGBD cherche les lignes. Sans index, le serveur parcourt souvent toute la table. Avec un index adapté, il peut accéder aux données pertinentes beaucoup plus vite.

Qu'est-ce qu'un index

Un index SQL est une structure de données supplémentaire qui aide le SGBD à trouver plus rapidement des lignes par valeur de colonne.

Une analogie simple est l'index d'un livre. Au lieu de lire toutes les pages, vous allez directement à la section voulue.

Dans les bases relationnelles, les index B-tree sont les plus courants et fonctionnent bien pour :

• recherche exacte (=);
• plages (>, <, BETWEEN);
• tri (ORDER BY) sur colonnes indexées.

Comment l'index impacte la performance

Il accélère la lecture (SELECT)

Quand une condition WHERE utilise une colonne indexée, le SGBD peut trouver les lignes sans scan complet de table.

Il peut ralentir l'écriture (INSERT, UPDATE, DELETE)

Chaque index doit être maintenu à jour. Quand les données changent, le SGBD met à jour la table et les index associés.

Il occupe de l'espace supplémentaire

Les index sont stockés à part et consomment du disque. Indexer toutes les colonnes est généralement une mauvaise stratégie.

Syntaxe de base

Création d'un index simple :

CREATE INDEX idx_customer_last_name
ON customer (last_name);

Suppression d'un index (syntaxe selon SGBD) :

DROP INDEX idx_customer_last_name ON customer;

Remarque : en PostgreSQL, la forme est DROP INDEX index_name; sans nom de table.

Exemple 1 : accelerer un filtre sur une colonne

Supposons qu'on recherche souvent des clients par nom de famille :

SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

Sans index sur last_name, le SGBD peut faire un scan complet de customer. Après création de l'index, le type d'accès devient en général plus efficace.

Vérification avec EXPLAIN :

EXPLAIN
SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

Résultat : dans le plan d'exécution, vous devriez voir l'utilisation d'un index (key/possible_keys en MySQL ou Index Scan en PostgreSQL).

Exemple 2 : index compose

Si des requêtes filtrent souvent sur deux champs ensemble, un index composé est utile.

CREATE INDEX idx_payment_customer_date
ON payment (customer_id, payment_date);

Une requête qui correspond bien à cet index :

SELECT
   payment_id,
   customer_id,
   amount,
   payment_date
FROM payment
WHERE customer_id = 15
  AND payment_date >= '2005-07-01'
ORDER BY payment_date;

Remarque : l'ordre des colonnes dans un index composé est important. Dans beaucoup de cas, mettez d'abord le champ le plus filtré.

Quand un index peut ne pas être utilisé

Même si l'index existe, l'optimiseur peut l'ignorer. Raisons fréquentes :

• fonction sur la colonne indexée dans WHERE (YEAR(payment_date));
• recherche avec % en début (LIKE '%abc');
• faible sélectivité de la colonne;
• ordre de colonnes peu adapté dans un index composé.

Exemple d'une condition qui bloque souvent l'index :

SELECT
   payment_id,
   payment_date
FROM payment
WHERE YEAR(payment_date) = 2005;

Version plus favorable aux index :

SELECT
   payment_id,
   payment_date
FROM payment
WHERE payment_date >= '2005-01-01'
  AND payment_date < '2006-01-01';

Recommandations pratiques

• Ajoutez des index pour des requêtes fréquentes réelles, pas "au cas où".
• Commencez par les colonnes de WHERE, JOIN et ORDER BY.
• Après ajout d'un index, comparez les plans avec EXPLAIN.
• Gardez un équilibre : trop d'index pénalise les écritures.

Points clés de cette leçon :

• Un index est une structure qui accélère la recherche de lignes.
• Les index accélèrent souvent SELECT, mais peuvent ralentir INSERT, UPDATE et DELETE.
• Index simple et index composé répondent à des besoins de filtrage différents.
• L'ordre des colonnes est critique dans un index composé.
• EXPLAIN permet de vérifier si le SGBD utilise l'index créé.

Questions fréquentes

Une clé primaire crée-t-elle un index automatiquement ?

Oui. Dans MySQL, PostgreSQL et SQL Server, les contraintes PRIMARY KEY et UNIQUE créent automatiquement un index. Pour les clés étrangères, cela dépend : MySQL (InnoDB) crée un index sur la colonne de la clé étrangère s'il n'en existe pas, alors que PostgreSQL ne le fait pas : il faut l'ajouter vous-même.

Combien d'index une table doit-elle avoir ?

Il n'y a pas de nombre fixe. Chaque index accélère les requêtes qui lui correspondent, mais ralentit INSERT, UPDATE et DELETE et occupe de l'espace disque. Indexez les colonnes des WHERE, JOIN et ORDER BY fréquents, et supprimez les index inutilisés ou en double : un index sur (customer_id) est redondant à côté d'un index sur (customer_id, payment_date).

Comment voir les index d'une table ?

Dans MySQL, exécutez SHOW INDEX FROM customer;. Dans PostgreSQL, utilisez \d customer dans psql ou interrogez la vue système : SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customer';.

Questions d'entretien

Qu'est-ce qu'un index SQL et pourquoi est-il utile ?

Un index est une structure de données supplémentaire qui accélère la recherche de lignes par valeur de colonne. Il est utile car il réduit le temps de lecture et le volume de données scannées.

Pourquoi un index peut accélérer SELECT mais ralentir INSERT ?

En lecture, l'index permet de localiser plus vite les lignes. En écriture, le SGBD doit mettre à jour la table et les structures d'index, ce qui ajoute du travail.

Comment vérifier qu'un index est réellement utilisé ?

Exécutez EXPLAIN sur la requête et analysez le plan : type d'accès, index choisi et estimation du nombre de lignes.