Detaillierte Erläuterung der allgemeinen Schritte zur SQL-Anweisungsoptimierung

Vorwort

In diesem Artikel erfahren Sie hauptsächlich die allgemeinen Schritte zur Optimierung von SQL-Anweisungen. Er dient zu Ihrer Information und zum Lernen. Werfen wir ohne weitere Umschweife einen Blick auf die ausführliche Einführung.

1. Verwenden Sie den Befehl „show status“, um die Ausführungshäufigkeit verschiedener SQL-Anweisungen zu verstehen

Nachdem die Verbindung zum MySQL-Client erfolgreich hergestellt wurde, können Sie mit show [session|global] status Informationen zum Serverstatus erhalten. Alternativ können Sie diese Nachrichten auch mysqladmin extend-status im Betriebssystem abrufen.

show status kann mit den Sitzungsoptionen (Standard) oder globalen Optionen hinzugefügt werden:

• Sitzung (aktuelle Verbindung)
• global (Daten seit dem letzten Start)

# Com_xxx gibt an, wie oft jede xxx-Anweisung ausgeführt wird.
mysql> Status wie „Com_%“ anzeigen;

Normalerweise interessieren uns eher die folgenden statistischen Parameter:

• Com_select: Die Häufigkeit, mit der der Auswahlvorgang ausgeführt wird. Für jede Abfrage wird nur 1 akkumuliert.
• Com_insert: Die Anzahl der Ausführungen des Einfügevorgangs. Bei Batch-Einfügevorgängen wird dies nur einmal akkumuliert.
• Com_update: Die Anzahl der durchgeführten Aktualisierungsvorgänge.
• Com_delete: Die Häufigkeit, mit der der Löschvorgang ausgeführt wird.

Die oben genannten Parameter werden für Tabellenoperationen aller Speicher-Engines akkumuliert. Die folgenden Parameter gelten nur für InnoDB und der Akkumulationsalgorithmus ist geringfügig anders:

• Innodb_rows_read: Die Anzahl der von einer Auswahlabfrage zurückgegebenen Zeilen.
• Innodb_rows_inserted: Die Anzahl der durch den Einfügevorgang eingefügten Zeilen.
• Innodb_rows_updated: Die Anzahl der durch den Aktualisierungsvorgang aktualisierten Zeilen.
• Innodb_rows_deleted: Die Anzahl der durch den Löschvorgang gelöschten Zeilen.

Anhand der oben genannten Parameter können Sie leicht erkennen, ob die aktuelle Datenbankanwendung hauptsächlich auf Einfüge-, Aktualisierungs- oder Abfragevorgängen basiert, und das ungefähre Ausführungsverhältnis verschiedener SQL-Typen ermitteln. Die Anzahl der Aktualisierungsvorgänge ist die Anzahl der Ausführungen, die akkumuliert wird, unabhängig davon, ob ein Commit oder ein Rollback ausgeführt wird.

Bei transaktionalen Anwendungen können Com_commit und Com_rollback verwendet werden, um die Commit- und Rollback-Situationen von Transaktionen zu verstehen. Bei Datenbanken mit sehr häufigen Rollback-Operationen kann dies bedeuten, dass ein Problem beim Schreiben der Anwendung vorliegt.

Darüber hinaus helfen die folgenden Parameter den Benutzern, die grundlegende Situation der Datenbank zu verstehen:

• Verbindungen: Die Anzahl der Versuche, eine Verbindung zum MySQL-Server herzustellen.
• Betriebszeit: Arbeitszeit des Servers.
• Slow_queries: Die Anzahl der langsamen Abfragen.

2. Definieren Sie SQL-Anweisungen mit geringer Ausführungseffizienz

1. Verwenden Sie das langsame Abfrageprotokoll, um SQL-Anweisungen mit geringer Ausführungseffizienz zu finden. Wenn mysqld mit --log-slow-queries[=file_name] gestartet wird, schreibt es eine Protokolldatei mit allen SQL-Anweisungen, deren Ausführungszeit long_query_time Sekunden überschreitet.

