面試題：MySQL表刪除一半數據，B+樹索引文件會不會變小？
本文轉載自微信公眾號「微觀技術」，作者Tom哥 。轉載本文請聯系微觀技術公眾號。
一張千萬級的數據表，刪除了一半的數據，你覺得B+樹索引文件會不會變小?
(答案在文章中!!)
我們先來做個實驗，看看表的大小是如何變化的?
做個實驗，讓數據說話
1、首先，在mysql中創建一張用戶表，表結構如下：
- 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、造數據。用戶表中批量插入1000W條數據
- @GetMapping("/insert_batch")
- public Object insertBatch(@RequestParam("batch") int batch) {
- // 設置批次batch=100000，共插入 1000W 條數據
- for (int j = 1; j <= batch; j++) {
- List<User> 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條數據。
3、查看表文件大小
索引文件大小約 595 M，最后修改時間 02:17
說明：
MySQL 8.0 版本以前，表結構是存在以.frm為后綴的文件里。
獨享表空間存儲方式使用.ibd文件來存放數據和索引，且每個表一個.ibd文件。
表數據既可以存在共享表空間，也可以是單獨文件。通過innodb_file_per_table參數控制。MySQL 5.6.6 版本之后，默認是ON，這樣，每個 InnoDB 表數據存儲在一個以 .ibd為后綴的文件中。
4、刪除 約500W條數據
- @GetMapping("/delete_batch")
- public Object deleteBatch(@RequestParam("batch") int batch) {
- for (int j = 1; j <= batch; j++) {
- List<Long> 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條數據，刪除若干后，目前剩余約 550W 條。
5、在刪除約500W條記錄后，再次查看表文件大小
索引文件大小約 595 M，最后修改時間 10:34
實驗結論：
對于千萬級的表數據存儲，刪除大量記錄后，表文件大小并沒有隨之變小。好奇怪，是什么原因導致的?不要著急，接下來，我們來深入剖析其中原因。
數據表操作有新增、刪除、修改、查詢，其中查詢屬于讀操作，并不會修改文件內容。修改文件內容的是寫操作，具體分為有刪除、新增、修改三種類型。
接下來，我們開始逐一分析。
刪除數據
InnoDB 中的數據采用B+樹來組織結構。如果對B+樹存儲結構不清楚的話，可以先看下我之前寫的一篇文章，鞏固下基礎知識。
面試題：mysql 一棵 B+ 樹能存多少條數據?
假如表中已經插入若干條記錄，構造的B+樹結構如下圖所示：
刪除id=7這條記錄，InnoDB引擎只是把id=7這條記錄標記為刪除，但是空間保留。如果后面有id位于(6,19)區間內的數據插入時，可以重復使用這個空間。
上圖，表示新插入一條id=16的記錄。
除了記錄可以復用外，數據頁也可以復用。當整個頁從B+樹摘掉后，可以復用到任何位置。
比如，將page number=5頁上的所有記錄刪除以后，該page標記為可復用。此時如果插入一條id=100的記錄需要使用新頁，此時page number=5便可以被復用了。
如果相鄰兩個page的利用率都很低，數據庫會將兩個頁的數據合并到其中一個page上，另一個page被標記為可復用。
當然，如果是像上面我們做的實驗那樣，將整個表的數據全部delete掉呢?所有的數據頁都會被標記為可復用，但空間并沒有釋放，所以表文件大小依然沒有改變。
總結：delete命令只是把數據頁或記錄位置標記為可復用，表空間并沒有被回收，該現象我們稱之為”空洞“。
新增數據
如果是插入的數據是隨機的非主鍵有序，可能會造成數據頁分裂。
上圖可以看到，假如page number=5的數據頁已經滿了，此時插入id=15的記錄，需要申請一個新的頁page number=6來保存數據。待頁分裂完成后，page number=5的最后位置就會留下一個可復用的空洞。
相反，如果數據是按照索引遞增順序插入的，那么索引是緊湊的，不會出現數據頁分裂。
修改數據
如果修改的是非索引值，那么并不會影響B+樹的結構。
比如，更新id=7的其它字段值，主鍵id保持不變。整個B+樹并沒有發生結構調整。
但是，如果修改的內容包含了索引，那么操作步驟是先刪除一個舊的值，然后再插入一個新值。可能會造成空洞。
分析發現，新增、修改、刪除數據，都可能造成表空洞，那么有沒有什么辦法壓縮表空間??
客官，請繼續往下看
新建表
我們可以新建一個影子表B與原表A的結構一致，然后按主鍵id由小到大，把數據從表A遷移到表B。由于表B是新表，并不會有空洞，數據頁的利用率更高。
待表A的數據全部遷移完成后，再用表B替換表A。
MySQL 5.5 版本之前，提供了一鍵命令，快捷式完成整個流程，轉存數據、交換表名、刪除舊表。
- alter table 表名 engine=InnoDB
但是，該方案有個致命缺點，表重構過程中，如果有新的數據寫入表A時，不會被遷移，會造成數據丟失。
Online DDL
為了解決上面問題，MySQL 5.6 版本開始引入 Online DDL，對流程做了優化。
執行步驟：
- 新建一個臨時文件
- 掃描表A主鍵的所有數據頁，生成B+ 樹，存儲到臨時文件中
- 在生成臨時文件過程中，如果有對表A做寫操作，操作會記錄到一個日志文件中
- 當臨時文件生成后，再重放日志文件，將操作應用到臨時文件
- 用臨時文件替換表A的數據文件
- 刪除舊的表A數據文件
與新建表的最大區別，增加了日志文件記錄和重放功能。遷移過程中，允許對表A做增刪改操作。
