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



23.04.2019

Optymalizacja zużycia chmury

HPE GreenLake Hybrid Cloud
23.04.2019

Zarządzanie wydajnością

VMware vRealize Operations 7.5
19.04.2019

Technologie open source

SUSECON 2019
19.04.2019

Wyjątkowo małe

OKI seria C800
19.04.2019

Łatwy montaż

Rittal AX i KX
18.04.2019

Technologie wideo

Avaya IX Collaboration Unit
18.04.2019

Krótki rzut

Optoma W318STe i X318STe
18.04.2019

Do mobilnej pracy

Jabra Evolve 65e
27.03.2019

Pożegnanie z systemem Windows...

System operacyjny Windows 7 wciąż cieszy się dużą popularnością wśród użytkowników...

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 © 2019 Presscom / Miesięcznik "IT Professional"