1. 前言

周一在對線上表進行數(shù)據(jù)清除時，發(fā)現(xiàn)一個問題，我要清除的單表大概有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ù)剩余。感覺肯定是有對應的一些索引數(shù)據(jù)沒有被刪除掉，仍然保存在表中，導致表空間仍然很大。

后面了解到這個是MySQL的數(shù)據(jù)碎片，加上使用的是MySQL的InnoDB引擎，導致即使我們刪除數(shù)據(jù)，表空間也不會縮小，需要通過一些額外的表優(yōu)化手段來清除這些數(shù)據(jù)碎片，因為用的是InnoDB引擎，所以就看了下關于InnoDB引擎表碎片相關的知識。

2. InnoDB表碎片

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

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

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

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

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

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

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

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

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

• 行碎片(Row fragmetation)

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

• 行間碎片(Intra-row fragmetaion)

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

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

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

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

3. 清除表碎片

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

3.1 查找碎片化嚴重的表

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

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，這種方式會重組表和索引的物理存儲，減少對存儲空間的使用和提升訪問表的I/O效率。OPTIMIZE操作會暫時鎖住表，數(shù)據(jù)量越大，則耗時越長。對每個表所做的確切更改取決于該表使用的存儲引擎。

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

對剛剛的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
-- 實際采用的事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對常規(guī)表和分區(qū)表使用在線DDL方式，從而減少并發(fā)DML操作的停機時間。由OPTIMIZE TABLE觸發(fā)的表重建會在原地完成，在操作的準備階段和提交階段，只短暫地采用排它表鎖，在準備階段，更新元數(shù)據(jù)并創(chuàng)建中間表，在提交階段，提交表元數(shù)據(jù)更改。

在線DDL(MySQL 8.0+)

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

• 在繁忙的生產(chǎn)環(huán)境中提高響應能力和可用性，因為讓表不可用幾分鐘或幾小時是不切實際的。
• 對于就地操作，可以使用LOCK子句在 DDL 操作期間調(diào)整性能和并發(fā)之間的平衡。
• 與表復制方法相比，磁盤空間使用量和 I/O 開銷更少。

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

• 當old_alter_table系統(tǒng)變量啟用時
• 當服務器使用—skip-new選項啟動時

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

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

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

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

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

4. 小結(jié)

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

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

表碎片的清除手段：

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

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

5. 參考

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

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