Polar Code 🎭

Command Palette

Search for a command to run...

Retour au dossier : Faites entrer SQL
06
Pièce N°06

SCENE 6 : Les Index - La Mémoire de la Ville

La nuit est épaisse. Les dossiers s'empilent. Sans repères, on se perd. Un index, c'est un raccourci. Une trace dans la mémoire. Le doigt qui pointe vers le corps.

Introduction aux Index – Pourquoi Chercher dans le Noir ?

Sans index, une recherche, c'est une fouille intégrale.
Un SELECT * FROM client WHERE nom = 'Morand' parcourt toute la table.
Ligne après ligne. Comme éclairer chaque visage dans une foule.
Ça marche. Jusqu'à ce que la foule devienne une ville.

L'index, c'est le fichier alphabétique.
Le registre des noms avec un numéro de dossier.
On ne cherche plus dans la pile, on regarde dans l'index.
C'est rapide. C'est la différence entre trouver une aiguille dans une botte de foin, et la trouver dans une boîte étiquetée "aiguilles".

📚 Comprendre les Index – L'Anatomie d'un Raccourci

Comment ça marche ? Le B-Tree, l'Arbre de la Connaissance

L'index le plus courant est l'arbre B (B-Tree).
Imaginez un annuaire téléphonique.

  • Racine : La première lettre des noms (A-M, N-Z).
  • Branches : Les lettres suivantes.
  • Feuilles : Les noms complets, avec le pointeur vers la ligne dans la table (l'adresse du dossier).

Chercher "Morand" :

  1. Racine : M est dans A-M → branche gauche.
  2. Branche : MO → feuille MOR...
  3. Feuille : "Morand" → pointeur vers la ligne 42 817.

Au lieu de lire 100 000 lignes, on lit 3 ou 4 pages d'index.

Types d'Index – Différents Outils pour Différentes Fouilles

1. B-Tree (Arbre-B) – Le Classique

  • Pour les égalités (=), les plages (BETWEEN, >, <), les tris (ORDER BY).
  • Supporte les chaînes, les nombres, les dates.
  • L'index par défaut. Le couteau suisse.

2. Hash – Pour l'Égalité Pure

  • Convertit la valeur en hash code. Super rapide pour WHERE colonne = 'valeur'.
  • Inutile pour les plages, les tris, les LIKE.
  • PostgreSQL, MySQL l'ont. À utiliser avec parcimonie.

3. GiST/SP-GiST (PostgreSQL) – Pour le Territoire

  • Pour les données géographiques, les plages de dates complexes, les recherches plein texte.
  • L'index du terrain. Celui qui connaît les frontières.

4. GIN (PostgreSQL) – Pour les Conteneurs

  • Pour les tableaux, le JSONB, la recherche plein texte.
  • L'index des inventaires. "Ce mot est-il dans ce document ?"

5. BRIN (PostgreSQL) – Pour les Gros Volumes Ordonnés

  • Pour les tables énormes, triées physiquement (ex: logs par date).
  • Stocke les plages min/max par blocs de données.
  • Léger. Rapide pour "Données de la semaine dernière".
  • L'index de l'archive.

🔧 Création et Gestion des Index – Graver la Mémoire

CREATE INDEX – Ouvrir un Nouveau Fichier

-- Index simple sur une colonne
CREATE INDEX idx_client_nom ON client(nom);

-- Index composite (plusieurs colonnes)
CREATE INDEX idx_client_nom_prenom ON client(nom, prenom);
-- L'ordre compte. Il est utilisé de gauche à droite.
-- idx(nom, prenom) aide pour `WHERE nom = 'X'` et `WHERE nom = 'X' AND prenom = 'Y'`
-- Mais pas pour `WHERE prenom = 'Y'` seul.

-- Index unique (implique une contrainte UNIQUE)
CREATE UNIQUE INDEX idx_client_email ON client(email);
-- Empêche les doublons. Plus qu'un index, c'est une loi.

-- Index avec tri spécifique
CREATE INDEX idx_commande_date_desc ON commande(date_commande DESC);
-- Pour les `ORDER BY date_commande DESC` rapides.

-- Index partiel (sur un sous-ensemble)
CREATE INDEX idx_client_actif ON client(id) WHERE actif = true;
-- Seulement pour les clients actifs. Plus léger, plus rapide.
-- Comme un fichier séparé pour les suspects "en activité".

DROP INDEX – Brûler le Fichier

DROP INDEX idx_client_nom;
-- La structure disparaît. La recherche redeviendra lente.
-- Les données restent. Seul le raccourci part en fumée.

Voir les Index – Consulter les Archives

-- PostgreSQL
SELECT tablename, indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'client';

-- MySQL
SHOW INDEX FROM client;

-- Voir ce que l'index contient (le planificateur vous le dira)
EXPLAIN ANALYZE SELECT * FROM client WHERE nom = 'Morand';
-- Là, vous verrez si l'index est utilisé. Ou pas.

⚖️ Avantages et Inconvénients – Le Pacte Faustien

Les Pour : La Vitesse

  1. Recherches rapides (WHERE, JOIN).
  2. Tris accélérés (ORDER BY).
  3. Aggrégations plus vites (GROUP BY sur colonne indexée).
  4. Garantie d'unicité (UNIQUE).
  5. Verrous optimisés (l'index permet de cibler les lignes à verrouiller).

Les Contre : Le Prix à Payer

  1. Espace disque – Un index, c'est une copie partielle des données. Parfois 20-30% de la table.
  2. Surcharge à l'écriture – Chaque INSERT, UPDATE, DELETE doit aussi mettre à jour tous les index sur la table. Ça ralentit.
  3. Maintenance – Les index se fragmentent. Il faut les réorganiser (REINDEX).
  4. Choix du planificateur – Parfois, le SGBD choisit un mauvais index. Il se trompe.

La règle : On ajoute un index quand le gain en lecture vaut le coût en écriture.
Sur une table de logs en écriture seule ? Presque pas d'index.
Sur une table de clients interrogée 1000 fois par seconde ? Plusieurs index.

🎯 Quand Indexer ? – L'Art du Ciblage

Les Colonnes à Indexer (Les Suspects Usuels)

  1. Clés primaires (index automatique).
  2. Clés étrangères (souvent indexées automatiquement, mais pas toujours. À vérifier).
  3. Colonnes des WHERE fréquents.
  4. Colonnes des JOIN.
  5. Colonnes des ORDER BY.
  6. Colonnes des GROUP BY.

Les Signes qu'il Manque un Index (Les Appels à l'Aide)

  • Les requêtes sont lentes.
  • EXPLAIN ANALYZE montre "Seq Scan" (scan séquentiel) sur une grande table.
  • Les JOIN prennent des secondes.
  • Les tris (ORDER BY) sont pénibles.

Les Pièges à Éviter (Les Fausses Pistes)

  1. Trop d'index – La table devient lourde à écrire.
  2. Index sur des colonnes peu sélectives (ex: genre avec 2 valeurs). L'index ne filtre pas assez.
  3. Index sur des colonnes trop souvent modifiées – La maintenance tue les performances.
  4. Index inutilisés – Vérifiez avec pg_stat_user_indexes (PostgreSQL) ou SHOW INDEX_STATISTICS.

🛠️ Bonnes Pratiques – Le Manuel de l'Indexeur

1. Mesurer d'Abord

-- Avant de créer, regardez la sélectivité
SELECT 
    COUNT(DISTINCT ville) as nb_villes_distinctes,
    COUNT(*) as nb_total_lignes,
    (COUNT(DISTINCT ville) * 100.0 / COUNT(*)) as selectivite_percent
FROM client;
-- Si < 5-10%, l'index peut être peu utile.

2. Index Composés : l'Ordre est Roi

-- Mauvais ordre (si on cherche souvent par prénom seul)
CREATE INDEX idx_mauvais ON client(nom, prenom);

-- Meilleur ordre (si on cherche souvent par prénom seul)
CREATE INDEX idx_meilleur ON client(prenom, nom);
-- Mais on ne peut pas tout couvrir. Il faut choisir.

3. Index Partiels pour les Sous-Ensembles

-- Seulement pour les commandes non expédiées (5% des données)
CREATE INDEX idx_commande_en_attente ON commande(date_creation) 
WHERE statut = 'en_attente';
-- Petit, rapide, ciblé.

4. Expressions et Fonctions

-- Index sur une expression (recherche insensible à la casse)
CREATE INDEX idx_client_nom_lower ON client(LOWER(nom));
-- Maintenant, `WHERE LOWER(nom) = 'morand'` utilisera l'index.
-- Mais `WHERE nom = 'Morand'` ne l'utilisera pas.

5. Maintenance Régulière

-- Reconstruction d'un index (recompacte, améliore les performances)
REINDEX INDEX idx_client_nom;

-- PostgreSQL : analyse pour mettre à jour les statistiques
ANALYZE client;

-- MySQL : optimize table
OPTIMIZE TABLE client;

🕵️‍♂️ Cas Concrets – Scènes de Crime Indexées

Cas 1 : La Recherche de Nom

-- Table client : 1 million de lignes
-- Requête fréquente : trouver par nom et prénom

-- Sans index : Seq Scan, 1 million de lignes lues.
-- Avec index :
CREATE INDEX idx_client_nom_prenom ON client(nom, prenom);
-- Recherche devenue instantanée.

Cas 2 : Les Commandes Récentes

-- Table commande : 10 millions de lignes
-- Dashboard : afficher les 100 dernières commandes

-- Sans index : Trie toute la table sur date (long).
-- Avec index :
CREATE INDEX idx_commande_date_desc ON commande(date_commande DESC);
-- Le tri est pré-établi. Instantané.

Cas 3 : Recherche dans du JSON (PostgreSQL)

-- Table produit avec détails en JSONB
CREATE TABLE produit (
    id SERIAL PRIMARY KEY,
    details JSONB
);

-- Recherche fréquente : produits avec "couleur": "noir"
CREATE INDEX idx_produit_couleur ON produit USING GIN (details);
-- Maintenant :
SELECT * FROM produit WHERE details @> '{"couleur": "noir"}';
-- Rapide, même avec 1 million de produits.

Cas 4 : Les Archives de Logs

-- Table log : 1 milliard de lignes, triée par date d'insertion
-- Requête : logs entre deux dates

-- Un B-Tree serait énorme.
-- Un BRIN est parfait :
CREATE INDEX idx_log_date_brin ON log USING BRIN (date_creation);
-- Léger. Rapide pour les plages temporelles.

🧪 Vérification et Dépannage – L'Autopsie des Performances

EXPLAIN et EXPLAIN ANALYZE – Le Rapport d'Autopsie

-- Voir le plan sans exécuter
EXPLAIN SELECT * FROM client WHERE nom = 'Morand';

-- Voir le plan et exécuter (pour les vrais temps)
EXPLAIN ANALYZE SELECT * FROM client WHERE nom = 'Morand';

-- Lecture du résultat :
-- "Index Scan using idx_client_nom on client" -> Bon, l'index est utilisé.
-- "Seq Scan on client" -> Mauvais, pas d'index ou mal choisi.
-- "Bitmap Index Scan" -> Mix entre index et lecture séquentielle.

Les Statistiques d'Utilisation

PostgreSQL :

-- Voir quels index sont utilisés
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes 
ORDER BY idx_scan;
-- idx_scan = nombre de fois où l'index a été utilisé.
-- Si 0, l'index est inutile.

MySQL :

-- Voir l'utilisation des index
SHOW INDEX_STATISTICS;
-- Ou via PERFORMANCE_SCHEMA.

📜 Script de Création d'Index Typique

-- deployment_indexes.sql
-- À exécuter après le déploiement de la structure

BEGIN;

-- Index sur les recherches clients
CREATE INDEX idx_client_nom_prenom ON client(nom, prenom);
CREATE UNIQUE INDEX idx_client_email ON client(email);

-- Index sur les commandes (recherche par client et date)
CREATE INDEX idx_commande_client_date ON commande(client_id, date_commande DESC);

-- Index partiel pour les commandes en attente (dashboard)
CREATE INDEX idx_commande_en_attente ON commande(id) 
WHERE statut IN ('en_attente', 'en_traitement');

-- Index sur les lignes de commande (jointures fréquentes)
CREATE INDEX idx_ligne_commande ON ligne_de_commande(commande_id, produit_id);

-- Index GIN pour la recherche dans les détails JSON (PostgreSQL)
CREATE INDEX idx_produit_details ON produit USING GIN (details);

-- Analyser pour mettre à jour les statistiques
ANALYZE client;
ANALYZE commande;
ANALYZE produit;

COMMIT;

Conclusion – L'Équilibre

Les index, c'est comme les informateurs.
Il en faut assez pour trouver vite. Pas trop pour ne pas payer en silences et en ralentissements.
Un bon index est ciblé, utile, maintenu.

Rappelez-vous :

  1. Indexez ce qui est cherché, pas ce qui est stocké.
  2. Mesurez avant de créer.
  3. Surveillez l'utilisation.
  4. Maintenez (REINDEX, ANALYZE).

La ville a sa mémoire.
Les index sont les chemins qu'on grave dans cette mémoire pour retrouver plus vite les cadavres dans les placards.

Prochaine étape : les vues et la sécurité.
Qui a le droit de voir quoi ?
Parce que dans cette ville, toutes les fenêtres ne doivent pas être éclairées.