SQL-Optimierungsfähigkeiten, die normale Programmierer beherrschen müssen

Ob bei der Arbeit oder in einem Vorstellungsgespräch, Sie müssen grundsätzlich einige Fähigkeiten zur SQL-Optimierung beherrschen, z. B. die Verwendung von EXPLAIN zum Anzeigen des Ausführungsplans von SQL und die anschließende Optimierung von SQL gemäß dem Ausführungsplan.

Der Einsatz von EXPLAIN und die Analyse verwandter Felder gehören mittlerweile grundsätzlich zum Standard für Programmierer.

Nein, bitte lesen Sie es sorgfältig durch.

1. Was ist ein MySQL-Ausführungsplan? Um den Ausführungsplan besser zu verstehen, müssen Sie über ein einfaches Verständnis der Grundstruktur von MySQL und der Grundprinzipien der Abfrage verfügen.

Die funktionale Architektur von MySQL selbst ist in drei Teile unterteilt, nämlich die Anwendungsschicht, die logische Schicht und die physische Schicht. Nicht nur MySQL, sondern die meisten anderen Datenbankprodukte sind entsprechend dieser Architektur unterteilt.

Die Anwendungsschicht ist hauptsächlich für die Interaktion mit dem Client, den Aufbau von Links, die Speicherung des Linkstatus, die Rückgabe von Daten und die Beantwortung von Anfragen verantwortlich.
Die Logikschicht ist hauptsächlich für die Abfrageverarbeitung, das Transaktionsmanagement und die Verarbeitung anderer Datenbankfunktionen verantwortlich, am Beispiel von Abfragen.
Nach dem ersten Empfang der SQL-Abfrage weist die Datenbank sofort einen Thread zu, um sie zu verarbeiten. Im ersten Schritt optimiert der Abfrageprozessor die SQL-Abfrage und erstellt sie anschließend der Planausführende für die Ausführung.

Der Planausführer muss auf den untergeordneten Transaktionsmanager und den Speichermanager zugreifen, um Daten zu verwalten. Ihre jeweiligen Arbeitsteilungen sind unterschiedlich. Schließlich werden die Abfragestrukturinformationen durch Aufrufen der Datei der physischen Schicht und des Endergebnisses abgerufen wird auf die Anwendungsschicht geantwortet.

Die physische Schicht, die auf der tatsächlichen physischen Festplatte gespeicherten Dateien, umfasst hauptsächlich Penny-Datendateien und Protokolldateien.
Anhand der obigen Beschreibung ist die Erstellung eines Ausführungsplans ein wesentlicher Schritt für die Ausführung einer SQL. Die Leistung einer SQL lässt sich intuitiv anhand des Ausführungsplans erkennen Überprüfen Sie es und nutzen Sie es als Grundlage für die Leistungsanalyse.

2. So analysieren Sie den AusführungsplanMySQL stellt uns das Schlüsselwort EXPLAIN zur Verfügung, um den Ausführungsplan einer SQL visuell anzuzeigen.

explain zeigt, wie MySQL Indizes verwendet, um Select-Anweisungen zu verarbeiten und Tabellen zu verbinden, was dabei helfen kann, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben.

Im Folgenden verwenden wir „explain“, um eine Abfrage wie folgt zu erstellen:

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Das Verständnis der Bedeutung jeder Spalte ist entscheidend für das Verständnis des Ausführungsplans .

System: Die Tabelle hat nur eine Zeile (=Systemtabelle). Dies ist ein Sonderfall des const-Join-Typs. : Lesen Sie für jede Kombination von Zeilen aus der vorherigen Tabelle eine Zeile aus dieser Tabelle. Dies ist neben const-Typen wahrscheinlich der beste Join-Typ. ref: Dieser Join-Typ ähnelt ref, fügt jedoch MySQL hinzu, um speziell nach Zeilen zu suchen, die NULL-Werte enthalten. index_merge: Dieser Join-Typ gibt an, dass die Index-Merge-Optimierungsmethode verwendet wird. Distinct: ​​​​Nachdem MySQL die erste passende Zeile gefunden hat, beendet es die Suche nach weiteren Zeilen für die aktuelle Zeilenkombination.

