So optimieren Sie MySQL-Gruppen nach Anweisungen

Erstellen Sie in MySQL eine neue Tabelle mit drei Feldern: id, a und b. Fügen Sie 1.000 Datensätze mit denselben Feldern wie folgt ein:

mysql> anzeigen, Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
    Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
 `id` int(11) NICHT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `a` (`a`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)
mysql> wähle * ab t1-Limit 10;
+----+------+------+
| Ich würde | a | b |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |
| 8 | 8 | 8 |
| 9 | 9 | 9 |
| 10 | 10 | 10 |
+----+------+------+
10 Zeilen im Satz (0,00 Sek.)

Wenn wir das folgende SQL mit „group by“ ausführen, können wir den Ausführungsplan sehen:

mysql> erklären Sie „select id%10 as m, count(*) as c from t1 group by m limit 10“;
+----+----------+-------+---------+-----------+---------------+------+---------+---------+------+---------+------+---------+----------+----------+----------+--------------------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+---------+-----------+---------------+------+---------+---------+------+---------+------+---------+----------+----------+----------+--------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | index | PRIMARY,a | a | 5 | NULL | 1000 | 100.00 | Index wird verwendet; Temporär wird verwendet; Filesort wird verwendet |
+----+----------+-------+---------+-----------+---------------+------+---------+---------+------+---------+------+---------+----------+----------+----------+--------------------------------------------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Am Ende stehen:

• Index verwenden: Überdeckender Index
• using temporary: Verwenden einer temporären Speichertabelle
• using filesort: Sortiervorgang wird verwendet

Um den Ausführungsprozess dieser Gruppierung nach Anweisungen besser zu verstehen, zeichne ich ein Bild zur Darstellung:

Durch einen Vergleich mit der obigen Tabelle können wir leicht feststellen, dass der Ausführungsprozess dieser Gruppierungsanweisung wie folgt abläuft:

a. Erstellen Sie zunächst eine temporäre Speichertabelle mit zwei Feldern m und c. Der Primärschlüssel ist m; m ist id%10 und c ist die Anzahl der Zählungen (*)

b. Scannen Sie den Index a der Tabelle t1, entnehmen Sie nacheinander die ID-Werte auf den Blattknoten, berechnen Sie das Ergebnis von id%10 und zeichnen Sie es als x auf. Wenn in der temporären Tabelle keine Zeile mit dem Primärschlüssel x vorhanden ist, fügen Sie einen Datensatz (x, 1) ein. Wenn in der Tabelle eine Zeile mit dem Primärschlüssel x vorhanden ist, addieren Sie 1 zum c-Wert der Zeile x.

c. Nach Abschluss der Durchquerung wird die Sortierung nach Feld m durchgeführt und der Ergebnissatz an den Client zurückgegeben. (Beachten Sie, dass diese Sortieraktion automatisch von „Group By“ hinzugefügt wird.)

Wenn wir nicht möchten, dass die „group by“-Anweisung automatisch für uns sortiert, können wir am Ende der Anweisung „order by null“ hinzufügen, sodass wir den Sortiervorgang nach „order by“ entfernen können. wie folgt:

mysql> erklären Sie „select id%10 as m, count(*) as c from t1 group by m order by null;“
+----+----------+-------+---------+-----------+---------------+------+---------+---------+------+---------+---------+----------+----------+--------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+---------+-----------+---------------+------+---------+---------+------+---------+---------+----------+----------+--------------+
| 1 | SIMPLE | t1 | NULL | index | PRIMARY,a | a | 5 | NULL | 1000 | 100.00 | Index wird verwendet; Temporär wird verwendet |
+----+----------+-------+---------+-----------+---------------+------+---------+---------+------+---------+---------+----------+----------+--------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wie Sie sehen, sind die Wörter „using filesort“ am Ende von Explain verschwunden. Schauen wir uns die Ergebnisse noch einmal an:

mysql> wähle id%10 als m, count(*) als c aus t1, gruppiere nach m;
+------+-----+
| m | c |
+------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+------+-----+
10 Zeilen im Satz (0,00 Sek.)
mysql> wähle id%10 als m, count(*) als c aus t1, gruppiere nach m, sortiere nach null;
+------+-----+
| m | c |
+------+-----+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 0 | 100 |
+------+-----+
10 Zeilen im Satz (0,00 Sek.)

Wenn wir order by null nicht hinzufügen, sortiert group by automatisch für uns, sodass der Datensatz mit m=0 an erster Stelle steht. Wenn wir order by null hinzufügen, sortiert group by nicht automatisch und der Datensatz mit m=0 steht am Ende.

In unserer aktuellen Anweisung gibt es insgesamt 1000 Datensätze in Tabelle t1. Modulo 10 gibt es nur 10 Ergebnisse, die in der temporären Speichertabelle abgelegt werden können. Die temporäre Speichertabelle wird in MySQL durch tmp_table_size gesteuert.

mysql> Variablen wie "%tmp_table%" anzeigen;
+-----------------+----------+
| Variablenname | Wert |
+-----------------+----------+
| max_tmp_tabellen | 32 |
| tmp_tabellengröße | 39845888 |
+-----------------+----------+
2 Zeilen im Satz, 1 Warnung (0,00 Sek.)

Wenn unsere Ergebnisse groß genug sind und die temporäre Tabelle im Speicher nicht ausreicht, um sie zu speichern, verwendet MySQL die temporäre Tabelle auf der Festplatte und die gesamte Zugriffsgeschwindigkeit wird sehr langsam. Wie optimieren wir also die Gruppierungsoperation?

01

Gruppieren nach optimiertem Index

Aus der obigen Beschreibung lässt sich leicht erkennen, dass bei der Gruppierung mit „group by“ alle erstellten temporären Tabellen einen eindeutigen Index aufweisen. Wenn die Datenmenge groß ist, ist die Ausführungsgeschwindigkeit von group by sehr langsam. Um diese Situation zu optimieren, müssen wir analysieren, warum group by eine temporäre Tabelle erfordert.

Dieses Problem liegt eigentlich daran, dass die Logik von group by darin besteht, die Häufigkeit des Auftretens unterschiedlicher Werte zu zählen. Da die Ergebnisse jeder Datensatzzeile nach group by ungeordnet sind, wird eine temporäre Tabelle zum Speichern dieser Zwischenergebnissätze benötigt. Was wäre, wenn alle unsere Werte geordnet und in Ordnung wären?

Beispielsweise haben wir eine Tabelle, deren Spalte „Datensatz-ID“ lautet:

0,0,0,1,1,2,2,2,2,3,4,4,

Wenn wir „Gruppieren nach“ verwenden, sammeln wir einfach die gleichen Werte von links nach rechts. Auf diese Weise ist keine temporäre Tabelle erforderlich.

Wir sind alle mit der obigen Struktur vertraut. Wenn wir einen Index für eine Datenspalte erstellen, wird die Spalte selbst sortiert. Wenn „group by“ auf dieser Spalte basiert, ist in diesem Prozess keine Sortierung erforderlich, da der Index automatisch sortiert ist. Um diese Optimierung zu erreichen, fügen wir der Tabelle t1 wie folgt eine neue Spalte z hinzu:

mysql> alter table t1 add column z int, immer generiert als (id % 10), add index(z);
Abfrage OK, 0 Zeilen betroffen (0,02 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql> wähle z als m, count(*) als c aus t1, Gruppe nach z;
+------+-----+
| m | c |
+------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+------+-----+
10 Zeilen im Satz (0,00 Sek.)

mysql> erklären Sie „Select z als m, count(*) als c aus t1, Gruppierung nach z“;
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+---------+----------+----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+---------+----------+----------+
| 1 | SIMPLE | t1 | NULL | Index | z | z | 5 | NULL | 1000 | 100,00 | Index wird verwendet |
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+---------+----------+----------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wir fügen ein neues Feld z hinzu, dessen Wert der Wert nach id%10 ist, und erstellen einen Index. Dann gruppieren wir die Spalte z über group by. Wir können sehen, dass im Ergebnis keine temporäre Tabelle vorhanden ist.

Daher kann uns die Verwendung von Indizes dabei helfen, die temporären Tabellen zu entfernen, von denen die Gruppierung abhängt.

02

Gruppieren nach Optimierung --- direkte Sortierung

Wenn wir bereits wissen, dass die Datenmenge in der Tabelle sehr groß ist und der temporäre Speicher der Tabelle definitiv nicht ausreicht, um die Sortierung durchzuführen, können wir den Sortiervorgang der temporären Speichertabelle tatsächlich überspringen, indem wir „group by“ anweisen, eine Datenträgersortierung durchzuführen.

Tatsächlich gibt es in MySQL eine solche Methode: Durch Hinzufügen des SQL_BIG_RESULT-Hinweises in der Group-By-Anweisung kann der Optimierer Folgendes erfahren: Diese Anweisung betrifft eine große Datenmenge. Verwenden Sie daher bitte direkt die temporäre Tabelle auf der Festplatte. Wenn wir diese Anweisung verwenden, nutzt MySQL automatisch die Array-Methode zum Organisieren der Felder in der temporären Festplattentabelle anstelle des uns allen bekannten B+-Baums. Zu diesem Wissenspunkt finden Sie hier eine Einführung in das offizielle Dokument:

SQL_BIG_RESULT oder SQL_SMALL_RESULT können mit GROUP BY oder DISTINCT verwendet werden, um dem Optimierer mitzuteilen, dass der Ergebnissatz viele Zeilen enthält bzw. klein ist. Für SQL_BIG_RESULT verwendet MySQL direkt festplattenbasierte temporäre Tabellen, wenn diese erstellt werden, und bevorzugt das Sortieren gegenüber der Verwendung einer temporären Tabelle mit einem Schlüssel für die GROUP BY-Elemente. Für SQL_SMALL_RESULT verwendet MySQL temporäre Tabellen im Arbeitsspeicher, um die resultierende Tabelle zu speichern, anstatt zu sortieren. Dies sollte normalerweise nicht erforderlich sein.

Die gesamte Gruppierung nach Verarbeitungsprozess wird zu:

a. Initialisieren Sie sort_buffer und fügen Sie ein Integer-Feld mit der Bezeichnung m ein.

b. Scannen Sie den Index a der Tabelle t1, nehmen Sie die darin enthaltenen ID-Werte nacheinander heraus und speichern Sie den Wert von id%100 im sort_buffer.

c. Nachdem der Scan abgeschlossen ist, sortieren Sie das Feld m von sort_buffer (wenn der Speicher von sort_buffer nicht ausreicht, werden temporäre Dateien auf der Festplatte zum Sortieren verwendet).

d. Nach Abschluss der Sortierung wird ein geordnetes Array erhalten. Wie 0,0,0,1,1,2,2,3,3,3,4,4,4,4

e. Erhalten Sie basierend auf dem geordneten Array die verschiedenen Werte im Array und die Anzahl der Vorkommen jedes Wertes.

Im gestrigen Artikel haben wir die Verwendung von temporären Tabellen in Union-Anweisungen analysiert. Heute haben wir die Verwendung von temporären Tabellen in Group-By-Anweisungen analysiert. Wann also verwendet MySQL temporäre Tabellen?

Wann verwendet MySQL interne temporäre Tabellen?

1. Wenn der Anweisungsausführungsprozess Daten lesen und Ergebnisse direkt abrufen kann, ist kein zusätzlicher Speicher erforderlich. Andernfalls ist zusätzlicher Speicher erforderlich, um die Zwischenergebnisse zu speichern.

2. Wenn die Ausführungslogik die Verwendung zweidimensionaler Tabellenfunktionen erfordert, wird temporären Tabellen Vorrang eingeräumt. Beispielsweise erfordert UNION eine eindeutige Indexeinschränkung und GROUP BY erfordert ein weiteres Feld zum Speichern der kumulativen Anzahl.

Oben finden Sie Einzelheiten zur Optimierung von MySQL-Group-by-Anweisungen. Weitere Informationen zur MySQL-Group-by-Optimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!