[[405168]]

本文轉載自微信公眾號「微觀技術」，作者Tom哥  。轉載本文請聯(lián)系微觀技術公眾號。

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

(答案在文章中!!)

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

做個實驗，讓數(shù)據(jù)說話

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<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條數(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<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條數(shù)據(jù)，刪除若干后，目前剩余約 550W 條。

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

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

實驗結論：

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

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

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

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

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

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

假如表中已經插入若干條記錄，構造的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ù)是隨機的非主鍵有序，可能會造成數(shù)據(jù)頁分裂。

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

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

修改數(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ù)丟失。

Online DDL

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

執(zhí)行步驟：

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

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