Narcis Miclaus

6 min di lettura

Indici clustered e non clustered, spiegati come avrei voluto me li spiegassero

Perché ogni tabella vuole un indice clustered, come scegliere la colonna, quanto costa davvero la frammentazione e l'amplificazione di scritture nascosta dietro le scelte sbagliate.

La prima volta che qualcuno mi ha detto che un indice clustered “è” la tabella, ho annuito educatamente facendo finta di aver capito. Mi ci è voluto un anno di piani di esecuzione sorprendenti perché la cosa facesse davvero clic. Questa è la spiegazione che avrei voluto avere il primo giorno.

Cos’è davvero un indice clustered

In SQL Server, una tabella è memorizzata come un B-tree. L’indice clustered è la regola di come quel B-tree è ordinato sul disco. Ce n’è esattamente uno per tabella, perché una tabella può essere ordinata fisicamente solo in un modo per volta.

Quando scrivi:

CREATE TABLE dbo.Orders (
    OrderId    BIGINT      NOT NULL PRIMARY KEY,
    CustomerId INT         NOT NULL,
    OrderDate  DATETIME2(0) NOT NULL,
    Amount     DECIMAL(18,2) NOT NULL
);

…SQL Server crea silenziosamente un indice clustered su OrderId per te, perché la primary key di default è clustered. Le foglie di quel B-tree sono le righe di Orders. Non c’è una struttura “tabella” separata sotto. L’indice è la tabella.

Un indice non clustered è un secondo B-tree, più piccolo, che vive accanto alla tabella. Le sue foglie non contengono le righe — contengono le colonne indicizzate più un puntatore di ritorno all’indice clustered. Quindi una query che usa un indice non clustered fa due lookup: uno nell’albero non clustered per trovare il puntatore, poi un “key lookup” di ritorno nell’indice clustered per il resto delle colonne. (A meno che l’indice non clustered non sia covering, cioè abbia ogni colonna che la query chiede nelle sue pagine foglia o nella lista INCLUDE.)

Se una tabella non ha indice clustered si chiama heap, e un heap è quasi sempre un errore. Gli heap non ordinano le loro pagine, si frammentano in modo imprevedibile e costringono ogni indice non clustered a usare un identificatore fisico di riga che si rompe se le pagine si spostano. Fai sì che ogni tabella abbia un indice clustered. Questa è l’unica regola che non ha quasi eccezioni.

Scegliere la chiave clustered

La chiave clustered è la decisione di schema più importante che prendi su una tabella, perché detta l’ordine fisico di ogni riga e ogni indice non clustered ne memorizza una copia. La checklist classica (è di Kimberly Tripp e regge da quindici anni):

  1. Stretta. Ogni byte della chiave clustered è ripetuto in ogni indice non clustered. Una chiave clustered larga gonfia ogni altro indice sulla tabella.
  2. Statica. Aggiornare la chiave clustered significa spostare fisicamente la riga, il che è costoso e aggiorna ogni indice non clustered. Scegli una colonna che non cambia.
  3. Unica. Se la tua chiave clustered non è unica, SQL Server aggiunge silenziosamente un “uniquifier” da 4 byte alle tue spalle. Meglio essere unici di proposito.
  4. Sempre crescente. Le nuove righe finiscono così sulla pagina di coda del B-tree, evitando page split nel mezzo dell’albero. Per questo BIGINT IDENTITY e DATETIME2 (per tabelle di eventi append-only) sono i due cavalli di battaglia per le chiavi clustered.

Per questo la gente ti mette in guardia dalle chiavi clustered GUID: sono larghe (16 byte), casuali (ogni insert finisce in una pagina diversa) e causano frammentazione costante. Se proprio devi usare un GUID come chiave logica, fanne un indice non clustered unico e clustera su un BIGINT IDENTITY. Questo cambio da solo ha salvato più di un sistema di produzione su cui ho lavorato.

Quali colonne mettere negli indici non clustered

