Beheben von Problemen mit der Abfrageleistung bei Aurora-MySQL-Datenbanken - Amazon Aurora
Verstehen des Zeitaufwands bei AbfragenÜ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.

Beheben von Problemen mit der Abfrageleistung bei Aurora-MySQL-Datenbanken

MySQL ermöglicht eine Abfrageoptimiererkontrolle über Systemvariablen, die sich auf die Auswertung von Abfrageplänen, umschaltbare Optimierungen, Optimierer- und Indexhinweise sowie das Optimiererkostenmodell auswirken. 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. Dazu gehören der Ausführungsplan, das Schema und die Größe der Tabelle, die Statistiken, die Ressourcen, die Indizes und die 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 geeignete Indizes verwendet. Sie können Ihre Abfrage optimieren, indem Sie EXPLAIN verwenden und die Details der einzelnen Pläne überprüfen.

  • Aurora-MySQL-Version 3 (kompatibel mit MySQL 8.0 Community Edition) verwendet eine EXPLAIN ANALYZE-Anweisung. Die EXPLAIN ANALYZE-Anweisung ist ein Profiling-Tool, das anzeigt, wo MySQL Zeit für Ihre Anfrage aufwendet und warum. Mit EXPLAIN ANALYZE plant Aurora MySQL die Abfrage, bereitet sie vor und führt sie aus, während Zeilen gezählt werden und die an verschiedenen Punkten des Ausführungsplans aufgewendete Zeit gemessen wird. Wenn die Abfrage abgeschlossen ist, druckt EXPLAIN ANALYZE den Plan und die Messwerte anstelle des Abfrageergebnisses.

  • Halten Sie Ihre Schemastatistiken mithilfe der ANALYZE-Anweisung auf dem neuesten Stand. Der Abfrageoptimierer wählt aufgrund veralteter Statistiken manchmal schlechte Ausführungspläne aus. Dies kann aufgrund ungenauer Kardinalitätsschätzungen von Tabellen und Indizes zu einer schlechten Abfrageleistung führen. Die last_update-Spalte der Tabelle innodb_table_stats zeigt an, wann Ihre Schemastatistiken zuletzt aktualisiert wurden. Dies ist ein guter „Alterungs“-Indikator.

  • Weitere Probleme können auftreten, beispielsweise eine ungleiche Datenverteilung, die bei der Tabellenkardinalität nicht berücksichtigt wird. Weitere Informationen finden Sie unter Schätzen der ANALYZE-TABLE-Komplexität für InnoDB-Tabellen und Histogrammstatistiken in MySQL in der MySQL-Dokumentation.

Verstehen des Zeitaufwands bei Abfragen

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

Profilerstellung

Die Profilerstellung ist standardmäßig 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, die die meiste Zeit beansprucht. Gemäß den allgemeinen Thread-Zuständen aus der MySQL-Dokumentation ist das Lesen und Verarbeiten von Zeilen für eine SELECT-Anweisung häufig der am längsten anhaltende 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 Slow-Query-Protokoll an, um rows_examined und rows_sent zu bewerten und sicherzustellen, dass der Workload in jeder Umgebung ähnlich ist. Weitere Informationen finden Sie unter Protokollieren bei 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 alle anderen Vorgänge aus, auch auf 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 vom information_schema, welches Schemainformationen über die DB-Instance bereitstellt. Weitere Informationen finden Sie unter Übersicht über das Leistungsschema für Performance Insights auf Aurora MySQL.

Ablaufverfolgung des Abfrageoptimierers

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

Führen Sie eine Optimierer-Ablaufverfolgung aus, um ausführliche Informationen zu allen Pfaden zu erhalten, die dem Optimierer zur Verfügung stehen, und zu dessen Wahl.

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 Optimierer-bezogene Änderungen auf. Wenn Sie einige benutzerdefinierte Werte für optimizer_switch festgelegt haben, empfehlen wir Ihnen, 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 die Leistung Ihrer Abfragen zu überprüfen.

Anmerkung

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

Mit dem folgenden Befehl können Sie die optimizer_switch-Werte anzeigen:

SELECT @@optimizer_switch\G;

Die folgende Tabelle zeigt die standardmäßigen optimizer_switch-Werte für die Versionen 2 und 3 von Aurora.

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 Umschaltbare Optimierungen (MySQL 5.7) und Umschaltbare Optimierungen (MySQL 8.0) in der MySQL-Dokumentation.