Index SQL : ce que tout développeur full-stack devrait savoir

Avant de devenir développeur full-stack, j'ai commencé par l'informatique décisionnelle : procédures stockées, rapports, bases de données volumineuses. Cette expérience m'a appris une chose que beaucoup de développeurs front découvrent tard : la plupart des problèmes de performance d'une application se règlent dans la base de données, et souvent avec un simple index.

Avec les ORM comme Prisma, on écrit rarement du SQL à la main. Mais l'ORM ne crée pas les index à votre place. Voici ce qu'il faut savoir.

Ce qu'est un index

Sans index, pour trouver les commandes d'un client, la base lit toutes les lignes de la table : c'est un parcours séquentiel (seq scan). Sur 1 000 lignes, c'est instantané ; sur 10 millions, c'est plusieurs secondes.

Un index est une structure annexe, le plus souvent un arbre B (B-tree), qui garde les valeurs d'une colonne triées avec un pointeur vers chaque ligne. Comme l'index d'un livre, il permet d'aller directement à la bonne page.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Avec Prisma, on le déclare dans le schéma :

model Order {
  id         String   @id @default(cuid())
  customerId String
  status     String
  createdAt  DateTime @default(now())

  @@index([customerId, createdAt])
}

Les index composites et la règle du préfixe

Un index sur plusieurs colonnes (customer_id, created_at) est trié d'abord par client, puis par date. Il sert donc :

• aux requêtes filtrant sur customer_id ;
• aux requêtes filtrant sur customer_id et triant ou filtrant sur created_at ;
• mais pas aux requêtes filtrant uniquement sur created_at, comme on ne peut pas utiliser un annuaire trié par nom pour chercher par prénom.

Règle pratique : placez d'abord les colonnes filtrées par égalité, puis celles utilisées pour les plages ou le tri.

Les requêtes qui empêchent l'utilisation d'un index

• Une fonction appliquée à la colonne : WHERE LOWER(email) = 'a@b.com' ne peut pas utiliser un index sur email. Solution : un index sur l'expression (CREATE INDEX ... ON users (LOWER(email))) ou stocker la valeur normalisée.
• Un `LIKE` qui commence par un joker : LIKE 'dupont%' utilise l'index, LIKE '%dupont' non. Pour la recherche plein texte, utilisez les outils dédiés (full-text search, trigrammes).
• Des types incompatibles : comparer une colonne texte à un nombre force une conversion ligne par ligne.
• Un `OR` sur des colonnes différentes : il empêche souvent l'utilisation d'un index unique ; un UNION ou deux index séparés peuvent aider.

Lire un plan d'exécution

Ne devinez pas : demandez à la base ce qu'elle fait. En PostgreSQL, EXPLAIN ANALYZE exécute la requête et affiche le plan réel ; SQL Server propose l'affichage du plan d'exécution réel dans son outil de gestion.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 'c_123'
ORDER BY created_at DESC
LIMIT 20;

-- Avant : Seq Scan on orders  (actual time=0.02..812.4 rows=20)
-- Après : Index Scan using idx_orders_customer_created on orders
--         (actual time=0.03..0.09 rows=20)

Les signaux à repérer : Seq Scan sur une grosse table, un grand écart entre le nombre de lignes estimé et réel, et une étape de tri (Sort) coûteuse qu'un index bien ordonné aurait évitée.

Les index couvrants

Si l'index contient toutes les colonnes dont la requête a besoin, la base n'a même plus à lire la table : c'est un index couvrant. PostgreSQL (depuis la version 11) et SQL Server permettent d'y ajouter des colonnes non triées avec INCLUDE.

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC)
  INCLUDE (status, total);

Le coût des index

Un index n'est pas gratuit : il occupe de l'espace et ralentit chaque écriture (insertion, mise à jour, suppression), puisqu'il doit être maintenu. Indexez les colonnes réellement utilisées dans les WHERE, JOIN et ORDER BY des requêtes fréquentes, et supprimez les index que les statistiques montrent inutilisés.

Conclusion

Un développeur full-stack n'a pas besoin d'être administrateur de bases de données, mais il doit savoir qu'un index existe, comment un index composite s'utilise, quelles écritures de requêtes le rendent inutile et comment lire un EXPLAIN. Ces quelques notions suffisent à transformer des pages de plusieurs secondes en pages instantanées.