你有沒(méi)有遇到這個(gè)問(wèn)題，覺(jué)得數(shù)據(jù)庫(kù)占用空間太大慕嚷，然后把一個(gè)最大的表刪掉了一半的數(shù)據(jù)哥牍，怎么表文件的大小還是沒(méi)變？

本文來(lái)聊聊數(shù)據(jù)庫(kù)表的空間回收喝检，看看如何解決這個(gè)問(wèn)題嗅辣。

這里，我們還是針對(duì) MySQL 中應(yīng)用最廣泛的 InnoDB 引擎展開(kāi)討論挠说。一個(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 版本開(kāi)始掐暮，它的默認(rèn)值就是 ON 了。
• 建議你不論使用 MySQL 的哪個(gè)版本政钟，都將這個(gè)值設(shè)置為 ON路克。因?yàn)檎两幔粋€(gè)表單獨(dú)存儲(chǔ)為一個(gè)文件更容易管理，而且在你不需要這個(gè)表的時(shí)候精算，通過(guò) drop table 命令瓢宦，系統(tǒng)就會(huì)直接刪除這個(gè)文件。而如果是放在共享表空間中灰羽，即使表刪掉了驮履，空間也是不會(huì)回收的。
• 所以廉嚼，將 innodb_file_per_table 設(shè)置為 ON玫镐，是推薦做法，我們接下來(lái)的討論都是基于這個(gè)設(shè)置展開(kāi)的怠噪。
• 我們?cè)趧h除整個(gè)表的時(shí)候恐似，可以使用 drop table 命令回收表空間。但是舰绘，我們遇到的更多的刪除數(shù)據(jù)的場(chǎng)景是刪除某些行蹂喻，這時(shí)就遇到了我們文章開(kāi)頭的問(wèn)題：表中的數(shù)據(jù)被刪除了葱椭，但是表空間卻沒(méi)有被回收捂寿。
• 我們要徹底搞明白這個(gè)問(wèn)題的話，就要從數(shù)據(jù)刪除流程說(shuō)起了孵运。

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

B+樹(shù)索引示意圖

• 假設(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ù)是按頁(yè)存儲(chǔ)的等孵，那么如果我們刪掉了一個(gè)數(shù)據(jù)頁(yè)上的所有記錄稚照，會(huì)怎么樣？
• 答案是俯萌，整個(gè)數(shù)據(jù)頁(yè)就可以被復(fù)用了果录。但是，數(shù)據(jù)頁(yè)的復(fù)用跟記錄的復(fù)用是不同的咐熙。
• 記錄的復(fù)用弱恒，只限于符合范圍條件的數(shù)據(jù)。比如上面的這個(gè)例子棋恼，R4 這條記錄被刪除后返弹，如果插入一個(gè) ID 是 400 的行锈玉，可以直接復(fù)用這個(gè)空間。但如果插入的是一個(gè) ID 是 800 的行义起，就不能復(fù)用這個(gè)位置了嘲玫。
• 而當(dāng)整個(gè)頁(yè)從 B+ 樹(shù)里面摘掉以后，可以復(fù)用到任何位置并扇。以上圖為例去团，如果將數(shù)據(jù)頁(yè) page A 上的所有記錄刪除以后，page A 會(huì)被標(biāo)記為可復(fù)用穷蛹。這時(shí)候如果要插入一條 ID=50 的記錄需要使用新頁(yè)的時(shí)候土陪，page A 是可以被復(fù)用的。
• 如果相鄰的兩個(gè)數(shù)據(jù)頁(yè)利用率都很小肴熏，系統(tǒng)就會(huì)把這兩個(gè)頁(yè)上的數(shù)據(jù)合到其中一個(gè)頁(yè)上鬼雀，另外一個(gè)數(shù)據(jù)頁(yè)就被標(biāo)記為可復(fù)用。
• 進(jìn)一步地蛙吏，如果我們用 delete 命令把整個(gè)表的數(shù)據(jù)刪除呢源哩？結(jié)果就是，所有的數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為可復(fù)用鸦做。但是磁盤上励烦，文件不會(huì)變小。
• 你現(xiàn)在知道了泼诱，delete 命令其實(shí)只是把記錄的位置坛掠，或者數(shù)據(jù)頁(yè)標(biāo)記為了“可復(fù)用”，但磁盤文件的大小是不會(huì)變的治筒。也就是說(shuō)屉栓，通過(guò) delete 命令是不能回收表空間的。這些可以復(fù)用耸袜，而沒(méi)有被使用的空間友多，看起來(lái)就像是“空洞”。
• 實(shí)際上堤框，不止是刪除數(shù)據(jù)會(huì)造成空洞管引，插入數(shù)據(jù)也會(huì)携取。如果數(shù)據(jù)是按照索引遞增順序插入的，那么索引是緊湊的。但如果數(shù)據(jù)是隨機(jī)插入的氯迂，就可能造成索引的數(shù)據(jù)頁(yè)分裂病涨。
• 假設(shè)上圖中 page A 已經(jīng)滿了居夹，這時(shí)要再插入一行數(shù)據(jù)幔烛，會(huì)怎樣呢？

