你有沒有遇到這個(gè)問題，覺得數(shù)據(jù)庫占用空間太大，然后把一個(gè)最大的表刪掉了一半的數(shù)據(jù)，怎么表文件的大小還是沒變？
本文來聊聊數(shù)據(jù)庫表的空間回收，看看如何解決這個(gè)問題。
這里，我們還是針對 MySQL 中應(yīng)用最廣泛的 InnoDB 引擎展開討論。一個(gè) InnoDB 表包含兩部分，即：表結(jié)構(gòu)定義和數(shù)據(jù)。在 MySQL 8.0 版本以前，表結(jié)構(gòu)是存在以.frm 為后綴的文件里。而 MySQL 8.0 版本，則已經(jīng)允許把表結(jié)構(gòu)定義放在系統(tǒng)數(shù)據(jù)表中了。因?yàn)楸斫Y(jié)構(gòu)定義占用的空間很小，所以我們今天主要討論的是表數(shù)據(jù)。
參數(shù) innodb_file_per_table
- 表數(shù)據(jù)既可以存在共享表空間里，也可以是單獨(dú)的文件。這個(gè)行為是由參數(shù) innodb_file_per_table 控制的：
- 這個(gè)參數(shù)設(shè)置為 OFF 表示的是，表的數(shù)據(jù)放在系統(tǒng)共享表空間，也就是跟數(shù)據(jù)字典放在一起；
- 這個(gè)參數(shù)設(shè)置為 ON 表示的是，每個(gè) InnoDB 表數(shù)據(jù)存儲(chǔ)在一個(gè)以 .ibd 為后綴的文件中。
- 從 MySQL 5.6.6 版本開始，它的默認(rèn)值就是 ON 了。
- 建議你不論使用 MySQL 的哪個(gè)版本，都將這個(gè)值設(shè)置為 ON。因?yàn)?，一個(gè)表單獨(dú)存儲(chǔ)為一個(gè)文件更容易管理，而且在你不需要這個(gè)表的時(shí)候，通過 drop table 命令，系統(tǒng)就會(huì)直接刪除這個(gè)文件。而如果是放在共享表空間中，即使表刪掉了，空間也是不會(huì)回收的。
- 所以，將 innodb_file_per_table 設(shè)置為 ON，是推薦做法，我們接下來的討論都是基于這個(gè)設(shè)置展開的。
- 我們在刪除整個(gè)表的時(shí)候，可以使用 drop table 命令回收表空間。但是，我們遇到的更多的刪除數(shù)據(jù)的場景是刪除某些行，這時(shí)就遇到了我們文章開頭的問題：表中的數(shù)據(jù)被刪除了，但是表空間卻沒有被回收。
- 我們要徹底搞明白這個(gè)問題的話，就要從數(shù)據(jù)刪除流程說起了。
數(shù)據(jù)刪除流程
B+樹索引示意圖
- 假設(shè)，我們要?jiǎng)h掉 R4 這個(gè)記錄，InnoDB 引擎只會(huì)把 R4 這個(gè)記錄標(biāo)記為刪除。如果之后要再插入一個(gè) ID 在 300 和 600 之間的記錄時(shí)，可能會(huì)復(fù)用這個(gè)位置。但是，磁盤文件的大小并不會(huì)縮小。
- 現(xiàn)在，你已經(jīng)知道了 InnoDB 的數(shù)據(jù)是按頁存儲(chǔ)的，那么如果我們刪掉了一個(gè)數(shù)據(jù)頁上的所有記錄，會(huì)怎么樣？
- 答案是，整個(gè)數(shù)據(jù)頁就可以被復(fù)用了。但是，數(shù)據(jù)頁的復(fù)用跟記錄的復(fù)用是不同的。
- 記錄的復(fù)用，只限于符合范圍條件的數(shù)據(jù)。比如上面的這個(gè)例子，R4 這條記錄被刪除后，如果插入一個(gè) ID 是 400 的行，可以直接復(fù)用這個(gè)空間。但如果插入的是一個(gè) ID 是 800 的行，就不能復(fù)用這個(gè)位置了。
- 而當(dāng)整個(gè)頁從 B+ 樹里面摘掉以后，可以復(fù)用到任何位置。以上圖為例，如果將數(shù)據(jù)頁 page A 上的所有記錄刪除以后，page A 會(huì)被標(biāo)記為可復(fù)用。這時(shí)候如果要插入一條 ID=50 的記錄需要使用新頁的時(shí)候，page A 是可以被復(fù)用的。
- 如果相鄰的兩個(gè)數(shù)據(jù)頁利用率都很小，系統(tǒng)就會(huì)把這兩個(gè)頁上的數(shù)據(jù)合到其中一個(gè)頁上，另外一個(gè)數(shù)據(jù)頁就被標(biāo)記為可復(fù)用。
- 進(jìn)一步地，如果我們用 delete 命令把整個(gè)表的數(shù)據(jù)刪除呢？結(jié)果就是，所有的數(shù)據(jù)頁都會(huì)被標(biāo)記為可復(fù)用。但是磁盤上，文件不會(huì)變小。
- 你現(xiàn)在知道了，delete 命令其實(shí)只是把記錄的位置，或者數(shù)據(jù)頁標(biāo)記為了“可復(fù)用”，但磁盤文件的大小是不會(huì)變的。也就是說，通過 delete 命令是不能回收表空間的。這些可以復(fù)用，而沒有被使用的空間，看起來就像是“空洞”。
- 實(shí)際上，不止是刪除數(shù)據(jù)會(huì)造成空洞，插入數(shù)據(jù)也會(huì)。如果數(shù)據(jù)是按照索引遞增順序插入的，那么索引是緊湊的。但如果數(shù)據(jù)是隨機(jī)插入的，就可能造成索引的數(shù)據(jù)頁分裂。
- 假設(shè)上圖中 page A 已經(jīng)滿了，這時(shí)要再插入一行數(shù)據(jù)，會(huì)怎樣呢？
插入數(shù)據(jù)頁分裂
- 可以看到，由于 page A 滿了，再插入一個(gè) ID 是 550 的數(shù)據(jù)時(shí)，就不得不再申請一個(gè)新的頁面 page B 來保存數(shù)據(jù)了。頁分裂完成后，page A 的末尾就留下了空洞（注意：實(shí)際上，可能不止 1 個(gè)記錄的位置是空洞）。
- 另外，更新索引上的值，可以理解為刪除一個(gè)舊的值，再插入一個(gè)新值。不難理解，這也是會(huì)造成空洞的。也就是說，經(jīng)過大量增刪改的表，都是可能是存在空洞的。所以，如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。
- 而重建表，就可以達(dá)到這樣的目的。
重建表
- 試想一下，如果你現(xiàn)在有一個(gè)表 A，需要做空間收縮，為了把表中存在的空洞去掉，你可以怎么做呢？
- 你可以新建一個(gè)與表 A 結(jié)構(gòu)相同的表 B，然后按照主鍵 ID 遞增的順序，把數(shù)據(jù)一行一行地從表 A 里讀出來再插入到表 B 中。
- 由于表 B 是新建的表，所以表 A 主鍵索引上的空洞，在表 B 中就都不存在了。顯然地，表 B 的主鍵索引更緊湊，數(shù)據(jù)頁的利用率也更高。如果我們把表 B 作為臨時(shí)表，數(shù)據(jù)從表 A 導(dǎo)入表 B 的操作完成后，用表 B 替換 A，從效果上看，就起到了收縮表 A 空間的作用。
- 這里，你可以使用 alter table A engine=InnoDB 命令來重建表。在 MySQL 5.5 版本之前，這個(gè)命令的執(zhí)行流程跟我們前面描述的差不多，區(qū)別只是這個(gè)臨時(shí)表 B 不需要你自己創(chuàng)建，MySQL 會(huì)自動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。
改鎖表DDL
- 顯然，花時(shí)間最多的步驟是往臨時(shí)表插入數(shù)據(jù)的過程，如果在這個(gè)過程中，有新的數(shù)據(jù)要寫入到表 A 的話，就會(huì)造成數(shù)據(jù)丟失。因此，在整個(gè) DDL 過程中，表 A 中不能有更新。也就是說，這個(gè) DDL 不是 Online 的。
- 而在 MySQL 5.6 版本開始引入的 Online DDL，對這個(gè)操作流程做了優(yōu)化，重建表流程如下：
- 建立一個(gè)臨時(shí)文件，掃描表 A 主鍵的所有數(shù)據(jù)頁；
- 用數(shù)據(jù)頁中表 A 的記錄生成 B+ 樹，存儲(chǔ)到臨時(shí)文件中；
- 生成臨時(shí)文件的過程中，將所有對 A 的操作記錄在一個(gè)日志文件（row log）中，對應(yīng)的是圖中 state2 的狀態(tài)；
- 臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表 A 相同的數(shù)據(jù)文件，對應(yīng)的就是圖中 state3 的狀態(tài)；
- 用臨時(shí)文件替換表 A 的數(shù)據(jù)文件。
Online DDL
- 由于日志文件記錄和重放操作這個(gè)功能的存在，這個(gè)方案在重建表的過程中，允許對表 A 做增刪改操作。這也就是 Online DDL 名字的來源。
- alter 語句在啟動(dòng)的時(shí)候需要獲取 MDL 寫鎖，但是這個(gè)寫鎖在真正拷貝數(shù)據(jù)之前就退化成讀鎖了。為什么要退化呢？為了實(shí)現(xiàn) Online，MDL 讀鎖不會(huì)阻塞增刪改操作。
- 為了保護(hù)自己，為什么不禁止其他線程對這個(gè)表同時(shí)做 DDL。
- 而對于一個(gè)大表來說，Online DDL 最耗時(shí)的過程就是拷貝數(shù)據(jù)到臨時(shí)表的過程，這個(gè)步驟的執(zhí)行期間可以接受增刪改操作。所以，相對于整個(gè) DDL 過程來說，鎖的時(shí)間非常短。對業(yè)務(wù)來說，就可以認(rèn)為是 Online 的。
- 需要補(bǔ)充說明的是，上述的這些重建方法都會(huì)掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件。對于很大的表來說，這個(gè)操作是很消耗 IO 和 CPU 資源的。因此，如果是線上服務(wù)，你要很小心地控制操作時(shí)間。如果想要比較安全的操作的話，推薦你使用 GitHub 開源的 gh-ost 來做。
Online 和 inplace
- 說到 Online，我還要再和你澄清一下它和另一個(gè)跟 DDL 有關(guān)的、容易混淆的概念 inplace 的區(qū)別。
- 你可能注意到了，在圖中，我們把表 A 中的數(shù)據(jù)導(dǎo)出來的存放位置叫作 tmp_table。這是一個(gè)臨時(shí)表，是在 server 層創(chuàng)建的。
- 在后圖中，根據(jù)表 A 重建出來的數(shù)據(jù)是放在“tmp_file”里的，這個(gè)臨時(shí)文件是 InnoDB 在內(nèi)部創(chuàng)建出來的。整個(gè) DDL 過程都在 InnoDB 內(nèi)部完成。對于 server 層來說，沒有把數(shù)據(jù)挪動(dòng)到臨時(shí)表，是一個(gè)“原地”操作，這就是“inplace”名稱的來源。
- 所以，我現(xiàn)在問你，如果你有一個(gè) 1TB 的表，現(xiàn)在磁盤間是 1.2TB，能不能做一個(gè) inplace 的 DDL 呢？
- 答案是不能。因?yàn)椋瑃mp_file 也是要占用臨時(shí)空間的。我們重建表的這個(gè)語句 alter table t engine=InnoDB，其實(shí)隱含的意思是：
alter table t engine=innodb,ALGORITHM=inplace;
其對應(yīng)的是拷貝做法：
lter table t engine=innodb,ALGORITHM=copy;
- 當(dāng)你使用 ALGORITHM=copy 的時(shí)候，表示的是強(qiáng)制拷貝表，對應(yīng)的流程就是非Online對應(yīng)的過程的操作過程。
- 但這樣說你可能會(huì)覺得，inplace 跟 Online 是不是就是一個(gè)意思？其實(shí)不是的，只是在重建表這個(gè)邏輯中剛好是這樣而已。比如，如果要給 InnoDB 表的一個(gè)字段加全文索引，寫法是：
alter table t add FULLTEXT(field_name);
- 這個(gè)過程是 inplace 的，但會(huì)阻塞增刪改操作，是非 Online 的。如果說這兩個(gè)邏輯之間的關(guān)系是什么的話，可以概括為：
- DDL 過程如果是 Online 的，就一定是 inplace 的；
- 反過來未必，也就是說 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空間索引 (SPATIAL index) 就屬于這種情況。
- 從 MySQL 5.6 版本開始，alter table t engine = InnoDB（也就是 recreate）默認(rèn)的就是上面Online DDL圖的流程了；
- analyze table t 其實(shí)不是重建表，只是對表的索引信息做重新統(tǒng)計(jì)，沒有修改數(shù)據(jù)，這個(gè)過程中加了 MDL 讀鎖；
- optimize table t 等于 recreate+analyze。
小結(jié)
- 通過本文我們知道，如果要收縮一個(gè)表，只是 delete 掉表里面不用的數(shù)據(jù)的話，表文件的大小是不會(huì)變的，你還要通過 alter table 命令重建表，才能達(dá)到表文件變小的目的。這里介紹了重建表的兩種實(shí)現(xiàn)方式，Online DDL 的方式是可以考慮在業(yè)務(wù)低峰期使用的，而 MySQL 5.5 及之前的版本，這個(gè)命令是會(huì)阻塞 DML 的，這個(gè)你需要特別小心。
