Wstęp
Indeksy w bazach danych to jeden z tych mechanizmów, które potrafią diametralnie zmienić wydajność aplikacji – zarówno na plus, jak i na minus. Dobrze zaprojektowane indeksy potrafią skrócić czas zapytania z kilku sekund do ułamka milisekundy. Złe… mogą zabić wydajność całej bazy.
W tym wpisie chciałbym się bliżej przyjrzeć tematowi – ogólnie opowiedzieć o indeksach w bazach danych, jak to wpływa na wydajność, a także pokazać kilka praktycznych przykładów.
Na potrzeby wpisu będziemy pracować na dwóch prostych tabelach: uzytkownicy i zamowienia.
CREATE TABLE uzytkownicy (
id INT NOT NULL PRIMARY KEY,
email VARCHAR(255) UNIQUE,
imie VARCHAR(100),
nazwisko VARCHAR(100),
kraj VARCHAR(50)
);
CREATE TABLE zamowienia (
id INT NOT NULL PRIMARY KEY,
uzytkownik_id INT NOT NULL,
data_zamowienia DATE,
kwota DECIMAL(10, 2),
opis TEXT,
FOREIGN KEY (uzytkownik_id) REFERENCES uzytkownicy(id)
);
A także poniżej kilka insertów do tabel:
INSERT INTO uzytkownicy (id, email, imie, nazwisko) VALUES (1, 'ala@example.com', 'Ala', 'Nowak', 'Polska'), (2, 'bartek@example.com', 'Bartek', 'Kowalski', 'Polska'), (3, 'celina@example.com', 'Celina', 'Wiśniewska', 'Polska'); INSERT INTO zamowienia (id, uzytkownik_id, data_zamowienia, kwota, opis) VALUES (1, 1, '2025-01-01', 99.99, 'Zakup książki IT'), (2, 2, '2025-01-03', 150.50, 'Licencja roczna na oprogramowanie'), (3, 1, '2025-01-05', 75.00, 'Szkolenie online: Git i GitHub'), (4, 3, '2025-01-07', 200.00, 'Konsultacja techniczna - 2h'), (5, 2, '2025-01-08', 120.00, 'Subskrypcja kursu backend'), (6, 3, '2025-01-09', 85.50, 'Ebook: Wzorce projektowe'), (7, 1, '2025-01-10', 60.00, 'Mini kurs: SQL w praktyce'), (8, 1, '2025-01-11', 300.00, 'Pakiet mentoringowy 3h'), (9, 1, '2025-01-12', 45.00, 'Checklista do code review'), (10, 2, '2025-01-13', 250.00, 'Szkolenie z mikroserwisów');
Czym jest indeks w bazach danych?
Najprościej mówiąc, indeks w bazie danych to struktura danych, która przyspiesza wyszukiwanie rekordów. Zamiast przeszukiwać całą tabelę, baza danych może szybko odnaleźć interesujące nas dane, trafiając bezpośrednio do odpowiedniego miejsca w strukturze indeksu.
Przykład zapytania, które skorzysta z indeksu (na podstawie utworzonych powyżej tablic):
SELECT * FROM uzytkownicy WHERE email = 'bartek@example.com';
Możemy też to zobaczyć w poniższej analizie zapytania, gdzie w pierwszej linii mamy odwołanie się do indeksu utworzonego podczas tworzenia tablicy uzytkownicy.
* Analizę zapytania możemy wykonać dodając przed zapytaniem, polecenie explain lub explain analyze. Świetne polecenie do sprawdzania jak się zachowują nasze zapytania!
** Fragment Index Scan using uzytkownicy_email_key informuje, że został użyty indeks po kolumnie ’email’ oraz podana jest jego nazwa.
Natomiast poniższe zapytanie już nie skorzysta z żadnego indeksu i baza będzie wyszukiwać całą tabele, a nie tylko po jej fragmencie:
SELECT * FROM uzytkownicy WHERE imie = 'Bartek';
Co również widzimy po analizie zapytania, gdzie widać w pierwszej linii, że mamy skan sekwencyjny tablicy.
* Fragment Seq scan on uzytkownicy pokazuje skanowanie sekwencyjne tabeli.
Przy małych zbiorach danych nie jest to istotne, ale co się wydarzy, gdy będziemy pracować na dużej liczbie danych?
Rodzaje indeksów
Oprócz zwykłego indeksu w bazie danych, możemy tworzyć też specjalne indeksy, które cechują się dodatkowymi właściwościami.
Tworzenie podstawowego indeksu:
-- Przykład ogólny jak stworzyć indeks CREATE INDEX index_name ON table_name (column1, column2, ...); -- Konkretny przykład tworzenia indeksu na kolumnie 'imie' w tabeli uzytkownicy CREATE INDEX idx_uzytkownicy_imie ON uzytkownicy(imie);
Indeks unikalny (unique index)
Indeks unikalny służy do tego, żeby przyspieszyć wyszukiwanie danych i jednocześnie zapewnić ich unikalność. Idealnie sprawdza się na kolumnach takich jak email, login czy PESEL – czyli tam, gdzie nie chcemy duplikatów. Dzięki temu indeksowi nie tylko przyspieszysz zapytania, ale też zabezpieczysz się przed błędami logicznymi w danych.
-- Tworzenie unikalnego indeksu CREATE UNIQUE INDEX idx_uzytkownicy_email ON uzytkownicy(email); -- Wykorzystanie SELECT * FROM uzytkownicy WHERE email = 'ala@example.com';
I oczywiście analiza zapytania bierze taki indeks jako ten optymalny:
Indeks złożony (composite index)
Indeks złożony to indeks, który obejmuje więcej niż jedną kolumnę. Świetnie sprawdza się w zapytaniach, które filtrują lub sortują po kilku polach jednocześnie, np. uzytkownik_id i data_zamowienia. Warto pamiętać, że kolejność kolumn w indeksie ma znaczenie – działa najlepiej, gdy zapytanie zaczyna się od pierwszej kolumny z indeksu.
-- Tworzenie złożonego indeksu CREATE INDEX idx_zamowienia_uzytkownik_data ON zamowienia(uzytkownik_id, data_zamowienia); -- Wykorzystanie SELECT * FROM zamowienia WHERE uzytkownik_id = 1 AND data_zamowienia >= '2025-01-01';
A także w wyniku analizy zapytania dostajemy informację, że właśnie tego indeksu użył:
Indeks częściowy (partial index)
To indeks, który obejmuje tylko te wiersze, które spełniają określony warunek w klauzuli WHERE. Można sobie to tak tłumaczyć, że skoro tak często wyszukujemy po konkretnym warunki where i są to zawsze stałe parametry to dlaczego nie umieścić ich w indeksie – wtedy baza też bardziej wie co my chcemy osiągnąć tym zapytaniem i szybciej przeszukać odpowiednią część tabeli.
-- Tworzenie częściowego indeksu CREATE INDEX idx_zamowienia_2025 ON zamowienia(uzytkownik_id, kwota) WHERE data_zamowienia = '2025-01-01'; -- Wykorzystanie SELECT * FROM zamowienia WHERE data_zamowienia = '2025-01-01' AND uzytkownik_id = 1;
Wynik analizy:
Indeks pokrywający (covering index)
To specjalny przypadek indeksu, który zawiera wszystkie dane potrzebne do wykonania zapytania – zarówno te z WHERE, jak i te z SELECT. Dzięki temu baza nie musi zaglądać do tabeli, tylko odczytuje wszystko bezpośrednio z indeksu.
-- Tworzenie pokrywającego indeksu CREATE INDEX idx_covering_zamowienia ON zamowienia(uzytkownik_id, data_zamowienia, kwota); -- Wykorzystanie SELECT data_zamowienia, kwota FROM zamowienia WHERE uzytkownik_id = 1;
I także tutaj wynik analizy:
Wpływ indeksów na wydajność
Indeksy potrafią drastycznie przyspieszyć zapytania, które w przeciwnym razie musiałyby przeszukiwać całą tabelę. Bez indeksu baza wykonuje tzw. full table scan – czyli sprawdza każdy wiersz, żeby znaleźć dopasowanie. Przy tysiącu rekordów różnicy może nie widać. Ale przy milionie? Czasem robi się dramat.
Spójrzmy na przykład bez indeksu:
SELECT * FROM zamowienia WHERE data_zamowienia >= '2025-01-01';
Jeśli data_zamowienia nie ma indeksu, to nawet przy dużej tabeli (zamowienia) baza musi przejść po wszystkich wierszach i sprawdzić, czy dany rekord spełnia warunek. To tzw. full table scan – i im więcej danych, tym gorzej.
A teraz jak dodamy indeks i wywołamy to samo zapytanie:
CREATE INDEX idx_zamowienia_data ON zamowienia(data_zamowienia);
Tym razem baza skorzysta z indeksu i od razu „przeskoczy” do wierszy z datą spełniającą warunek, ignorując wszystko, co wcześniejsze. Efekt? Wielokrotnie szybsze wykonanie zapytania.
Koszt wprowadzania indeksu
Indeksy bardzo dobrze potrafią optymalizować zapytania. Natomiast dodawanie dodatkowych struktur do tablicy oczywiście musi ze sobą nieść też jakieś dodatkowe koszty:
- Zużywają miejsce – przy dużej liczbie indeksów potrafi to być gigabajtowa różnica.
- Spowalniają operacja na bazach takie jak INSERT / UPDATE / DELETE – każdy taki zapis musi także zaktualizować indeksy.
- Nie zawsze są potrzebne – nadmiar indeksów może pogorszyć wydajność zamiast ją poprawić.
Czy zapytanie wzięło mój indeks?
W tym punkcie skupimy się na kilku przykładach, w których sprawdzimy kilku przypadków użycia indeksów.
Przykład 1 – funkcje
Podczas projektowania indeksów, jednym z elementów, na które trzeba zwrócić uwagę, są bardziej rozbudowane zapytania i korzystają z funkcji.
Tworzymy zwykły indeks, dla którego chcemy wyszukać wszystkie osoby z danym nazwiskiem, ale przy tym używamy funkcji lower, żeby mieć pewność, że nie zostaną jakieś nazwiska pominięte przez wielkość znaków.
-- Tworzenie indeks do wyszukiwania po nazwisku CREATE INDEX idx_uzytkownicy_nazwisko ON uzytkownicy(nazwisko); -- Wykorzystanie SELECT * FROM uzytkownicy WHERE lower(nazwisko) = 'nowak';
Jaki będzie wynik zapytania? Będzie robione skan sekwencyjny po całej tabeli. Efekt? Został stworzony indeks, który jedyne co robi, niepotrzebnie zużywa pamięć i spowalnia pozostałe operacje na bazie.
To w takim razie jak się przed tym chronić? Można w indeksie dodać informację, że na kolumnie z nazwiskiem chcemy dodatkowy używać metody lower.
-- Tworzenie indeks do wyszukiwania po nazwisku razem z metodą lower CREATE INDEX idx_uzytkownicy_nazwisko_lower ON uzytkownicy(lower(nazwisko));
W wyniku czego dostaniemy już skanowanie po odpowiednim indeksie:
Przykład 2 – Za mało selektywne dane
Warto też zastanowić się, czy dany indeks ma w ogóle sens — szczególnie w przypadku kolumn o niskiej selektywności. Jeśli prawie wszystkie rekordy mają tę samą wartość, a tylko kilka się wyróżnia, to optymalizacja przez indeks może niewiele dać. W takiej sytuacji trzeba się poważnie zastanowić, jakie operacje będą najczęściej wykonywane na tej kolumnie — i czy faktycznie indeks nie będzie tylko dodatkowym balastem dla zapisu, a nie realnym zyskiem przy odczycie.
CREATE INDEX idx_uzytkownicy_kraj ON uzytkownicy(kraj); -- pobranie danych po kolumnie, którą stanowi 100% obecnie naszej tabeli SELECT * FROM uzytkownicy WHERE kraj = 'Polska';
W naszej sytuacji, gdzie wszystkie rekordy mają wartość 'Polska’ użycie indeksu wydaje się niepotrzebne. Ale – jak to zwykle bywa – przy projektowaniu trzeba brać pod uwagę różne czynniki. Dziś wszystkie wartości w kolumnie są takie same, ale nie ma gwarancji, że ktoś nie zacznie dodawać innych. W takim przypadku być może warto rozważyć stworzenie indeksu „na przyszłość”. Jak widzisz – wszystko zależy od konkretnego przypadku i kontekstu użycia.
Przykład 3 – Indeks bez pokrycia kolumn z SELECT
Mamy sytuację, w której chcemy wyciągnąć imię i nazwisko użytkownika na podstawie emaila:
SELECT imie, nazwisko FROM uzytkownicy WHERE email = 'ala@example.com';
Wydaje się, że odpowiednim indeksem będzie zrobienie go po kolumnie ’email’:
CREATE INDEX idx_uzytkownicy_email ON uzytkownicy(email);
Jakby nie patrzeć, indeks wygląda okej i wykonując zapytanie z takim indeksem baza powinna wziąć właśnie ten indeks. Natomiast zastanówmy się co w sytuacji jak tabela będzie miała bardzo dużo rekordów. Kolumny 'imię’ i 'nazwisko’ nie są w indeksie, to baza i tak musi zajrzeć do tabeli, mimo że rekord został znaleziony przez indeks. Jest na to nawet pojęcie: index scan + table lookup.
Tak, więc jeśli mamy do czynienia z dużą tabelą możliwe, że lepszym rozwiązaniem mógłby się okazać indeks pokrywający na wszystkich 3 kolumnach:
CREATE INDEX idx_uzytkownicy_email_covering ON uzytkownicy(email, imie, nazwisko);
Czego unikać podczas projektowania indeksów
Mimo że indeksy są potężnym mechanizmem, są tabele lub kolumny, na których nie zawsze utworzenie indeksu będzie miało sens. Złe decyzje w tym obszarze mogą nie tylko nie pomóc, ale wręcz pogorszyć ogólną wydajność bazy danych.
- Zbyt wiele indeksów na jednej tabeli – ważne jest, żeby też nie przesadzać z liczbą indeksów na tabeli. Trzeba pamiętać, że każdy indeks niesie ze sobą w jakimś stopniu dodatkowe obciążenie na bazie. Przez co powinniśmy tworzyć indeksy tylko takie, które faktycznie nam się przydadzą.
- Indeks na kolumnie z dużą zmiennością danych – Jeśli masz kolumnę, która często się zmienia np. kolumna z datą ostatniej aktualizacji rekordu – to trzeba się liczyć z tym, że każda taka aktualizacja będzie wymagało również modyfikacji indeksu. Przy dużej liczbie operacji zapisu indeksu mogę stać się naszym wąskim gardłem, a sam zysk z założonego indeksu może tego nie rekompensować.
- Indeks na małej tabeli – dla bardzo małych tabel liczących kilkadziesiąt rekordów tworzenie indeksu może być w ogóle nieopłacalne. Może się okazać, że skanowanie sekwencyjne tabeli będzie szybsze niż wykorzystania indeksu.
Podsumowanie
Indeks w bazach danych to bardzo użyteczny mechanizm optymalizacji zapytań SQL. Dobrze zaprojektowane indeksy potrafią diametralnie zmienić wydajność. Ale oczywiście można równie dobrze źle zaprojektować indeksy i wtedy dostaniemy odwrotny efekt.
Zanim więc dodasz kolejny indeks „na wszelki wypadek”, sprawdź zapytanie przez EXPLAIN lub EXPLAIN ANALYZE . Monitoruj, analizuj, testuj. I pamiętaj — indeks ma sens tylko wtedy, gdy faktycznie jest używany.
