MySQL 上億大表優化實踐

語言: CN / TW / HK

:point_down:推薦大家關注一個公眾號:point_down:

點選上方 " 程式設計技術圈 "關注,  星標或置頂一起成長

後臺回覆“ 大禮包 ”有驚喜禮包!

日英文

Man has to be crazy for once, whether it is for a person,a love story, a journey or a dream.

人的一生要瘋狂一次,無論是為一個人,一段情,一段旅途,或一個夢想。

每日掏心話

人生就像蒲公英,看似自由,卻往往身不由己。生活沒有如果,只有結果,自己盡力了,努力了,就好。

責編:樂樂 | 來 自:jia-xin

連結:www.cnblogs.com/YangJiaXin/p/10828244.html

程式設計技術圈(ID:study_tech) 第 171  期推文

往日回顧: 政府機構 5000 萬臺電腦將替換為國產 Linux

大家好,我是小樂

XX 例項(一主一從)xxx 告警中每天凌晨在報 SLA 報警,該報警的意思是存在一定的主從延遲。(若在此時發生主從切換,需要長時間才可以完成切換,要追延遲來保證主從資料的一致性)

XX 例項的慢查詢數量最多(執行時間超過 1s 的 SQL 會被記錄),XX 應用那方每天晚上在做刪除一個月前資料的任務。

使用 pt-query-digest 工具分析最近一週的 mysql-slow.log:

結果第一部分:

最近一個星期內,總共記錄的慢查詢執行花費時間為 25403s,最大的慢 SQL 執行時間為 266s,平均每個慢 SQL 執行時間 5s,平均掃描的行數為 1766 萬。

結果第二部分:

select arrival_record 操作記錄的慢查詢數量最多有 4 萬多次,平均響應時間為 4s,delete arrival_record 記錄了 6 次,平均響應時間 258s。

 

select xxx_record 語句

select arrival_record 慢查詢語句都類似於如下所示,where 語句中的引數欄位是一樣的,傳入的引數值不一樣:

select arrival_record 語句在 MySQL 中最多掃描的行數為 5600 萬、平均掃描的行數為 172 萬,推斷由於掃描的行數多導致的執行時間長。

檢視執行計劃:

用到了索引 IXFK_arrival_record,但預計掃描的行數很多有 3000 多萬行:

①該表總記錄數約 1 億多條,表上只有一個複合索引,product_id 欄位基數很小,選擇性不好。

②傳入的過濾條件:

牛逼啊!接私活必備的 N 個開源專案!趕快收藏

沒有 station_nu 欄位,使用不到複合索引 IXFK_arrival_record 的 product_id,station_no,sequence,receive_time 這幾個欄位。

③根據最左字首原則,select arrival_record 只用到了複合索引 IXFK_arrival_record 的第一個欄位 product_id,而該欄位選擇性很差,導致掃描的行數很多,執行時間長。

④receive_time 欄位的基數大,選擇性好,可對該欄位單獨建立索引,select arrival_record sql 就會使用到該索引。

現在已經知道了在慢查詢中記錄的 select arrival_record where 語句傳入的引數欄位有 product_id,receive_time,receive_spend_ms,還想知道對該表的訪問有沒有通過其他欄位來過濾了

神器 tcpdump 出場的時候到了,使用 tcpdump 抓包一段時間對該表的 select 語句:

獲取 select 語句中 from 後面的 where 條件語句:

select 該表 where 條件中有 product_id,station_no,sequence 欄位,可以使用到複合索引 IXFK_arrival_record 的前三個欄位。

綜上所示,優化方法為:

  • 刪除複合索引 IXFK_arrival_record

  • 建立複合索引 idx_sequence_station_no_product_id

  • 建立單獨索引 indx_receive_time

delete xxx_record 語句

該 delete 操作平均掃描行數為 1.1 億行,平均執行時間是 262s。

delete 語句如下所示,每次記錄的慢查詢傳入的引數值不一樣:

執行計劃:

該 delete 語句沒有使用索引(沒有合適的索引可用),走的全表掃描,導致執行時間長。

優化方法也是: 建立單獨索引 indx_receive_time(receive_time)。

拷貝 arrival_record 表到測試例項上進行刪除重新索引操作。

XX 例項 arrival_record 表資訊:

磁碟佔用空間 48G,MySQL 中該表大小為 31G,存在 17G 左右的碎片,大多由於刪除操作造成的。(記錄被刪除了,空間沒有回收)

備份還原該表到新的例項中,刪除原來的複合索引,重新新增索引進行測試。

mydumper 並行壓縮備份:

並行壓縮備份所花時間(52s)和佔用空間(1.2G,實際該表佔用磁碟空間為 48G,mydumper 並行壓縮備份壓縮比相當高):

拷貝 dump 資料到測試節點:

多執行緒匯入資料:

邏輯匯入該表後磁碟佔用空間:

分別使用 online DDL 和 pt-osc 工具來做刪除重建索引操作。

先刪除外來鍵,不刪除外來鍵,無法刪除複合索引,外來鍵列屬於複合索引中第一列:

online DDL 花費時間為 34 分鐘,pt-osc 花費時間為 57 分鐘,使用 onlne DDL 時間約為 pt-osc 工具時間的一半。

做 DDL 參考:

由於是一主一從例項,應用是連線的 vip,刪除重建索引採用 online DDL 來做。

停止主從複製後,先在從例項上做(不記錄 binlog),主從切換,再在新切換的從例項上做(不記錄 binlog):

執行時間:

刪除重建索引花費時間為 28 分鐘,新增外來鍵約束時間為 48 分鐘。

再次檢視 delete 和 select 語句的執行計劃:

都使用到了 idx_receive_time 索引,掃描的行數大大降低。

索引優化後

delete 還是花費了 77s 時間:

delete 語句通過 receive_time 的索引刪除 300 多萬的記錄花費 77s 時間。

delete 大表優化為小批量刪除

應用端已優化成每次刪除 10 分鐘的資料(每次執行時間 1s 左右),xxx 中沒在出現 SLA(主從延遲告警):

另一個方法是通過主鍵的順序每次刪除 20000 條記錄:

表資料量太大時,除了關注訪問該表的響應時間外,還要關注對該表的維護成本(如做 DDL 表更時間太長,delete 歷史資料)。

對大表進行 DDL 操作時,要考慮表的實際情況(如對該表的並發表,是否有外來鍵)來選擇合適的 DDL 變更方式。

對大資料量表進行 delete,用小批量刪除的方式,減少對主例項的壓力和主從延遲。

歡迎有需要的同學試試,如果本文對您有幫助,也請幫忙點個  贊 + 在看  啦!:heart:

你還有什麼想要補充的嗎?

PS:歡迎在留言區留下你的觀點,一起討論提高。如果今天的文章讓你有新的啟發,歡迎轉發分享給更多人。

版權申明:內容來源網路,版權歸原創者所有。除非無法確認,我們都會標明作者及出處,如有侵權煩請告知,我們會立即刪除並表示歉意。謝謝!

歡迎加入後端架構師 交流群 ,在後臺回覆“ 學習 ”即可。