Nonclustered Columnstore Index (NCCI)

Nonclustered Columnstore Index to indeks kolumnowy zakładany na tabeli wierszowej, przyspieszający zapytania analityczne.

Rola NCCI w tabeli wierszowej

Nonclustered Columnstore Index, skracany do NCCI, jest nieklastrowym indeksem kolumnowym tworzonym na tabeli przechowywanej w układzie wierszowym w Microsoft SQL Server. Tabela bazowa zachowuje dotychczasowy indeks klastrowany albo strukturę sterty, a NCCI powstaje jako dodatkowa struktura przeznaczona przede wszystkim do odczytu analitycznego. Nie zastępuje więc automatycznie indeksu klastrowanego ani nie zmienia podstawowego sposobu przechowywania całej tabeli.

W indeksie wierszowym wartości z różnych kolumn są przechowywane razem w rekordach. Indeks kolumnowy organizuje dane według kolumn, dzięki czemu zapytanie odczytujące tylko kilka pól z szerokiej tabeli może pobrać ograniczony zestaw segmentów danych. SQL Server może dodatkowo używać kompresji kolumnowej oraz przetwarzać większe porcje rekordów w trybie batch mode. Z tego powodu NCCI jest szczególnie przydatny przy agregacjach, skanowaniu dużej części tabeli i analizie danych według zakresów.

Segmenty kolumnowe, delta store i usuwanie wierszy

Wewnętrznie NCCI nie jest pojedynczym prostym plikiem z kolumnami. Dane kolumnowe są organizowane w grupy wierszy, z których każda zawiera osobne segmenty dla poszczególnych kolumn. Segment może zostać pominięty, jeżeli metadane segmentu wskazują, że nie zawiera wartości pasujących do predykatu zapytania. Taka eliminacja segmentów ogranicza liczbę odczytów, ale jej skuteczność zależy od rozkładu danych, użytych warunków oraz jakości kompresji.

Nowe lub modyfikowane wiersze nie zawsze trafiają od razu do skompresowanych segmentów kolumnowych. SQL Server może umieścić je w delta store, czyli pomocniczej strukturze wierszowej używanej dla mniejszych partii zmian. Proces tuple mover przenosi odpowiednio duże, zamknięte grupy z delta store do właściwych segmentów kolumnowych. Wiersze oznaczone do usunięcia są obsługiwane za pomocą delete bitmap, dlatego częste aktualizacje i usunięcia mogą zwiększać koszt późniejszego odczytu oraz wymagać reorganizacji albo przebudowy indeksu.

Wpływ NCCI na zapytania i modyfikacje danych

Optymalizator zapytań SQL Server wybiera NCCI wtedy, gdy przewidywany koszt użycia indeksu kolumnowego jest niższy niż koszt skanowania tabeli lub indeksu wierszowego. Korzyść może pojawić się przy funkcjach agregujących, takich jak COUNT, SUM, AVG, MIN i MAX, przy grupowaniu oraz przy zapytaniach odczytujących niewielką liczbę kolumn z dużego zbioru. Nie oznacza to, że każdy SELECT będzie szybszy. Zapytanie zwracające pojedynczy rekord po selektywnym kluczu często lepiej obsłuży indeks wierszowy.

NCCI zwiększa również koszt operacji INSERT, UPDATE i DELETE, ponieważ zmiana tabeli musi zostać uwzględniona w dodatkowej strukturze indeksowej. Szczególnie istotne jest to w tabelach o dużej częstotliwości modyfikacji. Przy projektowaniu indeksu trzeba przeanalizować równowagę między szybkością raportów a narzutem zapisu, rozmiarem danych, czasem konserwacji oraz dostępną przestrzenią dyskową. Należy też sprawdzić plan wykonania, ponieważ samo utworzenie NCCI nie gwarantuje użycia go przez optymalizator.

Tworzenie i utrzymanie indeksu w SQL Server

Indeks można utworzyć poleceniem w rodzaju CREATE NONCLUSTERED COLUMNSTORE INDEX, wskazując tabelę oraz kolumny objęte indeksem. Dobór kolumn powinien wynikać z rzeczywistych zapytań analitycznych, a nie z zasady dodawania wszystkich pól tabeli. Warto uwzględnić kolumny używane w projekcji, filtrach, grupowaniu i agregacjach, a następnie zweryfikować plan wykonania oraz statystyki obciążenia. Składnia i dostępne opcje zależą od wersji SQL Server, dlatego dokładne ustawienia należy odnieść do używanej edycji i wersji silnika.

Diagnostyka NCCI obejmuje między innymi sprawdzenie fragmentacji, stanu segmentów, udziału wierszy znajdujących się w delta store oraz liczby wierszy oznaczonych jako usunięte. Do oceny można wykorzystać widoki katalogowe i dynamiczne widoki zarządzania udostępniane przez SQL Server, a także rzeczywiste plany wykonania zapytań. Reorganizacja może uporządkować istniejące dane kolumnowe, natomiast przebudowa tworzy indeks ponownie i zwykle wymaga większej ilości zasobów. Błędna konfiguracja, brak miejsca na operację serwisową albo nieodpowiedni dobór kolumn może sprawić, że indeks zwiększy obciążenie systemu bez poprawy czasu raportów.