#Index : accélérer la lecture, payer à l'écritureCopier le lien

Un index est une structure de données dérivée (le plus souvent un arbre équilibré, B-Tree) qui permet de trouver des lignes sans balayer toute la table. Il accélère WHERE, JOIN et ORDER BY, au prix d'espace disque et d'un surcoût à chaque INSERT, UPDATE et DELETE.

#Prérequis et objectifsCopier le lien

• Prérequis : SELECT (prédicats, SARGability), JOIN (clés étrangères), et une intuition de la complexité logarithmique.
• Décider quel index créer, et dans quel ordre placer les colonnes d'un index composite.
• Prédire si un prédicat peut exploiter un index.
• Expliquer pourquoi trop d'index dégrade les écritures, et quand ne pas indexer.

#Comment ça marche : la recherche B-TreeCopier le lien

Une table d'un million de lignes demande environ 3 niveaux d'arbre : la racine, un niveau intermédiaire, les feuilles. Trouver les lignes avec where id = 42 coûte trois lectures de pages au lieu d'un million. C'est tout le gain, et il se paie en écriture.

#Moteurs de stockage : InnoDB et MyISAMCopier le lien

Sous MySQL — le SGBD utilisé en TP — le comportement des index et des clés étrangères dépend du moteur de tables choisi. Deux moteurs dominent :

InnoDB est le moteur transactionnel : c'est lui qui rend le chapitre Transactions applicable, et lui qui permet de déclarer des clés étrangères. MyISAM, sans transactions ni clés étrangères, ne peut garantir l'intégrité référentielle — le moteur accepte la syntaxe mais l'ignore. Le choix n'est donc pas cosmétique : engine=InnoDB est ce qui rend un schéma relationnel réellement contraint.

Conséquence pour les index : sous InnoDB, les clés étrangères s'appuient sur des index. Déclarer une clé étrangère sans index sur la colonne correspondante oblige le moteur à balayer la table enfant à chaque vérification d'intégrité référentielle — c'est-à-dire à chaque INSERT, UPDATE et DELETE sur la table parente. MySQL crée souvent cet index automatiquement, mais le déclarer explicitement documente l'intention et évite les surprises lors d'une migration.

C'est exactement ce que recommande le cours lorsqu'il demande, pour la création du schéma, d'ajouter les attributs de liaison « avec index ».

#Premier exemple, mesurableCopier le lien

1explain query plan select count(*) from logs where ts = '2026-02-01';2 3-- Génération de 20 000 lignes (SQLite, requête récursive)4with recursive cnt(x) as (5  select 1 union all select x + 1 from cnt limit 200006)7insert into logs select8  date('2026-01-01', '+' || (x % 100) || ' days'),9  case when x % 17 = 0 then 'ERROR' else 'INFO' end,10  'message ' || x11from cnt;12 13-- Observation du plan AVANT l'index14explain query plan select count(*) from logs where ts = '2026-02-01';

Avant l'index, le plan annonce un SCAN logs : les 20 000 lignes sont lues. Après, il annonce SEARCH logs USING COVERING INDEX idx_logs_ts (ts=?) : seules les lignes de la date sont touchées, et la table n'est même pas consultée puisque count(*) se satisfait de l'index. Sur ce jeu, exactement 200 lignes par date — 20 000 lignes réparties sur 100 dates distinctes.

#L'outil d'observation : EXPLAIN QUERY PLANCopier le lien

explain query plan est l'incantation SQLite qui révèle la stratégie du moteur. Elle fonctionne dans le playground du module et dans la CLI sqlite3. Les mentions à guetter :

• SCAN table : balayage complet. Légitime sur une petite table ou si la requête lit la majorité des lignes.
• SEARCH table USING INDEX nom : la recherche passe par l'index. C'est l'objectif sur les prédicats sélectifs.
• SEARCH table USING COVERING INDEX nom : l'index suffit, la table n'est même pas lue.

PostgreSQL expose l'équivalent avec EXPLAIN ANALYZE, MySQL avec EXPLAIN. La syntaxe change, la démarche est la même : observer avant, modifier, observer après.

#SARGability : écrire des prédicats indexablesCopier le lien

Règles concrètes :

• Pas de fonction sur la colonne indexée : préférer ts >= '2026-01-05' and ts < '2026-01-06' à date(ts) = '2026-01-05'.
• LIKE 'prefix%' utilise l'index ; LIKE '%suffix' ne le peut pas (la recherche ne démarre pas d'une borne connue).
• Harmoniser les types : comparer un entier à une chaîne de caractères provoque une conversion qui neutralise l'index.
• Un prédicat très peu sélectif (une valeur sur deux) vaut mieux en SCAN qu'en index : l'index ramènerait la moitié de la table, lookup après lookup.

#Index composites : l'ordre décideCopier le lien

Pour where user_id = ? and created_at between ? and ? order by created_at, l'index idéal est (user_id, created_at) :

• égalité d'abord : la branche user_id est fixée, une seule sous-branche à descendre ;
• plage ensuite : à l'intérieur de ce user_id, les feuilles sont ordonnées par created_at, la plage est contiguë ;
• tri gratuit : l'ordre des feuilles est déjà created_at.

L'index inverse (created_at, user_id) ne sert que la première colonne du prédicat de plage : passé la comparaison sur created_at, le filtre user_id s'applique ligne à ligne. La règle mnémotechnique : égalité, plage, tri, dans cet ordre.

#Quand ne pas indexerCopier le lien

• Table petite : quelques pages, le scan est direct et l'index ajoute de l'écriture pour rien.
• Colonne à faible cardinalité seule dans le WHERE (booléen, statut à trois valeurs) : peu sélectif. Un index partiel sur la valeur rare, where statut = 'ERREUR', reste pertinent (SQLite : create index ... on t(statut) where statut = 'ERREUR').
• Colonne massivement mise à jour sans gain de lecture mesuré.

#Types d'index : un aperçuCopier le lien

• B-Tree : le défaut partout. Égalité, plages, tri. C'est celui que toutes les sections ci-dessus décrivent.
• Hash : égalité uniquement, pas d'ordre (PostgreSQL, engines mémoire MySQL).
• GIN / GiST (PostgreSQL) : plein-texte, tableaux, JSONB, géométrie. Le choix suit les opérateurs utilisés, pas la mode.

#Playground : mesurer avant et aprèsCopier le lien

Dans l'éditeur ci-dessous, exécutez le script une première fois tel quel (plan AVANT, notez SCAN), puis retirez le commentaire du create index et réinitialisez la base avec Nouvelle base avant de réexécuter : un CREATE INDEX n'affecte que les requêtes suivantes, et le playground garde la base entre deux exécutions.

#Exercice : choisir et vérifier un index compositeCopier le lien

Reprenez la table logs et la requête select niveau, count(*) from logs where ts >= '2026-03-01' and ts < '2026-04-01' and niveau = 'ERROR' group by niveau.

1. Quel index composite créez-vous, et dans quel ordre les colonnes ?
2. Vérifiez avec explain query plan que le plan passe de SCAN à SEARCH.
3. Pourquoi cet index est-il couvrant pour cette requête ?

#QuizCopier le lien