Fallstricke bei langsamen MySQL-Abfragen

Was sind die Folgen einer langsamen Abfrage? Als ich jung war, dachte ich immer, dass die Datenrückgabe einfach langsamer wäre und das Benutzererlebnis schlechter wäre. Tatsächlich ist es viel mehr als das. Ich habe mehrere Online-Unfälle erlebt, einer davon wurde durch eine langsame SQL-Abfrage verursacht.

Ich erinnere mich, dass es eine SQL-Abfrage war. Bei einem Datenvolumen von mehreren Zehntausend Sekunden dauerte es immer noch 0,2 Sekunden. Da die Datenmenge im Laufe der Zeit stark zunahm, betrug der Zeitaufwand einmal 2-3 Sekunden! Es wird kein Index gefunden, was zu einem vollständigen Tabellenscan führt. Das Extra in der Erklärung zeigt: Where verwenden; Temporär verwenden; Filesort verwenden. Wir sind gezwungen, temporäre Tabellensortierung zu verwenden. Aufgrund hochfrequenter Abfragen wird der DB-Threadpool schnell durch Parallelität gefüllt, was dazu führt, dass sich eine große Anzahl von Abfrageanforderungen stapelt. Die CPU des DB-Servers ist lange Zeit zu über 100 % ausgelastet und bei vielen Anforderungen kommt es zu Zeitüberschreitungen. . Schließlich stürzte das System ab. Der Boss ist hier~

Übrigens, es war der 2. Oktober, 20:30 Uhr. Ich war in meiner Heimatstadt Zaozhuang, saß mit meinen Kumpels an einem Essensstand und prahlte! Raten Sie mal, in welche peinliche Situation ich geraten werde?

Es ist ersichtlich, dass das Risiko sehr hoch ist, wenn das Team langsamen Abfragen nicht genügend Aufmerksamkeit schenkt. Nach diesem Unfall sagte unser Chef: Wenn ein ähnlicher Unfall noch einmal in irgendjemandes Code passiert, werden der Entwicklungs- und der Abteilungsleiter gemeinsam gefeuert. Das erschreckte viele Führungskräfte, also stellten sie schnell zwei DBA-Kollegen ein 🙂🙂🙂

Langsame Abfragen sind, wie der Name schon sagt, Abfragen, die sehr langsam ausgeführt werden. Wie langsam? Wenn der durch den Parameter „long_query_time“ festgelegte Zeitschwellenwert (Standard 10 s) überschritten wird, gilt die Abfrage als langsam und muss optimiert werden. Langsame Abfragen werden im Protokoll für langsame Abfragen aufgezeichnet.

Das Protokoll für langsame Abfragen ist standardmäßig nicht aktiviert. Wenn Sie SQL-Anweisungen optimieren müssen, können Sie diese Funktion aktivieren. So können Sie leicht erkennen, welche Anweisungen optimiert werden müssen (denken Sie daran, wie schrecklich es ist, wenn eine SQL-Anweisung 10 Sekunden dauert).

Murphys Gesetz: Was schiefgehen kann, wird schiefgehen.

Es ist eines dieser Dinge, die nur allzu real sind. Um Problemen vorzubeugen, bevor sie auftreten, sehen wir uns an, wie mit langsamen Abfragen umgegangen wird. Dieser Artikel ist sehr trocken, also denken Sie daran, sich ein Glas Wasser zu holen. Wenn Sie keine Zeit haben, ihn zu lesen, speichern Sie ihn bitte zuerst!

1. Langsame Abfragekonfiguration

1-1. Langsame Abfrage aktivieren

MySQL-Unterstützung durch

• 1. Geben Sie den Befehl ein, um langsame Abfragen (vorübergehend) zu aktivieren. Diese werden nach dem Neustart des MySQL-Dienstes automatisch deaktiviert.
• 2. Konfigurieren Sie die Systemdatei my.cnf (my.ini für Windows), um sie zu aktivieren. Durch Ändern der Konfigurationsdatei können Sie langsame Abfragen dauerhaft aktivieren.

Methode 1: Langsame Abfrage per Befehl aktivieren

Schritt 1. Fragen Sie slow_query_log ab, um zu prüfen, ob das langsame Abfrageprotokoll aktiviert ist:

Variablen wie „%slow_query_log%“ anzeigen;

