Come ottimizza un indice l'esecuzione di una query?

L'indice è una struttura dati (solitamente un albero B) che memorizza una piccola parte dei dati della tabella e collegamenti alle righe corrispondenti nella tabella principale.

Processo di ottimizzazione:

1. Ricerca rapida: Invece di scansionare l'intera tabella (full table scan), il sistema di gestione del database (DBMS) esamina l'indice. L'indice è ordinato e ottimizzato per ricerche rapide su colonne specifiche.
2. Riduzione dei dati letti: L'indice contiene solo le colonne indicizzate e i puntatori, riducendo significativamente la quantità di dati da leggere dal disco rispetto alla lettura di tutta la riga o di tutta la pagina di dati.
3. Nessuna ordinazione: L'indice è già ordinato per le colonne indicizzate. Ciò elimina la necessità di eseguire un'operazione di ordinamento costosa se la query richiede risultati ordinati.
4. Ottimizzazione delle operazioni JOIN: Durante l'unione di tabelle (JOIN) su colonne indicizzate, il DBMS può usare l'indice per trovare rapidamente le righe corrispondenti in un'altra tabella, invece di eseguire cicli annidati sull'intera tabella.

Esempio:

Senza indice sulla colonna LastName per la query SELECT * FROM Users WHERE LastName = 'Ivanov', il DBMS dovrebbe leggere ogni riga nella tabella Users e verificare il valore di LastName.

Con un indice su LastName, il DBMS trova rapidamente i record dove LastName è uguale a 'Ivanov', esaminando l'indice ordinato e poi usando i puntatori dell'indice per ottenere i dati completi delle righe nella tabella principale.

-- Esempio di query che beneficia dell'indice su LastName
SELECT UserId, FirstName
FROM Users
WHERE LastName = 'Ivanov';

-- Esempio di creazione di un indice per ottimizzare la query sopra
CREATE INDEX IX_Users_LastName ON Users (LastName);