2. Das langsame Abfrageprotokoll wird erst aufgezeichnet, nachdem die Abfrage abgeschlossen ist. Wenn die Anwendung daher ein Problem mit der Ausführungseffizienz anzeigt, kann das langsame Abfrageprotokoll das Problem nicht lokalisieren. Mit dem Befehl „show processlist“ können Sie die aktuellen MySQL-Threads anzeigen, einschließlich des Thread-Status, ob die Tabelle gesperrt ist usw. Sie können den Ausführungsstatus von SQL in Echtzeit anzeigen und einige Tabellensperrvorgänge optimieren.

3. Analysieren Sie den Ausführungsplan von ineffizientem SQL durch Erläuterung

Adresse der Testdatenbank: https://downloads.mysql.com/docs/sakila-db.zip (lokaler Download)

Um den Gesamtbetrag zu berechnen, der per E-Mail für die Ausleihe von Filmkopien bezahlt wurde, müssen Sie die Kundentabelle customer und die Zahlungstabelle payment verknüpfen und eine Summenoperation für das Betragsfeld ausführen. Der entsprechende Ausführungsplan lautet wie folgt:

mysql> erklären select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='[email protected]'\G 

*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: eine
 Partitionen: NULL
  Typ: ALLE
mögliche Schlüssel: PRIMARY
  Schlüssel: NULL
 key_len: NULL
  Ref: NULL
  Reihen: 599
 gefiltert: 10.00
 Extra: Verwenden von „where“
*************************** 2. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: b
 Partitionen: NULL
  Typ: ref
mögliche Schlüssel: idx_fk_customer_id
  Schlüssel: idx_fk_customer_id
 Schlüssellänge: 2
  Referenz: sakila.a.customer_id
  Reihen: 26
 gefiltert: 100,00
 Extra: NULL
2 Zeilen im Satz, 1 Warnung (0,00 Sek.)

• select_type: gibt den Auswahltyp an. Übliche Werte sind:
  einfach: einfache Tabelle, es wird kein Tabellenjoin oder keine Unterabfrage verwendet
  primär: Hauptabfrage, d. h. die äußere Abfrage
  Union: Die zweite oder nachfolgende Abfrageanweisung in einer Union
  Unterabfrage: Die erste Auswahl in der Unterabfrage
• Tabelle: Tabelle zur Ausgabe des Ergebnissatzes
• Typ: Gibt an, wie MySQL die erforderliche Zeile in der Tabelle findet, oder den Zugriffstyp. Gängige Typen, vom schlechtesten zum besten, sind: all, index, range, ref, eq_ref, const, system, null:
  

1. type=ALL , vollständiger Tabellenscan, MySQL durchsucht die gesamte Tabelle, um übereinstimmende Zeilen zu finden:

mysql> erklären select * from film where rating > 9 \G

*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: Film
 Partitionen: NULL
 Typ: ALLE
mögliche Schlüssel: NULL
  Schlüssel: NULL
 key_len: NULL
  Ref: NULL
 Reihen: 1000
 gefiltert: 33,33
 Extra: Verwenden von „where“
1 Zeile im Satz, 1 Warnung (0,01 Sek.)

2. type=index , vollständiger Index-Scan, MySQL durchsucht den gesamten Index nach übereinstimmenden Zeilen

mysql> erklären wählen Sie Titel Form Film\G

*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: Film
 Partitionen: NULL
 Typ: Index
mögliche Schlüssel: NULL
  Schlüssel: idx_title
 Schlüssellänge: 767
  Ref: NULL
 Reihen: 1000
 gefiltert: 100,00
 Extra: Index verwenden
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

3. type=range , Indexbereichsscan, häufig verwendet in <, <=, >, >=, zwischen und anderen Operationen:

mysql> erklären select * from payment where customer_id >= 300 und customer_id <= 350 \G 

*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: Zahlung
 Partitionen: NULL
 Typ: Bereich
