腳本之家服務器常用軟件

快捷導航

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

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

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

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

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

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

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

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

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

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ù)存儲也可能碎片化。然而數(shù)據(jù)存儲的碎片化比索引更加復雜。有三種類型的數(shù)據(jù)碎片化。##下面部分內容摘自【高性能MySQL】##

行碎片（Row fragmentation）

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

行間碎片（Intra-row fragmentaion）

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

剩余空間碎片（Free space fragmentation）

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

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

官方文檔14.15.4 Defragmenting a Table關于降低表的碎片化介紹如下（非常簡潔，MySQL官方文檔往往簡潔，信息量大，但是沒有詳細介紹）：

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%.

從二級索引中隨機插入或刪除可能會導致索引碎片化。碎片意味著磁盤上索引頁的物理排序不接近頁面上記錄的索引排序，或者64頁塊中有許多未使用的頁面被分配給索引。

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

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

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

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

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

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獲取表的碎片化信息。

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

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這兩種方法降低碎片，關于這兩者的簡單介紹如下：

OPTIMIZE TABLE

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

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

OPTIMIZE TABLE后，表的變化跟存儲引擎有關。

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

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

英文原文如下：

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表中等價 ALTER TABLE ... FORCE），它重建表以更新索引統(tǒng)計信息并釋放聚簇索引中未使用的空間。當您在InnoDB表上運行時，它會顯示在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操作的停機時間。由OPTIMIZE TABLE觸發(fā)表的重建，并在ALTER TABLE ... FORCE的掩護下完成。僅在操作的準備階段和提交階段期間短暫地進行獨占表鎖定。在準備階段，更新元數(shù)據(jù)并創(chuàng)建中間表。在提交階段，將提交表元數(shù)據(jù)更改。

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

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

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

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

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

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

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

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

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

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這種方法的。