Wie man MySQL-Indizes prüft und optimiert: Komplettanleitung mit SHOW INDEX, EXPLAIN und Performance‑Tuning

1. Einführung

MySQL ist ein relationales Datenbankmanagementsystem, das in vielen Webanwendungen und Datenverwaltungssystemen weit verbreitet ist. Um die Geschwindigkeit der Datenabfrage zu erhöhen, gibt es einen Mechanismus namens „Index“. Wird er jedoch nicht richtig verwaltet, können Indizes die Leistung tatsächlich verringern.

Warum ist das Überprüfen von Indizes wichtig?

Ein Index in einer Datenbank ist vergleichbar mit dem Index eines Buches. Gut gestaltete Indizes verbessern die Ausführungszeit von Suchabfragen. Es können jedoch folgende Probleme auftreten:

• Indizes sind nicht korrekt erstellt → Kann zu langsamen Suchvorgängen führen
• Unnötige Indizes existieren → Führt zu langsameren Updates und Inserts
• Unklarheit darüber, welche Indizes verwendet werden → Erschwert die Entscheidung, ob ungenutzte Indizes entfernt werden sollten

Was Sie in diesem Artikel lernen werden

• Der grundlegende Mechanismus von MySQL-Indizes
• Wie man aktuelle Indizes prüft (mit SQL-Befehlen)
• Wie man Indizes verwaltet und optimiert
• Techniken zur Analyse der Indexnutzung

Lassen Sie uns nun systematisch MySQL-Indizes kennenlernen und dieses Wissen nutzen, um die Datenbankleistung zu verbessern.

2. Was ist ein MySQL-Index?

Ein Index ist ein wesentliches Merkmal zur Verbesserung der Datenbankleistung. In diesem Abschnitt erklären wir das Grundkonzept, die Arten, Vorteile und Nachteile von Indizes.

Grundkonzept von Indizes

Ein Datenbank-Index ist ein Mechanismus, der ein schnelles Suchen von Werten in bestimmten Spalten ermöglicht. Beispielsweise muss bei der Suche nach einem bestimmten Datensatz in einer Tabelle mit einer großen Datenmenge ohne Index die Datenbank alle Datensätze durchsuchen (Full Table Scan). Durch die Verwendung eines Index wird die Datenabfrage effizient und die Verarbeitungsgeschwindigkeit deutlich erhöht.

Arten von MySQL-Indizes

MySQL unterstützt mehrere Indexarten, die jeweils für bestimmte Anwendungsfälle geeignet sind.

1. PRIMARY KEY (Primary Key Index)

• Kann pro Tabelle nur einmal festgelegt werden
• Garantiert die Eindeutigkeit der Tabelle
• Funktioniert als Clustered Index

1. UNIQUE Index

• Stellt sicher, dass Werte in der angegebenen Spalte nicht dupliziert werden
• NULL-Werte sind erlaubt (mehrere NULLs sind zulässig)

1. INDEX (General Index)

• Wird verwendet, um Suchvorgänge zu beschleunigen
• Duplizierte Daten sind erlaubt

1. FULLTEXT Index (for text search)

• Optimiert die Textsuche
• Wird in Kombination mit der Syntax MATCH ... AGAINST verwendet

1. SPATIAL Index (for geographic data)

• Entwickelt für räumliche (GIS) Daten

Vorteile und Nachteile von Indizes

Vorteile

• Verbessert die Geschwindigkeit von Abfrage‑Suchen
• Steigert die Leistung von JOIN‑Operationen und WHERE‑Klauseln
• Ermöglicht eine effizientere Abfrage spezifischer Daten

Nachteile

• Mehr Indizes verlangsamen INSERT‑, UPDATE‑ und DELETE‑Operationen
• Verbraucht Speicherplatz
• Schlecht entworfene Indizes können die Leistung mindern

3. Wie man MySQL-Indizes überprüft

Um MySQL-Indizes korrekt zu verwalten, ist es wichtig zu prüfen, welche Indizes derzeit für eine Tabelle definiert sind. In diesem Abschnitt erklären wir, wie man Indizes mit dem SHOW INDEX‑Befehl, INFORMATION_SCHEMA.STATISTICS und dem mysqlshow‑Befehl überprüft.

SHOW INDEX Befehl (Grundmethode)

In MySQL kann man den Befehl SHOW INDEX verwenden, um eine Liste der für eine bestimmte Tabelle definierten Indizes abzurufen. Dieser Befehl ermöglicht die Überprüfung von Details wie Indexname, von dem Index abgedeckte Spalten und ob eine eindeutige Einschränkung besteht.

