Eine kurze Diskussion zur MySQL-Indexoptimierungsanalyse

Warum sind die von Ihnen geschriebenen SQL-Abfragen langsam? Warum schlagen die von Ihnen erstellten Indizes oft fehl? In diesem Kapitel erfahren Sie die Gründe für die Leistungseinbußen von MySQL, erhalten eine Einführung in Indizes, die Grundsätze der Indexerstellung, die Verwendung des EXPLAIN-Befehls und die Bedeutung der EXPLAIN-Ausgabefelder. Hilft Ihnen, Indizes zu verstehen, Indizes zu analysieren und Indizes zu verwenden, um SQL-Anweisungen mit höherer Leistung zu schreiben. Worauf warten Sie noch? Krempeln Sie die Ärmel hoch und machen Sie sich an die Arbeit!

Fallstudie

Lassen Sie uns zunächst kurz den Unterschied zwischen nicht-relationalen und relationalen Datenbanken verstehen.

MongoDB ist eine Art NoSQL. Der vollständige Name von NoSQL lautet „Nicht nur SQL, nicht-relationale Datenbank“. Es zeichnet sich durch hohe Leistung, starke Skalierbarkeit und flexiblen Modus aus und bietet besonders gute Leistung in Szenarien mit hoher Parallelität. Derzeit handelt es sich jedoch lediglich um eine Ergänzung zu relationalen Datenbanken, und hinsichtlich der Datenkonsistenz, Datensicherheit und Abfragekomplexität besteht zwischen dieser und relationalen Datenbanken immer noch eine gewisse Lücke.

MySQL ist eine relationale Datenbank mit leistungsstarken Abfragefunktionen, hoher Datenkonsistenz, hoher Datensicherheit und Unterstützung für sekundäre Indizes. Allerdings ist die Leistung etwas schlechter als bei MongoDB, insbesondere bei Daten über einer Million, was leicht zu langsamen Abfragen führen kann. Zu diesem Zeitpunkt müssen Sie die Gründe für die langsame Abfrage analysieren. Im Allgemeinen liegt dies an der schlechten SQL-Schreibweise des Programmierers, dem Fehlen eines Schlüsselindex oder dem ungültigen Index.

Die Datenbank des ERP-Systems des Unternehmens besteht hauptsächlich aus MongoDB (dem NoSQL, das relationalen Daten am nächsten kommt), gefolgt von Redis und MySQL macht nur einen kleinen Teil aus. Dank Alibabas Qimen-System und Jushita-System verwenden wir jetzt wieder MySQL. Da die Anzahl der Bestellungen bereits über einer Million liegt, ist die Leistungsanalyse von MySQL besonders wichtig.

Beginnen wir mit zwei einfachen Beispielen. Die Funktion und Bedeutung der einzelnen Parameter wird später ausführlich vorgestellt.

Hinweis: Das benötigte SQL wurde auf GitHub abgelegt. Wenn es dir gefällt, kannst du auf den Stern klicken.

https://github.com/ITDragonBlog/daydayup/tree/master/MySQL/

Szenario 1: Importieren von Bestellungen und Vermeiden doppelter Bestellungen anhand der Transaktionsnummer

Geschäftslogik: Um Doppelbestellungen zu vermeiden, wird beim Importieren von Bestellungen grundsätzlich anhand der Transaktionsnummer die Datenbank abgefragt, ob die Bestellung bereits vorliegt.

Die grundlegendste SQL-Anweisung

mysql> wähle * aus itdragon_order_list, wobei transaction_id = "81X97310V32236260E";
+-------+--------------------+-------+----------+----------+--------------+----------+------------------+------------+-------------+-------------+-------------+-------------+
| ID | Transaktions-ID | Brutto | Netto | Lager-ID | Bestellstatus | Beschreibung | Finanzbeschreibung | Erstellungstyp | Bestellebene | Eingabebenutzer | Eingabedatum |
+-------+--------------------+-------+----------+----------+--------------+----------+------------------+------------+-------------+-------------+-------------+-------------+
| 10000 | 81X97310V32236260E | 6.6 | 6.13 | 1 | 10 | ok | ok | auto | 1 | itdragon | 18.08.2017 17:01:49 |
+-------+--------------------+-------+----------+----------+--------------+----------+------------------+------------+-------------+-------------+-------------+-------------+

