0Pricing
SQL Interview Prep · Lekcja

Kiedy indeksy szkodzą: zapisy i selektywność

Amplifikacja zapisów i wyjaśnienie, dlaczego indeks na kolumnie o niskiej selektywności jest bezużyteczny.

Kiedy indeksy szkodzą: zapisy i selektywność to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Pytanie kryjące się za pytaniem

Po trzech lekcjach o tym, dlaczego indeksy pomagają, rekruterzy odwracają pytanie: „Dlaczego po prostu nie zindeksować każdej kolumny?” Dobry kandydat wyjaśnia, że indeksy mają rzeczywiste koszty — dotyczące zapisów oraz pamięci podręcznej i miejsca na dane — a niektórych indeksów planista może nigdy nie użyć.

W tej lekcji omówimy dwa główne powody, dla których indeks może szkodzić: amplifikację zapisów i niską selektywność.

Każdy indeks spowalnia zapisy

Indeks musi pozostawać zsynchronizowany z tabelą. Każda operacja INSERT, każda operacja DELETE oraz każda operacja UPDATE dotycząca indeksowanej kolumny musi również aktualizować strukturę indeksu. To właśnie amplifikacja zapisów: jedna zmiana wiersza oznacza jeden zapis do tabeli oraz po jednym zapisie do każdego zmienianego indeksu.

Tabela z ośmioma indeksami wykonuje w przybliżeniu dziewięć razy więcej pracy związanej z zapisami niż tabela bez indeksów. W przypadku tabel intensywnie modyfikowanych lub obsługujących duży przepływ danych jest to poważny koszt.

Przykład: koszt zapisów

Wyobraźmy sobie tabelę zdarzeń, do której trafiają tysiące wierszy na sekundę. Każdy dodatkowy indeks sprawia, że każda operacja wstawiania wymaga więcej pracy: dzielenia stron indeksu, aktualizowania liści i konkurowania o pamięć podręczną.

W przypadku tabeli tylko do dopisywania danych, zdominowanej przez zapisy, właściwą odpowiedzią jest często niewiele indeksów albo ich brak poza kluczem głównym, a intensywne odczyty należy wykonywać na replice lub w hurtowni danych.

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

Co oznacza selektywność

Selektywność określa, jak dobrze kolumna rozróżnia wiersze, czyli jaki ułamek wierszy pasuje do typowej wartości. Wysoka selektywność oznacza niewiele wierszy przypadających na jedną wartość, na przykład w przypadku adresu e-mail lub UUID. Niska selektywność oznacza wiele wierszy przypadających na jedną wartość, na przykład w przypadku wartości logicznej lub statusu z trzema możliwościami.

Indeksy przynoszą korzyści w przypadku kolumn o wysokiej selektywności, gdy wyszukiwanie eliminuje niemal wszystkie wiersze. W przypadku kolumn o niskiej selektywności często tak nie jest.

Dlaczego indeks o niskiej selektywności jest bezużyteczny

Załóżmy, że dla 90% użytkowników is_active ma wartość true. Wyszukiwanie za pomocą indeksu zwróciłoby 90% tabeli, a dla takiej liczby wierszy silnik musiałby wykonać osobne pobranie z heapu dla każdego wiersza — byłoby to wolniejsze niż jednokrotne sekwencyjne przeskanowanie tabeli.

Dlatego planista prawidłowo ignoruje indeks i wykonuje skan sekwencyjny. Indeks generuje wtedy tylko dodatkowy koszt zapisów i zajmuje miejsce, nie przynosząc żadnej korzyści przy odczycie.

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

Przybliżony próg

Praktyczna zasada, którą warto przytoczyć: gdy predykat pasuje do więcej niż około 5–20% wierszy tabeli, skan sekwencyjny zazwyczaj przewyższa skan indeksu, ponieważ losowe pobieranie z heapu kosztuje więcej niż sekwencyjne odczytywanie stron.

Dokładny punkt zmiany zależy od rozmiaru wierszy, buforowania i szybkości pamięci masowej, dlatego planista korzysta ze statystyk, a nie ze stałej wartości, aby podjąć decyzję.

Indeksy częściowe na ratunek

Jeśli zapytania dotyczą wyłącznie rzadkich wartości skośnej kolumny, indeks częściowy (Postgres) może indeksować tylko te wiersze — będzie mały, selektywny i tani w utrzymaniu.