mysql> Variablen wie „%slow_query_log%“ anzeigen;
+---------------------+----------------------------------+
| Variablenname | Wert |
+---------------------+----------------------------------+
| slow_query_log | AUS |
| slow_query_log_datei | /var/lib/mysql/localhost-slow.log |
+---------------------+----------------------------------+
2 Zeilen im Satz (0,01 Sek.)

Schritt 2: Aktivieren Sie den Befehl „Slow Query“:

Setzen Sie global slow_query_log='ON'.

Schritt 3: Geben Sie den Schwellenwert für die Aufzeichnung der SQL-Ausführungszeit des langsamen Abfrageprotokolls an (Einheit „long_query_time“: Sekunden, Standard 10 Sekunden).

Wie unten gezeigt, habe ich es auf 1 Sekunde eingestellt. SQL-Anweisungen, deren Ausführung länger als 1 Sekunde dauert, werden im Protokoll für langsame Abfragen aufgezeichnet.

setze global long_query_time=1;

Schritt 4: Abfrage des „Speicherorts für langsame Abfrageprotokolldateien“

Variablen wie „%slow_query_log_file%“ anzeigen;

mysql> Variablen wie „%slow_query_log_file%“ anzeigen;
+---------------------+----------------------------------+
| Variablenname | Wert |
+---------------------+----------------------------------+
| slow_query_log_datei | /var/lib/mysql/localhost-slow.log |
+---------------------+----------------------------------+
1 Zeile im Satz (0,01 Sek.)

slow_query_log_file gibt den Speicherpfad und die Datei des Slow-Query-Logs an (wird standardmäßig zusammen mit der Datendatei abgelegt)

Schritt 5: Überprüfen, ob die langsame Abfrage aktiviert ist

Sie müssen das aktuelle MySQL-Terminal verlassen und sich zum Aktualisieren erneut anmelden.

Nachdem die langsame Abfrage konfiguriert wurde, werden die folgenden SQL-Anweisungen aufgezeichnet, die die Bedingungen erfüllen:

• Abfrageanweisung
• Anweisungen zur Datenänderung
• Zurückgesetztes SQL

Methode 2: Aktivieren durch Konfigurieren der Systemdatei my.cnf (my.ini für Windows)

(Version: MySQL 5.5 und höher)

Fügen Sie die folgende Konfiguration unter [mysqld] in der Datei my.cnf hinzu, um langsame Abfragen zu aktivieren, wie unten gezeigt

# Aktivieren Sie die langsame Abfragefunktion slow_query_log=ON
# Geben Sie den Schwellenwert für die Aufzeichnung der SQL-Ausführungszeit des langsamen Abfrageprotokolls an long_query_time=1
# Optionaler Standard-Datendateipfad # slow_query_log_file=/var/lib/mysql/localhost-slow.log

Nach dem Neustart der Datenbank ist die langsame Abfrage dauerhaft aktiviert. Die Abfrageüberprüfung erfolgt wie folgt:

mysql> Variablen wie „%_query_%“ anzeigen;
+------------------------------+-------------------------------------+
| Variablenname | Wert |
+------------------------------+-------------------------------------+
| habe_Abfragecache | JA |
| lange Abfragezeit | 1.000000 |
| slow_query_log | EIN |
| slow_query_log_datei | /var/lib/mysql/localhost-slow.log |
+------------------------------+-------------------------------------+
6 Zeilen im Satz (0,01 Sek.)

1-2. Einführung in langsame Abfrageprotokolle

Wie oben gezeigt, ist dies eine SQL-Anweisung, deren Ausführung länger als 1 Sekunde dauert (Test).

• Erste Zeile: Rekordzeit
• Die zweite Zeile: Benutzername, Benutzer-IP-Informationen, Thread-ID-Nummer
• Die dritte Zeile: die Ausführungszeit [Einheit: Sekunden], die Ausführungszeit zum Erhalten der Sperre, die Anzahl der erhaltenen Ergebniszeilen und die Anzahl der gescannten Datenzeilen
• Zeile 4: Der Zeitstempel der Ausführung dieses SQL
• Zeile 5: Spezifische SQL-Anweisung

2. Erklären Sie die Analyse langsamer SQL-Abfragen

