Query Optimizer (Database)

Le Query Optimizer est le composant SGBD analysant les SQL queries et choisissant le meilleur plan d'exécution (execution plan) parmi plusieurs alternatives possibles. Décide ordre des joins, quels indexes utiliser, méthodes d'accès (sequential scan vs index scan), algorithmes (nested loop join vs hash join vs merge join), parallélisation. C'est la magie qui rend SQL declaratif efficient.

Deux approches principales :
(1) **Rule-based optimizer (RBO)** — heuristiques fixes (toujours utiliser index si disponible, etc.). Legacy, moins flexible. Encore utilisé MySQL pour simple queries.
(2) **Cost-based optimizer (CBO)** — utilise statistiques sur tables (rows count, value distributions, NULL counts, correlations) pour estimer coût de chaque plan possible et choisir le moins cher. Postgres, Oracle, SQL Server, MySQL InnoDB modern utilisent CBO.

Facteurs considérés :
(1) **Selectivity** — quelle fraction de la table rows match WHERE clause ?
(2) **Cardinality** — combien de rows distinct dans chaque colonne ?
(3) **Indexes available** — quelle index est sélective enough ?
(4) **Table size** vs **memory** (small table = full scan rapide).
(5) **Join order** — algorithm dépend des sizes (small driving large).
(6) **Hardware estimates** (cost per disk page, per CPU op).

Statistiques critiques : maintenues par `ANALYZE` (Postgres) ou `ANALYZE TABLE` (MySQL). Stale stats = bad plans. Auto-vacuum/auto-analyze gère typically.

Commun problème : optimizer choisit suboptimal plan. Causes : (1) stats outdated ; (2) correlated columns (optimizer assume independence) ; (3) complex queries with many joins (search space huge, optimizer takes shortcuts) ; (4) function calls in WHERE (optimizer can't use indexes) ; (5) data type mismatches.

Debugging plans : (1) **EXPLAIN** (estimate) et **EXPLAIN ANALYZE** (actual execution) ; (2) **Visual tools** — pgAdmin, MySQL Workbench, DBeaver, EverSQL ; (3) **pgMustard, explain.dalibo.com** pour Postgres ; (4) **Query Store** SQL Server, **Performance Insights** RDS.

Query hints : last resort to override optimizer — `/*+ INDEX(t idx_name) */` (Oracle, MySQL), `pg_hint_plan` (Postgres extension). Use sparingly — optimizer usually better than humans. Compétences DP-300, DEA-C01.