Réponse.

Historique de la question :
Dès le départ, les requêtes SQL ont été conçues comme déclaratives : « ce que l'on veut obtenir », et non « comment obtenir ». L'optimiseur SGBD construit un plan d'exécution (execution plan), qui détermine l'ordre des jointures, des filtrages, des scans et de l'utilisation des indices.

Problème :
Sans comprendre le plan d'exécution, il est impossible d'expliquer pourquoi une requête apparemment simple s'exécute trop lentement, tandis qu'une complexe fonctionne rapidement. Un plan erroné peut bloquer le serveur pendant des heures en raison d'opérations inutiles ou d'une mauvaise utilisation des indices.

Solution :
Les outils d'analyse sont des instructions telles que EXPLAIN, EXPLAIN ANALYZE (PostgreSQL), SHOW EXPLAIN (MySQL), Management Studio Execution Plan (SQL Server). Grâce à eux, on peut voir étape par étape comment la requête s'est exécutée, quel volume de données a été lu, quel indice a été utilisé, où des scans complets de table ont eu lieu et où des retards sont survenus.

Exemple de code:

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'shipped';

Caractéristiques clés :

Le plan donnera un schéma détaillé des opérations exécutées par le SGBD et dans quel ordre.
On peut voir où un index scan a été utilisé, et où un table scan a eu lieu.
Permet d'identifier les goulets d'étranglement pour l'optimisation et de vérifier l'hypothèse sur l'impact de la réécriture de la requête sur les performances.

Questions pièges.

Si l'on ajoute un index, la requête sera-t-elle toujours accélérée ?

Non ! Un index ne sera utile que si le filtrage sur son champ réduit considérablement le nombre de lignes retournées. Si la plupart des enregistrements correspondent à la condition, l'optimiseur peut ignorer l'index.

Exemple :

-- Le champ 'gender' n'accepte que deux valeurs — l'index ne sera pas utile
CREATE INDEX idx_gender ON people(gender);
SELECT * FROM people WHERE gender = 'M';

L'ordre des tables dans un JOIN affecte-t-il le résultat d'exécution ?

Non, les données finales seront identiques, cependant, l'optimiseur peut changer l'ordre d'exécution des jointures pour améliorer les performances. Mais si un JOIN spécifique est écrit ou si des indices comme "JOIN HINT" sont utilisés, l'ordre peut affecter l'efficacité de l'exécution.

Pourquoi analyser "Estimated rows" et "Actual rows" dans le plan d'exécution ?

La différence entre les deux peut indiquer que les statistiques des tables sont obsolètes ou qu'elles ne reflètent pas la réalité, et que le plan choisi n'est pas optimal — il est donc nécessaire de mettre à jour les statistiques ou de revoir la structure de la requête.

-- PostgreSQL
ANALYZE table_name; -- mettre à jour les statistiques

Erreurs typiques et anti-modèles

Le plan d'exécution n'est pas examiné lorsqu'il y a des problèmes : on tente d'optimiser "à l'aveugle"
Ajout d'indices superflus/non ciblés pour "accélérer", ce qui ralentit efficacement les modifications de données
Les statistiques des tables ne sont pas mises à jour
Les requêtes sont conçues sans prendre en compte comment elles seront exécutées par l'optimiseur

Exemple de la vie réelle

Cas négatif

Sur le projet, les analystes se plaignaient longtemps d'un "gel" des rapports. Une requête avec cinq JOIN durait 25 minutes. Il s'est avéré qu'un plan avec un scan complet d'une énorme table avait été choisi au lieu d'un index scan, les indices n'étaient pas sur les bons champs, et les statistiques n'étaient pas mises à jour depuis un an.

Avantages :

Maintenance simple, minimisation des indices

Inconvénients :

La requête était lente, des heures de travail humain étaient perdues
Mécontentement des utilisateurs
Charge sur le serveur

Cas positif

Nous avons analysé le plan d'exécution, ajouté un index sur un champ réellement filtrant, mis à jour les statistiques. Le temps de la requête a été réduit à 20 secondes. La charge sur le serveur a diminué d'un ordre de grandeur.