Analysieren Sie langsame MySQL-Abfrageprotokolle. Verwenden Sie das Schlüsselwort „explain“, um den Optimierer zu simulieren, der SQL-Abfrageanweisungen ausführt, um langsame SQL-Abfrageanweisungen zu analysieren. Unsere Testtabelle unten ist eine App-Informationstabelle mit 1,37 Millionen Daten. Lassen Sie uns sie als Beispiel analysieren.

Das SQL-Beispiel lautet wie folgt:

-- 1,185 Sekunden
SELECT * from vio_basic_domain_info where app_name like '%陈哈哈%';

Dies ist eine gängige Fuzzy-Abfrageanweisung. Die Abfrage dauerte 1,185 Sekunden und ergab 148 Datensätze.
Die Ergebnisse der Explain-Analyse werden in der folgenden Tabelle angezeigt. Laut den Tabelleninformationen verwendet die SQL-Anweisung den Index nicht für das Feld app_name, der Abfragetyp ist ein vollständiger Tabellenscan und die Anzahl der gescannten Zeilen beträgt 1,37 W.

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%陈哈哈%';
+----+----------+----------------------+---------+------+---------------+---------+------+---------+---------+---------+---------+----------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------------------+---------+------+---------------+---------+------+---------+---------+---------+---------+----------+---------+
| 1 | SIMPLE | vio_basic_domain_info | NULL | ALL | NULL | NULL | NULL | NULL | 1377809 | 11.11 | Verwenden von „where“ |
+----+----------+----------------------+---------+------+---------------+---------+------+---------+---------+---------+---------+----------+---------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wenn dieses SQL den Index verwendet, lautet das SQL wie folgt: Abfragezeit: 0,156 s, 141 Daten gefunden

-- 0,156 s
SELECT * von vio_basic_domain_info, wobei app_name wie '陈哈哈%' ist;

Die Ergebnisse der Explain-Analyse lauten wie folgt. Gemäß den Tabelleninformationen verwendet SQL den Index idx_app_name, der Abfragetyp ist eine Indexbereichsabfrage und die Anzahl der gescannten Zeilen beträgt 141. Da sich nicht alle Spalten der Abfrage im Index befinden (Select *), wird die Tabelle einmal zurückgegeben, um die Daten anderer Spalten zu erhalten.

mysql> EXPLAIN SELECT * von vio_basic_domain_info, wobei app_name wie '陈哈哈%' ist;
+----+----------+----------------------+---------+-----------+---------------+---------------+-----------+-----------+-----------+----------+----------+-----------+-----------+-----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------------------+---------+-----------+---------------+---------------+-----------+-----------+-----------+----------+----------+-----------+-----------+-----------+
| 1 | SIMPLE | vio_basic_domain_info | NULL | Bereich | idx_app_name | idx_app_name | 515 | NULL | 141 | 100,00 | Indexbedingung wird verwendet |
+----+----------+----------------------+---------+-----------+---------------+---------------+-----------+-----------+-----------+----------+----------+-----------+-----------+-----------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wenn dieses SQL einen überdeckenden Index verwendet, lautet das SQL wie folgt: Abfragezeit: 0,091 s, 141 Daten gefunden

-- 0,091 s
SELECT app_name aus vio_basic_domain_info, wobei app_name wie „陈哈哈%“ aussieht;

Die Ergebnisse der Explain-Analyse lauten wie folgt. Gemäß den Tabelleninformationen wird der Index genauso verwendet wie das obige SQL. Da die Abfragespalte in der Indexspalte enthalten ist, werden 0,06 Sekunden Tabellenrückgabezeit gespart.

mysql> EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like 'Nicht zutreffend';
+----+----------+----------------------+---------+-------+---------------+---------------+--------------+-------+-------+----------+----------+----------+--------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------------------+---------+-------+---------------+---------------+--------------+-------+-------+----------+----------+----------+--------------+
| 1 | SIMPLE | vio_basic_domain_info | NULL | Bereich | idx_app_name | idx_app_name | 515 | NULL | 141 | 100,00 | Verwenden von „where“; Verwenden von „index“ |
+----+----------+----------------------+---------+-------+---------------+---------------+--------------+-------+-------+----------+----------+----------+--------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wie analysieren Sie also SQL anhand der EXPLAIN-Analyseergebnisse? Was stellen die Attribute jeder Spalte dar? Lasst uns gemeinsam nach unten schauen.

