Ich verwende die Test-DB von https://github.com/datacharmer/test_db. Es hat eine moderate Größe von 160 Mb. Zum Ausführen von Abfragen verwende ich MySQL Workbench.

Der folgende Code läuft in 0.015s

SELECT *
FROM employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no

Der ähnliche Code mit GROUP BY wird für 15.0s ausgeführt

SELECT AVG(salary), gender
FROM employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY gender

Ich habe den Ausführungsplan für beide Abfragen überprüft und festgestellt, dass die Abfragekosten in beiden Fällen ähnlich sind und etwa 600.000 betragen. Ich sollte hinzufügen, dass die Mitarbeitertabelle 300.000 Zeilen hat und die Gehaltstabelle ungefähr 3 Millionen Zeilen enthält.

Kann jemand einen Grund vorschlagen, warum der Unterschied in der Ausführungszeit so groß ist? Ich brauche diese Erklärung, um zu verstehen, wie SQL besser funktioniert.

Problemlösung: Wie ich aufgrund von Kommentaren und Antworten feststellte, war das Problem darauf zurückzuführen, dass ich nicht bemerkte, dass meine IDE bei der ersten Abfrage das Ergebnis auf 1000 Zeilen beschränkte. So habe ich 0.015s bekommen. In Wirklichkeit dauert es in meinem Fall 10,0 Sekunden, um eine Verbindung herzustellen. Wenn der Index für das Geschlecht erstellt wurde (Indizes für employee.emp_no und salaries.emp_no sind in dieser Datenbank bereits vorhanden), dauert es 10,0 Sekunden, bis der Beitritt und die Gruppierung nach abgeschlossen sind. Ohne Index für Geschlecht dauert die zweite Abfrage 18,0 Sekunden.

-1
xevepisis 20 Jän. 2019 im 19:10

3 Antworten

Beste Antwort

Das EXPLAIN für die erste Abfrage zeigt, dass es einen Tabellenscan (type=ALL) von 300 KB Zeilen von employees durchführt und für jede eine teilweise Primärschlüssel-Suche (type=ref) durchführt 1 Zeile (geschätzt) in salaries.

mysql> explain SELECT * FROM employees 
  INNER JOIN salaries ON employees.emp_no = salaries.emp_no;
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+-------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref                        | rows   | Extra |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+-------+
|  1 | SIMPLE      | employees | ALL  | PRIMARY       | NULL    | NULL    | NULL                       | 299113 | NULL  |
|  1 | SIMPLE      | salaries  | ref  | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no |      1 | NULL  |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+-------+

Das EXPLAIN für die zweite Abfrage (eigentlich eine sinnvolle Abfrage zur Berechnung von AVG (), wie Sie in Ihrem Kommentar erwähnt haben) zeigt etwas Zusätzliches:

mysql> EXPLAIN SELECT employees.gender, AVG(salary) FROM employees 
  INNER JOIN salaries ON employees.emp_no = salaries.emp_no 
  GROUP BY employees.gender;
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref                        | rows   | Extra                           |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+---------------------------------+
|  1 | SIMPLE      | employees | ALL  | PRIMARY       | NULL    | NULL    | NULL                       | 299113 | Using temporary; Using filesort |
|  1 | SIMPLE      | salaries  | ref  | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no |      1 | NULL                            |
+----+-------------+-----------+------+---------------+---------+---------+----------------------------+--------+---------------------------------+

Sehen Sie das Using temporary; Using filesort im Feld Extra? Das bedeutet, dass die Abfrage eine temporäre Tabelle erstellen muss, um die AVG () - Ergebnisse pro Gruppe zu sammeln. Es muss eine temporäre Tabelle verwendet werden, da MySQL nicht wissen kann, dass alle Zeilen für jedes Geschlecht zusammen gescannt werden. Daher muss davon ausgegangen werden, dass die laufenden Summen unabhängig voneinander beim Scannen von Zeilen beibehalten werden müssen. Es scheint nicht so, als wäre es ein großes Problem, zwei (in diesem Fall) Geschlechtssummen zu erfassen, aber nehmen wir an, es wäre eine Postleitzahl oder so etwas?

Das Erstellen einer temporären Tabelle ist eine ziemlich teure Operation. Dies bedeutet, dass Daten geschrieben und nicht nur wie bei der ersten Abfrage gelesen werden.

