MySQL-Sortierprinzipien und Fallanalyse

Vorwort

Das Sortieren ist eine grundlegende Funktion von Datenbanken und MySQL bildet hier keine Ausnahme. Benutzer können den Zweck erreichen, den angegebenen Ergebnissatz durch die Anweisung Order by zu sortieren. Tatsächlich verwenden nicht nur die Anweisung Order by, sondern auch die Anweisung Group by und die Anweisung Distinct implizit die Sortierung. Dieser Artikel stellt zunächst kurz vor, wie SQL Indizes verwendet, um Sortierkosten zu vermeiden. Anschließend werden die internen Prinzipien der MySQL-Sortierung und die mit der Sortierung verbundenen Parameter vorgestellt. Abschließend werden mehrere „seltsame“ Sortierbeispiele gegeben, um das Problem der Sortierkonsistenz zu erörtern und die wesentlichen Gründe für das Phänomen zu erklären.

1. Sortieroptimierung und Indexnutzung

Um die Sortierleistung von SQL-Anweisungen zu optimieren, ist es am besten, das Sortieren zu vermeiden. Die richtige Verwendung von Indizes ist eine gute Methode. Da auch der Index selbst geordnet ist, kann der Sortiervorgang übersprungen werden, wenn für das zu sortierende Feld ein geeigneter Index erstellt wird. Dadurch wird die SQL-Abfragegeschwindigkeit verbessert. Im Folgenden werde ich anhand einiger typischer SQL-Anweisungen veranschaulichen, bei welchen SQL-Anweisungen die Verwendung von Indizes zur Reduzierung des Sortieraufwands möglich ist und bei welchen nicht. Angenommen, die Tabelle t1 hat die Indizes key1(key_part1,key_part2),key2(key2)

a. SQL, das Indizes verwenden kann, um das Sortieren zu vermeiden

AUSWÄHLEN * AUS t1 ORDER BY Schlüsselteil1, Schlüsselteil2;
SELECT * FROM t1 WHERE key_part1 = konstant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1 > konstant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE Schlüsselteil1 = Konstante1 AND Schlüsselteil2 > Konstante2 ORDER BY Schlüsselteil2;

b. SQL, das keine Indizes verwenden kann, um das Sortieren zu vermeiden

//Das Sortierfeld befindet sich in mehreren Indizes, daher kann keine Indexsortierung verwendet werden. SELECT * FROM t1 ORDER BY key_part1, key_part2, key2;
 
//Die Sortierschlüsselreihenfolge stimmt nicht mit der Spaltenreihenfolge im Index überein, deshalb kann der Index nicht zum Sortieren verwendet werden. SELECT * FROM t1 ORDER BY key_part2, key_part1;
 
//Die aufsteigende und absteigende Reihenfolge ist inkonsistent und die Indexsortierung kann nicht verwendet werden. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
 
//key_part1 ist eine Bereichsabfrage, key_part2 kann nicht mithilfe des Index sortiert werden SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;

2. Sortieralgorithmus

Bei SQL, das keine Indizes verwenden kann, um das Sortieren zu vermeiden, muss die Datenbank die Sortierfunktion selbst implementieren, um den Benutzeranforderungen gerecht zu werden. Zu diesem Zeitpunkt wird im SQL-Ausführungsplan „Dateisortierung verwenden“ angezeigt. Hierbei ist zu beachten, dass Dateisortierung nicht Dateisortierung bedeutet. Tatsächlich kann es sich auch um Speichersortierung handeln, die hauptsächlich durch den Parameter sort_buffer_size und die Größe des Ergebnissatzes bestimmt wird. Es gibt drei Hauptmethoden zum Implementieren der Sortierung in MySQL: herkömmliche Sortierung, optimierte Sortierung und Prioritätswarteschlangensortierung, die hauptsächlich drei Sortieralgorithmen umfassen: Quicksort, Mergesort und Heapsort. Angenommen, die Tabellenstruktur und die SQL-Anweisung lauten wie folgt:

TABELLE ERSTELLEN t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
Wählen Sie Spalte1, Spalte2, Spalte3 aus t1, wobei Spalte1>100 ist. Bestellen Sie nach Spalte2.

a. Konventionelle Sortierung

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

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

(3) Wenn der Sortierpuffer alle (id, col2)-Paare speichern kann, die die Bedingungen erfüllen, werden sie sortiert. Andernfalls werden sie sortiert und in einer temporären Datei gespeichert, wenn der Sortierpuffer voll ist. (Der verwendete Sortieralgorithmus ist der Quicksort-Algorithmus)

(4) Wenn beim Sortieren eine temporäre Datei erstellt wird, muss ein Mergesort-Algorithmus verwendet werden, um sicherzustellen, dass die Datensätze in der temporären Datei in der richtigen Reihenfolge sind.

(5) Wiederholen Sie den obigen Prozess, bis alle Datensätze, die die Kriterien erfüllen, sortiert sind.

