Freigeben über

Columnstore-Indizes: Übersicht

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

Columnstore-Indizes stellen den Standard für das Speichern und Abfragen großer Data Warehousing-Faktentabellen dar. Dieser Index verwendet spaltenbasierte Datenspeicherung und Abfrageverarbeitung, um eine bis zu zehnmal höhere Abfrageleistung im Data Warehouse im Vergleich zur herkömmlichen zeilenorientierten Speicherung zu erzielen. Sie können im Vergleich zur unkomprimierten Datengröße außerdem eine bis zu 10-mal höhere Datenkomprimierung erzielen. Ab SQL Server 2016 (13.x) SP1 ermöglichen Columnstore-Indizes die operative Analyse und bieten damit die Möglichkeit, leistungsfähige Echtzeitanalysen von Transaktionsworkloads durchzuführen.

Erfahren Sie mehr über ähnliche Szenarien:

Was ist ein Columnstore-Index?

Ein Columnstore-Index ist eine Technologie zum Speichern, Abrufen und Verwalten von Daten mithilfe eines spaltenbasierten Datenformats, das als „Columnstore“ bezeichnet wird.

Hauptbegriffe und -Konzepte

Die folgenden Hauptbegriffe und -konzepte werden im Zusammenhang mit Columnstore-Indizes verwendet.

Columnstore

Ein Columnstore enthält Daten, die logisch als Tabelle mit Zeilen und Spalten organisiert und physisch in einem Spaltendatenformat gespeichert sind.

Rowstore

Ein Rowstore enthält Daten, die logisch als Tabelle mit Zeilen und Spalten organisiert und physisch in einem Zeilendatenformat gespeichert sind. Dieses Format wird üblicherweise zum Speichern relationaler Tabellendaten verwendet. In SQL Server bezieht Rowstore sich auf die Tabelle, deren zugrunde liegendes Datenspeicherformat ein Heap, ein gruppierter Index oder eine speicheroptimierte Tabelle ist.

Hinweis

Bei Diskussionen über Columnstore-Indizes werden die Begriffe „Rowstore“ und „Columnstore“ verwendet, um das Format der Datenspeicherung zu betonen.

Rowgroup

Eine Zeilengruppe ist eine Gruppe von Zeilen, die gleichzeitig im Columnstore-Format komprimiert werden. Eine Zeilengruppe enthält in der Regel die maximale Anzahl von Zeilen pro Zeilengruppe (d. h. 1.048.576 Zeilen).

Um eine hohe Leistung und hohe Komprimierungsraten zu erzielen, unterteilt der Columnstore-Index die Tabelle in Zeilengruppen und komprimiert dann jede Zeilengruppe nach Spalten. 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.

Eine Rowgroup, aus der alle Daten gelöscht wurden, ändert sich vom Zustand COMPRESSED nach TOMBSTONE. Später wird sie durch einen Hintergrundprozess entfernt, der auch Tupelverschiebungsvorgang (tuple-mover) genannt wird. Weitere Informationen zu Zeilengruppenzuständen finden Sie unter sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Tipp

Zu viele kleine Zeilengruppen verschlechtern die Qualität des Columnstore-Index. Bis SQL Server 2017 (14.x) ist ein Neuorganisierungsvorgang erforderlich, um kleinere COMPRESSED-Zeilengruppen zusammenzuführen. Dabei wird eine interne Schwellenwertrichtlinie eingehalten, die bestimmt, wie gelöschte Zeilen entfernt und die komprimierten Zeilengruppen kombiniert werden.
Ab SQL Server 2019 (15.x) wird ein Hintergrundmergevorgang durchgeführt, um COMPRESSED-Zeilengruppen zu zusammenzuführen, bei denen viele Zeilen gelöscht wurden.
Nachdem kleinere Zeilengruppen zusammengeführt wurden, sollte sich die Indexqualität verbessern.

Hinweis

Seit SQL Server 2019 (15.x), Azure SQL Database, Azure SQL Managed Instance und den dedizierten SQL-Pools in Azure Synapse Analytics wird der Tupelverschiebungsvorgang von einem Zusammenführungstask im Hintergrund unterstützt. Dieser komprimiert automatisch kleinere OPEN-Deltazeilengruppen, die für einen bestimmten durch einen internen Schwellenwert definierten Zeitraum vorhanden waren, oder führt COMPRESSED-Zeilengruppen zusammen, aus denen eine große Anzahl von Zeilen gelöscht wurde. Dies verbessert die Qualität des Columnstore-Index im Lauf der Zeit.

