Indeksy w SQL: teoria i praktyka

Indeksy działają jak spis treści w książce: pozwalają bazie danych pominąć nieistotne fragmenty i szybciej dotrzeć do wyniku. Nie są jednak darmowe — zwiększają koszty zapisu, zajmują miejsce i mogą pogorszyć wydajność, jeśli są tworzone bez planu.

W tym artykule przejdziesz przez teorię i praktykę: dowiesz się, jak działają różne rodzaje indeksów w PostgreSQL i MySQL, jak sprawdzić, czy Twoje zapytanie faktycznie z nich korzysta, i jakie błędy popełniają osoby, które tworzą „indeks na wszystko”.

Jak działają indeksy w relacyjnych bazach danych

Indeksy w SQL to dodatkowe, zwykle posortowane struktury danych (najczęściej B-Tree), które mapują klucz na wskaźnik wiersza. Przy selektywnych filtrach redukują liczbę odczytów z dysku/pamięci z O(n) do O(log n). W PostgreSQL indeks wskazuje TID w heapie; w InnoDB (MySQL) indeks główny jest klastrowany, więc dane są fizycznie posortowane po PRIMARY KEY, a indeksy wtórne przechowują wartości klucza głównego. Wysoka kardynalność i dobra selektywność to podstawa skuteczności.

Planner wybiera strategię na podstawie statystyk: skan sekwencyjny, Index Scan/Seek, Bitmap Index Scan lub Index Only Scan (gdy zapytanie jest “covering” i widoczność wierszy jest potwierdzona). To właśnie plan wykonania SQL pokaże, czy indeks pomaga, czy jest pomijany. Dla podstaw mechaniki zapytań zobacz: Podstawy SQL: SELECT, WHERE, JOIN, GROUP BY.

Kiedy warto tworzyć indeks

Indeks twórz, gdy kolumna (lub zestaw kolumn) często występuje w WHERE/JOIN i ma wysoką selektywność. Jeśli filtrujesz po A i sortujesz po B, rozważ indeks złożony (A, B). Indeks na kluczu obcym przyspiesza JOIN i DELETE/UPDATE po stronie podrzędnej. Gdy rozkład danych jest skośny, rozważ indeks częściowy (partial index) obejmujący np. tylko aktywne rekordy.

Unikaj indeksowania kolumn o bardzo niskiej kardynalności (np. boolean), chyba że wspierają selektywny predykat z dodatkowymi kolumnami. Unique index egzekwuje integralność i przyspiesza wyszukiwanie po kluczu naturalnym. Pamiętaj, że każdy indeks spowalnia INSERT/UPDATE/DELETE, więc decyzję podejmuj na podstawie realnych profili obciążenia.

Rodzaje indeksów w PostgreSQL i MySQL

W PostgreSQL domyślny jest B-Tree (uniwersalny do =, <, >, ORDER BY). Są też Hash (czyste równości), GIN (tablice, full-text, JSONB), GiST (przedziały, KNN, dane przestrzenne) i BRIN (bardzo duże, “naturalnie posortowane” tabele). Dostępne są indeksy częściowe (WHERE), wyrażeniowe (na funkcjach), unikalne oraz “covering” przez INCLUDE, co pomaga w Index Only Scan. Więcej o konkretnych strukturach: Indeksy w PostgreSQL: B-Tree, Hash, GIN, GiST i BRIN.

W MySQL (InnoDB) PRIMARY KEY to klastrowany indeks; indeksy wtórne przechowują wartości PK zamiast wskaźników stron. Najczęściej używany jest B-Tree; dodatkowo dostępne są FULLTEXT i SPATIAL. Dla długich VARCHAR stosuje się prefix index w MySQL (np. (email(10))). W obu systemach kluczowe “rodzaje indeksów w SQL” to: indeks złożony (composite index), unikalny (unique index), częściowy (w PostgreSQL), oraz indeksy specjalistyczne jak b-tree, hash, gin, gist, brin.

Jak sprawdzić, czy indeks jest używany

Najpewniejsza metoda to explain sql: w PostgreSQL użyj EXPLAIN ANALYZE, aby zobaczyć rzeczywisty czas i operator (Index Scan/Only Scan/Bitmap). W MySQL EXPLAIN/EXPLAIN FORMAT=JSON pokaże typ dostępu (const, ref, range, index, all) oraz klucz użyty przez optymalizator. Analizuj selektywność predykatów i dopasowanie do kolejności kolumn w indeksie.

