Columnstore Index - nietypowy indeks jako rozwiązanie typowych problemów wydajności w dostępie do danych  

Autor: Bartłomiej Graczyk

Opublikowano: 2012-09-06

Wprowadzenie

Każdy administrator baz danych (jak i programista) dokładnie wie, czym kończy się brak właściwych indeksów w bazie danych. Jednocześnie, warto mieć świadomość, że standardowy indeks, nawet najlepiej dobrany do zapytań (jeśli chodzi o wydajność realizacji zapytań), nie jest w stanie sprostać wymaganiom przy milionach wierszy w tabeli. Dość popularnym problemem jest kwestia raportowania w oparciu o źródło danych, jakim jest hurtownia danych. Czy raportowanie bezpośrednio z hurtowni danych może być porównywalne wydajnościowo z raportowaniem z baz OLAP? W tym artykule można znaleźć informacje, które w pewnym stopniu są rozwiązaniem wspomnianych problemów.

Nowy indeks, nowa struktura

Do tej pory, indeksy, oparte o strukturę B-drzewa, w której dane indeksowe lokowane są na poszczególnych stronach, wykorzystywane były standardowo. Zatem, podstawową jednostką struktury indeksu jest strona, która przechowuje dane w postaci wierszy. Dane odczytywane są podczas odczytu mimowolnie z innych kolumn, pomimo iż nie są one de facto potrzebne. Odczyt większej niż potrzeba ilości danych zwiększa ilość operacji I/O, jednocześnie wzrasta zapotrzebowanie na pamięć RAM.

Tymczasem, nowa struktura indeksu pozwala znacznie ograniczyć ilość operacji I/O. Jest to możliwe dzięki zmianie sposobu zapisu danych indeksowych. Dane z poszczególnych kolumn zapisywane są na oddzielnych stronach, dzięki temu, w momencie odczytu danych z indeksów, pobierane są jedynie kolumny, które faktycznie określone zostały w zapytaniu. Porównanie struktur indeksów widoczne jest na Rys. 1.

Rys. 1. Porównanie struktur "row store" oraz "column store".

Dodatkowo, w przypadku, gdy dane z określonej kolumny są wartościami powtarzającymi się dla kolejnych wierszy z tabeli, znaczącą rolę odgrywa możliwość kompresji danych na stronie (mechanizm znany z poprzednich wersji SQL Server). W dalszej części artykułu porównane zostaną właśnie takie przypadki rozkładu danych.

Tworzenie indeksu wykorzystującego column store jest stosunkowo proste i raczej nie odbiega niczym od tworzenia standardowego indeksu w trybie row store:

CREATE COLUMNSTORE INDEX [nazwa_indeksu] ON [nazwa_tabeli] ([kolumny])

Niestety, aby skorzystać z nowego typu indeksu, niezbędna będzie instalacja SQL Server 2012 w edycji Enterprise.

Zalety, ale również ograniczenia

Można zatem zaryzykować stwierdzenie, że nowy typ indeksu jest rozwiązaniem pozbawionym wad. Faktycznie, stosując nowy typ indeksu, zapytania zyskują na wydajności. Wykorzystanie zasobów (np. RAM) jest mniejsze – jednakże, należy pamiętać również o pewnych cechach, które nie są typowymi wadami, a jedynie ograniczeniami nowego rozwiązania:

• brak możliwości utworzenia CLUSTERED INDEX w trybie column store,
• brak opcji included column dla indeksu w trybie column store,
• indeks w trybie column store nie jest dostępny dla kolumn wyliczeniowych,
• możliwość utworzenia tylko 1 indeksu w trybie column store dla danej tabeli,
• indeks może składać się maksymalnie z 1024 kolumn,
• indeks nie może być unikalny,
• brak możliwości zmiany indeksu (dodanie, usunięcie kolumn), wymagane jest usunięcie i utworzenie indeksu ponownie,
• brak możliwości określenia porządku sortowania danych (ASC lub DESC),
• na koniec, chyba najważniejsze z ograniczeń – tabele, na których założono indeks w trybie column store, stają się tabelami read-only. Zatem, po nałożeniu tego indeksu z tabeli, nie można usuwać, dodawać ani modyfikować rekordów. Próba takich operacji skutkuje odpowiednim błędem.

W przypadku konieczności dodania danych do tabeli, należy skorzystać z funkcji wyłączenia indeksuALTER INDEX [nazwa] ON [tabela] DISABLE, a po załadowaniu danych, indeks należy ponownie włączyć, korzystając z opcji ALTER INDEX [nazwa] ON [tabela] REBUILD.

Column store index – aspekty praktyczne