mysql> erläutern Sie „select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+----------+---------------------+------------+------+---------------+------+---------+---------+------+---------+----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------------------+------------+------+---------------+------+---------+---------+------+---------+----------+----------+-------------+
| 1 | SIMPLE | itdragon_order_list | NULL | ALLE | NULL | NULL | NULL | NULL | 3 | 33.33 | Verwenden von „where“ |
+----+----------+---------------------+------------+------+---------------+------+---------+---------+------+---------+----------+----------+-------------+

Es gibt kein Problem mit der Abfrage selbst und es gibt kein Problem mit der Offline-Testumgebung. Sobald die Funktion jedoch gestartet wird, tritt das Problem der langsamen Abfrage auf. Hunderte oder zig Millionen Bestellungen. Vollständigen Tabellenscan verwenden? Ah? Schnauben!

Woher wissen Sie, dass es sich bei SQL um einen vollständigen Tabellenscan handelt? Mit dem Befehl „explain“ lässt sich anschaulich darstellen, wie MySQL SQL-Anweisungen verarbeitet. Der gedruckte Inhalt stellt dar:

1. id: Die Abfragesequenznummer ist 1.
2. select_type: Der Abfragetyp ist eine einfache Abfrage, eine einfache Select-Anweisung ohne Union und Unterabfrage.
3. Tabelle: Tabelle ist itdragon_order_list.
4. Partitionen: Keine Partitionen.
5. Typ: Verbindungstyp, „alle“ bedeutet vollständigen Tabellenscan.
6. Possible_keys: Mögliche Indizes können null sein.
7. Schlüssel: Der tatsächlich verwendete Index ist null.
8. key_len: Die Indexlänge ist natürlich auch null.
9. ref: Es wird keine Spalte oder kein Parameter mit dem Schlüssel verwendet.
10. Extra: wo die Abfrage verwendet wird.

Da die Datenbank nur drei Datensätze enthält, sind die Zeilen und gefilterten Informationen nicht sehr nützlich. Der entscheidende Punkt, den Sie hier verstehen müssen, ist, dass die Leistung des vollständigen Tabellenscans am schlechtesten ist, wenn der Typ ALL ist. Angenommen, die Datenbank enthält Millionen von Daten, wird sie ohne die Hilfe von Indizes extrem langsam sein.

Vorläufige Optimierung: Erstellen Sie einen Index für die Transaktions-ID.

mysql> eindeutigen Index idx_order_transaID auf itdragon_order_list (transaction_id) erstellen;
mysql> erläutern Sie „select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+----------+---------+---------+-----------+--------------------+--------------------+---------+-------+----------+----------+----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------+---------+-----------+--------------------+--------------------+---------+-------+----------+----------+----------+
| 1 | EINFACH | itdragon_order_list | NULL | const | idx_order_transaID | idx_order_transaID | 453 | const | 1 | 100 | NULL |
+----+----------+---------+---------+-----------+--------------------+--------------------+---------+-------+----------+----------+----------+

Der hier erstellte Index ist ein eindeutiger Index, kein normaler Index.

Der vom eindeutigen Index gedruckte Typwert ist const. Gibt an, dass es durch einmaliges Indizieren gefunden werden kann. Sobald der Wert gefunden wurde, wird der Scan beendet und das Abfrageergebnis zurückgegeben.

Der vom normalen Index gedruckte Typwert ist „ref“. Zeigt einen nicht eindeutigen Indexscan an. Wenn ein Wert gefunden wird, fahren Sie mit dem Scannen fort, bis die Indexdatei vollständig gescannt ist. (Hier wird kein Code gepostet)
Offensichtlich ist die Leistung von const viel höher als die von ref. Und aus geschäftlicher Sicht ist es sinnvoll, einen eindeutigen Index zu erstellen.

Erneut optimieren: Index abdecken

mysql> erläutern Sie „select transaction_id from itdragon_order_list where transaction_id = „81X97310V32236260E“;
+----+----------+---------+---------+-----------+--------------------+--------------------+---------+-----------+---------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------+---------+-----------+--------------------+--------------------+---------+-----------+---------+---------+
| 1 | SIMPLE | itdragon_order_list | NULL | const | idx_order_transaID | idx_order_transaID | 453 | const | 1 | 100 | Index wird verwendet |
+----+----------+---------+---------+-----------+--------------------+--------------------+---------+-----------+---------+---------+

