[PL] Aurora Get Noticed 2016 SQL

Clustered / NonClustered, czyli o indeksach w SQL Server

Witam serdecznie,

po zmaganiach z architekturą oraz logowaniem użytkownika, zbliżamy się nieubłaganie do implementowania pierwszych widoków aplikacji. Zanim to jednak nastąpi warto omówić jeszcze jeden temat, który nie będzie ściśle związany z logiką biznesową. Projektując wszelkiego rodzaju systemy informatyczne zdarza się (niestety często), że proces „tworzenia” bazy danych kończy się po wykreowaniu odpowiedniej jej struktury. Model fizyczny jest spójny, generujemy zapytania SQL, execute i gotowe. Czy coś jest wobec tego nie tak? Przecież po kliknięciu w przycisk na widoku dane zostają nam zwrócone z bazy danych, a w dodatku są one poprawne. Oczywiście, jest to pawda. Problem pojawia się w momencie kiedy o 2 w nocy rozwścieczony szef dzwoni do nas wykrzykując „mamy teraz wielki ruch na stronie, a klienci dostają z serwera timeout! Baza nie wyrabia!„.

Aby bardziej wprowadzić Was w problematykę rozpocznijmy od krótkiego przykładu. Wyobraźmy sobie, że pewien bank X posiada w swojej bazie danych tabelę firm, które zarejestrowały się u nich w systemie. Uproszczona wersja mogłaby wyglądać następująco:

 

 

sample1

 

Mamy teraz taki o to scenariusz. Pracownik banku chciałby posiadać wgląd w dane firm, które potwierdziły proces rejestracji. Aby zrealizować to zadanie, do SQL Server najprawdopodobniej zostanie wysłane następujące zapytanie:

 


SELECT * FROM Companies

WHERE IsVeryfied = 1

 

O niebezpieczeństwie gwiazdki na pewno też kiedyś napiszę, ale wróćmy do tematu. Spójrzmy jaki rezultat został wygenerowany:

 

sample2

 

Wszystko wygląda w jak najlepszym porządku. Przeanalizujmy jednak sposób w jaki polecenie zostało wykonane. W tym celu włączymy tzw. plan zapytania, czyli graficzną reprezentację kroków, które system bazy danych musiał poczynić, aby zwrócić nam rezultat. Aby to zrobić wystarczy skrót ctrl + M. Wykonajmy zatem ponownie kod SQL:

 

sample3

 

Jak widać pierwszym krokiem, który poczynił optymalizator zapytania było wykonanie operacji scan. Polega ona na przejściu przez wszystkie rekordy zawarte w naszej tabeli. Z punkty widzenia wydajności wydaje się to rozwiązaniem niedopuszczalnym. Z drugiej strony użycie w tym przypadku scan było jak najbardziej zasadne. Skąd optymalizator miał wiedzieć, które rekordy posiadają flagę IsVerified  równą 1, skoro dane nie zostały uprzednio posortowane? Aby to sprawdzić jedynym wyjściem było konsekwentne sprawdzanie kolejnych wierszy. W skali 5 rekordów problem wydaje się nie istnieć, ale co w przypadku gdy nasza tabela firm „urośnie” do rozmiarów 1 000 000 wierszy? Nawet gdyby każda firma nie potwierdziła rejestracji, optymalizator zapytań byłby zmuszony przejrzeć wszystkie rekordy, ponieważ nie miałby pewności czy na pozycji np. 999 999 nie znajduje się ta jedyna, potwierdzona. Oczywiście SQL poradzi sobie i z takim problemem, ale czas trwania transakcji ulegnie znacznemu wydłużeniu.Skoro wiemy już co nieco o problemie, czas poznać sposób, który pomoże nam go rozwiązać. Są nim tytułowe indeksy. Czym są?

 

Indeksem nazywamy specjalną strukturę w bazie danych, której celem jest optymalizacja zapytań bazodanowych SELECT

 

Dziś zajmiemy się dwoma podstawowymi rodzajami indeksów tj. Clustered (zgrupowany) oraz NonClustered (niezgrupowany).

 

Clustered index

