Powolny SELECT, który wcześniej działał w 20 milisekund, nagle zaczyna się zawieszać na 12 sekund. Baza urosła z 50 tysięcy wierszy do 5 milionów i każde zapytanie zamieniło się w loterię. Brzmi znajomo?

Indeksy, rzecz, o której wszyscy słyszeli, ale mało kto konfiguruje je świadomie. Dodał Pan kilka sztuk, niby przyspieszyło, i zapomniał. A po pół roku INSERT hamuje bardziej niż SELECT bez indeksu, ponieważ każde wstawienie przebudowuje pięć niepotrzebnych B-drzew.

Rozłożymy na czynniki pierwsze, jak indeksy naprawdę działają, jakie są ich typy i, co najważniejsze, według jakich zasad je budować, żeby nie narobić szkód na produkcji.

💡 Szybki przegląd:

• Zrozumieć mechanikę indeksów B-tree, hash i złożonych, bez tego wybór właściwego typu jest niemożliwy
• Opanować 7 kluczowych praktyk: od indeksowania kluczy obcych po usuwanie nieużywanych indeksów
• Nauczyć się czytać EXPLAIN i odróżniać działający indeks od niepotrzebnego

Czym jest indeks bazy danych

Indeks bazy danych to osobna struktura, która przechowuje posortowane wartości jednej lub kilku kolumn tabeli wraz ze wskaźnikami do wierszy. Kiedy wykonuje Pan SELECT ... WHERE category_id = 42, serwer bez indeksu skanuje tabelę wiersz po wierszu (full table scan). Z indeksem znajduje potrzebne rekordy w czasie O(log n), jak w książce telefonicznej.

Indeks tworzy się przez CREATE INDEX:

1
-- Обычный индекс
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Уникальный индекс
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Ale ceną za szybkość odczytu jest spowolnienie zapisu. Każdy INSERT, UPDATE i DELETE musi zaktualizować nie tylko tabelę, ale i wszystkie powiązane indeksy. Trzy indeksy na tabeli z milionem wierszy i masowe wstawianie spada o rząd wielkości. Równowaga między szybkością odczytu i zapisu to centralne pytanie projektowania indeksów.

Szczegółowe omówienie tematu od Husseina Nassera: 497 tysięcy subskrybentów, inżynierskie podejście bez lania wody. Na przykładzie PostgreSQL pokazana jest wewnętrzna mechanika indeksów, dlaczego jeden CREATE INDEX przyspiesza zapytanie 100 razy, a inny nie daje nic.

Typy indeksów: który kiedy stosować

Wybór typu indeksu decyduje o tym, jak efektywnie baza przetworzy Pana zapytanie. Różne SZBD implementują je na swój sposób, ale zasady są wspólne.

B-tree (zrównoważone drzewo)

Standardowy typ w większości relacyjnych SZBD, PostgreSQL, MySQL, Oracle i SQL Server używają B-tree jako indeksu domyślnego. Przechowuje klucze w posortowanej kolejności, obsługuje operacje porównania, zakresy (BETWEEN), wyszukiwanie prefiksowe (LIKE 'prefix%') i sortowanie, najlepszy wybór w zdecydowanej większości przypadków.

Indeks hash

Działa tylko dla dokładnego porównania =. Błyskawiczny przy zapytaniach punktowych, ale bezużyteczny dla zakresów i sortowania. W PostgreSQL indeksy hash są gotowe do użycia produkcyjnego od wersji 10; w MySQL nie ma ich w InnoDB (tylko w MEMORY).

Indeks klastrowy

Określa fizyczną kolejność wierszy na dysku. W MySQL InnoDB klucz główny jest zawsze klastrowy, wiersze są przechowywane w kolejności PRIMARY KEY. W SQL Server jeden indeks klastrowy na tabelę. Właściwy wybór klucza klastrowego (monotonicznie rosnący: BIGSERIAL, AUTO_INCREMENT lub UNIQUEIDENTIFIER z NEWSEQUENTIALID()) daje zysk przy zapytaniach zakresowych i zapobiega fragmentacji stron.

Indeks złożony

Indeks na dwóch lub więcej kolumnach. Krytyczny dla zapytań z filtrowaniem i sortowaniem po wielu kolumnach:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Kolejność kolumn ma znaczenie: jako pierwszą umieszcza się kolumnę o najwyższej selektywności, używaną w WHERE. Zasada najbardziej lewego prefiksu: indeks (A, B, C) działa dla WHERE A i WHERE A AND B, ale nie dla WHERE B AND C.

