Bei der SQL-Optimierung treten häufig Fehler auf, weil Sie die Verwendung von MySQL nicht verstehen.

1. Vorbereitung

Bereiten Sie drei Tabellen vor: eine Charaktertabelle, eine Ausrüstungstabelle und eine Basisdatentabelle. Hier zeigen wir nur einige der im Tutorial benötigten Felder. Im Laufe der Spieleentwicklung müssen mehr als diese Felder vorhanden sein. Ich denke, jeder versteht das.

Rollentabelle:

CREATE TABLE `Rolle` (
  `n_role_id` int DEFAULT NULL,
  `s_name` varchar(255) ZEICHENSATZ utf8mb4 SORTIMENT utf8mb4_bin STANDARD NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Ausrüstungsliste:

CREATE TABLE `ausrüsten` (
  `n_equip_id` int DEFAULT NULL,
  `s_equip_name` varchar(255) Zeichensatz utf8mb4 Sortiert nach utf8mb4_bin Standardwert NULL,
  `n_config_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Gerätekonfigurationstabelle

TABELLE `dict_equip` erstellen (
  `n_equip_id` int DEFAULT NULL,
  `s_desc` varchar(255) ZEICHENSATZ utf8mb4 SORTIMENT utf8mb4_bin STANDARD NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. Einführung in den Erklärungsplan

Es gibt zwei Möglichkeiten, den Erläuterungsplan anzuzeigen:

1. Befehlsmethode: „explain sql“ oder „desc sql“, beide Befehle sind in Ordnung. Ich denke, es ist besser, sich „explain“ zu merken, die Worte sind sehr direkt.

2. Benutze das Tool Navicat (andere Tools kenne ich nicht, aber es gibt vermutlich auch welche). Klicke im Abfragefenster auf „Explain“. Das Schlüsselwort „explain“ brauchst du nicht hinzuzufügen.

Sie können sehen, dass das Ergebnis viele Spalten enthält, von denen einige null sind und einige Werte haben. Solange wir den Erklärungsplan verstehen, können wir das SQL gezielt optimieren.

3. Detaillierte Erläuterung der Felder

Es gibt ziemlich viele Felder im Erläuterungsplan. Navicat zeigt 12 Felder an. Wir müssen einigen von ihnen besondere Aufmerksamkeit schenken, und es ist gut, einfach zu wissen, was mit einigen von ihnen passiert.

Offizielle Dokumentationserklärung: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1. Reihenfolge der ID-Ausführung

id ist die Ausführungsreihenfolge von select. Je größer die ID ist, desto höher ist die Priorität und desto früher wird sie ausgeführt. Wenn die ID gleich ist, wird die folgende zuerst ausgeführt .

Der Grund dafür ist, dass bei der Ausführung einer Unterabfrage zuerst die innere Schicht und dann die äußere Schicht überprüft wird

WÄHLEN
    de.*
AUS
    dict_equip de
WO
    de.n_equip_id = (
        Wählen Sie n_equip_id aus, um auszurüsten, WHERE
            e.n_role_id = (
                SELECT n_role_id FROM Rolle r WHERE r.s_name = 'Koriander' )
    ) 

Aus dem obigen Ausführungsplan können wir ersehen, dass zuerst die Abfrage der Rollentabelle ausgeführt wird, dann „equip“ und schließlich „dict_equip“.

2. select_type Typ auswählen

3. Tabellenabfragen beinhalten Tabellen oder abgeleitete Tabellen

Die derzeit für die Ausgabe verwendeten Tabellen können folgender Art sein:

<union M , N >: Die Zeilendaten sind die Daten nach der Vereinigung. Die ID liegt zwischen m und n.

<derived*N*>: abgeleitete Tabelle

<subquery N >: Unterabfrage

4. Partitionsabfrage beinhaltet Partitionen

Es kann nur bei Verwendung der Partitionstabelle verwendet werden. Diese erweiterte Funktion wurde bisher nicht verwendet.

5. Art der Abfrage

Gibt an, wie MySQL die erforderliche Zeile in der Tabelle findet. Dies wird auch als „Zugriffstyp“ bezeichnet. Gängige Typen sind die folgenden:

Leistung: alle < Index < Bereich < Index_Merge < Ref_or_null < Ref < Eq_Ref < System/Const

Von links nach rechts, vom Schlechtesten zum Besten

Wenn bei der Optimierung eine große Menge an abgefragten Daten erforderlich ist, können Sie einen vollständigen Tabellenscan verwenden, um die Verwendung von Indizes zu vermeiden.

Wenn Sie nur eine kleine Datenmenge abfragen, versuchen Sie, Indizes zu verwenden.

6. Possible_keys: voraussichtlich zu verwendende Indizes

Wenn keine Verknüpfung mit anderen Tabellen besteht, ist die Abfragetabelle der mögliche Index, der verwendet werden kann.

7. Schlüssel: der im eigentlichen Abfrageprozess verwendete Index

Zeigt den Index an, der von MySQL tatsächlich in der Abfrage verwendet wird. Wenn kein Index verwendet wird, wird NULL angezeigt.

8. 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.

9. ref zeigt an, mit welchem ​​Feld welcher Tabelle das Indexfeld der Tabelle verknüpft ist

Hinweis: Ich habe Indizes zu den Tabellen „equip“ und „dict_equip“ hinzugefügt und die Indexspalte ist „n_equip_id“

Wie aus dem obigen Ausführungsplan ersichtlich, wird zuerst der Index verwendet.

10. Zeilen: Schätzen Sie anhand der Tabellenstatistik und der Auswahl grob die Anzahl der zu findenden oder zu lesenden Datensätze oder Zeilen. Je kleiner der Wert, desto besser.

Beispielsweise ist eine Spalte eindeutig, obwohl kein Index vorhanden ist. Wenn während der Suche die gesamte Tabelle gelesen wird, entspricht der Wert der Datenmenge in der Tabelle. Zu diesem Zeitpunkt besteht die Optimierung darin, so wenig Tabellen wie möglich zu lesen. Sie können Indizes hinzufügen, um die Anzahl der gelesenen Zeilen zu verringern.

11. gefiltert: Der Prozentsatz der zurückgegebenen Zeilen als Prozentsatz der gelesenen Zeilen. Je größer der Wert, desto besser.

Wenn die gesamte Tabelle beispielsweise 100 Datensätze enthält, können alle Daten in der Tabelle gelesen werden, aber nur ein Datensatz stimmt überein. In diesem Fall beträgt der Prozentsatz 1. Daher müssen Sie dieses Verhältnis so groß wie möglich machen, d. h. die gelesenen Daten sollten so nützlich wie möglich sein und das Lesen nicht verwendeter Daten vermeiden, da IO sehr zeitaufwändig ist.

12. Extra

Die häufigsten sind die folgenden

use filesort: MySQL benötigt einen zusätzlichen Durchlauf, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden. Wenn dieser Wert wahr ist, sollte der Index optimiert werden.

Temporär verwenden: Um die Abfrage aufzulösen, muss MySQL eine temporäre Tabelle erstellen, um die Ergebnisse zu speichern. Ein typischer Fall ist, wenn die Abfrage GROUP BY- und ORDER BY-Klauseln enthält, die Spalten in unterschiedlichen Fällen auflisten.

Index verwenden: Rufen Sie Spalteninformationen aus einer Tabelle ab, indem Sie nur die Informationen im Indexbaum verwenden, ohne weitere Suche durchzuführen, um die tatsächlichen Zeilen zu lesen. Diese Strategie kann verwendet werden, wenn die Abfrage nur Spalten verwendet, die Teil eines einzelnen Indexes sind.

use where: where-Klausel wird verwendet, um einzuschränken, welche Zeile

Zusammenfassen

Das Prinzip der SQL-Optimierung besteht darin, die Zeit zu verkürzen und gleichzeitig die Richtigkeit sicherzustellen. Das Ziel ist klar. Indem Sie das Ziel zurückstellen, können Sie wissen, dass Sie, wenn Sie schnell ausführen möchten, so wenig Daten wie möglich lesen müssen. Es gibt nur zwei Hauptmethoden, um die Menge der gelesenen Daten zu reduzieren: Filtern und Verwenden von Indizes. Optimieren Sie innerhalb dieser Regeln, aber beachten Sie, dass Indizes zusätzlichen Speicherplatz beanspruchen, sodass Sie das Verhältnis zwischen beiden ausbalancieren müssen.

Dies ist das Ende dieses Artikels über SQL-Optimierungsfehler, weil Sie die Verwendung des MySQL-Erklärungsplans nicht verstehen. Weitere Informationen zur SQL-Optimierung und zum MySQL-Erklärungsplan finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!