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

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

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

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

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

對于大量的UPDATE，也會(huì)產(chǎn)生文件碎片化 ， Innodb的最小物理存儲(chǔ)分配單位是頁(page)，而UPDATE也可能導(dǎo)致頁分裂（page split），頻繁的頁分裂，頁會(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ù)雜。有三種類型的數(shù)據(jù)碎片化。##下面部分內(nèi)容摘自【高性能MySQL】##

行碎片（Row fragmentation）

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

行間碎片（Intra-row fragmentaion）

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

剩余空間碎片（Free space fragmentation）

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

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

官方文檔14.15.4 Defragmenting a Table關(guān)于降低表的碎片化介紹如下（非常簡潔，MySQL官方文檔往往簡潔，信息量大，但是沒有詳細(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)致索引碎片化。碎片意味著磁盤上索引頁的物理排序不接近頁面上記錄的索引排序，或者64頁塊中有許多未使用的頁面被分配給索引。

碎片化的一個(gè)癥狀是表格占用的空間比“應(yīng)該”占用的空間多。多少確切地說，很難確定。所有 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ù)庫名稱，第二個(gè)xxx:要查詢的表名。這個(gè)方法其實(shí)不太實(shí)用。例如，只能單個(gè)表的查詢碎片化情況（難道一個(gè)數(shù)據(jù)庫要一個(gè)個(gè)表去試？），不能查詢某個(gè)數(shù)據(jù)庫或整個(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;

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

然后我們在數(shù)據(jù)庫上刪除掉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ù)庫的表碎片化情況?；蛘呖臻e空間超過50M大小的表。這個(gè)可以根據(jù)自己的需求設(shè)定查詢條件。在此略過。

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) 
     ENDAS  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 會(huì)重組表和索引的物理存儲(chǔ)，減少對存儲(chǔ)空間使用和提升訪問表時(shí)的IO效率。對每個(gè)表所做的確切更改取決于該表使用的存儲(chǔ)引擎

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

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

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

• 如果表有已刪除的行或拆分行（split rows），修復(fù)該表。
• 如果未對索引頁面進(jìn)行排序，對它們進(jìn)行排序。
• 如果表的統(tǒng)計(jì)信息不是最新的（并且無法通過對索引進(jìn)行排序來完成修復(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.

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

對于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對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 對于包含F(xiàn)ULLTEXT索引的InnoDB表不支持online DDL。而是使用復(fù)制表的方法。

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

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

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

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

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

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

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

其實(shí)對于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)變量沒有啟用等等。另外對于MyISAM類型表，使用ALTER TABLE xxxx ENGINE= INNODB是明顯要優(yōu)于OPTIMIZE TABLE這種方法的。

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

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

其它工具

網(wǎng)友建議使用pt工具或者gh-ost降低表的碎片化，個(gè)人暫時(shí)還沒有使用過這類工具，估計(jì)也是封裝了上面兩個(gè)命令。此處不做展開介紹。

參考資料：

【高性能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/

到此這篇關(guān)于MySQL表的碎片整理和空間回收的方法的文章就介紹到這了,更多相關(guān)MySQL表的碎片整理和空間回收 內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

香港快速服務(wù)器

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。