0Pricing
SQL Interview Prep · Lezione

Indici covering e scansioni index-only

Inclusione delle colonne necessarie affinché una query non debba mai accedere all'heap della tabella.

Indici covering e scansioni index-only è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

Ripasso del recupero dall'heap

In precedenza ha imparato che un normale B-Tree memorizza solo le colonne indicizzate e un puntatore alla riga; quindi, dopo aver trovato le corrispondenze nell'indice, il motore deve comunque accedere alla tabella per leggere le altre colonne. Questo passaggio è il recupero dall'heap ed è il costo che un indice di copertura è progettato per eliminare.

Gli intervistatori chiedono degli indici di copertura per verificare se comprende perché un indice possa rispondere completamente a una query senza accedere alla tabella.

Che cosa significa «covering»

Un indice copre una query quando ogni colonna necessaria alla query, presente in SELECT, WHERE, ORDER BY e GROUP BY, è contenuta nell'indice stesso.

In questo caso, il motore legge solo l'indice e non accede mai alla tabella. PostgreSQL chiama questa operazione Index-Only Scan; SQL Server e altri sistemi la chiamano indice di copertura. Il vantaggio consiste in un minor numero di letture di pagine e in query più veloci.

Esempio svolto: una query coperta

Supponga che una query richieda solo customer_id e order_date. Un indice composto esattamente su queste colonne contiene tutto ciò che la query richiede, quindi è possibile rispondere alla query usando soltanto l'indice.

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;

Una sola colonna aggiuntiva interrompe la copertura

Se aggiunge una colonna che l'indice non contiene, la copertura viene persa e il motore deve recuperare l'heap per ottenerla.

Qui total non è presente nell'indice; quindi, anche se customer_id guida la ricerca, ogni riga corrispondente richiede un recupero dall'heap per leggere 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 clausola INCLUDE

Potrebbe aggiungere total come quarta colonna chiave, ma se non applica mai filtri né ordinamenti su di essa, sprecherebbe spazio nell'ordine di ordinamento dell'albero. Lo strumento più adatto è INCLUDE (supportato da PostgreSQL e SQL Server): memorizza le colonne aggiuntive solo nelle foglie dell'indice, come payload e non come parte della chiave di ordinamento.

In questo modo la query è coperta senza appesantire la parte ricercabile dell'indice.

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;

Colonne chiave e colonne incluse

Una distinzione precisa che fa una buona impressione agli intervistatori:

  • Le colonne chiave definiscono l'ordine di ordinamento e possono essere usate per eseguire seek e scansioni per intervallo. Seguono la regola del prefisso più a sinistra.
  • Le colonne incluse sono memorizzate solo nelle foglie come dati aggiuntivi; non possono essere ricercate, ma permettono all'indice di coprire un numero maggiore di query.

Regola pratica: le colonne su cui applica filtri o ordina vanno nella chiave; quelle che si limita a restituire vanno in INCLUDE.

MySQL/InnoDB: la particolarità del clustering

Dimostri di conoscere le differenze tra i vari dialetti. Le tabelle InnoDB (MySQL) sono clusterizzate sulla chiave primaria: gli indici secondari contengono implicitamente le colonne della chiave primaria. Di conseguenza, un indice secondario copre automaticamente qualsiasi query che selezioni solo le colonne indicizzate e quelle della chiave primaria; non serve alcuna clausola INCLUDE (MySQL non dispone di INCLUDE).

Il concetto di copertura è universale; la sintassi e le colonne incluse automaticamente variano a seconda del motore.

Verificare un Index-Only Scan

Dimostri la copertura con EXPLAIN. In PostgreSQL, il piano contiene il nodo Index Only Scan invece di Index Scan. In EXPLAIN (ANALYZE), verifichi la presenza di Heap Fetches: 0: è il segnale definitivo che non è stato effettuato alcun accesso alla tabella.

Se si aspettava un Index-Only Scan ma vede Index Scan con recuperi dall'heap, significa che una colonna selezionata non è presente nell'indice.

EXPLAIN (ANALYZE)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;
-- Look for: Index Only Scan ... Heap Fetches: 0

La particolarità della visibility map in Postgres

Un dettaglio di Postgres che vale un punto bonus: un Index-Only Scan può comunque accedere all'heap se una pagina non è contrassegnata come completamente visibile nella visibility map. Dopo numerosi aggiornamenti, esegua VACUUM per mantenere aggiornata la visibility map; altrimenti Heap Fetches aumenta e il vantaggio dell'operazione «solo indice» si riduce.

-- Keeps the visibility map fresh so index-only scans stay heap-free
VACUUM ANALYZE orders;

Quando NON creare un indice di copertura ampio

Gli indici di copertura non sono gratuiti. Inserire molte colonne in INCLUDE rende l'indice grande, consuma spazio nella cache e rallenta le scritture, perché ogni scrittura rilevante aggiorna l'indice. È utile esplicitare questi compromessi:

  • Ottimi per query di lettura frequenti, mirate e ad alto traffico.
  • Inadatti come contenitore indiscriminato per ogni colonna «nel dubbio».

Copra la query che conta, non l'intera riga.

Come formulare la risposta al colloquio

Un riepilogo efficace:

«Un indice di copertura contiene ogni colonna utilizzata da una query, quindi il motore può rispondere usando soltanto l'indice, con un Index-Only Scan, evitando il recupero dall'heap. Inserisco nella chiave le colonne ricercate e in INCLUDE quelle restituite soltanto, verifico con EXPLAIN ANALYZE che Heap Fetches sia zero e mantengo l'indice compatto per proteggere la velocità di scrittura.»

Verifica rapida

Ragioni sulla copertura e sulla posizione corretta di ogni colonna.

Riepilogo: indici di copertura

Punti chiave:

  • Un indice copre una query quando contiene ogni colonna necessaria alla query, consentendo un Index-Only Scan senza recupero dall'heap.
  • Le colonne chiave guidano le ricerche e seguono la regola del prefisso più a sinistra; le colonne in INCLUDE sono dati aggiuntivi presenti solo nelle foglie, usati per la copertura.
  • Gli indici secondari di InnoDB includono implicitamente la chiave primaria.
  • Verifichi con EXPLAIN (ANALYZE) e controlli Heap Fetches; in Postgres mantenga aggiornato VACUUM.
  • Mantenga gli indici di copertura compatti per proteggere le prestazioni di scrittura.

Prossimo argomento: il rovescio della medaglia, cioè quando gli indici fanno effettivamente peggiorare le prestazioni.

Domande Frequenti

La lezione «Indici covering e scansioni index-only» è gratuita?

Sì — il testo completo di «Indici covering e scansioni index-only» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Indici covering e scansioni index-only»?

Inclusione delle colonne necessarie affinché una query non debba mai accedere all'heap della tabella. Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Indici covering e scansioni index-only»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?

Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Indici B-Tree e loro utilità
  2. Ordine delle colonne negli indici compositi
  3. Indici covering e scansioni index-only
  4. Quando gli indici sono dannosi: scritture e selettività
← Torna a SQL Interview Prep