2-1. Einführung in jedes Spaltenattribut:

• id: SELECT-Abfragesequenznummer, die die Ausführungspriorität widerspiegelt. Wenn es sich um eine Unterabfrage handelt, erhöht sich die ID-Sequenznummer. Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt.
• select_type: gibt den Abfragetyp an.
• Tabelle: Die Tabelle des Ausgabeergebnissatzes. Wenn ein Alias ​​festgelegt ist, wird dieser ebenfalls angezeigt.
• Partitionen: passende Partitionen
• Typ: Zugriffsmethode auf die Tabelle
• Possible_keys: Gibt die möglichen Indizes an, die bei der Abfrage verwendet werden sollen
• Schlüssel: Gibt den tatsächlich verwendeten Index an
• key_len: Länge des Indexfeldes
• ref: Vergleich von Spalten und Indizes
• Zeilen: die Anzahl der gescannten Zeilen (geschätzte Zeilenanzahl)
• gefiltert: Der Prozentsatz der Zeilen, die nach Tabellenbedingungen gefiltert wurden
• Extra: Beschreibung und Erklärung der Durchführung

Die oben mit einem Sternchen versehenen Kategorien werden häufig verwendet, wenn wir langsame Abfragen optimieren.

2-2. Häufig verwendete Attribute für die Analyse langsamer Abfragen

1. Typ:
Die Tabellenzugriffsmethode gibt an, wie MySQL die erforderlichen Zeilen in der Tabelle findet. Sie wird auch als „Zugriffstyp“ bezeichnet.

Die vorhandenen Typen sind: ALL, index, range, ref, eq_ref, const, system, NULL (von links nach rechts, Leistung von niedrig nach hoch). Hier sind drei, die wir täglich sehen:

• ALL: (Full Table Scan) MySQL durchsucht die gesamte Tabelle nach übereinstimmenden Zeilen, was oft als Full Table Scan bezeichnet wird.
• index: (Vollständiger Index-Scan) Der Unterschied zwischen index und ALL besteht darin, dass der Indextyp nur den Indexbaum durchläuft
• Bereich: Ruft nur die Zeilen in einem bestimmten Bereich ab und verwendet einen Index zum Auswählen der Zeilen.

2. Schlüssel

Die Spalte key zeigt den tatsächlich von SQL verwendeten Index, normalerweise einen der Indizes in der Spalte possible_keys. Der MySQL-Optimierer wählt im Allgemeinen einen geeigneteren Index aus, indem er die Anzahl der Scanzeilen berechnet. Wenn kein Index ausgewählt ist, wird NULL zurückgegeben. Natürlich gibt es Fälle, in denen der MySQL-Optimierer den falschen Index auswählt. Sie können MySQL zwingen, „einen bestimmten Index zu verwenden oder zu ignorieren“, indem Sie das SQL ändern.

• Erzwingen Sie die Verwendung eines Indexes: FORCE INDEX (index_name), USE INDEX (index_name)
• Erzwingen Sie das Ignorieren eines Indexes: IGNORE INDEX (index_name)
  

3. Reihen
„Zeilen“ ist die Anzahl der Zeilen, die MySQL schätzungsweise lesen (scannen) muss, um die erforderliche Zeile zu finden. Diese Zahl ist möglicherweise nicht exakt.

4. Extra
In dieser Spalte werden einige zusätzliche Informationen angezeigt, die wichtig sind.

Verwenden des Index
Die abgefragten Spalten werden durch den Index abgedeckt, und die Where-Filterbedingung ist die führende Spalte des Index, und Extra ist Using-Index. Dies bedeutet, dass Sie die Daten, die die Bedingungen erfüllen, direkt über die Indexsuche finden können, ohne zur Tabelle zurückkehren zu müssen.

Hinweis: Mit der führenden Spalte ist im Allgemeinen die erste Spalte oder die „ersten paar Spalten“ in einem gemeinsamen Index gemeint, auch im Fall eines einspaltigen Index. Der Einfachheit halber nenne ich sie hier die führende Spalte.

Verwenden von „where“
Dies bedeutet, dass der MySQL-Server Zeilen filtert, nachdem die Speicher-Engine sie abgerufen hat. Das heißt, der Index wird nicht verwendet und die Tabelle wird abgefragt.

Mögliche Ursachen:

• Die abgefragte Spalte wird nicht durch den Index abgedeckt;
• Wenn die Filterbedingung nicht die führende Spalte des Index ist oder der Index nicht korrekt verwendet werden kann;

Mit temporären
Dies bedeutet, dass MySQL beim Sortieren der Abfrageergebnisse eine temporäre Tabelle verwendet.

Verwenden von Filesort
Dies bedeutet, dass MySQL zum Sortieren der Ergebnisse einen externen Index verwendet, anstatt die Zeilen in Indexreihenfolge aus der Tabelle zu lesen.

Verwenden der Indexbedingung
Die Abfragespalten sind nicht alle im Index und die Where-Bedingung ist ein Bereich führender Spalten.

Verwenden von „where“; Verwenden von „index“
Die abgefragte Spalte wird durch den Index abgedeckt, und die Where-Filterbedingung ist eine der Indexspalten, aber nicht die führende Spalte des Index, oder es liegen andere Situationen vor, die die direkte Verwendung des Index beeinflussen (z. B. das Vorhandensein einer Bereichsfilterbedingung). Zusätzlich wird Where verwendet; Index verwendet, was bedeutet, dass die Daten, die die Bedingungen erfüllen, nicht direkt über die Indexsuche abgefragt werden können und die Auswirkungen nicht signifikant sind.

3. Teilen Sie einige Erfahrungen mit der Optimierung langsamer Abfragen

3-1. LIMIT-Paging optimieren

Operationen, die eine Seitenauslagerung im System erfordern, werden üblicherweise mit der Methode „Limit plus Offset“ und einer entsprechenden „Order by“-Klausel implementiert. Wenn ein entsprechender Index vorhanden ist, ist die Effizienz normalerweise gut, andernfalls muss MySQL viele Dateisortiervorgänge durchführen.

Ein sehr lästiges Problem ist, wenn der Offset sehr groß ist, beispielsweise eine Abfrage wie „Limit 1000000,10“. Das bedeutet, dass MySQL 1.000.000 Datensätze abfragen muss und dann nur die letzten 10 zurückgibt. Die vorherigen 1.000.000 Datensätze werden verworfen. Das ist sehr kostspielig und führt zu langsamen Abfragen.

Eine der einfachsten Möglichkeiten zur Optimierung solcher Abfragen besteht darin, wenn immer möglich Index-Abdeckungsscans zu verwenden, statt alle Spalten abzufragen. Führen Sie dann nach Bedarf eine Join-Operation durch und geben Sie die erforderlichen Spalten zurück. Dadurch wird die Effizienz bei großem Versatz erheblich verbessert.

Für die folgende Abfrage:

-- Ausführungszeit: 1,379 s
Wählen Sie * aus vio_basic_domain_info LIMIT 1000000,10;

Erläutern Sie die Analyseergebnisse:

mysql> ERKLÄREN SIE, AUSWÄHLEN * von vio_basic_domain_info LIMIT 1000000,10;
+----+----------+----------------------+---------+------+---------------+---------+------+---------+---------+---------+---------+----------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------------------+---------+------+---------------+---------+------+---------+---------+---------+---------+----------+---------+
| 1 | EINFACH | vio_basic_domain_info | NULL | ALLE | NULL | NULL | NULL | NULL | 1377809 | 100,00 | NULL |
+----+----------+----------------------+---------+------+---------------+---------+------+---------+---------+---------+---------+----------+---------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Das größte Problem bei dieser Anweisung besteht darin, dass der Offset M im Grenzwert M,N zu groß ist, was dazu führt, dass jede Abfrage zuerst die ersten M Datensätze aus der gesamten Tabelle sucht, die die Bedingungen erfüllen, diese M Datensätze dann verwirft und beim M+1. Datensatz beginnt, um der Reihe nach N Datensätze zu suchen, die die Bedingungen erfüllen. Wenn die Tabelle sehr groß ist, das Filterfeld keinen geeigneten Index hat und M sehr groß ist, sind diese Kosten sehr hoch.

