慢SQL治理的經典案例分享

作者：如期 2022-02-28 08:09:14

菜鳥供應鏈金融慢sql治理已經有一段時間，自己負責的應用持續很長時間沒有慢sql告警，現階段在推進組內其他成員治理應用慢sql。這里把治理過程中的一些實踐拿出來分享下。

一、全表掃描

1. 案例

SELECT count(*) AS tmp_count FROM ( 
SELECT * FROM `XXX_rules` WHERE 1 = 1 ORDER BY gmt_create DESC ) a

2. 溯源

在分頁查詢治理的文章里已經介紹過我們系統舊的分頁查詢邏輯，上面的查詢sql明顯就是分頁查詢獲取總記錄數，通過XXX_rules表的分頁查詢接口溯源，找到發起調用的頁面是我們小二后臺的一個操作商家準入的頁面，頁面打開后直接調用分頁查詢接口，除了分頁參數，不傳入其他任何查詢參數，導致掃描全表。

3. 分析

靈魂拷問：為什么要掃描全表?全表數據展示到頁面，花里胡哨的數據有用嗎?

調研：和經常使用這個頁面的運營聊后了解到，打開頁面查詢出的全表數據對運營是沒有用的，他們根本不看這些數據。運營的操作習慣是拿到商家id，在頁面查詢框中輸入商家id，查到商家數據后進行操作。

4. 解決方案

由此優化方案就很明朗了：打開頁面時不直接查詢全量數據，等運營輸入商家id后，將商家id作為參數進行查詢。XXX_rules表中，商家id這一常用查詢條件設置為索引，再結合分頁查詢優化，全表掃描慢sql得以解決。

優化后的小二后臺頁面如下：

打開頁面時未查詢任何數據，查詢條件商家賬戶為必填項。

優化后的sql為:

SELECT count(*) AS tmp_count FROM ( 
   SELECT * FROM `xxx_rules` WHERE 1 = 1 AND `rule_value` = '2928597xxx' ) a

執行EXPLAIN得到結果如下：

可以看到命中了索引，掃描行數為3，查詢速度明顯提高。

5. 思考

掃描全表治理簡單來說就是加入查詢條件，命中索引，去除全表掃描查詢，雖然有些粗暴，但并不是沒有道理。實際業務場景中，很少有要掃描全表獲取全部數據的情況，限制調用上游必須傳入查詢條件，且該查詢條件能命中索引，能很大程度上避免慢sql。

另外，再引申下，XXX_rules初始的用意是準入表，記錄金融貨主維度的準入情況，最多也就幾千條數據，但是很多同事將這張表理解為規則表，寫入很多業務相關規則，導致這個表膨脹到一百多萬條數據，表不clean了。這就涉及到數據表的設計使用，明確表的使用規范，不亂寫入數據，能給后期維護帶來很大的便利。

二、索引混亂

1. 示例

2. 分析

除了時間、操作人字段，XXX_rules表就rule_name、rule_value、status、product_code四個字段，表的索引對這四個字段做各種排列組合。存在如下問題：

rule_name離散度不高，放在索引首位不合適;
前三個索引重合度很高;

顯然是對索引的命中規則不夠了解。XXX_rules表很多業務有定時任務對其寫入刪除，索引多、混亂，對性能有很大的影響。

高性能的索引有哪些，再來回顧下：

獨立的列：索引列不能是表達式的一部分;
選擇區分度高的列作為索引;
選擇合適的索引列順序：將選擇性高的索引列放在最前列;
覆蓋索引：查詢的列均在索引中，不需要回查聚簇索引;
使用索引掃描來做排序;
在遵守最左前綴的原則下，盡量擴展索引，而不是創建索引。

但凡記得第3和6規則，也不至于把索引建成這樣。

3. 治理

對索引進行整合如下：

