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