Konflikty indeksów

Konflikty indeksów to sytuacja, w której nakładające się struktury spowalniają zapis zamiast przyspieszać odczyt.

Na czym polega konflikt indeksów

Konflikt indeksów występuje wtedy, gdy tabela ma kilka struktur indeksowych przechowujących podobne informacje albo obejmujących te same kolumny w zbliżonej kolejności. Indeks jest dodatkową strukturą danych, która pozwala szybciej odnaleźć wiersze spełniające warunek zapytania. Nie jest jednak bezpłatny: każda zmiana danych może wymagać aktualizacji jednego lub wielu indeksów. Jeżeli indeksy częściowo się pokrywają, koszt utrzymania rośnie, a korzyść z kolejnej struktury może być niewielka.

Przykładem jest tabela zamówień z indeksami na kolumnie klient_id oraz na kolumnach klient_id, data_zamówienia. Drugi indeks może obsługiwać zapytania filtrujące po kliencie i sortujące według daty, natomiast pierwszy może być potrzebny tylko w określonych planach wykonania. Jeżeli oba indeksy są często aktualizowane, ale jeden z nich nie daje istotnej przewagi, ich współistnienie może powodować większe zużycie miejsca oraz dłuższy zapis.

Dlaczego nadmiar indeksów spowalnia zapis

Operacja INSERT musi dopisać rekord do tabeli oraz uzupełnić każdy indeks, którego dotyczy nowy wiersz. Operacje UPDATE i DELETE mogą wymagać zmiany albo usunięcia kilku wpisów indeksowych. W strukturach typu B-tree modyfikacje mogą prowadzić do podziału stron, przesuwania wpisów i okresowej reorganizacji układu. Im więcej indeksów obejmuje zmieniane kolumny, tym więcej pracy wykonuje silnik bazy danych przy pojedynczej zmianie.

Konflikt nie zawsze oznacza identyczność indeksów. Problemem może być także indeks szeroki, zawierający wiele kolumn, który pokrywa zastosowanie kilku węższych struktur, lecz zwiększa rozmiar wpisów i liczbę stron do odczytu. Odwrotna sytuacja również jest możliwa: wiele małych indeksów może wyglądać na oszczędne, ale żaden nie odpowiada dokładnie warunkom najczęstszych zapytań. Optymalizator może wtedy wybrać skanowanie tabeli, użyć tylko części indeksu albo połączyć kilka indeksów kosztem dodatkowych operacji.

Skutkiem bywa nie tylko dłuższy zapis. Duża liczba indeksów zwiększa zapotrzebowanie na pamięć podręczną, wydłuża tworzenie kopii i może powodować większą fragmentację. Przy tabelach intensywnie modyfikowanych utrzymanie niepotrzebnych indeksów może więc wpływać na cały system, mimo że pojedyncze zapytanie odczytujące dane wydaje się działać poprawnie.

Jak rozpoznać rzeczywisty problem w planie zapytania

Ocena konfliktu indeksów powinna opierać się na pomiarach, a nie wyłącznie na nazwach struktur. Najpierw analizuje się zapytania wykonujące odczyt i zapis, ich częstotliwość, czas trwania oraz liczbę przetwarzanych wierszy. Następnie porównuje się definicje indeksów: kolejność kolumn kluczowych, kolumny dołączone, warunki indeksów filtrowanych oraz ich udział w konkretnych zapytaniach. Dwa indeksy mogą wyglądać podobnie, ale służyć innym predykatom, kolejności sortowania lub ograniczeniu zakresu danych.

Plan wykonania pokazuje, czy silnik korzysta z wyszukiwania po indeksie, skanowania indeksu, skanowania tabeli, sortowania albo łączenia wyników z kilku struktur. Ważne są także wartości rzeczywiste, takie jak liczba odczytanych stron, liczba zwróconych wierszy oraz rozbieżność między estymacją a wykonaniem. Jeżeli plan regularnie wybiera kosztowną ścieżkę mimo obecności wielu indeksów, przyczyną może być niedopasowanie kolejności kolumn, nieaktualne statystyki, niska selektywność warunku albo niekorzystna konstrukcja zapytania.

Nie powinno się usuwać indeksu wyłącznie dlatego, że przez pewien czas nie odnotowano jego użycia. Rzadko używana struktura może być potrzebna podczas raportu okresowego, operacji awaryjnej albo zapytania wykonywanego tylko przy określonych danych. Bezpieczniejsza analiza obejmuje reprezentatywny okres obciążenia, test w środowisku kontrolnym oraz porównanie czasu odczytu i zapisu przed i po zmianie. Trzeba również sprawdzić zależności wynikające z kluczy unikatowych i ograniczeń integralności, ponieważ taki indeks może pełnić funkcję logiczną, a nie wyłącznie optymalizacyjną.

Porządkowanie indeksów bez utraty spójności danych

Usuwanie konfliktu indeksów zaczyna się od sporządzenia mapy zapytań i struktur. Indeksy identyczne lub całkowicie zbędne można rozważyć do usunięcia po potwierdzeniu, że nie są wymagane przez ograniczenia ani procesy uruchamiane sporadycznie. W przypadku indeksów częściowo pokrywających się często lepszym rozwiązaniem jest zaprojektowanie jednej struktury odpowiadającej najczęstszemu warunkowi, kolejności sortowania i potrzebie odczytu dodatkowych kolumn. Taka decyzja musi uwzględniać zarówno czas zapytań, jak i koszt aktualizacji tabeli.

Przed zmianą należy zapisać definicje indeksów, wykonać pomiary oraz ustalić możliwość odtworzenia poprzedniego stanu. Po modyfikacji sprawdza się plany wykonania, czas operacji INSERT, UPDATE i DELETE, obciążenie dysku oraz zachowanie zapytań raportowych. Sama reorganizacja lub przebudowa indeksu nie usuwa konfliktu projektowego, jeżeli problem wynika z nadmiarowych struktur albo niewłaściwego doboru kolumn. Diagnostyka powinna więc rozróżniać fragmentację, nieaktualne statystyki, blokowanie transakcji i rzeczywiste nakładanie się indeksów. Dopiero takie rozdzielenie przyczyn pozwala ograniczyć koszt zapisu bez pogorszenia odczytu.