So analysieren Sie die Leistung von MySQL-Abfragen

Abfrageoptimierung, Indexoptimierung und Tabellendesignoptimierung sind eng miteinander verknüpft. Wenn Sie über umfangreiche Erfahrung beim Schreiben von MySQL-Abfrageanweisungen verfügen, wissen Sie, wie Sie Tabellen und Indizes entwerfen, um effiziente Abfragen zu unterstützen. Ebenso hilft die Kenntnis des Tabellenentwurfs dabei zu verstehen, wie sich die Tabellenstruktur auf die Abfrageanweisungen auswirkt. Selbst wenn das Tabellen- und Indexdesign gut ist, ist die Abfrageleistung daher schlecht, wenn die Abfrageanweisung schlecht geschrieben ist.

Bevor Sie versuchen, schnelle Abfragen zu schreiben, müssen Sie sich daran erinnern, dass die Geschwindigkeit anhand der Antwortzeit gemessen wird. Eine Abfrageanweisung ist eine große Aufgabe, die aus mehreren Unteraufgaben besteht, und jede Unteraufgabe nimmt Zeit in Anspruch. Um die Abfrage zu optimieren, müssen wir die Anzahl der Unteraufgaben so weit wie möglich reduzieren oder dafür sorgen, dass die Unteraufgaben schneller ausgeführt werden. Hinweis: Manchmal müssen wir auch die Auswirkungen einer Abfrage auf andere Abfragen im System berücksichtigen. In diesem Fall müssen wir auch den Ressourcenverbrauch so weit wie möglich reduzieren. _ Im Allgemeinen können wir uns den Lebenszyklus einer Abfrage so vorstellen, dass er das gesamte Interaktionssequenzdiagramm vom Client zum Server durchläuft, einschließlich der Analyse der Abfrageanweisung, der Abfrageplanung, des Ausführungsprozesses und der an den Client zurückgegebenen Daten. Die Ausführung ist der wichtigste Teil des Abfrageprozesses und umfasst eine große Anzahl von Aufrufen zum Abrufen von Datenzeilen aus der Speicher-Engine sowie die Nachbearbeitung der Daten, beispielsweise Gruppieren und Sortieren.

Nach Abschluss aller dieser Aufgaben wird die Abfrage auch Zeit in Bezug auf Netzwerkübertragungsfehler, CPU-Verarbeitung, Datenstatistiken und Strategieplanung, das Warten auf Sperren und das Abrufen von Datenzeilen aus der Speicher-Engine in Anspruch nehmen. Diese Aufrufe verbrauchen Zeit bei Speicheroperationen, CPU-Operationen und E/A-Operationen. In jedem Fall entsteht zusätzlicher Zeitaufwand, wenn diese Vorgänge missbraucht, zu oft oder zu langsam ausgeführt werden. Das Ziel der Abfrageoptimierung besteht darin, diese Situationen zu vermeiden – entweder durch Eliminierung bzw. Reduzierung von Vorgängen oder durch deren Beschleunigung.

Es ist wichtig zu beachten, dass wir kein genaues Diagramm des Abfragelebenszyklus zeichnen können. Unser Ziel besteht darin, zu zeigen, wie wichtig es ist, den Abfragelebenszyklus zu verstehen und darüber nachzudenken, wie zeitaufwändig diese Schritte sind. Auf dieser Grundlage können Sie mit der Optimierung der Abfrageanweisung beginnen.

Grundlagen langsamer Abfragen: Optimieren der Datenerfassung

Der grundlegendste Grund für eine schlechte Abfrageleistung ist die Verarbeitung zu vieler Daten. Einige Abfragen müssen große Datenmengen filtern, was nicht optimiert werden kann. Aber das ist keine normale Situation. Die meisten fehlerhaften Abfragen können durch den Zugriff auf weniger Daten optimiert werden. Die folgenden beiden Schritte sind für die Analyse von Abfragen mit schlechter Leistung hilfreich:

1. Finden Sie heraus, ob die App auf andere Daten zugreift als die, die Sie benötigen. Normalerweise bedeutet dies, dass die Anwendung zu viele Datenzeilen oder -spalten abruft.
2. Finden Sie heraus, ob der MySQL-Server mehr Zeilen analysiert als nötig.
   

Prüfen Sie, ob unnötige Daten aus der Datenbank angefordert werden

Manche Abfragen fordern die benötigten Daten vom Datenbankserver an und verwerfen sie anschließend. Dies erhöht den Arbeitsaufwand des MySQL-Servers, erhöht die Netzwerklast und verbraucht mehr Speicher- und CPU-Ressourcen des Anwendungsservers. Hier sind einige typische Fehler:

1. Abrufen unnötiger Zeilen: Ein weit verbreitetes Missverständnis besteht darin, anzunehmen, dass MySQL nur die erforderlichen Ergebnisse liefert, anstatt den gesamten Ergebnissatz zu berechnen und zurückzugeben. Normalerweise passiert dieser Fehler Leuten, die mit anderen Datenbanksystemen vertraut sind. Diese Entwickler sind es gewohnt, SELECT-Anweisungen zu verwenden, die viele Zeilen zurückgeben, dann die ersten N Zeilen zu extrahieren und den zurückgegebenen Ergebnissatz danach nicht mehr zu verwenden (z. B. die 100 aktuellsten Artikel von einer Informationswebsite abzurufen und dann nur 10 davon auf der Front-End-Seite anzuzeigen). Sie denken, dass MySQL die Abfrage nach dem Erhalt von 10 Datenzeilen beendet, aber tatsächlich ruft MySQL den vollständigen Datensatz ab. Der Client ruft dann entweder alle Daten ab und verwirft die meisten davon. Die beste Lösung besteht darin, der Abfrage eine LIMIT-Bedingung hinzuzufügen.
2. Alle Spalten in einer Multi-Table-Join-Abfrage abrufen: Wenn Sie alle Schauspieler im Film „Age of Dinosaurs“ abrufen müssen, schreiben Sie Ihre SQL-Anweisung nicht so:

WÄHLEN SIE * VON sakila.actor
INNER JOIN sakila.file_actor USING(Schauspieler-ID)
INNER JOIN sakila.file USING (film_id)
WO sakila.film.title = „Akademie-Dinosaurier“;

Dadurch werden alle Spalten aus den drei an der Union-Abfrage beteiligten Tabellen zurückgegeben. Ein besserer Ansatz wäre, es so zu schreiben:

WÄHLEN SIE sakila.actor.* FROM sakila.actor
INNER JOIN sakila.file_actor USING(Schauspieler-ID)
INNER JOIN sakila.file USING (film_id)
WO sakila.film.title = „Akademie-Dinosaurier“;

1. Alle Datenspalten abrufen: Seien Sie skeptisch, wenn Sie eine Abfrage wie „SELECT *“ sehen: Benötigen Sie wirklich alle Spalten? Höchstwahrscheinlich nicht. Durch das Abrufen aller Datenspalten werden die entsprechenden Indizes ungültig und es kommt zu einer Erhöhung der E/A-Belastung, des Speicherverbrauchs und der CPU-Auslastung. Einige DBAs deaktivieren aus diesem Grund einfach SELECT *. Dadurch können die Probleme verringert werden, die durch das Ändern von Tabellenspalten entstehen. Natürlich ist die Abfrage unnötiger Daten nicht immer schlecht. Die Umfrage ergab, dass dieser Ansatz die Entwicklungsarbeit vereinfachen kann, da er die Wiederverwendbarkeit von Code verbessern kann. Solange Sie wissen, dass es die Leistung beeinträchtigt, wäre das ein gültiger Grund. Ebenso verbessert sich die Cache-Trefferquote, wenn in der Anwendung bestimmte Caching-Mechanismen verwendet werden. Das Abrufen und Zwischenspeichern vollständiger Objekte kann durch die Ausführung mehrerer separater Abfragen, die Teile der Objekte abrufen, verbessert werden.
2. Wiederholtes Abrufen derselben Daten: Wenn Sie nicht aufpassen, können Sie in Ihrer Anwendung leicht Code schreiben, der dieselben Daten abruft. Wenn Sie beispielsweise das Profilbild eines Benutzers in einer Kommentarliste anzeigen möchten, können Sie es einmal für jeden Kommentar abrufen. Effektiver ist es, die Daten nach dem ersten Abruf zwischenzuspeichern und direkt in der Kommentarliste zu verwenden.
   

Überprüfen Sie, ob MySQL zu viele Daten verarbeitet

Nachdem Sie sichergestellt haben, dass Ihre Abfragen keine unnötigen Daten abrufen, können Sie nach Abfragen suchen, die zu viele Daten verarbeiten, bevor sie Ergebnisse zurückgeben. In MySQL sind die einfachsten Abfragestandards:

1. Ansprechzeit
2. Die Anzahl der verarbeiteten Zeilen
3. Die Anzahl der zurückgegebenen Zeilen
   

Keine dieser Metriken ist ein perfektes Maß für die Abfrageleistung, aber sie geben eine ungefähre Vorstellung davon, wie viele Daten MySQL während der internen Verarbeitung bei der Ausführung einer Abfrage abruft und wie schnell die Abfrage ausgeführt wird. Diese drei Kriterien werden im Protokoll langsamer Abfragen aufgezeichnet. Daher ist das Entdecken von Abfragen mit übermäßiger Datenverarbeitung aus dem Protokoll langsamer Abfragen die beste Vorgehensweise zur Abfrageoptimierung.

Antwortzeit: Beachten Sie zunächst, dass die Antwortzeit bei Abfragen ein Symptom ist, das wir beobachten. In Wirklichkeit ist die Reaktionszeit komplizierter als wir denken. Die Reaktionszeit besteht aus zwei Teilen: Servicezeit und Wartezeit. Die Servicezeit ist die Zeit, die der Server benötigt, um die Abfrage tatsächlich zu verarbeiten. Die Wartezeit ist die Zeit, in der der Server die Abfrage nicht tatsächlich ausführt, sondern auf eine Ressource wartet, beispielsweise auf den Abschluss eines E/A-Vorgangs, die Freigabe einer Zeilensperre usw. Das Problem besteht darin, dass Sie die Reaktionszeit nicht genau in diese beiden Teile aufteilen können – es sei denn, Sie können die Zeit für jeden Teil separat messen, was schwierig ist. Die häufigsten und wichtigsten Fälle sind E/A-Blockierungen und das Warten auf Sperren, aber das trifft nicht immer zu.

Dies hat zur Folge, dass die Reaktionszeiten unter unterschiedlichen Lastbedingungen nicht konstant sind. Auch andere Faktoren wie Speichermodulsperren, hohe Parallelität und Hardware können die Reaktionszeit beeinflussen. Daher müssen Sie bei der Überprüfung der Antwortzeit zunächst feststellen, ob die Antwortzeit ausschließlich durch diese Abfrage verursacht wird. Die Abfrageantwortzeit kann mithilfe der QUBE-Methode (Query Quick Upper Bound Estimate) geschätzt werden: Durch die Untersuchung des Abfrageplans und der verwendeten Indizes wird die Anzahl der erforderlichen sequentiellen und zufälligen E/A-Zugriffsvorgänge ermittelt und diese dann mit der Zeit multipliziert, die die Hardware des Computers für die Ausführung jedes Vorgangs benötigt. Durch die Summierung aller Zeiten können wir beurteilen, ob die langsame Abfrageantwort durch die Abfrage selbst oder andere Gründe verursacht wird.

Anzahl der verarbeiteten und zurückgegebenen Zeilen Bei der Analyse einer Abfrage ist es hilfreich, an die Anzahl der verarbeiteten Zeilen zu denken, da wir dadurch eine intuitive Vorstellung davon bekommen, wie die Abfrage an die benötigten Daten gelangt. Dies ist jedoch kein perfektes Messinstrument zum Auffinden fehlerhafter Abfragen. Nicht alle Zeilenzugriffe sind identisch. Auf weniger Zeilen kann schneller zugegriffen werden und das Abrufen von Zeilen aus dem Speicher ist viel schneller als das Abrufen von der Festplatte.

Im Idealfall wären die verarbeiteten Zeilen und die zurückgegebenen Zeilen identisch, in der Praxis ist dies jedoch selten der Fall. Wenn Sie beispielsweise zum Erstellen einer zurückgegebenen Zeile einen gemeinsamen Index verwenden, muss der Server Daten aus mehreren Zeilen abrufen, um die zurückgegebenen Zeilendaten zu generieren. Das Verhältnis von verarbeiteten Zeilen zu zurückgegebenen Zeilen ist normalerweise gering und liegt zwischen 1:1 und 10:1, kann aber manchmal um Größenordnungen größer sein.

Datenzeilenverarbeitungs- und -abruftypen

Wenn Sie über die Kosten einer Abfrage nachdenken, berücksichtigen Sie die Kosten für das Abrufen einer einzelnen Zeile aus einer Tabelle. MySQL verwendet verschiedene Abrufmethoden, um eine Datenzeile zu finden und zurückzugeben. Einige müssen möglicherweise mehrere Zeilen verarbeiten, während andere Ergebnisse ohne Überprüfung zurückgeben.

Die Methode zum Abrufen der Daten befindet sich in der Typspalte der EXPLAIN-Ausgabeergebnisse. Beinhaltet vollständige Tabellenscans, Indexscans, Bereichsscans, eindeutige Indexsuchen und Konstanten. Jede der oben genannten Methoden ist schneller als die vorherige, da die gelesene Datenmenge sukzessive abnimmt. Wir müssen nicht daran denken, den Typ zu erhalten, aber wir müssen das Grundkonzept verstehen.

Wenn kein guter Abruftyp vorhanden ist, besteht die beste Möglichkeit zur Lösung des Problems darin, einen geeigneten Index hinzuzufügen. Durch Indizes muss MySQL weniger Daten untersuchen und kann somit Zeilen effizienter abrufen. Nehmen wir zum Beispiel die folgende einfache Abfrage:

EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

Diese Abfrage gibt 10 Datenzeilen zurück und der Befehl EXPLAIN zeigt, dass MySQL den Ref-Typ verwendet, um die Abfrageanweisung für den Index idx_fk_film_id auszuführen.

***********************1. Reihe************************
ID: 1
select_type: EINFACH
Tabelle:Filmschauspieler
Typ: ref 
möglich)Schlüssel: idx_fk_film_id
Schlüssel: idx_fk_film_id
Schlüssellänge: 2
Verweis: const
Reihen: 10
Extra: 

Der EXPLAIN-Befehl zeigt, dass MySQL schätzt, dass nur 10 Zeilen abgerufen werden müssen, um die Abfrage abzuschließen. Mit anderen Worten: Der Abfrageoptimierer weiß, wie der Abruftyp auszuwählen ist, um die Abfrage effizienter zu gestalten. Was passiert, wenn für die Abfrage kein passender Index vorhanden ist? MySQL muss einen suboptimalen Abruftyp verwenden und sich dann die Ergebnisse nach dem Löschen des Tabellenindex ansehen.

ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
ALTER TABLE sakila.film_actor DROP DROP KEY idx_fk_film_id;
EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

***********************1. Reihe************************
ID: 1
select_type: EINFACH
Tabelle:Filmschauspieler
Typ: ALLE 
möglich)Schlüssel: NULL
Schlüssel: NULL
key_len: NULL
Ref: NULL
Reihen: 5073
Extra: Verwenden von „where“