Spaltensegment

Ein Spaltensegment ist eine Spalte mit Daten aus der Zeilengruppe.

  • Jede Zeilengruppe enthält ein Spaltensegment für jede Spalte in der Tabelle.
  • Jedes Spaltensegment wird zusammenhängend komprimiert und auf physischen Medien gespeichert.
  • Es gibt Metadaten für jedes Segment, damit Segmente, ohne sie zu lesen, schnell eliminiert werden können.

Logisches Diagramm des Spaltensegments. Jede Spalte weist ein Spaltensegment pro Zeilengruppe auf.

Geclusterter Columnstore-Index

Ein gruppierter Columnstore-Index ist der physische Speicher für die gesamte Tabelle.

Logisches Diagramm eines gruppierten Columnstore-Indexes. Enthält komprimierte Spaltensegmente plus Zeilen im Index, aber nicht im Spaltenspeicher.

Um die Fragmentierung der Spaltensegmente zu verringern und die Leistung zu verbessern, können einige Daten im Columnstore-Index vorübergehend in einem gruppierten Index (Deltastore) und in einer B-Struktur mit IDs der gelöschten 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.

Hinweis

In der Dokumentation wird der Begriff „B-Baum“ im Allgemeinen in Bezug auf Indexe verwendet. In Rowstore-Indizes implementiert die Datenbank-Engine einen B+-Baum. Dies gilt nicht für Columnstore-Indizes oder Indizes auf speicheroptimierten Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Delta-Rowgroup

Eine Deltazeilengruppe ist ein gruppierter B-Baum-Index, der nur mit Columnstore-Indizes verwendet wird. Sie verbessert die Columnstore-Komprimierung und -Leistung, indem sie Zeilen solange speichert, bis bei der Zeilenanzahl ein bestimmter Schwellenwert (1.048.576 Zeilen) erreicht wird und diese dann in den Columnstore verschoben werden.

Wenn eine Deltazeilengruppe die maximale Zeilenanzahl erreicht, ändert sich ihr Zustand von OPEN in CLOSED. Ein Hintergrundprozess namens Tupelverschiebungsvorgang überprüft auf geschlossene Zeilengruppen. Wenn der Prozess eine geschlossene Zeilengruppe findet, wird die Deltazeilengruppe komprimiert und im Columnstore als COMPRESSED-Zeilengruppe gespeichert.

Wenn eine Delta-Rowgroup komprimiert wurde, ändert sich der Zustand der vorhandenen Delta-Rowgroup in TOMBSTONE, damit sie später im Tupelverschiebungsvorgang entfernt wird, wenn nicht auf sie verwiesen wird.

Weitere Informationen zu Zeilengruppenzuständen finden Sie unter sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Hinweis

Ab SQL Server 2019 (15.x) wird der Tupelverschiebungsvorgang von einem Mergetask im Hintergrund unterstützt, der automatisch kleinere OPEN-Deltazeilengruppen komprimiert, die für einen bestimmten Zeitraum vorhanden waren (wie durch einen internen Schwellenwert festgelegt), oder COMPRESSED-Zeilengruppen zusammenführt, aus denen eine große Anzahl von Zeilen gelöscht wurde. Dies verbessert die Qualität des Columnstore-Index im Lauf der Zeit.

Deltastore

Ein Columnstore-Index kann mehr als eine Deltazeilengruppe haben. Alle Delta-Rowgroups zusammen werden als „Deltastore“ bezeichnet.

Während eines großen Massenladevorgangs werden die meisten Zeilen ohne Umweg über den Deltastore direkt in den Columnstore verschoben. Einige Zeilen am Ende des Massenladevorgangs könnten zu wenige sein, um die Mindestgröße einer Zeilengruppe von 102.400 Zeilen zu erreichen. Als Ergebnis werden die letzten Zeilen in den Deltastore anstatt in den Columnstore verschoben. Bei kleinen Massenladevorgängen mit weniger als 102 400 Zeilen werden alle Zeilen direkt in den Deltastore verschoben.

Nicht geclusterter Columnstore-Index

Ein nicht gruppierter Columnstore-Index und ein gruppierter Columnstore-Index haben die gleiche Funktionsweise. Der Unterschied besteht darin, dass ein nicht gruppierter Index ein sekundärer Index ist, der für eine Rowstore-Tabelle erstellt wird. Ein gruppierter Columnstore-Index hingegen ist der primäre Speicher für die gesamte Tabelle.

