MySQL表空間回收的正確姿勢
本文轉載自微信公眾號「Linux開發那些事兒」，作者 LinuxThings 。轉載本文請聯系Linux開發那些事兒公眾號。
不知道大家有沒有遇到這樣的一種情況，線上業務在MySQL表上做增刪改查操作，隨著時間的推移，表里面的數據越來越多，表數據文件越來越大，數據庫占用的空間自然也逐漸增長
為了縮小磁盤上表數據文件占用的空間，我們在最大的一張業務表中用delete命令刪除了一半兒的舊數據，刪除之后，磁盤上表數據文件并沒有縮小，即使刪除整張表的數據，文件依然沒有變小，這是為什么呢?
本文將詳細的分析上述問題，并給出正確回收表空間的方法
前置說明
目前大部分MySQL數據庫都是用的 InnoDB 引擎，所以如無特殊說明，文中的實例都是基于InnoDB引擎的
在MySQL配置中有個配置項叫 innodb_file_per_table 將它設置為1之后， 每個表的數據會單獨存儲在一個以 .ibd 為后綴的文件中
如果 innodb_file_per_table 沒有開啟的話， 表的數據是存儲在系統的共享表空間，這樣即使刪除了表，共享表空間也不會釋放這部分空間
所以，通常情況下，都是將 innodb_file_per_table 選項設置為 1, 同時為了能直觀的看到表數據文件的大小變化，文中的實例也都是基于開啟了 此選項來說明的
問題重現
新建一張表ta，表的結構如下
- 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 中批量插入數據
- 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控制臺執行 call multinsert(0,100000) 命令，往 ta表插入10萬條數據
- mysql> call multinsert(0,100000);
- mysql> select count(*) from ta;
- +----------+
- | count(*) |
- +----------+
- | 100000 |
- +----------+
- 1 row in set (0.02 sec)
查看磁盤上ta表的數據文件 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
從上面的結果可以知道，ta表插入10萬條數據之后，ta.ibd 大小為 11534336 字節( 大約 11M )
現在我們使用 delete 命令刪除一半兒表數據( 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
從上面的結果可以知道，ta表刪除了一半兒，也就是5萬行數據之后，ta.ibd的大小是 11534336 字節( 約11M )
也就是說 ta表刪除數據前后，磁盤上表數據文件并沒有縮小
要弄明白數據文件為什么沒有縮小，就需要深入了解刪除數據的原理
刪除數據原理
我們都知道，InnoDB里的數據都是用B+樹組織的，關于B+樹的知識請參考 理解B+樹
圖(1)
上面是InnoDB的索引示意圖，其中用虛線框起來的節點是屬于Page1數據頁，葉子節點存儲的是索引對應的數據，它們按照索引從小到大的順序組成了一個有序數組
假如我們要刪除Page1頁中索引key值為 13 的數據，也即上圖中紅色部分
InnoDB引擎會把索引key值為13的節點標記為已刪除，它并不會回收節點真實的物理空間，只是將它標記為已刪除的節點，后續是可以復用的，所以，刪除表記錄，磁盤上數據文件不會縮小
你可能會說，上面只是刪除了Page1頁中一個節點的數據，那如果把Page1頁中節點數據全部刪除了，應該會回收Page1頁的空間吧?
答案是，不會回收
當Page1頁數據全部刪除了，整個數據頁都會被標記為已刪除，并且整個數據頁都可以復用，所以，這種情況下，磁盤上的數據文件仍然不會縮小
數據的復用
數據的復用涉及到數據節點的插入、刪除、轉移以及數據頁的合并等操作，具體的操作流程相關的細節請參考 理解B+樹，這里就不再重復說明了
數據節點的復用
在上面 圖(1) 中，當刪除了索引key值為 13 的節點后，此節點就被標記為可復用的
如果之后又插入了一條索引key值在 7 到 18 之間的記錄時，就會復用原來索引key值為13的數據節點
但是如果之后插入的記錄的索引key值不在 7 到 18 之間時，可能就無法復用原來索引key值為13的數據節點
也就是說，數據節點的復用，需要索引key值滿足一定的范圍條件
- 數據頁的復用
在 圖(1) 當刪除了Page1數據頁全部數據節點后，Page1整頁都是可復用的，當插入的記錄需要用到新頁的時候，Page1就可以被復用
當相鄰的數據頁利用率比較低的時候，有可能會把它們合并到其中一個數據頁中，這時，另外一個數據頁就空出來了，這個空出來的數據頁就變成可復用的了
哪些操作會造成數據空洞
我們用 delete 命令刪除一條記錄后，InnoDB只是把對應的數據節點標記為已刪除且可復用的，這些可空著的等待使用的數據節點可以看作是一個一個的數據空洞
- 刪除數據
刪除數據的時候，會造成數據空洞，前面已經解釋過，這里不再贅述了
- 插入數據
如果數據是按照索引大小順序插入，這個時候數據頁是緊湊的，不會出現數據空洞
如果是從索引中間插入的話，有可能會造成頁分裂，分裂之后的頁有可能出現數據空洞，下圖就是插入導致頁分裂的一個例子
如圖所示，分裂前葉子頁面已經滿了，這時數據排列得很緊湊
現在插入了一個索引key值為15的數據，插入之后，Page1 頁分裂成了上圖中 Page1，Page2兩個頁面
分裂之后，Page1 頁面出現了兩個空洞，這兩個數據節點是可復用的，而 Page2頁面剛好滿了
- 更新數據
更新數據可以看成先刪除再插入，也是有可能造成數據空洞
比如: id 是表 ta的主鍵， update ta set id = 10 where id = 1 語句把 id = 1 修改為 id = 10，相當于先刪除 id = 1 的記錄，再插入 id = 10 的記錄，這種情況是會產生數據空洞的
但是如果是類似 update ta set ia = ia + 1 where id = 1 這種沒有更改主鍵值的語句是不會造成空洞的
所以，更新數據可能會造成數據空洞
總結下來就是，表的增刪改操作，可能會造成數據空洞的，而線上的服務會對表進行大量的增刪改操作，數據空洞存在的可能性比較大
如何收縮表空間
既然一張表，經過大量無規則的增刪改操作之后，會產生大量的數據空洞
那如果我們新建一張和原來有數據空洞的表結構相同的新表，然后把舊表中的數據按照索引升序依次插入到新表中，待舊表數據全部插入到新表之后，刪除舊表，再把新表重命名為舊表的名字
由于新表中葉子節點數據是按順序添加的，所以頁面是很緊湊的, 頁面利用率很高，需要的頁面比舊表少了很多，這樣舊表中索引上的空洞在新表就不存在了，新表數據文件占用的磁盤空間自然就會縮小，這樣就實現了表空間的收縮的目的
下面介紹的幾種收縮表空間的方法，雖然方法不同，但是基本的原理都是通過重建表的形式來達到目的的
- truntace table 表名
此操作等于 drop + create，先刪除表，然后再創建一個同名的新表，當然，再執行 truncate table 命令之前需要先保存一份舊表的數據， 命令執行完成之后，再把這份數據導入新表
- alter table 表名 engine=InnoDB
這個操作是遍歷舊表主鍵索引的數據頁，把數據頁中的記錄生成B+樹結構，存儲到磁盤上的臨時文件中，數據頁遍歷完了之后，用臨時文件替換掉舊表的數據文件
從MySQL5.6版本之后，這個操作是 Online DDL 的，需要說明的是，這種方法需要掃描表數據文件，對于大表來說是非常耗時的，如果是針對線上服務的話，需要避開業務高峰期，小心操作。
注意: 在重建表的時候，InnoDB 不會把整張表占滿，每個頁留了大概10%左右的數據節點 給后續的更新用, 也就是說，其實重建表之后并不是最緊湊的
假如有這么一個過程: 將表 t 重建一次,
插入一部分數據，但是插入的這些數據，用掉了一部分的預留空間， 這種情況下，再重建一次表 t，就可能會出現重建表后比重建之前占用的空間還要大
小結
本文從一個實際的問題出發，重現問題、分析問題到解決問題，每一步都進行了詳細的分析，限于篇幅，有些細節沒有深入，需要讀者自行了解