mögliche Schlüssel: idx_fk_customer_id
  Schlüssel: idx_fk_customer_id
 Schlüssellänge: 2
  Ref: NULL
 Reihen: 1350
 gefiltert: 100,00
 Extra: Indexbedingung verwenden
1 Zeile im Satz, 1 Warnung (0,07 Sek.)

4. type=ref , verwenden Sie einen nicht eindeutigen Index-Scan oder einen Präfix-Scan eines eindeutigen Indexes, um Zeilen zurückzugeben, die mit einem einzelnen Wert übereinstimmen, zum Beispiel:

mysql> erklären select * from payment where customer_id = 350 \G 
*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: Zahlung
 Partitionen: NULL
 Typ: ref
mögliche Schlüssel: idx_fk_customer_id
  Schlüssel: idx_fk_customer_id
 Schlüssellänge: 2
  Verweis: const
 Reihen: 23
 gefiltert: 100,00
 Extra: NULL
1 Zeile im Satz, 1 Warnung (0,01 Sek.)

Der Index idx_fk_customer_id ist ein nicht eindeutiger Index. Die Abfragebedingung ist die gleichwertige Abfragebedingung customer_id = 350 , daher ist der Typ des Scan-Index ref. Ref erscheint auch oft in Join-Operationen:

mysql> erklären select b.*, a.* from Zahlung a,Kunde b wobei a.customer_id = b.customer_id \G

*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: b
 Partitionen: NULL
 Typ: ALLE
mögliche Schlüssel: PRIMARY
  Schlüssel: NULL
 key_len: NULL
  Ref: NULL
 Reihen: 599
 gefiltert: 100,00
 Extra: NULL
*************************** 2. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: eine
 Partitionen: NULL
 Typ: ref
mögliche Schlüssel: idx_fk_customer_id
  Schlüssel: idx_fk_customer_id
 Schlüssellänge: 2
  Referenz: sakila.b.customer_id
 Reihen: 26
 gefiltert: 100,00
 Extra: NULL
2 Zeilen im Satz, 1 Warnung (0,00 Sek.)

5. type=eq_ref , ähnlich wie ref, der Unterschied besteht darin, dass der verwendete Index ein eindeutiger Index ist. Für jeden Indexschlüsselwert muss nur ein Datensatz in der Tabelle übereinstimmen; vereinfacht ausgedrückt wird primary key oder der unique index als Verbindungsbedingung bei Mehrfachtabellenverbindungen verwendet.

mysql> erklären select * from film a , film_text b where a.film_id = b.film_id \G

*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: b
 Partitionen: NULL
 Typ: ALLE
mögliche Schlüssel: PRIMARY
  Schlüssel: NULL
 key_len: NULL
  Ref: NULL
 Reihen: 1000
 gefiltert: 100,00
 Extra: NULL
*************************** 2. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: eine
 Partitionen: NULL
 Typ: eq_ref
mögliche Schlüssel: PRIMARY
  Schlüssel: PRIMARY
 Schlüssellänge: 2
  Referenz: sakila.b.film_id
 Reihen: 1
 gefiltert: 100,00
 Extra: Verwenden von „where“
2 Zeilen im Satz, 1 Warnung (0,03 Sek.)

6. type=const/system , es gibt höchstens eine übereinstimmende Zeile in einer einzelnen Tabelle und die Abfrage ist sehr schnell, sodass die Werte anderer Spalten in dieser übereinstimmenden Zeile vom Optimierer in der aktuellen Abfrage als Konstanten behandelt werden können, z. B. Abfrage basierend auf dem primary key oder unique index .

mysql> Tabelle test_const erstellen (
 -> test_id int,
 -> test_context varchar(10),
 -> Primärschlüssel (`test_id`),
 -> );
 
in test_const-Werte einfügen (1, „Hallo“);

erkläre select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: test_const
 Partitionen: NULL
 Typ: const