W celu weryfikacji cech indeksu, opisanych w pierwszej części artykułu, poniżej znajduje się prosty skrypt, umożliwiający przeprowadzenie testu rozwiązania. Utworzona zostanie, na potrzeby prezentacji, tabela na podstawie tabeli z bazy AdventureWorks, zawierającej dane sprzedażowe. Aby obserwacje były bardziej miarodajne, dane w tabeli testowej zostaną zwielokrotnione. Testy prowadzone są na bazie AdventureWorks2012, którą można pobrać ze stron CodePlex:

USE AdventureWorks2012
 GO
 -- Utworzenie tabeli testowej
 CREATE TABLE [dbo].[SalesHistory](
 [SalesOrderID] [int] NOT NULL,
 [SalesOrderDetailID] [int] NOT NULL,
 [CarrierTrackingNumber] [nvarchar](25) NULL,
 [OrderQty] [smallint] NOT NULL,
 [ProductID] [int] NOT NULL,
 [SpecialOfferID] [int] NOT NULL,
 [UnitPrice] [money] NOT NULL,
 [UnitPriceDiscount] [money] NOT NULL,
 [LineTotal] [numeric](38, 6) NOT NULL,
 [rowguid] [uniqueidentifier] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
 ) ON [PRIMARY]
 GO

 -- Utworzenie indeksu typu clustered
 CREATE CLUSTERED INDEX [IDXC_SalesHistory] ON [dbo].[SalesHistory]
 ( [SalesOrderDetailID])
 GO

 -- Zasilenie tabeli danymi testowymi 
 INSERT INTO [dbo].[SalesHistory]
 SELECT SOD.*
 FROM Sales.SalesOrderDetail SOD
 GO 100   --- wielorotne wykonanie, aby zwiększyć ilość danych do testów
 




 
----Porównanie wydajności operacji odczytu danych z tabeli

 USE AdventureWorks2012
 GO

 SET STATISTICS IO ON
 GO

 -- Pobranie danych z wykrozystaniem indeksu typu clustered index
 SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
 SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
 FROM [dbo].[SalesHistory]
 GROUP BY ProductID
 ORDER BY ProductID
 GO

 
 --Utworzenie indeksu column store
 CREATE NONCLUSTERED COLUMNSTORE INDEX [IDXCS_SalesHistory_ColumnStore]
 ON [SalesHistory]
 (UnitPrice, OrderQty, ProductID)
 GO


 -- Pobranie danych z wykorzystaniem indeksu column store
 SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
 SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
 FROM [dbo].[SalesHistory]
 GROUP BY ProductID
 ORDER BY ProductID
 GO

 -- Kasowanie obiektów bazy
 DROP INDEX [IDXCS_SalesHistory_ColumnStore] ON [dbo].[SalesHistory]
 GO
 TRUNCATE TABLE dbo.SalesHistory
 GO
 DROP TABLE dbo.SalesHistory
 GO

Uruchomienie powyższego skryptu (oczywiście krok po kroku) pozwala zaobserwować kilka ciekawych rzeczy. Pierwsze uruchomienie zapytania, pobierającego dane (po utworzeniu indeksu clustered), daje efekty widoczne na Rys. 2. – dość duża ilość operacji odczytu, która w przypadku indeksu column store jest kilkakrotnie mniejsza – Rys. 3.

Rys. 2. Wynik operacji I/O przy wykorzystaniu standardowego indeksu.

Rys. 3. Wynik operacji I/O przy wykorzystaniu indeksu column store.

Dodatkowo, warto również zwrócić uwagę, tak jak zostało to opisane powyżej, że próba utworzenia kolejnego indeksu w trybie column store dla tej samej tabeli kończy się niepowodzeniem, co zostało zaprezentowane na Rys. 4.

-- Proba utworzenia drugiego indeksu column store
 CREATE NONCLUSTERED COLUMNSTORE INDEX [IDXCS_SalesHistory_ColumnStore_Index_2]
 ON [SalesHistory]
 (UnitPrice, OrderQty, ProductID)
 GO

Rys. 4. Błąd tworzenia indeksu column store dla tabeli, dla której istnieje już taki indeks.

Dodatkowo, próba wykonania operacji DML (Insert,Update,Delete), w przypadku tabeli z indeksem typu column store, również kończy się błędem, zgodnie z oczekiwaniem:

Rys. 5. Błąd operacji DML na tabeli z indeksem column store.

Podsumowanie

Indeksy column store to długo oczekiwana funkcjonalność, zwłaszcza w obszarze hurtowni danych. Zwiększenie wydajności realizacji zapytań na olbrzymich tabelach faktów pozwala zarówno skrócić czas procesowania kostek OLAP, jak również wykorzystać hurtownię danych, jako pełnowartościowe źródło danych dla raportów, bardzo często skracając również czas ich przygotowania i późniejszego rozwoju. Funkcjonalność dostępna jest co prawda jedynie w edycji enterprise, co jednak nie stanowi większego problemu, jako że większość rozwiązań hurtowni danych wykorzystuje właśnie taką edycję platformy bazodanowej Microsoft SQL Server 2012.