[[374540]]

本文轉(zhuǎn)載自微信公眾號「Linux開發(fā)那些事兒」，作者 LinuxThings  。轉(zhuǎn)載本文請聯(lián)系Linux開發(fā)那些事兒公眾號。

不知道大家有沒有遇到這樣的一種情況，線上業(yè)務(wù)在MySQL表上做增刪改查操作，隨著時間的推移，表里面的數(shù)據(jù)越來越多，表數(shù)據(jù)文件越來越大，數(shù)據(jù)庫占用的空間自然也逐漸增長

為了縮小磁盤上表數(shù)據(jù)文件占用的空間，我們在最大的一張業(yè)務(wù)表中用delete命令刪除了一半兒的舊數(shù)據(jù)，刪除之后，磁盤上表數(shù)據(jù)文件并沒有縮小，即使刪除整張表的數(shù)據(jù)，文件依然沒有變小，這是為什么呢?

本文將詳細(xì)的分析上述問題，并給出正確回收表空間的方法

前置說明

目前大部分MySQL數(shù)據(jù)庫都是用的 InnoDB 引擎，所以如無特殊說明，文中的實(shí)例都是基于InnoDB引擎的

在MySQL配置中有個配置項(xiàng)叫 innodb_file_per_table 將它設(shè)置為1之后， 每個表的數(shù)據(jù)會單獨(dú)存儲在一個以 .ibd 為后綴的文件中

如果 innodb_file_per_table 沒有開啟的話， 表的數(shù)據(jù)是存儲在系統(tǒng)的共享表空間，這樣即使刪除了表，共享表空間也不會釋放這部分空間

所以，通常情況下，都是將 innodb_file_per_table 選項(xiàng)設(shè)置為 1, 同時為了能直觀的看到表數(shù)據(jù)文件的大小變化，文中的實(shí)例也都是基于開啟了 此選項(xiàng)來說明的

問題重現(xiàn)

新建一張表ta，表的結(jié)構(gòu)如下

mysql> show create table ta\G 
*************************** 1. row *************************** 
       Table: ta 
