乾貨分享！ MySQL慢查詢的實作分析總結

MySQL的慢查詢，全名是慢查詢日誌，是MySQL提供的一種日誌記錄，用來記錄在MySQL中回應時間超過閥值的語句。靜態我們就來介紹介紹，有需要的可以參考參考。

一  為什麼要做這個事

#1  什麼是慢SQL？

這裡指的是MySQL慢查詢，具體指運行時間超過long_query_time值的SQL。

我們常聽常見的MySQL中有二進位日誌binlog、中繼日誌relaylog、重做回溯日誌redolog、undolog等。針對慢查詢，還有一種慢查詢日誌slowlog，用來記錄在MySQL中回應時間超過閥值的語句。

大家不要被慢查詢這個名字誤導，以為慢查詢日誌只會記錄select語句，其實也會記錄執行時間超過了long_query_time設定的閾值的insert、update等DML語句。

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

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

對於我們使用的AliSQL-X-Cluster即XDB來說，預設慢查詢是開啟的，long_query_time設定為1秒。

2  慢查詢為何會導致故障？

真實的慢SQL往往伴隨著大量的行掃描、臨時檔案排序或頻繁的磁碟flush，直接影響就是磁碟IO升高，正常SQL也變成了慢SQL，大面積執行逾時。

去年雙11後，針對技術側曝光的問題，菜鳥CTO線推出多個專案治理，CTO-D各領一項作為sponsor，我所在的大團隊負責慢SQL治理這個專案。

二  要做什麼程度

1  怎麼來測量一個應用程式的慢SQL嚴重程度？

微平均

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

我們認為，該值越大，影響越大；該值越小，影響可能小。

極端情況就是應用程式裡每次執行的SQL全是慢SQL，值為1；應用程式裡每次執行的SQL全不是慢SQL，值為0。

但這個指標帶來的問題是區分度不佳，尤其是對SQL QPS很高且大多數情況下SQL都不是慢查詢的情況，偶發的慢SQL會被淹沒。

另一個問題，偶發的慢SQL是真的慢SQL嗎？我們遇到很多被慢查詢日誌記錄的SQL，實際上可能受到其他慢SQL影響、MySQL磁碟抖動、優化器選擇等原因使得常規查詢下表現顯然不是慢SQL的變成了慢SQL。

宏平均

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

這個演算法建立在被抓到的慢SQL有一定執行次數的基礎上，可以減少假性慢SQL的影響。

當某些應用QPS很低，也就是一天執行SQL的次數很少，如果碰到假性SQL就會造成統計誤差。

執行次數

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

統計最近一週平均每天的慢SQL執行次數，可以消除掉巨集平均帶來的假性SQL問題。

慢SQL模板數量

以上維度均有時間限定範圍，為了追溯慢SQL歷史處理情況，我們也引入了全域慢SQL模板數量維度。

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

2  目標

• #核心應用程式：解決所有的慢SQL

• 一般應用：微平均指標下降50%

#3  CTO報表

以CTO-D為單位根據以上多維度指標統計匯總應用的加權平均，由低到高得出排名，突出頭尾top3，每周播報。

三  為什麼由我來做

猜測可能與我的背景有關，有C/C 背景，曾在上家公司負責過公司層面異地多活架構的設計和落地，對於MySQL比較了解一些。

另外可能是利益無關，我所在小團隊業務剛起步，不存在慢SQL，這樣可以插入到各個業務線去。

四  行動支撐

1  集團MySQL規則

索引規約摘錄部分：

#【強制】超過三個表禁止join。需要join的字段，資料類型保持絕對一致；多表關聯查詢時，保證被關聯的字段需要有索引。

說明：即使雙表join也要注意表索引、SQL效能。

【強制】在varchar欄位上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文字區分度決定索引長度。

說明：索引的長度與區分度是一對矛盾體，一般對字串類型數據，長度為20的索引，區分度會高達90%以上，可以使用count(distinct left(列名, 索引長度))/count(*)的區分度來決定。

【強制】頁面搜尋嚴禁左模糊或全模糊，如果需要請走搜尋引擎來解決。

說明：索引檔案具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那麼無法使用此索引。

【建議】防止因欄位類型不同造成的隱式轉換，導致索引失效。

【參考】建立索引時避免有下列極端誤解：

1） 索引寧濫勿缺少

#

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

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治理专项也被多次点名表扬。

七  小結

這是一篇遲到的總結，現在回頭看覺得這裡面的策略制定、問題分析和解決的過程還蠻值得拿出來和大家分享下。

相關推薦：《mysql教學》

###

以上是乾貨分享！ MySQL慢查詢的實作分析總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！