(6) Scannen Sie die sortierten (id, col2)-Paare und verwenden Sie die ID, um die Spalten (col1, col2, col3) abzurufen, die SELECT zurückgeben muss.

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

Ob die Dateisortierung verwendet werden soll, hängt im obigen Prozess hauptsächlich davon ab, ob der Sortierpuffer die zu sortierenden (ID, col2)-Paare aufnehmen kann. Die Größe dieses Puffers wird durch den Parameter sort_buffer_size gesteuert. Darüber hinaus erfordert eine Sortierung zwei IOs, eines zum Abrufen von (ID, Spalte 2) und das zweite zum Abrufen von (Spalte 1, Spalte 2, Spalte 3). Da der zurückgegebene Ergebnissatz nach Spalte 2 sortiert ist, sind die IDs ungeordnet. Beim Abrufen von (Spalte 1, Spalte 2, Spalte 3) über ungeordnete IDs wird eine große Menge zufälliger IOs generiert. Zum zweiten Mal verfügt MySQL selbst über eine Optimierung, d. h. vor dem Abrufen werden die IDs zunächst sortiert und in einen Puffer gelegt. Die Größe dieses Puffers wird durch den Parameter read_rnd_buffer_size gesteuert, und dann werden die Datensätze der Reihe nach abgerufen, wodurch zufällige IO in sequentielle IO umgewandelt wird.

b. Sortierung optimieren

Zusätzlich zum Sortieren selbst erfordert die herkömmliche Sortiermethode zwei zusätzliche IO-Operationen. Im Vergleich zur herkömmlichen Sortierung reduziert die optimierte Sortiermethode den zweiten IO. Der Hauptunterschied besteht darin, dass in den Sortierpuffer nicht (id,col2), sondern (col1,col2,col3) eingefügt wird. Da der Sortierpuffer alle für die Abfrage benötigten Felder enthält, kann dieser nach Abschluss der Sortierung direkt zurückgegeben werden, ohne dass die Daten erneut abgerufen werden müssen. Der Nachteil dieses Ansatzes besteht darin, dass der Sortierpuffer gleicher Größe weniger (col1, col2, col3) speichern kann als (id, col2). Wenn der Sortierpuffer nicht groß genug ist, müssen möglicherweise temporäre Dateien geschrieben werden, was zu zusätzlichen IOs führt. Natürlich bietet MySQL den Parameter max_length_for_sort_data. Nur wenn das Sortiertupel kleiner als max_length_for_sort_data ist, kann die optimierte Sortiermethode verwendet werden, andernfalls kann nur die herkömmliche Sortiermethode verwendet werden.

c. Sortierung nach Prioritätswarteschlangen

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ückgeben. Gibt es also im Vergleich zur Optimierung der Sortiermethode noch Raum für Optimierungen? Version 5.6 optimiert die Order by Limit M, N-Anweisung auf Speicherebene und fügt eine neue Sortiermethode hinzu – Prioritätswarteschlange, die mithilfe von Heapsort implementiert wird. Die Eigenschaften des Heapsort-Algorithmus können das Sortierproblem der Grenze M, N lösen. Obwohl alle Elemente weiterhin an der Sortierung teilnehmen müssen, wird nur der Sortierpufferspeicherplatz von M+N Tupeln benötigt. In Szenarien mit sehr kleinen M- und N-Werten besteht grundsätzlich kein Problem, dass aufgrund unzureichender Sortierpuffer temporäre Dateien für die Zusammenführungssortierung benötigt werden. Für die aufsteigende Sortierung wird ein Max-Heap verwendet und die Elemente im letzten Heap stellen die kleinsten N Elemente dar. Für die absteigende Sortierung wird ein Min-Heap verwendet und die Elemente im letzten Heap stellen die größten N Elemente dar.

3. Inkonsistentes Sortierproblem

Fall 1

Nach der Migration von Mysql von 5.5 auf 5.6 wurden beim Paging doppelte Werte gefunden.

Testtabelle und Daten:

Tabelle erstellen t1(id int Primärschlüssel, c1 int, c2 varchar(128));
in t1-Werte (1,1, „a“) ​​einfügen;
in t1-Werte (2,2, 'b') einfügen;
in t1-Werte (3,2, „c“) einfügen;
in t1-Werte einfügen (4,2,'d');
in t1-Werte einfügen (5,3, 'e');
in t1-Werte einfügen (6,4, „f“);
in t1-Werte einfügen (7,5, „g“);

Angenommen, es gibt 3 Datensätze pro Seite, dann beträgt das erste Seitenlimit 0,3 und das zweite Seitenlimit 3,3. Die Abfrageergebnisse lauten wie folgt:

Wir können sehen, dass der Datensatz mit der ID 4 in beiden Abfragen erscheint, was offensichtlich nicht den Erwartungen entspricht und in Version 5.5 kein solches Problem besteht. Der Grund für dieses Phänomen ist, dass 5.6 eine Prioritätswarteschlange für Limit M, N-Anweisungen verwendet und die Prioritätswarteschlange mithilfe eines Heaps implementiert wird. Im obigen Beispiel erfordert beispielsweise order by c1 asc limit 0, 3 einen Max-Heap der Größe 3; limit 3, 3 erfordert einen Max-Heap der Größe 6. Da es 3 Datensätze gibt, wobei c1 2 ist, und die Heapsortierung instabil ist (für denselben Schlüsselwert gibt es keine Garantie, dass die Position nach der Sortierung mit der vor der Sortierung übereinstimmt), kommt es zu einer Duplizierung der Seitenanzahl. Um dieses Problem zu vermeiden, können wir der Sortierung einen eindeutigen Wert hinzufügen, beispielsweise die Primärschlüssel-ID. Auf diese Weise können wir sicherstellen, dass die an der Sortierung beteiligten Schlüsselwerte unterschiedlich sind, da die ID eindeutig ist. Schreiben Sie das SQL wie folgt:

Wähle * aus t1, sortiere nach c1, ID, aufsteigendes Limit 0,3;
Wähle * aus t1, sortiere nach c1, ID, aufsteigendes Limit 3,3;

Fall 2

Zwei ähnliche Abfrageanweisungen sind bis auf die zurückgegebenen Spalten identisch, die Sortierergebnisse sind jedoch inkonsistent.

Testtabelle und Daten:

Tabelle erstellen t2(ID int Primärschlüssel, Status int, c1 varchar(255), c2 varchar(255), c3 varchar(255), Schlüssel(c1));
in t2-Werte einfügen (7,1, „a“, Wiederholung („a“, 255), Wiederholung („a“, 255));
in t2 einfügen: Werte (6,2, „b“, Wiederholung („a“, 255), Wiederholung („a“, 255));
in t2-Werte einfügen (5,2, „c“, Wiederholung („a“, 255), Wiederholung („a“, 255));
in t2-Werte einfügen (4,2, „a“, Wiederholung („a“, 255), Wiederholung („a“, 255));
in t2 einfügen: Werte (3,3, „b“, Wiederholung („a“, 255), Wiederholung („a“, 255));
in t2 einfügen: Werte (2,4, „c“, Wiederholung („a“, 255), Wiederholung („a“, 255));
in t2 einfügen: Werte (1,5, ‚a‘, Wiederholung (‚a‘, 255), Wiederholung (‚a‘, 255));

Führen Sie die SQL-Anweisungen separat aus:

Wählen Sie ID, Status, c1, c2 aus t2, erzwingen Sie Index (c1), wobei c1>='b' nach Status sortiert ist;
Wählen Sie ID, Status aus t2, erzwingen Sie Index (c1), wobei c1>='b' nach Status sortiert ist;

Die Ausführungsergebnisse sind wie folgt:

Überprüfen Sie, ob die Ausführungspläne der beiden gleich sind

Um das Problem zu veranschaulichen, habe ich der Anweisung den Hinweis „Index erzwingen“ hinzugefügt, um sicherzustellen, dass der Spaltenindex c1 verwendet werden kann. Die Anweisung ruft die ID über den Spaltenindex c1 ab und ruft dann die zurückgegebenen Spalten aus der Tabelle ab. Entsprechend der Größe des Spaltenwerts c1 ist die relative Position des Datensatzes im Index c1 wie folgt:

(c1,id)===(b,6),(b,3),(5,c),(c,2), die entsprechenden Statuswerte sind jeweils 2 3 2 4. Wenn wir Daten aus der Tabelle abrufen und sie nach Status sortieren, werden die relativen Positionen (6,2,b),(5,2,c),(3,3,c),(2,4,c). Dies ist das von der zweiten Abfrageanweisung zurückgegebene Ergebnis. Warum sind die ersten Abfrageanweisungen (6,2,b),(5,2,c) also in umgekehrter Reihenfolge? Hier sehen Sie die roten Teile in a. Konventionelle Sortierung und b. Optimierte Sortierung, die ich zuvor erwähnt habe, und Sie werden den Grund verstehen. Da die Anzahl der Bytes der von der ersten Abfrage zurückgegebenen Spalte max_length_for_sort_data überschreitet, wird eine herkömmliche Sortierung verwendet. In diesem Fall sortiert MySQL die Zeilen-ID und konvertiert zufällige IO in sequenzielle IO, sodass 5 zuerst und 6 zuletzt zurückgegeben wird. Die zweite Abfrage verwendet eine optimierte Sortierung ohne den zweiten Datenabrufprozess, und die relative Position der sortierten Datensätze wird beibehalten. Wenn wir für die erste Anweisung eine optimierte Sortierung verwenden möchten, können wir die Einstellung „max_length_for_sort_data“ erhöhen, beispielsweise auf 2048.

4. Referenzdokumente

• http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html
• http://mysql.taobao.org/monthly/2015/06/04/
• http://ifxoxo.com/mysql_order_by.html

Dies ist das Ende dieses Artikels über MySQL-Sortierprinzipien und Fallanalyse. Weitere relevante MySQL-Sortierprinzipien und Fallinhalte finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!