Abfrageprozess und Optimierungsmethode der (JOIN/ORDER BY)-Anweisung in MySQL

Die EXPLAIN-Anweisung wird im MySQL-Abfrageanweisungsprozess vorgestellt und es werden die grundlegenden Konzepte und die Optimierung der EXPLAIN-Anweisung vorgestellt. Außerdem wird ein Beispiel für eine langsame Abfrage gegeben:

Es ist ersichtlich, dass die obige Abfrage mehr als 10.000 Datensätze überprüfen muss und eine temporäre Tabelle und Dateisortierung verwendet. Eine solche Abfrage wird zu einem Albtraum, wenn die Anzahl der Benutzer schnell wächst.

Bevor wir diese Anweisung optimieren, wollen wir zunächst den grundlegenden Ausführungsprozess von SQL-Abfragen verstehen:

1. Die Anwendung sendet den Abfragebefehl über die MySQL-API an den MySQL-Server und dieser wird dann analysiert

2. Überprüfen Sie die Berechtigungen und optimieren Sie mit dem MySQL-Optimierer. Nach dem Parsen und Optimieren wird der Abfragebefehl in einen binären Abfrageplan kompiliert, der von der CPU ausgeführt und zwischengespeichert werden kann.

3. Wenn ein Index vorhanden ist, scannen Sie zuerst den Index. Wenn die Daten durch den Index abgedeckt sind, ist keine zusätzliche Suche erforderlich. Wenn nicht, suchen und lesen Sie den entsprechenden Datensatz basierend auf dem Index.

4. Wenn eine zugehörige Abfrage vorhanden ist, besteht die Abfragereihenfolge darin, die erste Tabelle zu scannen, um die Datensätze zu finden, die die Bedingungen erfüllen, und dann die zweite Tabelle zu scannen, um die Datensätze zu finden, die die Bedingungen gemäß dem zugehörigen Schlüsselwert der ersten und der zweiten Tabelle erfüllen, und in dieser Reihenfolge zu schleifen

5. Abfrageergebnisse ausgeben und Binärprotokolle aufzeichnen

Natürlich wird die Suche durch einen geeigneten Index erheblich vereinfacht und beschleunigt. Schauen wir uns die obige Abfrageanweisung an. Neben bedingten Abfragen gibt es auch assoziierte Abfragen und ORDER BY, also Sortieroperationen.

Schauen wir uns also genauer an, wie Joins und ORDER BY funktionieren. MySQL bietet drei Möglichkeiten, Joins und Datensortierung zu handhaben:

Die erste Methode basiert auf dem Index, die zweite besteht darin, die erste nicht konstante Tabelle per Dateisortierung (Quicksortierung) zu sortieren und die dritte darin, die Ergebnisse der gemeinsamen Abfrage in eine temporäre Tabelle zu übertragen und dann eine Dateisortierung durchzuführen.

Hinweis 1: Weitere Informationen zu Konstantentabellen finden Sie im MySQL-Entwicklerhandbuch: Konstanten und Konstantentabellen.
Hinweis 2: Was ist Filesort? Dies ist nicht die wörtliche Dateisortierung. Filesort hat zwei Modi:
1. Modus 1: Die sortierten Elemente decken die auszugebenden Daten ab. Das Sortierergebnis ist eine Zeichenfolge aus geordneten Sequenzelementen, und es ist kein zusätzliches Lesen der Datensätze erforderlich.
2. Modus 2: Das Sortierergebnis ist eine Folge von Schlüssel-Wert-Paaren <Sortierschlüssel, Zeilen-ID>, und die Datensätze werden über diese Zeilen-IDs gelesen (zufälliges Lesen, ineffizient).
Hinweis 3: Weitere Informationen zu temporären Tabellen finden Sie im MySQL-Entwicklerhandbuch: Wie MySQL interne temporäre Tabellen verwendet

Die erste Methode wird verwendet, wenn der Index der Spalte, von der ORDER BY abhängt, in der ersten nicht konstanten Tabelle vorhanden ist. In diesem Fall kann der bereits geordnete Index direkt verwendet werden, um die Daten der zugehörigen Tabelle zu finden. Diese Methode hat die beste Leistung, da keine zusätzliche Sortieraktion erforderlich ist:

Die zweite Methode wird verwendet, wenn alle Spalten, von denen ORDER BY abhängt, zur ersten Abfragetabelle gehören und kein Index vorhanden ist. In diesem Fall können wir zuerst eine Dateisortierung für die Datensätze der ersten Tabelle durchführen (der Modus kann Modus 1 oder Modus 2 sein), um den geordneten Zeilenindex zu erhalten, und dann die zugehörige Abfrage ausführen. Das Ergebnis der Dateisortierung kann sich im Speicher oder auf der Festplatte befinden, abhängig von der Systemvariablen sort_buffer_size (normalerweise etwa 2 MB):

Die dritte Methode wird verwendet, wenn das Element von ORDER BY nicht zur ersten Tabelle gehört. Das Ergebnis der zugehörigen Abfrage muss in eine temporäre Tabelle eingefügt werden, und dann wird für die temporäre Tabelle eine Dateisortierung durchgeführt:

Die temporäre Tabelle in der dritten Methode kann eine Tabelle im Arbeitsspeicher oder auf der Festplatte sein. Im Allgemeinen wird die Festplatte (On-Disk-Tabelle) in den folgenden zwei Situationen verwendet:

(1) Verwenden der Datentypen BLOB und TEXT

(2) Die Belegung der Speichertabelle überschreitet den Grenzwert der Systemvariablen tmp_table_size / max_heap_table_size (normalerweise etwa 16 MB), sodass sie nur auf der Festplatte abgelegt werden kann

Aus dem obigen Abfrageausführungsprozess und der Methode sollte klar werden, warum die Verwendung von Filesort und Temporary die Abfrageleistung erheblich beeinträchtigt, denn wenn ein Problem mit dem Datentyp oder dem Felddesign vorliegt,

Wenn die abzufragende Tabelle und die Ergebnisse große Datenfelder enthalten, aber kein geeigneter Index verfügbar ist, kann eine große Anzahl von E/A-Vorgängen generiert werden, was die Hauptursache für eine langsame Abfrageleistung ist.

Zurück zum Abfragebeispiel am Anfang des Artikels: Es verwendet offensichtlich die dritte Methode, die am wenigsten effizient ist. Die Optimierungsmethoden, die wir anwenden und ausprobieren müssen, sind:

1. Fügen Sie einen Index für users.fl_no hinzu und erstellen Sie Indizes für die in select und where verwendeten Felder.

2. Übertragen Sie users.fl_no in die Tabelle user_profile oder fügen Sie es als redundantes Feld hinzu

3. Entfernen Sie das Feld vom Typ TEXT. TEXT kann für Chinesisch durch VARCHAR (65535) oder VARCHAR (20000) ersetzt werden.

4. Wenn Sie die Verwendung von Filesort nicht vermeiden können, erhöhen Sie die sort_buffer_size, um die Belastung durch die IO-Vorgänge zu verringern.

5. Versuchen Sie, den Index zu verwenden, der von der ersten Tabelle abgedeckt wird, um zu sortieren. Wenn das nicht funktioniert, verschieben Sie die Sortierlogik von MySQL zur Ausführung in das PHP/Java-Programm.

Nach der Implementierung der Optimierungsmethoden 1, 2 und 3 lauten die EXPLAIN-Ergebnisse wie folgt:

Hinweis: Schreiben Sie eine einfache PHP-Anwendung und verwenden Sie Siege zum Testen. Die Abfrageeffizienz wird um mehr als das Dreifache verbessert.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.