Optymalizacja indeksowania i zapytania

1) Indeksowanie i optymalizacja celów

Opóźnienie: zmniejszenie P50/P95/P99.
Przepustowość: wzrost QPS bez skala-out.
Przewidywalność: stabilne plany i brak „skoków” w czasie reakcji.
Oszczędności: Mniej IO/CPU, mniej rachunku w chmurze.
Niezawodność: zmniejszenie blokad i impulsów ze względu na prawidłowy dostęp.

• Każda optymalizacja musi zachować poprawność i spójność.
• Śledź efekt w metrykach i logach planowania.

2) Podstawowe struktury indeksu i kiedy je stosować

2. 1 Drzewo B (domyślnie)

Równe/zakresy, sort, 'ORDER BY'.
Dobre dla większości filtrów czasu/ID/stanu.

2. 2 Hash

Czyste równości ('='), tańsze w pamięci, ale nie w porządku (PG: ograniczenia usunięte, ale nadal wybór niszowy).

2. 3 GIN/GiST (PostgreSQL)

GIN: tablice/klawisze JSONB, pełny tekst (tsvector), przechowywanie ('@>').
GiST: geo, zakresy, kNN.

2. 4 BRIN (PostgreSQL)

Super-tani indeks według „naturalnie sortowanych” tabel (dodatek tylko po czasie). Dobre dla serii czasowych z dużymi stołami.

2. 5 Bitmap (MySQL/InnoDB: żaden z rodzimych; DW-DBMS/OLAP)

Skuteczny dla niskiej kardynalności i aspektów, częściej w przechowywaniu kolumn.

2. 6 indeksów kolumn (ClickHouse)

Klucz podstawowy + pominięcie danych (minmax), wtórne „skip indexes” (bloom, set).
Zapytania OLAP z agregacjami i zakresami.

2. 7 Odwrócone indeksy (Elasticsearch/OpenSearch)

Pełny tekst, fakty, wyszukiwanie hybrydowe. Dla precyzyjnych filtrów użyj pól słów kluczowych i wartości doc.

2. 8 MongoDB

Pojedynczy, złożony, multikey (tablice), częściowy, TTL, tekst, hashed (dla jednolitego shading klucza).

3) Kluczowe i złożone wzornictwo indeksu

3. 1 Reguła lewego przedrostka

Kolejność pól w indeksie określa użyteczność.
Zapytanie 'GDZIE tenant_id =? I created_at> =? ZAMÓWIENIE PRZEZ created_at DESC '→ инндека' (tenant_id, created_at DESC, id DESC) '.

3. 2 Wyłącznik wiązania

Dodaj unikalny ogon (zwykle 'id') do stabilnego sortowania i poszukiwania paginacji.

3. 3 Wskaźniki częściowe/filtrowane

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 Indeksy obejmujące

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 Funkcjonalne/obliczone

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) Przegroda i strzyżenie

4. 1 podział (PG rodzime/dziedziczenie stołowe; MySQL ZAKRES/LISTA)

Rotacja stron według czasu („dziennie/co tydzień”) upraszcza „VACUUM/DELETE”.
Indeksy są lokalne partycje → mniejsze niż B-Tree, szybszy plan.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 Klucz podziału

W OLTP - przez 'lokator _ id' (lokalizacja obciążenia).
W serii czasowej/OLAP - przez 'ts' (zapytania zakresu).
Hybryda: '(tenant_id, ts)' + podstrony.

4. 3 Shading

Spójne hashing/range-shard przez 'lokator _ id' lub przez czas.
Zapytanie cross-shard → scatter-gather i k-way fusion; Trzymaj kursor na odłamek.

5) Statystyki, kardynalność i plany

5. 1 Aktualne statystyki

Włącz automatyczną analizę ('autovacuum/autoanalyze'), zwiększ 'default _ statistics _ target' dla brudnych dystrybucji.

