推薦閱讀:
史上最全最詳細Java全家桶,內附學習路線、面經、面試題庫
一張千萬級的資料表,刪除了一半的資料,你覺得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
批次插入,每個批次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
開始時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做增刪改操作。
如果覺得本文對你有幫助,可以轉發關注支援一下