MySQL 表空間碎片的概念及相關問題解決

Posted on by

背景

經常使用 MySQL 的話，會發現 MySQL 數據文件的磁盤空間一般會不停的增長，而且有時候刪瞭數據或者插入一批數據的時候，磁盤空間有時候還會毫無變化。引發這個其妙現象的就是 MySQL 的表空間碎片。

什麼是表空間碎片？

表空間碎片指的是表空間中存在碎片，形象一點來比喻的話，就像是一張 A4 紙，“表空間碎片”就像是把這張 A4 紙撕碎，再重新拼起來，各個碎片之間都會有一些縫隙存在，這些縫隙就是“表空間碎片”。重新拼起來的碎片實際上會比完整的 A4 紙大上一圈，這也代表著表空間容易引發的問題：空間浪費。

對於背景中描述的現象，可以用一張圖來進行解釋：

圖中的數字代表真實的數據行，圓角矩形代表一個表的表空間。從左往右，第一次操作是刪除數據，由於 MySQL 在設計上是不會主動釋放空間的，因此當表中的數據行被刪除時，雖然數據被“刪除”瞭，但是實際上這部分空間是沒有釋放的，依舊會被 Table A 占用，因此也就出現瞭這樣子的情景：刪除瞭日志表的很多數據，但是 MySQL 的磁盤空間並沒有降低。

PS：這種不釋放空間的設計多半和惰性刪除有關，早期設計數據庫時，使用的 IO 設備一般是機械盤，讀寫性能比 SSD 差很多，所以刪除操作一般不會直接觸發磁盤上的數據刪除。

可以看到數據刪除之後，原本連續的空間中出現瞭兩個空白的區域，這種一般就叫做表空間空洞，空洞太多瞭就叫做表空間碎片化（對應的是表空間連續）。這部分的空間雖然不會釋放，但是會被標記為可重復利用，參考最右邊的表空間示意圖（第三個圓角矩形），當新插入數據的時候新數據會重新寫入到表空間空洞中，這也代表著：在大規模刪除過數據的表上，寫入數據時，表空間可能不會明顯增長或者不會增長。

實際上產生表空間空洞的操作並不隻有 delete，update 也會引起這個問題，比如在 varchar 這種變長的字符型列中修改數據，改短一些的時候就會出現非常小的空洞，改長的話就有可能會因為空間不足導致把數據行的一些數據遷移到其他地方去。

怎麼查看表空間碎片

MySQL 的系統表記錄瞭表空間的使用情況，可以用如下查詢檢查：

SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
                table_rows AS 'Number of Rows',
                CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'data_size',
                CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'index_size' ,
                CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
                CONCAT(ROUND(data_free/data_length,2),' %') AS 'data_free_pct',
                ENGINE as 'engine'
FROM information_schema.TABLES
WHERE table_schema = 'tablename' 
ORDER by data_free desc;

data_free 指表空間碎片的總空間大小，data_free_pct 指這個表的碎片百分比，效果如下：

mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
    ->                 table_rows AS 'Number of Rows',
    ->                 CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'data_size',
    ->                 CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'index_size' ,
    ->                 CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
    ->                 CONCAT(ROUND(data_free/data_length,2),' %') AS 'data_free_pct',
    ->                 ENGINE as 'engine'
    -> FROM information_schema.TABLES
    -> WHERE table_schema = 'sbtest'
    -> ORDER by data_free desc;
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| table_name     | Number of Rows | data_size | index_size | data_free | data_free_pct | engine |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| sbtest.sbtest5 |              0 | 0.02 M    | 0.00 M     | 44.00 M   | 2816.00 %     | InnoDB |
| sbtest.sbtest4 |         986400 | 214.70 M  | 15.52 M    | 4.00 M    | 0.02 %        | InnoDB |
| sbtest.sbtest3 |         986400 | 214.70 M  | 15.52 M    | 4.00 M    | 0.02 %        | InnoDB |
| sbtest.sbtest2 |         986400 | 214.70 M  | 15.52 M    | 4.00 M    | 0.02 %        | InnoDB |
| sbtest.sbtest1 |         987400 | 199.70 M  | 15.52 M    | 4.00 M    | 0.02 %        | InnoDB |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
5 rows in set (0.00 sec)

第一行數據是測試用的數據，表中的所有數據都被刪掉瞭，因此計算出來的 data_free_pct 超過瞭 100%。

怎麼解決表空間碎片問題

目前，能夠回收表空間的辦法僅有一個，就是重建表，手段包括但不限於 optimize，alter table 等。alter table 的有些操作隻能靠 rebuild 表來完成，所以有時候對大表進行一些維護操作之後，也會看到磁盤空間使用率下降，這就是回收瞭表空間碎片騰出來的那一部分空間。

從一般經驗來看，表空間碎片的回收操作不建議經常執行，每個月一次就足夠瞭，因為 rebuild 表對服務器的資源影響會比較大，且會影響這個表的寫入操作。碎片率（data_free_pct）低於 20% 的時候也不用特別在意，除非磁盤空間非常緊張，且日志基本被清空。

對於回收空間的問題

對一些日志表，或者是有區域性特征的表，建議使用 MySQL 的分區表來管理，需要清理一批數據的時候，可以用 partition truncate 的方式進行清理，磁盤空間也能直接釋放掉。

以上就是MySQL 表空間碎片的概念及相關問題解決的詳細內容，更多關於MySQL 表空間碎片的資料請關註WalkonNet其它相關文章！

推薦閱讀：