Tworzenie indeksu

Tworzenie indeksu poleceniem CREATE INDEX przyspiesza wyszukiwanie kosztem dodatkowego miejsca i obciążenia zapisu.

Rola indeksu w strukturze bazy danych

Tworzenie indeksu polega na dodaniu pomocniczej struktury danych powiązanej z tabelą. Struktura ta przechowuje wartości wybranej kolumny lub zestawu kolumn w uporządkowanej postaci oraz informacje pozwalające odnaleźć odpowiadające im wiersze tabeli. Dzięki temu silnik bazy danych może ograniczyć zakres odczytu podczas wyszukiwania, zamiast sprawdzać kolejno każdy rekord. Indeks nie zawiera zwykle pełnej kopii wszystkich danych z tabeli, dlatego jego użycie nie eliminuje potrzeby odczytania właściwych wierszy, ale może znacząco zmniejszyć liczbę operacji wejścia i wyjścia.

Indeks jest przydatny przede wszystkim wtedy, gdy zapytania często filtrują dane według określonej kolumny, łączą tabele za pomocą klucza albo sortują wynik według przewidywalnego pola. Typowym przykładem jest kolumna przechowująca identyfikator, numer dokumentu, adres e-mail lub datę utworzenia rekordu. Samo utworzenie indeksu nie gwarantuje jednak przyspieszenia każdego zapytania. Optymalizator wybiera plan wykonania na podstawie statystyk, szacowanej liczby wyników, kosztu odczytu oraz dostępnych struktur indeksowych.

Polecenie CREATE INDEX i dobór kolumn

Do utworzenia zwykłego indeksu służy polecenie CREATE INDEX. Ogólna postać obejmuje nazwę indeksu, nazwę tabeli i kolumnę lub kolumny, na których indeks ma powstać, na przykład CREATE INDEX idx_orders_customer_id ON orders (customer_id);. Nazwy tabel, kolumn i dodatkowe opcje zależą od konkretnego silnika bazy danych, dlatego przed wykonaniem polecenia trzeba sprawdzić jego dokumentację. Indeks powinien mieć unikalną nazwę w zakresie wymaganym przez dany silnik, a kolumny muszą istnieć w tabeli.

Przy indeksie wielokolumnowym znaczenie ma kolejność kolumn. Indeks zdefiniowany jako (customer_id, created_at) może być użyteczny dla warunków filtrujących po customer_id, a następnie po created_at. Nie musi natomiast działać równie dobrze dla zapytania wykorzystującego wyłącznie drugą kolumnę. Z tego powodu kolejność ustala się na podstawie rzeczywistych predykatów zapytań, ich selektywności oraz częstotliwości użycia. Kolumna o dużej liczbie powtarzających się wartości może dawać mniejszą korzyść przy samodzielnym indeksowaniu niż kolumna, która skutecznie zawęża zbiór wyników.

Koszt utrzymania i wpływ na operacje zapisu

Indeks zajmuje dodatkowe miejsce na nośniku i w pamięci operacyjnej używanej przez silnik. Każde dodanie, zmiana albo usunięcie wiersza może wymagać aktualizacji wszystkich indeksów obejmujących zmieniane dane. W rezultacie indeks, który przyspiesza odczyty, może jednocześnie zwiększać koszt operacji INSERT, UPDATE i DELETE. Zmiana wartości kolumny indeksowanej może wymagać usunięcia starego wpisu indeksu i dodania nowego. Im więcej niepotrzebnych indeksów znajduje się na tabeli, tym większy może być narzut zapisu oraz konserwacji.

Nie każdy indeks jest też stale używany. Duplikaty, indeksy o bardzo podobnym zestawie kolumn oraz struktury pozostałe po dawnych zapytaniach zwiększają złożoność schematu bez gwarantowanej korzyści. Ocena powinna obejmować plan wykonania zapytania, częstotliwość odczytów i zapisów, rozmiar tabeli oraz aktualność statystyk. Przy dużych tabelach utworzenie indeksu może trwać długo i wymagać dodatkowego miejsca tymczasowego. W zależności od silnika i użytej opcji operacja może również wpływać na blokowanie tabeli lub współbieżność, dlatego indeksowanie tabel używanych produkcyjnie wymaga sprawdzenia zachowania konkretnej wersji silnika.

Weryfikacja działania indeksu po utworzeniu

Po wykonaniu CREATE INDEX należy sprawdzić, czy indeks został zapisany w schemacie i czy zapytania faktycznie mogą go wykorzystać. Przydatne jest porównanie planu wykonania przed i po zmianie, jeżeli środowisko umożliwia uzyskanie takiego planu. Plan może wskazywać skan indeksu, wyszukiwanie po kluczu indeksu albo pełny skan tabeli. Pełny skan nie zawsze oznacza błąd: przy małej tabeli odczyt całej struktury może być tańszy niż przejście przez indeks i późniejsze pobieranie wielu wierszy.

Diagnostyka problemów z indeksem obejmuje również sprawdzenie zgodności typu danych, warunku zapytania i kolejności kolumn w indeksie wielokolumnowym. Funkcja zastosowana do indeksowanej kolumny, niejawna konwersja typu albo wyrażenie o innej postaci może ograniczyć możliwość wykorzystania struktury. Należy także uwzględnić unikalność danych: indeks zwykły nie blokuje powtórzeń, natomiast indeks utworzony z opcją UNIQUE wymusza niepowtarzalność klucza zgodnie z regułami danego silnika. W przypadku nieudanej operacji trzeba przeanalizować komunikat błędu, stan schematu, istniejące nazwy indeksów i uprawnienia użytkownika wykonującego polecenie. Przy problemach z działaniem aplikacji na laptopie lub komputerze PC można zweryfikować lokalne narzędzia bazodanowe, konfigurację połączenia i logi programu, bez zakładania, że samo dodanie indeksu rozwiąże przyczynę opóźnienia.