面試題：mysql 表刪除一半數(shù)據(jù)，B+樹索引文件會不會變小？？？

一張千萬級的數(shù)據(jù)表，刪除了一半的數(shù)據(jù)，你覺得B+樹索引文件會不會變小？

（答案在文章中！！）

我們先來做個實驗，看看表的大小是如何變化的？？

1、首先，在mysql中創(chuàng)建一張用戶表，表結構如下：

CREATE?TABLE?`user`?(
??`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,
??`user_name`?varchar(128)?NOT?NULL?DEFAULT?''?COMMENT?'用戶名',
??`age`?int(11)?NOT?NULL??COMMENT?'年齡',
??`address`?varchar(128)?COMMENT?'地址',
???PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB??DEFAULT?CHARSET=utf8mb4?COMMENT='用戶表';

2、造數(shù)據(jù)。用戶表中批量插入1000W條數(shù)據(jù)

@GetMapping("/insert_batch")
public?Object?insertBatch(@RequestParam("batch")?int?batch)?{

????//?設置批次batch=100000，共插入?1000W?條數(shù)據(jù)
????for?(int?j?=?1;?j?<=?batch;?j++)?{
????????List?userList?=?new?ArrayList<>();
????????for?(int?i?=?1;?i?<=?100;?i++)?{
????????????User?user?=?User.builder().userName("Tom哥-"?+?((j?-?1)?*?100?+?i)).age(29).address("上海").build();
????????????userList.add(user);
????????}
????????userMapper.insertBatch(userList);
????}
????return?"success";
}

批量插入，每個批次100條記錄，100000個批次，共1000W條數(shù)據(jù)

3、查看表文件大小

索引文件大小約 595 M，最后修改時間 02:17

說明：

• MySQL 8.0 版本以前，表結構是存在以.frm為后綴的文件里
• 獨享表空間存儲方式使用.ibd文件來存放數(shù)據(jù)和索引，且每個表一個.ibd文件

表數(shù)據(jù)既可以存在共享表空間，也可以是單獨文件。通過innodb_file_per_table參數(shù)控制。MySQL 5.6.6 版本之后，默認是ON，這樣，每個 InnoDB 表數(shù)據(jù)存儲在一個以 .ibd為后綴的文件中。

4、刪除 約500W條數(shù)據(jù)

@GetMapping("/delete_batch")
public?Object?deleteBatch(@RequestParam("batch")?int?batch)?{
????for?(int?j?=?1;?j?<=?batch;?j++)?{
????????List?idList?=?new?ArrayList<>();
????????for?(int?i?=?1;?i?<=?100;?i?+=?2)?{
????????????idList.add((long)?((j?-?1)?*?100?+?i));
????????}
????????userMapper.deleteUser(idList);
????}
????return?"success";
}

開始時user表有1000W條數(shù)據(jù)，刪除若干后，目前剩余約 550W 條

5、在刪除約500W條記錄后，再次查看表文件大小

索引文件大小約 595 M，最后修改時間 10:34

實驗結論：

對于千萬級的表數(shù)據(jù)存儲，刪除大量記錄后，表文件大小并沒有隨之變小。好奇怪，是什么原因導致的？不要著急，接下來，我們來深入剖析其中原因

數(shù)據(jù)表操作有新增、刪除、修改、查詢，其中查詢屬于讀操作，并不會修改文件內容。修改文件內容的是寫操作，具體分為有刪除、新增、修改三種類型。

接下來，我們開始逐一分析

InnoDB 中的數(shù)據(jù)采用B+樹來組織結構。如果對B+樹存儲結構不清楚的話，可以先看下我之前寫的一篇文章，鞏固下基礎知識。

面試題：mysql 一棵 B+ 樹能存多少條數(shù)據(jù)？

假如表中已經(jīng)插入若干條記錄，構造的B+樹結構如下圖所示：

刪除id=7這條記錄，InnoDB引擎只是把id=7這條記錄標記為刪除，但是空間保留。如果后面有id位于(6,19)區(qū)間內的數(shù)據(jù)插入時，可以重復使用這個空間。

上圖，表示新插入一條id=16的記錄。

除了記錄可以復用外，數(shù)據(jù)頁也可以復用。當整個頁從B+樹摘掉后，可以復用到任何位置。

比如，將page number=5頁上的所有記錄刪除以后，該page標記為可復用。此時如果插入一條id=100的記錄需要使用新頁，此時page number=5便可以被復用了。

如果相鄰兩個page的利用率都很低，數(shù)據(jù)庫會將兩個頁的數(shù)據(jù)合并到其中一個page上，另一個page被標記為可復用。

當然，如果是像上面我們做的實驗那樣，將整個表的數(shù)據(jù)全部delete掉呢？所有的數(shù)據(jù)頁都會被標記為可復用，但空間并沒有釋放，所以表文件大小依然沒有改變。

總結：delete命令只是把數(shù)據(jù)頁或記錄位置標記為可復用，表空間并沒有被回收，該現(xiàn)象我們稱之為”空洞“

如果是插入的數(shù)據(jù)是隨機的非主鍵有序，可能會造成數(shù)據(jù)頁分裂。

上圖可以看到，假如page number=5的數(shù)據(jù)頁已經(jīng)滿了，此時插入id=15的記錄，需要申請一個新的頁page number=6來保存數(shù)據(jù)。待頁分裂完成后，page number=5的最后位置就會留下一個可復用的空洞。

相反，如果數(shù)據(jù)是按照索引遞增順序插入的，那么索引是緊湊的，不會出現(xiàn)數(shù)據(jù)頁分裂。

如果修改的是非索引值，那么并不會影響B(tài)+樹的結構

比如，更新id=7的其它字段值，主鍵id保持不變。整個B+樹并沒有發(fā)生結構調整。

但是，如果修改的內容包含了索引，那么操作步驟是先刪除一個舊的值，然后再插入一個新值。可能會造成空洞。

分析發(fā)現(xiàn)，新增、修改、刪除數(shù)據(jù)，都可能造成表空洞，那么有沒有什么辦法壓縮表空間？？

客官，請繼續(xù)往下看

我們可以新建一個影子表B與原表A的結構一致，然后按主鍵id由小到大，把數(shù)據(jù)從表A遷移到表B。由于表B是新表，并不會有空洞，數(shù)據(jù)頁的利用率更高。

待表A的數(shù)據(jù)全部遷移完成后，再用表B替換表A。

MySQL 5.5 版本之前，提供了一鍵命令，快捷式完成整個流程，轉存數(shù)據(jù)、交換表名、刪除舊表。

alter?table?表名??engine=InnoDB?

但是，該方案有個致命缺點，表重構過程中，如果有新的數(shù)據(jù)寫入表A時，不會被遷移，會造成數(shù)據(jù)丟失。

為了解決上面問題，MySQL 5.6 版本開始引入 ?Online DDL，對流程做了優(yōu)化。

執(zhí)行步驟：

• 新建一個臨時文件
• 掃描表A主鍵的所有數(shù)據(jù)頁，生成B+ 樹，存儲到臨時文件中
• 在生成臨時文件過程中，如果有對表A做寫操作，操作會記錄到一個日志文件中
• 當臨時文件生成后，再重放日志文件，將操作應用到臨時文件
• 用臨時文件替換表A的數(shù)據(jù)文件
• 刪除舊的表A數(shù)據(jù)文件

與新建表的最大區(qū)別，增加了日志文件記錄和重放功能。遷移過程中，允許對表A做增刪改操作。