Anhand von Beispielen erfahren Sie, wie Sie SQL optimieren

Obwohl die Hardwarekosten heutzutage gesunken sind, ist die Verbesserung der Systemleistung durch ein Upgrade der Hardware auch eine gängige Optimierungsmethode. Systeme mit hohen Echtzeitanforderungen müssen noch unter SQL-Gesichtspunkten optimiert werden. Heute stellen wir anhand von Beispielen vor, wie man SQL optimiert.

Problem SQL beurteilen

Sie können anhand von zwei Symptomen beurteilen, ob ein Problem mit SQL vorliegt:

• Symptome auf Systemebene

  • Der CPU-Verbrauch ist schwerwiegend. IO-Warten ist schwerwiegend. Die Seite Die Antwortzeit ist zu lang. Wenn das Protokoll einer langen

  • Anwendung eine Zeitüberschreitung und andere Fehler

  aufweist, können Sie den Befehl sar und den Befehl top verwenden, um den aktuellen Systemstatus anzuzeigen. Sie können den Systemstatus auch über Überwachungstools wie Prometheus und Grafana beobachten.

SQL-Anweisungsdarstellung

Lang

• Die Ausführungszeit ist zu lang.

  • Daten aus dem vollständigen Tabellenscan abrufen. Die Zeilen und Kosten im Ausführungsplan sind sehr groß

  Lange SQL ist leicht zu verstehen. Wenn eine SQL zu lang ist, ist die Lesbarkeit schlecht und die Häufigkeit von Problemen ist definitiv höher. Um das SQL-Problem weiter zu beurteilen, müssen wir mit dem Ausführungsplan beginnen, wie unten gezeigt:
  • Der Ausführungsplan sagt uns, dass diese Abfrage einen vollständigen Tabellenscan Typ=ALL verwendet und die Zeilen sehr groß sind (9950400 ). Wir können grundsätzlich davon ausgehen, dass es sich hierbei um einen geschmackvollen SQL-Absatz handelt.
    Problem-SQL abrufen
  Verschiedene Datenbanken haben unterschiedliche Methoden, um es abzurufen. Im Folgenden finden Sie das SQL-Erfassungstool für langsame Abfragen für die aktuellen Mainstream-Datenbanken.

MySQL. Protokoll für langsame Abfragen

Ptquery und andere Tools des Unternehmens

Oracle

• AWR-Bericht

  • Testtool Loadrunner usw.

  • Verwandte interne Ansichten wie v$, $session _warte usw.

  • GRID CONTROL-Überwachungstool

• Dameng-Datenbank

  • AWR-Bericht

  • Testtool Loadrunner usw.

  • Dameng-Leistungsüberwachungstool (dem)

  • Verwandte interne Ansichten wie z B. v$, $session_wait usw.

• SQL-Schreibfähigkeiten
  • Es gibt mehrere allgemeine Fähigkeiten beim SQL-Schreiben:
    • Verwenden Sie Indizes rational

  • Bei weniger Indizes werden Abfragen langsamer; Nehmen Sie mehr Platz ein und Sie müssen den Index dynamisch verwalten, wenn Sie Hinzufügungen, Löschungen und Änderungen ausführen. Die Auswahlrate ist hoch (weniger doppelte Werte) und wird häufig von den B-Tree-Indizes benötigt

  Im Allgemeinen müssen bei der Erstellung eines zusammengesetzten Index Volltextindizes verwendet werden Achten Sie auf Abfragen, die auf nicht führenden Spalten basieren

  • • Verwenden Sie UNION ALL anstelle von UNION

  • UNION ALL hat eine höhere Ausführungseffizienz als UNION, und UNION muss bei der Ausführung dedupliziert werden. UNION muss Daten sortieren

  • Vermeiden Sie das Schreiben von „select *“

Beim Ausführen von SQL muss der Optimierer * in bestimmte Spalten konvertieren, und jede Abfrage muss an die Tabelle zurückgegeben werden. Abdeckindizes können nicht verwendet werden.

• Es wird empfohlen, JOIN-Felder zu indizieren.

Im Allgemeinen werden JOIN-Felder im Voraus indiziert.

• Vermeiden Sie komplexe SQL-Anweisungen.

Verbessern Sie die Lesbarkeit. Vermeiden Sie die Wahrscheinlichkeit langsamer Abfragen , Verarbeiten Sie es mit der geschäftlichen Seite

• Vermeiden Sie die Schreibweise „1=1“

• Vermeiden Sie die Schreibweise „order by rand()“

RAND(), die dazu führt, dass die Datenspalte mehrmals gescannt wird

SQL-Optimierung

Ausführungsplan

