MySQL表碎片化(Table Fragmentation)的原因

關于MySQL中表碎片化(Table Fragmentation)產生的原因，簡單總結一下，MySQL Engine不同，碎片化的原因可能也有所差別。這里沒有深入理解、分析這些差別。此文僅以InnoDB引擎為主。總結如有不足或錯誤的地方，敬請指出。

InnoDB表的數據存儲在頁(page)中，每個頁可以存放多條記錄。這些記錄以樹形結構組織，這顆樹稱為B+樹索引。表中數據和輔助索引都是使用B+樹結構。維護表中所有數據的這顆B+樹索引稱為聚簇索引，通過主鍵來組織的。聚簇索引的葉子節點包含行中所有字段的值，輔助索引的葉子節點包含索引列和主鍵列。

在InnoDB中，刪除一些行，這些行只是被標記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒有真的被釋放回收。InnoDB的Purge線程會異步的來清理這些沒用的索引鍵和行。但是依然沒有把這些釋放出來的空間還給操作系統重新使用，因而會導致頁面中存在很多空洞。如果表結構中包含動態長度字段，那么這些空洞甚至可能不能被InnoDB重新用來存新的行，因為空間空間長度不足。關于這個你可以參考博客Overview of fragmented MySQL InnoDB tables。

另外，刪除數據就會導致頁(page)中出現空白空間，大量隨機的DELETE操作，必然會在數據文件中造成不連續的空白空間。而當插入數據時,這些空白空間則會被利用起來.于是造成了數據的存儲位置不連續。物理存儲順序與邏輯上的排序順序不同,這種就是數據碎片。

對于大量的UPDATE，也會產生文件碎片化 ， Innodb的最小物理存儲分配單位是頁(page)，而UPDATE也可能導致頁分裂(page split)，頻繁的頁分裂，頁會變得稀疏，并且被不規則的填充，所以最終數據會有碎片。

表的數據存儲也可能碎片化。然而數據存儲的碎片化比索引更加復雜。有三種類型的數據碎片化。

##下面部分內容摘自【高性能MySQL】##

行碎片(Row fragmentation)

這種碎片指的是數據行被存儲為多個地方的多個片段。即使查詢只從索引中訪問一行記錄。行碎片也會導致性能下降。

行間碎片(Intra-row fragmentaion)

行間碎片是指邏輯上順序的頁，或者行在磁盤上不是順序存儲的。行間碎片對諸如全表掃描和聚簇索引掃描之類的操作有很大的影響，因為這些操作原本能夠從磁盤上順序存儲的數據中獲益。

剩余空間碎片(Free space fragmentation)

剩余空間碎片是指數據頁中有大量的空余空間。這會導致服務器讀取大量不需要的數據。從而造成浪費。

對于MyISAM表，這三類碎片化都有可能發生。但InnoDB不會出現短小的行碎片；InnoDB會移動短小的行并寫到一個片段中。InnoDb會移動短小的行并重寫到一個片段中。

從二級索引中隨機插入或刪除可能會導致索引碎片化。碎片意味著磁盤上索引頁的物理排序不接近頁面上記錄的索引排序，或者64頁塊中有許多未使用的頁面被分配給索引。

碎片化的一個癥狀是表格占用的空間比“應該”占用的空間多。多少確切地說，很難確定。所有InnoDB數據和索引都存儲在B-trees中，它們的fill factor可能在50％到100％之間變化。碎片的另一個癥狀是像這樣的表掃描需要比“應該”花費更多的時間

MySQL中如何找出碎片化嚴重的表

關于MySQL中表碎片化，那么如何找出MySQL中的碎片，一般有兩種方法。

方法1：使用show table status from xxxx like 'xxxx' \G;

第一個xxx:表所在的數據庫名稱，第二個xxx:要查詢的表名。這個方法其實不太實用。例如，只能單個表的查詢碎片化情況(難道一個數據庫要一個個表去試？)，不能查詢某個數據庫或整個實例下所有表的碎片化等等。這里僅僅作為一個參考方法而已。mysql>?create?table?frag_tab_myisam->?(->?????id??int,->?????name?varchar(63)->?)?engine=MyISAM;Query?OK,?0?rows?affected?(0.00?sec)mysql>?insert?into?frag_tab_myisam->?values(1,?'it?is?only?test?row?1');Query?OK,?1?row?affected?(0.00?sec)mysql>mysql>?insert?into?frag_tab_myisam->?values(2,?'it?is?only?test?row?2');Query?OK,?1?row?affected?(0.00?sec)mysql>mysql>mysql>?insert?into?frag_tab_myisam->?values(3,?'it?is?only?test?row?3');Query?OK,?1?row?affected?(0.00?sec)mysql>mysql>?insert?into?frag_tab_myisam->?values(4,?'it?is?only?test?row?4');Query?OK,?1?row?affected?(0.00?sec)mysql>mysql>??show?table?status?from?kkk?like?'frag_tab_myisam'?\G;

