database-query-optimizer

Database Query Optimizer

Étape 1 — Collecte d'informations

Demande systématiquement (ne suppose rien) :

• SGBD et version : PostgreSQL 16, MySQL 8, SQL Server 2022, MongoDB 7, SQLite…
• Requête complète (pas un résumé, le SQL brut)
• Schéma : colonnes, types, index existants (\d table PG, SHOW CREATE TABLE MySQL)
• Volume : lignes dans les tables concernées (ordre de grandeur suffit)
• Temps mesuré : durée actuelle, cible souhaitée, outil de mesure
• Contexte d'exécution : fréquence, OLTP vs OLAP, connexions concurrentes

---

Étape 2 — Diagnostic avec EXPLAIN

PostgreSQL / MySQL / SQLite

-- PostgreSQL : plan + exécution réelle
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <requête>;

-- MySQL
EXPLAIN FORMAT=JSON <requête>;
SHOW STATUS LIKE 'Last_Query_Cost';

-- SQL Server
SET STATISTICS IO, TIME ON;
<requête>;
-- ou via SSMS : Query > Include Actual Execution Plan

Signaux d'alarme dans le plan

Signal	Signification	Priorité
Seq Scan sur grande table	Pas d'index utilisable	Critique
Hash Join + rows estimés très faux	Statistiques obsolètes	Élevée
Nested Loop × millions de rows	Potentiel N+1 ou jointure cartésienne	Critique
Sort sans Index Scan	Manque d'index sur ORDER BY	Modérée
cost=... très élevé vs actual rows faibles	Mauvaise estimation selectivité	Élevée
rows=1 partout mais slow	Problème réseau / lock / cache miss	Variable

Mettre à jour les statistiques

-- PostgreSQL
ANALYZE table_name;
-- MySQL
ANALYZE TABLE table_name;
-- SQL Server
UPDATE STATISTICS table_name;

---

Étape 3 — Optimisations concrètes

3.1 Index manquants

-- Créer un index couvrant (covering index) : évite un table lookup
CREATE INDEX CONCURRENTLY idx_orders_user_status
  ON orders (user_id, status)
  INCLUDE (created_at, total_amount);  -- PostgreSQL 11+

-- Index partiel : si requête filtre toujours sur une valeur
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- Index composite : ordre des colonnes = sélectivité décroissante
-- Bon : (user_id, status)   -- user_id très sélectif
-- Mauvais : (status, user_id) -- status peu sélectif en tête