Grundsyntax

SHOW INDEX FROM table_name;

Beispiel

Beispielsweise kann man, um die für die Tabelle users definierten Indizes zu prüfen, das folgende SQL ausführen:

SHOW INDEX FROM users;

Beispielausgabe

Verwendung von INFORMATION_SCHEMA.STATISTICS (Detaillierte Informationen erhalten)

Durch die Verwendung der MySQL-Systemtabelle INFORMATION_SCHEMA.STATISTICS lässt sich dieselbe Information wie mit SHOW INDEX, jedoch flexibler, abrufen.

Indizes für eine bestimmte Tabelle prüfen

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Indizes über die gesamte Datenbank abrufen

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

mysqlshow-Befehl (Überprüfung über die CLI)

Sie können Indexinformationen auch mit den MySQL‑Kommandozeilen‑Tools abrufen. Dies ist besonders nützlich, wenn Sie über SSH auf einem MySQL‑Server arbeiten.

Wie man den Befehl ausführt

mysqlshow -u username -p password database_name table_name

Beispiel

mysqlshow -u root -p my_database users

Was zu tun ist, wenn keine Indizes vorhanden sind

Wenn nach dem Ausführen von SHOW INDEX oder der Abfrage von INFORMATION_SCHEMA.STATISTICS keine Indizes angezeigt werden, hat die Tabelle möglicherweise keine geeigneten Indizes. In diesem Fall können Sie die Suchleistung verbessern, indem Sie bei Bedarf Indizes erstellen.

Einen neuen Index erstellen

CREATE INDEX idx_column ON users (email);

