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.
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.
Der erste Schritt besteht darin, Abfragen zu optimieren, die optimiert werden sollen:
Diese beiden Kategorien müssen während des Datenbankschemadesigns optimiert werden.
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.
Abfragen können entweder auf Anwendungs- oder Datenbankebene erfasst werden.
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.
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 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:
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.
Nachdem Sie die Abfragen in Transaktionen erfasst haben, müssen sie optimiert werden.
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?
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.
Nachdem der Abfrageausführungsplan verstanden wurde, gibt es mehrere Möglichkeiten, ihn zu beeinflussen und zu 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:
Zusammenfassung der Abfrageoptimierung:
Profitieren Sie von einem Guthaben über 300 $, um Google Cloud und mehr als 20 „Immer kostenlos“-Produkte kennenzulernen.