Comment analyser les performances d'un SQL

Cet article explique comment utiliser Explain pour analyser un SQL.

Il existe en fait de nombreux articles sur Internet qui présentent l'utilisation d'expliquer en détail. Cet article combine des exemples et des principes pour essayer de vous donner une meilleure compréhension, faites-moi confiance. avoir des gains spéciaux après l'avoir lu.

explain se traduit par expliquer. Cela s'appelle un plan d'exécution dans MySQL. Autrement dit, vous pouvez utiliser cette commande pour voir comment MySQL décide d'exécuter le SQL après avoir été analysé par l'optimiseur.

En parlant de l'optimiseur, encore une chose, MySQL dispose d'un puissant optimiseur intégré. La tâche principale de l'optimiseur est d'optimiser le SQL que vous écrivez et de l'exécuter au moindre coût possible. moins de lignes, éviter le tri, etc. Qu'avez-vous vécu lors de l'exécution d'une instruction SQL ? J'ai présenté l'optimiseur dans mon article précédent.

Vous vous demandez peut-être quand utilisez-vous habituellement Explain ? Dans la plupart des cas, certains SQL avec une efficacité de requête relativement lente sont extraits du journal de requêtes lentes de MySQL pour utiliser l'analyse Explication, et certains le sont lors de l'optimisation de MySQL. , comme l'ajout d'index, et l'utilisation d'explication pour analyser si l'index ajouté peut être atteint. De plus, pendant le développement commercial, si les besoins sont satisfaits, vous devrez peut-être utiliser Explication pour en choisir un SQL plus efficace.

Alors, comment utiliser expliquer ? C'est très simple, ajoutez simplement expliquer devant sql, comme indiqué ci-dessous.

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)

Comme vous pouvez le voir, expliquer renverra environ 10 champs. Les champs renvoyés par les différentes versions sont légèrement différents. Chaque champ représente une signification spécifique. Je ne vais pas détailler chaque champ dans cet article. Il y a beaucoup de choses dans l'introduction, et je crains que ce ne soit pas facile pour vous de vous en souvenir. Il est préférable de comprendre d'abord quelques domaines importants.

Je pense que les champs type, key, rows et Extra sont plus importants. Nous utiliserons des exemples spécifiques pour vous aider à mieux comprendre la signification de ces champs.

Tout d'abord, il est nécessaire d'introduire brièvement le sens littéral de ces champs.

type représente la façon dont MySQL accède aux données. Les plus courants incluent l'analyse complète de la table (toutes), la traversée d'index (index), la requête d'intervalle (plage), la requête constante ou équivalente (ref, eq_ref), la clé primaire et autres. valeurs. Requête (const), lorsqu'il n'y a qu'un seul enregistrement dans la table (système). Vous trouverez ci-dessous un classement de l’efficacité, du meilleur au pire.

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

key représente le nom d'index qui sera réellement utilisé dans le processus de requête.

rows représente le nombre de lignes qui peuvent devoir être analysées pendant le processus de requête. Ces données ne sont pas nécessairement exactes et sont des données de statistiques d'échantillonnage MySQL.

Extra représente des informations supplémentaires, indiquant généralement si des index sont utilisés, si un tri est requis, si des tables temporaires seront utilisées, etc.

D’accord, commençons officiellement l’analyse du cas.

Utilisons le moteur de stockage créé dans l'article précédent pour créer une table de test. Nous insérons ici 10 w de données de test. La structure de la table est la suivante :

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

Ensuite, regardez le. Instruction de requête suivante. Faites attention à ceci. La table n'a actuellement qu'un seul index de clé primaire et aucun index ordinaire n'a encore été créé.

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)

La valeur du type est ALL, ce qui signifie que la table entière a été analysée. Veuillez noter que le champ des lignes affiche 100 332 entrées. En fait, nous n'avons que 100 000 éléments de données au total, donc ce champ est juste. une estimation de mysql Pas nécessairement exacte. L'efficacité de cette analyse complète de la table est très faible et doit être optimisée.

Ensuite, nous ajoutons des index ordinaires aux champs a et b respectivement, puis examinons les différentes instructions SQL après avoir ajouté les index.

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)

Le SQL ci-dessus semble-t-il un peu déroutant ? Le type montre en fait qu'un index vient d'être ajouté au champ a, et possible_keys montre également que a_index est disponible, mais la clé affiche null, indiquant que mysql est en fait Il n'utilise pas d'index, pourquoi ?

En effet, si vous sélectionnez *, vous devez revenir à l'index de clé primaire pour trouver le champ b. Ce processus est appelé retour de table. Cette instruction filtrera 90 000 éléments de données qui remplissent les conditions. , ce qui signifie que ces 90 000 éléments de données sont tous Une opération de retour de table est requise et une analyse complète de la table ne contient que 100 000 éléments de données. Par conséquent, du point de vue de l'optimiseur MySQL, ce n'est pas aussi bon qu'une table complète directe. scan, au moins cela élimine le processus de retour de table.

Bien sûr, cela ne signifie pas que tant qu'il y a une opération de retour de table, l'index ne sera pas atteint. La clé pour savoir s'il faut ou non utiliser l'index dépend de la requête que MySQL considère comme la moins chère. Modifions légèrement la condition Where dans le SQL ci-dessus.

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)

Cette fois, la valeur du type est range et la clé est a_index, ce qui signifie que l'index a est atteint car il n'y a que 1000 éléments de données qui répondent à cette condition SQL. considère que 1 000 éléments de données doivent être pris en compte. Le retour de table est également moins cher que l'analyse complète de la table, donc MySQL est en fait un gars très intelligent.

Nous pouvons également voir que la valeur dans le champ Extra est Using index condition, ce qui signifie que l'index est utilisé, mais que la table doit être renvoyée. Regardez l'instruction suivante.

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教程栏目进行学习！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!