Partagez des informations utiles ! Résumé de l'analyse pratique des requêtes lentes MySQL

La requête lente de MySQL, dont le nom complet est journal des requêtes lentes, est un enregistrement de journal fourni par MySQL, qui est utilisé pour enregistrer les instructions dont le temps de réponse dépasse le seuil dans MySQL. Nous présenterons la statique, et vous pourrez vous y référer si nécessaire.

一 Pourquoi devons-nous faire cela

1 Qu'est-ce que le SQL lent ?

Cela fait référence à une requête lente MySQL, faisant spécifiquement référence à SQL dont la durée d'exécution dépasse la valeur long_query_time.

Nous entendons souvent dire que les journaux binaires MySQL courants incluent binlog, relay log relaylog, redo rollback log redolog, undolog, etc. Pour les requêtes lentes, il existe également un journal des requêtes lentes, slowlog, qui est utilisé pour enregistrer les instructions dont le temps de réponse dépasse le seuil dans MySQL.

Ne vous laissez pas tromper par le nom de requête lente, en pensant que le journal des requêtes lentes n'enregistrera que les instructions de sélection. En fait, il enregistrera également les instructions d'insertion, de mise à jour et autres instructions DML dont le temps d'exécution dépasse le seuil défini par long_query_time.

# 查看慢SQL是否开启
show variables like "slow_query_log%";

# 查看慢查询设定的阈值 单位:秒
show variables like "long_query_time";

Pour le AliSQL-X-Cluster que nous utilisons, qui est XDB, la requête lente est activée par défaut et long_query_time est défini sur 1 seconde.

2 Pourquoi une requête lente provoque-t-elle un échec ?

Un SQL vraiment lent s'accompagne souvent d'un grand nombre d'analyses de lignes, d'un tri de fichiers temporaires ou de vidages de disque fréquents. L'impact direct est que les E/S du disque augmentent, que le SQL normal devient également du SQL lent et que les exécutions à grande échelle expirent.

Après le Double 11 de l'année dernière, en réponse aux problèmes exposés sur le plan technique, la ligne Cainiao CTO a lancé plusieurs projets spéciaux de gouvernance CTO-D chacun en a reçu un en tant que sponsor, et ma grande équipe était responsable du projet spécial de. gouvernance SQL lente.

2 Dans quelle mesure

1 Comment mesurer la gravité d'un SQL lent dans une application ?

Micro moyenne

sum(aone应用慢SQL执行次数)
-----------------------
sum(aone应用SQL执行次数)

Nous pensons que plus la valeur est grande, plus l'impact est grand ; plus la valeur est petite, l'impact peut être faible.

Le cas extrême est que chaque SQL exécuté dans l'application est du SQL lent et que la valeur est 1 ; chaque SQL exécuté dans l'application n'est pas du SQL lent et la valeur est 0.

Mais le problème posé par cet indicateur est que la discrimination n'est pas bonne, en particulier dans les situations où SQL QPS est très élevé et où SQL n'est pas une requête lente dans la plupart des cas, un SQL lent occasionnel sera submergé.

Une autre question : le SQL lent occasionnellement est-il vraiment lent ? Nous rencontrons beaucoup de SQL enregistré dans le journal des requêtes lentes. En fait, cela peut être affecté par d'autres raisons SQL lentes, la gigue du disque MySQL, la sélection de l'optimiseur, de sorte que les performances des requêtes régulières ne sont évidemment pas des SQL lentes. mais devient SQL lent.

Macro moyenne

sum(慢SQL 1执行次数)    sum(慢SQL n执行次数)
-----------------  +  ------------------
sum(SQL 1执行次数)      sum(SQL n执行次数)
---------------------------------------
                   n

Cet algorithme est basé sur le fait que le SQL lent capturé a un certain nombre d'exécutions, ce qui peut réduire l'impact du SQL lent faux.

Lorsque le QPS de certaines applications est très faible, c'est-à-dire que le nombre de fois que SQL est exécuté par jour est très faible, si du faux SQL est rencontré, des erreurs statistiques se produiront.

