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



01.12.2022

Wyższy poziom programowania

Progress oferuje nowe narzędzia programistyczne: Progress Telerik, Progress Kendo UI i...
01.12.2022

Łączność w podróży

VMware SD-WAN VMware zaprezentował rozwiązanie SD-WAN nowej generacji, w tym nowego...
01.12.2022

Bezpieczne e-maile

Nowa aplikacja firmy Cypherdog Security Inc. umożliwia bezpieczną wymianę maili i...
01.12.2022

Pierwszy w branży

Schneider Electric wprowadza na rynek APC Smart-UPS Ultra. To pierwszy w branży...
01.12.2022

Przełączniki 10G dla MŚP

Nowe urządzenia to przełączniki 10G kompatybilne z systemem Omada SDN.
01.12.2022

Zarządzanie danymi

Firma Synology wprowadziła na rynek czterokieszeniowy DiskStation DS923+.
01.12.2022

Nowatorski system chłodzenia

OVHcloud zaprezentował nową, autorską technologię hybrydowego zanurzeniowego chłodzenia...
01.12.2022

Linia smart routerów

D-Link zaprezentował najnowszą rodzinę routerów Smart Wi-Fi z algorytmami sztucznej...
04.11.2022

Nowa platforma Red Hat

Nowa platforma Red Hat Enterprise Linux (RHEL) w wersjach 8.7 i 9.1 Beta obsługuje...

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.

prenumerata Numer niedostępny Spis treści

.

Transmisje online zapewnia: StreamOnline

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