Indexierung und Optimierung von Abfragen

1) Indexierungs- und Optimierungsziele

Latenz: Reduzierung der P50/P95/P99.
Bandbreite: QPS-Wachstum ohne horizontale Skalierung.
Vorhersagbarkeit: stabile Pläne und keine „Sprünge“ in der Reaktionszeit.
Einsparungen: weniger IO/CPU, weniger Cloud-Rechnung.
Zuverlässigkeit: Reduzierung von Schlössern und Dedlocks durch korrekte Zugriffe.

• Jede Optimierung muss korrekt und konsistent bleiben.
• Verfolgen Sie den Effekt in Metriken und Planprotokollen.

2) Grundlegende Indexstrukturen und wann sie anzuwenden sind

2. 1 B-Baum (Ausfall)

Gleich/Bereiche, Sortierung, 'ORDER BY'.
Gut für die meisten Zeit-/ID/Status-Filter.

2. 2 Hash

Reine Gleichheit ('='), billiger im Speicher, aber ohne Ordnung (PG: Begrenzungen entfernt, aber immer noch eine Nischenwahl).

2. 3 GIN / GiST (PostgreSQL)

GIN: Arrays/JSONB-Schlüssel, Volltext (tsvector), Inhalt ('@>').
GiST: Geo, Bereiche, kNN.

2. 4 BRIN (PostgreSQL)

Super-günstiger Index nach „natürlich sortierten“ Tischen (nur append nach Zeit). Gut für Zeitreihen mit großen Tabellen.

2. 5 Bitmap (MySQL/InnoDB: nicht nativ; DW-DBMS/OLAP)

Wirksam für niedrige Kardinalität und Facetten, häufiger in Säulenlagern.

2. 6 Säulenindizes (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
OLAP-Abfragen mit Aggregationen und Bereichen.

2. 7 Invertierte Indizes (Elasticsearch/OpenSearch)

Volltext, Facetten, hybride Suche. Verwenden Sie Keyword-Felder und Doc-Werte für genaue Filter.

2. 8 MongoDB

Single, Compound, Multikey (Arrays), Partial, TTL, Text, Hashed (für Sharding mit einem einheitlichen Schlüssel).

3) Entwurf von Schlüsseln und zusammengesetzten Indizes

3. 1 Regel „linkes Präfix“

Die Reihenfolge der Felder im Index bestimmt die Verwendbarkeit.
Die Abfrage' WHERE tenant_id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Fügen Sie einen einzigartigen Schwanz (normalerweise' id') für eine stabile Sortierung und Seek-Pagination hinzu.

3. 3 Partielle/gefilterte Indizes

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 Deckungsindizes

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

3. 5 Funktional/berechnet

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

4) Partitionierung und Sharding

4. 1 Partitionierung (PG native/tabellarische Vererbung; MySQL RANGE/LIST)

Die Rotation der Parteien nach Zeit („täglich/wöchentlich“) vereinfacht „VACUUM/DELETE“.
Indizes sind lokale Parteien → weniger B-Tree, schneller Plan.

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

4. 2 Partitionierungsschlüssel

In OLTP durch 'tenant _ id' (Lastlokalisierung).
In der Zeitreihe/OLAP - durch 'ts' (Bereichsanfragen).
Hybrid:'(tenant_id, ts)'+ Unterparteien.

4. 3 Sharding

Consistent hashing/range-shard durch 'tenant _ id' oder durch die Zeit.
Cross-Shard-Abfrage → Scatter-Gather und K-Way-Merge; Halten Sie per-shard cursor.

5) Statistiken, Kardinalität und Pläne

5. 1 Aktuelle Statistiken

Aktivieren Sie Auto-Analyse ('autovacuum/autoanalyze'), erhöhen Sie' default _ statistics _ target 'für' schmutzige' Verteilungen.

5. 2 Erweiterte Statistiken (PG)

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

5. 3 Ausführungsplan

