Optymalizacja bazy danych – indeksy cz. 1

Jest to krótki kurs wyjaśniający sposób działania indeksów bazodanowych i ich wpływ na szybkość działania zapytań SQL. We wstępnym artykule założymy pierwszy indeks i sprawdzimy jego wpływ na najprostsze zapytania.

Baza testowa

Załóżmy tabelę o nazwie OSOBY zawierającą listę osób. Wprowadzimy 3 wiersze.

PESEL IMIE NAZWISKO
62 Jan Kowalski
51 Ewa Wiśniewska
73 Piotr Kowalski

Ważne założenie: taka tabela jest przechowywana jako plik i pomijamy mechanizmy cachowania tego pliku w pamięci.

Zapytanie SQL przed założeniem indeksów

Wyszukajmy osobę o peselu równym 51

select * from osoby where pesel=51

jak sprawdzić czy ten select wykonuje się optymalnie?

Patrzymy na tzw. plan wykonania dostępny pod poleceniem SQL lub akcją edytora (np. F5 w PLSQL Developerze – Oracle).
PLAN WYKONANIA jest to podgląd sposobów w jaki baza wyszuka dane w tabelach. Pojedyncza tabela (tak jak w naszej bazie testowej) może być przeszukana na różne sposoby (nazwy pochodzą z bazy ORACLE ale podobne są w innych systemach). Oto pierwszy z poznanych sposobów:

TABLE ACCESS FULL – w poszukiwaniu wiersza z tabeli zawierającego pesel równy 51 zostanie w pętli załadowany z pliku do pamięci każdy rekord z tabeli i porównamy z szukaną wartością. Można przyjąć że czas wykonania zapytania jest proporcjonalny do ilości przejrzanych wierszy

Podglądając plan wykonania widzimy opis TABLE ACCESS FULL , więc zostanie przejrzana cała tabela w celu znalezienia osoby z peselem równym 51. To jest najwolniejsze rozwiązanie. Dla naszej tabeli wymaga przejrzenia od 1 do 3 wierszy. W celu przyspieszenie zapytania SQL założymy indeks.

Pierwszy indeks

Teraz dodamy indeks na polu PESEL do tabeli OSOBY

create index on osoby(pesel)

Upraszczając, indeksy można traktować jak niewidoczne, pomocnicze, wstępnie posortowane tabele, służące szybkiemu wyszukiwaniu danych w naszej tabeli OSOBY. Aby to bliżej wyjaśnić dodajmy kolumnę do naszej tabeli OSOBY numer wiersza. Jest to pomocnicza kolumna, którą posługuje się system bazy danych

numer wiersza PESEL IMIE NAZWISKO
1 62 Jan Kowalski
2 51 Ewa Wiśniewska
3 73 Piotr Kowalski

Niewidoczna tabela dla indeksu na polu pesel wygląda tak:

PESEL numer wiersza
51 2
62 1
73 3

Jest ona posortowana według pola PESEL.

Teraz wykonujemy ponownie select

select * from osoby where pesel=51

Baza danych skorzysta z posortowanej tablicy indeksu, wyszuka binarnie PESEL i otrzyma od razu numer wiersza z tablicy głównej, tym razem plan wykonania to INDEX UNIQUE SCAN. Dla naszej tabeli to jest wyszukanie binarne tabeli indeksu – przejrzenie od 1 do 2 wierszy i załadowanie 1 właściwego wiersza w tabeli OSOBY. Razem to przejrzenie od 2 do 3 wierszy.

System bazy danych skorzystał z drugiego sposób przejrzenia tabeli:

INDEX SCAN – nie będzie przeglądana cała tabela, dzięki indeksowi od razu zostanie wybrany właściwy wiersz, znacznie przyspiesza to wykonanie zapytań SQL, pomijając przy wyszukiwaniu niepasujące rekordy

Czy indeks przyspieszył zapytanie?

Jak słusznie widać dla naszej tabeli OSOBY z 3 wierszami założenie indeksu nie przyspieszyło zapytania. Mniej wierszy do przejrzenia ma system bez założonego indeksu.

Jednak nasza tabela jest specyficzna – ma tylko 3 wiersze i rzeczywiście system szybciej je przejrzy jeden po drugim niż skorzysta z indeksu.

Dla większych tabel przyspieszenie jest znaczne

Teraz załadujmy do tabeli OSOBY 1000 wierszy.

Przejrzenie bez indeksu wymaga załadowania z pliku bazy do pamięci od 1 do 1000 wierszy w zależności od położenia wyszukiwanego rekordu – statystycznie wypadnie około  500 wierszy.

Z indeksem, przy pomocy wyszukiwania bisekcją na tabeli z 1000 wierszy, będzie sie składać z operacji załadowania w czasie szukania 1 do 10 wierszy z tabeli indeksowej  oraz docelowego wiersza z tabeli OSOBY, razem to od 2 do 11 operacji załadowania wiersza do pamięci.

Wniosek: im tabela zawiera więcej wierszy tym założenie indeksu bardziej przyspieszy zapytania. Można przyjąć że dla tabel zawierających więcej niż 100 wierszy przyspieszenie będzie zauważalne a założenie indeksu staje się opłacalne.

Uwaga na koniec

W rzeczywistości tabela pomocnicza indeksu jest inną wersją posortowanej tablicy – jest B-drzewem, ułatwia to obsługę przez system nieustającego sortowania indeksu po każdym wstawieniu i usunięciu rekordu z tabeli OSOBY

One thought on “Optymalizacja bazy danych – indeksy cz. 1”

  1. Najprostszym rozwiazaniem wydaje sie zakup wydajniejszego serwera czy wiekszego dysku, ew. kolejnych serwerow czy dyskow. Do rozwiazanie ma jednak ta podstawowa wade, iz jest dosc drogie. Znacznie tansze jest wykonanie optymalizacji samej bazy danych – ta metoda mozna uzyskac niejednokrotnie lepsze rezultaty, ktore znacznie oddala w czasie kolejne problemy z wydajnoscia czy miejscem na dysku.

Możliwość komentowania jest wyłączona.