MySQL-Sortierungsunterschied

Lassen Sie uns im Detail über den MySQL-Sortiermodus von flacher nach tiefer sprechen, wie er sich auf die Auswahl verschiedener Sortiermodi durch MySQL auswirkt und wie die Sortierung optimiert werden kann.

Empfohlener Kurs: MySQL-Tutorial.

Sortieren ist eine Grundfunktion in Datenbanken, und MySQL bildet da keine Ausnahme.

Benutzer können die angegebene Ergebnismenge über die Order by-Anweisung sortieren. Tatsächlich verwenden nicht nur die Order by-Anweisung, sondern auch die Group by-Anweisung und die Distinct-Anweisung implizit die Sortierung. In diesem Artikel wird zunächst kurz vorgestellt, wie SQL Indizes verwendet, um Sortierkosten zu vermeiden, und anschließend werden die internen Prinzipien von MySQL zur Implementierung der Sortierung vorgestellt.

Lösen Sie die folgenden Fragen:

Wo verwendet MySQL die Sortierung und wie kann man beurteilen, ob MySQL die Sortierung verwendet?

MySQL verfügt über mehrere Sortiermodi und wie werden diese verwendet? MySQL wählt verschiedene Sortiermodi;

Welche Beziehung besteht zwischen MySQL-Sortierung und read_rnd_buffer_size und unter welchen Umständen kann eine Erhöhung von read_rnd_buffer_size die Sortierung optimieren?

Wie kann festgestellt werden, dass MySQL die Festplatte zum Sortieren verwendet? um es zu vermeiden oder zu optimieren.

Wie Felddaten variabler Länge (varchar) während der Sortierung im Speicher gespeichert werden und welche Verbesserungen es in 5.7 gibt.

Welche Verbesserungen gibt es in diesem Fall? Gibt es im Sortiermodus?

Welches Problem zeigt der Statuswert an, wenn er zu groß ist?

Wenn MySQL Sortierung verwendet? kann es analysiert und optimiert werden, um die Sortierung zu beschleunigen?

2. Sortierung

Wenn wir den MySQL-Ausführungsplan durch „explain“ betrachten, wird in der Spalte „Extra“ oft „Verwenden von Dateisortierung“ angezeigt.

Für SQL, das keine Indizes verwenden kann, um die Sortierung zu vermeiden, muss die Datenbank die Sortierfunktion selbst implementieren, um den Benutzeranforderungen gerecht zu werden. Zu diesem Zeitpunkt wird im SQL-Ausführungsplan „Verwenden von Dateisortierung“ angezeigt Hier bedeutet Dateisortierung nicht, dass es sich um eine Dateisortierung handelt. Dies wird hauptsächlich durch den Parameter sort_buffer_size und die Ergebnismengengröße bestimmt.

Tatsächlich zeigt diese Situation, dass MySQL Sortierung verwendet. Die Verwendung von Dateisortierung erfolgt häufig in der Reihenfolge „Reihenfolge nach“, „Gruppieren nach“, „Unterscheiden“, „Verbinden“ usw.

Es gibt drei Hauptmethoden, um die Sortierung intern in MySQL zu implementieren: reguläre Sortierung, optimierte Sortierung und Prioritätswarteschlangensortierung.

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

Bitte beachten Sie den Unterschied zwischen diesen drei Sortierungen:

Konventionelle Sortierung

(1) .Holen Sie sich Datensätze, die die WHERE-Bedingung erfüllen, aus Tabelle t1

(2). Nehmen Sie für jeden Datensatz den Primärschlüssel + Sortierschlüssel (id, col2) des Datensatzes und legen Sie ihn in den Sortierpuffer

( 3). Wenn der Sortierpuffer alle (id, col2)-Paare speichern kann, die die Bedingungen erfüllen, sortieren Sie sie andernfalls, wenn der Sortierpuffer voll ist, und verfestigen Sie sie in einer temporären Datei. (Der Sortieralgorithmus verwendet den Schnellsortierungsalgorithmus)

(4). Wenn beim Sortieren temporäre Dateien generiert werden, müssen Sie den Zusammenführungssortierungsalgorithmus verwenden, um sicherzustellen, dass die Datensätze in den temporären Dateien in Ordnung sind