Abgeschlossen Bei der Optimierung von SQL müssen Sie zunächst den Ausführungsplan lesen. Der Ausführungsplan zeigt Ihnen, wo die Effizienz gering ist und wo eine Optimierung erforderlich ist. Nehmen wir MYSQL als Beispiel, um zu sehen, wie der Ausführungsplan aussieht. (Der Ausführungsplan jeder Datenbank ist unterschiedlich, Sie müssen ihn selbst verstehen)

Als nächstes verwenden wir einen praktischen Optimierungsfall, um den SQL-Optimierungsprozess und die Optimierungstechniken zu erläutern. OptimierungsfallTabellenstruktur

Feld	Erläuterung
ID	Jede unabhängig ausgeführte Vorgangsidentifikation identifiziert die Reihenfolge, in der das Objekt ausgeführt wird. Je größer der ID-Wert, desto größer ist der Wert, der zuerst ausgeführt wird. Die Ausführungsreihenfolge ist von oben nach unten. Unten
select_type	Der Typ jeder Select-Klausel in der Abfrage
table	Der Name des Objekts, an dem gearbeitet wird, normalerweise der Tabellenname, aber es gibt auch andere Formate
Partitionen	Die passenden Partitionsinformationen (Für nicht partitionierte Tabellen ist der Wert NULL)
Typ	Typ der Join-Operation
mögliche_Schlüssel	mögliche Indizes
Schlüssel	Der tatsächlich vom Optimierer verwendete Index (die wichtigste Spalte). Die Join-Typen vom besten zum schlechtesten sind const, eq_reg, ref, range, index und ALL. Wenn ALL angezeigt wird, bedeutet dies, dass das aktuelle SQL einen „schlechten Geruch“ hat.
key_len	Die Länge des vom Optimierer ausgewählten Indexschlüssels, die Einheit ist Bytes Das betriebene Objekt in dieser Zeile, das Nicht-Referenzobjekt ist NULL
rows	Die Anzahl der von der Abfrageausführung gescannten Tupel (für Innodb ist dieser Wert eine Schätzung)
gefiltert	Die Anzahl der Tupel in der Bedingungstabelle, in der die Daten gefiltert werden Prozent
extra	Wichtige Zusatzinformationen des Ausführungsplans. Seien Sie vorsichtig, wenn in dieser Spalte die Wörter „Filesort verwenden“ und „Temporär verwenden“ angezeigt werden. Es ist sehr wahrscheinlich, dass die SQL-Anweisung dies tun muss optimiert werden

CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);

Drei Tabellen beziehen sich darauf, den Bestellstatus des aktuellen Benutzers 10 Stunden vor und nach der aktuellen Zeit abzufragen und sie in aufsteigender Reihenfolge entsprechend der Auftragserstellungszeit zu sortieren wie folgt

select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

Sehen Sie sich das Datenvolumen an konsequent sein mit der Tabellenstruktur. Die user_id in der Tabelle ist vom Typ varchar(50) Der verwendete int-Typ hat eine implizite Konvertierung und es wird kein Index hinzugefügt. Ändern Sie das Feld user_id in den Tabellen b und c in den Typ int.

Da zwischen Tabelle b und Tabelle c eine Zuordnung besteht, erstellen Sie einen Index für die Benutzer-ID von Tabelle b und Tabelle c.

Da zwischen Tabelle a und Tabelle b eine Zuordnung besteht, erstellen Sie einen Index für den Verkäufernamen Feld von Tabelle a und b

Verwenden Sie einen zusammengesetzten Index. Eliminieren Sie temporäre Tabellen und Sortierung.

Optimieren Sie zunächst SQL.

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

Sehen Sie sich die Ausführungszeit nach der Optimierung an Warninformationen anzeigen

Zusammenfassung

1. Erklärung des Ausführungsplans anzeigen

2. Wenn eine Alarmmeldung vorliegt, überprüfen Sie die Alarminformationen, zeigen Sie Warnungen an;

3. Sehen Sie sich die Tabellenstruktur und Indexinformationen an, die in SQL enthalten sind

4. Denken Sie über mögliche Optimierungspunkte gemäß nach Ausführungsplan

Führen Sie Tabellenstrukturänderungen durch, fügen Sie Indizes hinzu, schreiben Sie SQL neu usw. gemäß möglichen Optimierungspunkten. Vorgang

Anzeigen der optimierten Ausführungszeit und des Ausführungsplans

Wenn der Optimierungseffekt nicht offensichtlich ist, wiederholen Sie den Vorgang der vierte Schritt

Verwandte Empfehlungen: „

MySQL-Tutorial

“

Das obige ist der detaillierte Inhalt vonAnhand von Beispielen erfahren Sie, wie Sie SQL optimieren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!