Columnstore Indexes Described
L'indice columnstore in memoria SQL Server archivia e gestisce i dati usando l'archiviazione dei dati basata su colonne e l'elaborazione di query basate su colonne. Gli indici columnstore sono ideali per i carichi di lavoro di data warehousing che eseguono principalmente caricamenti bulk e query di sola lettura. Usare l'indice columnstore per migliorare fino a 10 volte le prestazioni delle query rispetto all'archiviazione tradizionale orientata alle righe e fino a 7 volte la compressione dei dati rispetto alle dimensioni dei dati non compressi.
Nota
L'indice columnstore cluster viene considerato lo standard per l'archiviazione di grandi tabelle dei fatti di data warehouse e si prevede venga utilizzato nella maggior parte degli scenari di data warehouse. Poiché l'indice columnstore cluster è aggiornabile, il carico di lavoro può eseguire molte operazioni di inserimento, aggiornamento ed eliminazione.
Sommario
Nozioni di base
L' columnstore index è una tecnologia per l'archiviazione, il recupero e la gestione dei dati utilizzando un formato di dati in colonna, detto columnstore. SQL Server supporta sia gli indici columnstore cluster che non cluster. Entrambi utilizzano la stessa tecnologia columnstore in memoria, ma presentano differenze nello scopo e nelle funzionalità supportate.
Vantaggi
Gli indici columnstore sono ottimali per la maggior parte delle query di sola lettura che eseguono analisi su grandi set di dati. Spesso, si tratta di query per carichi di lavoro di data warehousing. Gli indici columnstore consentono di migliorare notevolmente le prestazioni delle query che utilizzano scansioni complete delle tabelle e non sono particolarmente adatti per le query eseguite per cercare un valore specifico nei dati.
Vantaggi dell'indice columnstore:
Le colonne contengono spesso dati simili che generano frequenze di compressione elevate.
Le frequenze di compressione elevate migliorano le prestazioni delle query utilizzando un footprint di memoria più piccolo. A sua volta, le prestazioni delle query possono migliorare perché SQL Server può eseguire più operazioni di query e dati in memoria.
In SQL Server è stato aggiunto un nuovo meccanismo per l'esecuzione di query, denominato modalità batch, che consente di ridurre in modo significativo l'utilizzo della CPU. L'esecuzione in modalità batch è strettamente integrata al formato di archiviazione columnstore, per il quale è ottimizzata. L'esecuzione in modalità batch talvolta è detta esecuzione basata su vettore o vettorizzata.
Le query spesso selezionano solo alcune colonne di una tabella, riducendo il totale delle operazioni di I/O su un supporto fisico.
Versioni columnstore
In SQL Server 2012, SQL Server 2012 Parallel Data Warehouse e SQL Server 2014 vengono utilizzati gli indici columnstore per velocizzare le query di data warehouse comuni. SQL Server 2012 ha introdotto due nuove funzionalità: un indice columnstore non cluster e una funzionalità di esecuzione di query basata su vettori che elabora i dati in unità denominate "batch". SQL Server 2014 include le funzionalità di SQL Server 2012 più indici columnstore cluster aggiornabili.
Caratteristiche chiave
| Si applica a: da SQL Server 2014 a SQL Server 2019 (15,x). |
In SQL Server un indice columnstore cluster:
È disponibile nelle edizioni Enterprise, Developer ed Evaluation.
È aggiornabile.
È il metodo di archiviazione primario per l'intera tabella.
Non ha colonne chiave. Tutte le colonne sono incluse.
È l'unico indice nella tabella. Non può essere combinato con altri indici.
Può essere configurato per utilizzare columnstore o la compressione dell'archivio columnstore.
Non archivia fisicamente le colonne in base a un ordinamento. Archivia i dati per migliorare la compressione e le prestazioni.
| Si applica a: da SQL Server 2012 a SQL Server 2019 (15,x). |
In SQL Server un indice columnstore non cluster:
È possibile indicizzare un subset di colonne nell'heap o nell'indice cluster. Ad esempio, è possibile indicizzare le colonne utilizzate di frequente.
Richiede uno spazio di archiviazione aggiuntivo per archiviare una copia delle colonne dell'indice.
Viene aggiornato ricompilando l'indice o spostando le partizioni in uscita e in uscita. Non è aggiornabile usando le operazioni DML, ad esempio inserimento, aggiornamento ed eliminazione.
Può essere combinato con altri indici nella tabella.
Può essere configurato per utilizzare columnstore o la compressione dell'archivio columnstore.
Non archivia fisicamente le colonne in base a un ordinamento. Archivia i dati per migliorare la compressione e le prestazioni. Non è obbligatorio preordinare i dati prima di creare l'indice columnstore, ma questa operazione può migliorare la compressione columnstore.
Concetti e termini chiave
I seguenti concetti e termini chiave sono associati agli indici columnstore.
Indice columnstore Un indice columnstore è una tecnologia per l'archiviazione, il recupero e la gestione dei dati tramite un formato di dati a colonne, denominato columnstore. SQL Server supporta sia gli indici columnstore cluster che non cluster. Entrambi utilizzano la stessa tecnologia columnstore in memoria, ma presentano differenze nello scopo e nelle funzionalità supportate.
columnstore Un columnstore è costituito da dati organizzati logicamente come tabella con righe e colonne e archiviati fisicamente in un formato di dati a livello di colonna.
rowstore Un rowstore è costituito da dati organizzati logicamente come tabella con righe e colonne e quindi archiviati fisicamente in un formato di dati a livello di riga. È stata la modalità di archiviazione tradizionale per archiviare dati relazionali di tabella.
rowgroup e segmenti di colonna Per prestazioni elevate e frequenze di compressione elevate, l'indice columnstore suddivide la tabella in gruppi di righe, denominati gruppi di righe e quindi comprime ogni gruppo di righe in modo indipendente dalle colonne. Il numero di righe nel gruppo di righe deve essere sufficientemente grande da migliorare le frequenze di compressione e sufficientemente ridotto da poter trarre vantaggio dalle operazioni in memoria.
il gruppo di righe Un rowgroup è un gruppo di righe compresse contemporaneamente in formato columnstore.
segmento di colonna Un segmento di colonna è una colonna di dati dall'interno del rowgroup.
Un rowgroup contiene in genere il numero massimo di righe per rowgroup, pari a 1.048.576 righe.
Ogni rowgroup contiene un segmento di colonna per ogni colonna della tabella.
Ogni segmento di colonna è compresso e archiviato su un supporto fisico.
indice columnstore non cluster Un indice columnstore non cluster è un indice columnstore di sola lettura creato in una tabella heap o indice cluster esistente. Contiene una copia di un subset di colonne, fino a includere tutte le colonne della tabella. La tabella è di sola lettura mentre contiene un indice columnstore non cluster.
Un indice columnstore non cluster consente di disporre di un indice columnstore per eseguire query di analisi e, contemporaneamente, operazioni di sola lettura nella tabella originale.
indice columnstore cluster Un indice columnstore cluster è l'archiviazione fisica per l'intera tabella ed è l'unico indice per la tabella. L'indice cluster è aggiornabile. È possibile eseguire nell'indice operazioni di inserimento, eliminazione e aggiornamento, nonché il caricamento bulk dei dati.
cluster
Per ridurre la frammentazione dei segmenti di colonna e migliorare le prestazioni, l'indice columnstore può archiviare alcuni dati temporaneamente in una tabella rowstore, denominata deltastore, nonché un albero B di ID per le righe eliminate. Le operazioni deltastore sono gestite in modo automatico. Per tornare ai risultati della query corretti, l'indice columnstore cluster combina i risultati della query da columnstore e deltastore.
deltastore Usato solo con indici columnstore cluster, un deltastore è una tabella rowstore che archivia le righe fino a quando il numero di righe è sufficientemente grande da essere spostato nel columnstore. Un deltastore viene utilizzato con indici columnstore cluster per migliorare le prestazioni del caricamento e di altre operazioni DML.
Durante un caricamento bulk di grandi dimensioni, la maggior parte delle righe viene direttamente indirizzata al columnstore senza passare per il deltastore. È possibile che alla fine del caricamento bulk il numero delle righe sia insufficiente a soddisfare le dimensioni minime di un rowgroup, pari a 102.400. In questo caso, le righe finali vengono indirizzate al deltastore anziché al columnstore. Per i caricamenti bulk di piccole dimensioni, con meno di 102.400 righe, tutte le righe passano direttamente al deltastore.
Quando il deltastore raggiunge il numero massimo di righe, viene chiuso. Un processo tuple-move controlla i gruppi di righe chiusi. Quando trova il rowgroup chiuso, lo comprime e lo archivia nel columnstore.
Caricamento dei dati
Caricamento di dati in un indice columnstore non cluster
Per caricare i dati in un indice columnstore non cluster, caricare prima i dati in una tabella rowstore tradizionale archiviata come heap o indice cluster e quindi creare l'indice columnstore non cluster con CREATE COLUMNSTORE INDEX (Transact-SQL).
di
Una tabella con un indice columnstore non cluster è di sola lettura finché l'indice non viene eliminato o disabilitato. Per aggiornare la tabella e l'indice columnstore non cluster, è possibile passare da una partizione all'altra. È anche possibile disabilitare l'indice, aggiornare la tabella e quindi ricompilare l'indice.
Per ulteriori informazioni, vedere Using Nonclustered Columnstore Indexes.
Caricamento di dati in un indice columnstore cluster
Come suggerisce il diagramma, per caricare i dati in un indice columnstore cluster, SQL Server:
I rowgroup di dimensioni massime vengono inseriti direttamente nel columnstore. Quando i dati vengono caricati, SQL Server assegna le righe di dati in un ordine first-come first-serve in un rowgroup aperto.
Per ogni rowgroup, dopo aver raggiunto le dimensioni massime, SQL Server:
Il rowgroup viene contrassegnato come CLOSED.
Il deltastore viene ignorato.
Ogni segmento di colonna viene compresso con il rowgroup con la compressione del columnstore.
Ogni segmento di colonna compresso viene fisicamente archiviato nel columnstore.
Le righe rimanenti vengono inserite nel columnstore o nel deltastore come segue:
Se il numero di righe soddisfa il requisito minimo di righe per rowgroup, le righe vengono aggiunte al columnstore.
Se il numero di righe è inferiore al requisito minimo di righe per rowgroup, le righe vengono aggiunte al deltastore.
Per ulteriori informazioni sulle attività e i processi deltastore, vedere Using Clustered Columnstore Indexes.
Suggerimenti sulle prestazioni
Pianificare una quantità di memoria sufficiente per creare indici columnstore in parallelo
Per impostazione predefinita, la creazione di un indice columnstore è un'operazione parallela, a meno che la memoria non sia vincolata. La creazione dell'indice in parallelo richiede più memoria rispetto alla creazione dell'indice in modo seriale. Se si dispone di un'ampia quantità di memoria, la creazione di un indice columnstore richiede un tempo di circa 1,5 volte superiore rispetto alla compilazione di un albero B nelle stesse colonne.
La memoria richiesta per la creazione di un indice columnstore dipende dal numero di colonne, dal numero di colonne stringa, dal grado di parallelismo e dalle caratteristiche dei dati. Ad esempio, se la tabella contiene meno di un milione di righe, SQL Server utilizzerà un solo thread per creare l'indice columnstore.
Se la tabella dispone di più di un milione di righe, ma SQL Server non ha a disposizione memoria sufficiente per creare l'indice utilizzando MAXDOP, SQL Server ridurrà automaticamente MAXDOP secondo le esigenze per adattarsi alla memoria disponibile. In alcuni casi, è necessario ridurre il grado di parallelismo a uno per compilare l'indice nella memoria vincolata.
Attività e argomenti correlati
Indici columnstore non cluster
Per le attività comuni, vedere Using Nonclustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) con REBUILD.
Indici columnstore cluster
Per le attività comuni, vedere Using Clustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) con REBUILD o REORGANIZE.
Metadati
Tutte le colonne di un indice columnstore vengono archiviate nei metadati come colonne incluse. L'indice columnstore non contiene colonne chiave.