SQL-Indizierung erklärt

31 of 31 rows


Indexarten
	Ausgeglichener Baum für Gleichheit, Bereiche und sortierte Scans. Standard bei den meisten Datenbanken.	Für =, <, >, BETWEEN, ORDER BY verwenden. Nicht für Volltextsuche.
	Index, dessen Blattebene die Tabelle selbst ist. Zeilen liegen in Schlüsselreihenfolge. Genau einer pro Tabelle.	InnoDB clustert über den Primärschlüssel. SQL-Server-Tabellen ohne einen sind Heaps.
	Eigene Struktur mit Schlüsseln plus Zeiger zurück zur Basistabelle. Viele pro Tabelle möglich.	Auf einem Heap speichert er eine Row-ID, auf einer geclusterten Tabelle den Clustering-Schlüssel.
	Hashtabelle nur für Gleichheitssuchen. Exakte Treffer in konstanter Zeit.	Nur für =-Operationen verwenden; Bereiche und Sortierung brauchen B-tree. Der Hash memory-optimierter SQL-Server-Tabellen braucht eine Bucket-Anzahl nahe der Zeilenzahl.
	Generalized Inverted Index. Bildet Werte auf Zeilen ab. Für Arrays und Volltext gebaut.	Für jsonb, Arrays, tsvector und Trigramm-Suche verwenden. Langsamer im Aufbau als B-tree.
	Generalized Search Tree. Erweiterbar für nicht-skalare Typen und Überschneidungsabfragen.	Für geometrische Bereiche, kNN und Exclusion-Constraints verwenden. pg_trgm beschleunigt LIKE.
	Raum partitionierender GiST. Teilt Daten über Radix- oder Quad-Trees in nicht überlappende Regionen.	Für Punkte, IP-Präfixe und Präfix-Matching verwenden. Nicht für Überschneidungsabfragen; dafür GiST nehmen.
	Block Range Index. Speichert Min/Max pro Blockbereich. Winziger Footprint.	Auf großen, physisch sortierten Tabellen verwenden. Billig zu bauen, schwache Selektivität.
	Index als Spaltensegmente statt Zeilen gespeichert. Hohe Kompression, Batch-Ausführung.	Für analytische Scans über viele Zeilen und wenige Spalten. Einzelzeilen-Lookups sind langsam.
	Eigener invertierter Wortindex in MySQL und SQL Server. Pro Textspalte gebaut.	MATCH AGAINST oder CONTAINS verwenden. Postgres-Volltext nutzt stattdessen GIN auf tsvector.
	Oracle-Index mit einer Bitmap je unterschiedlichem Wert. Jedes Bit markiert eine Zeile.	Für Spalten mit niedriger Kardinalität in leseintensiven Warehouses. Gleichzeitige Schreibzugriffe serialisieren pro Bitmap.
	Indexspalte als DESC deklariert. Schlüssel dieser Spalte liegen in umgekehrter Reihenfolge.	Nötig für gemischte Sortierungen wie (a ASC, b DESC). Einfache DESC-Sortierungen können einen normalen Index auch rückwärts lesen.
	MySQL-8-Index, den der Optimierer ignoriert. Wird bei jedem Schreibzugriff weiter gepflegt.	Index vor dem Droppen verstecken, um die Wirkung zu testen. Ohne Rebuild wieder sichtbar machen.
Entwurf
	Mehrspaltiger Index. Führende Spalten müssen der Filterreihenfolge der Abfrage entsprechen.	Erst Gleichheit, dann Bereich anordnen. Zwei Einzelindexe ersetzen keinen Composite-Index.
	Index über ein WHERE-Prädikat. SQL Server nennt ihn gefilterten Index. Deckt nur passende Zeilen ab.	Nur Zeilen mit active='t' indizieren. Halbiert Größe und Schreibkosten.
	B-tree mit angehängten zusätzlichen Nicht-Schlüsselspalten. Index-only-Scans vermeiden Heap-Lookups.	Häufig abgerufene Spalten per INCLUDE hinzufügen. Hält den Index sortierbar.
	Index, der Duplikate verhindert. Weist kollidierende Inserts zurück.	Für natürliche Schlüssel und 1:1-Constraints verwenden. UNIQUE NULL erlaubt viele NULLs.
	Index über eine Funktion von Spalten statt über die Spalten selbst.	lower(email) oder date_trunc('day', created_at) indizieren. Die Abfrage muss exakt denselben Ausdruck wiederholen.
	Operatorklasse. Legt fest, wie ein Index den Typ einer Spalte vergleicht und speichert.	varchar_pattern_ops setzen für LIKE 'abc%' ohne Umwandlung der Groß-/Kleinschreibung. Defaults passen für Gleichheit und Bereiche.
	Plan, der eine Abfrage allein aus Indexseiten beantwortet. Der Heap wird nie gelesen.	Braucht jede abgerufene Spalte im Index oder in der INCLUDE-Liste. Vacuum hält die Visibility-Map aktuell.
	Fremdschlüsselspalten bekommen in der referenzierenden Tabelle keinen automatischen Index.	Jede FK-Spalte indizieren, über die Sie joinen oder löschen. Cascade-Deletes scannen die Child-Tabelle ohne ihn.
Wartung
	Plan-Inspektor. Zeigt Sequential Scans, Indexwahlen und Kostenschätzungen.	EXPLAIN (ANALYZE, BUFFERS) verwenden. Ein Seq Scan auf einer Tabelle mit 10 Mio. Zeilen heißt: Index fehlt.
	Beprobt Tabellenspalten für Planner-Statistiken. Histogramme steuern die Indexwahl.	Nach Bulk-Loads ausführen. Veraltete Statistiken verursachen Seq Scans auf indizierten Spalten.
	Jeder Index erhöht die Schreiblast. Inserts, Updates und Deletes zahlen pro Index.	Ungenutzte Indizes droppen. Nutzung in pg_stat_user_indexes verfolgen.
	Postgres-View mit der Nutzung je Index. Zählt Scans und gelesene Tuples seit dem Statistik-Reset.	Auf idx_scan = 0 filtern, um ungenutzte Indizes zu finden. Zähler mit pg_stat_reset() zurücksetzen.
	Baut einen Index, ohne Lese- oder Schreibzugriffe zu blockieren. Braucht zwei Tabellenscans.	Kann nicht in einer Transaktion laufen. Ein fehlgeschlagener Build hinterlässt einen INVALID-Index zum Droppen.
	Entfernt einen Index und gibt seinen Speicher frei. Nimmt standardmäßig einen exklusiven Lock.	DROP INDEX CONCURRENTLY auf stark genutzten Tabellen verwenden. Vor dem Entfernen die Nutzungsstatistiken prüfen.
	Baut einen aufgeblähten Index neu auf. Gewinnt Raum von toten Tuples zurück.	REINDEX CONCURRENTLY in Produktion verwenden. Ohne ihn blockieren Locks Schreibzugriffe.
	Tote Tuples und Seitenlücken, die sich in MVCC-Tabellen und ihren Indizes ansammeln.	Mit pgstattuple messen. Häufig Vacuumen; REINDEX, wenn toter Raum überwiegt.
	Prozentualer Füllgrad einer Seite beim Schreiben. Freiraum schluckt spätere Updates.	Auf 70-90 senken bei Tabellen mit vielen Updates. Volle Seiten erzwingen Page Splits.
	Schreibt die Tabelle physisch nach einem Index sortiert neu. Einmaliger Vorgang.	Passt zu BRIN für sortierte Blöcke. Die Reihenfolge bleibt bei späteren Schreibzugriffen nicht erhalten.