5 Indizes

Ein Index in einer Datenbank ist vergleichbar mit dem Inhaltsverzeichnis eines Buches. Statt alle Seiten durchblättern zu müssen, können Sie anhand des Inhaltsverzeichnisses schnell die gesuchte Seite finden. Genauso funktionieren Indizes in Datenbanken - sie beschleunigen das Auffinden von Datensätzen.

5.1 Einführung

Ein Index ist eine zusätzliche Datenstruktur, die Verweise auf die eigentlichen Datensätze enthält. MySQL verwendet B-Tree Indizes, die sehr effizient für Bereichsabfragen und Sortierungen sind.

5.1.1 Wann wird ein Index automatisch erstellt?

MySQL legt in folgenden Fällen automatisch einen Index an:

1. Primärschlüssel:

CREATE TABLE kunden (
    id INT PRIMARY KEY,  -- Erzeugt automatisch einen Index
    name VARCHAR(100)
);

2. UNIQUE Constraint:

CREATE TABLE mitarbeiter (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE  -- Erzeugt automatisch einen Index
);

3. Fremdschlüssel:

CREATE TABLE bestellungen (
    id INT PRIMARY KEY,
    kunden_id INT,
    FOREIGN KEY (kunden_id) REFERENCES kunden(id)  -- Erzeugt automatisch einen Index
);

5.1.2 Wie kann ich einen Index manuell erstellen?

Es gibt verschiedene Möglichkeiten, einen Index zu erstellen:

1. Bei der Tabellenerstellung:

CREATE TABLE produkte (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    kategorie VARCHAR(50),
    preis DECIMAL(10,2),
    INDEX idx_kategorie (kategorie),
    INDEX idx_preis (preis)
);

2. Nachträglich mit CREATE INDEX:

CREATE INDEX idx_name ON produkte(name);

3. Nachträglich mit ALTER TABLE:

ALTER TABLE produkte ADD INDEX idx_name_preis (name, preis);

5.2 Schlüsseleigenschaften und Dubletten

5.2.1 Wie kann ich die Schlüsseleigenschaft erzwingen?

Um sicherzustellen, dass bestimmte Werte eindeutig sind:

1. UNIQUE INDEX:

CREATE UNIQUE INDEX idx_email ON mitarbeiter(email);

2. UNIQUE Constraint:

ALTER TABLE mitarbeiter ADD UNIQUE (email);

5.2.2 Wie kann ich Dubletten verhindern?

1. Einzelne Spalte:

CREATE TABLE benutzer (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

2. Mehrere Spalten:

CREATE TABLE buchungen (
    id INT PRIMARY KEY,
    raum_nr INT,
    datum DATE,
    UNIQUE INDEX idx_buchung (raum_nr, datum)
);

5.2.3 Was bedeutet Indexselektivität?

Die Indexselektivität gibt an, wie viele unterschiedliche Werte im Verhältnis zur Gesamtanzahl der Datensätze existieren:

• Hohe Selektivität (gut für Indizes):
  • Primärschlüssel (jeder Wert ist eindeutig)
  • E-Mail-Adressen
  • Personalausweisnummern
• Niedrige Selektivität (weniger geeignet für Indizes):
  • Geschlecht (m/w/d)
  • Status (aktiv/inaktiv)
  • Bundesland (16 mögliche Werte)

Beispiel für die Berechnung der Selektivität:

SELECT COUNT(DISTINCT spalte) / COUNT(*) AS selektivitaet
FROM tabelle;

5.2.4 Wie kann ich einen Index löschen?

Es gibt zwei Möglichkeiten:

1. Mit DROP INDEX:

DROP INDEX idx_name ON produkte;

2. Mit ALTER TABLE:

ALTER TABLE produkte DROP INDEX idx_name;

WICHTIG: Der Index eines Primärschlüssels kann nicht direkt gelöscht werden. Sie müssen zuerst den Primärschlüssel entfernen:

ALTER TABLE produkte DROP PRIMARY KEY;

Tipps für die Verwendung von Indizes:

1. Analysieren Sie das Abfrageverhalten:

   • Welche Spalten werden häufig in WHERE-Klauseln verwendet?
   • Welche Spalten werden häufig für Sortierungen verwendet?
   • Welche Spalten werden häufig für Joins verwendet?

2. Beachten Sie die Performance-Auswirkungen:

   • Indizes beschleunigen Lesezugriffe
   • Indizes verlangsamen Schreibzugriffe
   • Indizes benötigen zusätzlichen Speicherplatz

3. Vermeiden Sie überflüssige Indizes:

   • Zu viele Indizes verlangsamen Schreiboperationen
   • Indizes auf Spalten mit niedriger Selektivität bringen wenig Nutzen
   • MySQL verwendet pro Abfrage nur einen Index

4. Überprüfen Sie die Indexnutzung:

EXPLAIN SELECT * FROM produkte WHERE kategorie = 'Elektronik';

5. Composite Indizes clever nutzen:

-- Besser:
CREATE INDEX idx_stadt_plz ON adressen(stadt, plz);

-- Statt:
CREATE INDEX idx_stadt ON adressen(stadt);
CREATE INDEX idx_plz ON adressen(plz);