Hier wird select * from in select transaction_id from geändert und Extra zeigt Using index an, was bedeutet, dass die Abfrage einen überdeckenden Index verwendet. Das sind sehr gute Neuigkeiten und zeigen, dass die Leistung der SQL-Anweisung sehr gut ist. Wenn die Eingabeaufforderung „Dateisortierung verwenden“ (interne Sortierung verwenden) oder „Temporäre Tabelle verwenden“ lautet, bedeutet dies, dass SQL sofort optimiert werden muss.

Gemäß der Geschäftslogik kann die Abfragestruktur, die die Transaktions-ID zurückgibt, die Anforderungen der Geschäftslogik erfüllen.

Szenario 2: Auftragsverwaltungsseite, Sortierung nach Auftragsebene und Auftragserfassungszeit

Geschäftslogik: Priorisieren Sie Aufträge mit hohem Auftragsbestand und langen Eingangszeiten.
Da es sich um das Sortieren handelt, sollte Ihnen als Erstes „Order by“ in den Sinn kommen, und außerdem wartet eine beängstigende „Using Filesort“-Aktion auf Sie.

Die grundlegendste SQL-Anweisung

mysql> erklären Sie „select * from itdragon_order_list“, sortieren Sie nach order_level, input_date;
+----+----------+---------------------+------------+------+---------------+---------+---------+------+---------+------+------+------+----------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------------------+------------+------+---------------+---------+---------+------+---------+------+------+------+----------------+
| 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Filesort verwenden |
+----+----------+---------------------+------------+------+---------------+---------+---------+------+---------+------+------+------+----------------+

Erstens ist die Verwendung eines vollständigen Tabellenscans nicht sinnvoll und die Verwendung von Filesort verringert die Leistung zusätzlich.

MySQL-Versionen vor 4.1 verwendeten einen Zweiwege-Sortieralgorithmus für die Dateisortierung. Da die Festplatte zweimal gescannt wurde, dauerte der I/O-Vorgang zu lange. Später wurde es zu einem Single-Path-Sortieralgorithmus optimiert. Sein Wesen besteht darin, Speicherplatz gegen Zeit einzutauschen. Wenn jedoch die Datenmenge zu groß und der Pufferspeicherplatz nicht ausreicht, kommt es zu mehreren E/A-Vorgängen. Die Wirkung ist sogar noch schlimmer. Anstatt Ihre Kollegen aus den Bereichen Betrieb und Wartung zu bitten, die MySQL-Konfiguration zu ändern, ist es besser, den Index selbst zu erstellen.

Vorläufige Optimierung: Erstellen Sie einen zusammengesetzten Index für order_level, input_date

mysql> erstelle Index idx_order_levelDate auf itdragon_order_list (order_level, input_date);
mysql> erklären Sie „select * from itdragon_order_list“, sortieren Sie nach order_level, input_date;
+----+----------+---------------------+------------+------+---------------+---------+---------+------+---------+------+------+------+----------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------------------+------------+------+---------------+---------+---------+------+---------+------+------+------+----------------+
| 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Filesort verwenden |
+----+----------+---------------------+------------+------+---------------+---------+---------+------+---------+------+------+------+----------------+

Nachdem Sie einen zusammengesetzten Index erstellt haben, werden Sie möglicherweise überrascht feststellen, dass dies dasselbe ist, als ob Sie keinen Index erstellt hätten. ? ? Bei allen handelt es sich um vollständige Tabellenscans, und bei allen wird eine Dateisortierung verwendet. Ist der Index ungültig? Oder ist die Indexerstellung fehlgeschlagen? Versuchen wir, den folgenden Ausdruck zu sehen

mysql> erklären Sie „select order_level,input_date“ aus der itdragon_order_list, sortieren Sie nach order_level,input_date;
+----+----------+---------+---------+-----------+---------------+---------------------+---------+------+---------+---------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------+---------+-----------+---------------+---------------------+---------+------+---------+---------+---------+
| 1 | SIMPLE | itdragon_order_list | NULL | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | Index wird verwendet |
+----+----------+---------+---------+-----------+---------------+---------------------+---------+------+---------+---------+---------+

