Analysieren der Abfrageleistung in Azure Database for MySQL – Flexibler Server mithilfe von EXPLAIN

GILT FÜR:Azure Database for MySQL Single Server Azure Database for MySQL Flexible Server

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.

Nächste Schritte

• Um Antworten anderer Benutzer auf Ihre wichtigsten Fragen zu erhalten oder eine Frage zu veröffentlichen oder zu beantworten, besuchen Sie Stack Overflow.