Indeks zgrupowany przetrzymuje fizycznie w swojej strukturze (drzewiastej) posortowane po kolumnie/kolumnach, rekordy w danej tabeli. Jest on mówiąc kolokwialnie nierozerwalnie połączony z tabelą, na którą został założony. Możemy to porównać do książki telefonicznej. Wszystkie numery telefonów są tam posortowane po nazwisku a następnie po imieniu odbiorcy. Nie istnieje zatem fizyczna możliwość na rozdzielenie kontaktów i porządku alfabetycznego. Implikacją tego podejścia jest to, że każda tabela może posiadać maksymalnie jeden indeks zgrupowany. Dlaczego? Ponieważ istnieje tylko jedna możliwość posortowania słów alfabetycznie, lub np. liczb rosnąco.  Jeżeli komuś uda się ta sztuka, niech biegnie po nobla 😀 To wszystko wymaga jednak pewnej graficznej prezentacji, która pokarze w jaki sposób odbywa się wyszukiwanie danych przy pomocy indeksu zgrupowanego. Załóżmy, że w tabeli kontaktów chcielibyśmy pobrać wszystkich Dariuszów:

sample4

 

Na poziomie korzenia (nie lubię spolszczenia stąd root na diagramie) optymalizator bazodanowy sprawdzi najpierw czy ‚DARIUSZ’ występuje po imieniu ‚ARKADIUSZ’. Jest to prawda dlatego w drugim kroku dokona podobnego porównania, tym razem z imieniem ‚MARCIN’. Ponieważ jednak warunek nie zostanie spełniony, optymalizator cofnie się do ostatniego pola po czym przejdzie do kolejnego poziomu drzewa (ścieżka została zaznaczona kolorem zielonym). Algorytm na wszystkich poziomach NO-LEAF działa identycznie. W momencie gdy optymalizator zapytania przejdzie na poziom liści, przechodzi kolejno przez rekordy dopóki nie natrafi na rezultat(y). Warto dodać, że cały poziom liści jest jak wspomniałem, fizycznymi wierszami w bazie danych, tyle że posortowanymi po kolumnie klastrowania. Przedstawiona operacja jest nazywana seek. Patrząc na to „życiowo” wydaje się to naturalne podejście i jest niczym innym jak własnie przeszukiwaniem posortowanej książki telefonicznej.

 

NonClustered index

Indeks niezgrupowany różni się od poprzednika tym, że na poziomie liści nie są przetrzymywane fizyczne dane tabeli, a jedynie wskaźniki do nich. Implikację wynikające  tego rozwiązania są trzy. Po pierwsze indeks niezgrupowany nie jest integralną częścią tabeli. Po drugie dane w tabeli nie muszą być posortowane, ponieważ posortowany jest sam indeks, a wskaźniki i tak wskażą odpowiednie wiersze. Po trzecie, takich indeksów możemy mieć wiele (dokładniej 999). Bardziej ludzkim wyjaśnieniem będzie przyrównanie indeksu niezgrupowanego do spisu treści w książce. Spis ten jest zawsze posortowany i wskazuje kolejne rozdziały. Co jednak się stanie jeśli go wyrwiemy? Zupełnie nic. Mimo tego iż nie jest on teraz integralną częścią książki, z łatwością możemy odczytać numer strony do której mamy się udać, aby odnaleźć dany rozdział. Co więcej, na podstawie własnych kryteriów (nie koniecznie numeru strony), możemy tworzyć n spisów treści z których każdy będzie miał własne kryterium (pole klastrowania). W celu zobrazowania, posłużmy się przykładem z wyszukiwaniem Dariuszów:

 

sample5

 

Przykładu nie będę omawiał, ponieważ jak widzicie jest to praktycznie kopiuj-wklej z poprzedniego diagramu. Warto zwrócić uwagę na wspomniany poziom liści. Kiedy optymalizator bazodanowy przechodzi po kolejnych parach klucz-wartość i natrafi na „interesujące” dane, używa wskaźnika w celu wydobycia wszelkich wymaganych do rezultatu danych. Ta operacja również nazywana jest seek.

 

Wróćmy zatem do naszego przykładu z bankiem. Aby sprawdzić czy faktycznie to co napisałem się zgadza, załóżmy na kolumnę IsVerified indeks zgrupowany. Polecenie SQL wygląda następująco:

 


CREATE CLUSTERED INDEX IX_Companies_IsVerified ON Companies(IsVerified)

 

Nazwa indeksu nie jest przypadkowa, a jest to konwencja nazewnictwa, która wygląda następująco

 

<typ_indeksu>_<nazwa_tabeli>_<pola klastrowania>

 

Po założeniu indeksu wykonajmy zapytanie SQL zwracające wszystkie firmy:

sample6