Costruisci indici non clustered per le query che fai davvero. La regola pratica:

  • Prima le colonne di equality, poi le colonne di range, poi quelle di ordinamento, e poi metti il resto in INCLUDE.
  • Per WHERE customer_id = @c AND order_date >= @start, l’indice (customer_id, order_date) è giusto; (order_date, customer_id) è sbagliato, perché una volta che fai una range scan su order_date la seconda colonna non è più ordinata sotto il tuo filtro.
  • Usa INCLUDE per le colonne che la query deve restituire ma non deve filtrare o ordinare. Vivono nelle pagine foglia, rendono l’indice covering, e non gonfiano i livelli alti dell’albero.

Non indicizzare ogni colonna “per sicurezza”. Ogni indice non clustered va mantenuto a ogni INSERT, UPDATE e DELETE. Cinque indici ben scelti supereranno quindici indici scelti male, perché le scritture non si accumulano.

Il costo delle scritture che tutti dimenticano

Gli indici rendono le letture più veloci e le scritture più lente. L’indice clustered è speciale perché è la tabella — non puoi evitarlo. Ma ogni indice non clustered moltiplica il costo delle tue scritture. Una tabella con 10 indici non clustered paga per 11 scritture a ogni INSERT (la tabella più ogni indice), e lo stesso per ogni DELETE, e per ogni UPDATE se una qualsiasi delle colonne indicizzate è cambiata.

È la parte che nessuno menziona nei tutorial. Ho visto un job di ingestion write-heavy passare da 90 secondi a 14 minuti perché qualcuno ha aggiunto due indici non clustered “utili” durante una sessione di debug e si è dimenticato di rimuoverli. I piani di esecuzione sul lato letture erano bellissimi. La pipeline è morta.

Una regola utile: prima di aggiungere un indice non clustered su una tabella calda, guarda quante scritture al minuto fa quella tabella e chiediti se il beneficio in lettura vale qualche microsecondo in più su ognuna di quelle.

Frammentazione, e come affrontarla davvero

Ci sono due tipi di frammentazione che contano:

  • Frammentazione esterna (logica) — pagine che non sono più in ordine fisico sul disco. Fa male alle range scan grandi (una SELECT su un milione di righe legge le pagine in disordine).
  • Frammentazione interna — pagine mezze vuote a causa di UPDATE e page split. Spreca cache e disco.

La regola pratica che funziona per me — anche questa originariamente dalla guida Microsoft:

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name                     AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id  = ips.index_id
WHERE ips.page_count > 1000          -- ignora gli indici minuscoli
ORDER BY ips.avg_fragmentation_in_percent DESC;

Poi:

  • Frammentati 5–30% → ALTER INDEX ... REORGANIZE. Online, basso impatto, non sistema completamente l’indice ma deframmenta le pagine foglia.
  • Frammentati > 30% → ALTER INDEX ... REBUILD. Più pesante, ricostruisce l’indice da zero. Usa WITH (ONLINE = ON) su Enterprise Edition per tenere la tabella disponibile; su Standard Edition il rebuild prende uno schema lock, quindi lo fai in una finestra di manutenzione.
  • Sotto il 5% → lascia stare. Deframmentare quantità minuscole è puro I/O senza beneficio.

La vittoria più grande però è non far accadere la frammentazione in primo luogo: scegli una chiave clustered sempre crescente, imposta un FILLFACTOR sensato (90% su tabelle che ricevono molti UPDATE, 100% su quelle append-only), e accetta che SHRINKDATABASE è quasi sempre un errore — riframmenta ogni indice che tocca.

Il modello mentale

Quando capisci che l’indice clustered è la tabella e che ogni indice non clustered porta con sé una copia della chiave clustered, un sacco di consigli che sembravano arbitrari trovano il loro posto:

  • Chiave clustered larga → ogni indice non clustered diventa più grande.
  • Chiave clustered casuale → ogni insert frammenta la tabella.
  • Aggiornare una colonna della chiave clustered → sposta fisicamente la riga e aggiorna ogni indice non clustered.
  • Troppi indici non clustered → le scritture rallentano di un fattore proporzionale a quanti ne hai.

Gli indici sono un budget. Spendilo sulle query che contano, lascia stare il resto, e il te del futuro ti ringrazierà quando l’alert delle 3 di notte sarà per la tabella di qualcun altro.