Gründe, warum MySQL-Abfragen langsam sind

Wenn es um die Leistungsoptimierung von MySQL geht, ist die Abfrageoptimierung die Quelle der Optimierung und auch der beste Indikator dafür, ob ein System schneller ist. In diesem und den folgenden Kapiteln liegt der Schwerpunkt auf der Optimierung der Abfrageleistung. Wir stellen einige Techniken zur Abfrageoptimierung vor, die Ihnen ein tieferes Verständnis davon vermitteln, wie MySQL Abfragen tatsächlich ausführt, wo es langsam ist, wie man es schneller machen kann und die Gründe für hohe bzw. niedrige Effizienz verstehen. Dies wird Ihnen helfen, SQL-Abfrageanweisungen besser zu optimieren.

Dieses Kapitel beginnt mit „Warum ist die Abfragegeschwindigkeit so langsam?“, damit Sie genau wissen, wo die Abfrage möglicherweise langsam ist. Auf diese Weise können Sie die Abfrage besser optimieren und anderen einen Schritt voraus sein.

1. Wo ist die Langsamkeit?

**Das wahre Maß der Abfragegeschwindigkeit ist die Antwortzeit. **Wenn Sie sich eine Abfrage als Aufgabe vorstellen, besteht sie aus einer Reihe von Unteraufgaben, von denen jede eine bestimmte Zeit in Anspruch nimmt. Wenn Sie eine Abfrage optimieren möchten, möchten Sie eigentlich deren Unteraufgaben optimieren, indem Sie entweder einige davon eliminieren, die Anzahl der Ausführungen einer Unteraufgabe verringern oder dafür sorgen, dass eine Unteraufgabe schneller ausgeführt wird.

Welche Unteraufgaben werden ausgeführt, wenn MySQL eine Abfrage ausführt, und welche Unteraufgaben nehmen am meisten Zeit in Anspruch? Dies erfordert den Einsatz bestimmter Tools oder Methoden (z. B. Ausführungspläne), um die Abfrage zu analysieren und die Ursache der Verlangsamung zu ermitteln.

Im Allgemeinen kann der Lebenszyklus einer Abfrage grob in der folgenden Reihenfolge betrachtet werden: **Vom Client zum Server, dann auf dem Server analysiert, ein Ausführungsplan wird generiert, ausgeführt und die Ergebnisse werden an den Client zurückgegeben. ** Unter diesen kann die „Ausführung“ als die wichtigste Phase im gesamten Lebenszyklus angesehen werden. Sie umfasst eine große Anzahl von Aufrufen der Speicher-Engine zum Abrufen von Daten und die Datenverarbeitung nach dem Aufruf, einschließlich Sortieren, Gruppieren usw.

Bei der Ausführung dieser Aufgaben müssen Abfragen an verschiedenen Stellen in verschiedenen Phasen Zeit verbringen, darunter Netzwerk, CPU-Berechnung, Generierung von Statistiken und Ausführungsplänen, Sperrwartevorgänge und andere Vorgänge, insbesondere die Aufrufvorgänge zum Abrufen von Daten aus der zugrunde liegenden Speicher-Engine. Diese Aufrufe erfordern Speichervorgänge, CPU-Vorgänge und können auch eine große Anzahl von Kontextwechseln und Systemaufrufen generieren.

Alle oben genannten Vorgänge sind sehr zeitaufwändig und es sind einige unnötige zusätzliche Vorgänge erforderlich. Einige Vorgänge müssen möglicherweise mehrmals wiederholt werden und andere werden möglicherweise sehr langsam ausgeführt. In diesem Fall können Abfragen tatsächlich langsam sein und das Ziel der Abfrageoptimierung besteht darin, den Zeitaufwand für diese Vorgänge zu reduzieren oder zu eliminieren.

Durch die obige Analyse erhalten wir ein umfassendes Verständnis des Abfrageprozesses und können deutlich erkennen, wo bei der Abfrage möglicherweise Probleme auftreten, die letztendlich zu einer Verlangsamung der gesamten Abfrage führen, und so eine Richtung für die tatsächliche Abfrageoptimierung vorgeben.

Mit anderen Worten kann die Abfrageoptimierung aus den folgenden zwei Perspektiven durchgeführt werden:

• Reduzieren Sie die Anzahl der Unterabfragen
• Reduzieren Sie zusätzliche, sich wiederholende Vorgänge
  

Eine häufige Ursache für eine schlechte Abfrageleistung ist der Zugriff auf zu viele Daten. Bei kleinen Datenmengen ist die Abfragegeschwindigkeit gut. Sobald die Datenmenge zunimmt, ändert sich die Abfragegeschwindigkeit drastisch, was die Leute in den Wahnsinn treibt und für eine sehr schlechte Erfahrung sorgt. Zur Abfrageoptimierung können Sie die folgenden Aspekte prüfen:

• Werden unnötige Daten abgefragt?
• Ob zusätzliche Datensätze gescannt wurden
  

2. Haben Sie unnötige Daten abgefragt?

Bei tatsächlichen Abfragen werden häufig die tatsächlich benötigten Daten abgefragt und anschließend die redundanten Daten von der Anwendung verworfen. Dies stellt einen zusätzlichen Overhead für MySQL dar und verbraucht zudem CPU- und Speicherressourcen des Anwendungsservers.

Einige typische Fälle sind wie folgt:

1. Unnötige Datensätze abfragen

Dies ist ein häufiger Fehler. Oft wird fälschlicherweise angenommen, dass MySQL nur die erforderlichen Daten zurückgibt. Tatsächlich gibt MySQL jedoch den gesamten Ergebnissatz zurück, bevor die Berechnungen durchgeführt werden.

Entwickler verwenden normalerweise SELECT-Anweisungen, um eine große Anzahl von Ergebnissen abzufragen, und verwenden dann die Anwendungsabfrage oder die Front-End-Anzeigeebene, um die ersten N Datenzeilen abzurufen. Beispielsweise werden auf einer Nachrichten-Website 100 Datensätze abgefragt, aber nur die ersten 10 werden auf der Seite angezeigt.

Die effektivste Lösung besteht darin, so viele Datensätze wie nötig abzufragen und normalerweise nach der Abfrage LIMIT hinzuzufügen, d. h. eine paginierte Abfrage.

2. Alle Spalten zurückgeben, wenn mehrere Tabellen verknüpft sind

Wenn Sie alle Schauspieler finden möchten, die im Film Academy Dinosaur aufgetreten sind, tun Sie Folgendes:

wähle * von Schauspieler a
innerer Join Filmschauspieler fa.actorId = a.actorId
innerer Join-Film f f.filmId = fa.filmId
wobei fa.title = ‚Akademie-Dinosaurier‘;

Dadurch werden alle Datenspalten der drei Tabellen zurückgegeben, aber die eigentliche Anforderung besteht darin, die Akteurinformationen abzufragen. Die korrekte Schreibweise lautet:

wähle a.* von Schauspieler a
innerer Join Filmschauspieler fa.actorId = a.actorId
innerer Join-Film f f.filmId = fa.filmId
wobei fa.title = ‚Akademie-Dinosaurier‘;

3. Immer alle Spalten abfragen

Jedes Mal, wenn Sie select * sehen, müssen Sie es mit einem seltsamen Blick betrachten. Müssen Sie wirklich alle Datenspalten zurückgeben?

In den meisten Fällen ist dies nicht erforderlich. Select * führt zu einem vollständigen Tabellenscan, der den Optimierer daran hindert, Optimierungen wie Indexscans abzuschließen. Zu viele Spalten führen außerdem zu zusätzlichem E/A-, Speicher- und CPU-Verbrauch für den Server. Auch wenn Sie wirklich alle Spalten abfragen müssen, sollten Sie alle Spalten einzeln auflisten, anstatt *.

4. Immer wieder dieselben Daten abfragen

Wenn Sie nicht aufpassen, kann leicht der Fehler passieren, dass Sie die gleiche Abfrage immer wieder ausführen und jedes Mal genau die gleichen Daten zurückgeben.

Wenn Sie beispielsweise die URL des Avatars des Benutzers im Kommentarbereich des Benutzers abfragen müssen, können Sie diese Daten wiederholt abfragen, wenn der Benutzer mehrere Kommentare abgibt. Eine bessere Möglichkeit, dies zu handhaben, besteht darin, die Daten bei der ersten Abfrage zwischenzuspeichern und sie dann bei der nachfolgenden Verwendung direkt aus dem Cache abzurufen.

3. Werden zusätzliche Datensätze gescannt?

Nachdem Sie sichergestellt haben, dass Ihre Abfrage nur nach den Daten sucht, die Sie benötigen, sollten Sie als Nächstes überprüfen, ob die Abfrage zu viele Daten scannt. Für MySQL lauten die drei einfachsten Metriken zum Messen des Abfrage-Overheads wie folgt:

• Ansprechzeit
• Anzahl der gescannten Zeilen
• Anzahl der zurückgegebenen Zeilen
  

Keine einzelne Metrik kann die Kosten einer Abfrage vollständig messen, sie kann jedoch in etwa widerspiegeln, auf wie viele Daten MySQL bei der Ausführung einer Abfrage zugreifen muss, und ermöglicht eine grobe Schätzung der tatsächlichen Zeit, die zum Ausführen einer Abfrage benötigt wird. Diese drei Indikatoren werden im MySQL-Slow-Log aufgezeichnet. Durch die Überprüfung der Slow-Log-Einträge können Sie daher die Abfragen ermitteln, die zu viele Zeilen scannen.

Langsame Abfrage: Wird verwendet, um Anweisungen in MySQL aufzuzeichnen, deren Antwortzeit den Schwellenwert (long_query_time, standardmäßig 10 s) überschreitet, und um die langsame Abfrage im langsamen Protokoll aufzuzeichnen. Sie können langsame Abfragen über die Variable slow_query_long aktivieren. Sie ist standardmäßig geschlossen. Langsame Protokolle können zur Überprüfung und Analyse in der slow_log-Tabelle oder in Dateien aufgezeichnet werden.