Spaltenname	Beschreibung
id	SELECT-Kennung, die die Abfragesequenznummer von SELECT ist.
select_type	SELECT-Typ, der einer der folgenden sein kann: SIMPLE: einfache SELECT (verwendet weder UNION noch Unterabfrage) PRIMARY: äußerste SELECT UNION: zweite in UNION oder die folgende SELECT-Anweisung: DEPENDENT UNION: Die zweite oder folgende SELECT-Anweisung in UNION, abhängig von der externen Abfrage Unterabfrage, hängt von der äußeren Abfrage ab. DERIVED: SELECT der abgeleiteten Tabelle (Unterabfrage der FROM-Klausel) Partitionen, auf die die Abfrage zugreift, wenn die Abfrage auf einer partitionierten Tabelle basiert. TypJoin-Typ. Die verschiedenen Join-Typen sind unten aufgeführt, sortiert vom besten zum schlechtesten:
const: Die Tabelle hat höchstens eine passende Zeile, die zu Beginn der Abfrage gelesen wird. Da es nur eine Zeile gibt, können die Spaltenwerte in dieser Zeile vom Rest des Optimierers als Konstanten behandelt werden. const-Tabellen sind schnell, weil sie nur einmal gelesen werden!	eq_ref
: Für jede Zeilenkombination aus der vorherigen Tabelle werden alle Zeilen mit übereinstimmenden Indexwerten aus dieser Tabelle gelesen. ref_or_null
unique_subquery: Dieser Typ ersetzt die Referenz der IN-Unterabfrage in der folgenden Form: value IN (SELECT Primary_key FROM single_table WHERE some_expr) unique_subquery ist eine Indexsuchfunktion, die die Unterabfrage vollständig ersetzen kann und effizienter ist. index_subquery: Dieser Join-Typ ähnelt unique_subquery. IN-Unterabfragen können ersetzt werden, aber nur für nicht eindeutige Indizes in Unterabfragen der Form: value IN (SELECT key_column FROM single_table WHERE some_expr) range: Rufen Sie nur Zeilen in einem bestimmten Bereich ab, indem Sie einen Index verwenden, um Zeilen auszuwählen ( Empfehlung, das Schlimmste ist dieses Niveau). index: Dieser Join-Typ ist derselbe wie ALL, außer dass nur der Indexbaum gescannt wird. Dies ist normalerweise schneller als ALL, da Indexdateien normalerweise kleiner als Datendateien sind. ALL: Führen Sie einen vollständigen Tabellenscan für jede Zeilenkombination aus der vorherigen Tabelle durch, um anzuzeigen, dass die Abfrage optimiert werden muss. Im Allgemeinen muss sichergestellt werden, dass die Abfrage mindestens die Bereichsebene und vorzugsweise die Referenzebene erreicht. Das obige System ist das beste, in absteigender Reihenfolge ist ALLE das schlechteste.
possible_keys	gibt an, welchen Index MySQL zum Suchen von Zeilen in der Tabelle verwenden kann MySQL entscheidet sich tatsächlich für die Verwendung (index). Wenn kein Index ausgewählt ist, ist der Schlüssel NULL.
key_len	Zeigt die Schlüssellänge an, die MySQL verwenden möchte. Wenn der Schlüssel NULL ist, ist die Länge NULL. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren
ref	Zeigt an, welche Spalte oder Konstante zusammen mit der Taste zum Auswählen von Zeilen aus der Tabelle verwendet wird.
rows	Zeigt die Anzahl der Zeilen an, die MySQL bei der Ausführung der Abfrage überprüfen muss. Die Multiplikation von Daten über mehrere Zeilen hinweg liefert eine Schätzung der Anzahl der zu verarbeitenden Zeilen.
filtered	zeigt die prozentuale Schätzung der Anzahl der durch die Bedingung gefilterten Zeilen.
Extra	Diese Spalte enthält die Details, wie MySQL die Abfrage gelöst hat
Wegoptimierte Tabellen auswählenMySQL gibt Daten zurück, ohne die Tabellen oder Indizes überhaupt zu durchlaufen, was darauf hinweist, dass sie so weit optimiert wurden, dass sie nicht mehr optimiert werden können	Nicht vorhanden : MySQL kann die Abfrage mit LEFT JOIN optimieren und gefunden 1 passender LEFT JOIN-Standard Nach einer Zeile werden keine weiteren Zeilen innerhalb der Tabelle auf die vorherige Zeilenkombination überprüft. Bereich für jeden Datensatz überprüft (Indexzuordnung: #): MySQL hat keinen guten Index gefunden, der verwendet werden kann, hat jedoch festgestellt, dass einige Indizes verwendet werden können, wenn die Spaltenwerte aus der vorherigen Tabelle bekannt sind. Filesort verwenden: MySQL erfordert einen zusätzlichen Durchgang, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden, was darauf hinweist, dass die Abfrage optimiert werden muss. Index verwenden: Rufen Sie Spalteninformationen aus einer Tabelle ab, indem Sie die tatsächlichen Zeilen lesen und dabei nur die Informationen im Indexbaum verwenden, ohne weitere Suche. Temporär verwenden: Um die Abfrage zu lösen, muss MySQL eine temporäre Tabelle erstellen, um die Ergebnisse aufzunehmen, was bedeutet, dass die Abfrage optimiert werden muss. Mit der where:WHERE-Klausel wird begrenzt, welche Zeile mit der nächsten Tabelle übereinstimmt oder an den Kunden gesendet wird. Verwenden von sort_union(...), Verwenden von union(...), Verwenden von intersect(...): Diese Funktionen veranschaulichen, wie Indexscans für den Join-Typ index_merge zusammengeführt werden. Index für Group-by verwenden: Ähnlich wie bei der Methode „Index verwenden“ für den Zugriff auf eine Tabelle bedeutet die Verwendung eines Index für Group-by, dass MySQL einen Index gefunden hat, mit dem alle Spalten von GROUP BY- oder DISTINCT-Abfragen ohne zusätzliche Abfragen abgefragt werden können Durchsuchen der Festplatte.

Gemäß der obigen Tabelle kann es eine gute Hilfe bei der Analyse des Ausführungsplans sein.

Hinweis: Wenn Sie das Interview bewältigen möchten, ist es am besten, es auswendig zu lernen. Wenn Sie sich nicht alles merken können, sollten Sie auch 123 sagen können und dann sagen, dass Sie sich nicht an so viel erinnern können. Sie können die relevanten Dokumente zum Vergleich lesen.

Das obige ist der detaillierte Inhalt vonSQL-Optimierungsfähigkeiten, die normale Programmierer beherrschen müssen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!