1. 前言

周一在對(duì)線上表進(jìn)行數(shù)據(jù)清除時(shí)，發(fā)現(xiàn)一個(gè)問題，我要清除的單表大概有2500w條數(shù)據(jù)，清除數(shù)據(jù)大概在1300w條左右，清除之前通過查詢語句獲取到的表大小約為7000MB。

SELECT table_name as Table, round(((data_length + index_length) / 1024 / 1024), 5) as Size(MB) FROM information_schema.tables WHERE table_schema ='db_name' AND table_name = 'table_name'\G

通過腳本清除之后，再通過查詢語句獲取表大小，發(fā)現(xiàn)表仍然有6000MB的數(shù)據(jù)剩余。感覺肯定是有對(duì)應(yīng)的一些索引數(shù)據(jù)沒有被刪除掉，仍然保存在表中，導(dǎo)致表空間仍然很大。

后面了解到這個(gè)是MySQL的數(shù)據(jù)碎片，加上使用的是MySQL的InnoDB引擎，導(dǎo)致即使我們刪除數(shù)據(jù)，表空間也不會(huì)縮小，需要通過一些額外的表優(yōu)化手段來清除這些數(shù)據(jù)碎片，因?yàn)橛玫氖荌nnoDB引擎，所以就看了下關(guān)于InnoDB引擎表碎片相關(guān)的知識(shí)。

2. InnoDB表碎片

InnoDB表的數(shù)據(jù)存儲(chǔ)在頁(page)中，每個(gè)頁可以存放多條記錄，InnoDB默認(rèn)使用B+樹作為索引結(jié)構(gòu)，表中的數(shù)據(jù)和輔助索引都是使用B+樹結(jié)構(gòu)，每個(gè)InnoDB表中都有一個(gè)稱為聚簇索引的特殊索引，用于存儲(chǔ)行數(shù)據(jù)。通常聚簇索引與主鍵索引同義。

通過聚簇索引訪問行的速度很快，以為索引搜索會(huì)直接找到包含行數(shù)據(jù)的頁面，如果表很大，與使用與索引記錄不同的頁面存儲(chǔ)行數(shù)據(jù)的存儲(chǔ)組織相比，聚簇所以架構(gòu)通?？梢怨?jié)省磁盤I/O操作。

除了聚簇索引之外，還有一個(gè)二級(jí)索引，我們也叫做輔助索引。在InnoDB中，輔助索引中的每個(gè)記錄都包含行的主鍵列以及為二級(jí)索引指定的列，InnoDB使用此主鍵值在聚簇索引中搜索行。如果主鍵很長，則輔助索引將使用更多的空間，因此使用較短的主鍵是比較好的。

對(duì)于InnoDB而言，隨機(jī)插入或者刪除輔助索引可能會(huì)導(dǎo)致索引碎片化，碎片化意味著磁盤上索引頁的物理順序與頁面上記錄的索引順序不接近，或者分配給索引的64頁塊中有許多未使用的頁面。

碎片的一個(gè)癥狀是表占用的空間比它“應(yīng)該”占用的空間要多，**具體會(huì)多多少很難確定。所有InnoDB數(shù)據(jù)和索引都存儲(chǔ)在B樹種，它們的填充因子可能從50%到100%不等。碎片的另一個(gè)癥狀是表掃描花費(fèi)的時(shí)間比它“應(yīng)該”花費(fèi)的時(shí)間要多。

在InnoDB中，刪除一些行，InnoDB并不會(huì)真正的刪除它們，只是會(huì)將這些行標(biāo)記為“已刪除”(同時(shí)也稱為可復(fù)用的位置，即后續(xù)如果有對(duì)應(yīng)的主鍵數(shù)據(jù)插在這段區(qū)域，會(huì)復(fù)用位置)，而不是真的從索引中物理刪除，因此存儲(chǔ)空間也沒有真的被釋放。

刪除數(shù)據(jù)會(huì)導(dǎo)致頁中出現(xiàn)空白空間，大量隨機(jī)的DELETE操作會(huì)在數(shù)據(jù)文件中造成不連續(xù)的空白空間，當(dāng)插入數(shù)據(jù)的時(shí)候，這些可復(fù)用的空白空間會(huì)被利用起來，但這會(huì)造成數(shù)據(jù)存儲(chǔ)位置的不連續(xù)，即物理存儲(chǔ)順序與邏輯上的排序順序不同，于是就產(chǎn)生了表數(shù)據(jù)碎片。

