Klastrowany indeks magazynu kolumn w małych tabelach
Tabele indeksowane magazynu kolumn klastrowych są ogólnie przydatne w przypadku dużych tabel. Idealnie z milionem wierszy. Przydatne także w przypadku zapytań, które wybierają tylko podzbiór dostępnych kolumn w takich tabelach.
Co się stanie, jeśli złamiemy te dwie „zasady” / najlepsze praktyki?
- Podobnie jak w przypadku tabeli zindeksowanej w klastrze, która może przechowywać tylko kilka lub setki tysięcy wierszy.
- I uruchamianie zapytań względem tych klastrowanych tabel magazynu kolumn, w których potrzebne są wszystkie kolumny.
Moje testy nie ujawniają żadnego pogorszenia wydajności w porównaniu z tabelą indeksów klastrowanych przechowywanych w wierszach. Co jest świetne w naszym przypadku.
Czy są jakieś „długoterminowe” efekty łamiące te dwie zasady? Albo jakieś ukryte pułapki, które jeszcze się nie pojawiły?
Kontekst, dlaczego jest to potrzebne: Zaprojektowałem model bazy danych, który będzie używany w wielu instancjach baz danych różnych dostawców. Schemat pozostaje taki sam w każdej bazie danych, ale różni dostawcy mają różną ilość danych. W związku z tym niewielu małych dostawców może mieć małą ilość danych (<1 000 000) w swoich tabelach. Nie mogę sobie pozwolić na utrzymywanie dwóch różnych baz danych dla modelu magazynu wierszy i magazynu kolumn.
Odpowiedzi
Aby @YunusUYANIK zwrócić uwagę na potencjalne błędy związane z projektowaniem schematu tylko z jednej strony, dlaczego nie utworzyć indeksów magazynu wierszy i magazynu kolumn w tabeli odpowiednio do obu scenariuszy? Oczywiście może się zdarzyć, że zindeksujesz te same pola w obie strony, ale główną wadą byłoby po prostu zwiększone wykorzystanie przestrzeni dyskowej, które generalnie jest znacznie mniejszym problemem podczas planowania wydajności.
Będzie to zależeć od Twojego schematu i ilości danych w tabelach dla każdego dostawcy, więc będziesz musiał przetestować, aby upewnić się, że projekt indeksów jest używany w odpowiednich zapytaniach dla różnych ilości danych w oparciu o predykaty dostawcy . W najgorszym przypadku może być konieczne czasami użycie wskazówek dotyczących indeksów, ale wydaje mi się, że jeśli prawidłowo zaprojektujesz oba typy indeksów, jest to mało prawdopodobne.
Indeks magazynu kolumn ma dużą zaletę w zakresie kompresji rozmiaru danych. Ogólnym celem indeksu Columnstore jest szybkie odczytanie zbioru danych dzięki ich kompresji.

CCI to indeks klastrowy magazynu kolumnowego, klastrowany to indeks klastrowy
Indeks magazynu kolumn kompresuje rozmiar danych z 4 MB do 2 MB.
Na wykonanie możemy spojrzeć w dwóch tabelach i trzech częściach.
Pierwsza to minimalna SELECT
operacja:
SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333


SELECT * FROM Users_Clustered WHERE Id=12333


Wynik : jest Columnstore Scan
tutaj i źle oszacowano. Ponadto logiczna różnica odczytu. Możesz powiedzieć, że nie ma to dla Ciebie znaczenia, ale jeśli używasz minimalnego zapytania SELECT, prawdopodobnie użyjesz go tysiące razy. Wpłynie to na całkowitą wydajność.
Druga to minimalna UPDATE
operacja:
UPDATE Users_CCI SET Age=10 WHERE Id=2

UPDATE Users_Clustered SET Age=10 WHERE Id=2

Wynik : widoczne są różnice w odczycie, procesorze i czasie.
Trzecia to REBUILD
operacja:
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
Jeśli odbuduję i zaktualizuję wszystkie wiersze pod kątem fragmentacji indeksu, zobaczę bardziej pofragmentowany indeks Clustered Columnstore niż Clustered Index. I nie pokazałem, ale Clustered Columnstore Index
proces Rebuild generuje dziennik transakcji więcej niż Clustered Index
.


Jak mówi dokument
- Ponad 10% operacji na tabeli to aktualizacje i usunięcia. Duża liczba aktualizacji i usunięć powoduje fragmentację. Fragmentacja wpływa na współczynniki kompresji i wydajność zapytań do czasu uruchomienia operacji o nazwie reorganizacja, która wymusza przeniesienie wszystkich danych do magazynu kolumn i usuwa fragmentację. Aby uzyskać więcej informacji, zobacz Minimalizowanie fragmentacji indeksu w indeksie magazynu kolumn.
Jeśli masz małą tabelę, nie musisz używać indeksu Columnstore.