Fehlerbehebung bei der Abfrageleistung für Aurora MySQL-Datenbanken - Amazon Aurora
Den Zeitaufwand für Abfragen verstehenÜberprüfen der Einstellungen des Abfrageoptimierers

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Fehlerbehebung bei der Abfrageleistung für Aurora MySQL-Datenbanken

MySQL ermöglicht die Steuerung des Abfrageoptimierers durch Systemvariablen, die sich darauf auswirken, wie Abfragepläne ausgewertet werden, umschaltbare Optimierungen, Optimizer- und Indexhinweise sowie das Optimierer-Kostenmodell. Diese Datenpunkte können nicht nur beim Vergleich verschiedener MySQL-Umgebungen hilfreich sein, sondern auch, um frühere Abfrageausführungspläne mit aktuellen Ausführungsplänen zu vergleichen und die Gesamtausführung einer MySQL-Abfrage zu jedem Zeitpunkt zu verstehen.

Die Abfrageleistung hängt von vielen Faktoren ab, darunter dem Ausführungsplan, dem Tabellenschema und der Größe, Statistiken, Ressourcen, Indizes und der Parameterkonfiguration. Die Abfrageoptimierung erfordert die Identifizierung von Engpässen und die Optimierung des Ausführungspfads.

  • Suchen Sie den Ausführungsplan für die Abfrage und überprüfen Sie, ob die Abfrage die entsprechenden Indizes verwendet. Sie können Ihre Abfrage optimieren, indem Sie die Details der einzelnen Pläne verwenden EXPLAIN und überprüfen.

  • Aurora MySQL Version 3 (kompatibel mit MySQL 8.0 Community Edition) verwendet eine EXPLAIN ANALYZE Anweisung. Die EXPLAIN ANALYZE Anweisung ist ein Profilierungstool, das zeigt, wo MySQL Zeit für Ihre Anfrage aufwendet und warum. MitEXPLAIN ANALYZE, Aurora MySQL plant, bereitet und führt die Abfrage aus, während gleichzeitig Zeilen gezählt und die Zeit gemessen wird, die an verschiedenen Punkten des Ausführungsplans verbracht wurde. Wenn die Abfrage abgeschlossen ist, werden der Plan und seine Messungen anstelle des Abfrageergebnisses EXPLAIN ANALYZE gedruckt.

  • Halten Sie Ihre Schemastatistiken mithilfe der ANALYZE Anweisung auf dem neuesten Stand. Der Abfrageoptimierer kann aufgrund veralteter Statistiken manchmal schlechte Ausführungspläne auswählen. Dies kann aufgrund ungenauer Kardinalitätsschätzungen von Tabellen und Indizes zu einer schlechten Leistung einer Abfrage führen. last_updateIn der Spalte der Tabelle innodb_table_stats wird angezeigt, wann Ihre Schemastatistiken zuletzt aktualisiert wurden. Dies ist ein guter Indikator für „Veraldung“.

  • Andere Probleme können auftreten, wie z. B. eine schiefe Verteilung von Daten, die bei der Tabellenkardinalität nicht berücksichtigt werden. Weitere Informationen finden Sie unter Schätzung der ANALYZE TABLE-Komplexität für InnoDB-Tabellen und Histogrammstatistiken in MySQL in der MySQL-Dokumentation.

Den Zeitaufwand für Abfragen verstehen

Es gibt folgende Möglichkeiten, den Zeitaufwand für Abfragen zu ermitteln:

Profilerstellung

Standardmäßig ist die Profilerstellung deaktiviert. Aktivieren Sie die Profilerstellung, führen Sie dann die langsame Abfrage aus und überprüfen Sie das zugehörige Profil.

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. Identifizieren Sie die Phase, in der die meiste Zeit verbracht wird. Gemäß den allgemeinen Thread-Zuständen in der MySQL-Dokumentation ist das Lesen und Verarbeiten von Zeilen für eine SELECT Anweisung oft der am längsten laufende Zustand während der Lebensdauer einer bestimmten Abfrage. Sie können die EXPLAIN Anweisung verwenden, um zu verstehen, wie MySQL diese Abfrage ausführt.

  2. Sehen Sie sich das Protokoll für langsame Abfragen anrows_sent, um eine Bewertung vorzunehmen rows_examined und sicherzustellen, dass die Arbeitslast in jeder Umgebung ähnlich ist. Weitere Informationen finden Sie unter Protokollierung für Aurora MySQL-Datenbanken.

  3. Führen Sie den folgenden Befehl für Tabellen aus, die Teil der identifizierten Abfrage sind:

    SHOW TABLE STATUS\G;

  4. Erfassen Sie die folgenden Ausgaben vor und nach der Ausführung der Abfrage in jeder Umgebung:

    SHOW GLOBAL STATUS;

  5. Führen Sie die folgenden Befehle in jeder Umgebung aus, um festzustellen, ob andere Abfragen/Sitzungen die Leistung dieser Beispielabfrage beeinflussen.

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    Wenn die Ressourcen auf dem Server ausgelastet sind, wirkt sich dies manchmal auf jeden anderen Vorgang auf dem Server aus, einschließlich Abfragen. Sie können auch regelmäßig Informationen erfassen, wenn Abfragen ausgeführt werden, oder einen cron Job einrichten, um Informationen in sinnvollen Intervallen zu erfassen.

Leistungsschema

Das Leistungsschema bietet nützliche Informationen über die Leistung der Serverlaufzeit und hat gleichzeitig nur minimale Auswirkungen auf diese Leistung. Dies unterscheidet sich von derinformation_schema, die Schemainformationen über die DB-Instance bereitstellt. Weitere Informationen finden Sie unter Überblick über das Leistungsschema für Performance Insights auf Aurora My SQL My SQL.

Den Trace des Optimizers abfragen

Um zu verstehen, warum ein bestimmter Abfrageplan für die Ausführung ausgewählt wurde, können Sie den optimizer_trace Zugriff auf den MySQL-Abfrageoptimierer einrichten.

Führen Sie einen Optimierer-Trace aus, um ausführliche Informationen zu allen Pfaden anzuzeigen, die dem Optimierer zur Verfügung stehen, und zu seiner Auswahl.

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

Überprüfen der Einstellungen des Abfrageoptimierers

Aurora MySQL Version 3 (kompatibel mit MySQL 8.0 Community Edition) weist im Vergleich zu Aurora MySQL Version 2 (kompatibel mit MySQL 5.7 Community Edition) viele Optimierungsänderungen auf. Wenn Sie einige benutzerdefinierte Werte für haben, empfehlen wir Ihnenoptimizer_switch, die Unterschiede in den Standardeinstellungen zu überprüfen und optimizer_switch Werte festzulegen, die für Ihren Workload am besten geeignet sind. Wir empfehlen Ihnen außerdem, die für Aurora MySQL Version 3 verfügbaren Optionen zu testen, um zu überprüfen, wie Ihre Abfragen funktionieren.

Anmerkung

Aurora MySQL Version 3 verwendet den Community-Standardwert 20 für den Parameter innodb_stats_persistent_sample_pages.

Sie können den folgenden Befehl verwenden, um die Werte optimizer_switch anzuzeigen:

SELECT @@optimizer_switch\G;

Die folgende Tabelle zeigt die optimizer_switch Standardwerte für Aurora MySQL Versionen 2 und 3.

Einstellung Aurora-MySQL-Version 2 Aurora-MySQL-Version 3
batched_key_access aus aus
block_nested_loop on on
condition_fanout_filter on on
derived_condition_pushdown – on
derived_merge on on
duplicateweedout on on
engine_condition_pushdown on on
firstmatch on on
hash_join aus on
hash_join_cost_based on –
hypergraph_optimizer – aus
index_condition_pushdown on on
index_merge on on
index_merge_intersection on on
index_merge_sort_union on on
index_merge_union on on
loosescan on on
materialization on on
mrr on on
mrr_cost_based on on
prefer_ordering_index on on
semijoin on on
skip_scan – on
subquery_materialization_cost_based on on
subquery_to_derived – aus
use_index_extensions on on
use_invisible_indexes – aus

Weitere Informationen finden Sie unter Switchable Optimizations (MySQL 5.7) und Switchable Optimizations (MySQL 8.0) in der MySQL-Dokumentation.