Detaillierte Erklärung der MySQL-Gruppierung durch Optimierung

Eine Standard-Group-by-Anweisung enthält Sortier-, Gruppierungs- und Aggregationsfunktionen, wie z. B. „select a,count(*) from t group by a“; Diese Anweisung verwendet standardmäßig „a“ zum Sortieren. Wenn Spalte a keinen Index hat, wird eine temporäre Tabelle erstellt, um a und count(*) zu zählen und dann nach a über sort_buffer sortiert.

Standardausführungsprozess

Struktur:

Erstellen Sie Tabelle t1 (ID int Primärschlüssel, a int, b int, Index(a));
Trennzeichen;;
Prozedur idata() erstellen
beginnen
 deklariere i int;

 setze i=1;
 während(i<=1000)mache
 in t1-Werte (i, i, i) einfügen;
 setze i=i+1;
 Ende während;
Ende;;
Trennzeichen ;
rufen Sie idata() auf;

Die Funktion besteht darin, 1000 Anweisungen in t1 einzufügen, von (1,1,1) bis (1000,1000,1000).

Führen Sie „select id%10 as m, count(*) as c from t1 group by m“ aus.

Analyse:

Die Verwendung von Index bedeutet, dass diese Anweisung einen überdeckenden Index verwendet und den Index a auswählt, sodass keine Notwendigkeit besteht, zur Tabelle zurückzukehren.
„Temporär verwenden“ bedeutet, eine temporäre Tabelle zu verwenden.
Die Verwendung von Filesort zeigt an, dass eine Sortierung erforderlich ist.

Verfahren:

1. Erstellen Sie eine temporäre Speichertabelle mit zwei Feldern m und c. Der Primärschlüssel ist m.
2. Scannen Sie den Index a der Tabelle t1, nehmen Sie die ID-Werte auf den Blattknoten nacheinander heraus und berechnen Sie das Ergebnis von ID%10, das als x aufgezeichnet wird.
1) Wenn in der temporären Tabelle keine Zeile mit dem Primärschlüssel x vorhanden ist, fügen Sie einen Datensatz (x,1) ein.
2) Wenn die Tabelle eine Zeile mit dem Primärschlüssel x enthält, addieren Sie 1 zum c-Wert der Zeile x.

Schritt 2: Wenn festgestellt wird, dass die Gesamtlänge der in der temporären Tabelle im Speicher gespeicherten Felder die durch den Parameter tmp_table_size festgelegte Größe erreicht, wird die temporäre Tabelle im Speicher in eine temporäre Tabelle auf der Festplatte aktualisiert und anschließend die Durchlaufberechnung neu gestartet.
3. Nachdem die Durchquerung abgeschlossen ist, sortieren Sie nach Feld m und geben Sie den Ergebnisset an den Client zurück.

Die endgültige Sortierung erfolgt durch den Vorgang im gestrichelten Feld in der Abbildung unten. Wenn die Größe des Sortierpuffers nicht ausreicht, wird eine temporäre Tabelle zur Unterstützung der Sortierung verwendet.

Optimierung

Der Gesamtprozess des nicht optimierten (d. h. die Gruppierungsspalten haben keinen Index) Gruppierens nach kann wie folgt zusammengefasst werden: Da die Daten ungeordnet sind, muss eine temporäre Tabelle erstellt werden. Anschließend wird für alle Daten einzeln beurteilt, zu welcher Gruppe sie gehören, und schließlich wird nach den Gruppierungsspalten sortiert. Daher gibt es zwei Ansätze zur Optimierung:

Sortierung entfernen

Wenn die zurückgegebenen Daten nicht sortiert werden müssen, können Sie das Sortieren verbieten, d. h. die obige Anweisung so ändern, dass „a,count(*)“ aus der t-Gruppe nach „order by null“ ausgewählt wird.

Sortieren nach

Wenn die Datensätze nach dem Sortierfeld sortiert werden, erhalten die Daten die folgende Struktur:

Auf diese Weise müssen Sie beim tatsächlichen Abrufen der zurückzugebenden Felder oder beim Berechnen der Aggregatfunktion nur der Reihe nach darauf zugreifen. Wenn der Spaltenwert der nächste wird, wissen Sie, dass der aktuelle Gruppenzugriff beendet ist und die zuvor gezählten Daten direkt zurückgegeben werden. Dadurch wird die Erstellung temporärer Tabellen vermieden und für die Sortierung ist kein zusätzliches Sortieren mit sort_buffer erforderlich. Dadurch wird die Effizienz der Ausführung erheblich verbessert.

erreichen

1. Wenn das Gruppierungsfeld für die Erstellung eines Index geeignet ist, erstellen Sie direkt einen Index für das Gruppierungsfeld.

MySQL Version 5.7 unterstützt den generierten Spaltenmechanismus, mit dem die zugehörige Aktualisierung von Spaltendaten implementiert wird. Sie können eine Spalte z wie folgt erstellen und dann einen Index für die Spalte z erstellen (bei MySQL 5.6 und früher können Sie zur Lösung dieses Problems auch eine normale Spalte und einen normalen Index erstellen).

