網(wǎng)絡(luò)上有很多MySQL表碎片整理的問題，大多數(shù)是通過demo一個表然后參考data free來進行碎片整理，這種方式對myisam引擎或者其他引擎可能有效（本人沒有做詳細的測試）.

對Innodb引擎是不是準(zhǔn)確的，或者data free是不是可以參考，還是值得商榷的。

本文基于MySQL的Innodb存儲引擎，數(shù)據(jù)庫版本是8.0.18，對碎片（fragment）做一個簡單的分析，來說明如何量化表的碎片化程度。

涉及的參數(shù)

1，information_schema_stats_expiry

information_schema是一個基于共享表空間的虛擬數(shù)據(jù)庫，存儲的是一些系統(tǒng)元數(shù)據(jù)信息，某些系統(tǒng)表的數(shù)據(jù)并不是實時更新的，具體更新是基于參數(shù)information_schema_stats_expiry。

information_schema_stats_expiry默認(rèn)值是86400秒，也就是24小時，意味著24小時刷新一次information_schema中的數(shù)據(jù)，做測試的時候可以設(shè)置為0，實時刷新information_schema中的元數(shù)據(jù)信息。

2，innodb_fast_shutdown

因為要基于磁盤做一些統(tǒng)計，需要將緩存或者redo log中的數(shù)據(jù)在重啟實例的時候?qū)崟r刷入磁盤，這里設(shè)置為0，在重啟數(shù)據(jù)庫的時候?qū)⒕彺婊蛘遰edo log實時寫入表的物理文件。

3，innodb_stats_persistent_sample_pages

因為涉及一些系統(tǒng)數(shù)據(jù)更新時對page的采樣比例，這里設(shè)置為一個較大的值，為100000，盡可能高比例采樣來生成系統(tǒng)數(shù)據(jù)。

4，innodb_flush_log_at_trx_commit sync_binlog

因為涉及大量數(shù)據(jù)的寫操作，為加快測試，關(guān)閉double 1模式。

5，innodb_fill_factor

頁面填充率保留默認(rèn)的設(shè)置，默認(rèn)值是100

以上涉及的參數(shù)僅針對本測試，并不一定代表最優(yōu)，同時測試過程中（數(shù)據(jù)寫入或者刪除后）會不斷地重啟實例，以刷新相對應(yīng)的物理文件。

碎片的概念

數(shù)據(jù)存儲在文件系統(tǒng)上的時候，總是不能100%利用分配給它的物理空間：

比如刪除數(shù)據(jù)會在頁面上留下一些”空洞”，或者隨機寫入（聚集索引非線性增加）會導(dǎo)致頁分裂，頁分裂導(dǎo)致頁面的利用空間少于50%；

另外對表進行增刪改會引起對應(yīng)的二級索引值的隨機的增刪改，也會導(dǎo)致索引結(jié)構(gòu)中的數(shù)據(jù)頁面上留下一些“空洞”；

雖然這些空洞有可能會被重復(fù)利用，但終究會導(dǎo)致部分物理空間未被使用，也就是碎片。

同時，即便是設(shè)置了填充因子為100%，Innodb也會主動留下page頁面1/16的空間作為預(yù)留使用（An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.）。

關(guān)系數(shù)據(jù)庫的存儲結(jié)構(gòu)原理上是類似的，理論上很簡單，就不過多啰嗦了。碎片是一個客觀存在的事實。

創(chuàng)建測試表以及數(shù)據(jù)

做個簡單的測試，表結(jié)構(gòu)如下，

CREATETABLE `fragment_test` (

`id` INTNOTNULL AUTO_INCREMENT,

`c1` INTNULLDEFAULTNULL,

`c2` INTNULLDEFAULTNULL,

`c3` VARCHAR(50) NULLDEFAULTNULL,

`c4` DATETIME(6) NULLDEFAULTNULL,

PRIMARYKEY (`id`)

);

CREATEINDEX idx_c1 ON fragment_test(c1);

CREATEINDEX idx_c2 ON fragment_test(c2);

CREATEINDEX idx_c3 ON fragment_test(c3);

生成200W測試數(shù)據(jù)（CALL test_insertdata(2000000);）

CREATE DEFINER=`root`@`%` PROCEDURE `test_insertdata`(

IN `loopcount` INT

)

BEGINdeclare v_uuid varchar(50);