插入數(shù)據(jù)頁(yè)分裂

• 可以看到格带，由于 page A 滿了撤缴，再插入一個(gè) ID 是 550 的數(shù)據(jù)時(shí)刹枉，就不得不再申請(qǐng)一個(gè)新的頁(yè)面 page B 來(lái)保存數(shù)據(jù)了。頁(yè)分裂完成后屈呕，page A 的末尾就留下了空洞（注意：實(shí)際上微宝，可能不止 1 個(gè)記錄的位置是空洞）。
• 另外虎眨，更新索引上的值蟋软，可以理解為刪除一個(gè)舊的值，再插入一個(gè)新值嗽桩。不難理解岳守，這也是會(huì)造成空洞的。也就是說(shuō)碌冶，經(jīng)過(guò)大量增刪改的表湿痢，都是可能是存在空洞的。所以扑庞，如果能夠把這些空洞去掉譬重，就能達(dá)到收縮表空間的目的。
• 而重建表罐氨，就可以達(dá)到這樣的目的臀规。

重建表

• 試想一下，如果你現(xiàn)在有一個(gè)表 A岂昭，需要做空間收縮以现，為了把表中存在的空洞去掉狠怨，你可以怎么做呢约啊？
• 你可以新建一個(gè)與表 A 結(jié)構(gòu)相同的表 B，然后按照主鍵 ID 遞增的順序佣赖，把數(shù)據(jù)一行一行地從表 A 里讀出來(lái)再插入到表 B 中恰矩。
• 由于表 B 是新建的表，所以表 A 主鍵索引上的空洞憎蛤，在表 B 中就都不存在了外傅。顯然地，表 B 的主鍵索引更緊湊俩檬，數(shù)據(jù)頁(yè)的利用率也更高萎胰。如果我們把表 B 作為臨時(shí)表，數(shù)據(jù)從表 A 導(dǎo)入表 B 的操作完成后棚辽，用表 B 替換 A技竟，從效果上看，就起到了收縮表 A 空間的作用屈藐。
• 這里榔组，你可以使用 alter table A engine=InnoDB 命令來(lái)重建表熙尉。在 MySQL 5.5 版本之前，這個(gè)命令的執(zhí)行流程跟我們前面描述的差不多搓扯，區(qū)別只是這個(gè)臨時(shí)表 B 不需要你自己創(chuàng)建检痰，MySQL 會(huì)自動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名锨推、刪除舊表的操作铅歼。

改鎖表DDL

• 顯然，花時(shí)間最多的步驟是往臨時(shí)表插入數(shù)據(jù)的過(guò)程换可，如果在這個(gè)過(guò)程中谭贪，有新的數(shù)據(jù)要寫入到表 A 的話，就會(huì)造成數(shù)據(jù)丟失锦担。因此俭识，在整個(gè) DDL 過(guò)程中，表 A 中不能有更新洞渔。也就是說(shuō)套媚，這個(gè) DDL 不是 Online 的。
• 而在 MySQL 5.6 版本開(kāi)始引入的 Online DDL磁椒，對(duì)這個(gè)操作流程做了優(yōu)化堤瘤，重建表流程如下：
  1. 建立一個(gè)臨時(shí)文件，掃描表 A 主鍵的所有數(shù)據(jù)頁(yè)浆熔；
  2. 用數(shù)據(jù)頁(yè)中表 A 的記錄生成 B+ 樹(shù)本辐，存儲(chǔ)到臨時(shí)文件中；
  3. 生成臨時(shí)文件的過(guò)程中医增，將所有對(duì) A 的操作記錄在一個(gè)日志文件（row log）中慎皱，對(duì)應(yīng)的是圖中 state2 的狀態(tài)；
  4. 臨時(shí)文件生成后茫多，將日志文件中的操作應(yīng)用到臨時(shí)文件天揖，得到一個(gè)邏輯數(shù)據(jù)上與表 A 相同的數(shù)據(jù)文件跪帝，對(duì)應(yīng)的就是圖中 state3 的狀態(tài)伞剑；
  5. 用臨時(shí)文件替換表 A 的數(shù)據(jù)文件。

Online DDL

