MySQL-Indizierung meistern für schnellere Abfrageleistung

Die MySQL-Indizierung ist normalerweise der erste Ort, an den ich mich wende, wenn eine Datenbank langsam erscheint, aber es ist auch einer der einfachsten Orte, um einen selbstbewussten Fehler zu machen. Ein Index kann einen Tabellenscan in eine schnelle Suche verwandeln. Er kann auch Schreibvorgänge verlangsamen, Speicher verschwenden und ein falsches Gefühl des Fortschritts vermitteln, wenn der Abfrageplaner ihn nie verwendet.

Die praktische Frage ist nicht "Sollte diese Spalte einen Index haben?" Die bessere Frage ist: "Welche Abfrage versuche ich billiger zu machen, und wie werde ich beweisen, dass der Index geholfen hat?" Behalten Sie diese Frage beim Lesen im Hinterkopf. Gute Indizierung beginnt mit echten Abfragen, nicht mit einer Liste von Spalten, die wichtig aussehen.

Was ist ein MySQL-Index?

Ein MySQL-Index ist eine Datenstruktur, die die Geschwindigkeit von Datenabrufoperationen in einer Datenbanktabelle verbessert. Stellen Sie es sich wie einen Index in einem Buch vor: Anstatt das gesamte Buch zu lesen, um ein bestimmtes Thema zu finden, können Sie das Thema im Index nachschlagen, der Ihnen die genaue Seitenzahl verrät. Ähnlich ermöglicht ein Datenbankindex MySQL, Zeilen, die einer bestimmten Abfragebedingung entsprechen, schnell zu lokalisieren, ohne die gesamte Tabelle zu scannen.

Wenn Sie eine Tabelle abfragen, kann MySQL einen Index verwenden, um die relevanten Zeilen viel schneller zu finden, als wenn es jede Zeile untersuchen würde. Dies ist besonders vorteilhaft für Tabellen mit einer großen Anzahl von Zeilen oder für Abfragen, die Filterung (WHERE-Klauseln), das Verknüpfen von Tabellen (JOIN-Klauseln) oder Sortierung (ORDER BY-Klauseln) beinhalten.

Wie Indizes funktionieren

MySQL verwendet üblicherweise B-Baum-Indizes für normale InnoDB-Indizes. Ein B-Baum hält Schlüssel in sortierter Reihenfolge, weshalb er gut für Gleichheitssuchen, Bereiche, geordnete Scans und viele Joins geeignet ist. Wenn Sie einen Index für eine oder mehrere Spalten erstellen, baut MySQL eine Struktur auf, in der:

• Blattknoten die eigentlichen Datenzeiger oder, im Falle von geclusterten Indizes (wie InnoDBs Primärschlüssel), die Datenzeilen selbst enthalten.
• Interne Knoten Schlüssel enthalten, die helfen, den Baum zu navigieren, um den richtigen Blattknoten zu finden.

Wenn eine Abfrage die linke Seite dieses Index verwenden kann, kann MySQL zu einem schmalen Teil des Baums navigieren, anstatt die gesamte Tabelle zu lesen. Das ist der eigentliche Gewinn. Der Index macht nicht jede Abfrage schnell; er macht bestimmte Zugriffsmuster billig.

Arten von MySQL-Indizes

MySQL unterstützt verschiedene Arten von Indizes, jede mit ihren eigenen Stärken und Anwendungsfällen.

1. PRIMARY KEY

• Eine PRIMARY KEY-Einschränkung stellt sicher, dass jeder Wert in der(n) Spalte(n) eindeutig und nicht NULL ist. Sie wird implizit indiziert.
• Eine Tabelle kann nur einen PRIMARY KEY haben.
• InnoDB-Tabellen sind physisch nach ihrem Primärschlüssel (geclusterter Index) geordnet.

Beispiel:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. UNIQUE Index

• Ein UNIQUE-Index erzwingt, dass alle Werte in der/den indizierten Spalte(n) unterschiedlich sind. Er erlaubt NULL-Werte, aber mehrere NULLs sind erlaubt (es sei denn, die Spalte ist auch Teil eines PRIMARY KEY oder einer anderen UNIQUE-Einschränkung, die dies verhindert).
• Nützlich zur Sicherstellung der Datenintegrität, wenn eine Spalte eindeutig sein muss, aber nicht der primäre Bezeichner ist.

Beispiel:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (oder KEY)

• Ein Standardindex, auch als nicht-eindeutiger Index bezeichnet.
• Wird verwendet, um den Datenabruf zu beschleunigen. Erzwingt keine Eindeutigkeit.

Beispiel:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. FULLTEXT Index

• Wird für Volltextsuchen in CHAR-, VARCHAR- und TEXT-Spalten verwendet.
• Ermöglicht Stichwortsuchen in großen Textfeldern.
• Wird von InnoDB in modernen MySQL-Versionen unterstützt. Ältere MySQL-Installationen können andere Einschränkungen haben, überprüfen Sie daher Ihre genaue Version, bevor Sie darauf aufbauen.

Beispiel:

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. SPATIAL Index

