MySQL ermöglicht langsame Abfragen (Einführung in die Verwendung der EXPLAIN-SQL-Anweisung)

Heutzutage werden Datenbankoperationen immer häufiger zum Leistungsengpass der gesamten Anwendung, was insbesondere bei Webanwendungen deutlich wird. Die Datenbankleistung ist nicht nur etwas, worüber sich DBAs Gedanken machen müssen, sondern auch etwas, dem wir Programmierer unsere Aufmerksamkeit schenken müssen. Wenn wir die Datenbanktabellenstruktur entwerfen und die Datenbank betreiben (insbesondere die SQL-Anweisungen beim Nachschlagen der Tabelle), müssen wir auf die Leistung der Datenoperationen achten.

1. Aktivieren Sie langsame Abfragen

1> Überprüfen Sie, ob die langsame Abfrage aktiviert ist

Variablen wie „%quer%“ anzeigen;
slow_query_log = ON # Aktiviert

2> So aktivieren Sie: my.cnf-Verzeichniskonfiguration

slow_query_log=on #Ob aktiviert werden soll slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log #Speicherort der langsamen Abfragedatei long_query_time=2 #Wie viele Sekunden dauert es, bis die Abfrage aufgezeichnet wird

2. SELECT-Abfragen erscheinen im EXPLAIN-Protokoll für langsame Abfragen

Erklärung der Spalte „Explain“

• Tabelle: Zeigt an, um welche Tabelle es sich bei den Daten in dieser Zeile handelt

• Typ: Dies ist die wichtige Spalte und zeigt, welche Art von Verbindung verwendet wird. Die Verbindungstypen vom Besten zum Schlechtesten sind const, eq_reg, ref, range, index, all

• Possible_keys: Zeigt die Indizes an, die auf diese Tabelle angewendet werden können. Wenn leer, ist kein Index möglich. Sie können aus der Where-Klausel für die jeweilige Domäne eine entsprechende Anweisung auswählen

• Schlüssel: Der tatsächlich verwendete Index. Wenn null, wird kein Index verwendet. In seltenen Fällen kann es sein, dass MySQL einen Index wählt, der nicht optimal genug ist. In diesem Fall können Sie „use index (indexname)“ in der Select-Anweisung verwenden, um die Verwendung eines Indexes zu erzwingen, oder „ignore index (indexname)“ verwenden, um MySQL zu zwingen, den Index zu ignorieren.

• key_len: Die Länge des verwendeten Indexes. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren.

• ref: zeigt an, welche Spalte des Index verwendet wird, wenn möglich eine Konstante

• Zeilen: Die Anzahl der Zeilen, die MySQL als notwendig erachtet, um die angeforderten Daten zurückzugeben

• extra: Zusätzliche Informationen darüber, wie MySQL die Abfrage analysiert. Beispiel: Die Verwendung von „temporär“ und „Filesort“ bedeutet, dass MySQL den Index überhaupt nicht verwenden kann, was zu langsamen Abrufen führt.

Berechnung von key_len

1. Für alle Indexfelder muss, sofern sie nicht auf Null gesetzt sind, ein Byte hinzugefügt werden.

2. Feld mit fester Länge: int belegt vier Bytes, date belegt drei Bytes und char(n) belegt n Zeichen.

3. Für ein varchar(n)-Feld gibt es n Zeichen + zwei Bytes.

4. Verschiedene Zeichensätze verwenden unterschiedliche Bytezahlen pro Zeichen. Bei der Latin1-Kodierung belegt ein Zeichen ein Byte, bei der GBK-Kodierung belegt ein Zeichen zwei Bytes und bei der UTF8-Kodierung belegt ein Zeichen drei Bytes.

3. Mehrere Prinzipien zum Erstellen von Indizes

• Das Prinzip der Übereinstimmung mit dem am weitesten links stehenden Präfix ist ein sehr wichtiges Prinzip. MySQL führt die Übereinstimmung nach rechts weiter aus, bis es auf eine Bereichsabfrage (>, <, zwischen, wie) stößt und die Übereinstimmung beendet. Wenn beispielsweise a = 1 und b = 2 und c > 3 und d = 4 in der Reihenfolge (a, b, c, d) erstellt werden, wird d nicht im Index verwendet. Wenn ein Index in der Reihenfolge (a, b, d, c) erstellt wird, können alle verwendet werden. Die Reihenfolge von a, b, d kann beliebig angepasst werden.

• = und in können in beliebiger Reihenfolge stehen, zum Beispiel a = 1 und b = 2 und c = 3. Sie können einen (a,b,c)-Index in beliebiger Reihenfolge erstellen und der MySQL-Abfrageoptimierer hilft Ihnen dabei, ihn in eine Form zu optimieren, die der Index erkennen kann.

• Versuchen Sie, Spalten mit hoher Unterscheidung als Indizes auszuwählen. Die Formel für die Unterscheidung lautet Anzahl (unterschiedliche Spalten)/Anzahl (*), was das Verhältnis der nicht duplizierten Felder angibt. Je größer das Verhältnis, desto weniger Datensätze müssen wir scannen. Die Unterscheidung eines eindeutigen Schlüssels beträgt 1, während einige Status- und Geschlechtsfelder angesichts großer Datenmengen eine Unterscheidung von 0 aufweisen können. Jemand könnte fragen, ob es für dieses Verhältnis einen empirischen Wert gibt. Dieser Wert ist aufgrund unterschiedlicher Verwendungsszenarien schwer zu bestimmen. Im Allgemeinen verlangen wir, dass der Wert des zu verknüpfenden Felds über 0,1 liegt, was bedeutet, dass für jedes Feld durchschnittlich 10 Datensätze gescannt werden.

• Indexspalten können nicht in Berechnungen oder Funktionen verwendet werden. Halten Sie die Spalten daher sauber.

• Versuchen Sie den Index so weit wie möglich zu erweitern und erstellen Sie keinen neuen Index. Wenn in der Tabelle beispielsweise bereits ein Index für a vorhanden ist und Sie einen Index für (a,b) hinzufügen möchten, müssen Sie nur den ursprünglichen Index ändern.