Comparer des plans et valider l’équivalence

Une requête peut sembler mieux optimisée sur le papier tout en renvoyant un résultat différent. Elle peut aussi utiliser un index sans être plus rapide sur vos données. Vous allez comparer deux formulations de la recherche des adhérents ayant au moins un emprunt en retard, puis contrôler à la fois leur résultat et leur exécution réelle.

Les deux requêtes ci-dessous utilisent les colonnes emprunts.adherent_id, emprunts.date_retour et emprunts.date_retour_prevue, ainsi que adherents.adherent_id. La première joint les tables et élimine les doublons ; la seconde teste directement l’existence d’un emprunt correspondant. Elles doivent toutes deux produire une ligne par adhérent, même si plusieurs de ses emprunts sont en retard.

Comparer les plans et l’exécution

Dans sql/diagnostics.sql, commencez par exécuter EXPLAIN sur chaque requête pour examiner les estimations et le plan choisi. Puis utilisez ANALYZE SELECT : contrairement à une simple estimation, cette commande exécute le SELECT et fournit des informations observées, notamment les lignes réellement parcourues selon les étapes du plan. Dans MariaDB 11, utilisez cette syntaxe plutôt que de supposer que la forme EXPLAIN ANALYZE d’un autre système est disponible.

-- Référence : la jointure peut produire plusieurs lignes par adhérent.
SELECT DISTINCT a.adherent_id
FROM adherents AS a
JOIN emprunts AS e
    ON e.adherent_id = a.adherent_id
WHERE e.date_retour IS NULL
  AND e.date_retour_prevue < CURRENT_DATE;

-- Variante : ne conserver que les adhérents ayant un emprunt correspondant.
SELECT a.adherent_id
FROM adherents AS a
WHERE EXISTS (
    SELECT 1
    FROM emprunts AS e
    WHERE e.adherent_id = a.adherent_id
      AND e.date_retour IS NULL
      AND e.date_retour_prevue < CURRENT_DATE
);

Dans sql/diagnostics.sql, copiez chacune de ces requêtes après EXPLAIN, puis après ANALYZE. Notez pour chaque version le type d’accès, l’index choisi, les lignes estimées et les lignes observées. Un écart important entre estimation et observation peut expliquer un choix de plan inadapté ; il ne prouve pas, à lui seul, que la réécriture est meilleure.

-- Plan estimé de la requête de référence.
EXPLAIN
SELECT DISTINCT a.adherent_id
FROM adherents AS a
JOIN emprunts AS e
    ON e.adherent_id = a.adherent_id
WHERE e.date_retour IS NULL
  AND e.date_retour_prevue < CURRENT_DATE;

-- Exécution observée de la requête de référence.
ANALYZE
SELECT DISTINCT a.adherent_id
FROM adherents AS a
JOIN emprunts AS e
    ON e.adherent_id = a.adherent_id
WHERE e.date_retour IS NULL
  AND e.date_retour_prevue < CURRENT_DATE;

-- Plan et exécution observée de la variante EXISTS.
EXPLAIN
SELECT a.adherent_id
FROM adherents AS a
WHERE EXISTS (
    SELECT 1
    FROM emprunts AS e
    WHERE e.adherent_id = a.adherent_id
      AND e.date_retour IS NULL
      AND e.date_retour_prevue < CURRENT_DATE
);

ANALYZE
SELECT a.adherent_id
FROM adherents AS a
WHERE EXISTS (
    SELECT 1
    FROM emprunts AS e
    WHERE e.adherent_id = a.adherent_id
      AND e.date_retour IS NULL
      AND e.date_retour_prevue < CURRENT_DATE
);

Démontrer l’équivalence des résultats

Ne vous contentez pas de constater que les deux requêtes semblent afficher les mêmes premières lignes. Contrôlez les écarts dans les deux sens sur le jeu de test : un identifiant présent dans la référence mais absent de la variante, ou l’inverse, doit être signalé. La comparaison porte ici sur l’ensemble des adhérents concernés ; la référence utilise DISTINCT pour respecter la même granularité que le résultat de EXISTS.

-- Chaque branche recherche les identifiants absents de l’autre résultat.
WITH reference AS (
    SELECT DISTINCT a.adherent_id
    FROM adherents AS a
    JOIN emprunts AS e
        ON e.adherent_id = a.adherent_id
    WHERE e.date_retour IS NULL
      AND e.date_retour_prevue < CURRENT_DATE
), optimisee AS (
    SELECT a.adherent_id
    FROM adherents AS a
    WHERE EXISTS (
        SELECT 1
        FROM emprunts AS e
        WHERE e.adherent_id = a.adherent_id
          AND e.date_retour IS NULL
          AND e.date_retour_prevue < CURRENT_DATE
    )
)
SELECT 'absent_de_optimisee' AS ecart, r.adherent_id
FROM reference AS r
WHERE NOT EXISTS (
    SELECT 1
    FROM optimisee AS o
    WHERE o.adherent_id = r.adherent_id
)
UNION ALL
SELECT 'absent_de_reference' AS ecart, o.adherent_id
FROM optimisee AS o
WHERE NOT EXISTS (
    SELECT 1
    FROM reference AS r
    WHERE r.adherent_id = o.adherent_id
);

Aucune ligne si les deux requêtes produisent le même ensemble d’identifiants.

Une comparaison utile sépare donc deux questions : les requêtes sont-elles équivalentes, et laquelle est la plus efficace sur votre environnement ? La première se vérifie sur les résultats, la seconde par les plans et des mesures répétées. Si le contrôle renvoie une ligne, examinez les conditions et les doublons avant de retenir la variante ; si les résultats concordent, vous pouvez comparer les observations sans altérer la sémantique.

À retenir

• Dans MariaDB 11, utilisez ANALYZE SELECT pour observer l’exécution réelle d’un SELECT.
• Comparez les estimations d’EXPLAIN aux lignes observées, sans déduire la performance du seul choix d’index.
• Vérifiez l’équivalence dans les deux sens sur un jeu de test et respectez la granularité du résultat.
• Consignez les conditions et les observations réelles avant de conclure qu’une réécriture est préférable.