Nach der Änderung „select * from“ in „select order_level, input_date from“. Der Typ wird von „alle“ auf „Index“ aktualisiert, was auf einen vollständigen Indexscan hinweist. „Extra“ zeigt auch an, dass ein abdeckender Index verwendet wird. Aber das ist nicht richtig! ! ! ! Obwohl die Suche schneller ist, enthält der zurückgegebene Inhalt nur zwei Felder: order_level und input_date. Wie können meine Geschäftskollegen ihn verwenden? Sollten wir für jedes Feld einen zusammengesetzten Index erstellen?

MySQL ist nicht so dumm. Sie können „Force Index“ verwenden, um einen angegebenen Index zu erzwingen. Ändern Sie einfach den Force-Index (idx_order_levelDate) in der ursprünglichen SQL-Anweisung.

mysql> erklären Sie „select * from itdragon_order_list force index(idx_order_levelDate) sortieren nach order_level, input_date;“
+----+----------+---------------------+------------+-------+---------------+---------------------+---------+------+------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------------------+------------+-------+---------------+---------------------+---------+------+------+------+------+------+
| 1 | EINFACH | itdragon_order_list | NULL | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | NULL |
+----+----------+---------------------+------------+-------+---------------+---------------------+---------+------+------+------+------+------+

Nochmals optimieren: Müssen Auftragsstände wirklich sortiert werden?

Tatsächlich macht es wenig Sinn, die Auftragsebenen zu sortieren, und es macht wenig Sinn, den Auftragsebenen Indizes hinzuzufügen. Denn die möglichen Werte von order_level sind nur low, medium, high und expedited. Für solche wiederholten und gleichmäßig verteilten Felder sind Sortieren und Indizierung wenig sinnvoll.

Können wir zuerst den Wert von order_level festlegen und dann input_date sortieren? Wenn der Abfrageeffekt offensichtlich ist, können Sie Geschäftskollegen empfehlen, diese Abfragemethode zu verwenden.

mysql> erläutern Sie „select * from itdragon_order_list where order_level=3 sortieren nach input_date;“
+----+----------+---------+---------+------+---------------------+---------------------+---------+---------+---+------+------+----------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+---------+---------+------+---------------------+---------------------+---------+---------+---+------+------+----------------------+
| 1 | SIMPLE | itdragon_order_list | NULL | ref | idx_order_levelDate | idx_order_levelDate | 5 | const | 1 | 100 | Indexbedingung wird verwendet |
+----+----------+---------+---------+------+---------------------+---------------------+---------+---------+---+------+------+----------------------+

Im Vergleich zum vorherigen SQL wird der Typ von Index auf Ref aktualisiert (nicht eindeutiger Indexscan). Die Länge des Indexes hat sich von 68 auf 5 geändert, was darauf hinweist, dass nur ein Index verwendet wird. ref ist auch eine Konstante. „Extra“ ist die Verwendung der Indexbedingung. Dies bedeutet, dass basierend auf dem kritischen Wert automatisch ein Index-Scan oder ein vollständiger Tabellen-Scan ausgewählt wird. Im Allgemeinen ist die Leistung viel besser als beim vorherigen SQL.

Die beiden oben genannten Fälle stellen nur eine kurze Einführung dar. Wir müssen eines bedenken: Die Optimierung basiert auf der Geschäftslogik. Die Geschäftslogik darf niemals eigenmächtig zu Optimierungszwecken verändert werden. Am besten wäre es natürlich, wenn es geändert werden könnte.

Index Einführung

Offizielle Definition: Ein Index ist eine Datenstruktur, die MySQL dabei hilft, Daten effizient abzurufen.

Jeder möchte sicherlich wissen, warum ein Index eine Datenstruktur ist und wie er die Abfragegeschwindigkeit verbessert. Nehmen wir den am häufigsten verwendeten Binärbaum, um zu analysieren, wie der Index funktioniert.

Schauen Sie sich das folgende Bild an:

Vorteile der Indexerstellung

