Datenbank Indizes & Performance-Optimierung: B-Tree, Hash, Fulltext & Clustered

Dieser Beitrag ist eine umfassende Anleitung zu Datenbank-Indizes und Performance-Optimierung – inklusive B-Tree, Hash, Fulltext und Clustered Indizes mit praktischen Beispielen.

In a Nutshell

Datenbank-Indizes beschleunigen Abfragen erheblich, indem sie schnellen Zugriff auf Daten ermöglichen. Verschiedene Index-Typen sind für unterschiedliche Anwendungsfälle optimiert.

Kompakte Fachbeschreibung

Datenbank-Indizes sind speziellen Datenstrukturen, die die Geschwindigkeit von Datenabrufoperationen in Datenbanktabellen verbessern. Sie funktionieren ähnlich wie ein Index in einem Buch.

Index-Typen und ihre Eigenschaften:

B-Tree Index (Balanced Tree)

Struktur: Ausgeglichener Baum mit sortierten Werten
Verwendung: Gleichheit, Bereichssuche, Sortierung
Performance: O(log n) für Suchoperationen
Beispiele: Primärschlüssel, Fremdschlüssel, Standard-Indizes

Hash Index

Struktur: Hash-Tabelle mit direkten Adressen
Verwendung: Nur Gleichheitssuchen (=)
Performance: O(1) für exakte Treffer
Beispiele: Memory-Tabellen, exakte Suchen

Fulltext Index

Struktur: Invertierter Index für Textsuche
Verwendung: Volltextsuche, Stichwortsuche
Performance: Optimierte Textsuchalgorithmen
Beispiele: Dokumentensuche, Content-Suche

Clustered Index

Struktur: Physische Sortierung der Tabelle
Verwendung: Primärschlüssel, häufige Bereichssuchen
Performance: Schnell für Primärschlüsselzugriffe
Beispiele: Zeitreihen, Log-Daten

Prüfungsrelevante Stichpunkte

B-Tree Index: Ausgeglichener Baum für Gleichheits- und Bereichssuche
Hash Index: Direkte Adressierung für exakte Treffer
Fulltext Index: Textsuche mit Stammformen und Relevanz
Clustered Index: Physische Datenspeicherung nach Index
Performance: Query-Optimierung durch Indizes
Trade-offs: Speicherplatz vs. Geschwindigkeit
IHK-relevant: Wichtig für Datenbankadministration und -optimierung

Kernkomponenten

Index-Struktur: Baum, Hash, Invertierter Index
Index-Typ: B-Tree, Hash, Fulltext, Clustered
Query-Typ: Gleichheit, Bereich, Volltext
Performance-Kennzahlen: Lesezeit, Schreibzeit, Speicher
Index-Strategie: Single-Column, Multi-Column, Covering
Optimierung: EXPLAIN-Analyse, Index-Tuning
Wartung: Rebuild, Fragmentierung, Statistiken

Praxisbeispiele

1. B-Tree Index Beispiele

-- MySQL B-Tree Index erstellen
CREATE INDEX idx_kunden_name ON kunden(name);
CREATE INDEX idx_bestellungen_datum ON bestellungen(bestelldatum);

-- Multi-Column B-Tree Index
CREATE INDEX idx_kunden_stadt_name ON kunden(stadt, name);

-- Unique B-Tree Index
CREATE UNIQUE INDEX idx_email_unique ON kunden(email);

-- Abfrage mit B-Tree Index Nutzung
EXPLAIN SELECT * FROM kunden WHERE name = 'Mustermann';
EXPLAIN SELECT * FROM bestellungen WHERE bestelldatum BETWEEN '2024-01-01' AND '2024-12-31';

-- Performance-Vergleich
-- Ohne Index: Full Table Scan
SELECT * FROM grosse_tabelle WHERE spalte_x = 'wert';

