Optimiser la recherche des emprunts en retard

Une recherche des emprunts en retard peut parcourir une grande partie de l’historique à chaque exécution. Ajouter un index au hasard ne garantit ni un plan plus efficace ni un résultat plus rapide. Vous allez comparer une requête de référence et une variante indexée sur les données du projet, puis vérifier que les deux retournent le même ensemble de lignes.

Le schéma et le jeu de données sont déjà en place dans sql/schema.sql et sql/donnees.sql. Dans ce projet, les retards sont enregistrés dans retards et rattachés à un emprunt par emprunt_id ; la recherche ci-dessous retourne l’identifiant de l’emprunt et sa date de retour prévue. Elle cible les retards dont l’emprunt n’a pas encore été rendu, et garde les colonnes utiles plutôt que de charger toutes les données.

-- sql/requetes.sql
-- Référence : retards dont l'emprunt est encore en cours.
SELECT r.emprunt_id,
       r.date_retard
FROM retards AS r
INNER JOIN emprunts AS e
    ON e.emprunt_id = r.emprunt_id
WHERE e.date_retour IS NULL
ORDER BY r.emprunt_id;

Avant de modifier les index, ajoutez cette requête à sql/requetes.sql et examinez son plan dans sql/diagnostics.sql. Le plan vous indique notamment quelles tables sont lues, quel accès est choisi et combien de lignes MariaDB estime devoir examiner. Une estimation élevée sur emprunts peut être cohérente si beaucoup de prêts ont été rendus et que seuls quelques-uns sont encore actifs ; l’estimation ne remplace pas la mesure sur votre base.

-- sql/diagnostics.sql
-- Plan initial pour la recherche des retards actifs.
EXPLAIN
SELECT r.emprunt_id,
       r.date_retard
FROM retards AS r
INNER JOIN emprunts AS e
    ON e.emprunt_id = r.emprunt_id
WHERE e.date_retour IS NULL
ORDER BY r.emprunt_id;

-- Exécution instrumentée dans MariaDB 11.
ANALYZE
SELECT r.emprunt_id,
       r.date_retard
FROM retards AS r
INNER JOIN emprunts AS e
    ON e.emprunt_id = r.emprunt_id
WHERE e.date_retour IS NULL
ORDER BY r.emprunt_id;

L’index primaire de emprunts permet de retrouver un emprunt à partir de son identifiant, et la clé étrangère de retards est indexée selon le schéma du projet. Le filtre qui peut rester coûteux est e.date_retour IS NULL : si le plan montre un parcours important des emprunts, un index sur cette colonne peut aider MariaDB à repérer les lignes non rendues. L’intérêt dépend toutefois de la proportion de valeurs NULL, du nombre de retards et du plan réellement choisi ; contrôlez d’abord les index existants avec SHOW INDEX FROM emprunts.

-- sql/index.sql
-- À ajouter seulement si l'index n'existe pas déjà.
-- Il cible le prédicat qui sélectionne les emprunts non rendus.
CREATE INDEX idx_emprunts_date_retour
    ON emprunts (date_retour);

MariaDB ne fournit pas ici un index partiel limité aux emprunts dont date_retour est NULL : l’index simple contient les valeurs de la colonne et doit être entretenu lors des écritures. Après sa création, relancez le même EXPLAIN, puis ANALYZE, et comparez les résultats avec la référence. Si le plan ne choisit pas cet index, cela ne prouve pas une erreur : pour une table peu volumineuse ou un prédicat peu sélectif, un parcours peut être moins coûteux que des accès par index suivis de lectures de lignes.

L’index ne doit pas modifier le résultat SQL ; il doit seulement changer le coût du travail nécessaire pour l’obtenir. La vérification la plus simple consiste à exécuter la même requête avant et après la création de l’index et à comparer les lignes. Pour documenter cette vérification, utilisez une table temporaire contenant le résultat de référence, puis vérifiez dans les deux sens qu’aucune ligne ne manque et qu’aucune ligne supplémentaire n’apparaît.

-- À exécuter dans une session de test, après chargement des données.
-- Mémoriser les lignes de la requête de référence.
CREATE TEMPORARY TABLE resultat_reference AS
SELECT r.emprunt_id,
       r.date_retard
FROM retards AS r
INNER JOIN emprunts AS e
    ON e.emprunt_id = r.emprunt_id
WHERE e.date_retour IS NULL;

-- La première requête doit retourner 0 : aucune ligne de référence absente.
SELECT COUNT(*) AS lignes_absentes
FROM resultat_reference AS ref
LEFT JOIN (
    SELECT r.emprunt_id,
           r.date_retard
    FROM retards AS r
    INNER JOIN emprunts AS e
        ON e.emprunt_id = r.emprunt_id
    WHERE e.date_retour IS NULL
) AS actuel
    ON actuel.emprunt_id = ref.emprunt_id
   AND actuel.date_retard = ref.date_retard
WHERE actuel.emprunt_id IS NULL;

-- La seconde requête doit également retourner 0 : aucune ligne ajoutée.
SELECT COUNT(*) AS lignes_supplementaires
FROM (
    SELECT r.emprunt_id,
           r.date_retard
    FROM retards AS r
    INNER JOIN emprunts AS e
        ON e.emprunt_id = r.emprunt_id
    WHERE e.date_retour IS NULL
) AS actuel
LEFT JOIN resultat_reference AS ref
    ON ref.emprunt_id = actuel.emprunt_id
   AND ref.date_retard = actuel.date_retard
WHERE ref.emprunt_id IS NULL;

DROP TEMPORARY TABLE resultat_reference;

Les deux requêtes de contrôle doivent afficher 0 pour les données inchangées entre la création de la référence et la comparaison.

Cette comparaison suppose que les tables n’ont pas été modifiées entre la sauvegarde et les contrôles. Si le jeu contient des doublons dans les colonnes comparées, une comparaison par jointure peut ne pas déceler une différence de multiplicité ; vérifiez alors aussi les contraintes et l’unicité attendue du modèle, définies dans les leçons précédentes. Consignez dans sql/diagnostics.sql le plan avant et après, les lignes estimées et observées, ainsi que les durées relevées, sans conclure à partir d’une seule exécution.

• Commencez par une requête réelle et un plan mesuré, avant de proposer un index.
• Ciblez l’index sur un prédicat coûteux observé ; vérifiez d’abord les index déjà présents.
• Comparez plans et exécutions dans des conditions proches, puis tenez compte du coût des écritures.
• Contrôlez que les résultats sont identiques dans les deux sens avant de retenir la réécriture ou l’index.

Dans la leçon suivante, vous appliquerez les transactions et les verrous à une attribution concurrente d’exemplaire, puis vérifierez l’état obtenu après validation ou annulation.