1 Verbessern Sie die Datenabrufgeschwindigkeit und reduzieren Sie die Datenbank-E/A-Kosten: Die Verwendung von Indizes ist sinnvoll, da sie die Suche beschleunigt, indem die Anzahl der Datensätze reduziert wird, die in der Tabelle abgefragt werden müssen.

2 Reduzieren Sie die Kosten für die Datensortierung und reduzieren Sie den CPU-Verbrauch: Der Grund, warum der Index schnell durchsucht wird, besteht darin, dass die Daten zuerst sortiert werden. Wenn das Feld zufällig sortiert werden muss, werden die Kosten für die Sortierung tatsächlich reduziert.

Nachteile der Indexerstellung

1 Speicherplatz belegen: Der Index ist eigentlich eine Tabelle, die den Primärschlüssel und die Indexfelder aufzeichnet und im Allgemeinen in Form einer Indexdatei auf der Festplatte gespeichert wird.

2 Reduzieren Sie die Aktualisierungsgeschwindigkeit der Tabelle: Wenn sich die Daten in der Tabelle ändern, muss auch der entsprechende Index geändert werden, wodurch die Aktualisierungsgeschwindigkeit verringert wird. Andernfalls sind die vom Index gezeigten physischen Daten möglicherweise falsch, was ebenfalls einer der Gründe für Indexfehler ist.

3. Es ist schwierig, einen qualitativ hochwertigen Index zu erstellen: Das Erstellen eines Index ist keine Arbeit, die man an einem Tag erledigen kann, und er bleibt auch nicht unverändert. Es ist notwendig, häufig den besten Index basierend auf dem Benutzerverhalten und der spezifischen Geschäftslogik zu erstellen.

Indexklassifizierung

Der Index, auf den wir uns oft beziehen, ist im Allgemeinen der Index, der in der BTree-Struktur (Multi-Way Search Tree) organisiert ist. Es gibt auch aggregierte Indizes, sekundäre Indizes, zusammengesetzte Indizes, Präfixindizes und eindeutige Indizes, die zusammen als Indizes bezeichnet werden. Natürlich gibt es neben B + -Bäumen auch Hash-Indizes usw.

1. Einzelwertindex: Ein Index enthält nur eine einzige Spalte. Eine Tabelle kann mehrere einspaltige Indizes haben.
2. Eindeutiger Index: Der Wert der Indexspalte muss eindeutig sein, Nullwerte sind jedoch zulässig.
3. Zusammengesetzter Index: Ein Index enthält mehrere Spalten. Es wird empfohlen, ihn bei der tatsächlichen Entwicklung zu verwenden.

Bei der tatsächlichen Entwicklung wird die Verwendung zusammengesetzter Indizes empfohlen. Die Anzahl der für eine einzelne Tabelle erstellten Indizes sollte fünf nicht überschreiten.

Grundlegende Syntax:

erstellen:

Erstellen Sie einen [eindeutigen] Index IndexName für Tabellenname (Spaltenname …).
alter tableName add [unique] index [indexName] on (columnName…)

löschen:

lösche den Index [Indexname] auf Tabellenname

Überprüfen:

Index aus Tabellenname anzeigen

In welchen Fällen müssen Sie einen Index erstellen?

1 Primärschlüssel, eindeutiger Index
2 Felder, die oft als Abfragebedingungen verwendet werden, müssen indiziert werden
3 Felder, die oft sortiert, gruppiert und gezählt werden müssen, müssen indexiert werden
4. Erstellen Sie Indizes für Fremdschlüsselbeziehungen in Abfragen, die sich auf andere Tabellen beziehen

In welchen Situationen wird kein Index erstellt:

1. Die Tabelle hat zu wenige Datensätze. Für Daten unter einer Million muss kein Index erstellt werden.
2. Tabellen, die häufig hinzugefügt, gelöscht und geändert werden, müssen keine Indizes erstellen
3 Für Felder mit wiederholten und gleichmäßig verteilten Daten, wie etwa „wahr“ und „falsch“, müssen keine Indizes erstellt werden.
4 Häufig aktualisierte Felder sind nicht für die Indexerstellung geeignet
5. Felder, die in der Where-Bedingung nicht verwendet werden, müssen nicht indiziert werden

Leistungsanalyse

MySQLs eigener Engpass

