MySQL表的碎片整理和空間回收的方法

更新時(shí)間：2021年09月17日 10:17:32 作者：瀟湘隱者

本文主要介紹了MySQL表的碎片整理和空間回收的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

MySQL表碎片化（Table Fragmentation）的原因

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

InnoDB表的數(shù)據(jù)存儲(chǔ)在頁(yè)（page）中，每個(gè)頁(yè)可以存放多條記錄。這些記錄以樹(shù)形結(jié)構(gòu)組織，這顆樹(shù)稱為B+樹(shù)索引。表中數(shù)據(jù)和輔助索引都是使用B+樹(shù)結(jié)構(gòu)。維護(hù)表中所有數(shù)據(jù)的這顆B+樹(shù)索引稱為聚簇索引，通過(guò)主鍵來(lái)組織的。聚簇索引的葉子節(jié)點(diǎn)包含行中所有字段的值，輔助索引的葉子節(jié)點(diǎn)包含索引列和主鍵列。

在InnoDB中，刪除一些行，這些行只是被標(biāo)記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒(méi)有真的被釋放回收。InnoDB的Purge線程會(huì)異步的來(lái)清理這些沒(méi)用的索引鍵和行。但是依然沒(méi)有把這些釋放出來(lái)的空間還給操作系統(tǒng)重新使用，因而會(huì)導(dǎo)致頁(yè)面中存在很多空洞。如果表結(jié)構(gòu)中包含動(dòng)態(tài)長(zhǎng)度字段，那么這些空洞甚至可能不能被InnoDB重新用來(lái)存新的行，因?yàn)榭臻g空間長(zhǎng)度不足。關(guān)于這個(gè)你可以參考博客Overview of fragmented MySQL InnoDB tables。

另外，刪除數(shù)據(jù)就會(huì)導(dǎo)致頁(yè)（page）中出現(xiàn)空白空間，大量隨機(jī)的DELETE操作，必然會(huì)在數(shù)據(jù)文件中造成不連續(xù)的空白空間。而當(dāng)插入數(shù)據(jù)時(shí),這些空白空間則會(huì)被利用起來(lái).于是造成了數(shù)據(jù)的存儲(chǔ)位置不連續(xù)。物理存儲(chǔ)順序與邏輯上的排序順序不同,這種就是數(shù)據(jù)碎片。

對(duì)于大量的UPDATE，也會(huì)產(chǎn)生文件碎片化， Innodb的最小物理存儲(chǔ)分配單位是頁(yè)(page)，而UPDATE也可能導(dǎo)致頁(yè)分裂（page split），頻繁的頁(yè)分裂，頁(yè)會(huì)變得稀疏，并且被不規(guī)則的填充，所以最終數(shù)據(jù)會(huì)有碎片。

First at all you must understand that Mysql tables get fragmented when a row is updated, so it's a normal situation. When a table is created, lets say imported using a dump with data, all rows are stored with no fragmentation in many fixed size pages. When you update a variable length row, the page containing this row is divided in two or more pages to store the changes, and these new two (or more) pages contains blank spaces filling the unused space.

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

行碎片（Row fragmentation）

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

行間碎片（Intra-row fragmentaion）

行間碎片是指邏輯上順序的頁(yè)，或者行在磁盤(pán)上不是順序存儲(chǔ)的。行間碎片對(duì)諸如全表掃描和聚簇索引掃描之類(lèi)的操作有很大的影響，因?yàn)檫@些操作原本能夠從磁盤(pán)上順序存儲(chǔ)的數(shù)據(jù)中獲益。

剩余空間碎片（Free space fragmentation）

剩余空間碎片是指數(shù)據(jù)頁(yè)中有大量的空余空間。這會(huì)導(dǎo)致服務(wù)器讀取大量不需要的數(shù)據(jù)。從而造成浪費(fèi)。

