Die Seite, die zehn Sekunden brauchte
Ein Support-Team öffnet die Anfragen eines Kunden. Am ersten Tag erscheint die Seite sofort. Ein Jahr später gibt es Millionen Anfragen, und dieselbe Seite braucht zehn Sekunden.
Niemand hat die Abfrage geändert. Die Tabelle ist gewachsen, und die Datenbank liest jetzt alle Anfragen, um Ihre zu finden.
Ein Index ist die übliche Abhilfe, und EXPLAIN zeigt Ihnen, ob Sie einen brauchen.
Am Ende dieser Lektion
Notiz
Ich kann eine Abfrage mit einem Index beschleunigen und mit EXPLAIN prüfen.
Schlüsselwörter
| Begriff | Was er bedeutet | Beispiel |
|---|---|---|
| Index | eine sortierte Liste neben einer Tabelle, mit der die Datenbank Zeilen findet, ohne alle zu lesen | |
| Volle Suche | alle Zeilen einer Tabelle lesen | type ALL in EXPLAIN |
| EXPLAIN | ein Befehl, der zeigt, wie die Datenbank eine Abfrage ausführen will, ohne sie auszuführen | |
| Abfrageplan | die Schritte, die die Datenbank wählt, um eine Abfrage zu beantworten | |
| Schätzung | die ungefähre Annahme der Datenbank, keine genaue Zahl |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Eine Tabelle, groß genug, um langsam zu sein
Der Shop des Labs hat nur einige Dutzend Artikel, deshalb ist jede Abfrage sofort fertig. Um einen Index arbeiten zu sehen, brauchen Sie mehr Zeilen.
Diese Runs legen in Ihrer eigenen Datenbank eine Tabelle anfragen mit 5000 Zeilen an. Der Shop bleibt unberührt.
Sie können sie jederzeit wiederholen, um von vorn zu beginnen.
Das Zertifikat des Servers annehmen
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfHier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Eine Abkürzung für den Client
alias db='mariadb -h db -u student -pstudent -t -e'Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Eine eigene Datenbank anlegen
db "CREATE DATABASE IF NOT EXISTS student_depot"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Eine alte Tabelle wegwerfen
db "DROP TABLE IF EXISTS student_depot.anfragen"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Die Tabelle anlegen
db "CREATE TABLE student_depot.anfragen (id INT PRIMARY KEY, kunde_id INT NOT NULL, status VARCHAR(10) NOT NULL, geoeffnet_am DATE NOT NULL)"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Mit 5000 Anfragen füllen
db "SET SESSION cte_max_recursion_depth = 10000; INSERT INTO student_depot.anfragen WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000) SELECT i, 1 + (i * 37) % 800, IF(i % 5 = 0, 'offen', 'erledigt'), DATE_ADD('2024-01-01', INTERVAL i % 365 DAY) FROM n"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Sie zählen
db "SELECT COUNT(*) AS zeilen_in_tabelle FROM student_depot.anfragen"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
EXPLAIN zeigt den Plan
Schreiben Sie das Wort EXPLAIN vor eine Abfrage. Die Datenbank führt sie nicht aus. Sie zeigt, wie sie es täte.
Drei Spalten zählen. type sagt, wie die Tabelle gelesen wird. key nennt den benutzten Index, oder NULL, wenn es keinen gibt. rows ist ihre Schätzung, wie viele Zeilen sie ansehen muss.
Fragen Sie nach den Anfragen eines einzigen Kunden.
Der Plan für einen Kunden, ohne Index
db "EXPLAIN SELECT * FROM student_depot.anfragen WHERE kunde_id = 42"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Beispiel: den Plan lesen
| Spalte | Was Sie sahen | Was es bedeutet |
|---|---|---|
| type | ALL | eine volle Suche: alle Zeilen werden gelesen |
| key | NULL | es wird kein Index benutzt |
| rows | etwa 5000 | sie erwartet, die ganze Tabelle zu lesen |
| Extra | Using where | sie liest jede Zeile und prüft dann kunde_id |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Ein Index ist eine sortierte Abkürzung
Das Register eines Buchs nennt Wörter der Reihe nach mit der Seitenzahl. Sie lesen nicht das ganze Buch, um ein Wort zu finden.
Ein Index einer Datenbank tut dasselbe. Er hält die Werte von kunde_id in Reihenfolge, jeden mit dem Ort seiner Zeile.
CREATE INDEX legt einen an. Geben Sie einen Namen an, dann Tabelle und Spalte.
Einen Index auf kunde_id anlegen
db "CREATE INDEX idx_kunde ON student_depot.anfragen (kunde_id)"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Den Plan noch einmal erfragen
db "EXPLAIN SELECT * FROM student_depot.anfragen WHERE kunde_id = 42"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Von 5000 Zeilen auf etwa 6
Jetzt ist type gleich ref, key gleich idx_kunde und rows gleich 6. Die Datenbank geht direkt zu den Anfragen dieses Kunden.
Die Abfrage hat sich nicht geändert. Nur der Plan.
Ein Primärschlüssel hat schon einen Index. Ein Fremdschlüssel auch: MySQL legt ihn für Sie an.
Die Indizes der Tabelle orders ansehen
mariadb -h db -u student -pstudent shop -t -e "SHOW INDEX FROM orders"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein Index hat seinen Preis
Ein Index ist eine zweite Kopie einer Spalte, in Reihenfolge gehalten. Er braucht Platz.
Jedes INSERT, UPDATE und DELETE muss auch den Index ändern. Jeder weitere Index macht das Schreiben also etwas langsamer.
Legen Sie einen Index für Fragen an, die Sie oft stellen, nicht für jede Spalte.
Den Platz eines Index ansehen
db "ANALYZE TABLE student_depot.anfragen; SELECT data_length, index_length FROM information_schema.tables WHERE table_schema = 'student_depot' AND table_name = 'anfragen'"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein häufiger Fehler
Warnung
Viele denken, ein Index werde immer benutzt, sobald es ihn gibt. Das stimmt nicht. Versteckt die Abfrage die Spalte in einer Funktion, kann die Datenbank die sortierte Liste nicht nutzen. Sehen Sie sich den Plan an, nehmen Sie nichts an.
Das Öffnungsdatum indizieren
db "CREATE INDEX idx_geoeffnet ON student_depot.anfragen (geoeffnet_am)"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Der Plan mit einer Funktion auf der Spalte
db "EXPLAIN SELECT * FROM student_depot.anfragen WHERE YEAR(geoeffnet_am) = 2024"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Der Plan mit einem Bereich auf der Spalte
db "EXPLAIN SELECT * FROM student_depot.anfragen WHERE geoeffnet_am >= '2024-03-01' AND geoeffnet_am < '2024-03-08'"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.