Teilen Sie nützliche Informationen! Zusammenfassung der praktischen Analyse langsamer MySQL-Abfragen

MySQLs langsame Abfrage, deren vollständiger Name Slow Query Log ist, ist ein von MySQL bereitgestellter Protokolldatensatz, der zum Aufzeichnen von Anweisungen verwendet wird, deren Antwortzeit den Schwellenwert in MySQL überschreitet. Wir werden die Statik einführen, und Sie können bei Bedarf darauf zurückgreifen.

一 Warum müssen wir das tun?

1 Was ist langsames SQL?

Dies bezieht sich auf langsame MySQL-Abfragen, insbesondere auf SQL, dessen Laufzeit den long_query_time-Wert überschreitet.

Wir hören oft, dass zu den gängigen MySQL-Binärprotokollen Binlog, Relay Log Relaylog, Redo Rollback Log Redolog, Undolog usw. gehören. Für langsame Abfragen gibt es auch ein langsames Abfrageprotokoll, Slowlog, mit dem Anweisungen aufgezeichnet werden, deren Antwortzeit den Schwellenwert in MySQL überschreitet.

Lassen Sie sich nicht durch den Namen „langsame Abfrage“ in die Irre führen, wenn Sie denken, dass das langsame Abfrageprotokoll nur SELECT-Anweisungen aufzeichnet. Tatsächlich werden auch Einfüge-, Aktualisierungs- und andere DML-Anweisungen aufgezeichnet, deren Ausführungszeit den durch long_query_time festgelegten Schwellenwert überschreitet.

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

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

Für den von uns verwendeten AliSQL-X-Cluster, also XDB, ist die langsame Abfrage standardmäßig aktiviert und long_query_time auf 1 Sekunde eingestellt.

2 Warum führt eine langsame Abfrage zu Fehlern?

Wirklich langsames SQL geht oft mit einer großen Anzahl von Zeilenscans, temporärer Dateisortierung oder häufigen Festplattenleerungen einher. Die direkte Auswirkung besteht darin, dass die Festplatten-IO zunimmt, normales SQL auch zu langsamem SQL wird und es bei der Ausführung in großem Umfang zu Zeitüberschreitungen kommt.

Nach dem Double 11 im letzten Jahr startete die CTO-Linie von Cainiao als Reaktion auf die aufgedeckten Probleme mehrere spezielle Governance-Projekte, von denen jedes eines als Sponsor erhielt, und mein großes Team war für das spezielle Projekt verantwortlich langsame SQL-Governance.

2 Inwieweit

1 Wie misst man den Schweregrad von langsamem SQL in einer Anwendung?

Mikrodurchschnitt

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

Wir glauben, dass die Auswirkung umso größer sein kann, je kleiner der Wert ist.

Der Extremfall ist, dass jedes in der Anwendung ausgeführte SQL langsames SQL ist und der Wert 1 ist; jedes in der Anwendung ausgeführte SQL ist kein langsames SQL und der Wert ist 0.

Aber das Problem dieses Indikators besteht darin, dass die Unterscheidung nicht gut ist, insbesondere in Situationen, in denen SQL QPS sehr hoch ist und SQL in den meisten Fällen keine langsame Abfrage ist, gelegentlich wird langsames SQL überfordert.

Noch eine Frage: Ist das gelegentliche langsame SQL wirklich langsames SQL? Wir stoßen auf eine Menge SQL, die im langsamen Abfrageprotokoll aufgezeichnet wird. Tatsächlich kann es durch anderes langsames SQL, MySQL-Festplattenjitter, Optimiererauswahl und andere Gründe beeinträchtigt werden, sodass die Leistung regulärer Abfragen offensichtlich nicht langsames SQL ist wird langsames SQL.

Makro-Durchschnitt

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

Dieser Algorithmus basiert auf der Tatsache, dass das abgefangene langsame SQL eine bestimmte Anzahl von Ausführungen hat, was die Auswirkungen von falsch langsamem SQL reduzieren kann.

