Indeksy pokrywające

Na temat indeksów pokrywających napisano już wiele ale niekiedy warto zrobić sobie małą powtórkę wiedzy.

Tak więc, indeks pokrywając to nic innego jak pokrycie zapytania indeksem który zawiera wszystkie kolumny (w tym część tylko dołączona) z żądania. Przy takim podejściu unikamy operacji wyszukiwania w indeksie klastrowym bądź stercie. Jest to duża zaleta ale nie główna.

Taka metoda dostępu do danych jest efektywna przede wszystkim z względu na mniejszy rozmiar wiersza. Zakładając, że w indeksie klastrowym na jednej stronie jesteśmy wstanie upakować 80 wierszy (w uproszczonym ujęciu zakładając, że jeden wiersz to 100bajtów = 0,1kb * 80 = 8kb), to przy indeksie pokrywającym np. przy połowie mniejszej ilości kolumn otrzymamy 160 wierszy. Teoretyczne zapytanie pobierające 10000 wierszy musi przeskanować o połowę mniejszą ilość stron na poziomie liści indeksu nieklastrowego.

Tak jak pisałem na wstępie, nic odkrywczego. Jednak to co najciekawsze w tym wpisie to wg, mnie przykład. Do tego użyłem bazę StackOverflow i jego największą tabelą Posts. Poniższe zapytanie wykonywało się 3:05 min i wygenerowało 5442694 odczyty przy użyciu indeksu klastrowego.

Wyklikując w SSMS indeks pokrywający bardzo mocno skróciliśmy czas zapytania ale tego można było się spodziewać

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table ‚Posts’. Scan count 1, logical reads 505, physical reads 4, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 130 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Jednak idzie zrobić to lepiej. Zakładając indeks pokrywający należy zwrócić uwagę na operatory użyte w predykacie oraz ich selektywność i na podstawie tego ustalić kolejność kolumn na liście klucza indeksu. I to jest główna zasad która należy kierować się przy zakładaniu takiego indeksu.

W ten sposób zapytanie zredukowane zostało do 8 logicznych odczytów. Dzięki takiej zmianie już na samym początku SQL Server jest wstanie mocno ograniczyć przeszukiwanie liści w indeksie. co dało nam poniższy rezultat.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table ‚Posts’. Scan count 1, logical reads 8, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 44 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Niby z pozoru nie wielka zmiana a potrafi zrobić różnicę.

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

1 Comments

  1. Adrian Chodkowski

    Przyjemny artykuł 🙂 indeksy pokrywające same w sobie są czymś z czego musimy zdawać sobie sprawę – ale np. w przypadku tabel in-memory „pokrywanie” indeksu już absolutnie nie ma żadnego znaczenia – każdy indeks tam jest pokrywający:)

Leave a Reply

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *