一、為什麼要做這個事情
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.資料分布不均勻
分庫分表不合理
該業務資料分了8個庫，每個庫分了16張表，通過檢視表空間可以看到資料幾乎都分布在各個庫的某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治理專項也被多次點名表揚。
七、小結
這是一篇遲到的總結，現在回頭看覺得這裡面的政策制定、問題分析和解決的過程還是蠻值得拿出來和大家分享下。