Wenn unsere nächste Abfrage dann an der Position beginnen kann, die nach der vorherigen Abfrage markiert wurde, können wir 10 Datensätze finden, die die Bedingungen erfüllen, und die Position notieren, an der die nächste Abfrage beginnen soll, sodass die nächste Abfrage direkt an dieser Position beginnen kann. Dann müssen wir nicht bei jeder Abfrage die ersten M Datensätze aus der gesamten Tabelle suchen, die die Bedingungen erfüllen, sie verwerfen und dann bei M+1 beginnen, um 10 weitere Datensätze zu finden, die die Bedingungen erfüllen.

Es gibt im Allgemeinen die folgenden Möglichkeiten, mit langsamen Paging-Abfragen umzugehen

Idee 1: Einen abdeckenden Index konstruieren

Durch Ändern von SQL und Verwenden des überdeckenden Index kann ich beispielsweise, wenn ich nur einige Felder in der Tabelle abfragen muss, wie etwa app_name und createTime, einen gemeinsamen Index für die Felder app_name und createTime festlegen, um einen überdeckenden Index zu erhalten, ohne die gesamte Tabelle zu scannen. Diese Methode eignet sich für Szenarios mit weniger Abfragespalten und wird für Szenarios mit zu vielen Abfragespalten nicht empfohlen.
Dauer: 0,390 s

mysql> EXPLAIN SELECT app_name,createTime aus vio_basic_domain_info LIMIT 1000000,10;
+----+----------+----------------------+---------+-----------+---------------+---------------+-----------+-----------+---------+---------+---------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------------------+---------+-----------+---------------+---------------+-----------+-----------+---------+---------+---------+---------+
| 1 | SIMPLE | vio_basic_domain_info | NULL | index | NULL | idx_app_name | 515 | NULL | 1377809 | 100.00 | Index wird verwendet |
+----+----------+----------------------+---------+-----------+---------------+---------------+-----------+-----------+---------+---------+---------+----------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Idee 2: Offset optimieren

Wenn keine abdeckenden Indizes verwendet werden können, besteht der Schlüssel darin, eine Möglichkeit zu finden, die ersten 1 Million Daten schnell herauszufiltern. Wir können die geordnete Bedingung des automatisch inkrementierten Primärschlüssels verwenden, um zuerst den ID-Wert des 1000001. Datums abzufragen und dann 10 Zeilen später abzufragen. Dies eignet sich für Szenarien, in denen die Primärschlüssel-ID automatisch inkrementiert wird.
Dauer: 0,471 s

SELECT * from vio_basic_domain_info wobei 
  id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

Prinzip: Fragen Sie zuerst den Wert der Primärschlüssel-ID ab, die dem 1000001. Datum basierend auf dem Index entspricht, und fragen Sie dann die 10 Daten hinter der ID direkt über den Wert der ID ab. In den folgenden EXPLAIN-Analyseergebnissen können Sie den zweistufigen Ausführungsprozess dieses SQL sehen.

mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;
+----+----------+----------------------+---------+-----------+---------------+--------+---------+---------+---------+---------+---------+----------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------------------+---------+-----------+---------------+--------+---------+---------+---------+---------+----------+---------+
| 1 | PRIMARY | vio_basic_domain_info | NULL | Bereich | PRIMARY | PRIMARY | 8 | NULL | 10 | 100,00 | Verwenden von „where“ |
| 2 | UNTERABFRAGE | vio_basic_domain_info | NULL | Index | NULL | PRIMÄR | 8 | NULL | 1000001 | 100,00 | Index wird verwendet |
+----+----------+----------------------+---------+-----------+---------------+--------+---------+---------+---------+---------+----------+---------+
2 Zeilen im Satz, 1 Warnung (0,40 Sek.)

Methode 3: „Verzögerte Assoziation“

Dauer: 0,439 s
Der verzögerte Join eignet sich für Tabellen mit größerer Größe. Das SQL lautet wie folgt:

SELECT * from vio_basic_domain_info inner join (wähle ID aus vio_basic_domain_info, sortiert nach ID-Limit 1000000,10) als myNew using(id);

Hier verwenden wir den abdeckenden Index + die verzögerte zugehörige Abfrage, was dem Abfragen nur der ID-Spalte entspricht, wobei wir den abdeckenden Index verwenden, um schnell die 10 Daten-IDs der Seite zu finden, und dann die zurückgegebenen 10 IDs in die Tabelle übernehmen und sie erneut über den Primärschlüsselindex abfragen. (Diese Methode wird durch das schnelle Wachstum von Tabellendaten weniger beeinflusst.)

mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (Wählen Sie ID aus vio_basic_domain_info, sortiert nach ID-Limit 1000000,10) als myNew using(id);
+----+----------+-----------+------------+--------+---------------+--------+-----------+---------+----------+----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+------------+--------+---------------+--------+-----------+---------+----------+----------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALLE | NULL | NULL | NULL | NULL | 1000010 | 100,00 | NULL |
| 1 | PRIMARY | vio_basic_domain_info | NULL | eq_ref | PRIMARY | PRIMARY | 8 | myNew.id | 1 | 100,00 | NULL |
| 2 | ABGELEITET | vio_basic_domain_info | NULL | index | NULL | PRIMARY | 8 | NULL | 1000010 | 100.00 | Index wird verwendet |
+----+----------+-----------+------------+--------+---------------+--------+-----------+---------+----------+----------+----------+-------------+
3 Zeilen im Satz, 1 Warnung (0,00 Sek.)

3-2. Überprüfen Sie, ob der Index nicht funktioniert

Vermeiden Sie es, Fuzzy-Abfragen mit dem Platzhalterzeichen „%“ zu beginnen, da dies dazu führt, dass die Datenbank-Engine den Index aufgibt und einen vollständigen Tabellenscan durchführt. wie folgt:

SELECT * FROM t WHERE Benutzername LIKE '%陈哈哈%'

Optimierungsmethode: Versuchen Sie, nach dem Feld eine Fuzzy-Abfrage zu verwenden. wie folgt:

SELECT * FROM t WHERE Benutzername LIKE '陈哈哈%'

Wenn die Anforderung darin besteht, Fuzzy-Abfragen im Frontend zu verwenden,

• Verwenden Sie zum Abgleichen die in MySQL integrierte Funktion INSTR(str,substr), die indexOf() in Java ähnelt, um die Indexposition der Zeichenfolge zu finden.
• Verwenden Sie die Volltextindizierung und vergleichen Sie sie mit
• Bei großen Datenmengen empfiehlt sich die Verwendung von ElasticSearch und Solr, die Milliarden von Daten in Sekunden abrufen können.
• Wenn die Tabelle nur eine kleine Datenmenge (Tausende von Datensätzen) enthält, sollten Sie nicht zu ausgefallen sein und einfach „%xx%“ verwenden.

Es muss jedoch gesagt werden, dass MySQLs Fuzzy-Matching großer Felder ein Fehler ist. Schließlich verbraucht die Sicherstellung der ACID-Eigenschaften von Transaktionen zu viel Leistung. Wenn in tatsächlichen Szenarien ähnliche Geschäftsanforderungen bestehen, wird daher empfohlen, die Big-Data-Speicher-Engine wie ElasticSearch, Hbase usw. entscheidend zu ändern. Es geht hier nicht um Gefühle~

Vermeiden Sie möglichst die Verwendung von „not in“, da dies dazu führt, dass die Engine einen vollständigen Tabellenscan durchführt. Es wird empfohlen, stattdessen „not exists“ wie folgt zu verwenden:

- Verwenden Sie nicht den Index SELECT * FROM t WHERE name not IN ('Timo','Captain');

-- Gehen Sie nach Index, wählen Sie * von t als t1, wo nicht vorhanden (wählen Sie * von t als t2, wo Name IN ('Timo', 'Captain') und t1.id = t2.id);

Vermeiden Sie möglichst die Verwendung von „oder“, da dies dazu führt, dass die Datenbank-Engine den Index aufgibt und einen vollständigen Tabellenscan durchführt. wie folgt:

Wählen Sie * von t, wobei id = 1 oder id = 3 ist.

Optimierungsmethode: Sie können Union statt oder verwenden. wie folgt:

WÄHLEN SIE * VON t, WO id = 1
   UNION
Wählen Sie * von t, wobei id = 3 ist.

Vermeiden Sie nach Möglichkeit die Beurteilung von Nullwerten, da diese dazu führen, dass die Datenbank-Engine den Index aufgibt und einen vollständigen Tabellenscan durchführt. wie folgt:

Wählen Sie * aus t, wobei Punktzahl NULL ist

Optimierungsmethode: Sie können dem Feld einen Standardwert von 0 hinzufügen und den Wert 0 beurteilen. wie folgt:

WÄHLEN SIE * VON t, WO Punktzahl = 0

