Indeksowanie SQLExplained

31 of 31 rows

Pojęcie↕	Wyjaśnienie↕	Wskazówka↕
Typy indeksów
⤢B-tree	Zbalansowane drzewo dla porównań równościowych, zakresów i posortowanych skanów. Domyślne w większości baz danych.	Używaj dla =, <, >, BETWEEN, ORDER BY. Nie do wyszukiwania pełnotekstowego.
⤢Clustered	Indeks, którego poziom liści to sama tabela. Wiersze są przechowywane w kolejności klucza. Jeden na tabelę.	InnoDB klastrowuje po kluczu głównym. Tabele SQL Servera bez niego to kopce (heaps).
⤢Nonclustered	Osobna struktura przechowująca klucze plus wskaźnik z powrotem do tabeli bazowej. Wiele na tabelę.	Na kopcu przechowuje identyfikator wiersza. Na tabeli klastrowanej przechowuje klucz klastrowania.
⤢Hash	Tablica haszująca wyłącznie do wyszukiwań równościowych. Dopasowanie dokładne w stałym czasie.	Używaj tylko do operacji =; zakresy i sortowanie wymagają B-tree. Haszowy indeks tabel zoptymalizowanych pod pamięć w SQL Serverze potrzebuje liczby kubełków bliskiej liczbie wierszy.
⤢GIN	Generalized Inverted Index. Mapuje wartości na wiersze. Zaprojektowany dla tablic i wyszukiwania pełnotekstowego.	Używaj dla jsonb, tablic, tsvector i wyszukiwania trigramowego. Wolniejszy w budowaniu niż B-tree.
⤢GiST	Generalized Search Tree. Rozszerzalny dla typów nieskalarnych i zapytań o nakładanie się.	Używaj dla zakresów geometrycznych, kNN i ograniczeń wykluczających. pg_trgm przyspiesza LIKE.
⤢SP-GiST	Space-partitioned GiST. Dzieli dane na nienakładające się regiony przez drzewa radix lub czwórkowe.	Używaj dla punktów, prefiksów IP i dopasowywania prefiksów. Nie do zapytań o nakładanie; użyj GiST.
⤢BRIN	Block Range Index. Przechowuje min/maks na zakres bloków. Bardzo mały ślad.	Używaj na dużych, fizycznie posortowanych tabelach. Tani w budowaniu, słaba selektywność.
⤢Columnstore	Indeks przechowywany jako segmenty kolumn zamiast wierszy. Wysoka kompresja, wykonanie wsadowe.	Używaj do skanów analitycznych po wielu wierszach i kilku kolumnach. Wyszukiwanie pojedynczych wierszy jest wolne.
⤢Full-text	Dedykowany odwrócony indeks słów w MySQL i SQL Serverze. Budowany per kolumna tekstowa.	Używaj MATCH AGAINST lub CONTAINS. Pełnotekstowy Postgres używa zamiast tego GIN na tsvector.
⤢Bitmap	Indeks Oracla przechowujący po jednej bitmapie na każdą wartość. Każdy bit oznacza jeden wiersz.	Używaj na kolumnach o niskiej kardynalności w hurtowniach zdominowanych przez odczyty. Równoległe zapisy są serializowane per bitmapa.
⤢Descending	Kolumna indeksu zadeklarowana jako DESC. Klucze tej kolumny są przechowywane w odwrotnej kolejności.	Potrzebne dla sortowań mieszanych jak (a ASC, b DESC). Zwykłe sortowania DESC mogą też czytać zwykły indeks od tyłu.
⤢Invisible	Indeks MySQL 8 ignorowany przez optymalizator. Nadal utrzymywany przy każdym zapisie.	Ukryj indeks przed usunięciem, by przetestować wpływ. Przywróć widoczność bez przebudowy.
Projektowanie
⤢Composite	Indeks wielokolumnowy. Kolumny wiodące muszą odpowiadać kolejności filtrów zapytania.	Najpierw kolumny równościowe, potem zakresowe. Dwa pojedyncze indeksy nie zastąpią jednego złożonego.
⤢Partial	Indeks na predykacie WHERE. SQL Server nazywa go indeksem filtrowanym. Obejmuje tylko pasujące wiersze.	Indeksuj tylko wiersze z active='t'. O połowę mniejszy rozmiar i koszt zapisu.
⤢Covering (INCLUDE)	B-tree z dołączonymi dodatkowymi kolumnami spoza klucza. Skany wyłącznie z indeksu unikają odwołań do kopca.	Dodaj często pobierane kolumny przez INCLUDE. Indeks pozostaje sortowalny.
⤢Unique	Indeks wymuszający brak duplikatów wartości. Odrzuca wstawienia powodujące kolizję.	Używaj dla kluczy naturalnych i ograniczeń jeden-do-jednego. UNIQUE z NULL dopuszcza wiele wartości NULL.
⤢Expression	Indeks zbudowany na funkcji kolumn zamiast na samych kolumnach.	Indeksuj lower(email) lub date_trunc('day', created_at). Zapytanie musi powtórzyć dokładnie to samo wyrażenie.
⤢Opclass	Klasa operatora. Definiuje, jak indeks porównuje i przechowuje typ danej kolumny.	Ustaw varchar_pattern_ops dla LIKE 'abc%' bez zwijania wielkości liter. Wartości domyślne pasują do równości i zakresów.
⤢Index-only scan	Plan, który odpowiada na zapytanie wyłącznie ze stron indeksu. Kopiec nigdy nie jest czytany.	Wymaga każdej pobieranej kolumny w indeksie lub na liście INCLUDE. Vacuum utrzymuje aktualność visibility map.
⤢Foreign keys	Kolumny kluczy obcych nie dostają automatycznego indeksu w tabeli odwołującej.	Indeksuj każdą kolumnę klucza obcego, przez którą łączysz lub usuwasz. Kasowanie kaskadowe bez indeksu skanuje tabelę podrzędną.
Utrzymanie
⤢EXPLAIN	Inspektor planów. Pokazuje skany sekwencyjne, wybory indeksów i szacunki kosztów.	Używaj EXPLAIN (ANALYZE, BUFFERS). Seq Scan na tabeli 10 mln wierszy oznacza brakujący indeks.
⤢ANALYZE	Próbuje kolumn tabeli, by zbudować statystyki planera. Histogramy decydują o wyborze indeksu.	Uruchom po masowych załadunkach. Nieaktualne statystyki powodują seq scan na indeksowanych kolumnach.
⤢Over-indexing	Każdy indeks zwiększa koszt zapisu. Inserty, update'y i delete'y płacą za każdy indeks.	Usuwaj nieużywane indeksy. Śledź użycie w pg_stat_user_indexes.
⤢pg_stat_user_indexes	Widok Postgresa o użyciu każdego indeksu. Zlicza skany i odczytane krotki od ostatniego resetu statystyk.	Filtruj po idx_scan = 0, by znaleźć nieużywane indeksy. Zeruj liczniki przez pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Buduje indeks bez blokowania odczytów ani zapisów. Wymaga dwóch skanów tabeli.	Nie może działać wewnątrz transakcji. Nieudane budowanie zostawia indeks INVALID do usunięcia.
⤢DROP INDEX	Usuwa indeks i zwalnia jego miejsce. Domyślnie bierze blokadę wyłączną.	Używaj DROP INDEX CONCURRENTLY na obciążonych tabelach. Sprawdź statystyki użycia przed usunięciem.
⤢REINDEX	Przebudowuje rozdęty indeks. Odzyskuje miejsce po martwych krotkach.	Używaj REINDEX CONCURRENTLY na produkcji. Bez tego blokady zatrzymują zapisy.
⤢Bloat	Martwe krotki i luki stron kumulujące się w tabelach MVCC i ich indeksach.	Mierz za pomocą pgstattuple. Często vacuum; REINDEX, gdy martwe miejsce dominuje.
⤢Fillfactor	Procent strony pakowany w chwili zapisu. Wolne miejsce pochłania przyszłe aktualizacje.	Obniż do 70-90 w tabelach z licznymi update'ami. Pełne strony wymuszają podziały stron.
⤢CLUSTER	Przepisuje tabelę fizycznie uporządkowaną według indeksu. Operacja jednorazowa.	Łączy się z BRIN dla posortowanych bloków. Kolejność nie jest utrzymywana przy kolejnych zapisach.