-- Mit B-Tree Index: Index Seek
SELECT * FROM grosse_tabelle WHERE spalte_x = 'wert';

2. Hash Index Beispiele

-- MySQL Hash Index (nur Memory Engine)
CREATE TABLE user_sessions (
    session_id VARCHAR(255) PRIMARY KEY,
    user_id INT,
    created_at TIMESTAMP,
    data TEXT,
    INDEX ((session_id)) USING HASH
) ENGINE=MEMORY;

-- PostgreSQL Hash Index
CREATE INDEX idx_hash_email ON benutzer USING HASH (email);

-- Abfrage mit Hash Index (nur Gleichheit)
SELECT * FROM benutzer WHERE email = 'user@example.com';

-- Hash Index wird NICHT verwendet für:
SELECT * FROM benutzer WHERE email LIKE 'user%';  -- Bereichssuche
SELECT * FROM benutzer WHERE email > 'a';        -- Vergleichsoperation

3. Fulltext Index Beispiele

-- MySQL Fulltext Index
CREATE TABLE artikel (
    id INT PRIMARY KEY,
    titel VARCHAR(255),
    inhalt TEXT,
    FULLTEXT KEY ft_inhalt (titel, inhalt)
);

-- Fulltext Suche
SELECT titel, inhalt 
FROM artikel 
WHERE MATCH(titel, inhalt) AGAINST('datenbank performance' IN NATURAL LANGUAGE MODE);

-- Mit Relevanz-Score
SELECT titel, 
       MATCH(titel, inhalt) AGAINST('datenbank performance' IN NATURAL LANGUAGE MODE) AS score
