MySQL-Datenbank-Indexreihenfolge durch Sortierung – detaillierte Erklärung

Wenn ich an das Wort „Sortieren“ denke, ist mein erster Eindruck, dass fast alle Apps eine Sortierfunktion haben. Taobao-Produkte werden nach Kaufzeitpunkt sortiert und Bilibili-Kommentare werden nach Popularität sortiert …

Was fällt Ihnen als Erstes zum Sortieren in MySQL ein? Stichwort sortieren nach? Ist es am besten, einen Index für das Order-by-Feld zu haben? Sind die Blattknoten schon geordnet? Oder sollten wir das Sortieren innerhalb von MySQL so weit wie möglich vermeiden?

Die Ursache des Vorfalls

Nehmen wir nun an, es gibt eine Freundestabelle eines Benutzers:

CREATE TABLE `Benutzer` (
  `id` int(10) AUTO_INCREMENT,
  `Benutzer-ID` int(10),
  `Freundadresse` varchar(1000),
  `Freundname` varchar(100),  
  Primärschlüssel (`id`),
  SCHLÜSSEL `user_id` (`user_id`)
)ENGINE=InnoDB;

Derzeit gibt es in der Tabelle zwei Punkte, die Aufmerksamkeit erfordern:

• Die Benutzer-ID des Benutzers, der Name des Freundes (friend_name), die Adresse des Freundes (friend_addr)
• user_id ist indiziert

Eines Tages erhielt ein Junior-Entwicklungsingenieur namens Xiaoyuan eine Anfrage von einem Junior-Produktmanager namens Xiaowang:
Xiao Wang: Genosse Xiaoyuan, wir müssen im Hintergrund eine Funktion hinzufügen. Diese Funktion sollte die Abfrage aller Namen und Adressen der Freunde anhand der Benutzer-ID unterstützen und erfordern, dass die Namen der Freunde gemäß dem Wörterbuch sortiert werden.
Xiaoyuan: Okay, diese Funktion ist einfach, ich gehe sofort online.

Also hat Xiaoyuan das folgende SQL geschrieben:

wähle friend_name，friend_addr vom Benutzer, wobei user_id=? nach Namen sortieren

Im Handumdrehen ging Xiaoyuan mit großem Tamtam online. Alles lief gut, bis eines Tages ein Kommilitone aus dem Fach Operations Research folgende Frage stellte:

Wählen Sie Friend_Name ， Friend_Addr vom Benutzer aus, wobei User_ID = 10086 nach Namen sortiert ist

Diese Abfrage war jedoch viel langsamer als üblich und die Datenbank meldete eine langsame Abfrage. Xiaoyuan geriet in Panik: Was ist los? Es gibt einen Index für die Benutzer-ID und ich habe geschickterweise nur „Select Friend_Name, Friend_Addr“ anstelle von „Select *“ verwendet. Zu diesem Zeitpunkt beruhigte sich Xiaoyuan immer wieder selbst und sagte sich, er solle ruhig bleiben, bis ihm plötzlich einfiel, dass es einen „explain“-Befehl gab. Er beschloss, „explain“ zu verwenden, um den Ausführungsplan dieses SQL zu überprüfen. Nachdem Xiaoyuan „explain“ verwendet hatte, fand er im zusätzlichen Feld ein gefährlich aussehendes Wort: „using filesort“.

„Diese Abfrage verwendet tatsächlich die legendäre Dateisortierung, aber wenn eine Person nicht viele Freunde hat, sollte sie auch mit Dateisortierung schnell sein“, es sei denn, user_id=10086 hat viele Freunde. Später überprüfte Xiaoyuan und stellte fest, dass dieser Benutzer tatsächlich mehr als 100.000 Freunde hat~.

Der kleine Affe war in Gedanken versunken und dachte: Es scheint, ich muss die Schuld dafür auf mich nehmen. 100.000 Datenpunkte sind doch etwas zu viel. Und was ist das Sortierprinzip von Filesort?

Sortieren von Anatomiedateien

Jemand könnte sagen, dass das obige Problem darin besteht, dass 10 W Daten zu groß sind und es langsam ist, auch wenn es nicht sortiert ist. Das macht tatsächlich Sinn. Wenn 10 W Daten auf einmal überprüft werden, werden sowohl der MySQL-Speicherpuffer als auch die Netzwerkbandbreite sehr stark beansprucht. Was ist, wenn ich ein Limit von 1000 hinzufüge? Das Problem der Netzwerkbandbreite wurde definitiv gelöst, da die Gesamtgröße der Datenpakete kleiner geworden ist, aber das Problem der Verwendung von Filesort wurde nicht gelöst. Angesichts dessen haben Sie möglicherweise Fragen: Werden die Dateien durch die Verwendung von Filesort sortiert? Wie sind sie in der Datei sortiert? Oder lassen Sie mich Folgendes fragen: Wie würden Sie vorgehen, wenn Sie mit der Gestaltung einer Sorte beauftragt würden? Lassen Sie uns vor dem Hintergrund dieser Fragen und Überlegungen einen Blick auf die technischen Schwierigkeiten werfen, die mit der Verwendung von Filesort verbunden sind, und wie diese gelöst werden können.