如下截圖所示，如果沒有DML操作，Data_free的大小是0

然后我們在數據庫上刪除掉2條記錄，如下所示,Data_free的大小為64KB大小了。mysql>?delete?from?frag_tab_myisam?where?id?=1;Query?OK,?1?row?affected?(0.00?sec)mysql>?delete?from?frag_tab_myisam?where?id?=3;Query?OK,?1?row?affected?(0.00?sec)

方法2：查詢information_schema.TABLES獲取表的碎片化信息。

如下所示，這個是我整理的一個查詢表碎片化的經典腳本。你可以在上面做很多衍生：例如，查詢某個數據庫的表碎片化情況。或者空閑空間超過50M大小的表。這個可以根據自己的需求設定查詢條件。在此略過。SELECT?CONCAT(table_schema,?'.',?table_name)???????????????????AS??TABLE_NAME,engine??????????????????????????????????????????????????AS??TABLE_ENGINE,table_type??????????????????????????????????????????????AS??TABLE_TYPE,table_rows??????????????????????????????????????????????AS??TABLE_ROWS,CONCAT(ROUND(data_length??/?(?1024?*?1024),?2),?'M')????AS??TB_DATA_SIZE,CONCAT(ROUND(index_length?/?(?1024?*?1024),?2),?'M')????AS??TB_IDX_SIZE,CONCAT(ROUND((data_length?+?index_length?)/?(?1024?*?1024?),?2),?'M')????????????????????????AS??TOTAL_SIZE,CASE?WHEN??data_length?=0?THEN?0ELSE??ROUND(index_length?/?data_length,?2)?END?????AS??TB_INDX_RATE,CONCAT(ROUND(?data_free?/?1024?/?1024,2),?'MB')???????????AS??TB_DATA_FREE,CASE?WHEN?(data_length?+?index_length)?=?0?THEN?0ELSE?ROUND(data_free/(data_length?+?index_length),2)END???????????????????????????????????????????????????????AS??TB_FRAG_RATEFROM?information_schema.TABLESORDER?BY?data_free?DESC;SELECT?CONCAT(table_schema,?'.',?table_name)????????????????????AS??TABLE_NAME,engine???????????????????????????????????????????????????AS??TABLE_ENGINE,table_type???????????????????????????????????????????????AS??TABLE_TYPE,table_rows???????????????????????????????????????????????AS??TABLE_ROWS,CONCAT(ROUND(data_length??/?(?1024?*?1024),?2),?'M')?????AS??TB_DATA_SIZE,CONCAT(ROUND(index_length?/?(?1024?*?1024),?2),?'M')?????AS??TB_IDX_SIZE,CONCAT(ROUND((data_length?+?index_length?)/?(?1024?*?1024?),?2),?'M')?????????????????????????AS??TOTAL_SIZE,CASE?WHEN??data_length?=0?THEN?0ELSE??ROUND(index_length?/?data_length,?2)?END??????AS??TB_INDX_RATE,CONCAT(ROUND(?data_free?/?1024?/?1024,2),?'MB')????????????AS??TB_DATA_FREE,CASE?WHEN?(data_length?+?index_length)?=?0?THEN?0ELSE?ROUND(data_free/(data_length?+?index_length),2)END????????????????????????????????????????????????????????AS??TB_FRAG_RATEFROM?information_schema.TABLESWHERE?ROUND(DATA_FREE/1024/1024,2)?>=50ORDER?BY?data_free?DESC;

SELECT?TABLE_SCHEMA,TABLE_NAME,ENGINE,ROUND(((DATA_LENGTH?+?INDEX_LENGTH)?/?1024?/?1024),?2)?AS?SIZE_MB,ROUND(DATA_FREE/1024/1024,2)?AS?FREE_SIZ_MBFROM?information_schema.TABLESWHERE?DATA_FREE?>=10*1024*1024ORDER?BY?FREE_SIZ_MB?DESC;

MySQL中如何減低表的碎片

在MySQL中，可以使用OPTIMIZE TABLE、ALTER TABLE XXXX ENGINE = INNODB這兩種方法降低碎片，關于這兩者的簡單介紹如下：

OPTIMIZE TABLEtable_name

OPTIMIZE TABLE 會重組表和索引的物理存儲，減少對存儲空間使用和提升訪問表時的IO效率。對每個表所做的確切更改取決于該表使用的存儲引擎

OPTIMIZE TABLE的支持表類型：INNODB,MYISAM, ARCHIVE，NDB；它會重組表數據和索引的物理頁，對于減少所占空間和在訪問表時優化IO有效果。OPTIMIZE 操作會暫時鎖住表,而且數據量越大,耗費的時間也越長。