• `Rows`, `Loops`, `Actual time`, `Shared Read/Hit`, `Recheck Cond`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Stabilität der Pläne

Parametrisierung (vorbereitete Aussagen) kann auf einem schlechten Plan „kleben“. Verwenden Sie plan cache guardrails (PG: 'plan _ cache _ mode = force_custom_plan' für problematische Abfragen) oder einen „Durchlauf“ von Konstanten.

6) Join- und Sortieroptimierung

6. 1 Strategien

Nested Loop: kleiner externer, schneller Index auf dem internen.
Hash Join: große Sets, genügend Speicher unter dem Hash-Tisch.
Merge Join: sortierte Eingänge, vorteilhaft bei bereits vorhandener Reihenfolge.

6. 2 Indizes unter join

Für „A JOIN B ON B.a_id = A.id“ → der Index auf „B (a_id)“.
Für den Filter nach join ist der Index auf den Filterspalten der internen Tabelle.

6. 3 Sortierung

Vermeiden Sie' ORDER BY 'ohne entsprechenden Index; Sortieren auf großen Sätzen Straße nach Speicher/Festplatte.

7) Abfragen neu schreiben (query rewrite)

Befreien Sie sich von „Schneeflocken“ Unterabfragen; in JOIN erweitern.
Verwenden Sie CTE-inline (standardmäßig PG ≥12 inlines CTE, aber 'MATERIALIZED' kann bei Bedarf das Zwischenergebnis erfassen).
Entfernen Sie' SELECT '→ listen Sie die Felder auf (IO/Netzwerk-Einsparungen).
Übertragen Sie die Berechnungen von 'WHERE' auf die indizierbare Form (vorwegnehmbare Spalten).
Aggregationen: Vorläufige Summentabellen/materialisierte Ansichten mit inkrementeller Aktualisierung.

8) Schlachten, Begrenzen und Paginieren

Batch-insert/update: 500-5000 Packungen statt Stück für Stück.
Seek-Paginierung durch'(sort_key, id) 'anstelle von tiefem' OFFSET'.
Limit des Sets vor dem Sortieren/Join (Push-down 'LIMIT').

9) Caching und Denormalisierung

Query-Cache auf Anwendungsebene (Schlüssel = SQL + bind-vars + Version der Rechte).
Materialisierte Ansichten für schwere Einheiten; Rotationsplan/Refresh.
Denormalisierung: Speichern Sie häufig gelesene berechnete Felder (Preis inklusive Rabatt), aber mit Trigger/Hintergrundaufgabe für die Konsistenz.
Redis als L2 für „heiße“ Schlüssel (mit TTL und Behinderung durch Ereignisse).

10) Besonderheiten der populären Motoren

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, 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

Statistik für Histogramme ('ANALYZE TABLE... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Primärschlüssel = Sortierung; 'ORDER BY (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 } } });

Verwenden Sie' hint () 'für die Diagnose, achten Sie auf' covered query'.

10. 5 Elasticsearch/OpenSearch

Schlüsselwort gegen Feldtext; doc_values für Sortierung/Aggregate.
Segmentierung heap: Aggregationen - heavy; Begrenzen Sie' size' und verwenden Sie' composite' Aggregationen (seitenweise Auswahl).
Schalten Sie Analysatoren nicht ein, wenn ein genauer Vergleich erforderlich ist.

11) Wettbewerb, Sperren und MVCC

Kurze Transaktionen; Vermeiden Sie „lange“ Lesungen unter 'REPEATABLE READ' unnötig.
Indexoperationen nehmen auch Blockaden (Verringerung des Schreibens durch die Eingabe).
Online-Indexierung planen: 'CREATE INDEX CONCURRENTLY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Einfügungen in den Schwanz für eine Stunde/ID → „heiße Seiten“ des Index; Verteilen Sie den Schlüssel (UUIDv7/Salz).

12) Beobachtbarkeit und SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) nach dem Namen der Anfrage.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• Anteil der Pläne mit 'Seq Scan', wo 'Index Scan' erwartet wurde.
• Regress-Alerts beim Ändern der DBMS-Version/Parameter.

