在降本增笑的大環(huán)境下，運(yùn)維被砍，開發(fā)也要承擔(dān)一部分 DBA 的任務(wù)。
例如，今天這個(gè)場景，某臺(tái) MySQL 5.7，磁盤 95%，繼續(xù)解決。
開發(fā)想當(dāng)然的做法是：刪掉一些表的數(shù)據(jù)，肯定能降。
然而，未經(jīng)指標(biāo)調(diào)研和測試的情況下，大概率會(huì)讓磁盤繼續(xù)膨脹，數(shù)據(jù)沒刪完，庫先炸了。
為什么刪完數(shù)據(jù)磁盤沒變化
這是 InnoDB 的 B+ 樹，如果刪除 R4，InnoDB 引擎只會(huì)把 R4 標(biāo)為刪除。如果之后要再插入一個(gè) ID 在 300和 600 之間的記錄時(shí)，新記錄可能會(huì)復(fù)用這個(gè)位置。但磁盤文件不會(huì)縮小。
怎么看刪完數(shù)據(jù)的表狀態(tài)
觀察 SHOW TABLE status 的輸出。
SHOW TABLE STATUS LIKE 'xxx'\G
*************************** 1. row ***************************
Name: xxx
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 565023
Avg_row_length: 1037
Data_length: 585973760
Max_data_length: 0
Index_length: 158515200
Data_free: 6291456
...
當(dāng)我們刪除大批量數(shù)據(jù)時(shí)，Rows 行數(shù)會(huì)減少，Data Free (Bytes) 會(huì)相應(yīng)升高。
如果 Data Free 已經(jīng)在相當(dāng)高的水平，不僅占空間，還會(huì)使得未索引字段查詢掃描更多數(shù)據(jù)，進(jìn)而查詢變慢。
這時(shí)，可以考慮執(zhí)行 OPTIMIZE TABLE 重建表或者 dump 數(shù)據(jù)到本地 reload 回 MySQL。
沒刪數(shù)據(jù)，為什么 Data Free 不等于 0
重建新表，插入大量新數(shù)據(jù)，你可能會(huì)發(fā)現(xiàn)，沒刪數(shù)據(jù)，怎么 Rows、Data_free 一起漲？
還是 InnoDB 的存儲(chǔ)結(jié)構(gòu)問題。
當(dāng)數(shù)據(jù)頁滿了，再插入一條新的數(shù)據(jù)，引擎需再申請一個(gè)新頁保存數(shù)據(jù)。頁分裂完成后，原有的頁末尾就留下了空洞。
同樣地，數(shù)據(jù)的索引寫入也會(huì)存在空洞。
不同類別的索引，空洞程度也不一樣，自增主鍵 ID 索引和其他一些業(yè)務(wù)字段做索引，順序?qū)懭氲那罢咭话銜?huì)更緊湊。
為什么 OPTIMIZE TABLE 能讓磁盤炸了
前面提到 OPTIMIZE TABLE 可以實(shí)現(xiàn)重建表的操作，簡化的流程是：
- 創(chuàng)建一個(gè)臨時(shí)表
- 復(fù)制數(shù)據(jù)到臨時(shí)表
- 交換新舊表，DROP 舊表
如果磁盤占用 95%，單表占用超過剩余比例，那么第二步可能就失敗了。
再次強(qiáng)調(diào)，這里簡化了新版本 OnlineDDL 做的鎖退化（占耗時(shí)大頭的復(fù)制數(shù)據(jù)階段沒有 DDL 鎖了），但無論新舊版本，都需要掃描舊表，寫入臨時(shí)表，也就是說中間過程磁盤、IO 必然是上升的。
如果沒有評估機(jī)器狀態(tài)貿(mào)然執(zhí)行，可能機(jī)器沒救成，雪崩來得更快。
為什么用 INPLACE 磁盤還是炸了
因?yàn)?INPLACE 并不是字面意義上的原地替換
舊的 COPY 很好理解，就是中間建了一張同樣數(shù)據(jù)的新表（為了維持一致，大表可能鎖數(shù)小時(shí)以上）。
為了減少復(fù)制數(shù)據(jù)的鎖表時(shí)間，才有了新的 OnlineDDL，也就是 INPLACE 算法。
原來的 COPY 是在 server 層創(chuàng)建了一個(gè)臨時(shí)表，而新的是在 InnoDB 的引擎層創(chuàng)建臨時(shí)文件，對于 server 層而言，看不到臨時(shí)表，的確是 inplace。
從下方的圖可以看到，MySQL 的 OnlineDDL 還多維護(hù)了一個(gè) row log 來記錄復(fù)制階段的增刪改，去掉了復(fù)制階段的鎖。
因此，OPTIMIZE TABLE 的 INPLACE 算法僅限于大幅降低鎖的時(shí)間，減少一部分磁盤 IO，對于磁盤占用而言，兩者的差異不大
還能怎么救磁盤滿的庫
有鈔能力當(dāng)然是擴(kuò)容，沒錢呢？
分析表的業(yè)務(wù)場景
- 中間表占用轉(zhuǎn)移到本地。對于非面向客戶的日志表，可以先 dump 本地，再 TRUNCATE 舊表，重新導(dǎo)入到新表，交換新舊表實(shí)現(xiàn) OPTIMIZE TABLE。
-
舍棄舊數(shù)據(jù)。對于緩存性質(zhì)或有明確過期時(shí)間的表，寫入一部分近期數(shù)據(jù)到新表，交換新舊表，DROP 舊表。
如果你用的是分區(qū)表，舊數(shù)據(jù)剛好落在特定分區(qū)，那么直接 DROP PARTITION 是比 OPTIMIZE TABLE 更合適的操作。
刪除不必要的索引
這個(gè)沒有銀彈，只能從查詢?nèi)罩窘y(tǒng)計(jì)
使用壓縮的數(shù)據(jù)格式
創(chuàng)建表列格式為 ROW_FORMAT=COMPRESSED
降低日志占用
redo log 體積調(diào)整為適當(dāng)?shù)拇笮?/p>
除了歸檔數(shù)據(jù)外的其他方式，其他方式對于持續(xù)增長的表大小是無能為力的。
如果確定數(shù)據(jù)的生命周期在調(diào)整后不會(huì)增長到磁盤滿，那么做上述改動(dòng)可能值得，如果答案是否，用數(shù)據(jù)說服領(lǐng)導(dǎo)擴(kuò)容吧！
總結(jié)
清理數(shù)據(jù)的思路是：
- 了解機(jī)器當(dāng)前的資源狀況。
執(zhí)行OPTIMIZE TABLE需要二次評估磁盤空間，以及確定業(yè)務(wù)高低峰時(shí)期才可以執(zhí)行 - 分析表業(yè)務(wù)場景，決定數(shù)據(jù)生命周期
- 平衡容災(zāi)要求，降低日志大小
