So analysieren Sie die Leistung eines SQL

In diesem Artikel wird erläutert, wie Sie EXPLAIN zum Analysieren einer SQL verwenden.

Es gibt tatsächlich viele Artikel im Internet, die die Verwendung von EXPLAIN im Detail vorstellen. Dieser Artikel kombiniert Beispiele und Prinzipien, um Ihnen ein besseres Verständnis zu vermitteln. Sie sollten es ernst nehmen besondere Vorteile haben, nachdem Sie es gelesen haben.

explain bedeutet „erklären“. Dies wird in MySQL als Ausführungsplan bezeichnet. Das heißt, Sie können diesen Befehl verwenden, um zu sehen, wie MySQL nach der Analyse durch den Optimierer entscheidet.

Apropos Optimierer: MySQL verfügt über einen leistungsstarken integrierten Optimierer. Die Hauptaufgabe des Optimierers besteht darin, das von Ihnen geschriebene SQL zu optimieren und es mit möglichst geringen Kosten auszuführen weniger Zeilen, Sortierung vermeiden usw. Was machen Sie durch, wenn Sie eine SQL-Anweisung ausführen? Ich habe den Optimierer in meinem vorherigen Artikel vorgestellt.

Sie fragen sich vielleicht, wann Sie normalerweise EXPLAIN verwenden. In den meisten Fällen werden einige SQL-Anweisungen mit relativ langsamer Abfrageeffizienz aus dem langsamen Abfrageprotokoll von MySQL extrahiert, und einige werden bei der Optimierung von MySQL verwendet B. das Hinzufügen von Indizes und die Verwendung von EXPLAIN, um zu analysieren, ob der hinzugefügte Index erreicht werden kann. Wenn die Anforderungen erfüllt sind, müssen Sie außerdem EXPLAIN verwenden, um einen effizienteren SQL-Code auszuwählen.

Wie benutzt man „explain“? Es ist ganz einfach, „explain“ vor SQL einzufügen, wie unten gezeigt.

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

Wie Sie sehen können, werden in diesem Artikel etwa 10 Felder zurückgegeben Vieles steht in der Einleitung, und ich fürchte, es wird Ihnen nicht leicht fallen, sich daran zu erinnern. Es ist besser, zuerst ein paar wichtige Bereiche zu verstehen.

Ich denke, die Felder Typ, Schlüssel, Zeilen und Extra sind wichtiger. Wir werden konkrete Beispiele verwenden, um Ihnen zu helfen, die Bedeutung dieser Felder besser zu verstehen.

Zunächst ist es notwendig, die wörtliche Bedeutung dieser Felder kurz vorzustellen.

Typ stellt die Art und Weise dar, wie MySQL auf Daten zugreift: vollständiger Tabellenscan (alle), Indexdurchquerung (Index), Intervallabfrage (Bereich), konstante oder gleichwertige Abfrage (ref, eq_ref), Primärschlüssel und andere Werte abfragen (const), wenn nur ein Datensatz in der Tabelle (System) vorhanden ist. Nachfolgend finden Sie eine Rangfolge der Effizienz vom besten zum schlechtesten.

system > const > eq_ref > ref > range > index > all

key stellt den Indexnamen dar, der tatsächlich im Abfrageprozess verwendet wird.

Zeilen stellen die Anzahl der Zeilen dar, die möglicherweise während des Abfragevorgangs gescannt werden müssen. Diese Daten sind nicht unbedingt korrekt und stammen aus MySQL-Stichprobenstatistiken.

Extra stellt einige zusätzliche Informationen dar, die normalerweise zeigen, ob Indizes verwendet werden, ob eine Sortierung erforderlich ist, ob temporäre Tabellen verwendet werden usw.

Okay, beginnen wir offiziell mit der Fallanalyse.

