von Markus Winand.

MySQL Beispiel Skripte für „Sortieren und Gruppieren“


Dieser Abschnitt enthält die MySQL Skripte und Ausführungspläne für Kapitel 4, „Die Join-Operation.

Indexed Order By

ALTER TABLE sales
 DROP INDEX sales_date;

ALTER TABLE sales
  ADD INDEX sales_dt_pr (sale_date, product_id);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date = CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date, product_id;

Die Ausführung erfolgt ohne „Extra: Using filesort“:

+-------------+------+-------------+------+-------------+
| select_type | type | key         | rows | Extra       |
+-------------+------+-------------+------+-------------+
| SIMPLE      | ref  | sales_dt_pr |    1 | Using where |
+-------------+------+-------------+------+-------------+

Derselbe Ausführungsplan wird verwendet, wenn nur nach PRODUCT_ID sortiert wird.

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date = CURDATE() - INTERVAL 1 DAY
  ORDER BY product_id;

Wenn der größer-gleich Operator verwendet wird, muss explizit sortiert werden.

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY product_id;

+-------------+------+-------------+------+----------------+
| select_type | type | key         | rows | Extra          |
+-------------+------+-------------+------+----------------+
| SIMPLE      | ref  | sales_dt_pr |  117 | Using where;   |
|             |      |             |      | Using filesort |
+-------------+------+-------------+------+----------------+

Order by ASC/DESC und NULLS FIRST/LAST

MySQL kann den Index auch rückwärts benutzen, ziegt das jedoch nicht extra im Ausführungsplan an:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date DESC, product_id DESC;
+-------------+-------+-------------+------+-------------+
| select_type | type  | key         | rows | Extra       |
+-------------+-------+-------------+------+-------------+
| SIMPLE      | range | sales_dt_pr |  117 | Using where | 
+-------------+-------+-------------+------+-------------+

Gemischte ASC und DESC Spezifikationen erfordern jedoch eine explizite Sortierung:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date ASC, product_id DESC;
+-------------+------+-------------+------+----------------+
| select_type | type | key         | rows | Extra          |
+-------------+------+-------------+------+----------------+
| SIMPLE      | ref  | sales_dt_pr |  117 | Using where;   |
|             |      |             |      | Using filesort |
+-------------+------+-------------+------+----------------+

MySQL akzeptiert zwar ASC und DESC in der Indexdefinition, ignoriert es aber:

ALTER TABLE sales
 DROP INDEX sales_dt_pr;

ALTER TABLE sales
  ADD INDEX sales_dt_pr (sale_date ASC, product_id DESC);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date ASC, product_id DESC;
+-------------+------+-------------+------+----------------+
| select_type | type | key         | rows | Extra          |
+-------------+------+-------------+------+----------------+
| SIMPLE      | ref  | sales_dt_pr |  117 | Using where;   |
|             |      |             |      | Using filesort |
+-------------+------+-------------+------+----------------+

Als Beweis kann man sehen, dass keine Sortierung stattfindet, wenn beide Spalten gleichreichtet sortiert werden:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date ASC, product_id ASC;
+-------------+-------+-------------+------+-------------+
| select_type | type  | key         | rows | Extra       |
+-------------+-------+-------------+------+-------------+
| SIMPLE      | range | sales_dt_pr |  117 | Using where | 
+-------------+-------+-------------+------+-------------+

Indexing Group By

Ein pipelined group by erkennt man daran, dass der Ausführungsplan keine Sortierung (filesort) anzeigt:

EXPLAIN
 SELECT product_id, sum(eur_value)
   FROM sales
  WHERE sale_date = CURDATE() - INTERVAL 1 DAY
  GROUP BY product_id;
+-------------+-------+-------------+------+-------------+
| select_type | type  | key         | rows | Extra       |
+-------------+-------+-------------+------+-------------+
| SIMPLE      | ref   | sales_dt_pr |    1 | Using where | 
+-------------+-------+-------------+------+-------------+

MySQL führt ein normales group by mit dem Sort/Group-Algorithmus aus, da der Hash-Algorithmus in Version 5.6 noch nicht implementiert ist.

EXPLAIN
 SELECT product_id, sum(eur_value)
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  GROUP BY product_id;
+-------------+-------+-------------+------+------------------+
| select_type | type  | key         | rows | Extra            |
+-------------+-------+-------------+------+------------------+
| SIMPLE      | range | sales_dt_pr |  117 | Using where;     |
|             |       |             |      | Using temporary; |
|             |       |             |      | Using filesort   |
+-------------+-------+-------------+------+------------------+

Ü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.

Kaufen Sie sein Buch bei Amazon

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.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

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 | CC-BY-NC-ND 3.0 Lizenz