背景
經(jīng)常使用 mysql 的話，會(huì)發(fā)現(xiàn) mysql 數(shù)據(jù)文件的磁盤空間一般會(huì)不停的增長，而且有時(shí)候刪了數(shù)據(jù)或者插入一批數(shù)據(jù)的時(shí)候，磁盤空間有時(shí)候還會(huì)毫無變化。引發(fā)這個(gè)其妙現(xiàn)象的就是 mysql 的表空間碎片。
什么是表空間碎片？
表空間碎片指的是表空間中存在碎片，形象一點(diǎn)來比喻的話，就像是一張 a4 紙，“表空間碎片”就像是把這張 a4 紙撕碎，再重新拼起來，各個(gè)碎片之間都會(huì)有一些縫隙存在，這些縫隙就是“表空間碎片”。重新拼起來的碎片實(shí)際上會(huì)比完整的 a4 紙大上一圈，這也代表著表空間容易引發(fā)的問題：空間浪費(fèi)。
對于背景中描述的現(xiàn)象，可以用一張圖來進(jìn)行解釋：
圖中的數(shù)字代表真實(shí)的數(shù)據(jù)行，圓角矩形代表一個(gè)表的表空間。從左往右，第一次操作是刪除數(shù)據(jù)，由于 mysql 在設(shè)計(jì)上是不會(huì)主動(dòng)釋放空間的，因此當(dāng)表中的數(shù)據(jù)行被刪除時(shí)，雖然數(shù)據(jù)被“刪除”了，但是實(shí)際上這部分空間是沒有釋放的，依舊會(huì)被 table a 占用，因此也就出現(xiàn)了這樣子的情景：刪除了日志表的很多數(shù)據(jù)，但是 mysql 的磁盤空間并沒有降低。
ps：這種不釋放空間的設(shè)計(jì)多半和惰性刪除有關(guān)，早期設(shè)計(jì)數(shù)據(jù)庫時(shí)，使用的 io 設(shè)備一般是機(jī)械盤，讀寫性能比 ssd 差很多，所以刪除操作一般不會(huì)直接觸發(fā)磁盤上的數(shù)據(jù)刪除。
可以看到數(shù)據(jù)刪除之后，原本連續(xù)的空間中出現(xiàn)了兩個(gè)空白的區(qū)域，這種一般就叫做表空間空洞，空洞太多了就叫做表空間碎片化（對應(yīng)的是表空間連續(xù)）。這部分的空間雖然不會(huì)釋放，但是會(huì)被標(biāo)記為可重復(fù)利用，參考最右邊的表空間示意圖（第三個(gè)圓角矩形），當(dāng)新插入數(shù)據(jù)的時(shí)候新數(shù)據(jù)會(huì)重新寫入到表空間空洞中，這也代表著：在大規(guī)模刪除過數(shù)據(jù)的表上，寫入數(shù)據(jù)時(shí)，表空間可能不會(huì)明顯增長或者不會(huì)增長。
實(shí)際上產(chǎn)生表空間空洞的操作并不只有 delete，update 也會(huì)引起這個(gè)問題，比如在 varchar 這種變長的字符型列中修改數(shù)據(jù)，改短一些的時(shí)候就會(huì)出現(xiàn)非常小的空洞，改長的話就有可能會(huì)因?yàn)榭臻g不足導(dǎo)致把數(shù)據(jù)行的一些數(shù)據(jù)遷移到其他地方去。
怎么查看表空間碎片
mysql 的系統(tǒng)表記錄了表空間的使用情況，可以用如下查詢檢查：
|
1
2
3
4
5
6
7
8
9
10
|
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.tableswhere table_schema = 'tablename'order by data_free desc; |
data_free 指表空間碎片的總空間大小，data_free_pct 指這個(gè)表的碎片百分比，效果如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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) |
第一行數(shù)據(jù)是測試用的數(shù)據(jù)，表中的所有數(shù)據(jù)都被刪掉了，因此計(jì)算出來的 data_free_pct 超過了 100%。
怎么解決表空間碎片問題
目前，能夠回收表空間的辦法僅有一個(gè)，就是重建表，手段包括但不限于 optimize，alter table 等。alter table 的有些操作只能靠 rebuild 表來完成，所以有時(shí)候?qū)Υ蟊磉M(jìn)行一些維護(hù)操作之后，也會(huì)看到磁盤空間使用率下降，這就是回收了表空間碎片騰出來的那一部分空間。
從一般經(jīng)驗(yàn)來看，表空間碎片的回收操作不建議經(jīng)常執(zhí)行，每個(gè)月一次就足夠了，因?yàn)?rebuild 表對服務(wù)器的資源影響會(huì)比較大，且會(huì)影響這個(gè)表的寫入操作。碎片率（data_free_pct）低于 20% 的時(shí)候也不用特別在意，除非磁盤空間非常緊張，且日志基本被清空。
對于回收空間的問題
對一些日志表，或者是有區(qū)域性特征的表，建議使用 mysql 的分區(qū)表來管理，需要清理一批數(shù)據(jù)的時(shí)候，可以用 partition truncate 的方式進(jìn)行清理，磁盤空間也能直接釋放掉。
以上就是mysql 表空間碎片的概念及相關(guān)問題解決的詳細(xì)內(nèi)容，更多關(guān)于mysql 表空間碎片的資料請關(guān)注服務(wù)器之家其它相關(guān)文章！