Primärschlüssel festlegen (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Unnötige Indizes entfernen

ALTER TABLE users DROP INDEX idx_column;

4. Wie man die Indexnutzung überprüft

Die Überprüfung, ob Indizes korrekt funktionieren, ist ein entscheidender Schritt bei der MySQL‑Leistungsoptimierung. In diesem Abschnitt erklären wir, wie man ermittelt, welche Indizes eine Abfrage verwendet, indem man den EXPLAIN‑Befehl und das Performance Schema nutzt.

Abfrageanalyse mit EXPLAIN

Der Befehl EXPLAIN wird verwendet, um zu visualisieren, wie eine gegebene SQL‑Abfrage ausgeführt wird. Er hilft Ihnen, die verwendeten Indizes, Zugriffsmethoden und den Ausführungsplan zu analysieren, was nützlich ist, um zu überprüfen, ob Indizes wie beabsichtigt funktionieren.

Grundsyntax

EXPLAIN SELECT * FROM table_name WHERE condition;

Beispiel

Zum Beispiel, um die Tabelle users mit der Spalte email als Bedingung zu durchsuchen:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Beispielausgabe

Wichtige Punkte

• Wenn type = ALL, führt die Abfrage einen Volltabellenscan durch, sodass Sie wahrscheinlich einen Index benötigen.
• Wenn ein Indexname in key erscheint, wird dieser Index verwendet.
• Wenn der Wert rows zu groß ist, kann eine Abfrageoptimierung erforderlich sein.

Verwendung des Performance Schema

Mit MySQLs performance_schema können Sie im Detail analysieren, welche Indizes verwendet werden und wie häufig sie während der Ausführung einer Abfrage eingesetzt werden.

Abfrageausführungsstatistiken abrufen

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Indexnutzung für eine bestimmte Tabelle prüfen

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

Was zu tun ist, wenn ein Index nicht verwendet wird

1. Abfrage überprüfen

Wenn ein Index nicht verwendet wird, kann die Struktur der Abfrage das Problem sein. Zum Beispiel kann das folgende Muster die Indexnutzung verhindern.

❌ Falsches Beispiel (Funktion deaktiviert Indexnutzung)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ Aufgrund von LOWER(email) könnte der Index auf email ignoriert werden.

✅ Korrigiertes Beispiel (Vermeiden Sie die Verwendung einer Funktion)

SELECT * FROM users WHERE email = 'test@example.com';

2. Index neu erstellen

Wenn ein vorhandener Index nicht ordnungsgemäß funktioniert, kann das Löschen und Neuerstellen des Indexes manchmal die Leistung verbessern.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Statistiken aktualisieren

Wenn Tabellestatistiken veraltet sind, verwendet MySQL möglicherweise Indizes nicht optimal. Sie können die Statistiken mit dem folgenden Befehl aktualisieren:

ANALYZE TABLE users;

5. Indexverwaltung

MySQL-Indexes sind entscheidend für die Verbesserung der Datenabrufleistung. Allerdings können sie bei unsachgemäßer Verwaltung die Gesamtleistung der Datenbank verschlechtern. In diesem Abschnitt erklären wir im Detail, wie man Indexe erstellt, löscht und unnötige Indexe identifiziert.

Indexe erstellen

Durch das Erstellen geeigneter Indexe können Sie die Datensuche erheblich beschleunigen. In MySQL können Sie Indexe mit CREATE INDEX oder ALTER TABLE hinzufügen.

Grundsyntax

CREATE INDEX index_name ON table_name(column_name);

Beispiel

Um einen Index zur Spalte email in der Tabelle users hinzuzufügen:

CREATE INDEX idx_email ON users(email);

Mehrspaltiger Index (Composite Index)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Eindeutiger Index

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Festlegen eines Primärschlüssels (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Indexe entfernen

Das Entfernen unnötiger Indexe hilft, den Datenbank-Overhead zu reduzieren.

Grundsyntax

ALTER TABLE table_name DROP INDEX index_name;

Beispiel

Zum Beispiel, um einen Index mit dem Namen idx_email zu löschen:

ALTER TABLE users DROP INDEX idx_email;

Identifizieren und Entfernen unnötiger Indexe

Prüfen auf ungenutzte Indexe

SELECT * FROM sys.schema_unused_indexes;

Tabellenstatus prüfen (Indexauswirkung)

SHOW TABLE STATUS LIKE 'users';

Unnötige Indexe entfernen

ALTER TABLE users DROP INDEX idx_unused;

Nach dem Löschen wird empfohlen, die Statistiken mit ANALYZE TABLE zu aktualisieren.

ANALYZE TABLE users;

6. Indexoptimierung (Leistungsverbesserung)

Eine ordnungsgemäße Indexverwaltung kann die MySQL-Abfrageleistung erheblich verbessern. Das bloße Erstellen von Indexen reicht jedoch nicht aus — richtiges Design, Verwaltung und Überwachung sind erforderlich, um optimale Leistung zu erhalten.

Richtige Indexgestaltung

Gut gestaltete Indexe können die Suchgeschwindigkeit in MySQL dramatisch erhöhen.

Fälle, in denen Indexe angewendet werden sollten

Fälle, in denen Indexe nicht angewendet werden sollten

Verwendung des Slow Query Logs

Das Slow Query Log ermöglicht es, langlaufende Abfragen zu identifizieren und zu analysieren, welche Indexe nicht verwendet werden.

Slow Query Log aktivieren

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Slow Query Log prüfen

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Eine langsame Abfrage analysieren

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Beispiel für das Anwenden eines Indexes

CREATE INDEX idx_last_login ON users(last_login);

Statistiken aktualisieren (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Statistiken aktualisieren)

ANALYZE TABLE users;

OPTIMIZE TABLE (Defragmentierung)

OPTIMIZE TABLE users;

7. FAQ zu Indexen (Häufig gestellte Fragen)

MySQL-Indexe sind ein wesentliches Mittel zur Verbesserung der Datenbankleistung. Bei unsachgemäßer Verwaltung können sie jedoch das Gegenteil bewirken. In diesem Abschnitt fassen wir häufig gestellte Fragen (FAQ) und Antworten zu MySQL-Indexen zusammen.

Wird die Suchgeschwindigkeit steigen, wenn ich mehr Indexe hinzufüge?

A. Nicht unbedingt.

Indexe sind dazu gedacht, die Abfrageleistung zu verbessern, aber das Hinzufügen zu vieler Indexe kann die Datenbankleistung tatsächlich verschlechtern.

• Erhöht den Schreibaufwand (INSERT, UPDATE, DELETE)
• Ob ein Index verwendet wird, hängt von der Abfrage ab
• Unnötige Indexe verbrauchen Speicher

Welche Spalten sollten Indexe haben?

A. Es ist sinnvoll, Indexe auf die folgenden Spaltentypen anzuwenden:

Werden Indexe automatisch erstellt?

A. Einige Indexe werden automatisch erstellt, andere müssen manuell hinzugefügt werden.

Automatisch erstellte Indizes

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Manuell erstellte Indizes

CREATE INDEX idx_email ON users(email);

Wie sollte ich zwischen B-Tree-, Hash- und FULLTEXT-Indizes wählen?

Wie kann ich die Größe von Indizes überprüfen?

SHOW TABLE STATUS LIKE 'users';

Wie kann ich prüfen, ob ein Index verwendet wird?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

Wann sollte ich einen Index entfernen?

Entfernen eines unnötigen Indexes

ALTER TABLE users DROP INDEX idx_unused;

8. Zusammenfassung

In diesem Artikel haben wir MySQL‑Indizes umfassend behandelt – von den Grundlagen über Verifikationsmethoden, Verwaltung, Optimierung bis hin zu häufig gestellten Fragen. Hier fassen wir die wichtigsten Punkte aus jedem Abschnitt zusammen und geben Best Practices für die Optimierung des MySQL‑Index‑Managements wieder.

Wichtigste Erkenntnisse

Was ist ein MySQL‑Index?

• Ein Index ist ein Mechanismus, der die Datenabfrage beschleunigt.
• Es gibt mehrere Typen, darunter B-Tree‑, Hash‑ und FULLTEXT‑Indizes.
• Am effektivsten, wenn er auf Spalten angewendet wird, die in WHERE‑Klauseln, JOINs und ORDER BY verwendet werden.

Wie man MySQL‑Indizes prüft

• Verwenden Sie den Befehl SHOW INDEX, um die Indexliste anzuzeigen.
• Nutzen Sie INFORMATION_SCHEMA.STATISTICS, um detaillierte Informationen abzurufen.

Wie man die Indexnutzung prüft

• Verwenden Sie EXPLAIN, um zu sehen, welche Indizes eine Abfrage nutzt.
• Nutzen Sie das Performance Schema, um die Häufigkeit der Indexnutzung zu analysieren.

Indexverwaltung

• Verwenden Sie CREATE INDEX, um Indizes auf geeignete Spalten anzuwenden.
• Verwenden Sie ALTER TABLE DROP INDEX, um unnötige Indizes zu entfernen.
• Prüfen Sie die Indexgröße mit SHOW TABLE STATUS und optimieren Sie bei Bedarf.

Indexoptimierung (Leistungsverbesserung)

• Wenden Sie Indizes auf häufig genutzte WHERE‑, JOIN‑ und ORDER BY‑Spalten an.
• Nutzen Sie das Slow‑Query‑Log, um langsame Abfragen zu identifizieren und zu optimieren.
• Aktualisieren Sie Statistiken mit ANALYZE TABLE und OPTIMIZE TABLE.

Best Practices für das MySQL‑Index‑Management

1. Identifizieren Sie Engpässe in Abfragen, bevor Sie Indizes hinzufügen.
2. Wählen Sie geeignete Indizes aus.

• Verwenden Sie Einzelspalten‑ und zusammengesetzte Indizes korrekt.
• Setzen Sie UNIQUE INDEX ein, wo eindeutige Einschränkungen nötig sind.

3. Entfernen Sie regelmäßig unnötige Indizes.

• Identifizieren Sie ungenutzte Indizes mit SHOW INDEX und schema_unused_indexes.

4. Aktualisieren Sie Statistiken regelmäßig.

• Aktualisieren Sie Statistiken mit ANALYZE TABLE.
• Führen Sie OPTIMIZE TABLE aus, um Fragmentierung durch Löschungen und Updates zu beheben.

Nächste Schritte

✅ Praktische Checkliste
✅ Haben Sie die aktuellen Indizes mit SHOW INDEX überprüft?
✅ Haben Sie das Slow‑Query‑Log aktiviert und langsame Abfragen identifiziert?
✅ Haben Sie den Abfrageausführungsplan mit EXPLAIN analysiert?
✅ Haben Sie unnötige Indizes entfernt und passende erstellt?
✅ Haben Sie Statistiken mit ANALYZE TABLE aktualisiert?

Abschließende Zusammenfassung

• Richtig verwaltete MySQL‑Indizes verbessern die Suchleistung erheblich.
• Nutzen Sie das Slow‑Query‑Log und EXPLAIN, um die Effektivität von Indizes zu analysieren und entsprechend zu optimieren.
• Erhalten Sie die Datenbankleistung, indem Sie regelmäßig Statistiken aktualisieren und Tabellen optimieren.

Damit endet der vollständige Leitfaden zum MySQL‑Index‑Management.

Nutzen Sie dieses Wissen, um schnellere und effizientere Datenbanksysteme zu bauen. 💡🚀