Nadmiarowe indeksy

Nadmiarowe indeksy to struktury pokrywające się zakresem z innymi — spowalniają zapis, nie przyspieszając odczytu.

Jak rozpoznać nadmiarowe indeksy w bazie danych

Nadmiarowe indeksy to dodatkowe struktury utworzone na tych samych kolumnach albo na bardzo zbliżonych zestawach kolumn, które nie zapewniają istotnej korzyści dla zapytań. Indeks może być całkowicie powielony, gdy ma identyczną kolejność kolumn i taki sam zakres zastosowania jak inny indeks. Może też być częściowo nadmiarowy, gdy jego kolumny początkowe pokrywają się z indeksem szerszym. W takiej sytuacji oba obiekty mogą obsługiwać podobne warunki wyszukiwania, ale każdy z nich musi być osobno utrzymywany podczas zmian danych.

Ocena nadmiarowości wymaga sprawdzenia rzeczywistego sposobu korzystania z bazy. Sama obecność dwóch indeksów zawierających podobne nazwy kolumn nie wystarcza do ich usunięcia. Znaczenie ma kolejność kolumn, typ indeksu, możliwość wykorzystania do sortowania, ograniczenia unikalności, filtr indeksu oraz to, czy struktura obejmuje dodatkowe kolumny używane do odczytu. Indeks na kolumnach (status, data_utworzenia) nie jest automatycznie równoważny indeksowi (data_utworzenia, status), ponieważ kolejność wpływa na zakres możliwych wyszukiwań i sortowanie.

Dlaczego powielone struktury spowalniają zapis

Każdy indeks opisujący tabelę musi być aktualizowany po dodaniu, zmianie lub usunięciu wiersza. Operacja zapisu obejmuje więc nie tylko stronę tabeli, lecz także odpowiednie strony wszystkich powiązanych indeksów. Im więcej podobnych struktur, tym więcej wpisów trzeba utworzyć albo zmienić. Dotyczy to również zmian wartości kolumny objętej indeksem, ponieważ stary wpis może wymagać usunięcia, a nowy dodania w innym miejscu drzewa indeksowego.

Nadmiarowe indeksy zwiększają wykorzystanie procesora, pamięci operacyjnej i operacji wejścia-wyjścia. W indeksach drzewiastych, takich jak B-tree lub struktury o zbliżonej organizacji, częste zapisy mogą powodować dzielenie stron, przesuwanie wpisów i powstawanie fragmentacji. Większa liczba struktur oznacza również większy rozmiar kopii zapasowych, dłuższe operacje odbudowy oraz dodatkowy czas potrzebny na sprawdzanie spójności. Skutek jest szczególnie widoczny w tabelach, do których dane są intensywnie dopisywane lub często aktualizowane.

Wpływ nadmiarowych indeksów nie ogranicza się do samego zapisu. Optymalizator zapytań musi rozważyć dostępne ścieżki wykonania, dlatego duża liczba podobnych indeksów może zwiększać złożoność wyboru planu. Nie oznacza to, że każde zapytanie będzie wykonywane wolniej, ale utrudnia ocenę planów i może prowadzić do wykorzystania struktury o podobnym, lecz mniej korzystnym układzie kolumn. Indeks, który kiedyś był potrzebny, może stać się zbędny po zmianie zapytań, schematu aplikacji albo rozkładu danych.

Porównanie zakresu indeksów przed zmianą

Bezpieczna analiza powinna rozpocząć się od inwentaryzacji indeksów dla konkretnej tabeli. Należy zebrać informacje o nazwie struktury, kolejności kolumn kluczowych, kolumnach dołączonych, unikalności, filtrach oraz ograniczeniach, z którymi indeks jest powiązany. Następnie porównuje się te dane z zapytaniami wykonywanymi przez aplikację. Indeks szeroki może zastąpić węższy tylko wtedy, gdy jego kolejność kolumn pozwala obsłużyć ten sam warunek, a dodatkowy koszt przechowywania i aktualizacji jest uzasadniony.

Warto odróżnić indeks nieużywany od indeksu nadmiarowego. Pierwszy może nie być wykorzystywany w obserwowanym okresie, ale nadal może chronić zapytanie wykonywane sporadycznie, na przykład podczas raportowania albo obsługi określonego formularza. Drugi dubluje funkcję innej struktury, lecz również powinien być oceniany na podstawie planów wykonania i obciążenia. Samo usunięcie indeksu na podstawie krótkiego monitoringu może spowodować pogorszenie działania rzadkiego, lecz ważnego zapytania.

Przed usunięciem indeksu należy sprawdzić zależności logiczne i techniczne. Indeks unikalny może wymuszać regułę integralności danych, więc jego zastąpienie zwykłym indeksem zmieniłoby zachowanie tabeli. Podobnie indeks powiązany z kluczem głównym, ograniczeniem unikalności lub mechanizmem kontroli relacji nie jest wyłącznie akceleratorem odczytu. Zmianę najlepiej przeprowadzić etapami: zarejestrować stan początkowy, usunąć albo wyłączyć jedną strukturę zgodnie z możliwościami używanego silnika, obserwować zapytania i operacje zapisu, a następnie potwierdzić wynik na reprezentatywnym obciążeniu.

Znaczenie uporządkowanych indeksów na komputerze

W aplikacji używanej na laptopie lub komputerze PC nadmiarowe indeksy mogą ujawnić się jako długi zapis danych, opóźnione importy, wolniejsze wyszukiwanie albo zwiększone zajęcie dysku. Przyczyną nie musi być uszkodzenie sprzętu. Podobne objawy mogą wynikać z nieprawidłowo zaprojektowanej bazy, niekontrolowanego tworzenia indeksów przez aplikację, fragmentacji lub zbyt małej ilości wolnego miejsca na dysku. Diagnostyka powinna rozdzielić problem programowy od awarii nośnika, pamięci operacyjnej albo systemu plików.

Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną naprawą laptopów, komputerów stacjonarnych PC i ich podzespołów, dlatego w przypadku spowolnienia aplikacji można zlecić sprawdzenie komputera, nośnika danych oraz warunków pracy programu. Ocena nadmiarowych indeksów wymaga dostępu do konfiguracji i danych konkretnej aplikacji, a każda zmiana w bazie powinna być poprzedzona kopią zapasową oraz potwierdzeniem, że usuwana struktura nie pełni funkcji integralnościowych.