Optymalizacja zapytań przez indeksy

Optymalizacja przez indeksy polega na tworzeniu struktur pokrywających zapytanie, by uniknąć przeglądania całej tabeli.

Na czym polega pokrycie zapytania przez indeks

Indeks jest dodatkową strukturą danych utrzymywaną obok tabeli. Zawiera uporządkowane wartości wybranych kolumn oraz informacje pozwalające odnaleźć odpowiadające im wiersze. Dzięki temu silnik bazy danych może ograniczyć liczbę odczytywanych rekordów, zamiast sprawdzać warunek kolejno dla każdego wiersza tabeli. Taki pełny odczyt tabeli jest określany jako skanowanie tabeli. Indeks nie zmienia wyniku zapytania, lecz może zmienić sposób dojścia do tego wyniku.

Indeks pokrywający zapytanie zawiera wszystkie dane potrzebne do wykonania określonego odczytu albo przynajmniej pozwala pobrać je bez ponownego odwoływania się do całej tabeli. Przykładowo, jeżeli zapytanie filtruje rekordy po kolumnie status i zwraca kolumnę data_utworzenia, odpowiednio zbudowany indeks może przechowywać obie te wartości. Wtedy silnik może odczytać warunek oraz wynik bez wykonywania dodatkowych odczytów stron danych tabeli. Zakres tego pokrycia zależy od budowy indeksu, kolejności kolumn oraz konkretnego planu wykonania.

Dobór kolumn i kolejności klucza

Projektowanie indeksu zaczyna się od analizy rzeczywistych zapytań, a nie od dodawania indeksu do każdej kolumny. Istotne są kolumny używane w predykatach WHERE, warunkach złączeń, sortowaniu oraz grupowaniu. W indeksie wielokolumnowym kolejność klucza ma znaczenie, ponieważ wyszukiwanie zwykle najłatwiej wykorzystuje początkowy fragment klucza. Indeks rozpoczynający się od klient_id może być przydatny dla wyszukiwania po kliencie, ale nie musi równie dobrze obsługiwać zapytania filtrującego wyłącznie po późniejszej kolumnie.

Przy ocenie przydatności indeksu uwzględnia się selektywność, czyli zdolność warunku do odrzucenia dużej części danych. Kolumna o niewielkiej liczbie różnych wartości, na przykład flaga logiczna, może być słabym pierwszym kluczem indeksu, jeżeli większość wierszy ma tę samą wartość. Z drugiej strony taka kolumna może mieć sens jako część większego indeksu, gdy występuje razem z bardziej rozróżniającym warunkiem. Należy także brać pod uwagę zakresy wartości, operatory nierówności i wyrażenia na kolumnach, ponieważ mogą ograniczać możliwość wykorzystania uporządkowanej struktury indeksu.

Koszt utrzymania a zysk podczas odczytu

Indeks nie jest bezpłatnym przyspieszeniem. Każdy dodatkowy indeks zajmuje miejsce i musi być aktualizowany przy wstawianiu, modyfikowaniu oraz usuwaniu danych. Tabela intensywnie zmieniana może więc ponosić większy koszt operacji zapisu, jeżeli posiada wiele rozbudowanych indeksów. Indeksy pokrywające zapytania mogą być szczególnie szerokie, ponieważ oprócz kolumn kluczowych przechowują także dane potrzebne do zwrócenia wyniku. Ich rozmiar powinien być uzasadniony częstotliwością zapytania oraz zmniejszeniem kosztu odczytu.

Ocena optymalizacji wymaga porównania planu wykonania przed i po zmianie. Należy sprawdzić, czy zmniejszyła się liczba odczytanych stron, liczba przetworzonych wierszy oraz całkowity koszt operacji, a także czy nie wzrósł nadmiernie czas zapisu. Sam fakt pojawienia się indeksu w planie nie dowodzi jeszcze poprawy. Silnik może wybrać skanowanie tabeli, gdy zapytanie zwraca dużą część danych, tabela jest mała albo koszt przejścia przez indeks i późniejszego pobierania wierszy byłby większy. Plan trzeba analizować dla reprezentatywnych danych i rzeczywistych parametrów zapytań.

Weryfikacja indeksu w działającym systemie

Praktyczna diagnostyka rozpoczyna się od zebrania zapytań, które wykonują się długo, często albo powodują duże obciążenie operacji wejścia-wyjścia. Następnie porównuje się ich predykaty, złączenia, sortowanie i listę zwracanych kolumn. Warto sprawdzić, czy istniejące indeksy nie dublują się częściowo oraz czy ich definicje odpowiadają aktualnemu sposobowi korzystania z tabeli. Zmiana zapytania może sprawić, że dawny indeks przestanie być pokrywający, mimo że nadal pozostaje formalnie obecny w schemacie.

Po utworzeniu lub zmianie indeksu należy wykonać test kontrolny obejmujący odczyt i zapis. Trzeba obserwować plan wykonania, czas odpowiedzi, obciążenie procesora, odczyty dyskowe oraz wpływ na równoległe operacje. Należy również uwzględnić zmianę rozkładu danych, ponieważ indeks skuteczny dla bieżącego zbioru może zachowywać się inaczej po jego rozroście. Diagnostyka komputerowa może pomóc ustalić, czy spowolnienie wynika z zasobów sprzętowych, konfiguracji nośnika lub pamięci, jednak sama wymiana podzespołu nie zastępuje analizy planu i struktury indeksów.