von Markus Winand.

MySQL Beispiel Skripts für den „3-Minuten Test“


Dieser Abschnitt enthält die create, insert und select Kommandos für den „3-Minuten Test“. Mach den Test doch selbst, bevor du weiter liest.

Die create und insert Anweisungen findest du in diesem Download.

Die Ausführungspläne sind zur besseren Lesbarkeit abgekürzt.

Frage 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);
SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2024;
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2024-01-01'
   AND date_column <  DATE'2018-01-01';

Der erste Ausführungsplan liest den ganzen Index (type=index). Der zweite Ausführungsplan liest nur die relevanten Zeilen aus dem Index (type=range). Beachte, dass die Zeilenangabe den Effizienzunterschied ebenfalls widerspiegelt.

+-------+---------------+---------+------+--------------------------+
| type  | possible_keys | key     | rows | Extra                    |
+-------+---------------+---------+------+--------------------------+
| index | NULL          | tbl_idx |  300 | Using where; Using index |
+-------+---------------+---------+------+--------------------------+
+-------+---------------+---------+------+--------------------------+
| type  | possible_keys | key     | rows | Extra                    |
+-------+---------------+---------+------+--------------------------+
| range | tbl_idx       | tbl_idx |  271 | Using where; Using index |
+-------+---------------+---------+------+--------------------------+

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1;

Die Abfrage nutzt die Zugriffsmethode (type) REF – ähnlich einem range scan. Beachte vor allem, dass es keine Sortieroperation in der Extra Spalte gibt.

+------+---------------+---------+-------------+
| type | possible_keys | key     | Extra       |
+------+---------------+---------+-------------+
| ref  | tbl_idx       | tbl_idx | Using where | 
+------+---------------+---------+-------------+

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b);
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;
SELECT *
  FROM tbl
 WHERE b = 1;
DROP INDEX tbl_idx ON tbl;
CREATE INDEX tbl_idx ON tbl (b, a);
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;
SELECT *
  FROM tbl
 WHERE b = 1;

Die erste Abfrage kann beide Indizes optimal nutzen:

+------+---------------+---------+------+-------+
| type | possible_keys | key     | rows | Extra |
+------+---------------+---------+------+-------+
| ref  | tbl_idx       | tbl_idx |    1 | NULL  |
+------+---------------+---------+------+-------+
+------+---------------+---------+------+-------+
| type | possible_keys | key     | rows | Extra |
+------+---------------+---------+------+-------+
| ref  | tbl_idx       | tbl_idx |    1 | NULL  |
+------+---------------+---------+------+-------+

Die zweite Abfrage kann den ersten Index nicht effizient nutzten und liest stattdessen die ganze Tabelle (type=ALL). Wenn die Spalten im Index umgedreht werden, können beide Abfragen optimal vom Index profitieren (Type=REF bzw. Range).

+------+---------------+------+------+-------------+
| type | possible_keys | key  | rows | Extra       |
+------+---------------+------+------+-------------+
| ALL  | NULL          | NULL |  300 | Using where |
+------+---------------+------+------+-------------+
+------+---------------+---------+------+-------+
| type | possible_keys | key     | rows | Extra |
+------+---------------+---------+------+-------+
| ref  | tbl_idx       | tbl_idx |    2 | NULL  |
+------+---------------+---------+------+-------+

Beachte, dass MySQL auch einen Full-Index-Scan (type=Index) durchführen könnte. Obwohl das besser sein kann als ein Full-Table-Scan, ist es noch immer schlechter als ein Index-Range-Scan (type=ref oder range)

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);
SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%';

Der Ausführungsplan zeigt klar, dass ein Index-Range-Scan durchgeführt wird (type=range). Da der Suchbegriff 'TERM%' nur ganz am ende ein Wildcard-Zeichen hat, kann der komplette Suchbegriff als Index-Zugriffsprädikat genutzt werden.

+-------+---------+---------+------+-----------------------+
| type  | key     | key_len | ref  | Extra                 |
+-------+---------+---------+------+-----------------------+
| range | tbl_idx | 258     | NULL | Using index condition |
+-------+---------+---------+------+-----------------------+

Frage 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Die erste Abfrage nutzt den Index, um auf der Spalte A zu suchen, kann aber auch die selektierte Spalte DATE_COLUMN aus dem Index lesen (Extra=Using Index). Die zweite Abfrage muss zusätzlich in die Tabelle sehen, um den Filter auf der Spalte B zu prüfen. Obwohl dieser Zugriff das Ergebnis schmälert, wird die Abfrage viel langsamer.

+------+---------------+---------+--------------------------+
| type | possible_keys | key     | Extra                    |
+------+---------------+---------+--------------------------+
| ref  | tbl_idx       | tbl_idx | Using where; Using index |
+------+---------------+---------+--------------------------+
+------+---------------+---------+------------------------------------+
| type | possible_keys | key     | Extra                              |
+------+---------------+---------+------------------------------------+
| ref  | tbl_idx       | tbl_idx | Using index condition; Using where |
+------+---------------+---------+------------------------------------+
Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Bluesky oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Neuigkeiten Abonnieren

Markus Winands MailinglistenMarkus Winands RSS-FeedMarkus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf TwitterMarkus Winand auf Bluesky
Copyright 2010-2025 Markus Winand. Alle Rechte vorbehalten.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO