Erläuterung der MySQL-Indexoptimierung

Bei unserer täglichen Arbeit führen wir manchmal langsame Abfragen aus, um einige SQL-Anweisungen aufzuzeichnen, deren Ausführung lange dauert. Das Auffinden dieser SQL-Anweisungen bedeutet nicht, dass die Arbeit erledigt ist. Manchmal verwenden wir häufig den Befehl „explain“, um den Ausführungsplan dieser SQL-Anweisungen anzuzeigen und festzustellen, ob die SQL-Anweisung einen Index verwendet und ob ein vollständiger Tabellenscan durchgeführt wird. Dies kann über den Befehl „explain“ angezeigt werden. Wir verfügen also über ein tiefes Verständnis des kostenbasierten Optimierers von MySQL und können auch viele Einzelheiten zu den Zugriffsstrategien erhalten, die vom Optimierer in Betracht gezogen werden können, und dazu, welche Strategie vom Optimierer voraussichtlich beim Ausführen von SQL-Anweisungen übernommen wird.

-- Aktuelles SQL, suche nach Mitarbeitern, deren Benutzername Jefabc ist: select * from emp where name = 'Jefabc';
-- Überprüfen Sie, ob SQL Indizes verwendet. Fügen Sie einfach „explain“ davor ein. select * from emp where name = 'Jefabc'; 

Die Informationen aus expain haben 10 Spalten, nämlich id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

Zusammenfassende Beschreibung:
id: Wählen Sie eine Kennung
select_type: gibt den Abfragetyp an.
Tabelle: Tabelle des Ausgabeergebnissatzes
Partitionen: passende Partitionen
Typ: Gibt den Verbindungstyp der Tabelle an
Possible_keys: Gibt die möglichen Indizes an, die bei der Abfrage verwendet werden sollen
Schlüssel: Gibt den tatsächlich verwendeten Index an
key_len: Länge des Indexfeldes
ref: Vergleich von Spalten und Indizes
Zeilen: die Anzahl der gescannten Zeilen (geschätzte Zeilenanzahl)
gefiltert: Der Prozentsatz der Zeilen, die nach Tabellenbedingungen gefiltert wurden
Extra: Beschreibung und Erklärung der Durchführung

Nachfolgend finden Sie eine Erläuterung des möglichen Aussehens dieser Felder:

1. Identität

SELECT-Kennung. Dies ist die Abfragesequenznummer von SELECT

Nach meinem Verständnis ist es die Reihenfolge, in der SQL ausgeführt wird. SQL wird von groß nach klein ausgeführt.

1. Wenn die ID gleich ist, erfolgt die Ausführungsreihenfolge von oben nach unten

2. Wenn es sich um eine Unterabfrage handelt, erhöht sich die ID-Nummer. Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt.

3. Wenn die ID gleich ist, kann sie als Gruppe betrachtet und von oben nach unten ausgeführt werden. In allen Gruppen gilt: Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt.

-- Mitarbeiter anzeigen, die in der Forschungs- und Entwicklungsabteilung arbeiten und deren Namen mit Jef beginnen. Klassische Abfrage „explain“: select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like „Jef%“ and d.name = „Forschungs- und Entwicklungsabteilung“; 

2. Typ auswählen

Gibt den Typ jeder Select-Klausel in der Abfrage an

(1) SIMPLE (einfaches SELECT, ohne Verwendung von UNION oder Unterabfragen usw.)
(2) PRIMARY (die äußerste Abfrage in einer Unterabfrage. Wenn die Abfrage komplexe Unterteile enthält, wird die äußerste Auswahl als PRIMARY gekennzeichnet)
(3) UNION (die zweite oder nachfolgende SELECT-Anweisung in UNION)
(4)DEPENDENT UNION (die zweite oder nachfolgende SELECT-Anweisung in einer UNION hängen von der äußeren Abfrage ab)
(5)UNION RESULT (das Ergebnis von UNION, alle nachfolgenden Auswahlen ab der zweiten Auswahl in der Union-Anweisung)
(6) SUBQUERY (das erste SELECT in einer Unterabfrage, das Ergebnis hängt nicht von der äußeren Abfrage ab)
(7)DEPENDENT SUBQUERY (das erste SELECT in der Unterabfrage, abhängig von der äußeren Abfrage)
(8) DERIVED (abgeleitete Tabelle SELECT, Unterabfrage in FROM-Klausel)
(9) UNCACHEABLE SUBQUERY (das Ergebnis einer Unterabfrage kann nicht zwischengespeichert werden und die erste Zeile des äußeren Links muss erneut ausgewertet werden)

3. Tabelle

Zeigt den Namen der Tabelle in der Datenbank an, auf die in diesem Schritt zugegriffen wurde (zeigt an, um welche Tabelle es sich bei den Daten in dieser Zeile handelt). Manchmal ist dies nicht der tatsächliche Tabellenname, sondern eine Abkürzung, wie oben e und d, oder die Abkürzung des Ergebnisses der Ausführung des Schritts

Typ

Die Tabellenzugriffsmethode gibt an, wie MySQL die erforderlichen Zeilen in der Tabelle findet. Sie wird auch als „Zugriffstyp“ bezeichnet.

Gängige Typen sind: ALL, index, range, ref, eq_ref, const, system, NULL (von links nach rechts, Leistung von schlecht bis gut)

ALL: Vollständiger Tabellenscan, MySQL durchsucht die gesamte Tabelle nach übereinstimmenden Zeilen

index: Vollständiger Index-Scan. Der Unterschied zwischen index und ALL besteht darin, dass der Indextyp nur den Indexbaum durchläuft.

Bereich: Ruft nur die Zeilen in einem bestimmten Bereich ab und verwendet einen Index zum Auswählen der Zeilen.

ref: Gibt die Join-Matching-Bedingung der obigen Tabelle an, d. h. welche Spalten oder Konstanten verwendet werden, um den Wert in der Indexspalte zu finden

eq_ref: Ähnlich wie ref, der Unterschied besteht darin, dass der verwendete Index ein eindeutiger Index ist. Für jeden Indexschlüsselwert gibt es nur einen übereinstimmenden Datensatz in der Tabelle. Einfach ausgedrückt bedeutet dies, dass bei Verknüpfungen mehrerer Tabellen der Primärschlüssel oder eindeutige Schlüssel als Verknüpfungsbedingung verwendet wird.

const, system: Wenn MySQL einen Teil der Abfrage optimiert und in eine Konstante umwandelt, verwendet es diese Zugriffsarten. Wenn Sie den Primärschlüssel in die Where-Liste einfügen, kann MySQL die Abfrage in eine Konstante umwandeln. System ist ein Sonderfall des Typs const. Wenn die Abfragetabelle nur eine Zeile hat, verwenden Sie system.

NULL: MySQL zerlegt Anweisungen während der Optimierung und greift während der Ausführung nicht einmal auf Tabellen oder Indizes zu. Beispielsweise kann die Auswahl des Mindestwerts aus einer Indexspalte mit einer einzigen Indexsuche erfolgen.

5. mögliche Schlüssel

Gibt an, welchen Index MySQL verwenden kann, um Datensätze in der Tabelle zu finden. Wenn für das in die Abfrage involvierte Feld ein Index vorhanden ist, wird der Index aufgelistet, er kann jedoch nicht von der Abfrage verwendet werden (der Index, den die Abfrage verwenden kann; wenn kein Index vorhanden ist, wird null angezeigt).

Diese Spalte ist völlig unabhängig von der Reihenfolge der in der EXPLAIN-Ausgabe angezeigten Tabellen. Dies bedeutet, dass einige der Schlüssel in „possible_keys“ in der Reihenfolge, in der die Tabelle generiert wird, nicht verwendet werden können.
Wenn die Spalte NULL ist, gibt es keinen zugehörigen Index. In diesem Fall können Sie die Abfrageleistung verbessern, indem Sie die WHERE-Klausel überprüfen, um festzustellen, ob sie auf bestimmte Spalten oder auf für die Indizierung geeignete Spalten verweist. Wenn ja, legen Sie einen entsprechenden Index an und prüfen Sie die Abfrage noch einmal mit EXPLAIN.

6. Schlüssel

In der Spalte key wird der Schlüssel (Index) angezeigt, den MySQL tatsächlich verwenden wollte. Dieser muss in possible_keys enthalten sein.