Widzimy pewną różnicę w porównaniu do pierwotnego rezultatu. Wiersze zostały posortowane po wartości kolumny IsVerified. Wykonajmy zatem zapytanie zwracające firmy, które potwierdziły proces rejestracji.

 

sample2

 

Plan zapytania wygląda tak:

 

sample7

 

Rezultat jest identyczny, jednak widać wyraźną różnicę w planie zapytania. Zamiast operacji scan, optymalizator skorzystał z indeksu używając seek.

Na dziś to cała teoria jaką przygotowałem. Mam nadzieję, że nie brzmiało to nadto skomplikowanie. W projekcie postaram się niedługo wprowadzić indeksy. Naturalnymi „kandydatami” do indeksowania są kolumny IsActive, które służą do soft delete-u wierszy, ponieważ każde zapytanie do bazy zawiera warunek sprawdzający czy wartość tej flagi jest równa 1. Reszta wyniknie kolejnych funkcjonalności, które zaimplementujemy już wkrótce 🙂 Przypominam, że cały kod projektu jest dla Was dostępny na githubie. Gorąco zachęcam również od śledzenia mojego twittera, gdzie pojawiają się najnowsze wpisy. Jeżeli wpis Wam się podobał możecie także dać kciuka na facebook-u 🙂

Do następnego !

  • A czemu IsActive jest lepszym kandydatem na indeks klastrowany od klucza głównego tabeli?

    • Dariusz Pawlukiewicz

      Dawid,
      mój błąd, miało być „do indeksowania”. Poprawione 😀

      • ja

        A selektywność?

        • Dariusz Pawlukiewicz

          ja (:D),
          nie pamiętam dokładnie „reguły”. Czy jeżeli selektywność nie osiągnie pewnego poziomu, to SQL nie skorzysta na pewno z indeksu, czy w przypadku takiego IsActive może uznać za przydatne ?

  • Pingback: dotnetomaniak.pl()

  • RK

    Ciekawy temat i na dodatek napisany bardzo przystępnym językiem :).
    Jeśli mogę się czegoś czepnąć to:
    „Po trzecie, takich indeksów możemy mieć wiele (dokładniej 255)” – hmm… nie jest przypadkiem tak, że w nowszych wersjach (chyba nawet od 2008) podnieśli ten limit do 999 per table? W starszych również mi się nie zgadza, bo z tego co pamiętam to było 250 (chociaż z moją pamięcią różnie bywa 🙂 )

    Dodałbym również jakąś informacje o wydajności zapisu dla indeksowanych danych, bo z wpisu bije taki indeksowy optymizm, że niejeden początkujący użytkownik MS SQL ma ochotę pozakładać indeksy gdzie tylko się da 🙂

    • Dariusz Pawlukiewicz

      RK,
      dzięki za wytknięcie babola, już poprawiłem 🙂 Co do uzupełnienia informacji, to może faktycznie powinienem coś dopisać, bo można odebrać jako „zakładamy na każdej kolumnie i super”. Z tego co wiem praktyką jest zakładanie indeksów na klucze obce plus kolumny często pojawiające się w SELECT. Minus to faktycznie wydajność zapisu danych, które podlegają indeksowaniu oraz pojemność dyskowa, ponieważ te struktury też swoje ważą. Postaram się dodać krótki paragraf wieczorem 🙂

      • rbl

        Możesz wspomnieć jeszcze o indeksach wielokolumnowych oraz dodawaniu pól z klauzulą INCLUDED.

        • grzelix

          Oraz indeksach z filtrowanych (WHERE)

          • Dariusz Pawlukiewicz

            rbl, grzelix,
            dzięki za podpowiedź!

  • Pingback: utilaje de inghetata()

  • Pingback: official website()

  • Pingback: Aws coehumancoehuman Diyala()

  • Pingback: iraqi Aws law()

  • Pingback: informática()

  • Pingback: Appliance repair North York()

  • Pingback: Best forex robot()

  • Pingback: appliance repair Richmond Hill()

  • Pingback: amazon guaranteed consultants()

  • Pingback: Digital Marketing()

  • Pingback: Sicherheitsschuhe()

  • Pingback: home1()

  • Pingback: ORM()

  • Pingback: carpet cleaning near london colney()

  • Pingback: Array Questions()

  • Pingback: How to cure premature ejaculation()

  • Pingback: Food Blog()

  • Pingback: forex signals()

  • Pingback: Caco-2 permeability assay()

  • Pingback: Mechanistic PK studies()