MySQL Beispiel Skripte 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 Ausführungspläne sind zur besseren Lesbarkeit abgekürzt.

Tabellen Setup

Die GENERATOR_X Views werden im Artikel „MySQL Zeilengenerator“ erklärt.

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;


INSERT INTO tbl
SELECT gen.n
     , DATE_SUB(CURDATE(), INTERVAL gen.n DAY)
     , MOD(gen.n,1234)
     , FLOOR(RAND()*10)
     , GROUP_CONCAT(CHAR((RAND() * 25)+97) SEPARATOR '')
     , IF(MOD(gen.n, 5) = 0, 'X', 'A')
  FROM generator_64k gen
     , generator_16 rnd
 WHERE gen.n < 50000
 GROUP BY gen.n;

VACUUM ANALYZE TABLE tbl;

Frage 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);


EXPLAIN
 SELECT text, date_column
   FROM tbl
  WHERE YEAR(date_column) = '2011';

EXPLAIN
 SELECT text, date_column
   FROM tbl
  WHERE date_column >= STR_TO_DATE('2011-01-01', '%Y-%m-%d')
    AND date_column <  STR_TO_DATE('2012-01-01', '%Y-%m-%d');


ALTER TABLE tbl DROP INDEX tbl_idx;

Der erste Ausführungsplan liest die ganze Tabelle (type=ALL). Der zweite Ausführungsplan verwendet den Index (type=range). Beachte, dass die Zeilenangabe den Effizienzunterschied ebenfalls wiederspiegelt.

+------+---------------+------+-------+-------------+
| type | possible_keys | key  | rows  | Extra       |
+------+---------------+------+-------+-------------+
| ALL  | NULL          | NULL | 50212 | Using where | 
+------+---------------+------+-------+-------------+


+-------+---------------+---------+-------+------------------+
| type  | possible_keys | key     | rows  | Extra            |
+-------+---------------+---------+-------+------------------+
| range | tbl_idx       | tbl_idx |   206 | [..] Using index | 
+-------+---------------+---------+-------+------------------+

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

EXPLAIN
 SELECT id, date_column
   FROM tbl
  WHERE a = 123
  ORDER BY date_column DESC
  LIMIT 1;


ALTER TABLE tbl DROP INDEX tbl_idx;

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; Using index | 
+------+---------------+---------+--------------------------+

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b);

EXPLAIN
 SELECT id, a, b
   FROM tbl
  WHERE a = 123
    AND b = 1;

EXPLAIN
 SELECT id, a, b
   FROM tbl
  WHERE b = 123;

ALTER TABLE tbl DROP INDEX tbl_idx;

Die zweite Abfrage liest den ganzen Index (type=Index). 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            |
+------+---------------+---------+------+------------------+
| ref  | tbl_idx       | tbl_idx |    5 | [..] Using index |
+------+---------------+---------+------+------------------+


+-------+---------------+---------+-------+------------------+
| type  | possible_keys | key     |  rows | Extra            |
+-------+---------------+---------+-------+------------------+
| index | NULL          | tbl_idx | 50304 | [..] Using index |
+-------+---------------+---------+-------+------------------+

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);

EXPLAIN
 SELECT id, text
   FROM tbl
  WHERE text LIKE '%TERM%';

ALTER TABLE tbl DROP INDEX tbl_idx;

Der Ausführungsplan sieht auf den ersten Blick gut aus, weil die Zugriffsmethode „Index“ angeführt ist und die Extra Spalte „Using Index“ anmerkt. Man muss jedoch immer skeptisch sein, wenn ein Index benutzt wird, obwohl die possible_keys Spalte keinen Eintrag hat. Die Zugriffsmethode „Index“ bedeutet nämlich, dass der ganze Index gelesen wird.

+-------+---------------+---------+-------+------------------+
| type  | possible_keys | key     |  rows | Extra            |
+-------+---------------+---------+-------+------------------+
| index | NULL          | tbl_idx | 50238 | [..] Using index |
+-------+---------------+---------+-------+------------------+

Frage 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column);

EXPLAIN
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = 123
  GROUP BY date_column;

EXPLAIN
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = 123
    AND b = 1
 GROUP BY date_column;

ALTER TABLE tbl DROP INDEX tbl_idx;

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

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.