Wie erwartet wird der Abruftyp zu einem vollständigen Tabellenscan (ALL), und MySQL schätzt, dass 5073 Datenzeilen verarbeitet werden müssen, um die Abfrage abzuschließen. Die Verwendung von „where“ in der Spalte „Extra“ zeigt, dass der MySQL-Server die WHERE-Bedingung verwendet, um andere Daten zu verwerfen, die die von der Speicher-Engine gelesenen Bedingungen nicht erfüllen. Normalerweise verwendet MySQL WHERE-Bedingungen auf die folgenden drei Arten (von der besten zur schlechtesten):

1. Entfernen nicht übereinstimmender Datenzeilen durch Indexsuchvorgänge, die auf der Speichermodulebene erfolgen;
2. Verwenden Sie abdeckende Indizes (angezeigt als „Verwendeter Index“ in der Spalte „Extra“), um den Zugriff auf Datenzeilen zu vermeiden, und filtern Sie nach Erhalt der Ergebnisse Daten heraus, die die Bedingungen nicht erfüllen. Dies geschieht auf Serverebene, erfordert jedoch kein Lesen von Zeilen aus den Tabellen.
3. Holen Sie sich die Datenzeilen aus der Datentabelle und filtern Sie dann die nicht übereinstimmenden Daten heraus (angezeigt als „Verwenden wo“ in der Spalte „Extra“). Dies geschieht auf Serverebene und erfordert das Lesen von Zeilen aus der Tabelle vor dem Filtern der Daten.

Das folgende Beispiel zeigt, wie wichtig gute Indizes sind. Gute Indizes helfen dabei, gute Datenabrufmuster zu verwenden und nur die erforderlichen Zeilen zu verarbeiten. Das Hinzufügen eines Indexes bedeutet jedoch nicht immer, dass MySQL konsistente Zeilen abruft und zurückgibt. Beispielsweise die unten stehende Aggregationsmethode COUNT().

Wählen Sie Schauspieler-ID, ANZAHL(*) AUS sakila.film_actor GRUPPE NACH Schauspieler-ID;

Diese Abfrage gibt nur 200 Zeilen zurück, muss aber Tausende von Zeilen lesen, bevor der zurückgegebene Ergebnissatz erstellt wird. Mit einer solchen Abfrageanweisung lässt sich die Anzahl der zu verarbeitenden Datenzeilen auch mit einem Index nicht reduzieren.

Leider verrät Ihnen MySQL nicht, wie viele Zeilen abgerufen wurden, um den zurückgegebenen Ergebnissatz zu erstellen. Es zeigt Ihnen nur die Gesamtzahl der abgerufenen Zeilen an. Viele Zeilen werden durch die WHERE-Bedingung herausgefiltert und haben keinen Einfluss auf den zurückgegebenen Ergebnissatz. Im vorherigen Beispiel ruft die Abfrage nach dem Entfernen des Index sakila.film_actor alle Zeilen der Datentabelle ab, nimmt jedoch nur 10 Daten daraus als Ergebnissatz. Wenn Sie wissen, wie viele Zeilen der Server abgerufen und wie viele Zeilen er zurückgegeben hat, können Sie die Abfrage selbst besser verstehen. Wenn Sie feststellen, dass Sie eine große Anzahl Zeilen abrufen müssen, im Ergebnis jedoch nur wenige Zeilen verwenden, können Sie dieses Problem folgendermaßen beheben:

1. Verwenden Sie abdeckende Indizes, die es der Speicher-Engine ersparen, die gesamte Datenzeile abzurufen (sie werden direkt aus dem Index abgerufen).
2. Ändern Sie eine Abfragetabelle. Ein Beispiel ist das Erstellen einer Übersichtstabelle zum Abfragen statistischer Daten.
3. Schreiben Sie komplexe Abfrageanweisungen neu, damit der MySQL-Abfrageoptimierer sie optimaler ausführen kann.

Oben finden Sie Einzelheiten dazu, wie MySQL die Abfrageleistung analysiert. Weitere Informationen zur Analyse der MySQL-Abfrageleistung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!