Wenn kein Index ausgewählt wurde, ist der Schlüssel NULL. Um MySQL zu zwingen, den Index der Spalte „possible_keys“ zu verwenden oder zu ignorieren, verwenden Sie in Ihrer Abfrage FORCE INDEX, USE INDEX oder IGNORE INDEX.

7. Schlüssellänge

Gibt die Anzahl der im Index verwendeten Bytes an. Mit dieser Spalte kann die Länge des in der Abfrage verwendeten Indexes berechnet werden (der von key_len angezeigte Wert ist die maximal mögliche Länge des Indexfeldes, nicht die tatsächlich verwendete Länge, d. h. key_len wird basierend auf der Tabellendefinition berechnet und nicht aus der Tabelle abgerufen).

Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren.

8. Schiedsrichter

Vergleich von Spalten und Indizes, der die Join-Matching-Bedingungen der obigen Tabellen angibt, d. h. welche Spalten oder Konstanten zum Suchen der Werte in den Indexspalten verwendet werden

9. Reihen

Schätzen Sie die Anzahl der Ergebnissatzzeilen. Dies bedeutet, dass MySQL anhand der Tabellenstatistik und der Indexauswahl die Anzahl der Zeilen schätzt, die zum Auffinden der erforderlichen Datensätze erforderlich sind.

10. Extra

In dieser Spalte finden Sie detaillierte Informationen darüber, wie MySQL die Abfrage löst. Es gibt mehrere Fälle:

Verwenden von where: Anstatt alle Informationen in der Tabelle zu lesen, können Sie die erforderlichen Daten nur über den Index abrufen. Dies geschieht, wenn alle angeforderten Spalten für die Tabelle Teil desselben Index sind, was bedeutet, dass der MySQL-Server filtert, nachdem die Speicher-Engine die Zeile abgerufen hat.

Temporär verwenden: Gibt an, dass MySQL eine temporäre Tabelle zum Speichern des Ergebnissatzes verwenden muss, was bei Sortier- und Gruppierungsabfragen sowie bei den üblichen Gruppierungs- und Sortierungsmethoden üblich ist.

Verwenden von Filesort: Wenn die Abfrage eine Order-by-Operation enthält und die Sortieroperation nicht mithilfe des Index abgeschlossen werden kann, wird dies als „Dateisortierung“ bezeichnet.

-- Testen Sie die Dateisortierung von Extra
Erklären Sie „Select * from emp order by name“.

Join-Puffer verwenden: Dieser Wert betont, dass beim Abrufen der Join-Bedingung kein Index verwendet wird und ein Join-Puffer zum Speichern von Zwischenergebnissen erforderlich ist. Wenn dieser Wert angezeigt wird, sollten Sie sich darüber im Klaren sein, dass Sie je nach den spezifischen Umständen der Abfrage möglicherweise einen Index hinzufügen müssen, um die Leistung zu verbessern.

Unmöglich, wo: Dieser Wert betont, dass die Where-Anweisung keine qualifizierenden Zeilen ergibt (kein mögliches Ergebnis durch Sammeln von Statistiken).

Tabellen mit optimierter Auswahl: Dieser Wert bedeutet, dass der Optimierer nur eine Zeile aus dem Ergebnis der Aggregatfunktion zurückgeben kann, indem nur der Index verwendet wird.

Keine Tabellen verwendet: Die Abfrageanweisung verwendet from dual oder enthält keine from-Klauseln

-- Erklären Sie „Select Now()“ von Dual.

Zusammenfassen:

• EXPLAIN informiert Sie nicht über Trigger, gespeicherte Prozeduren oder die Auswirkung benutzerdefinierter Funktionen auf die Abfrage.
• EXPLAIN berücksichtigt nicht verschiedene Caches
• EXPLAIN zeigt nicht die Optimierungsarbeit, die MySQL bei der Ausführung der Abfrage durchführt.
• Einige Statistiken sind Schätzungen und keine genauen Werte
• EXPALIN kann nur SELECT-Operationen erklären. Andere Operationen müssen als SELECT umgeschrieben und anschließend der Ausführungsplan angezeigt werden.

Es gibt kein mögliches Ergebnis durch das Sammeln von Statistiken

Dies ist das Ende dieses Artikels zur MySQL-Indexoptimierung. Weitere relevante Inhalte zur MySQL-Indexoptimierung finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!