【MySQL】資料庫系統中的“黑天鵝”
一 前言
納西姆.尼古拉斯.塔勒布的經典著作《黑天鵝》中對“黑天鵝現象”的定義是
二 分析
穩定性是一項衡量基礎系統是否永續服務的絕對指標,作為資深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 利用索引的有序性,更確切的是利用 where條件的索引有序性,儘可能使用到組合索引的created_time有序性代替使用order by id查詢,MySQL在使用索引的時候 只能利用一個有效索引,order by id 可能會導致最佳化器選擇主鍵而非 cc,dd,created_time這樣的組合索引。
2 通常我們推薦使用 延遲關聯 的方法來最佳化大分頁查詢---利用覆蓋索引獲取複合條件的記錄的主鍵id,然後驅動表根據主鍵來訪問想要的資料,這樣的訪問速度要比limit 順序掃描全索引然後回表的速度要快很多。
3 應用層最佳化商家本質上是想要獲取全量資料,之前的方式是每天或者每週固定時間點定期獲取某個時間段內的全量資料,換個思路我們的業務提供push推送任務,專門主動推送商家的增量資料,這樣可以避免大批次的拉取全量資料,減少db的不穩定性也同時節約公司的頻寬成本。
案例二 join 查詢最佳化
大致的業務邏輯根據商品交易資訊獲取商家售賣銷量,相關sql 以及表結構資訊
在MySQL中,目前而言只有一種join演算法 也即是nested loop join:是透過驅動表(from後的第一個表)的結果集作為迴圈的基礎資料,然後將結果集中的資料作為過濾條件一條條地到下一個表中查詢資料,最後合併結果。本案例中可以理解為 以so kid=16553711 的結果資料 去匹配 oi 表中gid=yyyy 符合記錄的資料,然後做count操作。通常我們對於join查詢的最佳化原則
優化方法
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 左右,解決了絕大多數的潛在的系統風險。
![](http://img.blog.itpub.net/blog/attachment/201608/5/22664653_1470406042p0W0.jpg?x-oss-process=style/bb)
誠然透過最佳化慢查詢,使用快取 ,並無法絕對避免“黑天鵝”式故障發生,系統的穩定性是應用層的健壯性,底層基礎服務 網路,機器硬體,資料庫層面等各個環節息息相關的,我們要做的就是透過提高資料庫系統和業務系統的 “反脆弱性”,提高抗擊打能力,為使用者提供可持續的穩定的服務。
四 推薦文章
[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 的增加而增大。
- 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 順序掃描全索引然後回表的速度要快很多。
- 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;
案例二 join 查詢最佳化
大致的業務邏輯根據商品交易資訊獲取商家售賣銷量,相關sql 以及表結構資訊
- select count(o.ono) as num from so o, oi i where o.ono = i.ono and `o`.`kid` = 'xxxx' and `i`.`gid` = 'yyyy';
- oi 表的索引
-
KEY `idx_sid` (`idx_sid`) USING BTREE,
-
KEY `idx_ono` (`idx_ono`) USING BTREE,
-
KEY `idx_created` (`created`)
-
so 表的索引
- key idx_kid(kid,cc,created_time)
-
1 減少nested loop的迴圈次數,使用小結果集驅動大結果集。
-
2 優先最佳化Nested Loop的內層迴圈,內迴圈中的where條件一定要使用最優的索引。
-
3 保證join語句中被驅動表的join條件欄位已經被索引;
- 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 左右,解決了絕大多數的潛在的系統風險。
![](http://img.blog.itpub.net/blog/attachment/201608/5/22664653_1470406042p0W0.jpg?x-oss-process=style/bb)
誠然透過最佳化慢查詢,使用快取 ,並無法絕對避免“黑天鵝”式故障發生,系統的穩定性是應用層的健壯性,底層基礎服務 網路,機器硬體,資料庫層面等各個環節息息相關的,我們要做的就是透過提高資料庫系統和業務系統的 “反脆弱性”,提高抗擊打能力,為使用者提供可持續的穩定的服務。
四 推薦文章
[1] 《》
[2] 《》
[3] 《》
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30496894/viewspace-2124577/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL資料庫審計系統MySql資料庫
- 鵝廠資料庫的進階之路資料庫
- 【MySQL資料庫】認識資料庫+環境搭建--------Windows系統MySql資料庫Windows
- windows系統下 重置mysql資料庫的密碼WindowsMySql資料庫密碼
- 【資料庫資料恢復】linux系統下MYSQL資料庫資料恢復案例資料庫資料恢復LinuxMySql
- 資料庫應用系統中的資料庫完整性(上)KP資料庫
- MySQL練習——教學系統資料庫設計MySql資料庫
- Linux系統徹底解除安裝MySQL資料庫LinuxMySql資料庫
- 將MYSQL資料顯示在QT的tablewidget中/將QT中的資料儲存到MYSQL資料庫中MySqlQT資料庫
- 【資料庫資料恢復】EXT3檔案系統下MYSQL資料庫恢復案例資料庫資料恢復MySql
- 資料庫物件資訊記錄表|全方位認識 mysql 系統庫資料庫物件MySql
- 瞭解直播帶貨系統使用的MySQL資料庫基礎MySql資料庫
- 資料庫與資料庫管理系統概述資料庫
- 陪玩系統原始碼中mysql資料庫備份還原的實現程式碼原始碼MySql資料庫
- 資料庫系統概述資料庫
- 明天和意外誰先來臨? 佈局“零信任”,不懼“黑天鵝”!
- 【資料庫系統】資料庫系統概論====第十三章 資料庫技術發展資料庫
- Linux環境下的Mysql資料庫入門-基於Centos系統LinuxMySql資料庫CentOS
- Centos系統關閉Mysql資料庫查詢操作日誌的方法CentOSMySql資料庫
- 資料庫系統概述之國產資料庫資料庫
- MySQL資料庫之mysql5.7基礎 檢視一個資料庫中的所有表MySql資料庫
- Linux系統MySQL資料庫效能最佳化詳細教程。LinuxMySql資料庫
- Centos7系統如何安裝MySQL5.7資料庫?CentOSMySql資料庫
- MySQL 基礎知識梳理學習(一)—-系統資料庫MySql資料庫
- 資料庫:系統設計的核心資料庫
- 如何將外部資料庫 匯入到系統的SQL中資料庫SQL
- mysql資料庫的索引MySql資料庫索引
- [資料庫]【MySQL】MySQL資料庫規範總結資料庫MySql
- MySQL資料庫中的分組函式ROLLUPMySql資料庫函式
- 知物由學 | 在熱詞中看輿論,擺脫“黑天鵝”效應
- 資料庫系統概念 第4章 中級SQL資料庫SQL
- 資料庫系統概述之資料庫最佳化資料庫
- 直播帶貨系統原始碼,實現MYSQL資料庫的主從同步原始碼MySql資料庫主從同步
- 直播賣貨系統,如何實現mysql資料庫的讀寫分離MySql資料庫
- Zabbix系統MySQL資料庫分割槽表的設定--精簡說明MySql資料庫
- MYSQL資料庫MySql資料庫
- 資料庫-MySQL資料庫MySql
- 資料庫 MySQL資料庫MySql
- 資料庫(MySQL)資料庫MySql