CREATE COLUMNSTORE INDEX

Polecenie CREATE COLUMNSTORE INDEX tworzy nieklastrowany indeks w formacie kolumnowym dla wybranych kolumn tabeli.

CREATE COLUMNSTORE INDEX w SQL Server

CREATE COLUMNSTORE INDEX jest instrukcją języka Transact-SQL używaną w aparacie Microsoft SQL Server do utworzenia indeksu przechowującego dane kolumnowo. Wariant NONCLUSTERED COLUMNSTORE INDEX tworzy dodatkową strukturę indeksową dla wskazanych kolumn istniejącej tabeli. Tabela zachowuje przy tym swój podstawowy sposób przechowywania danych, a indeks kolumnowy służy jako alternatywna reprezentacja wybieranych wartości. Dzięki temu jedna tabela może obsługiwać zarówno typowe operacje na pojedynczych wierszach, jak i zapytania analityczne odczytujące duże zakresy danych.

Przykładowa instrukcja ma postać CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sprzedaz_Analityka ON dbo.Sprzedaz (DataSprzedazy, ProduktID, Ilosc, Wartosc). Po słowie ON podaje się nazwę tabeli, a w nawiasie listę kolumn objętych indeksem. Nazwa indeksu musi być unikatowa w zakresie tabeli. W przeciwieństwie do indeksu klastrowanego kolumnowego wariant nieklastrowany nie zmienia podstawowego układu tabeli i może zostać dodany do tabeli posiadającej już indeks klastrowany, klucz główny albo inne indeksy. Dobór kolumn powinien wynikać z rzeczywistych zapytań, ponieważ każda dodatkowa struktura zwiększa wymagania dotyczące miejsca i obsługi zmian danych.

Segmenty kolumnowe, grupy wierszy i kompresja

SQL Server organizuje dane indeksu columnstore w grupy wierszy, czyli rowgroups, a wartości w poszczególnych kolumnach zapisuje w odrębnych segmentach kolumnowych. Taki układ pozwala odczytywać tylko kolumny potrzebne danemu zapytaniu, zamiast pobierać całą szerokość każdego wiersza. Segment może zawierać metadane opisujące zakres wartości, dlatego silnik ma możliwość pominięcia segmentów, które nie mogą zawierać rekordów spełniających predykat zapytania. Mechanizm ten nosi nazwę segment elimination i jest szczególnie użyteczny przy filtrowaniu po kolumnach o uporządkowanych lub względnie skupionych wartościach.

Indeksy columnstore wykorzystują kompresję zoptymalizowaną dla danych kolumnowych. Skuteczność kompresji zależy między innymi od typu danych, powtarzalności wartości, liczby kolumn oraz sposobu napływania rekordów. Zapytania agregujące, takie jak obliczanie sum, średnich lub liczby rekordów dla dużych zbiorów, mogą korzystać z przetwarzania paczkowego, określanego jako batch mode. W tym trybie SQL Server przetwarza grupy wartości zamiast wykonywać tę samą operację osobno dla każdego wiersza. Nie oznacza to jednak, że każde zapytanie automatycznie przyspieszy po utworzeniu indeksu. Optymalizator musi uznać indeks za korzystny, a plan wykonania zależy od predykatów, statystyk, selektywności filtrów i rozmiaru danych.

Wpływ indeksu nieklastrowanego na modyfikacje tabeli

Nieklastrowany indeks columnstore może współistnieć z operacjami INSERT, UPDATE i DELETE. Każda modyfikacja danych objętych indeksem wymaga jednak utrzymania dodatkowej struktury. Przy częstych zmianach pojedynczych rekordów SQL Server może wykorzystywać deltastore, czyli obszar przejściowy przeznaczony dla wierszy, które nie zostały jeszcze umieszczone w skompresowanych rowgroups. Rekordy usuwane mogą być oznaczane jako usunięte, a późniejsze operacje porządkowania i kompresji ograniczają narzut wynikający z takiego sposobu obsługi zmian.

Przed utworzeniem indeksu należy sprawdzić charakter tabeli oraz zapytania korzystające z jej danych. Konstrukcja jest zwykle bardziej uzasadniona wtedy, gdy zapytania odczytują wiele rekordów i wykonują agregacje, grupowanie lub skanowanie wybranych kolumn. Jeżeli tabela jest mała albo dominuje w niej obsługa pojedynczych rekordów na podstawie klucza, klasyczny indeks wierszowy może być lepiej dopasowany. W praktyce należy porównać plany wykonania przed i po zmianie, obserwować czas odczytu, koszt operacji modyfikujących oraz rozmiar indeksu. Sam fakt poprawnego wykonania polecenia nie jest dowodem, że indeks poprawił działanie aplikacji.

Weryfikacja definicji i planu wykonania w SQL Server

Po utworzeniu indeksu można sprawdzić jego definicję oraz właściwości za pomocą widoków katalogowych SQL Server, między innymi sys.indexes, sys.index_columns i sys.columns. Widoki te pozwalają potwierdzić nazwę indeksu, tabelę nadrzędną, typ struktury oraz listę kolumn. Do oceny wykorzystania indeksu służy plan wykonania zapytania. W planie można sprawdzić, czy optymalizator wybrał skan indeksu columnstore, czy zastosował przetwarzanie paczkowe oraz czy wystąpiły operacje odczytu z innej struktury.

Diagnostyka powinna obejmować również statystyki i aktualność danych. Zmienny rozkład wartości może spowodować, że plan przygotowany dla wcześniejszego stanu tabeli przestanie być korzystny. Warto analizować rzeczywiste zapytania, a nie tylko test wykonany na niewielkim zbiorze. Należy także zweryfikować uprawnienia wymagane do zmiany schematu tabeli oraz wpływ budowania indeksu na zasoby serwera, ponieważ tworzenie i przebudowa dużej struktury może zużywać procesor, pamięć, przestrzeń dyskową oraz czasowo obciążać operacje wykonywane równolegle. W tym zastosowaniu poprawność polecenia CREATE COLUMNSTORE INDEX oznacza nie tylko prawidłową składnię, lecz także zgodność indeksu z modelem zapytań i sposobem modyfikowania konkretnej tabeli.