Strona korzysta z plików cookies w celu realizacji usług i zgodnie z Polityką Plików Cookies.



16.10.2018

Pełna optymalizacja

Quest Foglight for Virtualization Enterprise 8.8 (FVE)
12.10.2018

Linux w chmurze Azure

SUSE Linux Enterprise 15 / Microsoft Azure
09.10.2018

Nowe antywirusy

Bitdefender 2019
03.10.2018

Niezawodny tandem

Veeam Hyper-Availability Platform i Cisco HyperFlex
26.09.2018

Zarządzane z chmury

NETGEAR GC752X(P)
21.09.2018

Kolor razy 6

Kyocera ECOSYS i TASKalfa
18.09.2018

Na ataki piątej generacji

Check Point 23900
14.09.2018

UHD dla pro

Samsung UJ59
11.09.2018

Ochrona dla firm

ESET Security Management Center

Indeksy kolumnowe – wskazówki i zastosowania

Data publikacji: 26-09-2018 Autor: Marcin Szeliga
Porównanie wydajności...

W pierwszej części artykułu przedstawione zostały budowa i działanie indeksów kolumnowych na przykładzie zaimplementowanej w serwerze SQL Server technologii xVelocity. W tej części przyjrzymy się praktycznym zastosowaniom indeksów kolumnowych, w tym sposobom osiągnięcia ich maksymalnej wydajności.

 

Typowym wąskim gardłem wydajności serwerów baz danych są dyski – indeksy kolumnowe rozwiązują ten problem. Po pierwsze, skompresowane dane zajmują wielokrotnie (około 10-krotnie) mniej miejsca na dysku, co pozwala ograniczyć liczbę niezbędnych do ich wczytania do pamięci operacji I/O.

Po drugie, struktura indeksów kolumnowych umożliwia odczytywanie tylko potrzebnych do wykonania zapytania kolumn i segmentów, w dodatku za pomocą szesnastokrotnie większych operacji odczytu. SQL Server odczytuje dane w 8-kilobajtowych blokach nazywanych stronami. Ponieważ pojedyncza, duża operacja odczytu jest znacznie szybsza od wielu małych, SQL Server w miarę możliwości przeprowadza odczyty z wyprzedzeniem, czyli jednorazowo wczytuje do pamięci do 64 sąsiednich stron. Pozwala to na jednorazowe wczytanie 512 KB danych. Tymczasem do odczytu danych z indeksów kolumnowych wykorzystywane są operacje I/O o wielkości do 8 MB.

Po trzecie, skompresowane dane zajmują mniej miejsca w pamięci operacyjnej, co pozwala serwerowi na buforowanie ich większej ilości i przekłada się na zmniejszenie liczby odczytów z dysku.
 
Po ograniczeniu wpływu dysków na wydajność kolejnym wąskim gardłem okazuje się procesor. Tutaj rozwiązaniem okazał się tryb blokowego przetwarzania danych. Przekonajmy się, jak duży ma on wpływ na wydajność zapytań.

Zaczniemy od wykonania przykładowych zapytań na tabeli lineitem (sposób utworzenia używanych do pomiaru wydajności tabel został przedstawiony w pierwszej części artykułu) skompresowanej w trybie PAGE. Na początek włączamy statystyki odczytu czasu oraz czyścimy bufor danych:

DBCC DROPCLEANBUFFERS
SET STATISTICS TIME, IO ON

Następnie wykonujemy dwa poniższe zapytania: pierwsze symuluje proste zapytanie analityczne odwołujące się wyłącznie do tabeli faktów, drugie zawiera podzapytanie odwołujące się do kilku powiązanych z nią tabel wymiarów.

SELECT

l_returnflag,
l_linestatus,
SUM(l_quantity) as sum_qty,
SUM(l_extendedprice) as sum_base_price,
SUM(l_extendedprice * (1 – l_discount)) as sum_disc_price,
SUM(l_extendedprice * (1 – l_discount) * (1 + l_tax)) as sum_charge,
AVG(l_quantity) as avg_qty,
AVG(l_extendedprice) as avg_price,
AVG(l_discount) as avg_disc,
COUNT(*) as count_order

FROM lineitem
WHERE l_shipdate <= ‘1998-12-01’
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

Wykonanie prostego zapytania wymagało odczytania z wyprzedzeniem 52 214 stron (417 712 KB danych) i zajęło 2,6 sekundy. Wykonanie złożonego zapytania wymagało dodatkowo odczytania znacznie mniejszych tabel wymiarów i również zajęło 2,5 sekundy, przy czym czas zajęcia procesorów wyniósł 15,5 sekundy (zapytanie wykonywane było wielowątkowo).

Po założeniu dla tabeli lineitem zgrupowanego indeksu kolumnowego i włączeniu kompresji archiwalnej wykonanie tych samych zapytań:

 

  • ƒƒw przypadku prostego zapytania zajęło 0,5 sekundy i wymagało wykonania 4600 operacji odczytu; ƒƒ
  • w przypadku złożonego zapytania zajęło 0,8 sekundy, a czas zajęcia procesorów spadł do 1,6 sekundy.


Jeżeli jednak dla drugiego zapytania wyłączymy tryb blokowego przetwarzania danych, dopisując na jego końcu dyrektywę OPTION (querytraceon 9453), przekonamy się, że czas wykonania tego samego zapytania w trybie blokowym był kilkukrotnie krótszy, jego koszt był ponad 3-krotnie niższy, a czas zajęcia procesora ponad 10-krotnie mniejszy (rysunek).

> PAMIĘĆ RAM

Indeksy kolumnowe do optymalnego działania wymagają wystarczająco dużo pamięci operacyjnej. Jeżeli będzie jej brakowało podczas tworzenia indeksu, dane zostaną podzielone na większą niż optymalna liczbę grup wierszy, a jeśli zabraknie pamięci podczas odczytywania danych z indeksu, do ich przetworzenia użyta zostanie systemowa baza tempdb.

 

Większe grupy wierszy poprawiają wydajność indeksów kolumnowych. Im są one większe, tym większe utworzone na ich podstawie segmenty – ponieważ dane kompresowane są w segmentach, większe segmenty oznaczają większy współczynnik kompresji danych. Z tego powodu, jeżeli serwer SQL nie będzie dysponował wystarczającą ilością pamięci podczas tworzenia czy przebudowywania indeksów kolumnowych, automatycznie zmniejszy rozmiar grup wierszy.


[...]

Pracownik naukowy Wyższej Szkoły Bankowej w Poznaniu Wydział Zamiejscowy w Chorzowie; jest autorem książek poświęconych analizie danych i posiada tytuł Microsoft Most Valuable Professional.

Pełna treść artykułu jest dostępna w papierowym wydaniu pisma.

.

Transmisje online zapewnia: StreamOnline

All rights reserved © 2013 Presscom / Miesięcznik "IT Professional"