Temps d'exécution

sum(aone应用慢SQL执行次数)
-----------------------
           7

Compte le nombre moyen d'exécutions SQL lentes par jour au cours de la semaine dernière, ce qui peut éliminer les faux problèmes SQL causés par la moyenne des macros.

Nombre de modèles SQL lents

Les dimensions ci-dessus ont toutes une limite de temps. Afin de retracer l'historique du traitement du SQL lent, nous avons également introduit la dimension globale de quantité de modèle SQL lent.

count(distinct(aone应用慢SQL模板) )

2 Objectif

• Application principale : Résoudre tous les SQL lents

• Application courante : L'indicateur moyen du micro chute de 50 %

3 Rapport CTO

En tant que CTO - D est l'unité basée sur la moyenne pondérée des statistiques d'indicateurs multidimensionnels et des applications récapitulatives ci-dessus, classées de bas en haut, mettant en évidence les trois premiers et diffusées chaque semaine.

3 Pourquoi devrais-je le faire

La supposition peut être liée à mon expérience. J'ai une formation en C/C++. J'étais responsable de la conception et de la mise en œuvre de l'architecture multiactive à distance au niveau de l'entreprise dans mon ancienne entreprise. et j'en sais beaucoup sur MySQL.

De plus, cela n'a peut-être rien à voir avec les intérêts. L'activité de ma petite équipe vient de démarrer, et il n'y a pas de SQL lent, il peut donc être inséré dans différents secteurs d'activité.

Support à quatre actions

1 Protocole MySQL de groupe

Extrait du protocole d'index :

[Obligatoire] La jointure est interdite pour plus de trois tables. Les types de données des champs qui doivent être joints doivent être absolument cohérents ; lors de l'interrogation de corrélations multi-tables, assurez-vous que les champs corrélés doivent avoir des index.

Remarque : même lorsque vous rejoignez des tables doubles, vous devez faire attention aux index des tables et aux performances SQL.

[Obligatoire] Lors de la création d'un index sur un champ varchar, vous devez spécifier la longueur de l'index. Il n'est pas nécessaire d'indexer l'intégralité du champ. La longueur de l'index est déterminée en fonction de la distinction réelle du texte.

Remarque : La longueur et la distinction de l'index sont une paire de contradictions. Généralement, pour les données de type chaîne, la distinction atteindra 90 % ou plus pour un index d'une longueur de 20. Vous pouvez utiliser count(distinct left(nom de la colonne, longueur de l'index) )/count(*).

[Obligatoire] Il est strictement interdit d'utiliser le flou gauche ou le flou complet dans la recherche de page. Si nécessaire, veuillez utiliser le moteur de recherche pour résoudre le problème.

Remarque : le fichier d'index a la fonctionnalité de correspondance de préfixe la plus à gauche de B-Tree. Si la valeur à gauche est indéterminée, cet index ne peut pas être utilisé.

【Recommandé】Empêchez la conversion implicite causée par différents types de champs, entraînant un échec de l'index.

[Référence] Évitez les malentendus extrêmes suivants lors de la création d'un index :

1) Il vaut mieux avoir trop d'index que d'en manquer

认为一个查询就需要建一个索引。

2） 吝啬索引的创建

认为索引会消耗空间、严重拖慢更新和新增速度。

3） 抵制唯一索引

认为唯一索引一律需要在应用层通过“先查后插”方式解决。

2  DB变更标准

DDL需要控制变更速度，注意灰度和并发控制，变更发布需要在规定的变更发布窗口内。

五  分享一些我参与优化的例子

1  数据分布不均匀

1）分库分表不合理

该业务数据分了8个库，每个库分了16张表，通过查看表空间可以看到数据几乎都分布在各个库的某2张表中。分库分表的策略有问题，另外过高预估了业务增量，这个持保留意见。

2）索引不合理

单表创建了idx_logistics_corp_id_special_id的联合索引，但即便这样区分度依然太低，根据实验及业务反馈(logistics_corp_id,transport_type_id)字段组合区分度非常高，且业务存在transport_type_id的单查场景。

2  索引问题

SELECT
  COUNT(0) AS `tmp_count`
FROM(
    SELECT
      `table_holder`.`user_id`,
      `table_holder`.`sc_item_id`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 1 THEN `table_holder`.`quantity`
          ELSE 0
        END
      ) AS `saleable_quantity`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 1 THEN `table_holder`.`lock_quantity`
          ELSE 0
        END
      ) AS `saleable_lock_quantity`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 401 THEN `table_holder`.`quantity`
          ELSE 0
        END
      ) AS `transfer_on_way_quantity`,
      `table_holder`.`store_code`,
      MAX(`table_holder`.`gmt_modified`) AS `gmt_modified`
    FROM
      `table_holder`
    WHERE(`table_holder`.`is_deleted` = 0)
      AND(`table_holder`.`quantity` > 0)
      AND `table_holder`.`user_id` IN(3405569954)
      AND `table_holder`.`store_code` IN('ZJJHBHYTJJ0001', '...1000多个')
    GROUP BY
      `table_holder`.`user_id`,
      `table_holder`.`sc_item_id`
    ORDER BY
      `table_holder`.`user_id` ASC,
      `table_holder`.`sc_item_id` ASC
  ) `a`;

