MySQL-Leistung optimal nutzen: Abfrageoptimierung
Datenbanken spielen bei der Leistung von Anwendungen eine wichtige Rolle. MySQL-Datenbank ist keine Ausnahme. Daher ist es wichtig, die zahlreichen Möglichkeiten zu verstehen, wie die Feinabstimmung, das Design und die Konfiguration der Datenbank die Leistung Ihrer Anwendung verbessern können. Im Folgenden finden Sie einige Möglichkeiten, wie MySQL für eine optimale Leistung optimiert werden kann.
Überblick
Abfrageoptimierung
Die Optimierung der Datenbankleistung beginnt in der Anwendung. Die Art und Weise, wie die Anwendung die Geschäftsanforderungen in Datenbankabfragen umwandelt, macht die Anwendung sehr komplex und effizienter. Der tatsächliche Leistungsmesswert ist, wie effizient jede Datenbankinstanz zu den Geschäftsanforderungen beiträgt.
Schemadesign
Wie Entitäten und Beziehungen in einer relationalen Datenbank definiert werden, bestimmt, wie einfach oder komplex eine Antwort auf eine Datenbankabfrage sein wird. Außerdem spielt die Definition des Primärschlüssels und der sekundären Indexe eine wichtige Rolle.
Serverkonfiguration
Die Serverkonfiguration ist für die Optimierung und Maximierung der Auslastung von Systemressourcen verantwortlich. Die Systemressourcen sind die Kerne der CPU (Core Processing Unit), der Arbeitsspeicher auf der physischen Maschine oder virtuellen Maschine (VM), das zugrunde liegende Speichersystem und das Netzwerk.
Dynamische Serveroptimierung
Kontinuierliche Monitorings, Optimierungen und Leistungsoptimierungen, um Datenbankarbeitslasten an die reale Dynamik anzupassen
In diesem Artikel geht es um die Feinabstimmung von Abfragen. Die restlichen Themen werden in den folgenden Artikeln behandelt.
Wir beginnen die Abfrageabstimmung oft mit der Datenbankabfrage. Eine bessere Möglichkeit wäre, mit der Bewertung zu beginnen, wie effizient die Geschäftsanforderungen in eine Datenbankabfrage umgewandelt wurden. Die Interpretation und Verarbeitung eines Geschäftsbedarfs für eine Abfrage bestimmt, wie klein oder groß die Kosten sein würden.
Abfragen entwerfen
Der erste Schritt besteht darin, Abfragen zu optimieren, die optimiert werden sollen:
- Datenbankfragen ermitteln, die die beste Reaktionszeit erfordern
- Datenbankfragen ermitteln, die häufig ausgeführt werden
Diese beiden Kategorien müssen während des Datenbankschemadesigns optimiert werden.
Kurze Transaktionen
Eine Transaktion ist eine logische Einheit, in der alle enthaltenen Anweisungen entweder vollständig übergeben werden oder ein Rollback durchgeführt wird. Transaktion ist das Feature, das für Atomarität, Konsistenz, Isolation und Langlebigkeit (ACID) für MySQL sorgt.
In InnoDB, der Speicher-Engine für MySQL, finden alle Nutzeraktivitäten innerhalb einer Transaktion statt. Standardmäßig ist der Autocommit-Modus aktiviert, d. h., jede SQL-Anweisung bildet eine einzelne Transaktion. Wenn eine Autocommit-Funktion aktiviert ist, führen Sie eine Transaktion mit mehreren Anweisungen explizit mit START_TRANSACTION oder BEGIN aus und beenden sie mit COMMIT oder ROLLBACK. Wenn der Autocommit-Modus deaktiviert ist, wird eine Transaktion geöffnet, bis ein COMMIT oder ROLLBACK beendet und eine neue gestartet wird.
Als Best Practice empfehlen wir, Transaktionen möglichst kurz zu halten. Das liegt daran, dass lange Transaktionen mehrere Nachteile haben, wie in diesem Artikel beschrieben.
Nachteile langer Transaktionen
- Langfristige Sperrungskonflikte, die zu langsameren Abfragen und potenziellen Abfragefehlern führen
- Das Sperren auf InnoDB-Zeilenebene wird für die Dauer der Transaktion beibehalten
- Dadurch werden potenziell Wartezeiten für Sperren, Zeitüberschreitungen bei Sperren und Deadlocks erhöht, wodurch Abfragen entweder langsamer werden oder vollständig fehlschlagen.
- Verminderte Serverleistung aufgrund einer großen Anzahl von Rückgängig-Logs
- Aufgrund der InnoDB Multi-version Concurrency Control (MVCC) werden alte Versionen geänderter Zeilen in Rückgängig-Logs gespeichert, um ein konsistentes Lesen und Rollback zu ermöglichen. Bei der standardmäßigen Isolationsebene für wiederholbare Lesevorgänge werden die Rückgängig-Logs erst gelöscht, wenn die Transaktionen vor ihrem Abschluss gestartet wurden. Daher werden bei einer lang andauernden Transaktion rückgängig gemachte Logs erfasst. Dies kann über die Verlaufsliste in der Befehlszeilenfunktion SHOW ENGINE INNODB STATUS beobachtet und überwacht werden.
- Wenn die Verlaufsliste mehr als Millionen Nutzer annimmt, würde dies die Serverleistung aufgrund der Mutex-Konflikte bei Rollback-Segmenten, dem erhöhten Volumen zum Lesen der Rückgängig-Logs und der längeren Durchlaufdauer der verknüpften Liste der rückgängig gemachten Logs beeinträchtigen. Außerdem wird das Löschen von Threads damit aufwendiger.
- Erhöhte Laufwerksnutzung
- Erhöhung der rückgängig gemachten Logs, die auf der Festplatte gespeichert sind, entweder im System-Tablespace oder beim Rückgängigmachen von Tablespace
- Langsames Herunterfahren
- Beim normalen Herunterfahren werden laufende Transaktionen zurückgesetzt. Die Rollback-Zeit ist häufig länger als die tatsächlich benötigte Zeit. Daher kann das Herunterfahren des Servers lange dauern.
- Langsame Wiederherstellung nach einem Absturz
- Während der Absturzwiederherstellung wiederholt InnoDB die Transaktionen ab dem letzten Checkpoint und sorgt für das Rollback von Transaktionen ohne Commit. Bei einer langen Transaktion würde der entsprechende Schritt länger dauern.
Kaufbereitschaft für Transaktionen mit einer Abfrage
- Abfragen AUSWÄHLEN
- Zeilensperren sind nicht möglich.
- Das kann dazu führen, dass Protokolle rückgängig gemacht werden
- Weitere Informationen finden Sie im Abschnitt zur Abfrageoptimierung unten.
- AbfragenAKTUALISIEREN/EINFÜGEN/LÖSCHEN
- Die Batchabfrage funktioniert besser als viele Änderungen an einer einzelnen Zeile
- Die Batch-Ausführungszeit unterbrechen und auf einige Sekunden begrenzen
Kaufbereitschaft für Transaktionen mit mehreren Anweisungen
- Es empfiehlt sich, Abfragen AUSWÄHLEN voneinander zu trennen.
- Wenn Anwendungslogik zwischen den Datenbankabfragen vorhanden ist, sollten Sie die Transaktion aufteilen
- Schätzung der Anzahl von Zeilensperren für jede Anweisung
- Ausführungsreihenfolge bewerten, um Zeilensperre zu minimieren
- Nach Möglichkeiten suchen, die Transaktionsgröße zu verringern
Abfragen erfassen
Abfragen können entweder auf Anwendungs- oder Datenbankebene erfasst werden.
Anwendungsseite
Es empfiehlt sich, Datenbankabfragen und die Ausführungszeit von Abfragen zu protokollieren. Mit dem anwendungsseitigen Logging können Sie die Effektivität und Effizienz der Abfragen im geschäftlichen Kontext ganz einfach bewerten. Beispielsweise können Nutzer die Antwortzeit jeder Abfrage oder die Antwortzeit für bestimmte Funktionen protokollieren. Dies ist auch eine einfache Möglichkeit, die Gesamtausführungszeit für Transaktionen mit mehreren Anweisungen zu ermitteln.
Darüber hinaus ist die Antwortzeit der Abfrage, die vom Logging auf Anwendungsebene gemessen wird, eine End-to-End-Messung, einschließlich der Netzwerkzeit. Sie ergänzt die Ausführungszeit für Abfragen aus der Datenbank und erleichtert die Identifizierung des Problems mit dem Netzwerk oder der Datenbank.
Datenbankseite
MySQL-Abfragestatistiken
Das Cloud SQL Query Insights-Tool ermöglicht die Erfassung, das Monitoring und die Diagnose von Abfragen.
Mit Query Insights können Sie die häufigsten Abfragen basierend auf der Ausführungszeit und der Ausführungshäufigkeit leicht finden.
Das Tool bietet Filteroptionen wie Zeitraum, Datenbank, Nutzerkonto und Clientadresse. Sie bietet Grafiken zur Darstellung der CPU-Nutzung sowie eine Aufschlüsselung der E/A- und Sperrwartezeiten. In der Tabelle „Top-Abfragen und -Tags“ werden die Top-Abfragen nach Ausführungszeit aufgelistet und die Abfragen werden normalisiert. Neben der Ausführungszeit enthält sie Statistiken zu den "gescannten durchschnittlichen Zeilen" und den "zurückgegebenen durchschnittlichen Zeilen", die Informationen zur Abfrageeffizienz liefern.
Weitere Informationen finden Sie in der Dokumentation zur Aktivierung.
Leistungsschema verwenden
In Cloud SQL for MySQL ist das Feature performance_schema für MySQL 8.0.26 und höher mit mindestens 15 GB Arbeitsspeicher standardmäßig aktiviert. Wenn Sie sie aktivieren oder deaktivieren, muss die Instanz neu gestartet werden.
Bei performance_schema=ON sind die Instrumente für Abfrageanweisungen standardmäßig aktiviert. Die Tabelle sys.statement_analysis liefert zusammengefasste Statistiken für normalisierte Abfragen. Es werden unter anderem folgende Fragen beantwortet:
- Bei welchen Abfragen wird die Tabelle vollständig gescannt?
- full_scan/exec_count: Ermittelt, ob Abfragen häufig einen vollständigen Tabellenscan ausführen, was häufig ineffizient ist
- Welche Abfragen werden langsam ausgeführt?
- avg_latenz: durchschnittliche Ausführungszeit der Abfrage
- Welche Abfragen sind ineffizient?
- rows_examined_avg/rows_sent_avg: Dies ist für Leseabfragen. Das ideale Verhältnis ist 1. Je größer das Verhältnis ist, desto ineffizienter ist die Abfrage.
- rows_examined_avg/rows_impacted_avg: Dies ist für Schreibabfragen vorgesehen. Das ideale Verhältnis ist 1. Je größer das Verhältnis ist, desto ineffizienter ist die Abfrage.
- Welche Abfragen verwenden temporäre Tabellen und müssen in temporäre Tabellen auf dem Laufwerk konvertiert werden?
- tmp_disk_tables/tmp_tables: gibt an, ob tmp_table_size/max_heap_table_size ausreicht
- Bei welchen Abfragen wird filesort verwendet?
- rows_sorted/exec_count, sort_merge_passes/exec_count: zur Identifizierung von Abfragen mit vielen Sortierungen und nutzt möglicherweise einen größeren sort_buffer_size
Wenn Sie MySQL Workbench verwenden, finden Sie hier Leistungsberichtschemas basierend auf der Systemansicht. Der Bericht enthält einen Abschnitt zu SQL-Anweisungen für hohe Kosten, der Informationen zur Abfrageleistung bietet.
Langsames Protokoll und Tools verwenden
Das langsame Log erfasst alle Abfragen, die länger als die long_query_time ausgeführt werden. Es protokolliert außerdem die Ausführungszeit, die Sperrzeit, die untersuchten Datenzeilen und die gesendeten Datenzeilen. Aufgrund der zusätzlichen Ausführungsstatistik werden Datenbankabfragen bevorzugter verwendet als das allgemeine Log.
Es empfiehlt sich, langsames Log zu aktivieren. Normalerweise sollte die long_query_time auf einem angemessenen Grenzwert bleiben, um Abfragen zu erfassen, die Sie ansehen und optimieren möchten.
log_output=FILE
slow_query_log=ON
long_query_time=2
Ab und zu sollten Sie long_query_time=0 festlegen, um für einen kurzen Zeitraum alle Abfragen zu erfassen und einen Überblick über das Abfragevolumen und die Leistung zu erhalten.
Es gibt Tools wie mysqldumpslow und pt-query-digest, mit denen Abfragesignaturen extrahiert und ein Bericht erstellt werden kann, um Abfragestatistiken anzeigen.
Es gibt weitere Monitoringtools von Drittanbietern, die Berichte zu Abfragestatistiken generieren, z. B.Percona Monitoring und Management , SolarWinds Datenbankleistungsüberwachung (früher VividCortex) und mehr.
Abfragen optimieren
Nachdem Sie die Abfragen in Transaktionen erfasst haben, müssen sie optimiert werden.
ERKLÄRUNG – worauf Sie achten müssen
Der Befehl „EXPLAIN“ bietet einen Abfrageausführungsplan. Ab 8.0.18 würde der Befehl „EXPLAIN ANALYZE“ eine Anweisung ausführen und die EXPLAIN-Ausgabe zusammen mit dem Timing aus der Ausführung generieren.
Die MySQL-Abfragestatistiken bieten praktischen Zugriff auf den EXPLAIN-Plan.
Worauf achten wir bei der Ausgabe?
- Im Feld „Zeilen“ sehen Sie die Anzahl der Zeilen, die gelesen werden sollen
- E/A ist der zeitaufwendigste Teil. Wenn eine Abfrage große Datenmengen lesen muss, ist sie wahrscheinlich langsam. Um eine ungefähre Vorstellung davon zu erhalten, multiplizieren Sie die „Zeilen“ unter den verknüpften Tabellen. Im Beispiel oben ist das 858 * 23523. 23.523 Zeilen aus t2 für jede der 858 Zeilen aus t1 zu lesen, ist nicht optimal. Daher würde die Optimierung die Menge des Datenzugriffs von t2 für jede Iteration reduzieren.
- Das Feld „Typ“ beschreibt den Join-Typ der Tabelle
- Der Typ „Index“ bedeutet, dass der Index gescannt wird. Wenn der Index alle Daten aus der Tabelle erfüllt, wird im Feld „Extra“ der Wert „Verwendet Index“ angezeigt.
- Der Typ „Bereich“ bedeutet, dass nicht nur ein Index verwendet wird, sondern auch, dass eine Bereichsbedingung angegeben ist, um den Datenscan zu beschränken.
- Bei nachfolgenden Tabellen in der Join-Reihenfolge bedeutet der Typ „eq_ref“, dass für jede Kombination aus Zeilen aus den vorherigen Tabellen eine Zeile aus dieser Tabelle gelesen wird. Dies ist die effizienteste.
- Der Typ „ref“ bedeutet, dass der Indexabgleich 1:m statt 1:1 ist. Für jede Kombination von Zeilen aus den vorherigen Tabellen werden mehr als eine Zeile aus dieser Tabelle gelesen.
- Der zu vermeidende Typ ist „ALLE“. Das bedeutet, dass für jede Kombination von Zeilen aus den vorherigen Tabellen ein kompletter Tabellenscan durchgeführt wird.
- Im Feld „Schlüssel“ wird der tatsächlich verwendete Index angezeigt.
- Die Auswahl des zu verwendenden Index basiert auf der Indexkardinalität, die möglicherweise veraltet ist. Daher muss geprüft werden, ob der selektivste Index verwendet wird.
- Das Feld „key_len“ gibt die Schlüssellänge in Byte an.
- Bei einem mehrspaltigen Index schlägt „key_len“ den verwendeten Teil des Index vor. Wenn ein Index beispielsweise (col1, col2, col3) und die Abfragebedingung „col1 = n und col2 wie '%string%'“ hat, wird nur col1 für Indexfilterung verwendet. Wenn die Abfrage zu „col1 = n“ und „col2“ wie „string%“ geändert werden kann, werden beide (col1, col2) für die Indexfilterung verwendet. Diese kleine Änderung kann einen wesentlichen Unterschied bei der Abfrageleistung ausmachen.
- Das Feld „Extra“ enthält zusätzliche Informationen zum Abfrageplan
- „Temporär verwenden“ bedeutet, dass eine interne temporäre Tabelle erstellt wird, durch die eine temporäre Tabelle auf dem Laufwerk generiert werden kann
- „Mit Dateisortierung“ bedeutet, dass die Sortierung keinen Index nutzen konnte, erfordert einen Sortierpuffer und möglicherweise temporäre Laufwerksdateien
- „Verwendet Index“ bedeutet, dass alle aus dieser Tabelle erforderlichen Daten im Index enthalten sind. keine Notwendigkeit, Datenzeilen zu lesen
Abfrageprofil
Sitzungsstatusvariablen können zum Abrufen von Details zur Abfrageausführung verwendet werden.
Löschen Sie zuerst die Sitzungsvariablen, führen Sie dann die Abfrage aus und prüfen Sie die Zähler. Der Status „Handler_*“ zeigt beispielsweise das Datenzugriffsmuster und den Zeilenbetrag an. „Created_*“ wird angezeigt, wenn eine temporäre Tabelle und/oder eine temporäre Tabelle auf dem Laufwerk erstellt wird. „Sort_*“ würde die Anzahl der sortierten Zusammenführungen und die Anzahl der sortierten Zeilen anzeigen. Weitere Sitzungsvariablen werden in der Dokumentation erläutert.
Die SHOW PROFILE-Anweisung gibt die Ausführungszeit der Abfragen nach Ausführungsphase an. Dies kann auch hilfreich sein.
Abfrageausführungsplan optimieren
Nachdem der Abfrageausführungsplan verstanden wurde, gibt es mehrere Möglichkeiten, ihn zu beeinflussen und zu optimieren.
- Indexdefinition hinzufügen oder aktualisieren
- Weniger Datenzugriff für bessere Filterung
- Dateisortierung vermeiden
- Indexstatistiken aktualisieren, falls deaktiviert
- TABELLE ANALYSE <tbl>;
- Prüfen Sie die EXPLAIN-Planausgabe noch einmal.
- Indexhinweis verwenden
- Um einen bestimmten Index vorzuschlagen oder zu erzwingen, dass er verwendet wird für Filtern, Verknüpfen oder Ordnen nach/Gruppieren nach
- Mit STRAIGHT_JOIN die Reihenfolge der Tabellenverknüpfungen definieren
- Optimierer-Hinweise verwenden
Ausführung für die Sitzung optimieren
Zur Optimierung der Serverkonfiguration für bestimmte Abfragen wird dringend empfohlen, die Variablen auf Sitzungsebene zu verwenden, anstatt den globalen Wert zu ändern, der sich auf alle Sitzungen auswirkt.
Die häufig verwendeten Sitzungswerte sind:
Fazit
Zusammenfassung der Abfrageoptimierung:
- Treffen Sie fundierte Entscheidungen beim Erstellen der Abfragen. Diese Entscheidungen sind der ausschlaggebende Faktor für die Abfrageleistung, den Gesamtserverdurchsatz und die Serverleistung.
- Verfolgen Sie die Daten zur Abfrageausführung auf Anwendungs- und Datenbankseite. Das anwendungsseitige Logging ist wichtig. Es kann basierend auf den geschäftlichen Interessen konfiguriert werden und den Geschäftsbetrieb widerspiegeln.
- Schließlich gibt es noch mehrere Tools, die Ihnen dabei helfen, den Abfrageausführungsplan, die mit verschiedenen Schritten verbundenen Kosten und die Möglichkeiten zur Optimierung der Abfragen zu verstehen.
Ähnliche Produkte und Dienste
Google Cloud bietet eine verwaltete MySQL-Datenbank, die auf Ihre geschäftlichen Anforderungen zugeschnitten ist – von der Stilllegung Ihres lokalen Rechenzentrums über die Ausführung von SaaS-Anwendungen bis hin zur Migration von Kerngeschäftssystemen.
