Indeksy pokrywające

Indeksy pokrywające to zbiorcze określenie struktur zawierających wszystkie kolumny wymagane przez dane zapytanie.

Na czym polega indeks pokrywający

Indeks pokrywający to indeks, którego struktura zawiera wszystkie kolumny potrzebne do wykonania określonego zapytania. Chodzi nie tylko o kolumny używane do wyszukiwania wierszy, lecz także o pola występujące w warunkach filtrowania, sortowaniu, grupowaniu oraz na liście zwracanych wyników. Dzięki temu silnik bazy danych może znaleźć wymagane wpisy i odczytać potrzebne wartości bez sięgania do głównej struktury tabeli, zwykle nazywanej stertą albo tabelą przechowywaną według klucza klastrowanego.

Określenie „pokrywający” nie opisuje osobnego, uniwersalnego typu indeksu niezależnego od silnika bazy danych. Jest to właściwość indeksu w odniesieniu do konkretnego zapytania. Ten sam indeks może pokrywać jedno zapytanie, ale nie pokrywać innego, jeżeli drugie zapytanie wymaga dodatkowej kolumny. Przykładowo indeks zawierający identyfikator klienta, status oraz datę utworzenia może wystarczyć do wyszukania aktywnych rekordów i zwrócenia ich dat, ale nie będzie pokrywający dla zapytania, które dodatkowo odczytuje adres e-mail, jeżeli ta kolumna nie znajduje się w indeksie.

Jak silnik wykorzystuje dodatkowe kolumny

Indeks zwykle ma kolumny kluczowe, które określają kolejność wpisów i umożliwiają wyszukiwanie, oraz ewentualne kolumny dołączone wyłącznie po to, aby przechować dane potrzebne do odczytu wyniku. Kolumny kluczowe mogą wspierać wyszukiwanie zakresowe, porównania wartości i sortowanie. Kolumny dołączone nie muszą wpływać na kolejność indeksu, ale pozwalają przechować dodatkowe informacje w jego liściach. Konkretna nazwa i sposób definiowania takich kolumn zależą od używanego systemu zarządzania bazą danych.

Gdy plan wykonania pokazuje odczyt wyłącznie z indeksu, zapytanie może uniknąć dodatkowych operacji wyszukiwania w tabeli. Taki plan bywa określany jako skan lub wyszukiwanie indeksu z pełnym pokryciem, zależnie od zakresu odczytu i terminologii danego silnika. Mniejsza liczba operacji wejścia-wyjścia może zmniejszyć koszt zapytania, szczególnie gdy tabela zawiera dużo wierszy, a zapytanie zwraca niewielki zestaw kolumn. Nie oznacza to jednak, że każde użycie indeksu pokrywającego będzie szybsze od odczytu tabeli. Optymalizator bierze pod uwagę selektywność warunków, rozmiar indeksu, statystyki oraz przewidywaną liczbę zwracanych rekordów.

Korzyści i koszty utrzymywania indeksu

Najważniejszą korzyścią indeksu pokrywającego jest ograniczenie tak zwanych odwołań do tabeli po znalezieniu kluczy pasujących do warunku. Bez pokrycia silnik może najpierw odszukać klucze w indeksie, a następnie pobrać pozostałe kolumny z tabeli. Przy dużej liczbie dopasowanych wierszy te dodatkowe odczyty mogą stać się znaczącą częścią czasu wykonania. Indeks pokrywający może również pomóc w obsłudze sortowania, jeżeli kolejność jego kluczy odpowiada kolejności wymaganej przez zapytanie.

Rozszerzanie indeksu o kolejne kolumny zwiększa jednak jego rozmiar. Większy indeks wymaga więcej miejsca na dysku i pamięci podręcznej, a jego skanowanie może obejmować więcej danych. Każda operacja dodania, zmiany lub usunięcia wiersza musi także uwzględnić modyfikację wszystkich odpowiednich indeksów. Zbyt szeroki indeks może więc pogorszyć wydajność zapisów, zwiększyć fragmentację i wydłużyć operacje konserwacyjne. Przy projektowaniu należy objąć indeksem tylko te kolumny, które wynikają z rzeczywistych zapytań i potwierdzonego planu wykonania.

Weryfikacja indeksu w działającej bazie

Ocena przydatności indeksu pokrywającego wymaga przeanalizowania konkretnego zapytania oraz jego planu wykonania. Należy sprawdzić, czy silnik rzeczywiście korzysta z danego indeksu, czy wykonuje wyszukiwanie, czy skanowanie, jakie kolumny odczytuje oraz czy występuje dodatkowe pobieranie danych z tabeli. Sam fakt istnienia indeksu nie dowodzi jego użyteczności. Plan może wybrać inną strukturę, jeżeli warunki zapytania są mało selektywne, statystyki są nieaktualne albo koszt odczytu indeksu jest wyższy od kosztu alternatywnego planu.

Przed zmianą schematu warto porównać czas wykonania i liczbę odczytów dla reprezentatywnych danych, a następnie sprawdzić zachowanie zapytań po utworzeniu indeksu. Analiza powinna obejmować również zapytania wykonywane przy modyfikacji tabeli, ponieważ nowa struktura wpływa na koszt zapisów. W środowisku serwisowym diagnoza problemu z wolnym programem powinna rozróżniać brak odpowiedniego indeksu, nieaktualne statystyki, niekorzystny plan wykonania oraz problem niezwiązany z bazą, taki jak opóźnienia dysku, pamięci lub sieci. Dopiero takie porównanie pozwala ocenić, czy indeks pokrywający rozwiązuje rzeczywiste wąskie gardło.