Klastrowany indeks magazynu kolumn w małych tabelach

Nov 23 2020

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?

  1. Podobnie jak w przypadku tabeli zindeksowanej w klastrze, która może przechowywać tylko kilka lub setki tysięcy wierszy.
  2. 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

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

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.

4 YunusUYANIK Nov 23 2020 at 22:16

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 SELECToperacja:

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

SELECT * FROM Users_Clustered WHERE Id=12333

Wynik : jest Columnstore Scantutaj 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 UPDATEoperacja:

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 REBUILDoperacja:

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 Indexproces 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.