Jeśli 1% zamówień ma status pending i właśnie te zamówienia są stale wyszukiwane, należy indeksować tylko je. Indeks pozostanie mały, a planista chętnie go użyje.

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Nieaktualne statystyki wprowadzają planistę w błąd

Optymalizator wybiera między indeksem a skanowaniem na podstawie statystyk kolumn. Jeśli statystyki są nieaktualne, na przykład po masowym załadowaniu danych lub dużej aktualizacji, może błędnie ocenić selektywność i wybrać niewłaściwy plan.

Gdy rekruter mówi: „Indeks istnieje, ale nie jest używany”, świetna odpowiedź obejmuje odświeżenie statystyk za pomocą ANALYZE, zanim obwini się sam indeks.

ANALYZE orders;  -- refresh planner statistics

Inne sposoby, na które indeksy szkodzą

Uzupełnijmy odpowiedź o mniej znane koszty:

  • Miejsce na dane i pamięć podręczna: indeksy zajmują miejsce na dysku i konkurują o pamięć, wypierając przydatne strony danych.
  • Nadmiarowe lub nakładające się indeksy: są utrzymywane, ale nigdy nie są wybierane.
  • Rozrost: przy intensywnych aktualizacjach drzewa B ulegają fragmentacji i wymagają wykonania REINDEX.
  • Dezorientacja optymalizatora: zbyt wiele podobnych indeksów spowalnia planowanie i sprawia, że jego wyniki są mniej przewidywalne.

Znajdowanie nieużywanych indeksów

Aby uzasadnić porządki w rzeczywistym systemie, warto wspomnieć, że Postgres śledzi użycie indeksów. Indeksy z idx_scan = 0 są kandydatami do usunięcia — zwiększają koszt zapisów i zajmują miejsce, choć nigdy nie obsługują odczytów.

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Jak ująć to podczas rozmowy kwalifikacyjnej

Pełne i wyważone podsumowanie:

„Indeksy powodują amplifikację zapisów — każda operacja wstawiania, aktualizacji lub usuwania musi je utrzymywać — a także zwiększają presję na miejsce i pamięć podręczną. Przynoszą korzyści tylko w przypadku predykatów o wysokiej selektywności; dla kolumny, w której pasuje większość wierszy, planista słusznie wybiera skan sekwencyjny, więc indeks jest wyłącznie dodatkowym kosztem. W przypadku skośnych kolumn sięgam po indeks częściowy, dbam o aktualność statystyk za pomocą ANALYZE i usuwam nieużywane indeksy”.

Szybki test

Zdecyduj, który indeks najprawdopodobniej nie jest wart swojego kosztu.

Podsumowanie: kiedy indeksy szkodzą

Najważniejsze informacje:

  • Każdy indeks zwiększa amplifikację zapisów oraz koszty miejsca na dane i pamięci podręcznej.
  • Indeksy pomagają w przypadku kolumn o wysokiej selektywności; dla kolumn o niskiej selektywności planista wybiera skan sekwencyjny.
  • Gdy pasuje więcej niż około 5–20% wierszy, skan zazwyczaj wygrywa.
  • W przypadku skośnych kolumn, dla których zapytania dotyczą tylko rzadkich wartości, należy używać indeksu częściowego.
  • Należy dbać o aktualność statystyk za pomocą ANALYZE i usuwać nieużywane indeksy (idx_scan = 0).

To kończy kurs dotyczący strategii indeksowania: należy tworzyć indeksy tam, gdzie przynoszą korzyści, i potwierdzać ich skuteczność za pomocą planu.

Często zadawane pytania

Czy lekcja „Kiedy indeksy szkodzą: zapisy i selektywność” jest bezpłatna?

Tak — pełny tekst „Kiedy indeksy szkodzą: zapisy i selektywność” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Kiedy indeksy szkodzą: zapisy i selektywność”?

Amplifikacja zapisów i wyjaśnienie, dlaczego indeks na kolumnie o niskiej selektywności jest bezużyteczny. Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Kiedy indeksy szkodzą: zapisy i selektywność”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Indeksy B-Tree i ich zastosowanie
  2. Kolejność kolumn w indeksie złożonym
  3. Indeksy pokrywające i skanowanie wyłącznie indeksu
  4. Kiedy indeksy szkodzą: zapisy i selektywność
← Powrót do SQL Interview Prep