Zu den Leistungsproblemen von MySQL selbst zählen unzureichender Speicherplatz, große Festplatten-E/A und geringe Leistung der Serverhardware.
1 CPU: Eine CPU-Sättigung tritt normalerweise auf, wenn Daten in den Speicher geladen oder von der Festplatte gelesen werden.
2 IO: Ein Festplatten-E/A-Engpass tritt auf, wenn die geladenen Daten viel größer sind als die Speicherkapazität
3 Leistungsengpässe der Serverhardware: top, free, iostat und vmstat zum Anzeigen des Systemleistungsstatus

Explain analysiert SQL-Anweisungen

Mit dem Schlüsselwort „explain“ können Sie die Ausführung von SQL-Abfrageanweisungen durch den Optimierer simulieren, um zu verstehen, wie MySQL SQL-Anweisungen verarbeitet.

+----+----------+----------+---------+------+---------------+-----+---------+------+---------+------+------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+-----+---------+------+---------+------+------+------+------+------+

Ausweis

Die Sequenznummer der Auswahlabfrage enthält eine Reihe wiederholbarer Zahlen, die die Reihenfolge angeben, in der SQL-Anweisungen in der Abfrage ausgeführt werden. Im Allgemeinen gibt es drei Situationen:
Der erste Typ: Alle IDs sind gleich und die Ausführungsreihenfolge von SQL ist von oben nach unten.
Der zweite Typ: Alle IDs sind unterschiedlich und die SQL-Ausführungsreihenfolge basiert auf der Priorität der größeren ID.
Der dritte Typ: IDs sind sowohl gleich als auch unterschiedlich. Führen Sie die Ausführung zuerst basierend auf der größten ID aus und führen Sie sie dann von oben nach unten basierend auf derselben ID aus.

Wählen Sie Typ

Der Typ der Auswahlabfrage wird hauptsächlich verwendet, um zwischen normalen Abfragen, gemeinsamen Abfragen und verschachtelten komplexen Abfragen zu unterscheiden.
einfach: eine einfache Auswahlabfrage, die keine Unterabfragen oder Vereinigungen enthält
primär: Wenn die Abfrage komplexe Unterabfragen enthält, wird die äußerste Abfrage als primär markiert
Unterabfrage: Eine Unterabfrage wird in die Select- oder Where-Liste eingefügt.
Abgeleitet: Die in der From-Liste enthaltenen Unterabfragen werden als abgeleitet gekennzeichnet. MySQL führt diese Unterabfragen rekursiv aus und speichert die Ergebnisse in einer temporären Tabelle.
Union: Wenn die zweite Auswahl nach der Union erscheint, wird sie als Union gekennzeichnet. Wenn die Union in einer Unterabfrage in der From-Klausel enthalten ist, wird die äußere Auswahl als abgeleitet gekennzeichnet.
Vereinigungsergebnis: Wählen Sie diese Option aus, um das Ergebnis aus der Vereinigungstabelle zu erhalten.

Partitionen

Die von der Tabelle verwendeten Partitionen: Wenn Sie die Anzahl der Unternehmensaufträge für zehn Jahre zählen möchten, können Sie die Daten in zehn Partitionen aufteilen, eine für jedes Jahr. Dies kann die Abfrageeffizienz erheblich verbessern.

Typ