• Wird zum Indizieren räumlicher Datentypen wie Punkte, Linien und Polygone verwendet.
• Verhalten und Anforderungen variieren je nach MySQL-Version und Speicher-Engine. Testen Sie die genaue räumliche Abfrage, die Sie ausführen möchten; gehen Sie nicht davon aus, dass ein räumlicher Index jedem GIS-ähnlichen Prädikat hilft.

6. HASH Index (Begrenzte Nutzung)

• Die MEMORY-Speicher-Engine von MySQL unterstützt HASH-Indizes. Sie sind für Gleichheitssuchen ausgelegt, nicht für Bereichsscans oder Sortierungen.
• Kein Allzweck-Indextyp für die meisten gängigen Szenarien.

Erstellen und Verwalten von Indizes

So erstellen Sie einen Index

Sie können Indizes entweder beim Erstellen einer Tabelle oder durch Ändern einer vorhandenen Tabelle erstellen.

1. Bei der Tabellenerstellung:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. Ändern einer vorhandenen Tabelle:

-- Einen einspaltigen Index hinzufügen
ALTER TABLE customers
ADD INDEX idx_email (email);

-- Einen eindeutigen Index hinzufügen
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- Einen mehrspaltigen (zusammengesetzten) Index hinzufügen
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

So löschen Sie einen Index

Wenn ein Index nicht mehr benötigt wird oder die Leistung negativ beeinflusst (z. B. bei Schreibvorgängen), können Sie ihn löschen.

-- Einen Standardindex löschen
ALTER TABLE customers
DROP INDEX idx_email;

-- Einen eindeutigen Index löschen
ALTER TABLE users
DROP INDEX uidx_username;

Mehrspaltige (zusammengesetzte) Indizes

Zusammengesetzte Indizes werden für zwei oder mehr Spalten erstellt. Die Reihenfolge der Spalten in einem zusammengesetzten Index ist entscheidend.

• Ein zusammengesetzter Index auf (col1, col2) kann für Abfragen verwendet werden, die nur nach col1 filtern, oder sowohl nach col1 UND col2.
• Er wird im Allgemeinen nicht für Abfragen verwendet, die nur nach col2 filtern.

Beispiel:

Betrachten Sie einen Index auf (customer_id, order_date). Dieser Index ist am effektivsten für Abfragen wie:

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

Er könnte für SELECT * FROM orders WHERE order_date = '2023-10-27'; nicht sehr nützlich sein.

Bewährte Methoden für die MySQL-Indizierung

1. Indizieren Sie Spalten, die in WHERE-, JOIN- und ORDER BY-Klauseln verwendet werden

Dies sind die häufigsten Stellen, an denen Indizes erhebliche Leistungsvorteile bieten.

• WHERE-Klauseln: Filterbedingungen sind der primäre Anwendungsfall.
• JOIN-Bedingungen: Das Indizieren von Spalten, die in ON-Klauseln von JOIN-Anweisungen verwendet werden, beschleunigt Tabellenverknüpfungen erheblich.
• ORDER BY- und GROUP BY-Klauseln: Indizes können MySQL helfen, Sortiervorgänge zu vermeiden.

2. Verwenden Sie zusammengesetzte Indizes mit Bedacht

• Die Reihenfolge ist wichtig: Setzen Sie die Spalten, die zur Abfrageform passen, an die erste Stelle. Gleichheitsfilter kommen normalerweise vor Bereichsfiltern. Spalten, die für die Sortierung verwendet werden, können nach den Filterspalten helfen.
• Setzen Sie nicht blind die selektivste Spalte an die erste Stelle, wenn Ihre tatsächlichen Abfragen nicht danach filtern. Ein Index auf (status, created_at) kann hervorragend für WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50 sein, selbst wenn status eine niedrige Kardinalität hat, weil er dem Zugriffsmuster entspricht.

3. Verwenden Sie EXPLAIN vorher und nachher

Beurteilen Sie einen Index niemals nur aufgrund von Hoffnung. Führen Sie EXPLAIN aus, fügen Sie den Index in einer Staging- oder wartungssicheren Umgebung hinzu und führen Sie dann erneut EXPLAIN aus.

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

Achten Sie auf type, key, rows und Extra. Wenn key NULL ist, hat MySQL keinen Index ausgewählt. Wenn rows immer noch nahe an der Tabellengröße liegt, ist der Index für diese Abfrage möglicherweise nicht selektiv genug. Wenn Extra Using filesort sagt, ist das nicht automatisch schlecht, aber es sagt Ihnen, dass MySQL die Zeilen nicht in der angeforderten Reihenfolge aus dem gewählten Index zurückgeben konnte.

Für MySQL 8.0.18 und höher kann EXPLAIN ANALYZE noch nützlicher sein, da es die Abfrage ausführt und die tatsächliche Zeit und Zeilenanzahlen meldet:

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Verwenden Sie es vorsichtig in Produktionssystemen, da es die Anweisung ausführt.

4. Bauen Sie Indizes um Workflows herum, nicht um Tabellen