1. Reaktionszeit

Die Reaktionszeit ist die Summe aus zwei Teilen: Servicezeit und Wartezeit. Die Servicezeit bezieht sich darauf, wie lange die Datenbank tatsächlich gebraucht hat, um die Abfrage zu verarbeiten. Die Wartezeit bezieht sich auf die Zeit, in der der Server die Abfrage nicht tatsächlich ausführt, weil er auf Ressourcen wartet (beispielsweise E/A-Vorgänge, Zeilensperren usw.).

Für die Reaktionszeit bei unterschiedlichen Anwendungsbelastungen gibt es kein einheitliches Muster oder keine einheitliche Formel. Viele Faktoren, wie z. B. Speichermodulsperren (Tabellensperren, Zeilensperren), Ressourcenkonkurrenz bei hoher Parallelität und Hardwarereaktionen, können die Reaktionszeit beeinflussen. Daher kann die Reaktionszeit je nach Fall sowohl das Ergebnis als auch die Ursache eines Problems sein.

Wenn Sie die Antwortzeit einer Anfrage sehen, müssen Sie sich zunächst fragen, ob diese Antwortzeit ein angemessener Wert ist.

2. Anzahl der gescannten Zeilen und Anzahl der zurückgegebenen Zeilen

Beim Analysieren einer Abfrage ist es hilfreich, die Anzahl der von der Abfrage gescannten Zeilen anzuzeigen und auch zu analysieren, ob zusätzliche Datensätze gescannt wurden.

Diese Metrik ist möglicherweise nicht perfekt zum Identifizieren fehlerhafter Abfragen geeignet, da nicht für alle Zeilen die gleichen Zugriffskosten anfallen. Auf kürzere Zeilen wird relativ schnell zugegriffen, und auf Zeilen im Speicher kann viel schneller zugegriffen werden als auf Zeilen auf der Festplatte.

**Idealerweise sollte die Anzahl der gescannten Zeilen und die Anzahl der zurückgegebenen Zeilen gleich sein. **Aber in Wirklichkeit ist diese Art von Schönheit nicht üblich. Wenn Sie beispielsweise eine Join-Abfrage ausführen, ist das Verhältnis zwischen der Anzahl der gescannten Zeilen und der Anzahl der zurückgegebenen Zeilen normalerweise sehr klein und liegt normalerweise zwischen 1:1 und 10:1. Manchmal kann dieser Wert jedoch sehr groß sein.

3. Anzahl der gescannten Zeilen und Zugriffstyp

Bei der Auswertung des Abfrage-Overheads müssen Sie den Aufwand für die Suche nach einer Datenzeile in der Tabelle berücksichtigen. MySQL verfügt über mehrere Zugriffsmethoden, die eine Ergebniszeile finden und zurückgeben können. Diese Zugriffsmethoden müssen möglicherweise auf viele Zeilen zugreifen, um ein Ergebnis zurückzugeben, und einige Zugriffsmethoden geben möglicherweise Ergebnisse ohne Scannen zurück.

Die Typspalte in der EXPLAIN-Anweisung des Ausführungsplans spiegelt den Zugriffstyp wider. Es gibt viele Zugriffsarten, vom vollständigen Tabellenscan bis zum Indexscan, Bereichsscan, eindeutigen Index, konstanten Index usw. Die hier aufgeführten Geschwindigkeiten reichen von langsam bis schnell, und die Anzahl der gescannten Zeilen reicht von hoch bis niedrig.

Wenn die Abfrage den entsprechenden Zugriffstyp nicht finden kann, besteht die beste Lösung normalerweise darin, einen geeigneten Index hinzuzufügen. Dies ist das Indexproblem, das wir zuvor besprochen haben. Jetzt sollte klar sein, warum Indizes für die Abfrageoptimierung so wichtig sind. Mithilfe von Indizes kann MySQL erforderliche Datensätze auf die effizienteste Weise finden und dabei die geringste Anzahl von Zeilen scannen.

Wenn Sie feststellen, dass Ihre Abfrage viele Daten scannt, aber nur wenige Zeilen zurückgibt, können Sie normalerweise die folgenden Techniken zur Optimierung ausprobieren:

• Verwenden Sie einen Index-Covering-Scan, um alle erforderlichen Spalten in den Index einzufügen, sodass die Speicher-Engine die Ergebnisse zurückgeben kann, ohne zur Tabelle zurückkehren zu müssen, um die entsprechenden Zeilen abzurufen.
• Tabellenstruktur optimieren. Verwenden Sie beispielsweise eine separate Übersichtstabelle, um die Abfrage zu vervollständigen.
• Schreiben Sie komplexe Abfragen neu, damit der MySQL-Optimierer die Abfrage optimaler ausführen kann.

Oben sind die Einzelheiten zu den Gründen aufgeführt, warum die MySQL-Abfragegeschwindigkeit langsam ist. Weitere Informationen zur MySQL-Abfragegeschwindigkeit finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!