• Aktivieren Sie Slow Query Log mit einem Schwellenwert (z. B. 200 ms).
• Korrelation von Anfragen mit Spans (trace_id).
• Nehmen Sie Pläne für problematische Abfragen auf und speichern Sie sie zur Retrospektive im Objektspeicher.

• P95 von „<= 150 ms“ bei „LIMIT <= 50“ und „heißem“ Tenant.
• P95 Einträge'<= 200 ms' bei Gefechten bis zu 1000 Zeilen.

13) Sicherheit und Multi-Tenant

Indizes nach Zugriffskontrollfeldern ('tenant _ id', 'owner _ id') sind erforderlich.
Richtlinien (RLS/ABAC) müssen ein Vorfilter sein; Ansonsten plant der Optimierer falsch.
Indizieren Sie empfindliche Felder nicht im Klartext; Verwenden Sie Hashes/Token.

14) Anti-Muster

Deep 'OFFSET' ohne Seek-Cursor-Alternative.
„Ein Index für alles“ - Speicherüberlastung und Write-Path.
'SELECT' in kritischen Pfaden.
Funktionen über der Spalte in 'WHERE' ohne Funktionsindex.
Instabile Pläne wegen alter Statistiken.
Kein 'ORDER BY' beim Warten auf eine stabile Ordnung.
Indizes um der Indizes willen: ROI <0 aufgrund des teuren Schreibens/Supports.

15) Checkliste Umsetzung

1. Top-N-Anfragen nach QPS und Zeit → 3-5 Kandidaten auswählen.
2. Entfernen Sie die Pläne' EXPLAIN ANALYZE', überprüfen Sie die Kardinalität vs sachlich.
3. Indizes entwerfen: Reihenfolge der Felder, INCLUDE/partial/functional.
4. Implementieren Sie Partitionierung für große Tabellen (temporäre/tenante Schlüssel).
5. Abfragen umschreiben: 'SELECT' entfernen, einfache CTEs inlinieren, Set einschränken.
6. Aktivieren Sie Batching und Seek-Pagination.
7. Richten Sie den Cache ein: L1/L2, Behinderung durch Ereignisse.
8. Einführung von Planüberwachung und Slow-Log, Regressalarme.
9. Führen Sie Lasttests mit echter Datenverteilung durch.
10. Aktualisieren Sie die Haydlines für die Entwicklung (ORM-Hints, Indexierung, Limits).

16) Vorher/nachher Beispiele

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) ORM und API-Protokolle

Vermeiden Sie N + 1: gierige Stichproben ('includes', 'JOIN FETCH', 'preload').
Explizite Projektionen von Feldern, paginieren mit dem Cursor.
gRPC/REST: Begrenzen Sie' page _ size', fixieren Sie' sort', verwenden Sie undurchsichtige Token.
Plan-Cache: Parametrierung verwenden; Generieren Sie nicht „eindeutige“ SQLs für jeden Aufruf.

18) Migration und Ausbeutung

Fügen Sie Indizes online hinzu und markieren Sie als INVISIBLE/CONCURRENTLY, testen Sie die Pläne und wechseln Sie dann.
Revisionen der Indizes - regelmäßige sanitäre Reinigung: Duplikate, ungenutzt, „tot“ für alte Fitch.
Der Plan der Rotation der Parteien (Drop der alten) und 'VACUUM/OPTIMIZE' Zeitplan.

19) Zusammenfassung

Abfrageoptimierung ist Systemengineering: die richtigen Schlüssel und Indizes, ordentliche Pläne, durchdachte Partitionierung und Sharding, Disziplin in Abfragen und ORM, Caching und Beobachtbarkeit. Durch die Einhaltung der beschriebenen Muster erhalten Sie ein schnelles, vorhersehbares und kostengünstiges System, das gegen Daten- und Lastwachstum resistent ist.