Analysieren der Abfrageleistung in Azure Database for MySQL – Flexibler Server mithilfe von EXPLAIN
EXPLAIN ist ein praktisches Tool, das Sie beim Optimieren von Abfragen unterstützen kann. Mithilfe einer EXPLAIN-Anweisung können Sie Informationen zur Ausführung von SQL-Anweisungen abrufen. Das folgende Beispiel zeigt die Ausgabe der Ausführung einer EXPLAIN-Anweisung.
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 10.00
Extra: Using where
In diesem Beispiel ist der Wert von key NULL. Das bedeutet, dass der flexible Azure Database for MySQL-Server keine für die Abfrage optimierten Indizes finden kann. Daher wird ein vollständiger Tabellenscan durchgeführt. Wir optimieren diese Abfrage, indem wir einen Index für die Spalte ID hinzufügen und dann die EXPLAIN-Anweisung erneut ausführen.
mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: id
key: id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
Nun geht aus der Ausgabe hervor, dass der flexible Azure Database for MySQL-Server einen Index verwendet, um die Anzahl von Zeilen auf 1 zu begrenzen, wodurch sich die Suchzeit erheblich verkürzt.
Abdeckender Index
Ein abdeckender Index umfasst alle Spalten einer Abfrage, wodurch das Abrufen von Werten aus Datentabellen reduziert wird. Die folgende GROUP BY-Anweisung und die zugehörige Ausgabe veranschaulichen dies.
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
Die Ausgabe zeigt, dass der flexible Azure Database for MySQL-Server keine Indizes verwendet, da die richtigen Indizes nicht verfügbar sind. Die Ausgabe zeigt zudem Using temporary; Using filesort. Das gibt an, dass der flexible Azure Database for MySQL-Server eine temporäre Tabelle erstellt, um die GROUP BY-Klausel zu erfüllen.
Das Erstellen eines Index nur für Spalte c2 macht keinen Unterschied, und der flexible Azure Database for MySQL-Server muss trotzdem eine temporäre Tabelle erstellen:
mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
In diesem Fall können Sie einen Index mit vollständiger Abdeckung für c1 und c2 erstellen, indem der Wert von c2 direkt im Index hinzugefügt wird, wodurch eine weitere Datensuche vermieden wird.
mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: covered
key: covered
key_len: 108
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using index
Wie aus der Ausgabe der obigen EXPLAIN-Anweisung hervorgeht, verwendet der flexible Azure Database for MySQL-Server jetzt den Index mit vollständiger Abdeckung und vermeidet, dass eine temporäre Tabelle erstellt werden muss.
Kombinierter Index
Ein kombinierter Index besteht aus Werten aus mehreren Spalten und kann als Array von Zeilen betrachtet werden, die durch Verketten der Werte der indizierten Spalten sortiert werden. Diese Methode kann in einer GROUP BY-Anweisung nützlich sein.
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using filesort
Der flexible Azure Database for MySQL-Server führt eine Dateisortierung aus. Dieser Vorgang ist ziemlich langsam, insbesondere, wenn viele Zeilen sortiert werden müssen. Zur Optimierung dieser Abfrage erstellen Sie einen kombinierten Index für beide Spalten, die sortiert werden.
mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
****************** 1. row ******************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: NULL
key: my_sort2
key_len: 108
ref: NULL
rows: 10
filtered: 11.11
Extra: Using where; Using index
Aus der Ausgabe der EXPLAIN-Anweisung geht nun hervor, dass der flexible Azure Database for MySQL-Server einen kombinierten Index verwendet, um eine zusätzliche Sortierung zu vermeiden, da der Index bereits sortiert ist.
Zusammenfassung
Sie können die Leistung erheblich steigern, indem Sie EXPLAIN zusammen mit verschiedenen Typen von Indizes verwenden. Wenn Sie über einen Index für eine Tabelle verfügen, bedeutet das nicht zwangsläufig, dass der flexible Azure Database for MySQL-Server ihn für Ihre Abfragen verwenden kann. Überprüfen Sie Ihre Annahmen immer mit EXPLAIN, und optimieren Sie Ihre Abfragen mithilfe von Indizes.