Columnstore Indexes Described
Der SQL Server In-Memory-Columnstore-Index speichert und verwaltet Daten mithilfe des spaltenbasierten Datenspeichers und der spaltenbasierten Abfrageverarbeitung. Columnstore-Indizes sind optimal für Data Warehousing-Arbeitsauslastungen geeignet, die hauptsächlich Massenladevorgänge und schreibgeschützte Abfragen ausführen. Verwenden Sie den Columnstore-Index, um eine bis zu zehnfache Abfrageleistung gegenüber der herkömmlichen zeilenorientierten Speicherung und eine bis zu siebenfache Datenkomprimierung im Vergleich zur unkomprimierten Datengröße zu erzielen.
Hinweis
Wir sehen den gruppierten Columnstore-Index als Standard für das Speichern von großen Data Warehousing-Faktentabellen an und erwarten, dass er in den meisten Data Warehousing-Szenarien verwendet wird. Da der gruppierte Columnstore-Index aktualisierbar ist, kann die Arbeitsauslastung eine große Anzahl von Einfüge-, Update- und Löschvorgängen ausführen.
Contents
Grundlagen
Ein columnstore index ist eine Technologie zum Speichern, Abrufen und Verwalten von Daten mithilfe eines spaltenbasierten Datenformats, das als Columnstore bezeichnet wird. SQL Server unterstützt gruppierte und nicht gruppierte Columnstore-Indizes. Beide verwenden die gleiche In-Memory-Columnstore-Technologie, sie unterscheiden sich jedoch hinsichtlich ihres Verwendungszwecks und Funktionsumfangs.
Vorteile
Columnstore-Indizes sind für die meisten schreibgeschützten Abfragen geeignet, die Analysen für große Datasets ausführen. Dabei handelt es sich häufig um Abfragen für Data Warehousing-Arbeitsauslastungen. Columnstore-Indizes bieten bei Abfragen, die vollständige Tabellenscans verwenden, große Leistungsvorteile und sind nicht für Abfragen geeignet, die Suchvorgänge in Daten ausführen, um einen bestimmten Wert suchen.
Vorteile von Columnstore-Indizes:
Spalten weisen häufig ähnliche Daten auf, wodurch eine hohe Komprimierungsraten erzielt wird.
Hohe Komprimierungsraten verbessern die Abfrageleistung, da der Arbeitsspeicherbedarf geringer ist. Die Abfrageleistung kann sich wiederum verbessern, da SQL Server mehr Abfrage- und Datenvorgänge im Arbeitsspeicher ausführen können.
SQL Server besitzt jetzt einen neuen Abfrageausführungsmechanismus, der als Batchmodusausführung bezeichnet wird und durch den die CPU-Nutzung beträchtlich reduziert wird. Die Batchmodusausführung ist eng in das Columnstore-Speicherformat integriert und für dieses optimiert. Die Batchmodusausführung wird auch als vektorbasierte oder vektorisierte Ausführung bezeichnet.
Bei Abfragen werden häufig nur wenige Spalten aus einer Tabelle ausgewählt, wodurch das Gesamtaufkommen der E/A-Vorgänge für das physische Medium reduziert wird.
Columnstore-Versionen
SQL Server 2012, SQL Server 2012 Parallel Data Warehouse und SQL Server 2014 verwenden Columnstore-Indizes, um häufig verwendete Data Warehouse-Abfragen zu beschleunigen. SQL Server 2012 wurden zwei neue Features eingeführt: ein nicht gruppierter Columnstore-Index und eine vektorbasierte Abfrageausführungsfunktion, die Daten in Einheiten verarbeitet, die als "Batches" bezeichnet werden. SQL Server 2014 verfügt über die Features von SQL Server 2012 sowie aktualisierbaren gruppierten Columnstore-Indizes.
Hauptmerkmale
| Gilt für: SQL Server 2014 bis SQL Server 2019 (15.x). |
In SQL Server ein gruppierter Columnstore-Index:
Er ist in Enterprise-, Developer- und Evaluation-Editionen verfügbar.
Er kann aktualisiert werden.
Er stellt die Hauptspeichermethode für die gesamte Tabelle dar.
Er besitzt keine Schlüsselspalten. Alle Spalten sind eingeschlossene Spalten.
Er stellt den einzigen Index für die Tabelle dar. Er kann nicht mit anderen Indizes kombiniert werden.
Er kann für die Verwendung von Columnstore- oder Columnstore-Archivierungskomprimierung konfiguriert werden.
Spalten werden nicht physisch in einer sortierten Reihenfolge gespeichert. Stattdessen werden Daten so gespeichert, dass Komprimierung und Leistung verbessert werden.
| Gilt für: SQL Server 2012 bis SQL Server 2019 (15.x). |
In SQL Server ein nicht gruppierter Columnstore-Index:
Er kann einen Index einer Teilmenge von Spalten im gruppierten Index oder Heap erstellen. Beispielsweise kann ein Index der häufig verwendeten Spalten erstellt werden.
Er benötigt zusätzlichen Speicherplatz, um eine Kopie der Spalten im Index zu speichern.
Wird aktualisiert, indem der Index neu erstellt oder Partitionen ein- und ausgeschaltet werden. Sie kann nicht mithilfe der DML-Vorgänge wie Einfügen, Aktualisieren und Löschen aktualisiert werden.
Er kann mit anderen Indizes der Tabelle kombiniert werden.
Er kann für die Verwendung von Columnstore- oder Columnstore-Archivierungskomprimierung konfiguriert werden.
Spalten werden nicht physisch in einer sortierten Reihenfolge gespeichert. Stattdessen werden Daten so gespeichert, dass Komprimierung und Leistung verbessert werden. Eine Vorsortierung der Daten vor der Erstellung des Columnstore-Indexes ist nicht erforderlich, kann die Columnstore-Komprimierung jedoch verbessern.
Hauptbegriffe und -konzepte
Die folgenden Hauptbegriffe und -konzepte werden im Zusammenhang mit Columnstore-Indizes verwendet.
Columnstore-Index Ein Columnstore-Index ist eine Technologie zum Speichern, Abrufen und Verwalten von Daten mithilfe eines spaltenbasierten Datenformats, das als Columnstore bezeichnet wird. SQL Server unterstützt gruppierte und nicht gruppierte Columnstore-Indizes. Beide verwenden die gleiche In-Memory-Columnstore-Technologie, sie unterscheiden sich jedoch hinsichtlich ihres Verwendungszwecks und Funktionsumfangs.
columnstore Ein Columnstore sind Daten, die logisch als Tabelle mit Zeilen und Spalten organisiert und physisch in einem spaltenbasierten Datenformat gespeichert sind.
rowstore Ein Rowstore sind Daten, die logisch als Tabelle mit Zeilen und Spalten organisiert und dann physisch in einem zeilenbasierten Datenformat gespeichert werden. Dies war die traditionelle Vorgehensweise beim Speichern von Daten aus relationalen Tabellen.
Zeilengruppen und Spaltensegmente Für hohe Leistung und hohe Komprimierungsraten unterteilt der Columnstore-Index die Tabelle in Zeilengruppen, die als Zeilengruppen bezeichnet werden, und komprimiert dann jede Zeilengruppe spaltenweise. Die Anzahl der Zeilen in der Zeilengruppe muss groß genug sein, um die Komprimierungsraten zu verbessern, und klein genug, um von In-Memory-Vorgängen profitieren zu können.
Zeilengruppe Eine Zeilengruppe ist eine Gruppe von Zeilen, die gleichzeitig im Columnstore-Format komprimiert werden.
Spaltensegment Ein Spaltensegment ist eine Spalte mit Daten aus der Zeilengruppe.
Eine Zeilengruppe enthält normalerweise die maximale Anzahl von Zeilen pro Zeilengruppe, die 1.048.576 Zeilen beträgt.
Jede Zeilengruppe enthält ein Spaltensegment für jede Spalte in der Tabelle.
Jedes Spaltensegment wird zusammenhängend komprimiert und auf physischen Medien gespeichert.
Nicht gruppierter Columnstore-Index Ein nicht gruppierter Columnstore-Index ist ein schreibgeschützter Index, der für einen vorhandenen gruppierten Index oder eine Heaptabelle erstellt wird. Er enthält eine Kopie einer Teilmenge von Spalten, die maximal alle Spalten in der Tabelle umfassen kann. Die Tabelle ist schreibgeschützt, während sie einen nicht gruppierten Columnstore-Index enthält.
Ein nicht gruppierter Columnstore-Index bietet eine Möglichkeit, über einen Columnstore-Index zum Ausführen von Analyseabfragen zu verfügen, während gleichzeitig schreibgeschützte Vorgänge für die ursprüngliche Tabelle ausgeführt werden.
gruppierter Columnstore-Index Ein gruppierter Columnstore-Index ist der physische Speicher für die gesamte Tabelle und der einzige Index für die Tabelle. Der gruppierte Index kann aktualisiert werden. Sie können Einfüge-, Lösch- und Updatevorgänge für den Index ausführen und per Massenladen Daten in den Index laden.
Um die Fragmentierung der Spaltensegmente zu verringern und die Leistung zu verbessern, können einige Daten im Columnstore-Index vorübergehend in einer Zeilenspeichertabelle, die als Deltastore bezeichnet wird, sowie eine B-Struktur der IDs für gelöschte Zeilen gespeichert werden. Die Deltastore-Vorgänge werden im Hintergrund verarbeitet. Damit die richtigen Abfrageergebnisse zurückgegeben werden, kombiniert der gruppierte Columnstore-Index Abfrageergebnisse aus dem Columnstore und dem Deltastore.
deltastore Wird nur mit gruppierten Columnstore-Indizes verwendet, ist ein Deltastore eine Rowstore-Tabelle, in der Zeilen gespeichert werden, bis die Anzahl der Zeilen groß genug ist, um in den Columnstore verschoben zu werden. Ein Deltastore wird mit gruppierten Columnstore-Indizes verwendet, um die Leistung bei Lade- und anderen DML-Vorgängen zu verbessern.
Während eines umfassenden Massenladevorgangs werden die meisten Zeilen ohne Umweg über den Deltastore direkt in den Columnstore verschoben. Einige Zeilen am Ende des Massenladevorgangs erreichen möglicherweise nicht die notwendige Anzahl für die minimale Größe einer Zeilengruppe von 102.400 Zeilen. Wenn dieser Fall auftritt, werden die letzten Zeilen in den Deltastore und nicht in den Columnstore aufgenommen. Bei kleinen Massenladevorgängen mit weniger als 102.400 Zeilen werden alle Zeilen direkt in den Deltastore verschoben.
Wenn der Deltastore die maximale Zeilenanzahl erreicht, wird er geschlossen. Ein Tupelverschiebungsvorgang überprüft auf geschlossene Zeilengruppen. Wenn die geschlossene Zeilengruppe gefunden wird, wird sie komprimiert und im Columnstore-Index gespeichert.
Laden von Daten
Laden von Daten in einen nicht gruppierten Columnstore-Index
Um Daten in einen nicht gruppierten Columnstore-Index zu laden, laden Sie zunächst Daten in eine herkömmliche Rowstore-Tabelle, die als Heap oder gruppierter Index gespeichert ist, und erstellen Sie dann den nicht gruppierten Columnstore-Index mit CREATE COLUMNSTORE INDEX (Transact-SQL).
Eine Tabelle mit einem nicht gruppierten Columnstore-Index ist so lange schreibgeschützt, bis der Index gelöscht oder deaktiviert wird. Um die Tabelle und den nicht gruppierten Columnstore-Index zu aktualisieren, können Sie Partitionen ein- und auswechseln. Sie können auch den Index deaktivieren, die Tabelle aktualisieren und dann den Index neu erstellen.
Weitere Informationen finden Sie unter Using Nonclustered Columnstore Indexes.
Laden von Daten in einen gruppierten Columnstore-Index
Um Daten in einen gruppierten Columnstore-Index zu laden, SQL Server wie im Diagramm dargestellt:
Zeilengruppen mit maximal zulässiger Größe werden direkt in den Columnstore eingefügt. Während die Daten geladen werden, weist SQL Server die Datenzeilen in einer First-Come-First-Serve-Reihenfolge einer offenen Zeilengruppe zu.
SQL Server für jede Zeilengruppe, nachdem sie die maximale Größe erreicht hat:
Die Zeilengruppe wird als CLOSED markiert.
Der Deltastore wird umgangen.
Jedes Spaltensegment für die Zeilengruppe wird mit der Columnstore-Komprimierung komprimiert.
Jedes komprimierte Spaltensegment wird physisch im Columnstore-Index gespeichert.
Die übrigen Zeilen werden wie folgt in den Columnstore oder den Deltastore eingefügt:
Wenn die Zeilenanzahl die Anforderung bzgl. der minimalen Anzahl von Zeilen pro Zeilengruppe erfüllt, werden die Zeilen dem Columnstore hinzugefügt.
Wenn die Zeilenanzahl unter der minimalen Anzahl von Zeilen pro Zeilengruppe liegt, werden die Zeilen dem Deltastore hinzugefügt.
Weitere Informationen zu Deltastore-Aufgaben und -Vorgängen finden Sie unter Using Clustered Columnstore Indexes.
Tipps zur Leistungssteigerung
Planen Sie ausreichend Arbeitsspeicher für eine parallele Erstellung von Columnstore-Indizes ein
Bei der Erstellung eines Columnstore-Indexes handelt es sich standardmäßig um einen parallel ausgeführten Vorgang, sofern der verfügbare Arbeitsspeicher nicht eingeschränkt ist. Die parallele Indexerstellung erfordert mehr Arbeitsspeicher als die serielle Erstellung des Index. Wenn ausreichend Arbeitsspeicher verfügbar ist, dauert das Erstellen eines Columnstore-Indexes 1,5-mal so lange wie das Erstellen einer B-Struktur für die gleichen Spalten.
Der Speicherplatz, der für das Erstellen eines Columnstore-Indexes erforderlich ist, hängt von der Anzahl von Spalten, der Anzahl der Zeichenfolgenspalten, dem Grad der Parallelität (Degree of Parallelism, DOP) und von den Eigenschaften der Daten ab. Wenn die Tabelle beispielsweise weniger als eine Million Zeilen aufweist, verwendet SQL Server nur einen Thread, um den Columnstore-Index zu erstellen.
Wenn die Tabelle mehr als eine Million Zeilen aufweist, SQL Server aber keine ausreichend dimensionierte Arbeitsspeicherzuweisung abrufen kann, um den Index mit MAXDOP zu erstellen, verringert SQL Server MAXDOP automatisch nach Bedarf, um es auf den verfügbaren Arbeitsspeicher zu beschränken. In bestimmten Fällen muss DOP auf eins verringert werden, um den Index mit eingeschränktem Arbeitsspeicher zu erstellen.
Verwandte Aufgaben und Themen
Nicht gruppierte Columnstore-Indizes
Informationen für häufige Aufgaben finden Sie unter Using Nonclustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) mit REBUILD.
Gruppierte Columnstore-Indizes
Informationen für häufige Aufgaben finden Sie unter Using Clustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) mit REBUILD oder REORGANIZE.
Metadaten
Alle Spalten in einem Columnstore-Index werden in den Metadaten als eingeschlossene Spalten gespeichert. Der Columnstore-Index weist keine Schlüsselspalten auf.