關(guān)于MySQL中表碎片化（Table Fragmentation）產(chǎn)生的原因：

? ? ? ?簡單總結(jié)一下，MySQL Engine不同，碎片化的原因可能也有所差別。這里沒有深入理解、分析這些差別。此文僅以InnoDB引擎為主。總結(jié)如有不足或錯誤的地方，敬請指出。InnoDB表的數(shù)據(jù)存儲在頁（page）中，每個頁可以存放多條記錄。這些記錄以樹形結(jié)構(gòu)組織，這顆樹稱為B+樹索引。表中數(shù)據(jù)和輔助索引都是使用B+樹結(jié)構(gòu)。維護表中所有數(shù)據(jù)的這顆B+樹索引稱為聚簇索引，通過主鍵來組織的。聚簇索引的葉子節(jié)點包含行中所有字段的值，輔助索引的葉子節(jié)點包含索引列和主鍵列。

? ? ? ?在InnoDB中，刪除一些行，這些行只是被標記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒有真的被釋放回收。InnoDB的Purge線程會異步的來清理這些沒用的索引鍵和行。但是依然沒有把這些釋放出來的空間還給操作系統(tǒng)重新使用，因而會導(dǎo)致頁面中存在很多空洞。如果表結(jié)構(gòu)中包含動態(tài)長度字段，那么這些空洞甚至可能不能被InnoDB重新用來存新的行，因為空間空間長度不足。關(guān)于這個你可以參考博客Overview of fragmented MySQL InnoDB tables。另外，刪除數(shù)據(jù)就會導(dǎo)致頁（page）中出現(xiàn)空白空間，大量隨機的DELETE操作，必然會在數(shù)據(jù)文件中造成不連續(xù)的空白空間。而當插入數(shù)據(jù)時,這些空白空間則會被利用起來.于是造成了數(shù)據(jù)的存儲位置不連續(xù)。物理存儲順序與邏輯上的排序順序不同,這種就是數(shù)據(jù)碎片。對于大量的UPDATE，也會產(chǎn)生文件碎片化 ， Innodb的最小物理存儲分配單位是頁(page)，而UPDATE也可能導(dǎo)致頁分裂（page split），頻繁的頁分裂，頁會變得稀疏，并且被不規(guī)則的填充，所以最終數(shù)據(jù)會有碎片。

? ? ??表的數(shù)據(jù)存儲也可能碎片化。然而數(shù)據(jù)存儲的碎片化比索引更加復(fù)雜。有三種類型的數(shù)據(jù)碎片化。##下面部分內(nèi)容摘自【高性能MySQL】##

行碎片（Row fragmentation）

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

?行間碎片（Intra-row fragmentaion）

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

?剩余空間碎片（Free space fragmentation）

? ? ? ??剩余空間碎片是指數(shù)據(jù)頁中有大量的空余空間。這會導(dǎo)致服務(wù)器讀取大量不需要的數(shù)據(jù)。從而造成浪費。對于MyISAM表，這三類碎片化都有可能發(fā)生。但InnoDB不會出現(xiàn)短小的行碎片；InnoDB會移動短小的行并寫到一個片段中。InnoDb會移動短小的行并重寫到一個片段中。

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

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

以下腳本可以直接使用

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_RATE FROM information_schema.TABLES ORDER 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_RATE FROM information_schema.TABLES WHERE ROUND(DATA_FREE/1024/1024,2) >=50 ORDER 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_MB FROM information_schema.TABLES WHERE DATA_FREE >=10*1024*1024 ORDER BY FREE_SIZ_MB DESC;

MySQL中如何減低表的碎片

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

OPTIMIZE TABLE

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

OPTIMIZE TABLE的支持表類型：INNODB,MYISAM, ARCHIVE，NDB；它會重組表數(shù)據(jù)和索引的物理頁，對于減少所占空間和在訪問表時優(yōu)化IO有效果。OPTIMIZE 操作會暫時鎖住表,而且數(shù)據(jù)量越大,耗費的時間也越長。?（但是我試了一下，正常的插入和查詢都是正常的，不知道是上面原因）

mysql 5.7 以上，或者是mysql8.0的，optimize table xxx會提示：OPTIMIZE TABLE: Table does not support optimize, doing recreate + analyze instead。
建議使用：ALTER TABLE xxxx ENGINE=InnoDB;ANALYZE TABLE xxxx; 進行操作，這也是官方推薦的表碎片優(yōu)化操作。

ALTER TABLE table_name ENGINE = Innodb;

這其實是一個NULL操作,表面上看什么也不做,實際上重新整理碎片了.當執(zhí)行優(yōu)化操作時,實際執(zhí)行的是一個空的 ALTER 命令,但是這個命令也會起到優(yōu)化的作用,它會重建整個表,刪掉未使用的空白空間.Running?ALTER TABLE?tbl_name?ENGINE=INNODB on an existing InnoDB table performs a “null”?ALTER TABLE?operation, which can be used to defragment an InnoDB table, as described in?Section 15.11.4, “Defragmenting a Table”. Running?ALTER TABLE?tbl_name?FORCE on an InnoDB table performs the same function.

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

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

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

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

其它工具

????網(wǎng)友建議使用pt工具或者gh-ost降低表的碎片化?

參考資料：

【高性能MySQL】

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-file-defragmenting.html

https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/?

https://yq.aliyun.com/articles/41166

http://mysql.taobao.org/monthly/2015/08/05/

http://www.cnblogs.com/kerrycode/

、

?

總結(jié)

以上是生活随笔為你收集整理的MySQL表碎片化（Table Fragmentation）以及处理的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。