Indeks pokrywający

Zawiera wszystkie kolumny potrzebne zapytaniu, zarówno do filtrowania, jak i do wyświetlenia. Baza pobiera wszystko z indeksu, nie zaglądając do tabeli. W PostgreSQL są to kolumny INCLUDE, w MySQL InnoDB niejawne pokrycie przez indeks klastrowy.

7 Kluczowych praktyk indeksowania

1. Indeksuj kolumny z WHERE

Pierwsza zasada: każda kolumna regularnie używana w WHERE, JOIN ... ON i HAVING powinna być zindeksowana. To właśnie te operacje zyskują na indeksach najwięcej.

Przed utworzeniem indeksu proszę sprawdzić selektywność kolumny. Jeśli w kolumnie status są tylko trzy wartości ('new', 'processing', 'done'), a wierszy jest 2 miliony, indeks na status jest prawie bezużyteczny: planer wybierze full table scan jako tańszy. Indeks ma sens, gdy liczba unikalnych wartości jest wystarczająco duża w stosunku do rozmiaru tabeli.

2. Indeksuj kolumny sortowania

ORDER BY bez indeksu to filesort (MySQL) lub explicit sort (PostgreSQL): serwer zbiera wszystkie wiersze i sortuje w pamięci (lub na dysku, jeśli work_mem/sort_buffer_size są małe). Indeks na tych samych kolumnach co ORDER BY jest darmowy, dane są już posortowane w B-drzewie.

1
-- Без индекса по created_at: filesort на миллионах строк
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Индекс решает проблему
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indeksuj GROUP BY i kolumny agregatów

Grupowanie bez indeksu wymaga pełnego skanowania i budowy tablicy mieszającej. Indeks na kolumnach GROUP BY zamienia operację na agregację strumieniową: wiersze są już zgrupowane w kolejności klucza.

4. Indeksuj wszystkie klucze obce

Niezindeksowany klucz obcy to bomba z opóźnionym zapłonem. DELETE FROM users WHERE id = 5 przy istnieniu FOREIGN KEY (user_id) REFERENCES users(id) w tabeli orders bez indeksu na user_id to full table scan orders przy każdym usunięciu. Wszystkie popularne SZBD wymagają indeksu na klucz obcy lub tworzą go niejawnie (MySQL InnoDB automatycznie, PostgreSQL nie).

5. Indeksuj unikalne kolumny i klucze główne

Klucz główny jest indeksowany automatycznie (często indeksem klastrowym). Jawny UNIQUE INDEX chroni przed duplikatami i jednocześnie przyspiesza wyszukiwanie. Każda kolumna z biznesowym ograniczeniem unikalności (na przykład email, slug lub external_id) musi mieć unikalny indeks, zarówno dla integralności, jak i dla wydajności.

6. Używaj indeksu klastrowego świadomie

Dla dużych tabel (dziesiątki milionów wierszy) właściwy klucz klastrowy jest krytyczny. Dobry wybór to wartość monotonicznie rosnąca: AUTO_INCREMENT, BIGSERIAL lub UUID v7. Losowy UUID jako klucz klastrowy powoduje fragmentację stron, każde wstawienie trafia w losowe miejsce B-drzewa, rozbijając wypełnione strony na dwie niepełne.

7. Usuwaj nieużywane indeksy

Indeks, który nie jest używany w zapytaniach, to czysta strata. Spowalnia zapis, zajmuje miejsce na dysku i w puli buforów, dezorientuje planer zapytań. W PostgreSQL listę nieużywanych indeksów daje tabela systemowa pg_stat_user_indexes:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

W MySQL analogiczną informację dostarcza sys.schema_unused_indexes (od wersji 5.7). Proszę zaplanować comiesięczny audyt i usuwać indeksy, które nie były użyte ani razu w okresie raportowym.

Jak sprawdzić, czy Pana indeksy działają

Utworzył Pan indeks, proszę sprawdzić, czy jest rzeczywiście stosowany. Polecenie EXPLAIN (lub EXPLAIN ANALYZE) pokazuje plan wykonania zapytania i faktyczne użycie indeksów:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