Der nicht gruppierte Index enthält eine Kopie eines Teils oder aller Zeilen und Spalten der zugrundeliegenden Tabelle. Der Index ist als mindestens eine Spalte der Tabelle definiert und weist eine optionale Bedingung auf, die zum Filtern der Zeilen dient.

Ein nicht gruppierter Columnstore-Index ermöglicht operative Echtzeitanalyse, bei der der OLTP-Workload den zugrunde liegenden gruppierten Index verwendet, während die Analyse parallel auf dem Columnstore-Index ausgeführt wird. Weitere Informationen finden Sie unter Erste Schritte mit Columnstore für operative Echtzeitanalyse.

Ausführung im Batchmodus

Die Batchmodusausführung ist eine Methode zur Abfrageverarbeitung, die zum gleichzeitigen Abfragen mehrerer Zeilen verwendet 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. Abfragen von Columnstore-Indizes verwenden die Batchmodusausführung, die die Abfrageleistung in der Regel um das Zwei- bis Vierfache steigert. Weitere Informationen finden Sie im Handbuch zur Architektur der Abfrageverarbeitung.

Warum sollte ich einen Columnstore-Index verwenden?

Ein Columnstore-Index kann eine sehr hohe Datenkomprimierung bieten, normalerweise etwa zehnfach. Dadurch werden die Speicherkosten für ein Data Warehouse erheblich reduziert. Für Analysezwecke bietet der Columnstore-Index eine um eine Größenordnung bessere Leistung als ein B-Baum-Index. Columnstore-Indizes stellen das bevorzugte Datenspeicherformat für Data Warehouse- und Analyseworkloads dar. Ab SQL Server 2016 (13.x) können Sie Columnstore-Indizes für Echtzeitanalysen Ihrer Betriebsarbeitsauslastung verwenden.

Gründe für die hohe Geschwindigkeit von Columnstore-Indizes:

  • Spalten speichern Werte aus der gleichen Domäne und weisen oft ähnliche Werte auf, was zu hohen Komprimierungsraten führt. E/A-Engpässe in Ihrem System werden minimiert oder beseitigt, und der Speicherbedarf wird deutlich reduziert.

  • Hohe Komprimierungsraten verbessern die Abfrageleistung, da der Arbeitsspeicherbedarf geringer ist. Auch die Abfrageleistung kann verbessert werden, da SQL Server eine größere Anzahl von speicherinternen Abfrage- und Datenvorgängen ausführen kann.

  • Die Batchausführung verbessert die Abfrageleistung, in der Regel um das Zwei- bis Vierfache, indem mehrere Zeilen zusammen verarbeitet werden.

  • Abfragen wählen oft nur wenige Spalten aus einer Tabelle aus, was die Gesamtanzahl der E/A-Vorgänge bei physischen Medien reduziert.

Wann sollte ein Columnstore-Index verwendet werden?

Empfohlene Einsatzgebiete:

Wie treffe ich die Entscheidung zwischen einem Rowstore-Index und einem Columnstore-Index?

Rowstore-Indizes eignen sich am besten für Abfragen, die Daten durchsuchen, wenn sie nach einem bestimmten Wert suchen, oder bei Abfragen mit einem kleinen Wertebereich. Verwenden Sie Rowstore-Indizes für Transaktionsworkloads, da sie tendenziell eher Suchvorgänge in Tabellen als Scans ganzer Tabellen erfordern.

Columnstore-Indizes ermöglichen große Leistungsvorteile bei Analyseabfragen, die große Mengen von Daten durchsuchen, insbesondere bei umfangreichen Tabellen. Verwenden Sie Columnstore-Indizes für Data Warehousing- und Analyseworkloads, insbesondere für Faktentabellen, da diese eher vollständige Tabellenscans als Suchvorgänge in Tabellen erfordern.

Sortierte gruppierte Columnstore-Indizes verbessern die Leistung für Abfragen basierend auf sortierten Spalten-Prädikaten. Sortierte Columnstore-Indizes können die Eliminierung von Zeilengruppen verbessern, wodurch sich Leistungsverbesserungen erzielen lassen, indem Zeilengruppen vollständig übersprungen werden. Weitere Informationen finden Sie unter Leistungsoptimierung mit geordneten Columnstore-Indizes. Informationen zur Verfügbarkeit des sortierten Columnstore-Indexes finden Sie unter Verfügbarkeit des sortierten Columnstore-Indexes.

Können Rowstore und Columnstore in der gleichen Tabelle kombiniert werden?