對(duì)于MyISAM表，這三類(lèi)碎片化都有可能發(fā)生。但I(xiàn)nnoDB不會(huì)出現(xiàn)短小的行碎片；InnoDB會(huì)移動(dòng)短小的行并寫(xiě)到一個(gè)片段中。InnoDb會(huì)移動(dòng)短小的行并重寫(xiě)到一個(gè)片段中。

官方文檔14.15.4 Defragmenting a Table關(guān)于降低表的碎片化介紹如下（非常簡(jiǎn)潔，MySQL官方文檔往往簡(jiǎn)潔，信息量大，但是沒(méi)有詳細(xì)介紹）：

Random insertions into or deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

One symptom of fragmentation is that a table takes more space than it “should” take. How much that is exactly, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%.

從二級(jí)索引中隨機(jī)插入或刪除可能會(huì)導(dǎo)致索引碎片化。碎片意味著磁盤(pán)上索引頁(yè)的物理排序不接近頁(yè)面上記錄的索引排序，或者64頁(yè)塊中有許多未使用的頁(yè)面被分配給索引。

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

MySQL中如何找出碎片化嚴(yán)重的表

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

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

第一個(gè)xxx:表所在的數(shù)據(jù)庫(kù)名稱，第二個(gè)xxx:要查詢的表名。這個(gè)方法其實(shí)不太實(shí)用。例如，只能單個(gè)表的查詢碎片化情況（難道一個(gè)數(shù)據(jù)庫(kù)要一個(gè)個(gè)表去試？），不能查詢某個(gè)數(shù)據(jù)庫(kù)或整個(gè)實(shí)例下所有表的碎片化等等。這里僅僅作為一個(gè)參考方法而已。

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;

如下截圖所示，如果沒(méi)有DML操作，Data_free的大小是0

然后我們?cè)跀?shù)據(jù)庫(kù)上刪除掉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獲取表的碎片化信息。

如下所示，這個(gè)是我整理的一個(gè)查詢表碎片化的經(jīng)典腳本。你可以在上面做很多衍生：例如，查詢某個(gè)數(shù)據(jù)庫(kù)的表碎片化情況。或者空閑空間超過(guò)50M大小的表。這個(gè)可以根據(jù)自己的需求設(shè)定查詢條件。在此略過(guò)。

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 0
            ELSE  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 0
             ELSE 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 0
            ELSE  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 0
             ELSE 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)于這兩者的簡(jiǎn)單介紹如下：

OPTIMIZE TABLE

OPTIMIZE TABLE 會(huì)重組表和索引的物理存儲(chǔ)，減少對(duì)存儲(chǔ)空間使用和提升訪問(wèn)表時(shí)的IO效率。對(duì)每個(gè)表所做的確切更改取決于該表使用的存儲(chǔ)引擎

OPTIMIZE TABLE的支持表類(lèi)型：INNODB,MYISAM, ARCHIVE，NDB；它會(huì)重組表數(shù)據(jù)和索引的物理頁(yè)，對(duì)于減少所占空間和在訪問(wèn)表時(shí)優(yōu)化IO有效果。OPTIMIZE 操作會(huì)暫時(shí)鎖住表,而且數(shù)據(jù)量越大,耗費(fèi)的時(shí)間也越長(zhǎng)。

OPTIMIZE TABLE后，表的變化跟存儲(chǔ)引擎有關(guān)。

對(duì)于MyISAM， PTIMIZE TABLE 的工作原理如下：

如果表有已刪除的行或拆分行（split rows），修復(fù)該表。
如果未對(duì)索引頁(yè)面進(jìn)行排序，對(duì)它們進(jìn)行排序。
如果表的統(tǒng)計(jì)信息不是最新的（并且無(wú)法通過(guò)對(duì)索引進(jìn)行排序來(lái)完成修復(fù)），更新它們。

英文原文如下：

For MyISAM tables, OPTIMIZE TABLE works as follows:

1. If the table has deleted or split rows, repair the table.