FROM artikel 
WHERE MATCH(titel, inhalt) AGAINST('datenbank performance' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- Boolean Mode für komplexe Suchen
SELECT titel, inhalt
FROM artikel 
WHERE MATCH(titel, inhalt) AGAINST('+datenbank +performance -mysql' IN BOOLEAN MODE);

-- PostgreSQL Fulltext Index
CREATE TABLE dokumente (
    id SERIAL PRIMARY KEY,
    titel VARCHAR(255),
    inhalt TEXT
);

-- Fulltext Index mit tsvector
ALTER TABLE dokumente ADD COLUMN searchable_text tsvector;
UPDATE dokumente SET searchable_text = to_tsvector('german', titel || ' ' || inhalt);
CREATE INDEX idx_fulltext ON dokumente USING GIN(searchable_text);

-- Fulltext Suche in PostgreSQL
SELECT titel, ts_rank(searchable_text, plainto_tsquery('german', 'datenbank performance')) AS rank
FROM dokumente 
WHERE searchable_text @@ plainto_tsquery('german', 'datenbank performance')
ORDER BY rank DESC;

4. Clustered Index Beispiele

-- SQL Server Clustered Index
CREATE TABLE log_daten (
    log_id INT IDENTITY(1,1),
    zeitstempel DATETIME NOT NULL,
    nachricht VARCHAR(1000),
    level VARCHAR(10)
);

-- Clustered Index auf Zeitstempel (physische Sortierung)
CREATE CLUSTERED INDEX idx_log_zeitstempel ON log_daten(zeitstempel);

-- PostgreSQL Clustered Index (CLUSTER)
CREATE TABLE messwerte (
    id SERIAL PRIMARY KEY,
    sensor_id INT,
    zeitpunkt TIMESTAMP NOT NULL,
    wert DECIMAL(10,4)
);

-- Clustered Index auf Primärschlüssel (Standard)
CREATE INDEX idx_messwerte_zeitpunkt ON messwerte(zeitpunkt);

-- Tabelle nach Index clustern
CLUSTER messwerte USING idx_messwerte_zeitpunkt;

-- MySQL InnoDB Clustered Index (automatisch auf Primärschlüssel)
CREATE TABLE transaktionen (
    id BIGINT PRIMARY KEY,  -- Automatisch clustered
    konto_id INT,
    betrag DECIMAL(12,2),
    datum DATETIME,
    INDEX idx_konto_datum (konto_id, datum)  -- Secondary Index
);

5. Performance-Analyse mit EXPLAIN

-- MySQL EXPLAIN Analyse
EXPLAIN FORMAT=JSON
SELECT k.name, b.bestelldatum, b.gesamtbetrag
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
WHERE k.stadt = 'Berlin'
  AND b.bestelldatum >= '2024-01-01'
ORDER BY b.gesamtbetrag DESC;

-- PostgreSQL EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT k.name, b.bestelldatum, b.gesamtbetrag
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
WHERE k.stadt = 'Berlin'
  AND b.bestelldatum >= '2024-01-01'
ORDER BY b.gesamtbetrag DESC;

-- Index-Nutzung überprüfen
-- MySQL: SHOW INDEX FROM tabelle;
SHOW INDEX FROM kunden;

-- PostgreSQL: \d tabelle
-- oder pg_indexes view
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'kunden';

6. Index-Optimierung und Best Practices

-- Covering Index (alle benötigten Spalten im Index)
CREATE INDEX idx_kunden_covering ON kunden(stadt, name, id);

-- Abfrage nutzt nur den Index (Index-Only Scan)
SELECT id, name FROM kunden WHERE stadt = 'Berlin';

-- Partial Index (nur für Teil der Daten)
CREATE INDEX idx_aktive_kunden ON kunden(id) WHERE status = 'aktiv';

-- Function-Based Index (PostgreSQL)
CREATE INDEX idx_kunden_name_lower ON kunden(LOWER(name));

-- Abfrage nutzt Function-Based Index
SELECT * FROM kunden WHERE LOWER(name) = 'mustermann';

-- Composite Index mit richtiger Spaltenreihenfolge
CREATE INDEX idx_bestellungen_optimal ON bestellungen(kunden_id, bestelldatum, status);

-- Gute Abfrage (nutzt Index vollständig)
SELECT * FROM bestellungen 
WHERE kunden_id = 123 
  AND bestelldatum >= '2024-01-01' 
  AND status = 'completed';

-- Schlechte Abfrage (kann Index nicht nutzen)
SELECT * FROM bestellungen 
WHERE status = 'completed' 
  AND bestelldatum >= '2024-01-01';  -- Führende Spalte nicht in WHERE

7. Index-Wartung und Monitoring

-- Index-Statistiken aktualisieren
-- MySQL
ANALYZE TABLE kunden;

-- PostgreSQL
ANALYZE kunden;

-- Index-Fragmentierung prüfen
-- SQL Server
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('kunden'), NULL, NULL, 'DETAILED');

-- PostgreSQL
SELECT schemaname, tablename, attname, n_distinct, correlation 
FROM pg_stats 
WHERE tablename = 'kunden';

-- Index-Rebuild bei Fragmentierung
-- SQL Server
ALTER INDEX ALL ON kunden REBUILD;

-- MySQL (InnoDB)
ANALYZE TABLE kunden;  -- Statistiken aktualisieren
OPTIMIZE TABLE kunden;  -- Tabelle optimieren

-- PostgreSQL
REINDEX INDEX idx_kunden_name;

Performance-Vergleich Index-Typen

Index-Typ	Suchtyp	Performance	Speicher	Verwendung
B-Tree	Gleichheit, Bereich	O(log n)	Mittel	Standard-Indizes
Hash	Nur Gleichheit	O(1)	Gering	Memory-Tabellen
Fulltext	Volltext	Variabel	Hoch	Textsuche
Clustered	Primärschlüssel	O(log n)	Tabelle	Häufige Zugriffe

Index-Strategien

Single-Column Index

-- Einfacher Index auf eine Spalte
CREATE INDEX idx_kunden_email ON kunden(email);

Multi-Column Index