系統中有很多任務拉取整個產品下的準入記錄，然后進行處理，所以將區分度較高的product_code放在索引首位，然后添加rule_name、status字段到索引里，進一步過濾數據，減少掃描行數，避免慢sql。針對常用的rule_value查詢條件，可以命中UK，因此不用單獨建立索引。

三、非必要排序

1. 問題描述

很多業務邏輯中，需要拉取滿足某個條件的記錄列表，查詢的sql語句帶有order by，記錄比較多的情況，排序代價往往很大，但是查詢出來的記錄是否有序對業務邏輯沒有影響，比如分頁治理里討論的count語句，只需要統計條數，order by對條數沒有影響，再比如查出記錄列表后，不依賴記錄的順序遍歷列表處理數據，這時候order by多此一舉。

2. 解決方案

查詢sql無limit語句，且業務處理邏輯不依賴于order by后列表記錄的順序，則去除查詢sql中的order by語句。

四、粗粒度查詢

1. 問題描述

業務中有很多定時任務，掃描某個表中某個產品下所有數據，對數據進行處理，比如：

SELECT * FROM XXX_rules
    WHERE rule_name = 'apf_distributors'
      AND status = '00'
      AND product_code = 'ADVANCE'

三個查詢條件都是區分度不高的列，查出的數據有27W條，加索引意義也不大。

2. 分析

實際業務量沒那么大，頂多幾千條數據，表里的數據是從上游同步過來的，最好的辦法是讓上游精簡數據，但是由于業務太久遠，找上游的人維護難度太大，因此只能想其他的辦法。

這個定時任務目的是拉出XXX_rules表的某些產品下的數據，和另一張表數據對比，更新有差異的數據。每天凌晨處理，對時效性沒有很高的要求，因此，能不能轉移任務處理的地方，不在本應用機器上實時處理那么多條數據?

3. 解決方案

數據是離線任務odps同步過來的，首先想到的就是dataWork數據處理平臺。

建立數據對比任務，將定時任務做的數據對比邏輯放到dataWork上用sql實現，每天差異數據最多幾百條，且結果集含有區分度很高的列，將差異數據寫入odps表，再將數據回流到idb。

新建定時任務，通過回流回來的差異數據中區分度高的列作為查詢條件查詢XXX_rules，更新XXX_rules，解決了慢sql問題。

這個方法的前提是對數據實效性要求不高，且離線產出的結果集很小。

五、OR導致索引失效

1. 案例

SELECT count(*)
FROM XXX_level_report
WHERE 1 = 1
  AND EXISTS (
    SELECT 1
    FROM XXX_white_list t
    WHERE (t.biz_id = customer_id
        OR customer_id LIKE CONCAT(t.biz_id, '@%'))
      AND t.status = 1
      AND (t.start_time <= CURRENT_TIME
        OR t.start_time IS NULL)
      AND (t.end_time >= CURRENT_TIME
        OR t.end_time IS NULL)
      AND t.biz_type = 'GOODS_CONTROL_BLACKLIST'
  )

2. 分析

explain上述查詢語句，得到結果如下：

XXX_white_list表有將biz_id作為索引，這里查詢XXX_white_list表有傳入biz_id作為查詢條件，為啥explain結果里type為ALL，即掃描全表?索引失效了?索引失效有哪些情況?

索引失效場景：

OR查詢左右有未命中索引的;
復合索引不滿足最左匹配原則;
Like以%開頭;
需要類型轉換;
where中索引列有運算;
where中索引列使用了函數;
如果mysql覺得全表掃描更快時(數據少時)

上述查詢語句第8行，customer_id為XXX_level_report表字段，未命中XXX_white_list表索引，導致索引失效。

3. 解決方案

這個語句用condition、枚舉、join花里胡哨的代碼拼接起來的，改起來好麻煩，而且看起來“OR customer_id LIKE CONCAT(t.biz_id, '@%')”這句不能直接刪掉。最后重構了該部分的查詢語句，去除or查詢，解決了慢sql。

責任編輯：趙寧寧來源：阿里技術

sql 分頁查詢