Tabelle t1 ändern, Spalte z int hinzufügen, immer generiert als (ID % 100), Index (z) hinzufügen;

Dann analysieren Sie:

Da keine temporäre Tabelle oder zusätzliche Sortierung verwendet wird, ist die Leistung besser.

2. Wenn das Gruppierungsfeld nicht geeignet ist (die Nutzungsrate ist sehr niedrig), können Sie versuchen, es mit SQL_BIG_RESULT zu optimieren.

Indem Sie der Group-By-Anweisung den Hinweis SQL_BIG_RESULT hinzufügen, können Sie dem Optimierer mitteilen, dass diese Anweisung eine große Datenmenge umfasst und dass Sie direkt eine temporäre Datenträgertabelle verwenden sollten. Der MySQL-Optimierer erkennt, dass die temporäre Festplattentabelle in einem B+-Baum gespeichert ist und ihre Speichereffizienz nicht so hoch ist wie die eines Arrays. Da SQL_BIG_RESULT verwendet wird, um eine große Datenmenge anzuzeigen, ist es im Hinblick auf den Speicherplatz besser, sie direkt in einem Array zu speichern. Nach der Verwendung von SQL_BIG_RESULT verwendet der Optimierer eine temporäre Festplattentabelle mit Array-Struktur.

Wenn die Bedingungen für die Verwendung einer temporären Festplattentabelle jedoch nicht erfüllt sind, wird die temporäre Festplattentabelle nicht verwendet. Das heißt, wenn der Speicherplatz des Sort_Buffer die Gesamtlänge der zurückzugebenden und zu sortierenden Felder speichern kann, wird die Array-Struktur des Sort_Buffer verwendet. Wenn die Gesamtfeldgröße die Größe des Sort_Buffer überschreitet, wird eine temporäre Festplattentabelle der Array-Struktur hinzugefügt, um das Sortieren zu erleichtern.

Wenn im Sortierpuffer genügend Platz vorhanden ist, werden die Daten im Sortierpuffer sortiert, der als Index dient.

Wir verwenden immer noch das obige Beispiel und verwenden SQL_BIG_RESULT

Tabelle t1 ändern, Spalte z int hinzufügen, immer generiert als (ID % 100), Index (z) hinzufügen;

Der konkrete Ablauf ist wie folgt:

1. Initialisieren Sie sort_buffer und fügen Sie ein ganzzahliges Feld mit der Bezeichnung m ein.
2. Scannen Sie den Index a der Tabelle t1, nehmen Sie die darin enthaltenen ID-Werte nacheinander heraus und speichern Sie den Wert von ID%10 im Sortierpuffer.
3. Sortieren Sie nach Abschluss des Scans das Feld m des Sortierpuffers (wenn der Speicher des Sortierpuffers nicht ausreicht, werden temporäre Dateien auf der Festplatte zum Sortieren verwendet).
4. Nach Abschluss der Sortierung wird ein geordnetes Array erhalten.

Analyse:

Es ist ersichtlich, dass die temporäre Tabelle zu diesem Zeitpunkt nicht verwendet wird, sondern sort_buffer direkt zum Sortieren verwendet wird, wodurch der durch die Verwendung der temporären Tabelle verursachte Leistungsverbrauch eingespart wird.

Zusammenfassen

1. Wenn es nicht erforderlich ist, die Ergebnisse der Group-by-Anweisung zu sortieren, fügen Sie nach der Anweisung „order by null“ hinzu. Dann ist es im Allgemeinen nicht erforderlich, eine temporäre Tabelle zu verwenden (die beiden oben genannten Optimierungen werden beide unter der Voraussetzung vorgeschlagen, dass eine Sortierung erforderlich ist).
2. Versuchen Sie, den Gruppierungsprozess den Index der obigen Tabelle verwenden zu lassen. Die Bestätigungsmethode besteht darin, zu prüfen, ob im Erläuterungsergebnis weder „Using temporary“ noch „Using filesort“ enthalten sind.
3. Wenn die Datenmenge, die in „Group by“ gezählt werden muss, nicht groß ist, versuchen Sie, nur temporäre Tabellen im Speicher zu verwenden. Sie können die Verwendung temporärer Tabellen auf der Festplatte auch vermeiden, indem Sie den Parameter „tmp_table_size“ entsprechend erhöhen.
4. Wenn die Datenmenge zu groß ist, verwenden Sie den SQL_BIG_RESULT-Hinweis, um dem Optimierer mitzuteilen, dass er den Sortieralgorithmus direkt verwenden soll, um das Gruppierungsergebnis zu erhalten.

Oben finden Sie eine ausführliche Erklärung zur MySQL-Gruppierung durch Optimierung. Weitere Informationen zur MySQL-Gruppierung durch Optimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!