2. If the index pages are not sorted, sort them.

3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

對(duì)于InnoDB而言，PTIMIZE TABLE 的工作原理如下

對(duì)于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE ... FORCE（或者這樣翻譯：在InnoDB表中等價(jià) ALTER TABLE ... FORCE），它重建表以更新索引統(tǒng)計(jì)信息并釋放聚簇索引中未使用的空間。當(dāng)您在InnoDB表上運(yùn)行時(shí)，它會(huì)顯示在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對(duì)InnoDB的普通表和分區(qū)表使用online DDL，從而減少了并發(fā)DML操作的停機(jī)時(shí)間。由OPTIMIZE TABLE觸發(fā)表的重建，并在ALTER TABLE ... FORCE的掩護(hù)下完成。僅在操作的準(zhǔn)備階段和提交階段期間短暫地進(jìn)行獨(dú)占表鎖定。在準(zhǔn)備階段，更新元數(shù)據(jù)并創(chuàng)建中間表。在提交階段，將提交表元數(shù)據(jù)更改。

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

啟用old_alter_table系統(tǒng)變量時(shí)。
啟用mysqld --skip-new 選項(xiàng)時(shí)。

OPTIMIZE TABLE 對(duì)于包含F(xiàn)ULLTEXT索引的InnoDB表不支持online DDL。而是使用復(fù)制表的方法。

InnoDB使用頁(yè)面分配方法存儲(chǔ)數(shù)據(jù)，并且不會(huì)像傳統(tǒng)存儲(chǔ)引擎（例如MyISAM）那樣受到碎片的影響。在考慮是否運(yùn)行優(yōu)化時(shí)，請(qǐng)考慮服務(wù)器將處理的事務(wù)的工作負(fù)載：

預(yù)計(jì)會(huì)有一定程度的碎片化。 InnoDB僅填充93％的頁(yè)面，為更新留出空間而無(wú)需拆分頁(yè)面。
刪除操作可能會(huì)留下空白，使頁(yè)面填充不如預(yù)期，這可能使得優(yōu)化表格變得有價(jià)值。

當(dāng)行有足夠的空間時(shí)，對(duì)行的更新通常會(huì)重寫(xiě)同一頁(yè)面中的數(shù)據(jù)，具體取決于數(shù)據(jù)類(lèi)型和行格式。見(jiàn) Section 14.9.1.5, “How Compression Works for InnoDB Tables” 和 Section 14.11, “InnoDB Row Formats” 。

高并發(fā)工作負(fù)載可能會(huì)隨著時(shí)間的推移在索引中留下空白，因?yàn)镮nnoDB通過(guò)其MVCC機(jī)制保留了相同數(shù)據(jù)的多個(gè)版本。見(jiàn) Section 14.3, “InnoDB Multi-Versioning” 。

另外，對(duì)于innodb_file_per_table=1的InnoDB表，OPTIMIZE TABLE 會(huì)重組表和索引的物理存儲(chǔ)，將空閑空間釋放給操作系統(tǒng)。也就是說(shuō)OPTIMIZE TABLE [tablename] 這種方式只適用于獨(dú)立表空間

關(guān)于OPTIMIZE TABLE，更多詳細(xì)細(xì)節(jié)參考https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html。感覺(jué)官方文檔相當(dāng)詳細(xì)。

ALTER TABLE table_name ENGINE = Innodb;

這其實(shí)是一個(gè)NULL操作,表面上看什么也不做,實(shí)際上重新整理碎片了.當(dāng)執(zhí)行優(yōu)化操作時(shí),實(shí)際執(zhí)行的是一個(gè)空的 ALTER 命令,但是這個(gè)命令也會(huì)起到優(yōu)化的作用,它會(huì)重建整個(gè)表,刪掉未使用的空白空間.

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.

問(wèn)題1：那么是用OPTIMIZE TABLE 還是ALTER TABLE xxxx ENGINE= INNODB好呢？

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