W wynikach proszę szukać wierszy Index Scan lub Index Only Scan (PostgreSQL) / Using index (MySQL). Jeśli widzi Pan Seq Scan (PostgreSQL) lub Using where; Using filesort (MySQL), indeks nie jest używany. Przyczyny: niska selektywność, nieodpowiedni typ indeksu, niezgodność kolejności kolumn w indeksie złożonym, nieaktualne statystyki (ANALYZE table_name;).

Proszę regularnie monitorować metryki: pg_stat_user_indexes.idx_scan w PostgreSQL, sys.schema_index_statistics w MySQL. Indeks z zerową liczbą skanowań w ciągu miesiąca to kandydat do usunięcia.

⁉️🤔 Częste pytania

Ile indeksów powinno być w tabeli?

Optymalnie od 2 do 6 indeksów na aktywnie używaną tabelę. Mniej niż dwa, prawie na pewno istnieją nieoptymalne zapytania. Więcej niż sześć, proszę dokładnie sprawdzić, czy wszystkie są naprawdę potrzebne: każdy zbędny indeks spowalnia zapis. Dla tabel słownikowych (rzadki zapis, częsty odczyt) więcej indeksów jest uzasadnione. Dla wysoko obciążonych tabel operacyjnych (dużo INSERT/UPDATE) proszę trzymać minimum.

Czym indeks złożony jest lepszy od kilku pojedynczych?

Indeks złożony (A, B) to JEDNA struktura. Serwer przechodzi przez nią jeden raz. Trzy oddzielne indeksy (A), (B) i (C) przy WHERE A=1 AND B=2 zmuszają serwer albo do wybrania jednego indeksu (i filtrowania reszty), albo do wykonania bitmap index scan (łączenia map bitowych). Indeks złożony jest prawie zawsze efektywniejszy, jeśli kolejność kolumn odpowiada zapytaniom.

Kiedy indeks nie pomaga, a przeszkadza?

Trzy typowe przypadki. Pierwszy: tabela jest mała (do kilku tysięcy wierszy), full table scan jest szybszy niż odczyt indeksu plus dobór wierszy. Drugi: indeks na kolumnie o niskiej selektywności (is_deleted, status z trzema wartościami). Trzeci: masowe wstawiania w ETL/imporcie, indeksy są przebudowywane przy każdym batchu, proszę je usunąć przed załadowaniem i utworzyć ponownie po.

Czy trzeba indeksować kolumny dla JOIN?

Obowiązkowo. Każdy JOIN bez indeksu na kolumnie łączącej tabeli zewnętrznej to nested loop z pełnym skanowaniem. Dla LEFT JOIN orders ON users.id = orders.user_id indeks na orders.user_id zamienia nested loop na index lookup. Proszę indeksować kolumny, po których następują złączenia, zawsze.

B-tree czy hash: co wybrać do dokładnego wyszukiwania?

Dla = hash jest szybszy: wyszukiwanie po hashu zajmuje stały czas, podczas gdy B-tree przechodzi przez drzewo w logarytmicznej liczbie kroków. Ale hash nie obsługuje zakresów, sortowania i UNIQUE. W praktyce B-tree pokrywa zdecydowaną większość scenariuszy; hash to niszowe narzędzie do punktowych lookupów po kluczu w wysoko obciążonych systemach (sesje, cache). W PostgreSQL indeksy hash od wersji 10 są gotowe do użycia produkcyjnego i zajmują mniej miejsca niż B-tree.

Czy warto indeksować „na wszelki wypadek"?

Nie. Każdy indeks to kompromis. Przyspiesza odczyt kosztem spowolnienia zapisu i dodatkowej przestrzeni dyskowej. Proszę indeksować nie „na wszelki wypadek", a pod konkretne zapytania, które rzeczywiście są wykonywane w aplikacji. Proszę profilować wolne zapytania (pg_stat_statements, slow_query_log), dodawać indeksy punktowo i sprawdzać EXPLAIN przed i po.

Główny wniosek jest prosty: indeksy to narzędzie, a nie cel sam w sobie. Jeden poprawny indeks złożony może zastąpić trzy pojedyncze i zaoszczędzić gigabajty dysku. A jeden nieużywany indeks na tabeli z intensywnym zapisem może spowolnić całą aplikację.

Jeśli chce Pan zgłębić temat, proszę zacząć od oficjalnego przewodnika po projektowaniu indeksów SQL Server i dokumentacji PostgreSQL o typach indeksów. A jeśli napotkał Pan zapytanie, którego nie da się wyleczyć indeksami, być może problem leży w samym modelu danych.