Optimierung der SQL-Anweisung der MySQL-Datenbank

Beurteilen Sie das Problem mit SQL

Wenn Sie beurteilen, ob ein Problem mit SQL vorliegt, können Sie beurteilen es durch zwei Erscheinungen:

• Symptome auf Systemebene
  • Erhebliche CPU-Auslastung
  • Schwere E/A-Wartezeit
  • Seitenantwortzeit ist zu lang
  • Anwendungsprotokoll Wenn Fehler wie Timeout auftreten

können Sie mit dem Befehl sar und dem Befehl top den aktuellen Systemstatus anzeigen.

Sie können den Systemstatus auch durch Überwachungstools wie Prometheus、Grafana beobachten.

• Erscheinen der SQL-Anweisung
  • Langfristig
  • Ausführungszeit ist zu lang
  • Daten vollständig abrufen Tabellenscan
  • Die Zeilen und Kosten im Ausführungsplan sind sehr groß

Lange SQL ist leicht zu verstehen. Wenn eine SQL zu lang ist, beeinträchtigt dies die Lesbarkeit wird definitiv schlecht sein und es werden definitiv Probleme auftreten. Um das SQL-Problem weiter zu bestimmen, müssen wir mit dem Ausführungsplan beginnen, wie unten gezeigt:

Der Ausführungsplan sagt uns, dass diese Abfrage einen vollständigen Tabellenscan durchlaufen hatType=ALL und die Zeilen sind sehr groß (9950400). Grundsätzlich kann man davon ausgehen, dass es sich um ein „geschmackvolles“ SQL handelt.

Problem-SQL abrufen

Verschiedene Datenbanken haben unterschiedliche Erfassungsmethoden. Das Folgende ist das langsame Abfrage-SQL-Erfassungstool für aktuelle Mainstream-Datenbanken

• MySQL
  • Langsames Abfrageprotokoll
  • Testtool Loadrunner
  • Perconas Ptquery und andere Tools
• Oracle
  • AWR Bericht
  • Testtool Loadrunner usw.
  • Verwandte interne Ansichten wie v$sql, v$session_wait usw.
  • GRID CONTROL-Überwachungstool
• Dameng-Datenbank
  • AWR-Bericht
  • Testtool Loadrunner usw.
  • Dameng-Leistungsüberwachungstool (dem)
  • Verwandte interne Ansichten wie v$sql, v$session_wait usw.

SQL-Schreibfähigkeiten

Es gibt mehrere allgemeine Fähigkeiten beim SQL-Schreiben :

• Sinnvoller Einsatz von Indizes

Bei weniger Indizes sind Abfragen langsamer; beim Ausführen von Hinzufügungen, Löschungen und Änderungen nimmt der Index viel Platz in Anspruch muss dynamisch verwaltet werden, was sich auf die Leistung auswirkt
Hohe Auswahlrate (weniger doppelte Werte) Und es muss häufig darauf verwiesen werden, dass B-Tree-Indizes erstellt werden müssen. Es müssen mehr komplexe Dokumenttypabfragen indiziert werden effizient bei Volltextindizes; die Indexerstellung sollte auf ein Gleichgewicht zwischen Abfrage- und DML-Leistung achten; Verwenden Sie UNION ALL anstelle von UNION. UNION ALL hat eine höhere Ausführungseffizienz als UNION. Bei der Ausführung muss UNION die Daten deduplizieren >• Vermeiden Sie das Schreiben von „select *“

Bei der Ausführung von SQL muss der Optimierer * in bestimmte Spalten konvertieren und darf den Index nicht überschreiben.

• Es wird empfohlen, einen Index für JOIN-Felder zu erstellen

Im Allgemeinen werden JOIN-Felder im Voraus indiziert

• Vermeiden Sie komplexes SQL Anweisungen

Verbesserung der Lesbarkeit; Vermeidung der Wahrscheinlichkeit, dass langsame Abfragen in mehrere kurze Abfragen umgewandelt und vom Unternehmensende verarbeitet werden können

• Vermeiden Sie, dass 1=1 geschrieben wird 🎜>

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

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

SQL-Optimierung Ausführungsplan

Lesen Sie unbedingt den Ausführungsplan, bevor Sie die SQL-Optimierung abschließen. 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)

字段	解释
id	每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
select_type	查询中每个select 字句的类型
table	被操作的对象名称，通常是表名，但有其他格式
partitions	匹配的分区信息(对于非分区表值为NULL)
type	连接操作的类型
possible_keys	可能用到的索引
key	优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
key_len	被优化器选定的索引键长度，单位是字节
ref	表示本行被操作对象的参照对象，无参照对象为NULL
rows	查询执行所扫描的元组个数（对于innodb，此值为估计值）
filtered	条件表上数据被过滤的元组个数百分比
extra	执行计划的重要补充信息，当此列出现Using Optimierung der SQL-Anweisung der MySQL-Datenbanksort , Using temporary 字样时就要小心了，很可能SQL语句需要优化

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

Optimierungsfall

• Tabellenstruktur

• 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-Assoziation Fragen Sie den Bestellstatus des aktuellen Benutzers 10 Stunden vor und nach der aktuellen Zeit ab und sortieren Sie ihn in aufsteigender Reihenfolge nach der Bestellerstellungszeit. Die spezifische SQL lautet 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

• Datenvolumen anzeigen

  

• Ursprüngliche Ausführungszeit

  

• Ursprünglicher Ausführungsplan

  

• Erste Optimierungsideen

• Der Typ des Where-Bedingungsfelds in SQL muss mit der Tabellenstruktur konsistent sein, in der Tabelle user_id Es handelt sich um einen varchar(50)-Typ. Der tatsächlich in SQL verwendete int-Typ hat eine implizite Konvertierung und es wird kein Index hinzugefügt. Ändern Sie die Tabellenfelder b und c user_id in den Typ int.

• Da Tabelle b und Tabelle c vorhanden sind, erstellen Sie einen Index für Tabelle b und Tabelle c user_id

• Weil Tabelle a und Tabelle b existieren Assoziation, erstellen Sie Indizes für die seller_name Felder der Tabellen a und b

• Verwenden Sie zusammengesetzte Indizes, um temporäre Tabellen und Sortierungen zu eliminieren

• Anfänglich Optimierung von 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`);

• Ausführungszeit nach der Optimierung anzeigen

  

• Ausführungszeit nach Optimierungsplan anzeigen

  

• Warninformationen anzeigen

  

• Weiter optimieren
  

• alter table a modify "gmt_create" datetime DEFAULT NULL

• Ausführungszeit anzeigen

• Ausführungsplan anzeigen

Optimierungszusammenfassung

1. Ausführungsplan anzeigen, erklären
2. Wenn Warninformationen vorhanden sind, überprüfen Sie die Alarminformationen, zeigen Sie Warnungen an;
3. Überprüfen Sie die Tabellenstruktur und die Indexinformationen in SQL
4. Denken Sie über mögliche Optimierungspunkte nach gemäß dem Ausführungsplan
5. Möglichst Tabellenstrukturänderungen, Indexergänzungen, SQL-Umschreiben und andere Vorgänge am Optimierungspunkt durchführen
6. Optimierte Ausführungszeit und Ausführungsplan anzeigen

Wenn der Optimierungseffekt nicht offensichtlich ist, wiederholen Sie den vierten Schritt

Empfehlen Sie „MySQL-Video-Tutorial“

Das obige ist der detaillierte Inhalt vonOptimierung der SQL-Anweisung der MySQL-Datenbank. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!