Ottimizzare con gli indici

Gli indici sono strutture di dati che accelerano il recupero dei dati creando percorsi di ricerca ottimizzati per le righe di tabella. Senza indici, il motore di database deve analizzare ogni riga di una tabella per trovare record corrispondenti, un'analisi completa della tabella che diventa proibitivamente lenta man mano che le tabelle aumentano.

Un indice funziona come l'indice di un libro: invece di leggere ogni pagina per trovare un articolo, consultare l'indice per passare direttamente alle pagine pertinenti. Il database usa indici in modo analogo, convertendo potenzialmente milioni di confronti di righe in poche ricerche efficienti.

Tuttavia, gli indici utilizzano lo spazio di archiviazione e rallentano INSERTle operazioni , UPDATEe DELETE perché il database deve mantenere la struttura dell'indice insieme ai dati. Questo compromesso rende la selezione dell'indice una decisione di progettazione critica che influisce direttamente sulle prestazioni delle query e sulla velocità effettiva di scrittura.

Diversi tipi di indice servono scopi diversi.

Usare gli indici rowstore

La progettazione di indici efficienti è fondamentale per ottenere prestazioni ottimali del database e dell'applicazione. La mancanza di indici, l'over-indexing o gli indici progettati in modo non appropriato sono le principali fonti di problemi di prestazioni del database.

Gli indici rowstore organizzano i dati in formato di riga, archiviando tutte le colonne di una riga nella stessa pagina, rendendoli ottimali per i carichi di lavoro transazionali che recuperano record completi o eseguono aggiornamenti frequenti.

Un indice cluster ordina e archivia le righe di dati nella tabella in base ai relativi valori chiave. Questi valori chiave sono le colonne incluse nella definizione dell'indice. Per ogni tabella è disponibile un solo indice cluster, poiché alle righe di dati è possibile applicare un solo tipo di ordinamento.

Un indice non cluster ha una struttura separata dalle righe di dati. Un indice non clusterizzato contiene i valori di chiave dell'indice non clusterizzato e ogni valore di chiave ha un puntatore alla riga di dati che contiene il valore di chiave. È possibile creare più indici non clusterizzati su una tabella o vista indicizzata.

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

Gli indici cluster sono ottimali quando sono necessarie query di intervallo efficienti, chiavi stabili e strette o un ordinamento naturale, ad esempio colonne Identity o campi di data, perché definiscono l'ordine delle righe fisiche e ottimizzano le analisi sui dati ordinati.

Gli indici non cluster sono ideali quando sono necessarie ricerche rapide per predicati, join o criteri di ordinamento specifici che non sono allineati alla chiave cluster o quando si vuole coprire una query includendo colonne aggiuntive per evitare ricerche chiave.

La scelta tra di esse dipende dal modo in cui si accede ai dati: usare un indice cluster per il percorso di accesso primario e gli indici non cluster per supportare modelli alternativi, altamente selettivi o frequentemente sottoposti a query durante il bilanciamento dei costi introdotti nelle operazioni di scrittura.

Comprendere gli indici columnstore

Gli indici rowstore tradizionali archiviano i dati riga per riga, ideale per i sistemi transazionali che recuperano singoli record. Tuttavia, le query analitiche che analizzano milioni di righe per calcolare le aggregazioni (SUM, AVG, COUNT) sprecano tempo durante la lettura delle colonne non necessarie. Gli indici columnstore hanno lo scopo di risolvere questo problema archiviando i dati colonna per colonna, leggendo solo le colonne necessarie per la tua query.

Informazioni sull'architettura columnstore

Un indice columnstore organizza i dati in rowgroup, ognuno contenente fino a 1.048.576 righe. All'interno di ogni rowgroup, il motore archivia ogni colonna separatamente come segmento di colonna e la comprime in modo indipendente. Questa architettura consente a Query Optimizer di leggere solo le colonne necessarie per una query, ignorando completamente i dati irrilevanti.

Quando si inseriscono dati, i batch di piccole dimensioni passano prima a un deltastore, ovvero una struttura rowstore temporanea usando un indice albero B+. Una volta che un gruppo di righe delta accumula un numero sufficiente di righe (almeno 102.400), un processo in background denominato tuple-mover lo comprime nell'archivio a colonne. Le righe che arrivano attraverso carichi in blocco di 102.400 o più righe ignorano il deltastore e comprimono direttamente nel columnstore.

Nella tabella seguente viene descritta la raccomandazione per gli indici columnstore:

Usare gli indici columnstore in cluster (CCI)

Un indice columnstore compresso (CCI) è un tipo di indice columnstore che diventa la struttura di archiviazione principale dell'intera tabella, sostituendo qualsiasi indice rowstore clusterizzato esistente. A differenza di un indice columnstore noncluster (NCCI), che crea una copia secondaria a colonne accanto alla tabella rowstore, un CCI archivia tutti i dati della tabella esclusivamente in formato columnstore.

Ciò significa che la tabella non ha un archivio tradizionale basato su righe, ovvero il motore comprime e archivia ogni colonna separatamente. Sia CCI che NCCI usano la stessa compressione a colonne e le stesse ottimizzazioni di elaborazione batch, ma usare un CCI quando l'analisi è il carico di lavoro primario e non sono necessari modelli di accesso transazionale a livello di riga. Al contrario, un NCCI consente di gestire gli indici rowstore per le query transazionali, fornendo al tempo stesso una struttura a colonne per le query analitiche nella stessa tabella.

È possibile creare un indice columnstore raggruppato usando l'istruzione CREATE CLUSTERED COLUMNSTORE INDEX. Ecco un esempio:

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

Usare l'indice columnstore non raggruppato (NCCI)

Un indice columnstore non clusterizzato crea una copia colonnare separata delle colonne selezionate accanto alla tabella rowstore esistente, permettendo alla stessa tabella di gestire in modo efficiente carichi di lavoro transazionali e analitici. La tabella mantiene l'indice rowstore in cluster originale per ricerche e aggiornamenti veloci a riga singola, mentre NCCI offre un accesso ottimizzato in base alle colonne per le query analitiche. Query Optimizer sceglie automaticamente tra le strutture rowstore e columnstore in base al modello di query.

È possibile creare un indice columnstore nonclusterizzato utilizzando l'istruzione CREATE NONCLUSTERED COLUMNSTORE INDEX . Ecco un esempio:

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

Monitorare gli indici columnstore

È possibile monitorare lo stato di salute e le prestazioni degli indici columnstore eseguendo una query sulla vista di gestione dinamica sys.dm_db_column_store_row_group_physical_stats.

La query seguente mostra le statistiche del rowgroup, inclusi lo stato, i conteggi delle righe, le righe eliminate e le dimensioni di archiviazione. I rowgroup aperti accettano ancora inserimenti nel deltastore, quelli chiusi sono in attesa che il tuple-mover li comprima, mentre i rowgroup compressi archiviano i dati in formato colonnare. Numero elevato di righe eliminate o molti rowgroup di piccole dimensioni indicano la frammentazione che è possibile risolvere con ALTER INDEX REORGANIZE.

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

La selezione dell'indice influisce direttamente sulle prestazioni delle query e sulla velocità effettiva di scrittura. Progettare con attenzione gli indici durante lo sviluppo iniziale per evitare costose ricompilazioni e problemi di prestazioni in produzione.