OPTIMIZE TABLE后，表的變化跟存儲引擎有關。

對于MyISAM， PTIMIZE TABLE的工作原理如下：

·如果表有已刪除的行或拆分行(split rows)，修復該表。

·如果未對索引頁面進行排序，對它們進行排序。

·如果表的統計信息不是最新的(并且無法通過對索引進行排序來完成修復)，更新它們。

對于InnoDB而言，PTIMIZE TABLE的工作原理如下

對于InnoDB表，OPTIMIZE TABLE映射到ALTER TABLE ... FORCE(或者這樣翻譯：在InnoDB表中等價 ALTER TABLE ... FORCE)，它重建表以更新索引統計信息并釋放聚簇索引中未使用的空間。當您在InnoDB表上運行時，它會顯示在OPTIMIZE TABLE的輸出中，如下所示：mysql>?OPTIMIZE?TABLE?foo;+----------+----------+----------+-------------------------------------------------------------------+|?Table????|?Op???????|?Msg_type?|?Msg_text??????????????????????????????????????????????????????????|+----------+----------+----------+-------------------------------------------------------------------+|?test.foo?|?optimize?|?note?????|?Table?does?not?support?optimize,?doing?recreate?+?analyze?instead?||?test.foo?|?optimize?|?status???|?OK????????????????????????????????????????????????????????????????|+----------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE對InnoDB的普通表和分區表使用online DDL，從而減少了并發DML操作的停機時間。由OPTIMIZE TABLE觸發表的重建，并在ALTER TABLE ... FORCE的掩護下完成。僅在操作的準備階段和提交階段期間短暫地進行獨占表鎖定。在準備階段，更新元數據并創建中間表。在提交階段，將提交表元數據更改。

OPTIMIZE TABLE在以下條件下使用表復制方法重建表：

o啟用old_alter_table系統變量時。

o啟用mysqld--skip-new選項時。

OPTIMIZE TABLE對于包含FULLTEXT索引的InnoDB表不支持online DDL。而是使用復制表的方法。

InnoDB使用頁面分配方法存儲數據，并且不會像傳統存儲引擎(例如MyISAM)那樣受到碎片的影響。在考慮是否運行優化時，請考慮服務器將處理的事務的工作負載

o預計會有一定程度的碎片化。InnoDB僅填充93％的頁面，為更新留出空間而無需拆分頁面。

o刪除操作可能會留下空白，使頁面填充不如預期，這可能使得優化表格變得有價值。

當行有足夠的空間時，對行的更新通常會重寫同一頁面中的數據，具體取決于數據類型和行格式。見Section 14.9.1.5,“How Compression Works for InnoDB Tables”和Section 14.11,“InnoDB Row Formats”。

高并發工作負載可能會隨著時間的推移在索引中留下空白，因為InnoDB通過其MVCC機制保留了相同數據的多個版本。見Section 14.3,“InnoDB Multi-Versioning”。

另外，對于innodb_file_per_table=1的InnoDB表，OPTIMIZE TABLE 會重組表和索引的物理存儲，將空閑空間釋放給操作系統。也就是說OPTIMIZE TABLE [tablename] 這種方式只適用于獨立表空間

關于OPTIMIZE TABLE，更多詳細細節參考https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html。感覺官方文檔相當詳細。

ALTER TABLE table_name ENGINE = Innodb;

這其實是一個NULL操作,表面上看什么也不做,實際上重新整理碎片了.當執行優化操作時,實際執行的是一個空的 ALTER 命令,但是這個命令也會起到優化的作用,它會重建整個表,刪掉未使用的空白空間.

問題1：那么是用OPTIMIZE TABLE 還是ALTER TABLE xxxx ENGINE= INNODB好呢？

其實對于InnoDB引擎，ALTER TABLE xxxx ENGINE= INNODB是執行了一個空的ALTER TABLE操作。而OPTIMIZE TABLE等價于ALTER TABLE ... FORCE。 參考上面描述，在有些情況下，OPTIMIZE TABLE 還是ALTER TABLE xxxx ENGINE= INNODB基本上是一樣的。但是在有些情況下，ALTER TABLE xxxx ENGINE= INNODB更好。例如old_alter_table系統變量沒有啟用等等。另外對于MyISAM類型表，使用ALTER TABLE xxxx ENGINE= INNODB是明顯要優于OPTIMIZE TABLE這種方法的。

問題2：ALTER TABLE xxxx ENGINE= INNODB 表上的索引碎片會整理么

ALTER TABLE ENGINE= INNODB,會重新整理在聚簇索引上的數據和索引。如果你想用實驗驗證，可以對比執行該命令前后index_length的大小。

總結

以上是生活随笔為你收集整理的mysql 回收空间_MySQL表的碎片整理和空间回收小结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。