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 |
+------+---------------+---------+------------------------------------+