Verwenden wir die im vorherigen Artikel erstellte Speicher-Engine, um eine Testtabelle zu erstellen. Wir fügen hier 10 W Testdaten ein. Die Tabellenstruktur ist wie folgt:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Schauen Sie sich dann an Beachten Sie die folgende Abfrageanweisung: Die Tabelle verfügt derzeit nur über einen Primärschlüsselindex und es wurde noch kein gewöhnlicher Index erstellt.

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Der Typwert ist ALL, was bedeutet, dass die gesamte Tabelle gescannt wurde. Bitte beachten Sie, dass das Zeilenfeld insgesamt nur 100.000 Daten enthält eine Schätzung von MySQL. Nicht unbedingt korrekt. Die Effizienz dieses vollständigen Tabellenscans ist sehr gering und muss optimiert werden.

Als nächstes fügen wir gewöhnliche Indizes zu den Feldern a und b hinzu und schauen uns dann die verschiedenen SQL-Anweisungen an, nachdem wir die Indizes hinzugefügt haben.

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Sieht das obige SQL etwas verwirrend aus? Der Typ zeigt tatsächlich an, dass gerade ein Index zum Feld a hinzugefügt wurde, und „possible_keys“ zeigt auch an, dass a_index verfügbar ist, aber der Schlüssel zeigt null an, was darauf hinweist, dass es sich tatsächlich um MySQL handelt Es wird kein Index verwendet. Warum?

Dies liegt daran, dass Sie bei Auswahl von * zum Primärschlüsselindex zurückkehren müssen, um das b-Feld zu finden. Dieser Vorgang wird als Tabellenrückgabe bezeichnet. Diese Anweisung filtert 90.000 Daten heraus, die die Bedingungen erfüllen Dies bedeutet, dass diese 90.000 Datenelemente alle eine Tabellenrückgabeoperation erfordern und ein vollständiger Tabellenscan nur 100.000 Datenelemente umfasst. Daher ist er aus Sicht des MySQL-Optimierers nicht so gut wie eine direkte vollständige Tabelle Scan, zumindest eliminiert es den Tabellenrückgabeprozess.

Natürlich bedeutet dies nicht, dass der Index nicht erreicht wird, solange es eine Tabellenrückgabeoperation gibt. Der Schlüssel zur Verwendung des Index hängt davon ab, welche Abfrage MySQL für günstiger hält. Lassen Sie uns die Where-Bedingung in der obigen SQL leicht ändern.

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

Dieses Mal ist der Typwert „range“ und der Schlüssel ist „a_index“, was bedeutet, dass der a-Index erreicht wird, da es nur 1000 Datenelemente gibt, die diese SQL-Bedingung erfüllen Die Berücksichtigung von 1000 zu berücksichtigenden Daten ist auch günstiger als der vollständige Tabellenscan, daher ist MySQL tatsächlich ein sehr kluger Kerl.

Wir können auch sehen, dass der Wert im Feld „Extra“ die Bedingung „Index verwenden“ lautet, was bedeutet, dass der Index verwendet wird, die Tabelle jedoch zurückgegeben werden muss. Sehen Sie sich die folgende Anweisung an.

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

这个 Extra 中的值为 Using where; Using index ，表示查询用到了索引，且要查询的字段在索引中就能拿到，不需要回表，显然这种效率比上面的要高，所以不要轻易写 select * ，只查询业务需要的字段即可，这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 Extra 中返回了一个 Using filesort，意味着需要排序，这种是需要重点优化的的，也就是说查到数据后，还需要 mysql 在内存中对其进行排序，你要知道索引本身就是有序的，所以一般来讲要尽量利用索引的有序性，比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | a_index | 5       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

我们再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | ab_index | 5       | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

这条 sql 刚刚在上面也有讲到过，在没有创建复合索引的时候，是走的全表扫描，现在其实是利用了覆盖索引，同样是免去了回表过程，即在 （ab_index） 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划，也提到了一些常见的索引优化，事实上还有更多的可能性，你也可以自己去写一个 sql ，然后使用 explain 分析，看看有哪些是可以被优化的。

这篇文章我断断续续写了有三四天了，本来准备了更多的例子，但每次都是写了一部分，思路也打乱了，好了，有问题欢迎在下面留言交流，文章对你有帮助，点个赞表示鼓励支持。

更多MySQL相关技术文章，请访问MySQL教程栏目进行学习！

Das obige ist der detaillierte Inhalt vonSo analysieren Sie die Leistung eines SQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!