• 由于日志文件記錄和重放操作這個(gè)功能的存在腰素，這個(gè)方案在重建表的過(guò)程中雪营，允許對(duì)表 A 做增刪改操作献起。這也就是 Online DDL 名字的來(lái)源谴餐。
• alter 語(yǔ)句在啟動(dòng)的時(shí)候需要獲取 MDL 寫鎖，但是這個(gè)寫鎖在真正拷貝數(shù)據(jù)之前就退化成讀鎖了汁展。為什么要退化呢厌殉？為了實(shí)現(xiàn) Online，MDL 讀鎖不會(huì)阻塞增刪改操作器紧。
• 為了保護(hù)自己楼眷，為什么不禁止其他線程對(duì)這個(gè)表同時(shí)做 DDL。
  • 而對(duì)于一個(gè)大表來(lái)說(shuō)掌腰，Online DDL 最耗時(shí)的過(guò)程就是拷貝數(shù)據(jù)到臨時(shí)表的過(guò)程硝清，這個(gè)步驟的執(zhí)行期間可以接受增刪改操作芦拿。所以查邢，相對(duì)于整個(gè) DDL 過(guò)程來(lái)說(shuō)扰藕，鎖的時(shí)間非常短。對(duì)業(yè)務(wù)來(lái)說(shuō)笔刹，就可以認(rèn)為是 Online 的舌菜。
  • 需要補(bǔ)充說(shuō)明的是亦镶，上述的這些重建方法都會(huì)掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件缤骨。對(duì)于很大的表來(lái)說(shuō)，這個(gè)操作是很消耗 IO 和 CPU 資源的精拟。因此，如果是線上服務(wù)虱歪，你要很小心地控制操作時(shí)間串前。如果想要比較安全的操作的話，推薦你使用 GitHub 開(kāi)源的 gh-ost 來(lái)做荡碾。

Online 和 inplace

• 說(shuō)到 Online，我還要再和你澄清一下它和另一個(gè)跟 DDL 有關(guān)的局装、容易混淆的概念 inplace 的區(qū)別。
• 你可能注意到了铐尚，在圖中拨脉，我們把表 A 中的數(shù)據(jù)導(dǎo)出來(lái)的存放位置叫作 tmp_table宣增。這是一個(gè)臨時(shí)表玫膀，是在 server 層創(chuàng)建的。
• 在后圖中爹脾，根據(jù)表 A 重建出來(lái)的數(shù)據(jù)是放在“tmp_file”里的，這個(gè)臨時(shí)文件是 InnoDB 在內(nèi)部創(chuàng)建出來(lái)的灵妨。整個(gè) DDL 過(guò)程都在 InnoDB 內(nèi)部完成解阅。對(duì)于 server 層來(lái)說(shuō)货抄，沒(méi)有把數(shù)據(jù)挪動(dòng)到臨時(shí)表蟹地，是一個(gè)“原地”操作怪与，這就是“inplace”名稱的來(lái)源。
• 所以，我現(xiàn)在問(wèn)你茎杂，如果你有一個(gè) 1TB 的表错览，現(xiàn)在磁盤間是 1.2TB，能不能做一個(gè) inplace 的 DDL 呢煌往？
• 答案是不能倾哺。因?yàn)椋瑃mp_file 也是要占用臨時(shí)空間的刽脖。我們重建表的這個(gè)語(yǔ)句 alter table t engine=InnoDB羞海，其實(shí)隱含的意思是：

alter table t engine=innodb,ALGORITHM=inplace;

其對(duì)應(yīng)的是拷貝做法：

lter table t engine=innodb,ALGORITHM=copy;

• 當(dāng)你使用 ALGORITHM=copy 的時(shí)候，表示的是強(qiáng)制拷貝表曲管，對(duì)應(yīng)的流程就是非Online對(duì)應(yīng)的過(guò)程的操作過(guò)程却邓。
• 但這樣說(shuō)你可能會(huì)覺(jué)得，inplace 跟 Online 是不是就是一個(gè)意思院水？其實(shí)不是的腊徙，只是在重建表這個(gè)邏輯中剛好是這樣而已。比如檬某，如果要給 InnoDB 表的一個(gè)字段加全文索引撬腾，寫法是：

alter table t add FULLTEXT(field_name);

• 這個(gè)過(guò)程是 inplace 的，但會(huì)阻塞增刪改操作恢恼，是非 Online 的民傻。如果說(shuō)這兩個(gè)邏輯之間的關(guān)系是什么的話，可以概括為：
  • DDL 過(guò)程如果是 Online 的场斑，就一定是 inplace 的漓踢；
  • 反過(guò)來(lái)未必，也就是說(shuō) inplace 的 DDL和簸，有可能不是 Online 的彭雾。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空間索引 (SPATIAL index) 就屬于這種情況锁保。
• 從 MySQL 5.6 版本開(kāi)始，alter table t engine = InnoDB（也就是 recreate）默認(rèn)的就是上面Online DDL圖的流程了；
  • analyze table t 其實(shí)不是重建表爽柒，只是對(duì)表的索引信息做重新統(tǒng)計(jì)吴菠，沒(méi)有修改數(shù)據(jù)，這個(gè)過(guò)程中加了 MDL 讀鎖浩村；
  • optimize table t 等于 recreate+analyze做葵。

小結(jié)

• 通過(guò)本文我們知道，如果要收縮一個(gè)表心墅，只是 delete 掉表里面不用的數(shù)據(jù)的話酿矢，表文件的大小是不會(huì)變的，你還要通過(guò) alter table 命令重建表怎燥，才能達(dá)到表文件變小的目的瘫筐。這里介紹了重建表的兩種實(shí)現(xiàn)方式，Online DDL 的方式是可以考慮在業(yè)務(wù)低峰期使用的铐姚，而 MySQL 5.5 及之前的版本策肝，這個(gè)命令是會(huì)阻塞 DML 的，這個(gè)你需要特別小心隐绵。