这个case对应的表有store_code索引，因此认为没问题，没办法优化了。实则通过执行计划，我们发现MySQL选择了全表扫描。针对该case实践发现，当范围查询的个数超过200个时，索引优化器将不再使用该字段索引。

最终经过拉取最近一段时间的相关查询SQL，结合业务的数据分布，我们发现采用(is_deleted,quantity)即可解决。

判断执行计划采用的索引长度：key_len的长度计算公式(>=5.6.4)

char(10)允许NULL      =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL)
char(10)不允许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)
varchr(10)允许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(变长字段)
varchr(10)不允许NULL  =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 2(变长字段)
int允许NULL           =  4 + 1(NULL)
int不允许NULL         =  4
timestamp允许NULL     =  4 + 1(NULL)
timestamp不允许NULL   =  4
datatime允许NULL      =  5 + 1(NULL)
datatime不允许NULL    =  5

3  被人影响

用到了索引却依然被爆出扫描2千万行：

索引字段区分度很高：

同时期常规SQL变为了慢查询：

DB数据盘访问情况：

排查共用物理机其他实例的情况，发现有个库在问题时间附近有很多慢sql需要排序，写临时文件刚好写入了2GB：

多个MySQL实例leader节点混合部署在同一台物理机，虽然通过docker隔离了CPU、MEM等资源，但目前还没有做到buffer io的隔离。

4  无法解决

通过汇总分析高频的查询并结合业务得出合适的索引往往能够解决日常遇到的慢查询，但这并不是万能的。

比如有可能索引越加越多，乃至成了这样：

有些场景，比如支持多个字段组合查询，又没有必填项，如果都要通过索引来支持显然是不合理的。

查询场景下，将区分度较高的字段设定为必填项是个好习惯；查询组合很多的情况下考虑走搜索支持性更好的存储或者搜索引擎。

六  日常化处理

随着各个CTO-D线的深入治理，各项指标较之前均有非常大的改观，比如核心应用完成慢查询清零，影响最大的一些慢SQL被得以解决，而我所在的团队排名也由最初的尾部top3进入到头部top3。
慢SQL治理进入日常化，通过每周固定推送慢SQL工单、owner接手处理、结单，基本形成了定期清零的习惯和氛围，慢SQL治理专项也被多次点名表扬。

Sept résumés

Il s'agit d'un résumé tardif. Avec le recul, je pense que le processus de formulation de la stratégie, d'analyse des problèmes et de solution mérite d'être partagé avec tout le monde.

Recommandations associées : "Tutoriel 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!