while loopcount>0 do

set v_uuid = uuid();

INSERTINTO fragment_test(c1,c2,c3,c4) VALUES (RAND()*200000000,RAND()*200000000,UUID(),NOW(6));

set loopcount = loopcount -1;

endwhile;

END

查詢語句，參考自最后的鏈接中的文章

SELECT NAME,

TABLE_ROWS,

UPDATE_TIME,

format_bytes(t.data_length) DATA_SIZE,

format_bytes(t.index_length) INDEX_SIZE,

format_bytes(t.data_length+t.index_length) TOTAL_SIZE,

format_bytes(t.data_free) DATA_FREE,

format_bytes(it.FILE_SIZE) FILE_SIZE,

format_bytes((it.FILE_SIZE/10- (t.data_length/10+ t.index_length/10))*10) WASTED_SIZE

FROM information_schema.TABLES as t

JOIN information_schema.INNODB_TABLESPACES as it

ON it.name = concat(table_schema,"/",table_name)

WHERE TABLE_NAME ='fragment_test';

碎片的量化

上面說到數(shù)據(jù)在存儲的時候，總是無法100%利用物理存儲空間，Innodb甚至?xí)约褐鲃宇A(yù)留一部分空閑的空間（1/16），那么如何衡量一個表究竟有多少尚未利用的空間？

這里從系統(tǒng)表information_schema.tables和information_schema.innodb_tablespaces，來對比實際使用空間和已分配空間來對比，來間接量化碎片或者說未利用空間的程度。

然后觀察數(shù)據(jù)空間的分配情況，盡管系統(tǒng)表中的數(shù)據(jù)不是完全準(zhǔn)確的，但是也比較接近實際的200W，系統(tǒng)表顯示1971490，暫時拋開這一小點誤差。

可以很清楚地看到，數(shù)據(jù)和索引的空間是329MB，文件空間是344MB，DATA_FREE空間是6MB。

隨機刪除1/4的數(shù)據(jù)，也就是50W行（DELETE FROM fragment_test ORDER BY RAND() LIMIT 500000;）

然后重啟實例，并執(zhí)行分析表（analyze table），繼續(xù)來觀察這個空間的分配，這里看到，

1，系統(tǒng)表顯示150000行，跟表中的數(shù)據(jù)完全一致（盡管更多的時候這個值是一個大概的值，并不一定準(zhǔn)確，嚴(yán)格說可能非常不準(zhǔn)確，這里歸因于innodb_stats_persistent_sample_pages的設(shè)置）。

2，數(shù)據(jù)文件空間沒有增加（344MB），可以理解，因為這里是刪數(shù)據(jù)操作，所以不用申請空間。

3，刪除了1/4的數(shù)據(jù)，數(shù)據(jù)和索引的的大小基本上不變，這里就開始有疑問了，為什么沒有成比例減少？

4，data_free增加了3MB，顯然這不是跟刪除的數(shù)據(jù)成比例增加的

那么怎么理解碎片？DATA_FREE怎么理解？碎片或者說可用空間又怎么衡量？

從200W數(shù)據(jù)中隨機刪除50W，也就是1/4，表的空間沒有變化，可以肯定的是現(xiàn)在存在大量的碎片或者說可用空間，但是表的總的大小沒變化，data_free也基本上沒有變化到這里就有點說不通了。

那么data free到底是怎么計算的，看官方的解釋：

The number of allocated but unused bytes.

InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace.

If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table.

Free space means the number of bytes in completely free extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

data_free的計算方式或者說條件，是完全空閑的區(qū)（extents，每個區(qū)1MB，64個連續(xù)的16 kb 大小的page），只有一個完全沒有使用的區(qū)，才統(tǒng)計為data_free，因此data_free并不能反映出來真正的空閑空間。

同時測試中發(fā)現(xiàn)，performance_schema.tables中的table_rows會受到innodb_stats_persistent_sample_pages的影響，但是data_length和index_length看起來是不會受innodb_stats_persistent_sample_pages的影響的

這里采樣比例已經(jīng)足夠大，盡管table_rows已經(jīng)是一個完全準(zhǔn)確的數(shù)字了，但是data_length和index_length卻仍舊是一個誤差非常大的數(shù)字。

