Es gibt viele Tutorials zur SQL-Optimierung, aber sie sind ziemlich chaotisch, als ich Zeit hatte, und habe sie aufgeschrieben, um sie mit Ihnen zu teilen. Der folgende Artikel stellt hauptsächlich die allgemeinen Schritte zur SQL-Anweisung vor Freunde, die es brauchen, können es als Referenz mit dem unten stehenden Editor lernen.
Vorwort
Dieser Artikel teilt Ihnen hauptsächlich die allgemeinen Schritte der SQL-Anweisungsoptimierung mit Im Folgenden gibt es nicht viel zu sagen. Werfen wir jedoch 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 MySQL-Client-Verbindung erfolgreich hergestellt wurde, können Sie sie verwenden Der Befehl show [session|global] status stellt Serverstatusinformationen bereit, die auch mit dem Befehl mysqladmin extend-status im Betriebssystem abgerufen werden können.
show status Dem Befehl kann die Option session (Standard) oder global hinzugefügt werden:
session (aktuelle Verbindung)
global (seit dem letzten Start der Daten)
# Com_xxx 表示每个 xxx 语句执行的次数。 mysql> show status like 'Com_%';
Wir kümmern uns normalerweise um die folgenden statistischen Parameter:
Com_select: Die Häufigkeit, mit der der Auswahlvorgang ausgeführt wird. Für eine Abfrage wird nur 1 akkumuliert.
Com_insert: Die Anzahl der durchgeführten Einfügevorgänge. Bei Stapeleinfügevorgängen wird nur einer akkumuliert.
Com_update: Die Häufigkeit, mit der Aktualisierungsvorgänge durchgeführt werden.
Com_delete: Die Häufigkeit, mit der der Löschvorgang ausgeführt wird.
Die oben genannten Parameter werden für alle Tabellenoperationen der Speicher-Engine akkumuliert. Die folgenden Parameter gelten nur für Innodb und der Akkumulationsalgorithmus unterscheidet sich geringfügig:
Innodb_rows_read: Die Anzahl der von der 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: 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- und Aktualisierungs- oder Abfragevorgängen basiert, sowie das ungefähre Ausführungsverhältnis verschiedener Arten von SQL How viel ist es. Die Anzahl der Aktualisierungsvorgänge ist eine Zählung der Anzahl der Ausführungen und wird unabhängig von der Übermittlung oder dem Rollback akkumuliert.
Für Transaktionsanwendungen können Sie Com_commit und Com_rollback verwenden, um die Transaktions-Commit- und Rollback-Situation zu verstehen. Bei Datenbanken mit sehr häufigen Rollback-Vorgängen kann es sein, dass beim Schreiben der Anwendung Probleme auftreten .
Darüber hinaus helfen die folgenden Parameter Benutzern, die Grundsituation der Datenbank zu verstehen:
Verbindungen: Die Anzahl der Versuche, eine Verbindung zum MySQL-Server herzustellen.
Verfügbarkeit: Server-Arbeitszeit.
Slow_queries: Die Anzahl der langsamen Abfragen.
2. Definieren Sie SQL-Anweisungen mit geringer Ausführungseffizienz
1. Suchen Sie solche mit geringer Ausführungseffizienz durch langsame Abfragen Protokolle Niedrigere SQL-Anweisungen: Wenn mysqld mit der Option --log-slow-queries[=file_name] gestartet wird, schreibt es eine Protokolldatei mit allen SQL-Anweisungen, deren Ausführung länger als long_query_time Sekunden dauert.
2. Das langsame Abfrageprotokoll wird nach Abschluss der Abfrage aufgezeichnet. Wenn die Anwendung also Probleme mit der Ausführungseffizienz aufweist, kann das langsame Abfrageprotokoll das Problem nicht finden. Sie können den Befehl show Processlist verwenden, um das aktuelle MySQL anzuzeigen In Bearbeitung befindliche Threads, einschließlich Der Status des Threads, ob die Tabelle gesperrt ist usw. können in Echtzeit angezeigt werden, und die Ausführung von SQL kann in Echtzeit angezeigt werden, während einige Sperrtabellenvorgänge optimiert werden können.
3. Analysieren Sie den Ausführungsplan von ineffizientem SQL durch EXPLAIN
Testdatenbankadresse: https://downloads.mysql.com/docs /sakila-db.zip (lokaler Download)
Um den Gesamtbetrag zu zählen, der von einer bestimmten E-Mail-Adresse für das Ausleihen einer Filmkopie gezahlt wurde, müssen Sie die Kundentabelle „Kunde“ und die Zahlungstabelle „Zahlung“ verknüpfen Führen Sie die Summenoperation im Feld „Zahlungsbetrag“ aus. Der entsprechende Ausführungsplan lautet wie folgt:
mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 filtered: 10.00 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec)
select_type: Stellt den Auswahltyp dar, gemeinsame Werte sind:
einfach: einfache Tabelle und verwendet keine Tabellenverknüpfungen oder Unterabfragen
Primär: Die Hauptabfrage, die Abfrage der äußeren Ebene
Union: Die zweite oder nachfolgende Abfrageanweisung in Union
Unterabfrage: Die erste Auswahl in der Unterabfrage
Tabelle: Die Tabelle, die die Ergebnismenge ausgibt
Typ: Zeigt an, dass MySQL alle Ergebnisse findet In der Tabelle ist die erforderliche Methode oder Zugriffsart die Leistung gängiger Typen vom schlechtesten zum besten: all, index, range, ref, eq_ref, const, system, null:
1.type=ALL, vollständiger Tabellenscan, MySQL durchsucht die gesamte Tabelle, um passende Zeilen zu finden:
mysql> explain select * from film where rating > 9 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 33.33 Extra: Using where 1 row in set, 1 warning (0.01 sec)
2.type=index, 索引全扫描，mysql 遍历整个索引来查询匹配的行
mysql> explain select title form film\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: index possible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)
3.type=range,索引范围扫描，常见于<、<=、>、>=、between等操作：
mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment partitions: NULL type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 1350 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.07 sec)
4.type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：
mysql> explain select * from payment where customer_id = 350 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 23 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec)
索引 idx_fk_customer_id 是非唯一索引，查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中：
mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 26 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec)
5.type=eq_ref,类似 ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用 primary key 或者 unique index 作为关联条件。
mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.b.film_id rows: 1 filtered: 100.00 Extra: Using where 2 rows in set, 1 warning (0.03 sec)
6.type=const/system,单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key 或者唯一索引 unique index 进行查询。
mysql> create table test_const ( -> test_id int, -> test_context varchar(10), -> primary key (`test_id`), -> ); insert into test_const values(1,'hello'); explain select * from ( select * from test_const where test_id=1 ) a \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_const partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
7.type=null, mysql 不用访问表或者索引，直接就能够得到结果：
mysql> explain select 1 from dual where 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 1 row in set, 1 warning (0.00 sec)
类型 type 还有其他值，如 ref_or_null (与 ref 类似，区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询)等。
possible_keys : 表示查询时可能使用的索引。
key ：表示实际使用索引
key-len : 使用到索引字段的长度。
rows ： 扫描行的数量
extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
show warnings 命令
执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写：
MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 filtered: 10.00 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec) MySQL [sakila]> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE.BENNETT@sakilacustomer.org')) | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件，也就是说优化器在改写 sql 时会自动去掉恒成立的条件。
explain 命令也有对分区的支持.
MySQL [sakila]> CREATE TABLE `customer_part` ( -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, -> `store_id` tinyint(3) unsigned NOT NULL, -> `first_name` varchar(45) NOT NULL, -> `last_name` varchar(45) NOT NULL, -> `email` varchar(50) DEFAULT NULL, -> `address_id` smallint(5) unsigned NOT NULL, -> `active` tinyint(1) NOT NULL DEFAULT '1', -> `create_date` datetime NOT NULL, -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`customer_id`) -> -> ) partition by hash (customer_id) partitions 8; Query OK, 0 rows affected (0.06 sec) MySQL [sakila]> insert into customer_part select * from customer; Query OK, 599 rows affected (0.06 sec) Records: 599 Duplicates: 0 Warnings: 0 MySQL [sakila]> explain select * from customer_part where customer_id=130\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer_part partitions: p2 type: const possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warnings (0.00 sec)
可以看到 sql 访问的分区是 p2。
四、通过 performance_schema 分析 sql 性能
旧版本的 mysql 可以使用 profiles 分析 sql 性能，我用的是5.7.18的版本，已经不允许使用 profiles 了，推荐用
performance_schema 分析sql。
五、通过 trace 分析优化器如何选择执行计划。
mysql5.6 提供了对 sql 的跟踪 trace，可以进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划，帮助我们更好的理解优化器的行为。
使用方式：首先打开 trace ，设置格式为 json，设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on; Query OK, 0 rows affected (0.00 sec) MySQL [sakila]> set optimizer_trace_max_mem_size=1000000; Query OK, 0 rows affected (0.00 sec)
接下来执行想做 trace 的 sql 语句，例如像了解租赁表 rental 中库存编号 inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25 4:00:00 ~ 5:00:00 之间出租的记录：
mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)
MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from infomation_schema.optimizer_trace
TRACE: {
"steps": [
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)六、 确定问题并采取相应的优化措施
经过以上步骤，基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。
Das obige ist der detaillierte Inhalt vonTutorial zur Optimierung von SQL-Anweisungen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Was ist Optimierung?
Keyword-Optimierungssoftware von Baidu
Baidu SEO-Methode zur Optimierung des Keyword-Rankings
Was sind die Gründe, warum ein Mobiltelefon eine leere Nummer hat?
Wie man mit Laptop-Verzögerungen und langsamer Reaktion umgeht
Warum hat WLAN ein Ausrufezeichen?
Tutorial zum Zusammenführen mehrerer Wörter zu einem Wort
940mx-Grafikkarte
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
