MySQL的慢查詢，全名是慢查詢?nèi)罩荆荕ySQL提供的一種日志記錄，用來(lái)記錄在MySQL中響應(yīng)時(shí)間超過(guò)閥值的語(yǔ)句。靜態(tài)我們就來(lái)介紹介紹，有需要的可以參考參考。

一 為什么要做這個(gè)事情

1 什么是慢SQL？

這里指的是MySQL慢查詢，具體指運(yùn)行時(shí)間超過(guò)long_query_time值的SQL。

我們常聽(tīng)常見(jiàn)的MySQL中有二進(jìn)制日志binlog、中繼日志relaylog、重做回滾日志redolog、undolog等。針對(duì)慢查詢，還有一種慢查詢?nèi)罩緎lowlog，用來(lái)記錄在MySQL中響應(yīng)時(shí)間超過(guò)閥值的語(yǔ)句。

大家不要被慢查詢這個(gè)名字誤導(dǎo)，以為慢查詢?nèi)罩局粫?huì)記錄select語(yǔ)句，其實(shí)也會(huì)記錄執(zhí)行時(shí)間超過(guò)了long_query_time設(shè)定的閾值的insert、update等DML語(yǔ)句。

# 查看慢SQL是否開(kāi)啟 show variables like "slow_query_log%";  # 查看慢查詢?cè)O(shè)定的閾值 單位:秒 show variables like "long_query_time";

對(duì)于我們使用的AliSQL-X-Cluster即XDB來(lái)說(shuō)，默認(rèn)慢查詢是開(kāi)啟的，long_query_time設(shè)置為1秒。

2 慢查詢?yōu)楹螘?huì)導(dǎo)致故障？

真實(shí)的慢SQL往往會(huì)伴隨著大量的行掃描、臨時(shí)文件排序或者頻繁的磁盤flush，直接影響就是磁盤IO升高，正常SQL也變?yōu)榱寺齋QL，大面積執(zhí)行超時(shí)。

去年雙11后，針對(duì)技術(shù)側(cè)暴露的問(wèn)題，菜鳥CTO線推出多個(gè)專項(xiàng)治理，CTO-D各領(lǐng)一項(xiàng)作為sponsor，我所在的大團(tuán)隊(duì)負(fù)責(zé)慢SQL治理這個(gè)專項(xiàng)。

二 要做到什么程度

1 怎么來(lái)衡量一個(gè)應(yīng)用的慢SQL嚴(yán)重程度？

微平均

sum(aone應(yīng)用慢SQL執(zhí)行次數(shù)) ----------------------- sum(aone應(yīng)用SQL執(zhí)行次數(shù))

我們認(rèn)為，該值越大，影響越大；該值越小，影響可能小。

極端情況就是應(yīng)用里每次執(zhí)行的SQL全是慢SQL，該值為1；應(yīng)用里每次執(zhí)行的SQL全不是慢SQL，該值為0。

但是這個(gè)指標(biāo)帶來(lái)的問(wèn)題是區(qū)分度不佳，尤其是對(duì)SQL QPS很高且大多數(shù)情況下SQL都不是慢查詢的情況，偶發(fā)的慢SQL會(huì)被淹沒(méi)。

另外一個(gè)問(wèn)題，偶發(fā)的慢SQL是真的慢SQL嗎？我們遇到很多被慢查詢?nèi)罩居涗浀腟QL，實(shí)際上可能受到其他慢SQL影響、MySQL磁盤抖動(dòng)、優(yōu)化器選擇等原因使得常規(guī)查詢下表現(xiàn)顯然不是慢SQL的變成了慢SQL。

宏平均

sum(慢SQL 1執(zhí)行次數(shù))    sum(慢SQL n執(zhí)行次數(shù)) -----------------  +  ------------------ sum(SQL 1執(zhí)行次數(shù))      sum(SQL n執(zhí)行次數(shù)) ---------------------------------------                    n

這個(gè)算法建立在被抓到的慢SQL有一定執(zhí)行次數(shù)的基礎(chǔ)上，可以減少假性慢SQL的影響。

當(dāng)某些應(yīng)用QPS很低，即一天執(zhí)行SQL的次數(shù)很少，如果碰到假性SQL就會(huì)引起統(tǒng)計(jì)誤差。

執(zhí)行次數(shù)

sum(aone應(yīng)用慢SQL執(zhí)行次數(shù)) -----------------------            7

統(tǒng)計(jì)最近一周平均每天的慢SQL執(zhí)行次數(shù)，可以消除掉宏平均帶來(lái)的假性SQL問(wèn)題。

慢SQL模板數(shù)量

以上維度均有個(gè)時(shí)間限定范圍，為了追溯慢SQL歷史處理情況，我們還引入了全局慢SQL模板數(shù)量維度。

count(distinct(aone應(yīng)用慢SQL模板) )

2 目標(biāo)

• 核心應(yīng)用：解決掉所有的慢SQL

• 普通應(yīng)用：微平均指標(biāo)下降50%

3 CTO報(bào)表

以CTO-D為單位根據(jù)以上多維度指標(biāo)統(tǒng)計(jì)匯總應(yīng)用的加權(quán)平均，由低到高得出排名，突出頭尾top3，每周播報(bào)。

三 為什么由我來(lái)做

猜測(cè)可能與我的背景有關(guān)，有C/C++背景，曾在上家公司負(fù)責(zé)過(guò)公司層面異地多活架構(gòu)的設(shè)計(jì)和落地，對(duì)于MySQL比較了解一些。

另外可能是利益無(wú)關(guān)，我所在小團(tuán)隊(duì)業(yè)務(wù)剛起步，不存在慢SQL，這樣可以插入到各個(gè)業(yè)務(wù)線去。

四 行動(dòng)支撐