mögliche Schlüssel: PRIMARY
  Schlüssel: PRIMARY
 Schlüssellänge: 4
  Verweis: const
 Reihen: 1
 gefiltert: 100,00
 Extra: NULL
 1 Zeile im Satz, 1 Warnung (0,00 Sek.)

7. type=null , MySQL kann das Ergebnis direkt abrufen, ohne auf die Tabelle oder den Index zuzugreifen:

mysql> erklären select 1 from dual where 1 \G
*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: NULL
 Partitionen: NULL
 Typ: NULL
mögliche Schlüssel: NULL
  Schlüssel: NULL
 key_len: NULL
  Ref: NULL
 Zeilen: NULL
 gefiltert: NULL
 Extra: Keine Tabellen verwendet
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Der Typ type hat andere Werte, wie etwa ref_or_null (ähnlich wie ref, außer dass die Bedingung eine Abfrage für null enthält), index_merge (Optimierung für Indexzusammenführung), unique_subquery (auf in folgt eine Unterabfrage, die das Primärschlüsselfeld abfragt), index_subquery (ähnlich wie unique_subquery, außer dass auf in eine Unterabfrage folgt, die das nicht eindeutige Indexfeld abfragt) usw.

• possible_keys: Gibt die möglichen Indizes an, die während der Abfrage verwendet werden können.
• Schlüssel: Gibt den tatsächlich verwendeten Index an
• key-len: Die Länge des verwendeten Indexfelds.
• Zeilen: die Anzahl der Scanzeilen
• Extra: Bezeichnung und Beschreibung der Ausführung, einschließlich zusätzlicher Informationen, die nicht zur Anzeige in anderen Spalten geeignet sind, für den Ausführungsplan jedoch sehr wichtig sind.

Befehl „Warnungen anzeigen“

Führen Sie nach der Ausführung von „explain“ show warnings aus, um zu sehen, welches SQL der Optimierer neu schreibt, bevor das SQL tatsächlich ausgeführt wird:

MySQL [sakila]> erklären Sie „select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = '[email protected]'\G
*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: eine
 Partitionen: NULL
  Typ: ALLE
mögliche Schlüssel: PRIMARY
  Schlüssel: NULL
 key_len: NULL
  Ref: NULL
  Reihen: 599
 gefiltert: 10.00
 Extra: Verwenden von „where“
*************************** 2. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: b
 Partitionen: NULL
  Typ: ref
mögliche Schlüssel: idx_fk_customer_id
  Schlüssel: idx_fk_customer_id
 Schlüssellänge: 2
  Referenz: sakila.a.customer_id
  Reihen: 26
 gefiltert: 100,00
 Extra: NULL
2 Zeilen im Satz, 1 Warnung (0,00 Sek.)

MySQL [sakila]> Warnungen anzeigen;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Ebene | Code | Nachricht |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hinweis | 1003 | /* Auswahl Nr. 1 */ Auswahl von Summe(`sakila`.`b`.`Betrag`) als `Summe(Betrag)` von `sakila`.`Kunde` `a` Verbindung zu `sakila`.`Zahlung` `b`, wobei ((`sakila`.`b`.`Kunden-ID` = `sakila`.`a`.`Kunden-ID`) und (`sakila`.`a`.`E-Mail` = '[email protected]')) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

Aus dem Warnmeldungsfeld können wir ersehen, dass der Optimierer automatisch die Bedingung entfernt, dass 1=1 immer gilt. Mit anderen Worten: Der Optimierer entfernt automatisch die Bedingung, die beim Umschreiben von SQL immer gilt.

Der Befehl „explain“ unterstützt auch Partitionen.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigniert NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NICHT NULL,
 -> `Vorname` varchar(45) NICHT NULL,
 -> `Nachname` varchar(45) NICHT NULL,
 -> `E-Mail` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NICHT NULL,
 -> `aktiv` tinyint(1) NICHT NULL STANDARD '1',
 -> `create_date` Datum/Uhrzeit NICHT NULL,
 -> `last_update` Zeitstempel NULL DEFAULT CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP,
 -> PRIMÄRSCHLÜSSEL (`customer_id`)
 -> 
 ->) Partition nach Hash (Kunden-ID), Partitionen 8;