5. 2 Zaawansowane statystyki (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 Plan realizacji

• "Wiersze", "Pętle", "Rzeczywisty czas", "Wspólne odczytanie/trafienie", "Recheck Cond'.
• Тида Join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Skanowanie/Skanowanie tylko/Bitmap Hałas Skanowanie.

5. 4 Stabilność planów

Parameteryzacja (przygotowane oświadczenia) może „trzymać się” złego planu. Użyj barier pamięci podręcznej planu (PG: 'plan _ cache _ mode = force_custom_plan' dla zapytań problemowych) lub stałych „spedycyjnych”.

6) Optymalizacja połączeń i rodzajów

6. 1 Strategie

Gniazdowana pętla: mały zewnętrzny, szybki indeks na wewnętrznej.
Hash Join: duże zestawy, wystarczająco dużo pamięci dla tabeli hash.
Merge Join: sortowane wpisy, korzystne w kolejności już dostępnej.

6. 2 Indeksy pod połączeniem

Dla 'A JOIN B ON B.a_id = A.id', → indeks do 'B (a_id)'.
Dla filtra po połączeniu - indeks na kolumnach filtra tabeli wewnętrznej.

6. 3 Triage

Unikaj 'ORDER BY' without odpowiedniego indeksu; sortowanie na dużych zestawach jest drogie przez pamięć/dysk.

7) Redakcja zapytania

Pozbyć się „płatków śniegu” subqueries; rozszerzyć w JOIN.
Użyj CTE-inline (PG ≥ 12 domyślne inliny CTE, ale 'MATERIALIZED' może popełnić wynik pośredni w razie potrzeby).
Usuń 'SELECT' → listę pól (oszczędności IO/sieci).
Przeniesienie obliczeń z 'WHERE' do postaci indeksowanej (kolumny wstępnie obliczone).
Agregacje: wstępne tabele podsumowujące/zmaterializowane widoki z aktualizacją przyrostową.

8) Masowanie, ograniczanie i paginacja

Batch-insert/update: 500-5000 partii zamiast jednej po drugiej.
Szukaj paginacji przez '(sort_key, id)' zamiast głębokiego 'OFFSET'.
Ograniczyć wybór przed sortowaniem/joyne (push-down 'LIMIT').

9) Buforowanie i denormalizacja

Aplikacja-poziom query-cache (key = SQL + bind-vars + rights version).
Zmaterializowane poglądy na ciężkie agregaty; plan rotacji/odniesienia.
Denormalizacja - Sklep często czytać obliczone pola (cena, w tym rabat), ale z wyzwalaczem/tło zadania dla spójności.
Redis jako L2 dla kluczy gorących (z TTL i niepełnosprawności zdarzeń).

10) Specyfika popularnych silników

10. 1 PostgreSQL

Индекса: B-Tree, Hash, GIN/GiST, BRIN, częściowy, funkcjonalny, INCLUDE.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

Statystyki histogramu ("ANALYZE TABLE... AKTUALIZACJA HISTOGRAM 'м8. 0).

10. 3 ClickHouse

Klucz pierwotny = sort; „ZAMÓWIENIE WEDŁUG (tenant_id, ts, id)”.

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

Użyj „wskazówki ()” do diagnostyki, uważaj na „zadawane pytania”.

10. 5 Elasticsearch/OpenSearch

Słowo kluczowe vs pola tekstowe; doc_values do sortowania/agregatów.
Segmentacja hałdy: agregacje - ciężkie; ograniczyć 'rozmiar' i użyć agregacji 'composite' (przywoływanie).
Nie należy uwzględniać analizatorów, w których wymagane jest dokładne porównanie.

11) Konkurencyjność, blokady i MVCC

Transakcje krótkie; unikać „długich” odczytów w „POWTARZALNE CZYTANIE” niepotrzebnie.
Operacje indeksu również blokują (redukcja przepustowości zapisu).
Planowanie indeksowania online: 'CREATE INDEX SPECIFICALLY' (PG), 'ALGORYTM = INPLACE '/' ONLINE' (MySQL).
Wkładki w ogonie przez godzinę/ID → „gorące strony” indeksu; rozprowadzić klucz (UUIDv7/sól).

