菜鳥供應鏈金融慢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四個欄位，表的索引對這四個欄位做各種排列組合，存在如下問題：
1）rule_name離散度不高，放在索引首位不合適；
2）前三個索引重合度很高；
顯然是對索引的命中規則不夠了解，XXX_rules表很多業務有定時任務對其寫入洗掉，索引多、混亂，對性能有很大的影響，
高性能的索引有哪些，再來回顧下：
①獨立的列：索引列不能是運算式的一部分；
②選擇區分度高的列作為索引；
③選擇合適的索引列順序：將選擇性高的索引列放在最前列；
④覆寫索引：查詢的列均在索引中，不需要回查聚簇索引；
⑤使用索引掃描來做排序;
⑥在遵守最左前綴的原則下，盡量擴展索引，而不是創建索引，
但凡記得第③和⑥規則，也不至于把索引建成這樣，
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治理已經有一段時間，自己負責的應用持續很長時間沒有慢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四個欄位，表的索引對這四個欄位做各種排列組合，存在如下問題：
1）rule_name離散度不高，放在索引首位不合適；
2）前三個索引重合度很高；
顯然是對索引的命中規則不夠了解，XXX_rules表很多業務有定時任務對其寫入洗掉，索引多、混亂，對性能有很大的影響，
高性能的索引有哪些，再來回顧下：
①獨立的列：索引列不能是運算式的一部分；
②選擇區分度高的列作為索引；
③選擇合適的索引列順序：將選擇性高的索引列放在最前列；
④覆寫索引：查詢的列均在索引中，不需要回查聚簇索引；
⑤使用索引掃描來做排序;
⑥在遵守最左前綴的原則下，盡量擴展索引，而不是創建索引，
但凡記得第③和⑥規則，也不至于把索引建成這樣，
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，
作者丨如期
本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/5_Classic_Cases_of_Alibaba_Slow_SQL_Governance.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/445386.html
標籤：MySQL
上一篇：MySQL優化之索引決議
下一篇：MySQL視圖