Ja. Ab SQL Server 2016 (13.x) können Sie einen aktualisierbaren, nicht gruppierten Columnstore-Index für eine Rowstore-Tabelle erstellen. Der Columnstore-Index speichert eine Kopie der ausgewählten Spalten, sodass zusätzlicher Speicherplatz benötigt wird. Allerdings werden die ausgewählten Daten im Durchschnitt um das Zehnfache komprimiert. So können Sie Analysen im Columnstore-Index und Transaktionen im Rowstore-Index zur gleichen Zeit ausführen. Der Columnstore wird aktualisiert, wenn sich Daten in der Rowstore-Tabelle ändern, daher arbeiten beide Indizes mit den gleichen Daten.

Ab SQL Server 2016 (13.x) können Sie mindestens einen nicht gruppierten Rowstore-Index auf einem Columnstore-Index haben und auf dem zugrunde liegenden Columnstore effiziente Suchen in Tabellen durchführen. Auch weitere Optionen werden dadurch verfügbar. Beispielsweise können Sie eine Primärschlüsseleinschränkung durchsetzen, indem Sie eine UNIQUE-Bedingung auf die Rowstore-Tabelle anwenden. Da ein nicht eindeutiger Wert nicht in die Rowstore-Tabelle eingefügt werden kann, kann SQL Server den Wert nicht in den Columnstore einfügen.

Sortierte Columnstore-Indizes

Durch die Aktivierung einer effizienten Segmentlöschung bieten sortierte Columnstore-Indizes eine schnellere Leistung, indem große Mengen sortierter Daten übersprungen werden, die nicht mit dem Abfrage-Prädikat übereinstimmen. Das Laden von Daten in einen geordneten Columnstore-Index kann aufgrund des Datensortierungsvorgangs länger dauern als in einem nicht sortierten Index, bei geordneten Columnstore-Indizes können Abfragen jedoch später schneller ausgeführt werden.

Verfügbarkeit des sortierten Columnstore-Indexes

Erstmals mit SQL Server 2022 (16.x) eingeführt, stehen geordnete Spaltenspeicherindizes auf den folgenden Plattformen zur Verfügung:

Plattform Sortierte gruppierte Columnstore-Indizes Sortierte nicht geclusterte Columnstore-Indizes
Azure SQL-Datenbank Ja Ja
Azure SQL Managed InstanceAUTD Ja Ja
Azure SQL Managed Instance2022 Ja Nein
SQL-Datenbank in Microsoft Fabric Ja1 Ja
SQL Server 2022 (16.x) Ja Nein
Dedizierte SQL-Pools in Azure Synapse Analytics Ja Nein

AUTD- Gilt für Azure SQL Managed Instance-Instanzen, die mit der Updaterichtlinie konfiguriert sind, die sicherstellt, dass die Instanzen immer auf dem neuesten Stand sind.
2022 gilt für azure SQL Managed Instance, die mit der SQL Server 2022-Updaterichtliniekonfiguriert ist.
1In der Fabric SQL-Datenbank werden Tabellen mit geclusterten Columnstore-Indizes nicht in Fabric OneLake gespiegelt.

Metadaten

Alle Spalten in einem Columnstore-Index werden in den Metadaten als eingeschlossene Spalten gespeichert. Der Columnstore-Index hat keine Schlüsselspalten.

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (Transact-SQL)