( 5). Führen Sie den obigen Vorgang in einer Schleife aus, bis alle Datensätze, die die Bedingungen erfüllen, an der Sortierung beteiligt sind

(6) und verwenden Sie die ID um die Spalten abzurufen, die von SELECT (col1,col2,col3) zurückgegeben werden müssen

(7) Geben Sie den erhaltenen Ergebnissatz an den Benutzer zurück.

Aus dem obigen Prozess hängt die Verwendung der Dateisortierung hauptsächlich davon ab, ob der Sortierpuffer das zu sortierende Paar (id, col2) aufnehmen kann. Die Größe dieses Puffers wird durch den Parameter sort_buffer_size gesteuert. Darüber hinaus sind für eine Sortierung zwei E/As erforderlich, eine zum Abrufen von (id, col2) und die zweite zum Abrufen von (col1, col2, col3). Da die zurückgegebene Ergebnismenge nach col2 sortiert ist, sind die IDs nicht in der richtigen Reihenfolge. Beim Angeln nach (col1, col2, col3) mit der ID wird eine große Menge zufälliger E/A generiert. Eine Optimierung für MySQL selbst besteht zum zweiten Mal darin, die IDs vor dem Abrufen zu sortieren und in den Puffer zu legen. Die Größe dieses Puffers wird durch den Parameter read_rnd_buffer_size gesteuert, und dann werden die Datensätze in geordneter Weise abgerufen und zufällige E/A-Werte in umgewandelt sequentielles IO.

b. Optimierte Sortierung

Zusätzlich zur Sortierung selbst erfordert die herkömmliche Sortiermethode zwei zusätzliche IOs. Im Vergleich zur herkömmlichen Sortierung reduziert die optimierte Sortiermethode den zweiten IO. Der Hauptunterschied besteht darin, dass der Sortierpuffer nicht (id, col2), sondern (col1, col2, col3) ist. Da der Sortierpuffer alle für die Abfrage erforderlichen Felder enthält, kann er nach Abschluss der Sortierung direkt zurückgegeben werden, ohne dass die Daten erneut abgerufen werden müssen. Der Preis dieser Methode besteht darin, dass die Anzahl der (col1, col2, col3), die in einem Sortierpuffer derselben Größe gespeichert werden können, kleiner ist als (id, col2). Wenn der Sortierpuffer nicht groß genug ist, können temporäre Dateien gespeichert werden geschrieben werden müssen, was zusätzliche E/A verursacht. Natürlich stellt MySQL den Parameter max_length_for_sort_data bereit. Nur wenn das sortierte Tupel kleiner als max_length_for_sort_data ist, kann die optimierte Sortiermethode verwendet werden.

c. Sortierung der Prioritätswarteschlange

Um das endgültige Sortierergebnis zu erhalten, müssen wir in jedem Fall alle Datensätze sortieren, die die Bedingungen erfüllen, bevor wir sie zurücksenden. Gibt es also im Vergleich zur Optimierung der Sortiermethode noch Optimierungspotenzial? Version 5.6 hat die Order by limit M, N-Anweisung auf räumlicher Ebene optimiert und eine neue Sortiermethode hinzugefügt – die Prioritätswarteschlange, die mithilfe der Heap-Sortierung implementiert wird. Die Eigenschaften des Heap-Sortieralgorithmus können das Sortierproblem der Grenze M, N lösen. Obwohl alle Elemente weiterhin an der Sortierung teilnehmen müssen, wird nur der Sortierpufferraum von M + N Tupeln benötigt. Für Szenarien, in denen M und N vorhanden sind klein. Grundsätzlich besteht kein Problem, dass temporäre Dateien für die Zusammenführungssortierung aufgrund unzureichenden Sortierpuffers erforderlich sind. Für die aufsteigende Reihenfolge wird ein großer oberer Heap verwendet, und die Elemente im endgültigen Heap bilden die kleinsten N Elemente. Für die absteigende Reihenfolge wird ein kleiner oberer Heap verwendet, und die Elemente im endgültigen Heap bilden die größten N Elemente.

Das obige ist der detaillierte Inhalt vonMySQL-Sortierungsunterschied. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!