Schemat indeksu

Schemat indeksu to sposób grupowania kolumn i porządkowania danych w strukturze przyspieszającej wyszukiwanie.

Jak zbudowany jest schemat indeksu

Schemat indeksu określa, które kolumny tabeli uczestniczą w indeksie, w jakiej kolejności są ułożone oraz jakie reguły dotyczą przechowywanych wartości. Indeks jest dodatkową strukturą danych powiązaną z tabelą. Zawiera wartości pochodzące z 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 przeglądać całą tabelę od początku do końca.

W indeksie jednokolumnowym kluczem jest jedna kolumna, na przykład email albo data_utworzenia. Indeks wielokolumnowy, nazywany także złożonym, wykorzystuje kilka kolumn. Ich kolejność ma znaczenie, ponieważ struktura jest porządkowana najpierw według pierwszej kolumny, następnie według kolejnych wartości przy takim samym prefiksie. Indeks zdefiniowany jako (nazwisko, imie) może być przydatny dla wyszukiwania po nazwisku oraz po nazwisku i imieniu, ale jego zastosowanie przy filtrze obejmującym wyłącznie imię może być ograniczone.

Kolejność kolumn a sposób wyszukiwania

Projektowanie schematu indeksu powinno wynikać z rzeczywistych zapytań wykonywanych na tabeli. Kolumna używana do równościowego filtrowania często znajduje się przed kolumną wykorzystywaną w zakresie, na przykład w warunku większym, mniejszym albo pomiędzy dwiema wartościami. Dla zapytania wyszukującego rekordy konkretnego użytkownika w określonym przedziale czasu użyteczny może być indeks obejmujący najpierw identyfikator użytkownika, a następnie znacznik czasu.

Znaczenie ma także sortowanie wartości rosnąco lub malejąco, jeżeli silnik obsługuje taki parametr w definicji indeksu i może wykorzystać go przy sortowaniu wyników. Nie każdy indeks poprawi każde zapytanie. Jeżeli warunek filtruje kolumnę bez użycia początkowego fragmentu klucza indeksu, silnik może uznać, że pełny skan tabeli będzie tańszy. Ocena zależy między innymi od liczby rekordów, rozkładu wartości, selektywności warunku oraz aktualnych statystyk optymalizatora.

Schemat indeksu może również zawierać ograniczenie unikalności. Indeks unikalny nie pozwala zapisać dwóch wierszy z taką samą kombinacją wartości klucza, z uwzględnieniem reguł dotyczących wartości pustych obowiązujących w konkretnym silniku bazy danych. W ten sposób indeks pełni jednocześnie funkcję kontrolną i wyszukiwawczą. Zwykły indeks nie gwarantuje unikalności, dlatego sam fakt szybkiego wyszukiwania nie zabezpiecza przed powtórzeniem danych.

Koszt indeksowania i aktualność struktury

Indeks przyspiesza część operacji odczytu, ale zwiększa koszt zapisu. Przy dodawaniu, zmianie albo usuwaniu wiersza silnik musi zaktualizować nie tylko tabelę, lecz także każdą strukturę indeksową, której dotyczy zmieniona wartość. Indeksy zajmują również miejsce na dysku i mogą zwiększać wymagania dotyczące pamięci operacyjnej, ponieważ często używane fragmenty struktur są przechowywane w pamięci podręcznej.

Zbyt duża liczba indeksów może powodować dłuższe operacje INSERT, UPDATE i DELETE, a także utrudniać optymalizatorowi wybór planu wykonania. Z kolei brak indeksu na często filtrowanej lub łączonej kolumnie może prowadzić do odczytu całej tabeli. Nie należy więc tworzyć indeksów wyłącznie na podstawie nazw kolumn. Potrzebna jest analiza zapytań, ich częstotliwości, liczby zwracanych wierszy oraz kosztu utrzymywania struktury.

W czasie eksploatacji indeks może ulegać fragmentacji albo mieć statystyki nieodzwierciedlające aktualnego rozkładu danych. Sposób reagowania na taki stan zależy od konkretnego silnika bazy danych. Bezpieczna ocena wymaga sprawdzenia planu wykonania, rozmiaru tabeli, obciążenia operacjami zapisu i odczytu oraz tego, czy problem rzeczywiście wynika z indeksu. Sama obecność indeksu nie jest dowodem, że zapytanie będzie wykonywane optymalnie.

Rozpoznawanie problemów ze schematem indeksu na komputerze

Objawem nieprawidłowo dobranego schematu indeksu może być długi czas wykonywania zapytań, wzrost użycia procesora, intensywna aktywność dysku albo opóźnienia podczas zapisywania danych. Na laptopie lub komputerze PC takie symptomy mogą jednak wynikać także z braku wolnej pamięci, wolnego nośnika, błędów systemu plików, przeciążenia procesu bazy danych lub nieprawidłowej konfiguracji aplikacji. Dlatego diagnozę rozpoczyna się od ustalenia, czy spowolnienie dotyczy jednego zapytania, całej aplikacji, czy wszystkich operacji na komputerze.

W analizie warto porównać zapytanie przed i po zmianie schematu indeksu oraz sprawdzić plan wykonania udostępniany przez używany silnik. Należy zwrócić uwagę na pełny skan tabeli, skan indeksu, operacje sortowania, koszt łączenia tabel i liczbę faktycznie odczytanych wierszy. Trzeba także zweryfikować, czy zapytanie nie przekształca kolumny w warunku w sposób uniemożliwiający wykorzystanie indeksu, na przykład przez funkcję zastosowaną bezpośrednio do wartości przechowywanej w tabeli.

Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną naprawą laptopów, komputerów PC i ich podzespołów, dlatego przy problemach z wydajnością aplikacji można zlecić sprawdzenie stanu komputera, nośnika danych oraz środowiska, w którym działa używane oprogramowanie. W przypadku problemu wynikającego z samego schematu indeksu potrzebna jest dodatkowo analiza konfiguracji i dokumentacji właściwego silnika bazy danych.