Der erste Schritt besteht darin, Abfragen zu optimieren, die optimiert werden sollen:

1. Datenbankfragen ermitteln, die die beste Reaktionszeit erfordern
2. Datenbankfragen ermitteln, die häufig ausgeführt werden

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 START_TRANSACTION aus und beenden sie mit START_TRANSACTION oder START_TRANSACTION. Wenn der Autocommit-Modus deaktiviert ist, wird eine Transaktion geöffnet, bis ein COMMIT oder COMMIT 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.

1. 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.

2. 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.

3. 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

4. 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.

5. 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.

• 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
• Batch-Ausführungszeit auf wenige Sekunden herunterbrechen und beschränken.

• 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 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.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_latency: durchschnittliche Ausführungszeit der Abfrage
• Welche Abfragen sind ineffizient?
• rows_examined_avg/rows_examined_avg: für Leseabfragen. Das ideale Verhältnis ist 1. Je größer das Verhältnis, desto ineffizienter die Abfrage. 
• rows_examined_avg/rows_examined_avg: für Schreibabfragen. Das ideale Verhältnis ist 1. Je größer das Verhältnis, desto ineffizienter die Abfrage. 
• Welche Abfragen verwenden temporäre Tabellen und müssen in temporäre Tabellen auf dem Laufwerk konvertiert werden?
• tmp_disk_tables/tmp_disk_tables: gibt an, ob tmp_disk_tables/tmp_disk_tables ausreicht
• Bei welchen Abfragen wird filesort verwendet?
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: zur Identifizierung von Abfragen mit vielen Sortierungen und nutzt möglicherweise einen größeren rows_sorted

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?

• 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

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. 

• 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

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: 

• 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.