Index couvrants et parcours par index seul
Inclure des colonnes afin qu’une requête n’accède jamais au tas de la table
Index couvrants et parcours par index seul est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.
Rappel : la lecture du tas
Vous avez appris précédemment qu’un arbre B classique ne stocke que les colonnes indexées et un pointeur de ligne ; après que l’index a trouvé les correspondances, le moteur doit encore accéder à la table pour lire les autres colonnes. Cet accès est la lecture du tas, et c’est le coût qu’un index couvrant est conçu pour éliminer.
Les recruteurs posent des questions sur les index couvrants pour vérifier que vous comprenez pourquoi un index peut répondre entièrement à une requête sans accéder à la table.
Ce que signifie « couvrant »
Un index couvre une requête lorsque chaque colonne dont celle-ci a besoin, dans SELECT, WHERE, ORDER BY et GROUP BY, est présente dans l’index lui-même.
Lorsque c’est le cas, le moteur lit uniquement l’index et ne consulte jamais la table. PostgreSQL appelle cela un parcours d’index uniquement ; SQL Server et d’autres systèmes parlent d’index couvrant. Le bénéfice est une réduction du nombre de pages lues et une accélération des requêtes.
Exemple détaillé : une requête couverte
Supposons qu’une requête n’ait besoin que de customer_id et de order_date. Un index composite portant exactement sur ces colonnes contient tout ce que la requête demande ; celle-ci peut donc obtenir sa réponse à partir de l’index seul.
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);
-- Covered: both selected columns are in the index
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 42;Une colonne supplémentaire empêche la couverture
Ajoutez une colonne absente de l’index et la couverture disparaît : le moteur doit lire le tas pour l’obtenir.
Ici, total ne figure pas dans l’index. Ainsi, même si customer_id guide la recherche, chaque ligne correspondante déclenche une lecture du tas pour lire total.
-- NOT covered: total is not in the index, forces heap fetches
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;La clause INCLUDE
Vous pourriez ajouter total comme quatrième colonne clé, mais si vous n’effectuez jamais de filtrage ni de tri sur cette colonne, vous gaspillez de l’espace dans l’ordre de tri de l’arbre. L’outil plus propre est INCLUDE (pris en charge par PostgreSQL et SQL Server) : il stocke les colonnes supplémentaires uniquement dans les feuilles de l’index, comme données utiles, et non comme éléments de la clé de tri.
La requête est ainsi couverte sans gonfler la partie interrogeable de l’index.
CREATE INDEX idx_orders_cust_date_inc
ON orders (customer_id, order_date)
INCLUDE (total);
-- Now covered: total is carried in the leaf
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;Colonnes clés et colonnes incluses
Voici une distinction précise qui impressionne les recruteurs :
- Les colonnes clés définissent l’ordre de tri et peuvent servir aux recherches et aux parcours par intervalle. Elles respectent la règle du préfixe le plus à gauche.
- Les colonnes incluses sont stockées uniquement dans les feuilles comme données supplémentaires ; elles ne peuvent pas être recherchées, mais elles permettent à l’index de couvrir davantage de requêtes.
Règle générale : les colonnes sur lesquelles vous filtrez ou triez vont dans la clé ; celles que vous faites seulement retourner vont dans INCLUDE.
MySQL/InnoDB : la particularité de l’index clusterisé
Montrez que vous connaissez les différences entre les systèmes. Les tables InnoDB (MySQL) sont clusterisées selon la clé primaire : les index secondaires contiennent implicitement les colonnes de la clé primaire. Ainsi, un index secondaire couvre automatiquement toute requête qui sélectionne uniquement les colonnes indexées et les colonnes de la clé primaire ; aucune clause INCLUDE n’est nécessaire (MySQL ne possède pas INCLUDE).
Le concept d’index couvrant est universel ; la syntaxe et les colonnes ainsi disponibles diffèrent selon le moteur.
Vérifier un parcours d’index uniquement
Prouvez la couverture avec EXPLAIN. Dans PostgreSQL, le nœud du plan indique parcours d’index uniquement au lieu de Index Scan. Recherchez Heap Fetches: 0 dans EXPLAIN (ANALYZE) : c’est le signe définitif qu’aucun accès à la table n’a eu lieu.
Si vous vous attendiez à un parcours d’index uniquement, mais que vous voyez Index Scan avec des lectures du tas, c’est qu’une colonne sélectionnée manque dans l’index.
EXPLAIN (ANALYZE)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;
-- Look for: Index Only Scan ... Heap Fetches: 0La réserve de la carte de visibilité de PostgreSQL
Un point subtil concernant PostgreSQL qui peut vous rapporter des points supplémentaires : un parcours d’index uniquement peut tout de même accéder au tas si une page n’est pas marquée comme entièrement visible dans la carte de visibilité. Après d’importantes mises à jour, exécutez VACUUM afin que la carte de visibilité soit à jour ; sinon, Heap Fetches augmente et l’avantage de l’« index uniquement » diminue.
-- Keeps the visibility map fresh so index-only scans stay heap-free
VACUUM ANALYZE orders;Quand NOT créer un index couvrant large
Les index couvrants ne sont pas gratuits. Ajouter de nombreuses colonnes à INCLUDE rend l’index volumineux, consomme du cache et ralentit les écritures (chaque écriture concernée met l’index à jour). Compromis à énoncer :
- Idéal pour les requêtes de lecture ciblées, très sollicitées et fréquentes.
- À éviter comme fourre-tout où entasser chaque colonne « au cas où ».
Couvrez la requête importante, pas la ligne entière.
Comment le formuler en entretien
Un résumé clair :
« Un index couvrant contient chaque colonne consultée par une requête ; le moteur y répond donc uniquement à partir de l’index, via un parcours d’index uniquement, en évitant la lecture du tas. Je place les colonnes recherchées dans la clé et celles qui sont uniquement renvoyées dans INCLUDE, je vérifie que les lectures du tas sont nulles avec EXPLAIN ANALYZE et je garde l’index étroit pour préserver la vitesse d’écriture. »
Vérification rapide
Analysez la couverture et le bon emplacement de chaque colonne.
Récapitulatif : les index couvrants
Points essentiels :
- Un index couvre une requête lorsqu’il contient chaque colonne dont celle-ci a besoin, ce qui permet un parcours d’index uniquement sans lecture du tas.
- Les colonnes clés guident les recherches et respectent la règle du préfixe le plus à gauche ; les colonnes d’INCLUDE sont des données utiles présentes uniquement dans les feuilles pour assurer la couverture.
- Les index secondaires InnoDB incluent implicitement la clé primaire.
- Vérifiez avec
EXPLAIN (ANALYZE)et surveillezHeap Fetches; dans PostgreSQL, maintenezVACUUMà jour. - Gardez les index couvrants compacts pour préserver les performances d’écriture.
Ensuite : le revers, lorsque les index nuisent réellement.
Apprends SQL avec un tuteur IA — gratuit
Écris et exécute du vrai code dans ton navigateur, obtiens de l'aide instantanée d'un tuteur IA disponible 24h/24, et reprends là où tu t'es arrêté sur le web ou dans l'app.
- Cours
- 30
- Leçons
- 120
Questions Fréquemment Posées
La leçon « Index couvrants et parcours par index seul » est-elle gratuite ?
Oui — le texte complet de « Index couvrants et parcours par index seul » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Index couvrants et parcours par index seul » ?
Inclure des colonnes afin qu’une requête n’accède jamais au tas de la table Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?
Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.
Combien de temps prend la leçon « Index couvrants et parcours par index seul » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?
Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Index B-tree et leur utilité
- Ordre des colonnes d’un index composite
- Index couvrants et parcours par index seul
- Quand les index nuisent : écritures et sélectivité