Clustered Columnstore-Index für kleine Tabellen

Nov 23 2020

Indizierte Tabellen mit gruppiertem Spaltenspeicher sind im Allgemeinen für große Tabellen nützlich. Idealerweise mit Millionen von Zeilen. Und auch nützlich bei Abfragen, bei denen nur die Teilmenge der verfügbaren Spalten in solchen Tabellen ausgewählt wird.

Was passiert, wenn wir gegen diese beiden "Regeln" / Best Practices verstoßen?

  1. Wie bei einer indizierten Tabelle mit gruppierten Spaltenspeichern, in der nur wenige Tausend oder Hunderttausende von Zeilen gespeichert werden.
  2. Ausführen von Abfragen für die gruppierte Spaltenspeichertabelle, in der alle Spalten benötigt werden.

Meine Tests zeigen keine Leistungseinbußen im Vergleich zur in Zeilen gespeicherten Clustered-Index-Tabelle. Welches ist großartig in unserem Fall.

Gibt es "Langzeit" -Effekte, die gegen diese beiden Regeln verstoßen? Oder versteckte Fallstricke, die noch nicht aufgetaucht sind?

Kontext Warum wird es benötigt: Ich habe ein Datenbankmodell entworfen, das für viele Instanzen verschiedener Herstellerdatenbanken verwendet wird. Das Schema bleibt in jeder Datenbank gleich, aber verschiedene Anbieter haben unterschiedliche Datenmengen. Daher haben möglicherweise nur wenige kleine Anbieter eine geringe Datenmenge (<1 000 000) in ihren Tabellen. Ich kann mir nicht erlauben, zwei verschiedene Datenbanken für das Zeilen- und Spaltenspeichermodell zu verwalten.

Antworten

3 J.D. Nov 24 2020 at 00:20

Um @YunusUYANIK auf die möglichen Nachteile beim Entwerfen Ihres Schemas hinzuweisen, das nur auf eine Seite ausgerichtet ist, können Sie für Ihre Tabelle sowohl Zeilen- als auch Spaltenspeicherindizes erstellen, die für beide Szenarien geeignet sind. Sicher, Sie indizieren möglicherweise dieselben Felder in beide Richtungen, aber der Hauptnachteil besteht darin, dass nur mehr Speicherplatz verwendet wird, was bei der Planung der Leistung im Allgemeinen weniger wichtig ist.

Dies hängt von Ihrem Schema und der Datenmenge in Ihren Tabellen für jeden Anbieter ab. Sie müssen daher testen, ob Ihr Design der Indizes in den entsprechenden Abfragen für die verschiedenen Datenmengen basierend auf den Prädikaten Ihres Anbieters verwendet wird . Im schlimmsten Fall müssen Sie manchmal auch Indexhinweise verwenden, aber ich bin der Meinung, dass dies nicht sehr wahrscheinlich ist, wenn Sie beide Indextypen korrekt entwerfen.

4 YunusUYANIK Nov 23 2020 at 22:16

Der Columnstore-Index hat einen großen Vorteil bei der Größe der Komprimierungsdaten. Das allgemeine Ziel des Columnstore-Index besteht darin, aufgrund seiner Komprimierung schnell eine Reihe von Daten zu lesen.

CCI ist Columnstore Clustered Index, Clustered ist Clustered Index

Der Columnstore-Index komprimiert die Datengröße von 4 MB auf 2 MB.

Wir können die Leistung in zwei Tabellen und drei Teilen betrachten.

Die erste ist die minimale SELECTOperation:

SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333

SELECT * FROM Users_Clustered WHERE Id=12333

Ergebnis : Es gibt Columnstore Scanhier und falsch geschätzt. Weiter logischer Leseunterschied. Sie können sagen, dass es für Sie nicht wichtig ist, aber wenn Sie eine minimale SELECT-Abfrage verwenden, verwenden Sie diese wahrscheinlich tausende Male. Dies wirkt sich auf die Gesamtleistung aus.

Die zweite ist die minimale UPDATEOperation:

UPDATE Users_CCI SET Age=10 WHERE  Id=2

UPDATE Users_Clustered SET Age=10 WHERE  Id=2

Ergebnis : Wie wir sehen können, gibt es Lese-, CPU- und Zeitunterschiede.

Der dritte ist die REBUILDOperation:

USE [StackOverflow2013]
GO
ALTER INDEX [CCI_Users] ON [dbo].[Users_CCI] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
USE [StackOverflow2013]
GO
ALTER INDEX [PK_Users_Clustered_Id] ON [dbo].[Users_Clustered] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE Users_CCI SET Age=10 
UPDATE Users_Clustered SET Age=10 

Wenn ich alle Zeilen für die Indexfragmentierung neu erstelle und aktualisiere, wird ein stärker fragmentierter Clustered Columnstore-Index als der Clustered Index angezeigt. Und ich habe nicht gezeigt, aber der Clustered Columnstore IndexWiederherstellungsprozess erzeugt mehr Transaktionsprotokoll als Clustered Index.

Wie das Dokument sagt

  • Mehr als 10% der Vorgänge in der Tabelle sind Aktualisierungen und Löschungen. Eine große Anzahl von Aktualisierungen und Löschungen führt zu einer Fragmentierung. Die Fragmentierung wirkt sich auf die Komprimierungsraten und die Abfrageleistung aus, bis Sie eine Operation namens "Reorganisieren" ausführen, die alle Daten in den Spaltenspeicher zwingt und die Fragmentierung entfernt. Weitere Informationen finden Sie unter Minimieren der Indexfragmentierung im Columnstore-Index.

Wenn Sie eine kleine Tabelle haben, müssen Sie den Columnstore-Index nicht verwenden.