Zusammenfassung der wichtigsten Wissenspunkte zur MySQL-Abfrageoptimierung

Vorwort

Abfrageoptimierung ist nichts, was über Nacht erreicht werden kann. Sie müssen lernen, die entsprechenden Tools zu verwenden, aus den Erfahrungen anderer lernen, um SQL zu optimieren, und sich selbst verbessern.

Lassen Sie uns zunächst die Vorteile von Indizes zusammenfassen: schneller Datenabruf, stabile Abfrage, sequentielle Speicherung, um zu vermeiden, dass der Server temporäre Tabellen erstellt, und Umwandlung von zufälligem I/O in geordneten I/O.

Wenn der Index jedoch nicht standardisiert erstellt wird, führt dies zu folgenden Problemen: Er beansprucht zusätzlichen Speicherplatz, verschwendet Speicher und verringert die Leistung beim Hinzufügen, Löschen und Ändern von Daten.

Daher können effiziente Indizes nur basierend auf einem Verständnis der Indexdatenstruktur erstellt werden.

**Alle Vorgänge in diesem Artikel werden in MySQL 8.0.12 ausgeführt**

1. Indexspezifikationen erstellen

Bevor Sie die Indexoptimierung erlernen, müssen Sie über ein gewisses Verständnis der Spezifikationen zum Erstellen von Indizes verfügen, die aus dem Alibaba-Entwicklungshandbuch stammen.

Primärschlüsselindex: pk_column_column

Eindeutiger Index: uk_column_column

Gemeinsamer Index: idx_column_column

2. Gründe für Indexfehler

Beim Erstellen eines Index müssen Sie wissen, unter welchen Umständen der Index fehlschlägt. Nur wenn Sie die Gründe für Indexfehler verstehen, können Sie einige bekannte Fehler beim Erstellen des Index vermeiden.

1. Der Anführer kann nicht sterben

Diese klassische Aussage beinhaltet die Tatsache, dass Sie beim Erstellen eines Index das Left-Extreme-Prinzip einhalten müssen.

Die Tabellenstruktur ist beispielsweise u_id，u_name，u_age，u_sex，u_phone，u_time

Erstellen Sie einen Index mit dem Namen idx_user_name_age_sex .

Die Abfragebedingung muss die Spalte u_name enthalten.

2. Führen Sie keine Operationen an der Indexspalte durch

Führen Sie keine Berechnungen, Funktionen oder automatischen oder manuellen Typkonvertierungen an den Indexspalten durch, da sonst ein vollständiger Tabellenscan durchgeführt wird. Kurz gesagt: Führen Sie keine Operationen an der Indexspalte durch.

3. Die Typen der beiden Seiten sind nicht gleich

Beispielsweise wird der Index idx_user_name erstellt und der Name-Feldtyp ist varchar

Verwenden Sie bei der Abfrage where name = kaka . Diese Abfragemethode führt direkt zum Indexfehler.

Die korrekte Verwendung ist where name = "kaka" .

4. Ungeeignete Like-Abfragen können zu Indexfehlern führen

Erstellen Sie einen Index mit dem Namen idx_user_name

Die Ausführungsanweisung lautet select * from user where name like "kaka%"; und der Index erreicht werden kann.

Die Ausführungsanweisung lautet select name from user where name like "%kaka"; der Index kann verwendet werden (nur in Versionen über 8.0).

Die Ausführungsanweisung lautet select * from user where name like ''%kaka"; was direkt zum Fehlschlagen des Index führt.

5. Der Index nach der Bereichsbedingung wird ungültig

Erstellen Sie einen Index mit dem Namen idx_user_name_age_sex

Führen Sie die Anweisung select * from user where name = 'kaka' and age > 11 and sex = 1;

Die obige SQL-Anweisung trifft nur auf die Namens- und Altersindizes, der Geschlechtsindex ist ungültig.

Wenn ein zusammengesetzter Index fehlschlägt, müssen Sie nur die Länge von key_len überprüfen.

Zusammenfassung: % Der Index wird später angefordert. Wenn ein überdeckender Index verwendet wird, kann jede Abfragemethode auf den Index zugreifen.

Oben ist Kakas Zusammenfassung der Gründe, warum Indizes fehlschlagen können. In vielen Artikeln ist die MySQL-Version nicht gekennzeichnet, sodass Sie möglicherweise die Schlussfolgerung sehen, dass sie null ist oder dass Indizes fehlschlagen können.

3. Erklären Sie das Killer-Feature der SQL-Optimierung

Nachdem Sie die SQL-Anweisung geschrieben haben, müssen Sie sie unbedingt mit Explain überprüfen, um zu sehen, ob sie den Index trifft.

Die folgende Abbildung zeigt das Ausgabeformat mit „explain“. Das Ausgabeformat wird im Folgenden kurz erläutert.

