Freigeben über

Profilabfrageleistung in Azure-Datenbank für MySQL – Flexibler Server mithilfe von EXPLAIN

  • Artikel

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

Wichtig

Azure Database for MySQL single server is on the retirement path. Es wird dringend empfohlen, ein Upgrade auf azure Database for MySQL flexiblen Server durchzuführen. Weitere Informationen zum Migrieren zu Azure Database for MySQL flexible Server finden Sie unter Was geschieht mit Azure Database for MySQL Single 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 des Schlüssels NULL, was bedeutet, dass azure Database for MySQL flexible Server keine Indizes finden kann, die für die Abfrage optimiert sind. 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 zeigt die Ausgabe an, dass der flexible Azure-Datenbank für MySQL-Server einen Index verwendet, um die Anzahl der Zeilen auf 1 zu beschränken, wodurch die Suchzeit erheblich verkürzt wird.

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 azure Database for MySQL flexible Server keine Indizes verwendet, da die richtigen Indizes nicht verfügbar sind. Die Ausgabe zeigt auch die Verwendung vorübergehend; Die Verwendung von Filesort, was angibt, dass azure Database for MySQL flexible Server eine temporäre Tabelle erstellt, um die GROUP BY-Klausel zu erfüllen.

Das Erstellen eines Indexes nur in Spalte c2 macht keinen Unterschied, und azure Database for MySQL flexible Server muss weiterhin 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 die Ausgabe der obigen ERKLÄRUNG zeigt, verwendet azure Database for MySQL flexible Server jetzt den abgedeckten Index und vermeidet das Erstellen einer temporären Tabelle.

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-Datenbank für MySQL-Server führt einen Dateisortierungsvorgang aus, der recht langsam ist, 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

Die Ausgabe der EXPLAIN-Anweisung zeigt nun, dass azure Database for MySQL flexible Server einen kombinierten Index verwendet, um 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. Die Verwendung eines Indexes für eine Tabelle bedeutet nicht unbedingt, dass azure Database for MySQL flexible Server sie 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.