MySQL Beispiel Skripte für „Sortieren und Gruppieren“


Dieser Abschnitt enthält die MySQL Skripte und Ausführungspläne für Kapitel 4.

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

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.