喝著枸杞水，大白差點把MySQL磁盤干爆
本文轉載自微信公眾號「后端技術指南針」，作者+++++ 。轉載本文請聯系后端技術指南針公眾號。
1. MySQL磁盤報警了
年前的一周，大白早早來到公司，像往常一樣泡上一杯枸杞水，然后看了下數據庫的磁盤。
嚯!super庫的bighero表磁盤占用率竟然85%了，馬上就到報警設定的閾值。
喝了一口養生枸杞水，大白決定盤它，因為這磁盤不能在過年的時候爆了，否則這事就大了。
分析了下bighero表中的數據，發現有幾百萬數據目前已經不用了，可以直接刪掉，說干就干，一頓操作猛如虎，半個多小時，刪除腳本寫完自測OK了。
考慮到白天算是高峰期，于是決定晚上回家再執行腳本，大白怕忘了在手機上訂了個鬧鐘提醒。
經過一天的忙碌，晚上11點半回到家中，簡單收拾下，鬧鐘提醒大白要刪數據了，于是連上準備開搞。
安全起見刪除腳本加了個sleep幾毫秒，nohup腳本拉起，看了下日志已經正常啟動了，看著時間還早刷了會兒抖音，回來看腳本也執行完了，完美!
數據刪完了，呼呼睡大覺去了!
2. 為啥磁盤還滿?
第二天，像往常一樣，大白去小區附近的菜市場3元買了個餅，邊吃邊等公交車。
轉了3次地鐵，終于到公司了，一杯枸杞水，準備開干!
呃?昨晚刪了 咋磁盤占用率竟然86%了，不降反而漲了，真是見鬼了。
穩住了神，分析一波應該是MySQL并沒有真正清理掉這部分數據，而是假刪除。
這種假刪除的行為在Linux中并不稀罕，屬于常規操作，算是一種策略思想，所以斷定MySQL也可能這么干了。
谷歌一下，文章還真不少呢，翻了幾篇之后 找到了一個命令查看碎片信息：
- SELECT * from
- (
- SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
- table_rows AS 'Number of Rows',
- CONCAT(ROUND(data_length/(1024*1024),6),' M') AS 'data_size',
- CONCAT(ROUND(index_length/(1024*1024),6),' M') AS 'index_size' ,
- CONCAT(ROUND(data_free/(1024*1024),6),' M') AS'data_free',
- ENGINE as 'engine'
- FROM information_schema.TABLES
- WHERE table_schema = #{庫名}
- ) t ORDER BY data_free DESC;
- data_size : 數據的大小
- index_size: 索引的大小
- data_free :數據在使用中的留存空間
- engine：表引擎名稱
其中data_free就代表磁盤碎片的大小，也就是我們需要消滅的地方。
于是把上面的sql語句修改了一下執行，果然bighero表的data_free有20GB這么多，找到了原因，所以大白準備手動清理一波。
3. 磁盤清理神器
谷歌一波之后發現，不同的MySQL存儲引擎清理方式有所不同。
- SHOW ENGINES;//查看引擎命令
MySQL有多種存儲引擎，常用的是MyISAM和InnoDB，大白的庫使用的是InnoDB引擎，不過先簡單看下這倆有啥特點吧。
3.1 MyISAM引擎
MyISAM基于ISAM存儲引擎，并對其進行擴展。
- 支持 B-tree/FullText/R-tree 索引類型;
- 鎖級別為表鎖，表鎖優點是開銷小，加鎖快;缺點是鎖粒度大，發生鎖沖動概率較高，容納并發能力低，這個引擎適合查詢為主的業務;
- 此引擎不支持事務，也不支持外鍵;
- BLOB和TEXT列可以被索引;
- 強調了快速讀取操作，比如它存儲表的行數，只需要直接讀取已經保存好的值而不需要進行全表掃描。
3.2 InnoDB引擎
- 支持事務，支持回滾，支持外鍵;
- 支持 Hash/B-tree 索引類型;
- 鎖級別為行鎖，行鎖優點是適用于高并發的頻繁表修改，高并發是性能優于 MyISAM;
- 系統消耗較大，索引不僅緩存自身，也緩存數據，相比 MyISAM 需要更大的內存;
3.3 操作一把
InnoDB引擎可以選擇的操作命令包括：
- OPTIMIZE TABLE tablename
- ALTER TABLE tablename ENGINE = INNODB
實際上在運行上述清理命令時，MySQL會鎖定表，清理的數據越大消耗的時間越長，因此這個操作一定要在夜深人靜的時候進行操作。
OPTIMIZE TABLE命令會重組表和索引的物理存儲，減少對存儲空間使用。
ALTER TABLE tablename ENGINE = Innodb;命令好像是句廢話，但是實際上也重新整理碎片了，它實際執行的是一個空的ALTER命令，會重建整個表，刪掉未使用的空白空間。
好了，大致找到了命令，但是還得半夜操作，這事真是不地道啊，不過也沒辦法。
像往常一樣，11點半到家，洗漱了下，開始清理，心里還有點忐忑，等待了一會兒，看到已經OK了。
刷一下監控磁盤占用率已經降到80%以下了，舒一口氣，可以安心睡覺了。
4. MySQL為什么會有碎片?
我們以InnoDB存儲引擎為例，來看看為什么會出現碎片。
- 當執行刪除一些行，這些行只是被標記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒有真的被釋放回收。
- 大量隨機刪除操作，會造成不連續的空白空間，當插入數據時，這些空白空間則會優先被利用起來，但是肯定不會全部被利用，也就出現數據碎片。
- 大量UPDATE操作，Innodb的最小物理存儲分配單位是頁，在更新變長數據時UPDATE也可能導致頁分裂，頻繁的頁分裂，頁會變得稀疏，并且被不規則的填充，最終會有碎片，比如原來長度是255字節修改之后是128字節，那么就可能出現128字節左右的空洞無法被100%利用。
由于清理碎片需要鎖表，對于業務有影響，MySQL官方建議不要每小時或每天進行碎片整理，一般根據實際情況，只需要每周甚至更久整理一次即可。
就這樣吧!明天就要開工了，今天再好好耍一天。
