Aller au contenu principal

Index

Progression

#Indexes

Accélèrent la recherche au prix d'espace et de coûts d'écriture. Ils servent surtout aux clauses WHERE, JOIN et tri.

Objectifs d’apprentissage

  • Concevoir des index utiles: colonnes filtrantes, ordre (égalité → range), couverture.
  • Comprendre SARGability: écrire des prédicats indexables et éviter les fonctions sur colonnes.
  • Mesurer les compromis: espace, coût d’insertion/MAJ, maintien de la statistique.
sqlsql
1create table t(id int, x int);2insert into t values (1,10),(2,20),(3,30);3create index if not exists idx_t_x on t(x);4-- La requête peut utiliser l'index sur x5select * from t where x >= 20;

Note: Dans SQLite en WASM, on ne peut pas inspecter facilement le plan d’exécution, mais le principe reste valable.

#Règles pratiques (SARGability)

  • Éviter les fonctions sur la colonne indexée côté gauche: where date(ts) = '2024-09-01' empêche l’index. Préférer des bornes: where ts >= '2024-09-01' and ts < '2024-09-02'.
  • LIKE 'prefix%' utilise un index; LIKE '%suffix' non (sauf index spécialisés).
  • Casts implicites peuvent casser l’index (ex: comparer int à text). Harmoniser les types.
  • Conditions combinées sur (a,b): l’index (a,b) aide pour a=? et a=? and b between ...; l’ordre compte.

#Index composites et couverture

  • Ordre des colonnes: placez d’abord celles testées par égalité (= ou IN), puis range (>/</BETWEEN), puis tri (ORDER BY).
  • Index couvrant: inclure toutes les colonnes lues (selon moteur: colonnes incluses) pour éviter les lookups.
  • Sélectivité: privilégier les colonnes très discriminantes (haute cardinalité) pour maximiser le gain.
Coûts d’écriture

Chaque INSERT/UPDATE/DELETE met à jour les index concernés. Trop d’index ralentissent l’écriture et les migrations; supprimez ceux non utilisés.

#Quand ne pas indexer

  • Colonnes à faible cardinalité (peu de valeurs distinctes) utilisées seules dans WHERE (ex.: booléen) — peu sélectif; combinez avec d’autres colonnes ou index partiels.
  • Tables toutes petites — le scan séquentiel est souvent plus rapide et plus simple.
  • Colonnes très souvent mises à jour si l’index n’apporte pas de gain réel.

#Types d’index (aperçu)

  • B‑Tree: par défaut, efficace pour égalité et plages ordonnées; supporte ORDER BY.
  • Hash: égalité seulement (selon moteur); pas d’ordre.
  • GIN/GiST (PostgreSQL): pour documents, tableaux, recherche plein‑texte, géo. Choisir selon le type et les opérateurs.

#Statistiques et plans

Les moteurs s’appuient sur des statistiques (histogrammes, NDV). Des stats obsolètes peuvent conduire à des plans sous‑optimaux. Exécutez l’analyse/ANALYZE périodiquement si votre moteur le requiert.

#Playground

Chargement de l’éditeur...

#Quiz éclair

  1. Laquelle de ces clauses favorise l’usage d’un index (ts) ?
  • where date(ts) = '2025-01-01'
  • where ts >= '2025-01-01' and ts < '2025-01-02'
  • where strftime('%Y', ts) = '2025'

Réponse: bornes sur ts (seconde option).

#Animation: recherche via B-Tree (concept)

Page racine
Choix de la branche selon les clés
Descente
2–4 lectures log(N) jusqu’à la feuille
Feuille
Trouver le range de clés (≥/=)
Lookup table
Aller lire les lignes si non couvert
Couvrant
Tout dans l’index → pas de lookup

#Pas‑à‑pas: couvrant vs lookup

Un index (a) permet de localiser rapidement les lignes pour where a=? ou a>=…
Étape 1 / 4

#Mini‑lab: ordre des colonnes d’un index composite

Objectif: essayer des requêtes du type where user_id=? and created_at between ... order by created_at.

  1. Créez un index (created_at, user_id) et observez les requêtes qui en tirent parti ou non.
  2. Inversez l’ordre (user_id, created_at) et comparez LA compatibilité avec égalité→plage→tri.
Principe

Placez d’abord les colonnes testées par égalité, puis celles en plage, puis celles utilisées au tri. Les prédicats doivent être SARGables (pas de fonction sur colonne).

#Exercice : Comparaison de performances avec et sans index

Comparez les performances de requêtes SQL avec et sans index sur une table de grande taille.

#Instructions

  1. Créez une table avec un grand nombre d'enregistrements.
  2. Exécutez une requête de recherche sans index et mesurez le temps.
  3. Créez un index approprié et réexécutez la même requête.
  4. Comparez les performances et expliquez les différences.

#Exemple de requête

sqlsql
1-- Création d'une table de test avec beaucoup de données2create table test_performance(id int, name text, category int);3 4-- Insertion de données de test (simulation)5WITH RECURSIVE6  cnt(x) AS (7     SELECT 18     UNION ALL9     SELECT x+1 FROM cnt10     LIMIT 1000011  )12INSERT INTO test_performance 13SELECT x, 'Name' || x, x % 100 FROM cnt;14
Chargement de l’éditeur...