對(duì)表進(jìn)行大量的UPDATE操作也可能會(huì)導(dǎo)致頁分裂，頻繁地頁分裂，頁會(huì)變得稀疏，并且被不規(guī)則的填充，繼而產(chǎn)生表碎片，

另外，表的數(shù)據(jù)存儲(chǔ)也可能會(huì)碎片化，數(shù)據(jù)存儲(chǔ)的碎片化比索引更加復(fù)雜，主要有三種類型的數(shù)據(jù)碎片：

• 行碎片(Row fragmetation)

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

• 行間碎片(Intra-row fragmetaion)

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

• 剩余空間碎片(Free space fragmenation)

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

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

3. 清除表碎片

刪除了數(shù)據(jù)而空間沒有得到釋放，于是我們需要采取一些手段來清除刪除的數(shù)據(jù)留下的表碎片，從而釋放存儲(chǔ)空間，同時(shí)提升查詢效率。

3.1 查找碎片化嚴(yán)重的表

對(duì)于表中是否含有碎片，可以通過下面的命令直接查看表信息。

show table status from db_name like '%table_nam%'\G

mysql> show table status like '%user_tab%'\G
*************************** 1. row ***************************
           Name: user_tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4   -- 可以看到有4行數(shù)據(jù)
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0  -- 可釋放的空間為0
 Auto_increment: 5
    Create_time: 2023-08-12 16:58:07
    Update_time: 2024-06-23 16:38:08
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
-- 插入一些數(shù)據(jù)，然后看Data Free，發(fā)現(xiàn)有許多可釋放的數(shù)據(jù)       
mysql> show table status like 'user_tab'\G
*************************** 1. row ***************************
           Name: user_tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 351
 Avg_row_length: 10502
    Data_length: 3686400
Max_data_length: 0
   Index_length: 1589248
      Data_free: 9437184
 Auto_increment: 41282
    Create_time: 2023-08-12 16:58:07
    Update_time: 2024-06-23 17:00:29
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

3.2 清除碎片

清理碎片主要有兩種方式：

第一種是優(yōu)化表，即OPTIMIZE TABLE，這種方式會(huì)重組表和索引的物理存儲(chǔ)，減少對(duì)存儲(chǔ)空間的使用和提升訪問表的I/O效率。OPTIMIZE操作會(huì)暫時(shí)鎖住表，數(shù)據(jù)量越大，則耗時(shí)越長。對(duì)每個(gè)表所做的確切更改取決于該表使用的存儲(chǔ)引擎。

對(duì)于InnoDB表，OPTIMIZE TABLE會(huì)映射到ALTER TABLE … FORCE, 這將重建表以更新索引統(tǒng)計(jì)信息并釋放聚簇索引中未使用的空間。

對(duì)剛剛的user_tab采用OPTIMIZE的命令，可以看到空間被釋放了。

mysql> optimize table user_tab\G
*************************** 1. row ***************************
   Table: duanxi.user_tab
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
-- 實(shí)際采用的事recreate + analyze方式
*************************** 2. row ***************************
   Table: duanxi.user_tab
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.04 sec)

mysql> show table status like 'user_tab'\G
*************************** 1. row ***************************
           Name: user_tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0   -- 優(yōu)化后Data Free變?yōu)?了
 Auto_increment: 41282
    Create_time: 2024-06-23 17:02:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

InnoDB的OPTIMIZE TABLE對(duì)常規(guī)表和分區(qū)表使用在線DDL方式，從而減少并發(fā)DML操作的停機(jī)時(shí)間。由OPTIMIZE TABLE觸發(fā)的表重建會(huì)在原地完成，在操作的準(zhǔn)備階段和提交階段，只短暫地采用排它表鎖，在準(zhǔn)備階段，更新元數(shù)據(jù)并創(chuàng)建中間表，在提交階段，提交表元數(shù)據(jù)更改。

在線DDL(MySQL 8.0+)

