MySQL-Abfrageanweisungsprozess und grundlegende Konzepte der EXPLAIN-Anweisung und deren Optimierung

Die Leistung Ihrer Website oder Ihres Dienstes hängt weitgehend vom Design Ihrer Datenbank ab (vorausgesetzt, Sie wählen das richtige Framework für die Sprachentwicklung) und davon, wie Sie die Daten abfragen.

Wir kennen die Methoden zur Leistungsoptimierung von MySQL, zu denen im Allgemeinen das Erstellen von Indizes, das Vermeiden komplexer gemeinsamer Abfragen, das Festlegen redundanter Felder, das Erstellen von Zwischentabellen, Abfrage-Cache usw. gehören. Wir wissen auch, wie man mit EXPLAIN den Ausführungsplan anzeigt.

Über den Ausführungsprozess und die internen Mechanismen komplexer MySQL-Abfrageanweisungen, die vom MySQL Optimizer selbst vorgenommenen Optimierungen sowie die Auswirkungen von Anpassungen der Abfrageanweisungen auf die Leistung und deren Ursachen ist jedoch wenig bekannt.

In diesem Artikel wird versucht, einige Schlüsselkonzepte wie den Ausführungsprozess und die Indexverwendung eingehender zu erörtern, um das Warum und Warum zu verstehen.

Dadurch kann ein blinder Wechsel zu NoSQL-Speicher oder die Investition in Infrastrukturupgrades vermieden werden, wenn durch eine einfache MySQL-Optimierung gute Ergebnisse erzielt werden können.

Wenn Sie Ihre Arbeit gut machen möchten, müssen Sie zuerst Ihre Werkzeuge schärfen. Hier stellen wir zunächst das Tool zur Leistungsanalyse von MySQL-Abfrageanweisungen vor.

Der EXPLAIN-Befehl von MySQL ist ein Tool zur Analyse der Abfrageleistung. Jede Zeile der EXPLAIN-Ausgabe entspricht der Ausführungsplanbeschreibung einer Tabelle in der Abfrageanweisung. Die Bedeutung der Ausgabespalten ist wie folgt:

Die Typspalte in der obigen Tabelle ist der Tabellenassoziationstyp. Gängige Typen sind die folgenden (von hoch nach niedrig sortiert, je nach Effizienz der Assoziationsabfrage):

const (konstante Verbindung), wie z. B. SELECT * FROM user WHERE id=1;
eq_ref (Referenz mit gleichem Wert), z. B. SELECT * FROM user,card WHERE user.id=card.userid;
ref (Referenz), wird für nicht eindeutige Indizes verwendet, wie z. B. SELECT * FROM user,card WHERE user.last_name='test';
Bereich, z. B. SELECT * FROM tbl_name WHERE key_column > 10;
Index: Daten entsprechend dem Index lesen. Wenn der Index die Abfragedaten bereits enthält, muss nur der Indexbaum gescannt werden. Andernfalls wird ein vollständiger Tabellenscan durchgeführt, der „All“ ähnelt.
ALL (alles), vollständiger Tabellenscan

Die Schlüsselspalte stellt den Index dar und die Zeilen stellen die geschätzte Anzahl der zu scannenden Zeilen dar.

Extra gibt zusätzliche Informationen an. Häufige sind die folgenden (ebenfalls in absteigender Reihenfolge der Abfrageeffizienz angeordnet):

Using index: zeigt die Verwendung eines Indexes an. Wenn gleichzeitig Using where erscheint, bedeutet dies, dass der Index zum Suchen und Lesen von Datensätzen verwendet wird. Wenn Using where nicht gefunden wird, bedeutet dies, dass der Index die Abfragedaten enthält und keine zusätzliche Suche erforderlich ist.
Die Verwendung von where: weist auf eine bedingte Abfrage hin. Wenn die Typspalte ALL oder Index ist und diese Information nicht angezeigt wird, führen Sie möglicherweise eine falsche Abfrage aus: Sie gibt alle Daten zurück.
Filesort verwenden: Das ist nicht das, was „Dateiindex verwenden“ bedeutet! Filesort ist eine von MySQL implementierte Sortierstrategie. Diese Meldung erscheint normalerweise, wenn die Sortieranweisung ORDER BY verwendet wird.
Temporär verwenden: Um das Ergebnis zu erhalten, wird eine temporäre Tabelle verwendet. Dies kommt normalerweise vor, wenn mehrere Tabellen verknüpft und die Ergebnisse sortiert werden.

Wenn die folgenden beiden Meldungen (Using filesort, Using temporary) in EXPLAIN erscheinen und die Zeilen relativ groß sind, bedeutet dies normalerweise, dass Sie die Abfrageanweisung anpassen oder einen Index hinzufügen müssen. Kurz gesagt, Sie müssen versuchen, diese beiden Meldungen zu vermeiden.

Nachfolgend sehen Sie ein Beispiel für EXPLAIN-Ergebnisse (Suchen nach Spitznamen und Geschlechtern aus der Benutzerprofiltabelle, Sortieren nach der Anzahl der Benutzer-Follower in der Benutzertabelle):

Die obige Abfrageanweisung ist ein typischer Problemfall. Die spezifische Bedeutung von „Using filesort“ und „Using temporary“ sowie die Optimierung der obigen Anweisung werden im nächsten Artikel im Zusammenhang mit dem Abfrageprozess und den Prinzipien erläutert.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird Ihnen hilfreich sein. Wenn Sie weitere Fragen haben, können Sie uns gerne eine Nachricht hinterlassen. Bitte schauen Sie weiterhin bei 123WORDPRESS.COM vorbei!