Dies ist ein sehr wichtiger Parameter, der Verbindungstyp. Die häufigsten sind: all, index, range, ref, eq_ref, const, system, null, eight levels.
Leistungsreihenfolge vom Besten zum Schlechtesten: system > const > eq_ref > ref > range > index > all
Wenn ein Java-Programmierer sicherstellen kann, dass die Abfrage mindestens die Bereichsebene oder besser noch die Referenzebene erreicht, gilt er als hervorragender und verantwortungsvoller Programmierer.
all: (vollständiger Tabellenscan) Der vollständige Tabellenscan ist zweifellos der schlechteste. Wenn das Datenvolumen im Millionen- oder Zehnmillionenbereich liegt, ist ein vollständiger Tabellenscan sehr langsam.
index: (vollständiger Indexscan) Ein vollständiger Indexdateiscan ist viel besser als alles andere. Schließlich ist das Suchen von Daten aus dem Indexbaum schneller als das Suchen von Daten aus der gesamten Tabelle.
Bereich: Ruft nur die Zeilen in einem bestimmten Bereich ab und verwendet dabei den Index zum Abgleichen der Zeilen. Der Umfang ist eingeschränkt und natürlich ist es schneller als ein vollständiger Tabellenscan und ein vollständiger Indexdateiscan. SQL-Anweisungen enthalten im Allgemeinen Abfragen wie „zwischen“, „in“, „>“, „<“ usw.
Ref: Ein nicht eindeutiger Indexscan ist im Wesentlichen ein Indexzugriff, der alle Zeilen zurückgibt, die einem einzelnen Wert entsprechen. Wenn Sie beispielsweise alle Kollegen im Forschungs- und Entwicklungsteam eines Unternehmens abfragen, sind die übereinstimmenden Ergebnisse mehrere, aber keine eindeutigen Werte.
eq_ref: Eindeutiger Indexscan, für jeden Indexschlüssel gibt es einen passenden Datensatz in der Tabelle. Wenn Sie beispielsweise den CEO eines Unternehmens abfragen, besteht das übereinstimmende Ergebnis möglicherweise nur aus einem Datensatz.
const: gibt an, dass der Wert durch einmaliges Indizieren gefunden werden kann. const wird zum Vergleichen von Primärschlüsseln oder eindeutigen Indizes verwendet. Da nur eine Datenzeile abgeglichen werden muss, kann MySQL die Abfrage schnell in eine Konstante umwandeln, wenn der Primärschlüssel in der Where-Liste platziert wird.
System: Die Tabelle hat nur einen Datensatz (gleich der Systemtabelle). Dies ist eine spezielle Spalte vom Typ const. Normalerweise erscheint sie nicht. Sie müssen es einfach verstehen.

mögliche Schlüssel

Zeigt die Indizes an, die von der Abfrageanweisung verwendet werden können (einer oder mehrere oder null), die jedoch möglicherweise nicht tatsächlich von der Abfrage verwendet werden. Nur als Referenz.

Schlüssel

Zeigt den tatsächlich von der Abfrageanweisung verwendeten Index an. Wenn null, bedeutet dies, dass kein Index verwendet wird.

Schlüssellänge

Zeigt die Anzahl der im Index verwendeten Bytes an. Mit key_len können Sie die in der Abfrage verwendete Indexlänge berechnen. Je kürzer die Indexlänge, desto besser, ohne an Genauigkeit zu verlieren. Der von key_len angezeigte Wert ist die wahrscheinlichste Länge des Indexfelds, nicht die tatsächlich verwendete Länge. Das heißt, key_len wird basierend auf der Tabellendefinition berechnet und nicht aus der Tabelle abgerufen.

Referenz

Zeigt an, welche Spalte oder Konstante des Index verwendet wird, um den Wert der Indexspalte nachzuschlagen.

Reihen

Basierend auf den Tabellenstatistiken und der Indexauswahl schätzt es grob die Anzahl der Zeilen, die gelesen werden müssen, um die erforderlichen Datensätze zu finden. Je größer der Wert, desto schlechter ist es.

Extra

