mysql清除磁碟片段

標籤：form   整理   任務   操作   you   roc   nal   清除   shadow   

任務背景

接到金山雲警示簡訊，說某資料庫的容量已經達到了90%的水位線，於是登陸控制台查看詳細情況。

在控制台首先發現，每一天的磁碟容量的確有所波動，那麼就證明開發人員寫的“資源回收”模組是在正常啟動並執行，

那麼就說明沒有什麼資料是可以刪的，既然刪不掉多餘的資料又不想多掏錢擴磁碟容量，只能從“磁碟片段”下手了。而InnoDB引擎清理磁碟片段的命令就是OPTIMIZE。

具體操作

首先我先查詢一下所有的“磁碟片段情況”，使用語句如下：

    select CONCAT(TABLE_SCHEMA,‘.‘,TABLE_NAME) as 資料表名,concat(truncate(sum(DATA_LENGTH+DATA_FREE+INDEX_LENGTH)/1024/1024,2),‘ MB‘) as total_size, concat(truncate(sum(DATA_LENGTH)/1024/1024,2),‘ MB‘) as data_size,concat(truncate(sum(DATA_FREE)/1024/1024,2),‘ MB‘) as data_free, concat(truncate(sum(INDEX_LENGTH)/1024/1024,2),‘MB‘) as index_size from information_schema.tables group by TABLE_NAME order by data_length desc; 

或者使用select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (‘information_schema‘, ‘mysql‘) and data_free > 0;也可以，這個是查詢data_free大於0的所有表。

然後看到我這個叫history_device_flow_day的表裡情況如下：

表裡的data_free就是磁碟片段的量，比如我現在要幹掉history_device_flow_day裡所有的磁碟片段，是975MB，於是先查詢一下這個history_device_flow_day的儲存引擎，使用語句如下：

    show table status from jsonlinefssrds where name=‘history_device_flow_day‘;

上面語句裡的jsonlinefssrds是對應的資料庫，看到的效果如下：

儲存引擎是InnoDB，那麼就可以啟動清除片段的語句了：OPTIMIZE TABLE 資料表表名;，因為OPTIMIZE TABLE只對MyISAM、BDB和InnoDB表起作用。

再執行了OPTIMIZE TABLE history_device_flow_day;之後，大約9分鐘，就會看到“OK”的字樣：

估計有的朋友會問，那上面不是明明寫了“Table does not support optimize, doing recreate + analyze instead”嗎？這個其實無妨，實際上磁碟片段已經被清除掉了。我們可以再用一次查詢磁碟片段的命令看一下，

的確釋放了900多M。

或者使用ALTER TABLE 表名 ENGINE = Innodb;（只是InnoDB的表可以這麼做）來達到清理磁碟片段的目的，這個命令表面上看什麼也不做,實際上是重新整理片段了。當執行最佳化操作時,實際執行的是一個空的ALTER命令,但是這個命令也會起到最佳化的作用,它會重建整個表,刪掉未使用的空白空間。

補充

為什麼會產生磁碟片段？那是因為某一個表如果經常插入資料和刪除資料，必然會產生很多未使用的空白空間，這些空白空間就是不連續的片段，這樣久而久之，這個表就會佔用很大空間，但實際上表裡面的記錄數卻很少，這樣不但會浪費空間，並且查詢速度也更慢。

注意！OPTIMIZE操作會暫時鎖住表,而且資料量越大,耗費的時間也越長,它畢竟不是簡單查詢操作。所以把OPTIMIZE命令放在程式中是不妥當的,不管設定的命中率多低,當訪問量增大的時候,整體命中率也會上升,這樣肯定會對程式的運行效率造成很大影響。比較好的方式就是做個shell,定期檢查mysql中 information_schema.TABLES欄位,查看DATA_FREE欄位,大於0的話,就表示有片段，然後啟動指令碼。

參考資料

http://pengbotao.cn/mysql-suipian-youhua.html
http://irfen.me/mysql-data-fragmentation-appear-and-optimization/

最後的最後，如果您覺得本文對您升職加薪有協助，那麼請不吝贊助之手，刷一下下面的二維碼，贊助本人繼續寫更多的博文！

mysql清除磁碟片段