【MySQL】資料庫系統中的“黑天鵝”

bitifi發表於2016-09-06
MySql資料庫
一 前言
 納西姆.尼古拉斯.塔勒布的經典著作《黑天鵝》中對“黑天鵝現象”的定義是
  1. － 不可預測，人們事前往往低估其發生的可能性
  2. － 造成極大影響
  3. － 事後回頭再看，又覺得此事發生的有理
二 分析
  穩定性是一項衡量基礎系統是否永續服務的絕對指標，作為資深DBA從業人員，相信大多數公司運維團隊都會制定穩定性的SLA指標達到N個9，為使用者提供Full-Time 服務。然而前一段時間各種"黑天鵝”式的因素導致一系列的系統故障，嚴重影響了C端B端的使用者的使用體驗。故障是資料庫系統或者說業務系統的“脆弱性”表現。什麼是導致業務故障的“黑天鵝”呢？例舉最近遇到的和資料庫相關的場景：
a 程式異常，比如異常傳參導致本應該獲取1行資料的結果去呼叫14w行，高壓力下慢查詢將資料庫會話佔滿，引發”雪崩效應“。
b 正常分頁呼叫，但是遇到大分頁查詢高頻訪問db，同樣會導致慢查詢引發“雪崩效應”。
c 第三方業務開發不瞭解api的使用方法 ，選擇全量拉取而非增量拉取業務資料，導致大量慢查詢。
上述三個例子的共性基本都含有慢查詢，高頻訪問。找到導致問題發生的資料庫層面的原因，剩下的就是發揮產品/開發DBA的特長了，獲取到慢查詢，然後各個擊破之。本文舉例幾個具有代表性的sql
案例一 大分頁查詢優化
    商家會使用第三方軟體拉取訂單資料進行對賬,使用limit N,M  分頁查詢每次拉取50 或者100頁，小批量資料時比如N小於 10000時效能表現正常，但是遇到大的商家比如羅輯思維 ，糕媽優選等大商家，拉取資料的時間會隨著N 的增加而增大。
  1. select * from so where 1 and `bb` = 'xxxxx' and `cc` in ('5') and `dd` in ('0','1','2','3') order by id desc limit 70000,100;
優化方法
1 利用索引的有序性，更確切的是利用 where條件的索引有序性，儘可能使用到組合索引的created_time有序性代替使用order by id查詢,MySQL在使用索引的時候 只能利用一個有效索引，order by id 可能會導致優化器選擇主鍵而非 cc,dd,created_time這樣的組合索引。
2 通常我們推薦使用 延遲關聯 的方法來優化大分頁查詢---利用覆蓋索引獲取複合條件的記錄的主鍵id，然後驅動表根據主鍵來訪問想要的資料，這樣的訪問速度要比limit 順序掃描全索引然後回表的速度要快很多。
  1. select a.* from so a,(select id from so where 1 and `bb` = 'xxxxx' and `cc` in ('5') and `dd` in ('0','1','2','3') order by created_time desc limit 70000,101 ) b where a.id=b.id；
3 應用層優化商家本質上是想要獲取全量資料，之前的方式是每天或者每週固定時間點定期獲取某個時間段內的全量資料，換個思路我們的業務提供push推送任務，專門主動推送商家的增量資料，這樣可以避免大批量的拉取全量資料，減少db的不穩定性也同時節約公司的頻寬成本。
案例二 join 查詢優化
大致的業務邏輯根據商品交易資訊獲取商家售賣銷量,相關sql 以及表結構資訊 
  1. select count(o.ono) as num from so o, oi i where o.ono = i.ono and `o`.`kid` = 'xxxx' and `i`.`gid` = 'yyyy';
  1. oi 表的索引
  2.   KEY `idx_sid` (`idx_sid`) USING BTREE,
  3.   KEY `idx_ono` (`idx_ono`) USING BTREE,
  4.   KEY `idx_created` (`created`)
  5. so 表的索引
  6.   key idx_kid(kid,cc,created_time)
  在MySQL中，目前而言只有一種join演算法 也即是nested loop join:是通過驅動表（from後的第一個表）的結果集作為迴圈的基礎資料，然後將結果集中的資料作為過濾條件一條條地到下一個表中查詢資料，最後合併結果。本案例中可以理解為 以so kid=16553711 的結果資料 去匹配 oi 表中gid=yyyy 符合記錄的資料，然後做count操作。通常我們對於join查詢的優化原則
  1. 1 減少nested loop的迴圈次數,使用小結果集驅動大結果集。
  2. 2 優先優化Nested　Loop的內層迴圈,內迴圈中的where條件一定要使用最優的索引。
  3. 3 保證join語句中被驅動表的join條件欄位已經被索引；
  4. 4 如果無法保證被 驅動表的Join條件欄位被索引且記憶體充足的情況下，可以通過調join_buffer_size來設定join buffer的大小 。
優化方法
  1 根據優化原則我們將 oi表的idx_ono 索引調整為 idx_gid_ono(gid,ono),使用覆蓋索引解決內迴圈回表的IO消耗。可能會有人會諮詢為什麼不調整表的順序，其實第一個想到優化的就是調整順序，但是在現有索引條件下調整驅動表的順序並沒有提高查詢效率。
  2 其實作為一個服務電商業務線的老司機，我認為涉及C端應用呼叫應該避免或者說禁止使用join查詢，業務增長帶來訪問量透傳給DB的壓力，很可能將上面的優化結果輕鬆覆蓋。最優化的方式儘可能的使用kv查詢，單表查詢。好在我們公司給力的開發同學王野已經將該優化業務遷移到es中，直接通過es獲取結果。
案例三 併發count(*) 優化
     因為業務邏輯處理不力，導致資料庫併發count 程式數飆高到200左右，嚴重影響到其他業務的正常請求。其實對於count操作的優化相對比較有限 
     1 確保where條件一定利用到最優索引。
     2 業務層面避免併發count操作，可以使用快取來規避直接訪問db。
     關於count的優化文章可以參考 拙作 《效能優化之 count(*) VS count(col) 》 
三 小結
    最近一個多月一直緊跟公司的慢查詢這塊做集中優化，到目前為止效果相當不錯，基本將慢查詢減少了90%左右。從slow log檔案大小來看，此次優化將檔案大小從1M 減少到4k 左右,解決了絕大多數的潛在的系統風險。 

誠然通過優化慢查詢，使用快取 ，並無法絕對避免“黑天鵝”式故障發生，系統的穩定性是應用層的健壯性,底層基礎服務 網路，機器硬體，資料庫層面等各個環節息息相關的，我們要做的就是通過提高資料庫系統和業務系統的 “反脆弱性”，提高抗擊打能力，為使用者提供可持續的穩定的服務。
四 推薦文章
[1] 《黑天鵝:如何應對不可知的未來》
[2] 《反脆弱:從不確定性中獲益》
[3] 《關於高可用的系統》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2124098/，如需轉載，請註明出處，否則將追究法律責任。

相關文章