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