12) Obserwowalność i SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) według nazwy zapytania.
• 'rows _ examined', 'rows _ returned', 'buffer _ hit _ ratio'.
• 'deadlocks', 'lock _ wait _ ms', 'temp _ sort _ disk _ usage'.
• Oczekiwano udziału planów z 'Seq Scan' where 'Index Scan'.
• Alerty regresji podczas zmiany wersji/parametrów DBMS.

• Włącz powolny dziennik zapytań z progiem (na przykład 200 ms).
• Korelacja zapytań z przęsłami (trace_id).
• Usuń plany zapytań problemowych i zapisz, aby obiektywnie przechowywać dane retrospektywne.

• Należy odczytać P95 '<= 150 ms' z 'LIMIT <= 50 ° i gorącym najemcą.
• rekordy P95 '<= 200 ms' z partiami do 1000 linii.

13) Bezpieczeństwo i wielopoziomowość

Wymagane są indeksy na polach kontroli dostępu ('lokator _ id',' owner _ id').
Zasady (RLS/ABAC) muszą być wstępnie filtrowane; w przeciwnym razie optymalizator planuje nieprawidłowo.
Nie indeksuj pól wrażliwych w jasnym tekście; używać hashes/tokenów.

14) Anty-wzory

Głębokie 'OFFSET' bez poszukiwania alternatywy kursora.
„Jeden indeks dla wszystkich” - przeciążenie pamięci i ścieżka zapisu.
„WYBIERZ” w krytycznych ścieżkach.
Funkcje powyżej kolumny w 'WHERE' bez indeksu funkcji.
Niestabilne plany ze względu na stare statystyki.
Brakuje rozkazu, czekając na stabilne zamówienie.
Indeksy dla indeksów: ROI <0 ze względu na drogie napisy/wsparcie.

15) Lista kontrolna wdrażania

1. Top N żądania przez QPS i czas → wybierz 3-5 kandydatów.
2. Usuń plany 'EXPLAIN ANALYZE', sprawdź kardynalność vs actual.
3. Indeksy projektowe: pole kolejności, INCLUDE/partial/functional.
4. Wdrożenie podziału dla dużych tabel (klucze tymczasowe/lokatorskie).
5. Nadpisywanie zapytań: usuń 'SELECT', wline proste CTEs, ograniczyć zestaw.
6. Włączyć mycie i szukać paginacji.
7. Konfiguracja pamięci podręcznej: L1/L2, niepełnosprawność według zdarzeń.
8. Wprowadzenie monitorowania planów i slow-log, wpisów dla regresji.
9. Wykonaj testy obciążenia z rzeczywistą dystrybucją danych.
10. Aktualizacja wytycznych dotyczących rozwoju (wskazówki ORM, indeksowanie, limity).

16) Przed/po przykładach

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) protokoły ORM i API

Unikać N + 1: chciwych próbek („zawiera”, „JOIN FETCH”, „preload”).
Wyraźne projekcje pola, pagate przez kursor.
gRPC/REST: limit 'page _ size', fix 'sort', use opaque tokens.
Pamięć podręczna planu: parametryzacja użytkowania; nie generuj „unikalnego” SQL na połączenie.

18) Migracja i operacje

Dodaj indeksy online i zaznacz jako INVISIBLE/CONCURRENT, plany testów, a następnie przełącz.
Korekty indeksu - regularne czyszczenie sanitarne: duplikaty, nieużywane, „martwe” dla starych funkcji.
Plan rotacji partii (upuść stary) i harmonogram „VACUUM/OPTIMIZE”.

19) Podsumowanie

Optymalizacja zapytań to inżynieria systemów: poprawne klucze i indeksy, schludne plany, przemyślane partycjonowanie i strzyżenie, dyscyplina w zapytaniach i ORM, buforowanie i obserwowalność. Wykonując opisane wzory, otrzymasz szybki, przewidywalny i ekonomiczny system, który jest odporny na wzrost i obciążenie danych.