1. Zuerst wird unsere Benutzer-ID indiziert, daher suchen wir zuerst im Benutzer-ID-Indexbaum nach unseren Zieldaten, also den Daten von Benutzer-ID = 10086. Wir möchten jedoch die Felder „Freundname“ und „Freund-Adresse“ abfragen. Leider kann der Benutzer-ID-Index allein die Werte dieser beiden Felder nicht finden.
2. Wir müssen also zur Tabelle zurückkehren und den Primärschlüsselindexbaum nach dem Primärschlüssel durchsuchen, der der Benutzer-ID entspricht. OK, wir haben die Felder „friend_name“ und „friend_addr“ der ersten Benutzer-ID = 10086 gefunden.
3. Was soll ich jetzt tun? Eine direkte Rückkehr ist definitiv nicht richtig, da ich friend_name sortieren muss. Wie sortiere ich es? Die Daten wurden noch nicht gefunden, daher müssen Sie die gefundenen Daten zunächst an einer Stelle ablegen, nämlich im sort_buffer. Ich denke, Sie hätten es anhand des Namens erraten müssen. Ja, sort_buffer ist der Puffer, der in diesem Fall zum Sortieren verwendet wird. Dabei ist zu beachten, dass jeder Thread einen separaten sort_buffer hat. Der Zweck dieser Vorgehensweise besteht hauptsächlich darin, Sperrkonflikte zu vermeiden, die durch mehrere Threads verursacht werden, die auf demselben Speicherblock arbeiten.
4. Wenn der Freundname und die Freundadresse der ersten Daten in den Sortierpuffer eingefügt wurden, ist es natürlich noch nicht vorbei, und die Synchronisierungsschritte werden wiederholt, bis alle Freundnamen und Freundadressen von user_id=10086 in den Sortierpuffer eingefügt wurden.
5. Die Daten im Sortierpuffer wurden in die Daten eingefügt und es ist Zeit, sie zu sortieren. Hier führt MySQL eine Schnellsortierung für Friend_Name durch. Nach der Schnellsortierung ist Friend_Name im Sortierpuffer in Ordnung.
6. Schließlich werden die ersten 1000 Elemente im Sort_Buffer zurückgegeben und der Prozess endet.

Alles sieht reibungslos aus, aber sort_buffer nimmt Speicherplatz ein, was unangenehm ist. Der Speicher selbst ist nicht unendlich, er hat definitiv eine Obergrenze. Natürlich kann sort_buffer nicht zu klein sein. Wenn er zu klein ist, macht es nicht viel Sinn. In der InnoDB-Speicher-Engine beträgt dieser Wert standardmäßig 256 K.

mysql> Variablen wie „sort_buffer_size“ anzeigen;
+------------------+--------+
| Variablenname | Wert |
+------------------+--------+
| Sortierpuffergröße | 262144 |
+------------------+--------+

Das heißt, wenn die in den Sortierpuffer einzufügenden Daten größer als 256 KB sind, funktioniert die Schnellsortiermethode im Sortierpuffer definitiv nicht. Zu diesem Zeitpunkt fragen Sie sich möglicherweise: Kann MySQL nicht automatisch entsprechend der Datengröße erweitert werden? Nun, MySQL ist ein Multithread-Modell. Wenn jeder Thread erweitert wird, wird der anderen Funktionen zugewiesene Puffer kleiner (z. B. Änderungspuffer usw.), was sich auf die Qualität anderer Funktionen auswirkt.

Zu diesem Zeitpunkt müssen wir die Sortiermethode ändern. Ja, dies ist die eigentliche Dateisortierung, d. h. die temporäre Datei auf der Festplatte. MySQL verwendet die Idee der Zusammenführungssortierung, um die zu sortierenden Daten in mehrere Teile aufzuteilen. Nachdem jedes Datenstück im Speicher sortiert wurde, wird es in eine temporäre Datei abgelegt. Schließlich werden die Daten dieser sortierten temporären Dateien zusammengeführt und erneut sortiert. Dies ist ein typisches Teile-und-herrsche-Prinzip. Die spezifischen Schritte sind wie folgt:

1. Teilen Sie zunächst die zu sortierenden Daten in Teile auf, die in den Sortierpuffer eingefügt werden können.
2. Sortieren Sie jedes Datenelement im Sortierpuffer und schreiben Sie es nach dem Sortieren in eine temporäre Datei.
3. Wenn alle Daten in die temporäre Datei geschrieben wurden, ist jede temporäre Datei in der richtigen Reihenfolge, sie stellen jedoch kein Ganzes dar, und das Ganze ist nicht in der richtigen Reihenfolge. Daher müssen die Daten als Nächstes zusammengeführt werden.
4. Angenommen, es gibt zwei temporäre Dateien, tmpX und tmpY. Zu diesem Zeitpunkt wird ein Teil der Daten aus tmpX in den Speicher gelesen, und dann wird ein Teil der Daten aus tmpY in den Speicher gelesen. Sie fragen sich vielleicht, warum es ein Teil ist und nicht die ganze Datei oder eine einzelne Datei? Erstens sind Festplatten langsam. Versuchen Sie daher, bei jedem Mal so viele Daten wie möglich in den Speicher zu lesen. Lesen Sie jedoch nicht zu viele, da der Pufferspeicher begrenzt ist.
5. Nehmen wir für tmpX an, dass tmpX[0-5] eingelesen wird, und für tmpY nehmen wir an, dass tmpY[0-5] eingelesen wird. Dann müssen wir nur noch wie folgt vergleichen: Wenn tmpX[0] < tmpY[0], dann muss tmpX[0] das Kleinste sein. Vergleichen wir dann tmpX[1] und tmpY[0]. Wenn tmpX[1] > tmpY[0], dann muss tmpY[0] das Zweitkleinste sein. Indem wir sie nacheinander vergleichen, können wir schließlich tmpX und tmpY zu einer geordneten Datei tmpZ zusammenführen. Mehrere solcher tmpZ-Dateien können erneut zusammengeführt werden. Schließlich können alle Daten zu einer geordneten großen Datei zusammengeführt werden.

Das Sortieren von Dateien ist sehr langsam. Gibt es eine andere Lösung?

Durch den obigen Sortiervorgang wissen wir, dass eine Dateisortierung erforderlich ist, wenn die zu sortierenden Daten sehr groß sind und die Größe von sort_buffer überschreiten. Die Dateisortierung umfasst Stapelsortierung und Zusammenführung, was sehr zeitaufwändig ist. Die Hauptursache dieses Problems ist, dass sort_buffer nicht ausreicht. Ich weiß nicht, ob Sie bemerkt haben, dass unser friend_name sortiert werden muss, aber friend_addr auch in sort_buffer gestopft wird. Auf diese Weise ist die Größe einer einzelnen Datenzeile gleich der Länge von friend_name + der Länge von friend_addr. Können wir nur das Feld friend_name in sort_buffer speichern? Auf diese Weise ist der gesamte Nutzungsraum groß und temporäre Dateien werden möglicherweise nicht benötigt. Richtig, dies ist eine weitere Sortieroptimierung, über die ich als nächstes sprechen werde: Rowid-Sortierung.

Die Idee der Zeilen-ID-Sortierung besteht darin, unnötige Daten aus dem Sortierpuffer fernzuhalten und nur die erforderlichen Daten im Sortierpuffer zu behalten. Was sind Ihrer Meinung nach also die erforderlichen Daten? Geben Sie einfach den Namen Ihres Freundes ein. Das wird definitiv nicht funktionieren. Was passiert mit friend_addr, nachdem die Sortierung abgeschlossen ist? Daher müssen wir auch die Primärschlüssel-ID eingeben. Nach dem Sortieren können wir über die ID zur Sekundärtabelle zurückkehren und die Friend_Addr abrufen. Daher ist der allgemeine Prozess wie folgt:

1. Suchen Sie anhand des user_id-Index die Zieldaten, kehren Sie dann zur Tabelle zurück und fügen Sie nur die ID und den Freundnamen in den Sortierpuffer ein
2. Wiederholen Sie Schritt 1, bis alle Zieldaten im Sortierpuffer sind.
3. Sortieren Sie die Daten im sort_buffer nach dem Feld friend_name
4. Nach dem Sortieren wird die Tabelle erneut anhand der ID durchsucht, um friend_addr zu finden, und der Vorgang endet, wenn 1.000 Datensätze zurückgegeben werden.

Hier sind tatsächlich einige Punkte zu beachten:

• Diese Methode erfordert zwei Rückgaben an die Tabelle.
• Obwohl der sort_buffer klein ist, sollten temporäre Dateien dennoch sortiert werden, wenn die Datenmenge immer noch groß ist.

Die Frage ist also, wie MySQL zwischen den beiden Methoden wählen sollte. Die Entscheidung, welche Methode verwendet wird, hängt von einer bestimmten Bedingung ab. Die Bedingung ist die Länge einer einzelnen Zeile im Sortierpuffer. Wenn die Länge zu groß ist (die Länge von friend_name + friend_addr), wird rowid verwendet. Andernfalls verwendet die erste Methode den Längenstandard basierend auf max_length_for_sort_data, der standardmäßig 1024 Byte beträgt:

mysql> Variablen wie „max_length_for_sort_data“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| maximale Länge für Sortierdaten | 1024 |
+--------------------------+----------+

Ich möchte nicht zum Tisch zurückkehren und ihn erneut sortieren

Tatsächlich müssen alle der oben genannten Methoden, egal welche verwendet wird, zur Tabelle zurückkehren und sortieren. Die Rückkehr zur Tabelle erfolgt, weil im sekundären Index kein Zielfeld vorhanden ist, und die Sortierung erfolgt, weil die Daten nicht geordnet sind. Wenn im sekundären Index ein Zielfeld vorhanden ist und dieses bereits sortiert ist, wäre das dann nicht das Beste aus beiden Welten?

Das ist richtig, es ist ein gemeinsamer Index. Wir müssen nur einen gemeinsamen Index von (user_id, friend_name, friend_addr) erstellen. Auf diese Weise kann ich die Zieldaten über diesen Index abrufen, und das Feld friend_name ist bereits sortiert. Es gibt auch ein Feld friend_addr. Dies ist in einem Durchgang erledigt, ohne zur Tabelle zurückzukehren oder erneut zu sortieren. Daher ist der allgemeine Ablauf für das obige SQL wie folgt:

• Suchen Sie die Daten von user_id = 10086 über den gemeinsamen Index, lesen Sie dann die entsprechenden Felder friend_name und friend_addr und geben Sie sie direkt zurück, da friend_name bereits sortiert ist und keine zusätzliche Verarbeitung erforderlich ist
• Wiederholen Sie den ersten Schritt und suchen Sie weiter rückwärts entlang des Blattknotens, bis die ersten Daten gefunden werden, die nicht 10086 sind.

Obwohl gemeinsame Indizes dieses Problem lösen können, sollten sie in tatsächlichen Anwendungen nicht blind erstellt werden. Sie sollten anhand der tatsächlichen Geschäftslogik entscheiden, ob sie erstellt werden müssen. Wenn ähnliche Abfragen nicht häufig sind, müssen Sie sie nicht erstellen, da gemeinsame Indizes mehr Speicherplatz beanspruchen und Wartungskosten verursachen.

Zusammenfassen

1. Wenn die Order-By-Anweisung keinen Index verwendet, werden die Worte „using filesort“ im Feld „Extra“ der EXPLAIN-Anweisung angezeigt.
2. Keine Panik, wenn Filesort angezeigt wird. Wenn das Datenvolumen nicht groß ist, z. B. nur ein paar Dutzend Daten, ist die Verwendung von Quicksort im Sortierpuffer ebenfalls sehr schnell.
3. Wenn die Datenmenge groß ist und die Größe des Sortierpuffers überschreitet, ist eine temporäre Dateisortierung erforderlich, die als Mergesort bezeichnet wird. Dies wird vom MySQL-Optimierer bestimmt.
4. Wenn die Abfrage viele Felder enthält und Sie die Verwendung temporärer Dateien zum Sortieren vermeiden möchten, können Sie versuchen, die Größe des Felds max_length_for_sort_data kleiner als die Summe der Längen aller Abfragefelder festzulegen. Dadurch kann das Problem möglicherweise vermieden werden, es ist jedoch ein zusätzlicher Tabellenrückgabevorgang erforderlich.
5. Im tatsächlichen Geschäft können wir auch einen gemeinsamen Index für die Kombination häufig abgefragter Felder erstellen, sodass keine separate Rückkehr zur Tabelle oder Sortierung erforderlich ist. Der gemeinsame Index beansprucht jedoch mehr Speicherplatz und Aufwand.
6. Beim Abfragen einer großen Datenmenge empfiehlt es sich, die Abfrage in Stapeln durchzuführen und im Voraus zu erklären, wie der SQL-Ausführungsplan beachtet werden muss.

Oben finden Sie den detaillierten Inhalt der Sortierung der MySQL-Datenbank. Weitere Informationen zur Sortierung der MySQL-Datenbank finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!