在線 DDL 功能支持即時(shí)、就地表更改和并發(fā) DML。此功能的優(yōu)點(diǎn)包括：

• 在繁忙的生產(chǎn)環(huán)境中提高響應(yīng)能力和可用性，因?yàn)樽尡聿豢捎脦追昼娀驇仔r(shí)是不切實(shí)際的。
• 對(duì)于就地操作，可以使用LOCK子句在 DDL 操作期間調(diào)整性能和并發(fā)之間的平衡。
• 與表復(fù)制方法相比，磁盤空間使用量和 I/O 開銷更少。

OPTIMIZE TABLE在以下情況下使用表賦值方法重建表

• 當(dāng)old_alter_table系統(tǒng)變量啟用時(shí)
• 當(dāng)服務(wù)器使用—skip-new選項(xiàng)啟動(dòng)時(shí)

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

• 預(yù)計(jì)會(huì)出現(xiàn)一定程度的碎片，InnoDB僅填充93%的頁面，以便留出更新空間，而無需拆分頁面
• 刪除操作可能會(huì)留下空隙，導(dǎo)致頁面填充不足，這可能會(huì)使優(yōu)化表變得有價(jià)值
• 當(dāng)有足夠的空間時(shí)，對(duì)行的更新通常會(huì)重寫同一頁內(nèi)的數(shù)據(jù)，具體取決于數(shù)據(jù)類型和行格式。
• 高并發(fā)工作負(fù)載可能會(huì)隨著時(shí)間的推移在索引中留下空白，因?yàn)镮nnoDB通過其MVCC機(jī)制保留了同一數(shù)據(jù)的多個(gè)版本。

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

• 如果表有刪除或拆分行，則修復(fù)該表。
• 如果索引頁未排序，則對(duì)其進(jìn)行排序。
• 如果表的統(tǒng)計(jì)信息不是最新的（并且無法通過對(duì)索引進(jìn)行排序來完成修復(fù)），則更新它們。

第二種操作則是使用ALTER TABLE table_name ENGINE= InnoDB; 的方式，此方式看起來沒有執(zhí)行什么操作，實(shí)際上重新整理碎片了，當(dāng)執(zhí)行這個(gè)優(yōu)化操作時(shí)，InnoDB會(huì)重建整個(gè)表并釋放聚簇索引中未使用的空間。

4. 小結(jié)

因?yàn)閯h除表數(shù)據(jù)發(fā)現(xiàn)表使用空間未被釋放，繼而發(fā)現(xiàn)有表碎片問題，查找一些資料去了解表碎片的產(chǎn)生以及表碎片的處理，最終讓自己學(xué)習(xí)到了關(guān)于InnoDB表碎片相關(guān)的知識(shí)。

表碎片的產(chǎn)生主要是InnoDB刪除非物理刪除，而是標(biāo)記”刪除”，且這些被“刪除”的空間后續(xù)還可復(fù)用，進(jìn)而導(dǎo)致磁盤上索引頁的物理順序與頁面上記錄的索引順序不接近，引發(fā)表的碎片化。同時(shí)表的大量更新、表的數(shù)據(jù)存儲(chǔ)頁都會(huì)產(chǎn)生不同的表碎片。

表碎片的清除手段：

• OPTIMIZE TABLE table_name;
• ALTER TABLE table_name ENGINE = InnoDB;

需要注意的是，無論我們采用哪種手段清除表碎片，都會(huì)有鎖表的時(shí)間，我們需要根據(jù)自己服務(wù)器要處理的事務(wù)的工作負(fù)載分析，研判這種鎖表時(shí)間對(duì)于業(yè)務(wù)是否接受，如果可以接受則可以對(duì)表碎片進(jìn)行優(yōu)化，如果不能接受，則無需進(jìn)行優(yōu)化，等待后續(xù)再進(jìn)行優(yōu)化。(思考再三，我選擇放棄優(yōu)化，讓碎片繼續(xù)留在表中)

5. 參考

• Defragmenting a Table
• Overview of fragmented MySQL InnoDB tables
• OPTIMIZE TABLE Statement

到此這篇關(guān)于MySQL之表碎片化的問題解決的文章就介紹到這了,更多相關(guān)MySQL 表碎片化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論