1 集團(tuán)MySQL規(guī)約

索引規(guī)約摘錄部分：

【強(qiáng)制】超過(guò)三個(gè)表禁止join。需要join的字段，數(shù)據(jù)類型保持絕對(duì)一致；多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引。

說(shuō)明：即使雙表join也要注意表索引、SQL性能。

【強(qiáng)制】在varchar字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度。

說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為20的索引，區(qū)分度會(huì)高達(dá)90%以上，可以使用count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來(lái)確定。

【強(qiáng)制】頁(yè)面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來(lái)解決。

說(shuō)明：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那么無(wú)法使用此索引。

【推薦】防止因字段類型不同造成的隱式轉(zhuǎn)換，導(dǎo)致索引失效。

【參考】創(chuàng)建索引時(shí)避免有如下極端誤解：

1） 索引寧濫勿缺

認(rèn)為一個(gè)查詢就需要建一個(gè)索引。

2） 吝嗇索引的創(chuàng)建

認(rèn)為索引會(huì)消耗空間、嚴(yán)重拖慢更新和新增速度。

3） 抵制唯一索引

認(rèn)為唯一索引一律需要在應(yīng)用層通過(guò)“先查后插”方式解決。

2 DB變更標(biāo)準(zhǔn)

DDL需要控制變更速度，注意灰度和并發(fā)控制，變更發(fā)布需要在規(guī)定的變更發(fā)布窗口內(nèi)。

五 分享一些我參與優(yōu)化的例子

1 數(shù)據(jù)分布不均勻

1）分庫(kù)分表不合理

該業(yè)務(wù)數(shù)據(jù)分了8個(gè)庫(kù)，每個(gè)庫(kù)分了16張表，通過(guò)查看表空間可以看到數(shù)據(jù)幾乎都分布在各個(gè)庫(kù)的某2張表中。分庫(kù)分表的策略有問(wèn)題，另外過(guò)高預(yù)估了業(yè)務(wù)增量，這個(gè)持保留意見(jiàn)。

2）索引不合理

單表創(chuàng)建了idx_logistics_corp_id_special_id的聯(lián)合索引，但即便這樣區(qū)分度依然太低，根據(jù)實(shí)驗(yàn)及業(yè)務(wù)反饋(logistics_corp_id,transport_type_id)字段組合區(qū)分度非常高，且業(yè)務(wù)存在transport_type_id的單查場(chǎng)景。

2 索引問(wèn)題

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多個(gè)')     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`;

這個(gè)case對(duì)應(yīng)的表有store_code索引，因此認(rèn)為沒(méi)問(wèn)題，沒(méi)辦法優(yōu)化了。實(shí)則通過(guò)執(zhí)行計(jì)劃，我們發(fā)現(xiàn)MySQL選擇了全表掃描。針對(duì)該case實(shí)踐發(fā)現(xiàn)，當(dāng)范圍查詢的個(gè)數(shù)超過(guò)200個(gè)時(shí)，索引優(yōu)化器將不再使用該字段索引。

最終經(jīng)過(guò)拉取最近一段時(shí)間的相關(guān)查詢SQL，結(jié)合業(yè)務(wù)的數(shù)據(jù)分布，我們發(fā)現(xiàn)采用(is_deleted,quantity)即可解決。

判斷執(zhí)行計(jì)劃采用的索引長(zhǎng)度：key_len的長(zhǎng)度計(jì)算公式(>=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(變長(zhǎng)字段) varchr(10)不允許NULL  =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 2(變長(zhǎng)字段) 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千萬(wàn)行：

索引字段區(qū)分度很高：

同時(shí)期常規(guī)SQL變?yōu)榱寺樵儯?/p>

DB數(shù)據(jù)盤訪問(wèn)情況：

排查共用物理機(jī)其他實(shí)例的情況，發(fā)現(xiàn)有個(gè)庫(kù)在問(wèn)題時(shí)間附近有很多慢sql需要排序，寫臨時(shí)文件剛好寫入了2GB：

多個(gè)MySQL實(shí)例leader節(jié)點(diǎn)混合部署在同一臺(tái)物理機(jī)，雖然通過(guò)docker隔離了CPU、MEM等資源，但目前還沒(méi)有做到buffer io的隔離。

4 無(wú)法解決

通過(guò)匯總分析高頻的查詢并結(jié)合業(yè)務(wù)得出合適的索引往往能夠解決日常遇到的慢查詢，但這并不是萬(wàn)能的。

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

有些場(chǎng)景，比如支持多個(gè)字段組合查詢，又沒(méi)有必填項(xiàng)，如果都要通過(guò)索引來(lái)支持顯然是不合理的。

查詢場(chǎng)景下，將區(qū)分度較高的字段設(shè)定為必填項(xiàng)是個(gè)好習(xí)慣；查詢組合很多的情況下考慮走搜索支持性更好的存儲(chǔ)或者搜索引擎。

六 日?；幚?/h2>

隨著各個(gè)CTO-D線的深入治理，各項(xiàng)指標(biāo)較之前均有非常大的改觀，比如核心應(yīng)用完成慢查詢清零，影響最大的一些慢SQL被得以解決，而我所在的團(tuán)隊(duì)排名也由最初的尾部top3進(jìn)入到頭部top3。
慢SQL治理進(jìn)入日?；?，通過(guò)每周固定推送慢SQL工單、owner接手處理、結(jié)單，基本形成了定期清零的習(xí)慣和氛圍，慢SQL治理專項(xiàng)也被多次點(diǎn)名表?yè)P(yáng)。

七 小結(jié)

這是一篇遲到的總結(jié)，現(xiàn)在回頭看覺(jué)得這里面的策略制定、問(wèn)題分析和解決的過(guò)程還是蠻值得拿出來(lái)和大家分享下。