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

一、全表掃描

1. 案例

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

2. 溯源

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

3. 分析

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

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

4. 解決方案

由此優(yōu)化方案就很明朗了：打開頁面時不直接查詢?nèi)繑?shù)據(jù)，等運營輸入商家id后，將商家id作為參數(shù)進行查詢。XXX_rules表中，商家id這一常用查詢條件設(shè)置為索引，再結(jié)合分頁查詢優(yōu)化，全表掃描慢sql得以解決。

優(yōu)化后的小二后臺頁面如下：

打開頁面時未查詢?nèi)魏螖?shù)據(jù)，查詢條件商家賬戶為必填項。

優(yōu)化后的sql為:

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

執(zhí)行EXPLAIN得到結(jié)果如下：

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

5. 思考

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

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

二、索引混亂

1. 示例

2. 分析

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

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

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

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

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

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

3. 治理

對索引進行整合如下：

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

三、非必要排序

1. 問題描述

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

2. 解決方案

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

四、粗粒度查詢

1. 問題描述

業(yè)務(wù)中有很多定時任務(wù)，掃描某個表中某個產(chǎn)品下所有數(shù)據(jù)，對數(shù)據(jù)進行處理，比如：

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

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

2. 分析

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

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

3. 解決方案

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

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

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

這個方法的前提是對數(shù)據(jù)實效性要求不高，且離線產(chǎn)出的結(jié)果集很小。

五、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上述查詢語句，得到結(jié)果如下：

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

索引失效場景：

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

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

3. 解決方案

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