Wenn wir einen Index erstellen könnten, der nach Geschlecht geordnet ist, würde der Optimierer von MySQL wissen, dass er alle Zeilen mit demselben Geschlecht zusammen scannen kann. So kann die laufende Summe von jeweils einem Geschlecht berechnet werden. Sobald das Scannen eines Geschlechts abgeschlossen ist, wird der AVG (Gehalt) berechnet und es wird garantiert, dass keine weiteren Zeilen für dieses Geschlecht gescannt werden. Daher kann das Erstellen einer temporären Tabelle übersprungen werden.

Dieser Index hilft:

mysql> alter table employees add index (gender, emp_no);

Das EXPLAIN derselben Abfrage zeigt nun, dass ein Index-Scan (type=index) durchgeführt wird, der dieselbe Anzahl von Einträgen besucht, aber in einer hilfreicheren Reihenfolge für die Berechnung des aggregierten AVG () gescannt wird.

Gleiche Abfrage, aber kein Using temporary Hinweis:

mysql> EXPLAIN SELECT employees.gender, AVG(salary) FROM employees 
  INNER JOIN salaries ON employees.emp_no = salaries.emp_no 
  GROUP BY employees.gender;
+----+-------------+-----------+-------+----------------+---------+---------+----------------------------+--------+-------------+
| id | select_type | table     | type  | possible_keys  | key     | key_len | ref                        | rows   | Extra       |
+----+-------------+-----------+-------+----------------+---------+---------+----------------------------+--------+-------------+
|  1 | SIMPLE      | employees | index | PRIMARY,gender | gender  | 5       | NULL                       | 299113 | Using index |
|  1 | SIMPLE      | salaries  | ref   | PRIMARY        | PRIMARY | 4       | employees.employees.emp_no |      1 | NULL        |
+----+-------------+-----------+-------+----------------+---------+---------+----------------------------+--------+-------------+

Die Ausführung dieser Abfrage ist viel schneller:

+--------+-------------+
| gender | AVG(salary) |
+--------+-------------+
| M      |  63838.1769 |
| F      |  63769.6032 |
+--------+-------------+
2 rows in set (1.06 sec)
2
Bill Karwin 20 Jän. 2019 im 16:46

Das Hinzufügen der GROUP BY-Klausel könnte leicht den großen Leistungsabfall erklären, den Sie sehen.

Aus der Dokumentation:

Die allgemeinste Möglichkeit, eine GROUP BY-Klausel zu erfüllen, besteht darin, die gesamte Tabelle zu scannen und eine neue temporäre Tabelle zu erstellen, in der alle Zeilen jeder Gruppe aufeinander folgen. Anschließend können Sie mit dieser temporären Tabelle Gruppen ermitteln und Aggregatfunktionen anwenden (falls vorhanden).

Die zusätzlichen Kosten, die durch den Gruppierungsprozess entstehen, können sehr teuer sein. Die Gruppierung erfolgt auch dann, wenn keine Aggregatfunktion verwendet wird.

Wenn Sie keine Aggregatfunktion benötigen, gruppieren Sie nicht. Stellen Sie in diesem Fall sicher, dass Sie über einen einzigen Index verfügen, der auf alle gruppierten Spalten verweist, wie in der Dokumentation vorgeschlagen:

In einigen Fällen kann MySQL viel besser als das und die Erstellung temporärer Tabellen mithilfe des Indexzugriffs vermeiden.

PS: Bitte beachten Sie, dass «SELECT * ... GROUP BY» -ähnliche Anweisungen seit MySQL 5.7.5 nicht mehr unterstützt werden (es sei denn, Sie deaktivieren die Option ONLY_FULL_GROUP_BY).

1
GMB 20 Jän. 2019 im 16:33

Es gibt noch einen anderen Grund sowie das, worauf GMB hinweist. Grundsätzlich sehen Sie sich wahrscheinlich das Timing der ersten Abfrage an, bis die erste Zeile zurückgegeben wird. Ich bezweifle, dass alle Zeilen in 0,015 Sekunden zurückgegeben werden.

Die zweite Abfrage mit GROUP BY muss alle Daten verarbeiten, um die Ergebnisse abzuleiten.

Wenn Sie der ersten Abfrage ein ORDER BY hinzufügen (für das alle Daten verarbeitet werden müssen), wird ein ähnlicher Leistungsabfall angezeigt.

0
Gordon Linoff 20 Jän. 2019 im 16:48