Detaillierte Erklärung des Explain-Typs in MySQL

Einführung:

In vielen Fällen denken viele Leute, dass ihre Arbeit erfolgreich abgeschlossen ist, nachdem sie verschiedene Select-Anweisungen verwendet haben, um die gewünschten Daten abzufragen.
Diese Dinge passieren oft einigen Studenten oder Neulingen, die gerade erst ins Berufsleben eingestiegen sind, aber noch keine guten Datenbankkenntnisse haben. Wie das Sprichwort jedoch sagt, lernt jeder zu einem anderen Zeitpunkt. Solange wir Neulinge fleißig lernen und jeden Tag Fortschritte machen, ist es immer noch sehr zuverlässig.

Wenn eine SQL-Abfrageanweisung geschrieben ist, ist Ihre Arbeit eigentlich erst zur Hälfte erledigt. Die nächste, wichtigere Aufgabe besteht darin, die Qualität und Effizienz des von Ihnen geschriebenen SQL zu bewerten. MySQL stellt uns mit „explain“ eine sehr nützliche Hilfswaffe zur Verfügung, die uns den Ausführungsplan einer von MySQL empfangenen SQL-Anweisung zeigt. Anhand der von Explain zurückgegebenen Ergebnisse können wir erkennen, wie unser SQL geschrieben ist und ob es zu Abfrageengpässen führt. Gleichzeitig können wir die Abfrageanweisung basierend auf den Ergebnissen kontinuierlich ändern und anpassen, um den SQL-Optimierungsprozess abzuschließen.

Obwohl „explain“ viele Ergebniselemente zurückgibt, konzentrieren wir uns hier nur auf drei Typen, nämlich Typ, Schlüssel und Zeilen. Der Schlüssel gibt den bei dieser Suche verwendeten Index an, und „Zeilen“ bezieht sich auf die Anzahl der für diese Suche gescannten Zeilen (so kann man es zunächst verstehen, aber eigentlich ist es die Anzahl der inneren Schleifen). Typ ist der Verbindungstyp, den dieser Artikel im Detail aufzeichnet. Die ersten beiden Punkte sind wichtig und einfach, daher müssen keine weiteren Erläuterungen gegeben werden.

Typ – Verbindungstyp

Typ bedeutet Typ. Der offizielle vollständige Name des Typs lautet hier „Jointyp“, was „Jointyp“ bedeutet. Dies kann leicht den Eindruck erwecken, dass mehr als zwei Tabellen erforderlich sind, um einen Join-Typ zu haben. Tatsächlich ist der Verbindungstyp hier nicht so eng gefasst, wie sein Name vermuten lässt. Es handelt sich vielmehr um eine Möglichkeit für eine Datenbank-Engine, nach einer Tabelle zu suchen. In dem Buch „High Performance MySQL“ hält es der Autor für angemessener, ihn einen Zugriffstyp zu nennen.

In MySQL 5.7 gibt es nicht weniger als 14 Typen. Hier erfassen und verstehen wir nur die sechs wichtigsten und am häufigsten vorkommenden Typen, nämlich all, index, range, ref, eq_ref und const. Von links nach rechts nimmt ihre Effizienz zu. Unabhängig von der spezifischen Anwendungsumgebung von SQL und anderen Faktoren sollten Sie versuchen, Ihre SQL-Anweisung so zu optimieren, dass ihr Typ möglichst richtig ist. Bei der tatsächlichen Anwendung müssen Sie jedoch dennoch alle Aspekte umfassend berücksichtigen.

Um diese fünf Typen besser zu verstehen, habe ich als Nächstes eine neue Datentesttabelle erstellt, um diese Verbindungstypen zu demonstrieren und zu reproduzieren.