Wenn die QPS einiger Anwendungen sehr niedrig ist, dh die Anzahl der SQL-Ausführungen pro Tag sehr gering ist, treten statistische Fehler auf, wenn falsches SQL auftritt.

Ausführungszeiten

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

Zählt die durchschnittliche Anzahl langsamer SQL-Ausführungen pro Tag in der letzten Woche, wodurch durch Makro-Mittelwertbildung verursachte falsche SQL-Probleme beseitigt werden können.

Anzahl langsamer SQL-Vorlagen

Die oben genannten Dimensionen haben alle ein Zeitlimit. Um die historische Verarbeitung langsamer SQL zu verfolgen, haben wir auch die globale Dimension langsamer SQL-Vorlagenmenge eingeführt.

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

2 Ziel

• Kernanwendung: Alle langsamen SQL-Anweisungen lösen

• Allgemeine Anwendung: Mikrodurchschnittsindikator sinkt um 50 %

3 CTO-Bericht.

Als CTO - D ist die Einheit, die auf dem gewichteten Durchschnitt der oben genannten mehrdimensionalen Indikatorstatistiken und zusammenfassenden Anwendungen basiert, von niedrig nach hoch eingestuft wird, die ersten drei hervorhebt und jede Woche ausgestrahlt wird.

3 Warum sollte ich das tun? Die Vermutung hängt möglicherweise mit meinem Hintergrund zusammen. Ich habe einen C/C++-Hintergrund und war in meinem vorherigen Unternehmen für das Design und die Implementierung der Remote-Multi-Active-Architektur verantwortlich. und ich weiß viel über MySQL.

Außerdem hat es möglicherweise nichts mit Interessen zu tun. Das Geschäft meines kleinen Teams hat gerade erst begonnen und es gibt kein langsames SQL, sodass es in verschiedene Geschäftsbereiche eingefügt werden kann.

Unterstützung für vier Aktionen

1-Gruppen-MySQL-Protokoll Auszug aus dem Indexprotokoll:

[Obligatorisch] Der Beitritt ist für mehr als drei Tabellen verboten. Die Datentypen der zu verknüpfenden Felder müssen absolut konsistent sein. Stellen Sie bei der Abfrage von Korrelationen mit mehreren Tabellen sicher, dass die zu korrelierenden Felder über Indizes verfügen.

Hinweis: Auch beim Zusammenführen von Doppeltabellen müssen Sie auf Tabellenindizes und SQL-Performance achten.

[Obligatorisch] Beim Erstellen eines Index für ein Varchar-Feld muss die Indexlänge angegeben werden. Es ist nicht erforderlich, das gesamte Feld zu indizieren. Die Indexlänge wird basierend auf der tatsächlichen Textunterscheidung bestimmt.

Hinweis: Die Länge und die Unterscheidung des Index sind zwei Widersprüche. Bei Daten vom Typ String beträgt die Unterscheidung im Allgemeinen mehr als 90 % für einen Index mit einer Länge von 20. Sie können count(distinct) verwenden left(Spaltenname, Indexlänge) )/count(*).

[Obligatorisch] Es ist strengstens verboten, bei der Seitensuche die linke oder vollständige Unschärfe zu verwenden. Bitte verwenden Sie bei Bedarf die Suchmaschine, um das Problem zu lösen.

Hinweis: Die Indexdatei verfügt über die Präfix-Matching-Funktion ganz links von B-Tree. Wenn der Wert auf der linken Seite unbestimmt ist, kann dieser Index nicht verwendet werden.

【Empfohlen】Verhindern Sie implizite Konvertierungen, die durch unterschiedliche Feldtypen verursacht werden und zu Indexfehlern führen.

[Referenz] Vermeiden Sie die folgenden extremen Missverständnisse beim Erstellen eines Index:

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

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  被人影响