說到這里，那么這個碎片問題如何衡量？如果只是看performance_schema.tables或者information_schema.INNODB_TABLESPACES，其實依舊是一個無解的問題，因為無法通過這些信息，得到一個相對準(zhǔn)確的碎片化程度。

其實在這里（參考鏈接）的評論中也提到這個問題，我是比較贊同的。

如果要真正得到碎片程度，其實還是需要重建表來對比實現(xiàn)，這里刪除了1/4的數(shù)據(jù)，理論上就有大概1/4的可用空間，但是上面的查詢結(jié)果并不能給出一個明確的答案，怎么驗證這個答案呢？

這里就要粗暴地優(yōu)化表了（optimize table fragment_test+analyze table），優(yōu)化表只是“重整”了碎片，但是系統(tǒng)表的數(shù)據(jù)并沒有更新，因此必須要再執(zhí)行一次分析表 analyze table來更新元數(shù)據(jù)信息

其實這里也能說明，analyze table只是更新元數(shù)據(jù)，如果存儲空間沒有更新（recreated），單純地analyze table也是沒有用的。

對標(biāo)進行optimize和anlayze之后，這里可以看到，物理空間確實減少了大概1/4的量。

這里其實就是為了說明一個問題：Innodb表無法通過data free來判斷表的碎片化程度。

然而這里（參考鏈接）的測試說明刪除數(shù)據(jù)后data free有明顯的變化，這個又是為什么，剛特么說無法通過data free來判斷表的碎片化程度，現(xiàn)在又說刪除數(shù)據(jù)后data free有明顯的變化？？？

其實（參考鏈接）中有另外一個比較有意思的測試，相對用隨機刪除的方式，采用連續(xù)刪除的時候（或者是整個表的數(shù)據(jù)全部刪除），這個data free確實會相對準(zhǔn)確地體現(xiàn)出來刪除數(shù)據(jù)后表size的變化情況。

這又是為什么？其實不難理解，上面已經(jīng)說了，data free的計算方式，是按照完全“干凈”的區(qū)（extent）來做統(tǒng)計的，

如果按照聚集索引連續(xù)的方式刪除（相對隨機刪除），那些存儲連續(xù)數(shù)據(jù)的區(qū)（extent）是可以完全釋放出來的，這些區(qū)的空間釋放出來之后，會被認(rèn)為是data free，所以data free此時又是相對來說準(zhǔn)確的。

因此，很多測試，如果想到得到客觀的數(shù)據(jù)，需要盡可能多地考慮到對應(yīng)的場景和測試數(shù)據(jù)情況。

碎片的衡量

實際業(yè)務(wù)中，對表的刪除或者增刪改，很少是按照聚集索引進行批量刪除，或者說一旦存在隨機性的刪除或者更新（頁分裂），都會造成一定程度的碎片，而這個碎片化的程度是無法通過data free來衡量的。

那么又如何衡量這個碎片程度呢？

1，自己根據(jù)業(yè)務(wù)進行預(yù)估，在可接受程度內(nèi)進行optimize table，記錄optimize table之后的table size變化程度，來衡量一個表在一定時間操作后的碎片化程度，從而來指導(dǎo)是否，或者多久對該表再次進行optimize table

2，采用上述連接中提到的innodb_ruby 這個工具，直接解析表的物理文件，這種方式相對來說更加直接。不過這個工具本人沒來得及測試，理論上是沒有問題的。

　這里盜用上述鏈接中的圖片，綠色的是實際使用的空間，中間的黑塊就是所謂的碎片或者說是空洞。

補充：

早上起來，又想到了另外一種case，就是說隨機刪除后，剩余空間中出現(xiàn)了“空洞”，這些空洞在寫數(shù)據(jù)的時候，會不會被再次利用？

驗證其實很簡單，寫入200W數(shù)據(jù)，隨機刪除50W后，analyze table更新performance_schema,然后繼續(xù)再寫入50W行的數(shù)據(jù)，如果會利用之前隨機刪除的空洞空間，那么就不會重新分配物理空間，否則就會重新分配物理空間。

因為聚集索引的Id是自增的，相當(dāng)于順序?qū)懭?#xff0c;理論上是不會重用之前刪除留下的空洞的，測試的結(jié)果還是在預(yù)期之內(nèi)的，重新寫入50W數(shù)據(jù)后，表對應(yīng)的物理文件會有一個很明顯的增加。