MySQL CREATE INDEX-Anweisung

Die Anweisung `CREATE INDEX` in MySQL wird verwendet, um einen Index für die Spalte(n) einer Tabelle zu erstellen, um die Geschwindigkeit der Abfrage zu erhöhen. Indizes sind entscheidend für die Optimierung der Datenbankleistung, vor allem bei großen Datenmengen.

Verwendung

Die Anweisung `CREATE INDEX` wird verwendet, wenn du die Leistung von Datenabfragen verbessern willst. Sie ist besonders nützlich, um `SELECT`-Abfragen zu beschleunigen, die Daten filtern oder sortieren.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

In dieser Syntax definiert `CREATE INDEX` den Indexnamen und gibt die Tabelle und die Spalten an, für die der Index erstellt wird.

Beispiele

1. Grundlegende Indexerstellung

CREATE INDEX idx_lastname
ON employees (last_name);

In diesem Beispiel wird ein Index namens `idx_lastname` auf der Spalte `last_name` der Tabelle `employees` erstellt, um Abfragen zu optimieren, die nach Nachnamen filtern.

2. Komposit-Index

CREATE INDEX idx_name_dob
ON employees (last_name, birth_date);

Diese Syntax zeigt, wie man einen zusammengesetzten Index auf "Nachname" und "Geburtsdatum" erstellt, der für Abfragen nützlich ist, die nach beiden Spalten filtern.

3. Einzigartiger Index

CREATE UNIQUE INDEX idx_email
ON users (email);

In diesem Beispiel wird ein eindeutiger Index für die Spalte `E-Mail` der Tabelle `Benutzer` erstellt, um sicherzustellen, dass alle E-Mail-Einträge eindeutig sind und um die Suche nach E-Mails zu beschleunigen.

4. Fulltext Index

CREATE FULLTEXT INDEX idx_content
ON articles (content);

Dieses Beispiel erstellt einen `FULLTEXT`-Index auf der Spalte `Inhalt` der Tabelle `Artikel`, der für die Volltextsuche nützlich ist.

5. Räumlicher Index

CREATE SPATIAL INDEX idx_location
ON locations (geo_point);

Dadurch wird ein "SPATIAL"-Index für die Spalte "geo_point" in der Tabelle "locations" erstellt, was für geografische Datentypen von Vorteil ist.

Tipps und bewährte Praktiken

• Selektiv indizieren. Erstelle Indizes nur für Spalten, die häufig durchsucht oder sortiert werden, um unnötigen Overhead zu vermeiden.
• Überwache die Leistung. Verwende die "EXPLAIN"-Anweisung, um die Abfrageleistung zu bewerten und festzustellen, ob ein Index von Vorteil ist.
• Berücksichtige den Index-Typ. Wähle den passenden Indextyp (z. B. Unique, Volltext, Spatial) auf der Grundlage deiner Daten und Abfrageanforderungen.
• Gleichgewicht zwischen Lesen und Schreiben. Sei dir bewusst, dass Indizes zwar die Lesevorgänge beschleunigen, aber die Schreibvorgänge (INSERT, UPDATE, DELETE) verlangsamen können.
• Interne Abläufe. Die meisten Indizes verwenden B-Baum-Strukturen, um die Daten sortiert zu halten, was ein schnelles Nachschlagen ermöglicht.
• Beschränkungen. Verstehe die Einschränkungen wie die maximale Anzahl von Indizes pro Tabelle oder die maximale Größe der Indexschlüssel.
• Namenskonventionen. Verwende eindeutige und beschreibende Namen für Indizes, um die Lesbarkeit zu erhalten, z.B. mit dem Präfix `idx_`.
• Verwaltbarkeit. Überprüfe und verwalte die Indizes regelmäßig mit `ALTER TABLE`, um sie zu ändern, und `DROP INDEX`, um unnötige Indizes zu entfernen.

Verwalten und Entfernen von Indizes

Um einen Index zu ändern, kannst du die Anweisung `ALTER TABLE` verwenden. Um einen Index zu entfernen, wird die Anweisung `DROP INDEX` verwendet, wie unten gezeigt:

DROP INDEX idx_lastname ON employees;

Überlegungen

• Primärschlüssel vs. Einzigartiger Index. Ein Primärschlüssel ist eine Einschränkung, die eindeutige Werte und Nichtnullbarkeit gewährleistet, während ein eindeutiger Index nur die Eindeutigkeit erzwingt.
• Niedrige Kardinalitätsspalten. Vermeide die Indizierung von Spalten mit geringer Kardinalität, da sie möglicherweise keine nennenswerten Leistungsvorteile bringen.
• Kleine Tabellen. Indizes für sehr kleine Tabellen bringen unter Umständen keine spürbaren Leistungsverbesserungen, da die Pflege des Indexes zu viel Aufwand bedeutet.