Create Table: CREATE TABLE `ta` ( 
  `id` int(11) NOT NULL, 
  `ia` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

使用下面的存儲過程，向 ta 中批量插入數(shù)據(jù)

delimiter // 
create procedure multinsert(in beg int,in cnt int) 
begin 
    declare icnt int default 0; 
    declare tmp int default 0; 
    while icnt < cnt do 
        set icnt = icnt + 1; 
        set tmp = beg + icnt; 
        insert into ta(id,ia) values(tmp,tmp); 
    end while; 
end// 
 
delimiter ; 

在MySQL控制臺執(zhí)行 call multinsert(0,100000) 命令，往 ta表插入10萬條數(shù)據(jù)

mysql> call multinsert(0,100000); 
mysql> select count(*) from ta; 
+----------+ 
| count(*) | 
+----------+ 
|   100000 | 
+----------+ 
1 row in set (0.02 sec) 

查看磁盤上ta表的數(shù)據(jù)文件 ta.ibd 的大小

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ 
[root@ecs-centos-7 test]# ls -l ta.ibd  
-rw-r----- 1 mysql mysql 11534336 1月   3 23:14 ta.ibd 

從上面的結(jié)果可以知道，ta表插入10萬條數(shù)據(jù)之后，ta.ibd 大小為 11534336 字節(jié)( 大約 11M )

現(xiàn)在我們使用 delete 命令刪除一半兒表數(shù)據(jù)( 5萬行記錄 )

mysql> delete from ta where id between 1 and 50000; 
Query OK, 10000 rows affected (0.03 sec) 
mysql> select count(*) from ta; 
+----------+ 
| count(*) | 
+----------+ 
|    50000 | 
+----------+ 
1 row in set (0.02 sec) 

刪除操作完成之后，再次查看磁盤上 ta.ibd 的大小

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ 
[root@ecs-centos-7 test]# ls -l ta.ibd  
-rw-r----- 1 mysql mysql 11534336 1月   3 23:14 ta.ibd 

從上面的結(jié)果可以知道，ta表刪除了一半兒，也就是5萬行數(shù)據(jù)之后，ta.ibd的大小是 11534336 字節(jié)( 約11M )

也就是說 ta表刪除數(shù)據(jù)前后，磁盤上表數(shù)據(jù)文件并沒有縮小

要弄明白數(shù)據(jù)文件為什么沒有縮小，就需要深入了解刪除數(shù)據(jù)的原理

刪除數(shù)據(jù)原理

我們都知道，InnoDB里的數(shù)據(jù)都是用B+樹組織的，關(guān)于B+樹的知識請參考 理解B+樹

圖(1)

上面是InnoDB的索引示意圖，其中用虛線框起來的節(jié)點(diǎn)是屬于Page1數(shù)據(jù)頁，葉子節(jié)點(diǎn)存儲的是索引對應(yīng)的數(shù)據(jù)，它們按照索引從小到大的順序組成了一個有序數(shù)組

假如我們要刪除Page1頁中索引key值為 13 的數(shù)據(jù)，也即上圖中紅色部分

InnoDB引擎會把索引key值為13的節(jié)點(diǎn)標(biāo)記為已刪除，它并不會回收節(jié)點(diǎn)真實(shí)的物理空間，只是將它標(biāo)記為已刪除的節(jié)點(diǎn)，后續(xù)是可以復(fù)用的，所以，刪除表記錄，磁盤上數(shù)據(jù)文件不會縮小

你可能會說，上面只是刪除了Page1頁中一個節(jié)點(diǎn)的數(shù)據(jù)，那如果把Page1頁中節(jié)點(diǎn)數(shù)據(jù)全部刪除了，應(yīng)該會回收Page1頁的空間吧?

答案是，不會回收

當(dāng)Page1頁數(shù)據(jù)全部刪除了，整個數(shù)據(jù)頁都會被標(biāo)記為已刪除，并且整個數(shù)據(jù)頁都可以復(fù)用，所以，這種情況下，磁盤上的數(shù)據(jù)文件仍然不會縮小

數(shù)據(jù)的復(fù)用

數(shù)據(jù)的復(fù)用涉及到數(shù)據(jù)節(jié)點(diǎn)的插入、刪除、轉(zhuǎn)移以及數(shù)據(jù)頁的合并等操作，具體的操作流程相關(guān)的細(xì)節(jié)請參考 理解B+樹，這里就不再重復(fù)說明了

數(shù)據(jù)節(jié)點(diǎn)的復(fù)用

在上面 圖(1) 中，當(dāng)刪除了索引key值為 13 的節(jié)點(diǎn)后，此節(jié)點(diǎn)就被標(biāo)記為可復(fù)用的

如果之后又插入了一條索引key值在 7 到 18 之間的記錄時，就會復(fù)用原來索引key值為13的數(shù)據(jù)節(jié)點(diǎn)

但是如果之后插入的記錄的索引key值不在 7 到 18 之間時，可能就無法復(fù)用原來索引key值為13的數(shù)據(jù)節(jié)點(diǎn)

也就是說，數(shù)據(jù)節(jié)點(diǎn)的復(fù)用，需要索引key值滿足一定的范圍條件

• 數(shù)據(jù)頁的復(fù)用

在 圖(1) 當(dāng)刪除了Page1數(shù)據(jù)頁全部數(shù)據(jù)節(jié)點(diǎn)后，Page1整頁都是可復(fù)用的，當(dāng)插入的記錄需要用到新頁的時候，Page1就可以被復(fù)用

當(dāng)相鄰的數(shù)據(jù)頁利用率比較低的時候，有可能會把它們合并到其中一個數(shù)據(jù)頁中，這時，另外一個數(shù)據(jù)頁就空出來了，這個空出來的數(shù)據(jù)頁就變成可復(fù)用的了

哪些操作會造成數(shù)據(jù)空洞

我們用 delete 命令刪除一條記錄后，InnoDB只是把對應(yīng)的數(shù)據(jù)節(jié)點(diǎn)標(biāo)記為已刪除且可復(fù)用的，這些可空著的等待使用的數(shù)據(jù)節(jié)點(diǎn)可以看作是一個一個的數(shù)據(jù)空洞

• 刪除數(shù)據(jù)

刪除數(shù)據(jù)的時候，會造成數(shù)據(jù)空洞，前面已經(jīng)解釋過，這里不再贅述了

• 插入數(shù)據(jù)

如果數(shù)據(jù)是按照索引大小順序插入，這個時候數(shù)據(jù)頁是緊湊的，不會出現(xiàn)數(shù)據(jù)空洞

如果是從索引中間插入的話，有可能會造成頁分裂，分裂之后的頁有可能出現(xiàn)數(shù)據(jù)空洞，下圖就是插入導(dǎo)致頁分裂的一個例子

如圖所示，分裂前葉子頁面已經(jīng)滿了，這時數(shù)據(jù)排列得很緊湊

現(xiàn)在插入了一個索引key值為15的數(shù)據(jù)，插入之后，Page1 頁分裂成了上圖中 Page1，Page2兩個頁面

分裂之后，Page1 頁面出現(xiàn)了兩個空洞，這兩個數(shù)據(jù)節(jié)點(diǎn)是可復(fù)用的，而 Page2頁面剛好滿了

• 更新數(shù)據(jù)

更新數(shù)據(jù)可以看成先刪除再插入，也是有可能造成數(shù)據(jù)空洞

比如: id 是表 ta的主鍵， update ta set id = 10 where id = 1 語句把 id = 1 修改為 id = 10，相當(dāng)于先刪除 id = 1 的記錄，再插入 id = 10 的記錄，這種情況是會產(chǎn)生數(shù)據(jù)空洞的

但是如果是類似 update ta set ia = ia + 1 where id = 1 這種沒有更改主鍵值的語句是不會造成空洞的

所以，更新數(shù)據(jù)可能會造成數(shù)據(jù)空洞

總結(jié)下來就是，表的增刪改操作，可能會造成數(shù)據(jù)空洞的，而線上的服務(wù)會對表進(jìn)行大量的增刪改操作，數(shù)據(jù)空洞存在的可能性比較大

如何收縮表空間

既然一張表，經(jīng)過大量無規(guī)則的增刪改操作之后，會產(chǎn)生大量的數(shù)據(jù)空洞

那如果我們新建一張和原來有數(shù)據(jù)空洞的表結(jié)構(gòu)相同的新表，然后把舊表中的數(shù)據(jù)按照索引升序依次插入到新表中，待舊表數(shù)據(jù)全部插入到新表之后，刪除舊表，再把新表重命名為舊表的名字

由于新表中葉子節(jié)點(diǎn)數(shù)據(jù)是按順序添加的，所以頁面是很緊湊的, 頁面利用率很高，需要的頁面比舊表少了很多，這樣舊表中索引上的空洞在新表就不存在了，新表數(shù)據(jù)文件占用的磁盤空間自然就會縮小，這樣就實(shí)現(xiàn)了表空間的收縮的目的

下面介紹的幾種收縮表空間的方法，雖然方法不同，但是基本的原理都是通過重建表的形式來達(dá)到目的的

• truntace table 表名

此操作等于 drop + create，先刪除表，然后再創(chuàng)建一個同名的新表，當(dāng)然，再執(zhí)行 truncate table 命令之前需要先保存一份舊表的數(shù)據(jù)， 命令執(zhí)行完成之后，再把這份數(shù)據(jù)導(dǎo)入新表

• alter table 表名 engine=InnoDB

這個操作是遍歷舊表主鍵索引的數(shù)據(jù)頁，把數(shù)據(jù)頁中的記錄生成B+樹結(jié)構(gòu)，存儲到磁盤上的臨時文件中，數(shù)據(jù)頁遍歷完了之后，用臨時文件替換掉舊表的數(shù)據(jù)文件

從MySQL5.6版本之后，這個操作是 Online DDL 的，需要說明的是，這種方法需要掃描表數(shù)據(jù)文件，對于大表來說是非常耗時的，如果是針對線上服務(wù)的話，需要避開業(yè)務(wù)高峰期，小心操作。

注意: 在重建表的時候，InnoDB 不會把整張表占滿，每個頁留了大概10%左右的數(shù)據(jù)節(jié)點(diǎn) 給后續(xù)的更新用, 也就是說，其實(shí)重建表之后并不是最緊湊的

假如有這么一個過程: 將表 t 重建一次,

插入一部分?jǐn)?shù)據(jù)，但是插入的這些數(shù)據(jù)，用掉了一部分的預(yù)留空間， 這種情況下，再重建一次表 t，就可能會出現(xiàn)重建表后比重建之前占用的空間還要大

小結(jié)

本文從一個實(shí)際的問題出發(fā)，重現(xiàn)問題、分析問題到解決問題，每一步都進(jìn)行了詳細(xì)的分析，限于篇幅，有些細(xì)節(jié)沒有深入，需要讀者自行了解