1. Die Spalte „id“ ist die Abfrage-ID. Wenn in der Abfrageanweisung keine Unterabfrage oder gemeinsame Abfrage vorhanden ist, ist diese ID immer 1.

Wenn eine Unterabfrage oder eine Union-Abfrage vorhanden ist, wird diese Zahl erhöht.

2.Typ auswählen

Die gebräuchlichsten Typen sind SIMPLE und PRIMARY und Sie müssen nur diese Spalten kennen.

3.Tabelle

Verstehen Sie es einfach als den Tabellennamen

4. **Typ

Diese Spalte ist eine der wichtigsten Spalten, auf die Sie bei der Optimierung von SQL-Anweisungen achten müssen. Diese Spalte zeigt, welchen Typ die Abfrage verwendet.

Die folgenden sind in der Reihenfolge vom Besten bis zum Schlechtesten aufgeführt.

• System: Es gibt nur eine Datenzeile in der Tabelle
• const: Es stimmt höchstens ein Datensatz überein. Wird häufig für bedingte Abfragen mit Primärschlüsseln oder eindeutigen Indizes verwendet.
• eq_ref: erscheint, wenn der von der Verbindung verwendete Index der Primärschlüssel und eindeutig ist
• ref: Die Verwendung der normalen Indizierungsoperatoren = oder <=> für den Vergleich führt zu
• fulltext: Volltextindex verwenden
• ref_or_null: Ähnlich dem Ref-Typ, fügt aber eine Nullwertprüfung hinzu, die in der Praxis nicht oft verwendet wird. Die Anweisung lautet: where name = 'kaka' and name is null，name ist ein normaler Index.
• index_merge: Die Abfrageanweisung verwendet mehr als zwei Indizes. Dies ist bei der Verwendung von and oder or üblich. Das offizielle Dokument setzt diesen Typ nach ref_or_null. In vielen Fällen ist die Leistung jedoch möglicherweise nicht so gut wie bei range, da zu viele Indizes gelesen werden.
• unique_subquery: wird in Abfragen verwendet, ersetzt die Unterabfrage vollständig und ist effizienter. Die Anweisung lautet value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery: Die in der Unterabfrage zurückgegebene Kombination von Feldern ist ein Index (oder eine Indexkombination), jedoch kein Primärschlüssel oder eindeutiger Index.
• Bereich: Indexbereichsabfrage, häufig verwendet in Abfragen mit Operatoren wie =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() oder ähnlichen.
• Index: Vollständiger Tabellenscan des Index, scannt den Index von Anfang bis Ende
• alle: vollständiger Tabellenscan, schlechteste Leistung.

5.mögliche Schlüssel

In dieser Spalte werden die möglichen Indizes angezeigt, die verwendet werden können.

6. **Schlüssel

Der vom Optimierer gefundene Index aus Possible_Keys

7.Schlüssellänge

Die Länge (Anzahl der Bytes) des für die Abfrage verwendeten Indexes. key_len berechnet nur die Länge des in der Where-Bedingung verwendeten Indexes. Auch wenn der Index zum Sortieren und Gruppieren verwendet wird, wird er in key_len nicht berechnet.

8.ref

Wenn Sie eine Abfrage mit konstantem gleichen Wert verwenden, wird hier „const“ angezeigt.

Wenn es sich um eine Join-Abfrage handelt, zeigt der Ausführungsplan der gesteuerten Tabelle die zugehörigen Felder der gesteuerten Tabelle an.

Wenn die Bedingung einen Ausdruck oder eine Funktion verwendet oder eine interne implizite Konvertierung in der Bedingungsspalte erfolgt, wird sie möglicherweise als „func“ angezeigt.

9. **Zeilen

Dies ist die MySQL-Schätzung der Anzahl der Zeilen, die gescannt werden müssen (kein genauer Wert).

Dieser Wert zeigt direkt die Effizienz von SQL. Grundsätzlich gilt: je weniger Zeilen, desto besser.

10.gefiltert

Diese Spalte gibt den Anteil der Anzahl der Datensätze an, die der Abfrage entsprechen, nachdem die von der Speicher-Engine zurückgegebenen Daten auf Serverebene gefiltert wurden. Beachten Sie, dass es sich um einen Prozentsatz und nicht um eine bestimmte Anzahl von Datensätzen handelt.

11. **extra

In den meisten Fällen treten die folgenden Situationen auf.

• Verwenden eines Index: Es wird ein überdeckender Index verwendet und die Abfragespalten sind alle Indexfelder.
• Verwenden von where: Verwenden der where-Anweisung
• Temporäre Tabelle verwenden: Beim Sortieren der Abfrageergebnisse wird eine temporäre Tabelle verwendet.
• Verwenden von Filesort: Verwenden Sie einen externen Index zum Sortieren der Daten
• Indexbedingung verwenden: Index-Pushdown wird verwendet. Weitere Informationen zum Index-Pushdown finden Sie in Kakas vorherigem Artikel MySQL Index

12. Zusammenfassung