| Mitarbeiter | CREATE TABLE `Mitarbeiter` (
 `rec_id` int(11) NICHT NULL AUTO_INCREMENT,
 `nein` varchar(10) NICHT NULL,
 `name` varchar(20) NICHT NULL,
 `position` varchar(20) NICHT NULL,
 `Alter` varchar(2) NICHT NULL,
 PRIMÄRSCHLÜSSEL (`rec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

alle

Dies ist der sogenannte „Full Table Scan“. Wenn alle Datenelemente in einer Datentabelle angezeigt werden sollen, spielt dies keine Rolle. Wenn jedoch der Typ „all“ in einer SQL-Anweisung erscheint, die nach Datenelementen sucht, bedeutet dies normalerweise, dass sich Ihre SQL-Anweisung im nativen Zustand befindet und viel Raum für Optimierungen bietet.
Warum sage ich das? Da es sich bei all diesen Suchmethoden um sehr gewalttätige und primitive Methoden handelt, sind sie sehr zeitaufwändig und ineffizient. Die Datensuche mit „all“ funktioniert folgendermaßen: In der S-Schule sind 20.000 Leute und ich sage Ihnen, Sie sollen Xiao Ming für mich finden. Was machen Sie also? Natürlich werden Sie alle 20.000 Personen in der Schule einzeln durchsuchen. Selbst wenn Sie Glück haben und Xiao Ming als erste Person finden, können Sie trotzdem nicht aufhören, da Sie erst bestätigen können, ob es noch einen anderen Xiao Ming gibt, wenn Sie alle 20.000 Personen durchsucht haben. Daher sollten wir diese Art der Suche in fast allen Fällen vermeiden, es sei denn, es ist unbedingt erforderlich.
Am Beispiel der Mitarbeitertabelle handelt es sich beim folgenden Szenario um eine Suche nach allen Typen:

mysql> erläutern Sie die Auswahl * vom Mitarbeiter, wobei „nein“ = „20150001“ ist;
+----+----------+----------+------+---------------+------+---------+---------+------+---------+---------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+------+---------------+------+---------+---------+------+---------+---------+
| 1 | SIMPLE | Mitarbeiter | ALLE | NULL | NULL | NULL | NULL | 5 | Verwenden von „where“ |
+----+----------+----------+------+---------------+------+---------+---------+------+---------+---------+

Dies liegt daran, dass die Spalte „Nr.“ weder ein Primärschlüssel noch ein Index ist und daher nur ein vollständiger Tabellenscan zum Suchen der Zielnummer verwendet werden kann.

Index

Diese Art der Verknüpfung ist lediglich eine andere Form eines vollständigen Tabellenscans, mit dem Unterschied, dass die Scan-Reihenfolge der Reihenfolge der Indizes entspricht. Diese Art des Scannens ruft Daten basierend auf dem Index aus der Tabelle ab. Im Vergleich zu „alle“ rufen beide die Daten der gesamten Tabelle ab, und der Index muss zuerst den Index lesen und dann zufällig Daten aus der Tabelle abrufen. Daher kann „Index“ nicht schneller sein als „alle“ (dieselben Tabellendaten abrufen). Aber warum heißt es im offiziellen Handbuch, dass seine Effizienz besser ist als die von „alle“? Der einzige mögliche Grund ist, dass die Daten der gesamten Tabelle beim Scannen entsprechend dem Index sortiert werden. Daher sind die Ergebnisse unterschiedlich und ein Effizienzvergleich macht keinen Sinn.
Wenn Sie die Effizienz wirklich vergleichen möchten, müssen Sie nur die Daten in dieser Tabelle abrufen und sortieren, um zu sehen, welche effizienter ist:

mysql> erläutern Sie „select * from employee order“ mit „no“;
+----+----------+----------+------+---------------+-----------+---------+---------+------+------+----------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+------+---------------+-----------+---------+---------+------+------+----------------+
| 1 | SIMPLE | Mitarbeiter | ALLE | NULL | NULL | NULL | NULL | 5 | Dateisortierung verwenden |
+----+----------+----------+------+---------------+-----------+---------+---------+------+------+----------------+
mysql> erläutern Sie „select * from employee order by rec_id“;
+----+----------+----------+-------+---------------+---------+---------+---------+---------+------+------+------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+-------+---------------+---------+---------+---------+---------+------+------+------+
| 1 | SIMPLE | Mitarbeiter | Index | NULL | PRIMARY | 4 | NULL | 5 | NULL |
+----+----------+----------+-------+---------------+---------+---------+---------+---------+------+------+------+

Wie oben zu sehen ist, ist der Verknüpfungstyp, der auf der Sortierung der Spalte „No“ basiert, der Typ „All“. Beachten Sie jedoch, dass die zusätzliche Spalte sortiert ist (mithilfe von Filesort), während der Verknüpfungstyp, der auf der Sortierung der Spalte „rec_id“ basiert, der Typ „Index“ ist und die Ergebnisse auf natürliche Weise ohne zusätzliche Sortierung geordnet sind. Vielleicht ist dieser Index aus diesem Grund effizienter als alle anderen, aber beachten Sie, dass hierfür dieselben Bedingungen erforderlich sind (d. h. eine Sortierung ist erforderlich).

Wenn der Verbindungstyp Typ ist und der Wert in der zusätzlichen Spalte „Index verwenden“ lautet, wird diese Situation als Indexabdeckung bezeichnet.
Was bedeutet Indexabdeckung? Stellen Sie sich folgendes Szenario vor: Wenn ein Xinhua-Wörterbuch eine Tabelle ist, ist natürlich der vorherige Indexteil (vorausgesetzt, der Index basiert auf Radikalen) der Index dieser Tabelle, dann ist die Indexabdeckung gleichbedeutend mit dem Abrufen der ersten bis letzten Zeichen (alle Zeichen im Xinhua-Wörterbuch) basierend auf dem Radikalindex. Wir erhalten alle Wörter im Wörterbuch, müssen aber nicht einmal in der Tabelle nachschlagen, da alle gewünschten Wörter bereits im Index enthalten sind, d. h. der Index umfasst.

mysql> erläutern Sie „select rec_id from employee“;
+----+----------+----------+-------+---------------+---------+---------+---------+---------+------+---------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+-------+---------------+---------+---------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | Mitarbeiter | Index | NULL | PRIMARY | 4 | NULL | 5 | Index wird verwendet |
+----+----------+----------+-------+---------------+---------+---------+---------+---------+------+---------+-------------+

Im obigen Beispiel ist die erhaltene rec_id zufällig die Indexspalte, es besteht also keine Notwendigkeit, zur Tabelle zurückzukehren, um Daten abzurufen.

Reichweite

Mit Range ist ein bereichsbezogener Indexscan gemeint. Im Vergleich zum vollständigen Indexscan eines Indexes weist er eine Bereichseinschränkung auf und ist daher einem Index überlegen. Bereiche sind relativ einfach zu verstehen. Sie müssen sich merken, dass Bereiche, wenn sie auftreten, auf einem Index basieren müssen. Neben den offensichtlichen Zeichen „zwischen“, „und“, „>“, „<“, „in“ und „oder“ gibt es auch Indexbereichsscans.

Referenz

Die Bedingungen für das Auftreten dieses Verbindungstyps sind: Die Suchbedingungsspalte verwendet einen Index und ist kein Primärschlüssel oder eindeutig. Tatsächlich bedeutet dies, dass der Index zwar verwendet wird, der Wert der Indexspalte jedoch nicht eindeutig ist und Duplikate vorhanden sind. Auf diese Weise kann der Vorgang nicht gestoppt werden, auch wenn das erste Datum mithilfe des Index schnell gefunden wird, und es muss ein kleiner Bereichsscan in der Nähe des Zielwerts durchgeführt werden. Der Vorteil liegt jedoch darin, dass nicht die gesamte Tabelle gescannt werden muss, da der Index geordnet ist und selbst wenn doppelte Werte vorhanden sind, diese nur in einem sehr kleinen Bereich gescannt werden. Um diese Situation zu demonstrieren, fügen Sie der Namensspalte in der Mitarbeitertabelle einen gemeinsamen Schlüssel hinzu (doppelte Werte sind zulässig).

 Tabelle „Mitarbeiter“ ändern, Schlüssel „I_EMPLOYEE_NAME“ hinzufügen (`Name`);

Als nächstes wählt der MySQL-Optimierer bei der Suche nach Daten nach Namen in der Mitarbeitertabelle den Ref-Verbindungstyp aus.

mysql> erläutern Sie „select * from employee“, wobei „Name“ = „Mitarbeiter“;
+----+----------+----------+------+----------------+----------------+---------+-----------+------+-----------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+------+----------------+----------------+---------+-----------+------+-----------------------+
| 1 | SIMPLE | Mitarbeiter | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 | const | 1 | Indexbedingung verwenden |
+----+----------+----------+------+----------------+----------------+---------+-----------+------+-----------------------+

ref_eq

Was ist der Vorteil von ref_eq im Vergleich zu ref? Es besteht darin, dass es weiß, dass für diese Art der Suche nur ein Ergebnisset vorhanden ist. Unter welchen Umständen gibt es nur einen Ergebnissatz? Dies ist der Fall, wenn für die Suche der Primärschlüssel oder eindeutige Index verwendet wird. Wenn wir beispielsweise anhand der Matrikelnummer nach einem Schüler einer bestimmten Schule suchen, wissen wir vor der Suche, dass es nur ein Ergebnis geben darf. Wenn wir die Matrikelnummer zum ersten Mal finden, beenden wir die Suche sofort. Diese Art der Verbindung führt jedes Mal präzise Abfragen durch, ohne übermäßiges Scannen, sodass die Sucheffizienz höher ist. Natürlich muss die Eindeutigkeit der Spalte basierend auf der tatsächlichen Situation bestimmt werden.
In einer einzelnen Tabelle habe ich viele Methoden ausprobiert, um den Verbindungstyp von ref_eq zu erhalten, aber meistens war es const, also musste ich eine Tabelle verbinden, um den gewünschten Verbindungstyp zu erhalten. Der Tabellenerstellungscode für diese Tabelle lautet: (Der Blogger ist faul und hat zwei unabhängige Tabellen verbunden, o(╯□╰)o)

CREATE TABLE `Punktzahl` (
 `rec_id` INT(11) NICHT NULL AUTO_INCREMENT,
 `stu_id` INT(11) NICHT NULL,
 `mark` INT(11) NICHT NULL STANDARD '0',
 PRIMÄRSCHLÜSSEL (`rec_id`),
 EINDEUTIGER SCHLÜSSEL `UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 STANDARD-CHARSET=utf8

Es gibt fünf Datensätze in der Mitarbeitertabelle und fünf entsprechende Datensätze in der Punktetabelle, wobei die „rec_id“ des Mitarbeiters und die „stu_id“ des Punktes einander eins zu eins entsprechen.

mysql> erläutern Sie „select ep.name,sc.mark“ aus „employee ep,score sc“, wobei ep.rec_id = sc.stu_id;
+----+----------+----------+--------+-----------------+---------+---------+-----------------+------+----------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+--------+-----------------+---------+---------+-----------------+------+----------+
| 1 | EINFACH | sc | ALLE | UK_SCORE_STU_ID | NULL | NULL | NULL | 5 | NULL |
| 1 | EINFACH | ep | eq_ref | PRIMARY | PRIMARY | 4 | my_db.sc.stu_id | 1 | NULL |
+----+----------+----------+--------+-----------------+---------+---------+-----------------+------+----------+

Aus dem Obigen können wir erkennen, dass die Score-Tabelle ein Full Table Scan-Typ ist. Zeilen = 5 bedeutet, dass die äußere Tabelle fünfmal durchlaufen wird (weil es fünf Datenstücke gibt). Aber warum sind die Zeilen der Mitarbeitertabelle 1? Wie ist das möglich? Ich war am Anfang auch sehr verwirrt. Dies hängt eng mit dem Abfrageprinzip von MySQL zusammen. Zeilen spiegeln tatsächlich die Anzahl der inneren Schleifen der Abfrage wider. Für jede Datenübereinstimmung in der äußeren Schicht kann der Mitarbeiter mit einem Schuss getroffen werden, also sind Zeilen 1.

Konstante

Wenn Sie als bedingte Abfrage einen Primärschlüssel nach der Where-Klausel platzieren, kann der MySQL-Optimierer die Abfrage normalerweise in eine Konstante optimieren. Wie und wann die Konvertierung erfolgt, hängt vom Optimierer ab.

Zusammenfassen

Explain ist wie ein Spiegel. Denken Sie daran, es nach dem Schreiben Ihres SQL zu erklären. Gleichzeitig habe ich beim Schreiben von Artikeln festgestellt, dass es viele Dinge und Details gibt, die nicht so leicht zu verstehen sind. Dazu ist ein klares Verständnis der zugrunde liegenden Abfrage- und Betriebsprinzipien des Betriebssystems und der Datenbank erforderlich. Gleichzeitig basieren mehrere Typen fast alle auf Indizes, sodass wir ein tiefes Verständnis von Indizes haben müssen. Die Ergebnisse der Erläuterung können uns zeigen, wann wir Indizes hinzufügen und wann nicht, sodass wir Indizes besser nutzen können.

Oben finden Sie eine ausführliche Erläuterung der Erklärungsarten in MySQL. Weitere Informationen zur Erklärungsart in MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!