Comment optimisez-vous les requêtes SQL lentes ?

Question

Accepted Answer

L'optimisation des requêtes lentes commence par **analyser le plan d'exécution** (`EXPLAIN`) pour trouver le goulot d'étranglement, puis appliquer des correctifs ciblés — le plus souvent **ajouter des index**, réécrire la requête et éviter les anti-patterns courants. Le plus gros gain est généralement de transformer les analyses complètes de table en recherches par index.

## Étape 1 : analyser avec EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (ou `EXPLAIN ANALYZE`) montre comment la base de données exécute la requête — essentiellement, si elle effectue une **analyse complète de table** (lent) ou utilise un **index** (rapide). Profilez toujours avant d'optimiser.

## Le plus gros gain : ajouter des index

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

Le correctif le plus courant et percutant : **ajouter des index** sur les colonnes utilisées dans `WHERE`, `JOIN` et `ORDER BY`. Cela transforme les analyses complètes de table en recherches rapides.

## Anti-patterns courants à corriger

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Autres techniques d'optimisation

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Pourquoi c'est important

L'optimisation des requêtes est une connaissance critique pour la performance des bases de données — les requêtes lentes sont une cause majeure de mauvaise performance des applications, et savoir comment les identifier et les corriger est une compétence essentielle au niveau senior pour quiconque travaille avec des bases de données à grande échelle.

La discipline fondamentale est **l'analyse avec `EXPLAIN`** (examiner le plan d'exécution pour trouver le véritable goulot d'étranglement — essentiellement si la requête effectue une lente analyse complète de table ou une rapide recherche par index) avant d'optimiser, plutôt que de deviner — cette approche basée sur la mesure est ce qui rend l'optimisation efficace.

Le **gain unique le plus important et le plus courant est d'ajouter des index** sur les colonnes filtrées, jointes et triées (transformant les analyses complètes de table en recherches rapides — améliorant souvent le temps de requête de plusieurs ordres de grandeur), ce qui rend la connaissance des index centrale à l'optimisation.

Comprendre les **anti-patterns courants** est tout aussi important : les fonctions sur les colonnes et les `LIKE` avec caractères génériques en début empêchent l'utilisation d'index, `SELECT *` récupère des données inutiles, et notamment les **requêtes N+1** (requêtes en boucle au lieu d'un seul JOIN — un tueur de performance fréquent et grave dans le code applicatif) — reconnaître et corriger ceux-ci résout les causes les plus courantes de requêtes lentes.

D'autres techniques (index composites, réduction des données analysées, réécriture des sous-requêtes en JOIN, mise à jour des statistiques, vues matérialisées pour les requêtes coûteuses) complètent la boîte à outils.

Puisque la performance des requêtes affecte directement la réactivité et la scalabilité des applications, et puisque l'approche systématique (analyser avec EXPLAIN, puis corriger — généralement en ajoutant des index et en éliminant les anti-patterns comme N+1) est ce qui maintient les bases de données rapides sous charge, maîtriser l'optimisation des requêtes est une connaissance importante et de haut niveau qui distingue les développeurs capables de construire des applications orientées base de données performantes et scalables — une compétence fréquemment pertinente, critique et un sujet d'entrevue courant pour la performance des bases de données.