Vermeiden Sie nach Möglichkeit die Ausführung von Ausdrücken oder Funktionsoperationen auf der linken Seite des Gleichheitszeichens in der Where-Bedingung, da dies dazu führt, dass die Datenbank-Engine den Index aufgibt und einen vollständigen Tabellenscan durchführt.
Ausdrücke und Funktionsoperationen können auf die rechte Seite des Gleichheitszeichens verschoben werden. wie folgt:

- Vollständiger Tabellenscan SELECT * FROM T WHERE score/10 = 9
-- Gehe zum Index SELECT * FROM T WHERE score = 10*9

Vermeiden Sie bei großen Datenmengen die Verwendung der Bedingung „where 1=1“. Um die Zusammenstellung von Abfragebedingungen zu erleichtern, verwenden wir normalerweise standardmäßig diese Bedingung. Die Datenbank-Engine gibt dann den Index auf und führt einen vollständigen Tabellenscan durch. wie folgt:

SELECT Benutzername, Alter, Geschlecht FROM T WHERE 1=1

Optimierungsmethode: Treffen Sie Urteile, wenn Sie SQL mit Code zusammenstellen. Wenn keine Where-Bedingung vorhanden ist, entfernen Sie die Where-Bedingung. Wenn eine Where-Bedingung vorhanden ist, fügen Sie und hinzu.

Verwenden Sie in der Abfragebedingung nicht <> oder !=.
Vermeiden Sie die Verwendung von Beurteilungsbedingungen wie <> oder !=, wenn Sie die Indexspalte als Bedingung für die Abfrage verwenden. Wenn das Unternehmen das Ungleich-Symbol verwenden muss, müssen Sie die Indexerstellung neu bewerten, um die Indizierung dieses Felds zu vermeiden und es in den Abfragebedingungen durch andere Indexfelder zu ersetzen.

Die Where-Bedingung enthält nur die nicht führenden Spalten des zusammengesetzten Indexes. Beispielsweise enthält der zusammengesetzte (gemeinsame) Index drei Spalten: key_part1, key_part2 und key_part3, aber die SQL-Anweisung enthält nicht die führende Spalte „key_part1“ des Indexes. Gemäß dem am weitesten links stehenden Übereinstimmungsprinzip des MySQL-gemeinsamen Indexes wird der gemeinsame Index nicht verwendet.

-- Wählen Sie col1 aus der Tabelle, in der key_part2=1 und key_part3=2 ist, ohne Index
-- Gehen Sie nach Index und wählen Sie Spalte 1 aus der Tabelle aus, wobei Schlüsselteil1 = 1 und Schlüsselteil2 = 1 und Schlüsselteil3 = 2 ist.

Implizite Typkonvertierung führt dazu, dass der Index nicht verwendet wird. Die folgende SQL-Anweisung kann den Index nicht richtig verwenden, da der Indexspaltentyp varchar ist, der angegebene Wert jedoch ein numerischer Wert ist, was eine implizite Typkonvertierung mit sich bringt.

Wählen Sie Spalte1 aus der Tabelle, in der col_varchar=123 ist.

Zusammenfassen

Ich hoffe, dass Ihnen dieser Artikel einige Methoden und Erfahrungen zur Analyse langsamer MySQL-Abfragen vermittelt hat. Langsame Abfragen sind in MySQL immer ein unvermeidliches Thema. Es gibt viele Möglichkeiten, langsam zu sein. Was ist, wenn Sie langsame Abfragen vollständig vermeiden möchten? Junger Mann, ich schlage vor, Sie lernen aus Ihren Fehlern.

Was wir tun müssen, ist, langsame Abfragen rechtzeitig zu erkennen und zu lösen. Tatsächlich treten viele langsame Abfragen passiv auf, beispielsweise wenn die Datenmenge für ein bestimmtes Unternehmen dramatisch zunimmt oder wenn Felder geändert oder vorhandene Indizes aufgrund geänderter Geschäftsanforderungen bearbeitet werden. Obwohl es nicht Ihre Schuld ist, müssen Sie möglicherweise trotzdem die Schuld auf sich nehmen

Dies ist das Ende dieses Artikels über die Fallstricke langsamer MySQL-Abfragen. Weitere relevante Inhalte zu langsamen MySQL-Abfragen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!