Detailliertes Beispiel zum Auffinden und Optimieren langsamer SQL-Abfragen in MySQL

1. So finden und optimieren Sie langsame SQL-Abfragen

a. Lokalisieren Sie langsame SQL-Abfragen anhand langsamer Protokolle

SHOW VARIABLES LIKE '%query%' um Informationen zum langsamen Protokoll abzufragen

slow_query_log ist standardmäßig deaktiviert. Wenn Sie es verwenden möchten, müssen Sie es aktivieren.

slow_query_log_file zeichnet die langsame Protokolldatei auf

Der Standardwert von long_query_time beträgt 10 s. Jedes Mal, wenn die ausgeführte SQL-Anweisung diese Zeit erreicht, wird sie aufgezeichnet.

SHOW STATUS LIKE '%slow_queries%' um den Status langsamer Abfragen anzuzeigen

Slow_queries zeichnet die Anzahl langsamer Abfragen auf. Wenn eine SQL-Anweisung langsam ausgeführt wird, ist dieser Wert 1 (Aufzeichnung der Anzahl langsamer SQL-Anweisungen in dieser Sitzung).

Beachten:

So aktivieren Sie langsame Abfragen: SET GLOBAL slow_query_log = ON;

Ändern Sie die Standardzeit auf 1 S: SET GLOBAL long_query_time = 1;

(Nach dem Einstellen musst du dich erneut mit der Datenbank verbinden. PS: Wenn du es nur hier änderst, werden bei einem erneuten Neustart des Datenbankdienstes alle Einstellungen automatisch auf die Standardwerte zurückgesetzt. Für dauerhafte Änderungen musst du es in my.ini ändern.)

b. Verwenden Sie Tools wie „explain“, um SQL zu analysieren

Fügen Sie vor dem auszuführenden SQL eine Erklärung hinzu. Beispiel: EXPLAIN SELECT menu_name FROM t_sys_menu ORDER BY menu_id DESC;

Schauen Sie sich als nächstes die Schlüsselfelder an

Typ:

Wenn sich herausstellt, dass der Wert des Typs einer der letzten beiden ist, muss die Beweisanweisung optimiert werden.

Extra:

c. Ändern Sie SQL oder versuchen Sie, SQL durch den Index laufen zu lassen

Der MySQL-Abfrageoptimierer bestimmt je nach Situation, welcher Index verwendet werden soll. Er verwendet nicht unbedingt den Primärschlüssel (der Schlüssel in „explain“ kann zeigen, welcher Schlüssel verwendet wird). Die konkrete Situation hängt von der konkreten Situation ab. Wenn Sie die Verwendung eines bestimmten Schlüssels erzwingen möchten:

Fügen Sie am Ende der Abfrage „Force Index (Primary)“ hinzu, um den Primärschlüssel zu erzwingen.

2. Die Ursache des am weitesten links liegenden Übereinstimmungsprinzips des gemeinsamen Index

Erklären Sie kurz, was das Leftmost-Matching-Prinzip ist

Wie der Name schon sagt: ganz links zuerst, jeder aufeinanderfolgende Index beginnend ganz links kann abgeglichen werden. Wenn gleichzeitig eine Bereichsabfrage (>, <, zwischen, wie) gefunden wird, wird der Abgleich beendet.

Beispiel: b = 2. Wenn Sie einen Index in der Reihenfolge (a, b) erstellen, stimmt dieser nicht mit dem Index (a, b) überein. Wenn die Abfragebedingung jedoch a = 1 und b = 2 oder a = 1 (oder b = 2 und b = 1) lautet, funktioniert es, da der Optimierer die Reihenfolge von a und b automatisch anpasst. Wenn beispielsweise a = 1 und b = 2 und c > 3 und d = 4 ist und ein Index in der Reihenfolge (a, b, c, d) erstellt wird, wird d nicht indiziert, da das Feld c eine Bereichsabfrage ist und die Felder danach nicht mehr übereinstimmen.

Das Prinzip des Leftmost-Matching-Prinzips

Das Prinzip der ganz linken Übereinstimmung gilt für gemeinsame Indizes. Daher ist es für uns notwendig, das Prinzip gemeinsamer Indizes zu verstehen. Nachdem Sie den gemeinsamen Index verstanden haben, werden Sie verstehen, warum es das Prinzip der ganz linken Übereinstimmung gibt.

Wir alle wissen, dass die zugrunde liegende Schicht des Index ein B + -Baum ist, daher ist der gemeinsame Index natürlich immer noch ein B + -Baum, aber die Anzahl der Schlüsselwerte im gemeinsamen Index ist nicht eins, sondern ein Vielfaches. Ein B+-Baum kann nur basierend auf einem Wert erstellt werden, daher erstellt die Datenbank den B+-Baum basierend auf dem äußersten linken Feld des gemeinsamen Index.

Beispiel: Wenn Sie einen gemeinsamen Index (a, b) erstellen, sieht sein Indexbaum folgendermaßen aus:

Sie können sehen, dass die Werte von a in der Reihenfolge 1, 1, 2, 2, 3, 3 sind, während die Werte von b nicht in der Reihenfolge 1, 2, 1, 4, 1, 2 sind. Daher kann die Abfragebedingung b = 2 den Index nicht verwenden, da der gemeinsame Index zuerst nach a sortiert wird und b ungeordnet ist.

Gleichzeitig können wir auch feststellen, dass, wenn die a-Werte gleich sind, die b-Werte in der richtigen Reihenfolge angeordnet sind, diese Reihenfolge jedoch relativ ist. Daher wird das Übereinstimmungsprinzip ganz links beendet, wenn eine Bereichsabfrage auftritt, und die verbleibenden Felder können keine Indizes verwenden. Wenn beispielsweise a = 1 und b = 2 ist, können sowohl das a- als auch das b-Feld den Index verwenden, da b relativ geordnet ist, wenn der a-Wert bestimmt wird. Wenn a>1 und b=2 ist, kann das a-Feld mit dem Index übereinstimmen, der b-Wert jedoch nicht, da der a-Wert ein Bereich ist und b innerhalb dieses Bereichs ungeordnet ist.

Ursache:

Wenn Sie einen gemeinsamen Index wie (Spalte 3, Spalte 2) durchsuchen, können Sie sehen, dass es sich auch um eine B+-Baumstruktur handelt, die nach unten sucht. Wenn Sie direkt in Spalte 2 suchen, können Sie 34 und 77 nicht direkt finden. Dieser gemeinsame Index wird nicht mehr benötigt.

3. Ist es besser, mehr Indizes zu erstellen?

1. Tabellen mit kleinen Datenmengen müssen nicht indiziert werden, da durch die Indizierung zusätzlicher Index-Overhead entsteht.

2. Datenänderungen erfordern eine Indexwartung. Mehr Indizes bedeuten also höhere Wartungskosten.

3. Mehr Indizes bedeuten, dass mehr Speicherplatz benötigt wird.

Zusammenfassen

Dies ist das Ende dieses Artikels zum Auffinden und Optimieren von langsamen MySQL-Abfragen in SQL. Weitere relevante Inhalte zum Auffinden und Optimieren von langsamen MySQL-Abfragen in SQL finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!