Filesort verwenden: Gibt an, dass MySQL zum Sortieren der Daten einen externen Index verwendet, anstatt sie in der Reihenfolge des Index in der Tabelle zu lesen. Sortiervorgänge in MySQL, die nicht mithilfe von Indizes durchgeführt werden können, werden als „Dateisortierungen“ bezeichnet. Wenn dies passiert, müssen Sie SQL sofort optimieren.
Temporäre Tabellen verwenden: Eine temporäre Tabelle wird zum Speichern von Zwischenergebnissen verwendet. MySQL verwendet eine temporäre Tabelle beim Sortieren von Abfrageergebnissen. Wird häufig beim Sortieren nach und Gruppieren von Abfragen nach verwendet. Wenn dies passiert, sollten Sie SQL sofort optimieren.
Index verwenden: Gibt an, dass bei der entsprechenden Auswahloperation ein überdeckender Index verwendet wird, um den Zugriff auf die Datenzeilen der Tabelle zu vermeiden. Die Wirkung ist gut! Wenn gleichzeitig „Using where“ erscheint, bedeutet dies, dass der Index für die Suche nach dem Indexschlüsselwert verwendet wird. Wenn „Using where“ nicht vorhanden ist, bedeutet dies, dass der Index zum Lesen von Daten verwendet wird, anstatt eine Suche durchzuführen.
Überdeckender Index: Auch Indexüberdeckung genannt. Die ausgewählte Datenspalte kann nur aus dem Index abgerufen werden, ohne dass die Datenzeile gelesen werden muss. MySQL kann den Index verwenden, um die Felder in der Auswahlliste zurückzugeben, ohne die Datendatei gemäß dem Index erneut lesen zu müssen.
Indexbedingung verwenden: Eine neue Funktion, die nach Version 5.6 hinzugefügt wurde. Wenn ein Index vorhanden ist, entscheidet der Optimierer, ob der Index verwendet werden soll oder eine vollständige Tabellendurchquerung durchgeführt werden soll, basierend auf dem Verhältnis der Anzahl der Einträge, die den RANGE-Bereich erfüllen, zur Gesamtzahl.
Die Verwendung von where: gibt an, dass die Filterung verwendet wird
Join-Puffer verwenden: Zeigt an, dass der Verbindungs-Cache verwendet wird
Unmöglich, wo: Der Wert der Where-Anweisung ist immer falsch, nicht verfügbar und kann nicht zum Abrufen von Elementen verwendet werden.
distinct: Optimiert die distinct-Operation und beendet die Suche nach identischen Werten, nachdem das erste passende Tupel gefunden wurde.

Filtern nach

Ein Prozentwert, der zusammen mit dem Wert der Zeilenspalte verwendet wird, kann den Ergebnissatz der vorherigen Tabelle im Abfrageausführungsplan (QEP) schätzen, um die Anzahl der Iterationen des Verbindungsvorgangs zu bestimmen. Kleine Tabellen steuern große Tabellen und reduzieren so die Anzahl der Verknüpfungen.

Durch die Parametereinführung von Explain können wir Folgendes erfahren:
1. Reihenfolge beim Lesen der Tabelle (ID)
2 Art des Datenlesevorgangs (Typ)
3 Welche Indizes werden tatsächlich verwendet (Schlüssel)
4 Verweise zwischen Tabellen (Ref)
5 Wie viele Zeilen jeder Tabelle werden vom Optimierer abgefragt (Zeilen)

Gründe für Leistungseinbußen

Aus der Sicht eines Programmierers
1. Die Abfrageanweisung ist nicht gut geschrieben
2. Es wird kein Index erstellt, der Index wurde nicht ordnungsgemäß erstellt oder der Index ist ungültig.
3. Es gibt zu viele Joins in der zugehörigen Abfrage

Aus Sicht des Servers
1 Nicht genügend Speicherplatz auf dem Server
2. Die Einstellungen der Konfigurationsparameter für die Serveroptimierung sind unangemessen

Zusammenfassen

1 Ein Index ist eine sortierte und schnell durchsuchbare Datenstruktur. Sein Zweck besteht darin, die Effizienz der Abfrage zu verbessern.
2 Nach dem Erstellen des Index wird das Abfragen der Daten schneller, das Aktualisieren der Daten jedoch langsamer.
3 Die Leistungsverschlechterung ist höchstwahrscheinlich auf einen Indexfehler zurückzuführen.
4 Grundsätze der Indexerstellung: Felder, die häufig abgefragt werden, eignen sich für die Indexerstellung, während Daten, die häufig aktualisiert werden müssen, nicht für die Indexerstellung geeignet sind.
5 Häufige Aktualisierungen von Indexfeldern oder das physische Löschen von Tabellendaten können leicht zu Indexfehlern führen.
6. Verwenden Sie „explain“, um SQL-Anweisungen zu analysieren
7 Neben der Optimierung von SQL-Anweisungen können Sie auch den Tabellenentwurf optimieren. Versuchen Sie beispielsweise, eine einzelne Tabellenabfrage durchzuführen, um die Verknüpfungen zwischen Tabellen zu reduzieren. Gestaltung von Ablagetischen etc.

Damit ist die MySQL-Indexoptimierungsanalyse abgeschlossen. Wenn Sie Fehler finden, weisen Sie uns bitte darauf hin. Wenn Du es gut findest, kannst Du es per Klick weiterempfehlen.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.