Verwenden von erklären, um bessere MySQL -Abfragen zu schreiben

Mysql Abfrageoptimierung mit Erklären: Ein tiefes Tauchgang

Wenn Sie eine MySQL -Abfrage ausführen, erstellt der Abfrageoptimierer einen Ausführungsplan. Verwenden Sie den Befehl EXPLAIN, um diesen Plan zu inspizieren. EXPLAIN ist von unschätzbarem Wert, um langsame Abfragen zu verstehen und zu optimieren, doch viele Entwickler unterlegen es. In diesem Artikel wird die Ausgabe von EXPLAIN und seine Anwendung in Schema- und Abfrageoptimierung untersucht.

Key Takeaways:

• Hebel EXPLAIN zur Analyse von Abfrageausführungsplänen, genaue Ineffizienzen und Verbesserung der Leistung.
• entschlüsselt EXPLAIN Ausgangsspalten (z. B. type, possible_keys, key, rows, Extra), um die Abfrageverarbeitung zu verstehen und Bereiche für Verbesserungen zu identifizieren.
• Fügen Sie Tabellen strategisch Indizes zu Tabellen hinzu, die auf Spalten in JOIN oder WHERE Klauseln basieren, um Zeilen -Scans drastisch zu reduzieren, die Geschwindigkeit zu steigern und Lastzeiten zu minimieren.
• Verwenden Sie EXPLAIN EXTENDED und SHOW WARNINGS für detaillierte Einblicke in Abfrage -Transformationen und -ausführung, insbesondere für komplexe Optimierungsaufgaben.
• Überprüfen und optimieren Sie die SQL -Abfragen regelmäßig mit EXPLAIN, um die optimale Datenbankleistung aufrechtzuerhalten, insbesondere in dynamischen Anwendungen mit sich entwickelnden Daten.

Verständnis EXPLAIN 's Ausgabe

Präfix einfach Ihre SELECT Abfrage mit EXPLAIN. Lassen Sie uns ein grundlegendes Beispiel analysieren:

<code class="language-sql">EXPLAIN SELECT * FROM categoriesG;</code>

Ein Beispielausgang könnte so aussehen:

<code>********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)</code>

Diese scheinbar präzise Ausgabe ist reich an Informationen. Die Schlüsselspalten sind:

• id: Sequentielle Kennung für jedes SELECT in der Abfrage (relevant für verschachtelte Unterabfragen).
• select_type: Art der SELECT -Anfrage (einfach, primär, abgeleitet, unterabgeordnet usw.). SIMPLE zeigt eine einfache Abfrage ohne Unterabfragen oder UNION s.
an
• table: Tabelle, auf die in der Zeile verwiesen wird.
• type: Wie MySQL Tabellen verbindet. Entscheidend für die Identifizierung fehlender Indizes oder Bereiche für das Umschreiben von Abfragen. Die Werte reichen von hocheffizienten (system, const, eq_ref) bis ineffizient (ALL, was einen vollständigen Tabellen -Scan angibt).
• possible_keys: Schlüssel, die von MySQL potenziell verwendet werden können. NULL schlägt keine relevanten Indizes vor.
• key: Der tatsächliche Index verwendet. Kann aufgrund der Optimierer -Auswahl von possible_keys abweichen.
• key_len: Länge des gewählten Index.
• ref: Spalten oder Konstanten im Vergleich zum Index in der Spalte key.
• rows: Anzahl der untersuchten Zeilen. Ein hoher Wert zeigt auf potenzielle Optimierungsbedürfnisse, insbesondere mit JOIN s und Unterabfragen.
• Extra: Zusätzliche Informationen (z. B. "mit Temporary" "" mit Dateiort "). Konsultieren Sie die MySQL -Dokumentation für detaillierte Interpretationen.

EXPLAIN EXTENDED liefert weitere Details. Verwenden Sie SHOW WARNINGS anschließend, um Abfragemanien zu sehen, die vom Optimierer durchgeführt werden:

<code class="language-sql">EXPLAIN SELECT * FROM categoriesG;</code>

Fehlerbehebung mit der Leistung mit EXPLAIN

veranschaulichen wir die Optimierung einer schlecht leistungsfähigen Abfrage. Betrachten Sie eine E-Commerce-Datenbank (Schema, die auf Github verfügbar ist), die Indexe fehlen. Eine schlecht geschriebene Frage könnte so aussehen:

<code>********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)</code>

Die Ausgabe EXPLAIN zeigt wahrscheinlich "alle" -Se -Typen, NULL für possible_keys und key und extrem hohe rows -Werte, was einen vollständigen Tabellen -Scan für jede Tabelle anzeigt. Dies ist äußerst ineffizient.

Hinzufügen von Primärschlüssel und Indizes (z. B. auf Spalten, die in JOIN Klauseln verwendet werden), verbessert die Leistung dramatisch. Das Wiederholen der EXPLAIN nach dem Hinzufügen von Indizes zeigt signifikant niedrigere rows -Werte und effizientere Jointypen ("const", "eq_ref").

Ein weiteres Beispiel beinhaltet ein UNION von zwei Tabellen, die jeweils mit productlines:

verbunden sind

<code class="language-sql">EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia';
SHOW WARNINGS;</code>

ohne geeignete Indizes zeigt EXPLAIN vollständige Tabellen -Scans an. Hinzufügen von Indizes und strategisches Platzieren WHERE Bedingungen innerhalb der Abfragen UNION kann die Anzahl der gescannten Zeilen erheblich verringern.

Zusammenfassung

EXPLAIN Ist Ihr Verbündeter in der MySQL -Abfrageoptimierung. Durch die Analyse seiner Ausgabe können Sie Leistungs Engpässe identifizieren und ansprechen, was zu effizienteren und schnelleren Abfragen führt. Denken Sie daran, dass einfaches Hinzufügen von Indizes nicht immer ausreicht. Die Abfragestruktur spielt auch eine wichtige Rolle. Die regelmäßige Verwendung von EXPLAIN ist der Schlüssel zur Aufrechterhaltung der Datenbankgesundheit, insbesondere in dynamischen Anwendungen.

Das obige ist der detaillierte Inhalt vonVerwenden von erklären, um bessere MySQL -Abfragen zu schreiben. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!