sys.column_store_segments (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_row_groups (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

Alle relationalen Tabellen, sofern Sie sie nicht als gruppierten Columnstore-Index festlegen, verwenden Rowstore als zugrundeliegendes Datenformat. CREATE TABLE erstellt eine Rowstore-Tabelle, es sei denn, Sie geben die Option WITH CLUSTERED COLUMNSTORE INDEX an.

Beim Erstellen einer Tabelle mit der CREATE TABLE-Anweisung können Sie die Tabelle als Columnstore erstellen, indem Sie die Option WITH CLUSTERED COLUMNSTORE INDEX angeben. Wenn Sie bereits über eine Rowstore-Tabelle verfügen, die Sie in einen Columnstore konvertieren möchten, können Sie die Anweisung CREATE COLUMNSTORE INDEX verwenden.

Aufgabe Referenzartikel Hinweise
Erstellen einer Tabelle als Columnstore. CREATE TABLE (Transact-SQL) Ab SQL Server 2016 (13.x) können Sie die Tabelle als gruppierten Columnstore-Index erstellen. Sie müssen nicht zuerst eine Rowstore-Tabelle erstellen, die Sie dann in Columnstore konvertieren.
Erstellen Sie eine speicheroptimierte Tabelle mit einem Columnstore-Index. CREATE TABLE (Transact-SQL) Ab SQL Server 2016 (13.x) können Sie eine speicheroptimierte Tabelle mit einem Columnstore-Index erstellen. Der Columnstore-Index kann auch nach dem Erstellen der Tabelle mit der ALTER TABLE ADD INDEX-Syntax hinzugefügt werden.
Konvertieren einer Rowstore-Tabelle in eine Columnstore-Tabelle. CREATE COLUMNSTORE INDEX (Transact-SQL) Konvertieren Sie einen vorhandenen Heap- oder B-Struktur in eine Columnstore-Tabelle. Aus den Beispielen können Sie ersehen, wie vorhandene Indizes und der Name des Index beim Durchführen der Konvertierung behandelt werden.
Konvertieren einer Columnstore-Tabelle in einen Rowstore CREATE CLUSTERED INDEX (Transact-SQL) oder Konvertieren Sie eine Columnstore-Tabelle zurück in einen Rowstore-Heap In der Regel müssen Sie nicht konvertieren, allerdings kann es vorkommen, dass Sie diese Aktion durchführen müssen. Die Beispiele zeigen, wie ein Columnstore in einen Heap oder einen gruppierten Index konvertiert werden kann.
Erstellen Sie einen Columnstore-Index für eine Rowstore-Tabelle. CREATE COLUMNSTORE INDEX (Transact-SQL) Eine Rowstore-Tabelle kann über einen Columnstore-Index verfügen. Ab SQL Server 2016 (13.x) kann der Columnstore-Index eine Filterbedingung aufweisen. In den Beispielen wird die grundlegende Syntax verwendet.
Erstellen leistungsfähiger Indizes für Betriebsanalysen. Erste Schritte mit Columnstore für operative Echtzeitanalyse Beschreibt, wie man ergänzende Columnstore-Indizes und B-Baum-Indizes erstellt, sodass OLTP-Abfragen B-Baum-Indizes und Analyseabfragen Columnstore-Indizes verwenden.
Erstellen leistungsfähiger Columnstore-Indizes für Data Warehousing Columnstore-Indizes für die Datenarchivierung Beschreibt, wie B-Baum-Indizes für Columnstore-Tabellen verwendet werden können, um leistungsfähige Abfragen im Data Warehousing zu erstellen.
Verwenden eines B-Strukturindexes, um eine Primärschlüsseleinschränkung für einen Columnstore-Index zu erzwingen. Columnstore-Indizes für die Datenlagerung Zeigt, wie B-Struktur- und Columnstore-Indizes kombiniert werden können, um Primärschlüsseleinschränkungen für den Columnstore-Index zu erzwingen.
Löschen eines Columnstore-Indexes DROP INDEX (Transact-SQL) Beim Löschen eines Columnstore-Indexes wird die standardmäßige DROP INDEX-Syntax verwendet, die auch B-Strukturindizes verwenden. Beim Löschen eines gruppierten Columnstore-Indexes wird die Columnstore-Tabelle in einen Heap konvertiert.
Löschen einer Zeile aus einem Columnstore-Index. DELETE (Transact-SQL) Verwenden Sie DELETE (Transact-SQL) zum Löschen einer Zeile.

columnstore row: SQL Server markiert die Zeile als logisch gelöscht, der physische Speicherplatz für die Zeile wird jedoch erst wieder freigegeben, wenn der Index neu erstellt wurde.
deltastore row: SQL Server löscht die Zeile logisch und physisch.
Aktualisieren einer Zeile im Columnstore-Index. UPDATE (Transact-SQL) Verwenden Sie UPDATE (Transact-SQL), um eine Zeile zu aktualisieren.

columnstore row: SQL Server markiert die Zeile als logisch gelöscht und fügt die aktualisierte Zeile dann in den Deltastore ein.
deltastore row: SQL Server aktualisiert die Zeile im Deltastore.
Daten in einen Columnstore-Index laden. Columnstore-Indizes: Leitfaden zum Datenladevorgang
Erzwingen, dass alle Zeilen im Deltastore in den Columnstore verschoben werden ALTER INDEX (Transact-SQL) ... REBUILD

Optimierung der Indexwartung zur Verbesserung der Abfrageleistung und Reduzierung des Ressourcenverbrauchs		 ALTER INDEX mit der Option REBUILD erzwingt das Verschieben aller Zeilen in den Columnstore.
Defragmentieren eines Columnstore-Index. ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE defragmentiert Columnstore-Indizes online.
Zusammenführen von Tabellen mit Columnstore-Indizes MERGE (Transact-SQL)

Zugehöriger Inhalt