Abfrage OK, 0 Zeilen betroffen (0,06 Sek.)

MySQL [sakila]> in Kundenteil einfügen, auswählen * vom Kunden;
Abfrage OK, 599 Zeilen betroffen (0,06 Sek.)
Datensätze: 599 Duplikate: 0 Warnungen: 0

MySQL [sakila]> erklären Sie select * from customer_part where customer_id=130\G
*************************** 1. Reihe ***************************
  ID: 1
 select_type: EINFACH
 Tabelle: Kundenteil
 Partitionen: p2
  Typ: const
mögliche Schlüssel: PRIMARY
  Schlüssel: PRIMARY
 Schlüssellänge: 2
  Verweis: const
  Reihen: 1
 gefiltert: 100,00
 Extra: NULL
1 Zeile im Set, 1 Warnung (0,00 Sek.)

Sie können sehen, dass die Partition, auf die SQL zugreift, p2 ist.

4. Analysieren Sie die SQL-Leistung über performance_schema

Ältere MySQL-Versionen können Profile zur Analyse der SQL-Leistung verwenden. Ich verwende Version 5.7.18, die die Verwendung von Profilen nicht mehr zulässt. Es wird empfohlen,
performance_schema analysiert SQL.

5. Analysieren Sie mithilfe der Ablaufverfolgung, wie der Optimierer den Ausführungsplan auswählt.

MySQL 5.6 bietet eine Ablaufverfolgungsfunktion für SQL-Anweisungen, die uns helfen kann, besser zu verstehen, warum der Optimierer Ausführungsplan A anstelle von Ausführungsplan B wählt, und uns helfen kann, das Verhalten des Optimierers besser zu verstehen.

Verwendung: Öffnen Sie zuerst die Ablaufverfolgung, stellen Sie das Format auf JSON ein und legen Sie die maximale Speichergröße fest, die die Ablaufverfolgung verwenden kann, um die unvollständige Anzeige während des Analysevorgangs aufgrund eines zu kleinen Standardspeichers zu vermeiden.

MySQL [hinzugefügt]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

MySQL [hinzugefügt]> setze optimizer_trace_max_mem_size=1000000;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Führen Sie als Nächstes die SQL-Anweisung aus, die Sie verfolgen möchten, um beispielsweise die Datensätze der Filmkopie mit der Inventarnummer inventory_id 4466 in der Verleihtabelle „rental“ zu ermitteln, die zwischen dem Verleihdatum „rental_date“ vom 25.05.2005 zwischen 4:00:00 und 5:00:00 Uhr vermietet wurde:

mysql> wähle „rental_id“ aus „rental“, wobei 1=1 und „rental_date“ >= „25.05.2005 04:00:00“ und „rental_date“ <= „25.05.2005 05:00:00“ und „inventory_id“=4466;
+-------------+
| Miet-ID |
+-------------+
| 39 |
+-------------+
1 Zeile im Satz (0,06 Sek.)

MySQL [hinzugefügt]> select * from information_schema.optimizer_trace\G
*************************** 1. Reihe ***************************
    ABFRAGE: select * from infomation_schema.optimizer_trace
    VERFOLGEN: {
 "Schritte": [
 ] /* Schritte */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  Unzureichende Berechtigungen: 0
1 Zeile im Satz (0,00 Sek.)

6. Identifizieren Sie das Problem und ergreifen Sie entsprechende Optimierungsmaßnahmen

Nach den oben genannten Schritten kann die Ursache des Problems grundsätzlich bestätigt werden. Zu diesem Zeitpunkt können je nach Situation geeignete Maßnahmen ergriffen werden, um die Ausführungseffizienz zu optimieren und zu verbessern.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels Ihnen bei Ihrem Studium oder Ihrer Arbeit helfen kann. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.