Qu'est-ce qu'un index en SQL ? Quand devriez-vous et ne devriez-vous pas en utiliser ?

Question

Accepted Answer

Un **index** est une structure de données (généralement un **B-tree**) qui permet à la base de données de trouver rapidement des lignes **sans scanner la table entière** — comme l'index d'un livre. Il accélère dramatiquement les recherches, les filtres, les jointures et les tris sur la ou les colonnes indexées, au prix d'un stockage supplémentaire et d'écritures plus lentes.

## Comment un index aide

```sql
-- without an index on email, this scans EVERY row (slow on a big table) — O(n)
SELECT * FROM users WHERE email = 'ann@x.com';

-- create an index → the database can find the row directly — O(log n)
CREATE INDEX idx_users_email ON users(email);
-- now the same query uses the index → dramatically faster
```

Sans index, la base de données effectue un **full table scan** (vérification de chaque ligne). Avec un index, elle navigue dans un B-tree pour trouver les lignes correspondantes en temps logarithmique — la différence entre scanner des millions de lignes et faire une poignée de recherches.

## Quand utiliser les indexes

```text
✓ Columns in WHERE clauses (frequently filtered)
✓ Columns used in JOINs (foreign keys especially)
✓ Columns in ORDER BY (sorting can use the index)
✓ Columns with high SELECTIVITY (many distinct values — email, id)
✓ Primary keys (auto-indexed) and unique constraints
```

## Quand NE PAS utiliser les indexes (les compromis)

```text
✗ Columns rarely queried — wasted storage and write overhead
✗ Small tables — a full scan is already fast; an index adds no benefit
✗ Low-selectivity columns (e.g. a boolean with 2 values) — index barely helps
✗ Write-heavy columns — EVERY INSERT/UPDATE/DELETE must also update the indexes
  → too many indexes SLOW DOWN writes significantly
```

Les indexes ne sont pas gratuits : ils **consomment du stockage** et **ralentissent les écritures** (chaque modification doit mettre à jour les indexes). Un sur-indexage nuit aux performances d'écriture.

## Indexes composites et autres types d'index

```sql
CREATE INDEX idx_users_country_name ON users(country, name);   -- COMPOSITE (multi-column)
-- helps WHERE country = ? AND name = ?, and WHERE country = ? (leftmost prefix)
-- but NOT WHERE name = ? alone (order matters!)

CREATE UNIQUE INDEX ...      -- enforces uniqueness
-- also: partial indexes, covering indexes, GIN/GiST (Postgres) for special data
```

## Pourquoi c'est important

Les indexes sont l'un des sujets les plus importants en matière de performance des bases de données — ils sont l'outil principal pour accélérer les requêtes, et comprendre quand et comment les utiliser est une connaissance essentielle pour quiconque travaille avec des bases de données à grande échelle.

La valeur fondamentale est dramatique : sans indexes, les requêtes qui filtrent, joignent ou trient sur une colonne nécessitent des **full table scans** (vérification de chaque ligne — catastrophiquement lent au fur et à mesure que les données augmentent), tandis que les indexes les transforment en recherches logarithmiques rapides — souvent la différence entre une requête prenant des millisecondes versus des secondes ou des minutes.

Comprendre **quand indexer** (colonnes WHERE fréquemment filtrées, colonnes de JOIN/clé étrangère, colonnes ORDER BY, colonnes à haute sélectivité) est fondamental pour la performance des requêtes.

Également important est comprendre les **compromis et quand NE PAS indexer** : les indexes consomment du stockage et, crucialmente, **ralentissent les écritures** (chaque INSERT/UPDATE/DELETE doit mettre à jour tous les indexes), donc un sur-indexage nuit aux performances d'écriture, et les indexes ajoutent peu de valeur sur les petites tables ou les colonnes à faible sélectivité.

Cet équilibre — indexer ce qui est interrogé sans sur-indexer — est une compétence clé.

Connaître les **indexes composites** (multi-colonnes, où l'ordre des colonnes importe pour les requêtes qu'elles aident) complète la connaissance pratique des indexes.

Étant donné que la performance des requêtes est critique pour les applications réelles, et que l'indexation appropriée (les bons indexes sur les bonnes colonnes, équilibrant la vitesse de lecture par rapport au coût d'écriture et au stockage) est l'optimisation de base de données la plus impactante, maîtriser les indexes — comment ils fonctionnent, quand les utiliser, les compromis d'écriture/stockage et les indexes composites — est une connaissance fondamentale, de grande valeur, pour des bases de données performantes et l'un des sujets les plus importants et les plus fréquemment testés concernant les bases de données.