Résumé et partage des idées d'optimisation des requêtes lentes MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement les problèmes liés à l'optimisation des requêtes lentes, notamment l'utilisation de journaux de requêtes lentes pour localiser les requêtes lentes SQL, l'analyse des requêtes lentes SQL via l'explication et la modification de SQL autant que possible Laisser SQL utiliser l'index. Jetons-y un coup d'oeil. J'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

1 Idées d'optimisation des requêtes lentes

Lorsqu'une requête lente se produit, l'idée d'optimisation est la suivante :

• Utiliser les journaux de requêtes lentes pour localiser les requêtes lentes SQL

• Analysez la requête lente SQL en expliquant

• Modifiez le SQL et essayez de créer l'index SQL

2 Journal des requêtes lentes

MySQL fournit une fonction - le journal des requêtes lentes, qui enregistrera le SQL dont le temps de requête dépasse le seuil de temps spécifié. Dans le journal, il nous est pratique de localiser les requêtes lentes et d'optimiser les instructions SQL correspondantes.

Vérifiez d'abord les variables globales liées aux requêtes lentes dans MySQL :

mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | OFF                           |
| log_throttle_queries_not_using_indexes | 0                             |
==========================================================================
| long_query_time                        | 10.000000                     |【1】慢查询的时间阈值
==========================================================================
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
==========================================================================
| slow_query_log                         | OFF                           |【2】慢查询日志是否开启
| slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |【3】慢查询日志文件存储位置
==========================================================================
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)

Ici, nous nous concentrons principalement sur trois variables :

• long_query_time, le seuil de temps d'une requête lente, en secondes, si le temps d'exécution d'une instruction SQL dépasse cette valeur, Ensuite, MySQL l'identifie comme une requête lente

• slow_query_log. Si la fonction de journalisation des requêtes lentes est activée. Elle est désactivée par défaut. Une fois activée, la requête lente est enregistrée

• slow_query_log_file. .L'emplacement de stockage du fichier journal des requêtes lentes

Le journal des requêtes lentes par défaut La fonction est désactivée, nous devons donc l'activer

# 开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
# 设置慢查询时间阈值
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

Après l'avoir défini comme ceci, MySQL perdra ces configurations au redémarrage, et ils doivent être modifiés dans le fichier de configuration pour être efficaces de manière permanente.

3 expliquer

Nous pouvons utiliser expliquer pour analyser l'exécution d'instructions SQL, par exemple :

mysql> explain select sum(1+2);

Les résultats de l'exécution sont les suivants, vous pouvez voir qu'il existe de nombreux champs

Nous examinons principalement certains champs importants field:

• select_type représente le type de requête de l'instruction de requête, y compris une requête simple, une sous-requête, etc.

• table représente la table de requête, qui n'existe pas nécessairement. Il peut s'agir d'une table temporaire obtenue dans cette requête.

• type représente le type de récupération, utilise l'analyse complète de la table, l'analyse d'index, etc.

• possible_keys indique les colonnes d'index qui peuvent être utilisées

• keys indique les colonnes d'index réellement utilisées dans la requête, ce qui est déterminé par l'optimiseur de requête

3.1 champ select_type

3.2 champ type

Pour le moteur de stockage InnoDB, la colonne type est généralement all ou index.

Concernant la valeur du champ type, les performances d'exécution du SQL correspondant se dégradent progressivement de haut en bas. 3.3 Champ supplémentaire reee

Interception Données partielles :

Exécutez l'instruction SQL suivante sans utiliser de champs d'index :

create table user_info_large (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`account` VARCHAR(20) NOT NULL COMMENT '用户账号',
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(20) not null COMMENT '用户密码',
`area` VARCHAR(20) NOT NULL COMMENT '用户地址',
`signature` VARCHAR(50) not null COMMENT '个性签名',
PRIMARY KEY (`id`) COMMENT '主键',
UNIQUE (`account`) COMMENT '唯一索引',
KEY `index_area_signture` (`area`,  `signature`)  COMMENT '组合索引'
);

L'heure de la requête affichée par l'outil Navicat est la suivante : ce n'est pas l'heure à laquelle MySQL exécute réellement SQL. Elle inclut la transmission réseau et d'autres heures :

.

Spécifique à SQL Pour le temps de requête, vous pouvez consulter le journal des requêtes lentes :

mysql> select count(id) from user_info_large;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.38 sec)

Explication sur certaines informations :

Time : L'heure de début de l'exécution SQL

Query_time : Le temps passé sur Requête d'instruction SQL, vous pouvez voir que cela a pris 10 secondes Clock

Lock_time : le temps d'attente pour la table de verrouillage

Rows_sent : le nombre d'enregistrements renvoyés par l'instruction

Rows_examined : le nombre de enregistrements renvoyés par le moteur de stockage

• La requête lente en cours d'exécution ne le sera pas. Si le journal des requêtes lentes est enregistré, il ne sera enregistré dans le journal qu'après avoir attendu la fin de son exécution.
  Nous pouvons utiliser show processlist pour afficher le thread exécutant SQL.
• Exécutez à nouveau l'instruction suivante et utilisez le champ du compte d'index :

  SELECT name from user_info_large ORDER BY name desc limit 0,100000;

  Affichez le journal des requêtes lentes et il n'est pas enregistré.
• Utilisez maintenant expliquer pour visualiser l'exécution des instructions SQL :

  # Time: 2022-09-26T13:44:18.405459Z
  # User@Host: root[root] @  [ip]  Id:  1893
  # Query_time: 10.162999  Lock_time: 0.000113 Rows_sent: 100000  Rows_examined: 2100000
  SET timestamp=1664199858;
  SELECT name from user_info_large ORDER BY name desc limit 0,100000;

  L'analyse est la suivante :



可以看到没有使用到索引，type 为 ALL 表示全表扫描，效率最差，并且 Extra 也是外部排序。

再看看这条 SQL 语句：

explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;

分析情况如下：



type 为 index，使用了索引，使用的索引字段为 account，Extra 显示为使用索引排序。

因此，在实际开发中，我们可以针对慢查询的 SQL，使用 explain 分析语句，根据分析情况以及索引的设计，重新设计 SQL 语句，让 SQL 语句尽量走索引，走合适的索引。

5 优化器与索引

在执行 SQL 时，MySQL 的优化器会根据情况选择索引，但并不能保证其执行时间一定最短，我们可以根据实际情况使用 force key (index) 让 SQL 语句强制走某个索引。

例如，以下语句执行后，key 字段为 account，并没有走主键索引。

explain SELECT count(id) from user_info_large;



如果使用 force key，就可以强制令语句走主键索引。

explain SELECT count(id) from user_info_large force key (PRIMARY);



6 总结

在项目中如果发现部分 SQL 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：

• 通过慢查询日志定位 SQL

• 使用 explain 分析 SQL

• 修改 SQL，令其走合适的索引

 在使用 explain 时，我们主要关注这些字段：

• type

• key

• Extra

在编写 SQL 使用索引的时候，我们尽量注意一下规则：

• 模糊查询不要使用通配符 % 开头，例如 like '%abc'

• 使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or

• 使用复合索引遵循最左原则

• 索引字段不要参加表达式运算、函数运算

推荐学习：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!