-- Composite Index mit optimaler Spaltenreihenfolge
CREATE INDEX idx_kunden_stadt_name ON kunden(stadt, name);

Covering Index

-- Index enthält alle benötigten Spalten
CREATE INDEX idx_bestellungen_covering ON bestellungen(kunden_id, bestelldatum, gesamtbetrag);

Partial Index

-- Index nur für Teil der Daten
CREATE INDEX idx_aktive_kunden ON kunden(id) WHERE status = 'aktiv';

Function-Based Index

-- Index auf berechnete Werte
CREATE INDEX idx_kunden_name_lower ON kunden(LOWER(name));

Query-Optimierung

Index-Nutzung maximieren

-- GUTE Abfragen (nutzen Indizes)
SELECT * FROM kunden WHERE stadt = 'Berlin';                    -- Single-Column Index
SELECT * FROM bestellungen WHERE kunden_id = 123 AND datum > '2024-01-01'; -- Composite Index
SELECT * FROM artikel WHERE MATCH(titel) AGAINST('suchbegriff');    -- Fulltext Index

-- SCHLECHTE Abfragen (nutzen keine Indizes)
SELECT * FROM kunden WHERE LOWER(name) = 'mustermann';           -- Kein Function-Based Index
SELECT * FROM bestellungen WHERE YEAR(datum) = 2024;            -- Funktion auf Spalte
SELECT * FROM kunden WHERE name LIKE '%mustermann%';             -- Leading Wildcard

EXPLAIN-Analyse

-- MySQL
EXPLAIN SELECT * FROM kunden WHERE stadt = 'Berlin';

-- Wichtige Spalten:
-- type: ALL (schlecht), ref, range, index (gut)
-- key: Genutzter Index
-- rows: Geschätzte Zeilenanzahl
-- Extra: Using index (gut), Using filesort (schlecht)

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM kunden WHERE stadt = 'Berlin';

-- Wichtige Informationen:
-- Seq Scan vs Index Scan
-- Index-Only Scan (sehr gut)
-- tatsächliche Ausführungszeit

Vorteile und Nachteile

Vorteile von Indizes

Performance: Dramatisch schnellere Abfragen
Sortierung: ORDER BY ohne zusätzliche Sortierung
Eindeutigkeit: UNIQUE-Indizes garantieren Datenintegrität
Joins: Fremdschlüssel-Indizes beschleunigen JOINs

Nachteile

Speicherplatz: Indizes benötigen zusätzlichen Speicher
Schreibperformance: INSERT/UPDATE/DELETE werden langsamer
Wartung: Indizes müssen gepflegt werden
Overhead: Zu viele Indizes können kontraproduktiv sein

Best Practices

Wann Indizes erstellen

Häufige WHERE-Klauseln
JOIN-Bedingungen
ORDER BY-Klauseln
GROUP BY-Klauseln
UNIQUE-Constraints

Wann Indizes vermeiden

Selten verwendete Spalten
Tabellen mit wenigen Zeilen
Spalten mit hohen Kardinalität bei geringer Selektivität
Häufige UPDATE/DELETE-Operationen

Index-Design

Spaltenreihenfolge: Selektivität absteigend
Index-Breite: So schmal wie möglich
Covering Index: Alle benötigten Spalten inkludieren
Partial Index: Nur für relevante Daten

Häufige Prüfungsfragen

Was ist der Unterschied zwischen B-Tree und Hash Index? B-Tree unterstützt Gleichheits- und Bereichssuche, Hash nur Gleichheitssuche.
Wann verwendet man einen Fulltext Index? Für Textsuche in großen Textfeldern mit Stammformen und Relevanz-Bewertung.
Erklären Sie Clustered vs Non-Clustered Index! Clustered: Physische Datenspeicherung, Non-Clustered: Separate Index-Struktur.
Wie überprüft man die Index-Nutzung? Mit EXPLAIN/EXPLAIN ANALYZE zur Analyse des Query-Plans.