Stellen Sie sich einen Admin-Bildschirm vor, der kürzlich fehlgeschlagene Zahlungen auflistet:

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

Ein Index nur auf status kann MySQL immer noch dazu zwingen, eine große Anzahl fehlgeschlagener Zeilen zu sortieren. Ein Index auf (status, created_at) ist normalerweise eine bessere Übereinstimmung, da MySQL fehlgeschlagene Zeilen finden und sie in zeitlicher Reihenfolge lesen kann. Wenn die Abfrage nur Spalten im Index zurückgibt, könnten Sie einen abdeckenden Index in Betracht ziehen:

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

Das kann schnell sein, ist aber nicht kostenlos. Der Index ist breiter, benötigt mehr Speicher und kostet bei jedem Schreibvorgang mehr. Ich greife nur bei heißen Abfragen zu abdeckenden Indizes, die oft genug ausgeführt werden, um die zusätzlichen Wartungskosten zu rechtfertigen.

5. Achten Sie auf Indizes, die richtig aussehen, aber nichts tun

Dies sind häufige Fallstricke:

• Eine Funktion versteckt den indizierten Wert: WHERE DATE(created_at) = '2025-01-01'.
• Ein führender Platzhalter verhindert die normale B-Baum-Nutzung: WHERE email LIKE '%@example.com'.
• Ein Typkonflikt erzwingt eine Konvertierung: Der Vergleich einer Integer-Spalte mit einer in Anführungszeichen gesetzten Zeichenfolge kann funktionieren, aber er kann Pläne in realen Schemata verwirren.
• Der Index beginnt mit der falschen Spalte für die Abfrage: (created_at, customer_id) ist nicht dasselbe wie (customer_id, created_at).

Schreiben Sie das Prädikat um, wenn Sie können:

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

Diese Form ermöglicht es MySQL, einen Bereichsscan auf created_at zu verwenden.

6. Entfernen Sie redundante und ungenutzte Indizes sorgfältig

Übermäßige Indizierung ist ein leises Leistungsproblem. Jeder zusätzliche Sekundärindex muss während INSERT, UPDATE und DELETE gewartet werden. Bei einer schreibintensiven Tabelle können fünf ungenutzte Indizes mehr ausmachen als ein langsames SELECT.

In MySQL 5.7 und 8.0 kann das sys-Schema Ihnen helfen, Kandidaten zu finden:

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

Behandeln Sie diese Ausgabe als Hinweis, nicht als Befehl. Ein Index kann ungenutzt erscheinen, weil der Server kürzlich neu gestartet wurde, weil ein monatlicher Bericht noch nicht ausgeführt wurde oder weil der Datenverkehr in der Staging-Umgebung nicht mit der Produktion übereinstimmt. Bevor Sie einen Index löschen, überprüfen Sie den Bereitstellungsverlauf, geplante Jobs und Fremdschlüsselanforderungen.

7. Fügen Sie Indizes sicher auf großen Tabellen hinzu

Bei kleinen Tabellen ist ALTER TABLE ... ADD INDEX normalerweise ereignislos. Bei einer großen Produktionstabelle kann es eine echte Operation sein. Abhängig von der MySQL-Version, der Speicher-Engine, der Tabellendefinition und dem genauen DDL kann das Hinzufügen eines Index Online-DDL verwenden oder immer noch Druck durch Metadatensperren, temporären Speicherplatz, Redo-Generierung und Replikationsverzögerung erzeugen.

Bevor Sie einen großen Index hinzufügen, überprüfen Sie:

• Wie groß die Tabelle und die vorhandenen Indizes sind.
• Ob Replikate mithalten können.
• Ob Ihre MySQL-Version den Online-Algorithmus unterstützt, den Sie erwarten.
• Ob die Anwendung eine Metadatensperre tolerieren kann, wenn eine lange Transaktion das DDL blockiert.

Verwenden Sie für sensible Systeme ein Migrationstool wie pt-online-schema-change oder gh-ost oder planen Sie das DDL während eines verkehrsarmen Zeitfensters.

Eine praktische Routine zur Indexüberprüfung

Wenn ich eine langsame MySQL-Abfrage überprüfe, verwende ich diese Reihenfolge:

1. Erfassen Sie das genaue SQL mit echten Bindungswerten.
2. Führen Sie EXPLAIN und, wo sicher, EXPLAIN ANALYZE aus.
3. Überprüfen Sie, ob die vorhandenen Indizes zum WHERE-, JOIN- und ORDER BY-Muster passen.
4. Fügen Sie den kleinsten nützlichen zusammengesetzten Index in der Staging-Umgebung hinzu.
5. Vergleichen Sie untersuchte Zeilen, Abfragezeit und Schreibauswirkung.
6. Rollen Sie vorsichtig aus und beobachten Sie die Slow-Query-Logs und die Replikationsverzögerung.

Diese Routine hält die Indizierung ehrlich. Sie versuchen nicht, Indizes zu sammeln. Sie versuchen, die Arbeit zu reduzieren, die MySQL für die Abfragen leisten muss, die Ihre Anwendung tatsächlich ausführt.