Do statystyk: w PostgreSQL sprawdź pg_stat_user_indexes (kolumny idx_scan, idx_tup_read) oraz powiąż z pg_stat_statements, by znaleźć najcięższe zapytania. W MySQL pomocne są performance_schema oraz widok sys.schema_unused_indexes, ale interpretuj ostrożnie. Połączenie metryk i planu zapytania explain analyze daje pełny obraz. Szerzej o diagnozie: Optymalizacja zapytań SQL i analiza planów wykonania.

Jakie są koszty nadmiernego indeksowania

Każdy dodatkowy indeks to koszt zapisu: INSERT/UPDATE/DELETE muszą modyfikować wiele struktur, co zwiększa zużycie CPU, I/O i write-ahead log. Rośnie też przestrzeń dyskowa i presja na pamięć podręczną. W InnoDB indeksy wtórne duplikują klucz główny, co dodatkowo zwiększa rozmiar. W PostgreSQL więcej indeksów oznacza więcej pracy dla VACUUM/ANALYZE i częstsze REINDEX przy fragmentacji.

Nadmierna liczba indeksów może utrudnić optymalizatorowi wybór, wydłużyć migracje (ALTER TABLE/CREATE INDEX CONCURRENTLY) i zwiększyć ryzyko blokad. Lepiej mieć mniej, dobrze dobranych indeksów niż “na wszelki wypadek”.

Przykłady praktyczne z EXPLAIN

Poniżej minimalny, działający przykład pokazujący wpływ indeksu złożonego i sortowania. W PostgreSQL uzyskasz Index Only Scan, jeśli zapytanie jest “covering” i widoczność to umożliwia; w MySQL właściwy porządek kolumn pozwala na range scan bez sortowania.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- PostgreSQL
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  amount NUMERIC(12,2) NOT NULL
);

-- Indeks dopasowany do filtra i sortowania
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- Zapytanie korzystające z prefiksu indeksu i unikające sortowania
EXPLAIN ANALYZE
SELECT user_id, created_at, amount
FROM orders
WHERE user_id = 123 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

-- Indeks częściowy (tylko aktywne zamówienia)
CREATE INDEX idx_orders_active_partial ON orders (created_at) WHERE status = 'ACTIVE';

-- MySQL (InnoDB)
CREATE TABLE orders_mysql (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  amount DECIMAL(12,2) NOT NULL
) ENGINE=InnoDB;

CREATE INDEX idx_orders_user_created_mysql ON orders_mysql (user_id, created_at);

EXPLAIN FORMAT=JSON
SELECT user_id, created_at, amount
FROM orders_mysql
WHERE user_id = 123 AND created_at >= NOW() - INTERVAL 30 DAY
ORDER BY created_at DESC
LIMIT 20;

Zwróć uwagę na kolejność kolumn: (user_id, created_at) obsługuje warunek równości i zakres czasu oraz pozwala uniknąć dodatkowego sortowania. W MySQL oraz PostgreSQL efekt zobaczysz jako range/Index Scan w planie wykonania SQL. Różnice mechaniki (np. klastrowany indeks InnoDB) omówiłem w: PostgreSQL vs MySQL: różnice, które mają znaczenie.

Rekomendacje i dobre praktyki

Projektuj indeksy do konkretnych, krytycznych zapytań i weryfikuj je przez explain sql oraz rzeczywiste czasy. Dbaj o kolejność w indeksie złożonym: najpierw kolumny równościowe, potem zakresowe, na końcu te do ORDER BY. Rozważ indeks częściowy w PostgreSQL przy silnie skośnych danych i “INCLUDE”, by osiągnąć Index Only Scan. W MySQL używaj prefix index dla długich tekstów i pamiętaj, że każdy indeks wtórny przechowuje klucz główny.

Regularnie aktualizuj statystyki (ANALYZE), w PostgreSQL utrzymuj VACUUM, monitoruj użycie przez pg_stat_* i performance_schema/sys. Usuwaj nieużywane lub duplikujące się indeksy, a nowe dodawaj iteracyjnie na podstawie planu zapytania i realnych metryk obciążenia. Indeksy w SQL są potężne, ale tylko wtedy, gdy są precyzyjnie dopasowane do wzorców dostępu.