Das Obige ist die Beschreibung aller Spalten in Explain. Im normalen Entwicklungsprozess achten wir normalerweise nur auf Typ, Schlüssel, Zeilen und zusätzliche Spalten.

• Das Ziel der Typoptimierung muss mindestens die Bereichsebene erreichen, und die Anforderung ist die Referenzebene. Wenn Konstanten möglich sind, ist das am besten.
• Schlüssel ist der für die Abfrage verwendete Index. Wenn diese Spalte leer ist, wurde der Index entweder nicht erstellt oder ist ungültig.
• Zeilen ist die Anzahl der von dieser SQL-Anweisung gescannten Zeilen. Je weniger, desto besser.
• extra: Diese Spalte ist eine erweiterte Spalte. Wenn temporäre Tabellen oder Dateisortierungen vorkommen, ist eine Optimierung erforderlich.

4. SQL-Optimierungskiller: Langsame Abfrage

Wie oben erwähnt, können Sie „explain“ direkt verwenden, um zu analysieren, ob Ihre SQL-Anweisungen sinnvoll sind. Als Nächstes sprechen wir über langsame Abfragen.

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

Prüfen Sie, ob SQL-Anweisungen, die keine Indizes verwenden, protokolliert werden

Aktivieren Sie langsame Abfragen und zeichnen Sie SQL-Anweisungen auf, die keine Indizes verwenden

Setzen Sie global log_queries_not_using_idnexes='on'.

Setzen Sie global log_queries_not_using_indexes='on'.

Überprüfen Sie, ob die beiden oben genannten Konfigurationen aktiviert sind

Stellen Sie die langsame Abfragezeit ein, die Sie selbst steuern, normalerweise ist 1 Sekunde ausreichend. set globle long_query_time=1;

Wenn sich die Zeit nicht geändert hat, verbinden Sie den Client einfach erneut.

Anzeigen des Speicherorts für langsame Abfragen

Führen Sie dann eine beliebige Anweisung aus, die den Index nicht ausführt. Sie können diese Anweisung in diesem Protokoll sehen

In der obigen Abbildung sind vor allem die Abfragezeit und der Inhalt der SQL-Anweisung zu beachten.

Oben erfahren Sie, wie Sie mithilfe langsamer Abfragen SQL-Anweisungen anzeigen, bei denen es im Projekt zu Problemen kommt.

5. Optimierungsmethode

Hier werde ich Ihnen einige häufig verwendete Lösungen zur Optimierung von SQL-Anweisungen vorstellen. Die beiden oben genannten Tools sollten gut eingesetzt werden, um uns beim Kampf gegen Monster zu helfen.

• Es ist verboten, select * zu verwenden. Welche Felder werden benötigt, um welche Felder abzufragen?
• wobei das Feld den Index festlegt
• Festlegen des Index für die Felder „Gruppieren nach“ und „Sortieren nach“
• Verzichten Sie auf Offset und begrenzen Sie das Paging und verwenden Sie die verzögerte Zuordnung, um das Paging zu implementieren (nicht erforderlich, wenn die Datenmenge nicht groß ist).
• Wenn Sie Paging schreiben und die Anzahl 0 ist, kehren Sie direkt zurück, um die Ausführung von Paging-Anweisungen zu vermeiden
• Verwenden Sie abdeckende Indizes, um Tabellenrückgaben zu vermeiden
• Beim Erstellen eines zusammengesetzten Index wird der Index mit der höchsten Unterscheidungskraft ganz links platziert.
• Um die Anzahl der Datenzeilen zu zählen, verwenden Sie einfach count(*) anstelle von Fachwörtern.
• Bezüglich in und exists: Wenn die beiden abgefragten Tabellen dieselbe Größe haben, kann der Leistungsunterschied ignoriert werden. Wenn die Unterabfragetabelle groß ist, verwenden Sie exist, andernfalls in.
• Beim Abfragen einer Datenzeile Limit 1 hinzufügen
• Wählen Sie einen sinnvollen Datentyp. Je kleiner der Datentyp, desto besser.
• Eine Union-Abfrage verknüpft bis zu drei Tabellen und die Datentypen der zu verknüpfenden Felder müssen konsistent sein.
• Wenn der In-Vorgang vermieden werden kann, versuchen Sie, ihn zu vermeiden. Wenn er nicht vermieden werden kann, halten Sie die Anzahl der In-Elemente innerhalb von 1000.
• Spalten mit häufigen Datenaktualisierungen und geringer Differenzierung sind nicht für die Indizierung geeignet.
• Der Typ in „explain“ muss mindestens range und ref sein.
• Der gemeinsame Index erfüllt das Leftmost-Prinzip

VI. Fazit

Damit ist dieser Artikel zu den wesentlichen Wissenspunkten für die MySQL-Abfrageoptimierung abgeschlossen. Weitere relevante Inhalte zur MySQL-Abfrageoptimierung 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!