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) = 2017
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2017-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 |
+------+---------------+---------+------------------------------------+

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Sein Training

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

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz