1.背景
在完成一個(gè)分表項(xiàng)目后，發(fā)現(xiàn)分表的數(shù)據(jù)遷移后，新庫(kù)所需的存儲(chǔ)容量遠(yuǎn)大于原本兩張表的大小。在做了一番查詢了解后，完成了優(yōu)化。

回過(guò)頭來(lái)，需要進(jìn)一步了解下為什么會(huì)出現(xiàn)這樣的情況。

與標(biāo)題的問(wèn)題的類似問(wèn)題還有，為什么表數(shù)據(jù)內(nèi)容刪除了而表大小沒(méi)有變化。其本質(zhì)都是一樣的。

要回答這些問(wèn)題，我們需要從mysql的索引模型談起。

2.InnoDB 的索引模型
在 MySQL 中，索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的，所以并沒(méi)有統(tǒng)一的索引標(biāo)準(zhǔn)，即不同存儲(chǔ)引擎的索引的工作方式并不一樣。

而即使多個(gè)存儲(chǔ)引擎支持同一種類型的索引，其底層的實(shí)現(xiàn)也可能不同。由于 InnoDB 存儲(chǔ)引擎在 MySQL 數(shù)據(jù)庫(kù)中使用最為廣泛，所以接下來(lái)就以 InnoDB 為例，分析其中的索引模型。

在 InnoDB 中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表。而InnoDB中，使用了 B+ 樹索引模型，所以數(shù)據(jù)都是存儲(chǔ)在 B+ 樹中的，每一個(gè)索引會(huì)對(duì)應(yīng)一顆B+樹。

假設(shè)，我們有一個(gè)主鍵列為 ID 的表，表中有字段 k，并且在 k 上有索引，建表語(yǔ)句如下

• CREATE TABLE `t` (
• `id` int(11) NOT NULL,
• `k` int(11) NOT NULL,
• `name` varchar(16) DEFAULT NULL,
• PRIMARY KEY (`id`),
• KEY `k` (`k`)
• ) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中 R1~R5 的 (ID,k) 值分別為 (10,1)、(20,2)、(30,3)、(50,5) 和 (70,7)，索引id和索引k的B+樹的示例示意圖如下。

根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類型分為主鍵索引和非主鍵索引，主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)R1~R5，非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。

從圖中可以看出，基于非主鍵索引的查詢需要多掃描一棵索引樹才能找到對(duì)應(yīng)的數(shù)據(jù)。提一句題外話，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢。

3.索引維護(hù)
B+ 樹為了維護(hù)索引有序性，在增刪改數(shù)據(jù)的時(shí)候需要做必要的維護(hù)。

假設(shè)，我們要?jiǎng)h掉 R4 這個(gè)記錄，InnoDB 引擎只會(huì)把 R4 這個(gè)記錄標(biāo)記為刪除。如果之后要再插入一個(gè) ID 在 300 和 600 之間的記錄時(shí)，可能會(huì)復(fù)用這個(gè)位置。

如果刪掉了一個(gè)數(shù)據(jù)頁(yè)上的所有記錄，那么整個(gè)數(shù)據(jù)頁(yè)就能被復(fù)用了。進(jìn)一步地，如果我們用 delete 命令把整個(gè)表的數(shù)據(jù)刪除呢？結(jié)果就是，這個(gè)表相關(guān)的所有的數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為可復(fù)用。

但是，無(wú)論如何，磁盤文件的大小并不會(huì)縮小。

這些被標(biāo)記為可復(fù)用，而并沒(méi)有實(shí)際被使用的空間，就是一些“存儲(chǔ)空洞”。

實(shí)際上，不止是刪除數(shù)據(jù)會(huì)造成空洞，插入數(shù)據(jù)也會(huì)。

以上圖為例，如果插入新的行 ID 值為 80，則只需要在 R5 的記錄后面插入一個(gè)新記錄。

如果新插入的 ID 值為 60，就相對(duì)麻煩了，需要邏輯上挪動(dòng)后面的數(shù)據(jù)，空出位置。

而更糟的情況是，如果 R5 所在的數(shù)據(jù)頁(yè)已經(jīng)滿了，根據(jù) B+ 樹的算法，這時(shí)候需要申請(qǐng)一個(gè)新的數(shù)據(jù)頁(yè)，然后挪動(dòng)部分?jǐn)?shù)據(jù)過(guò)去。這個(gè)過(guò)程稱為頁(yè)分裂。在這種情況下，性能自然會(huì)受影響。

除了性能外，頁(yè)分裂操作還影響數(shù)據(jù)頁(yè)的利用率。原本放在一個(gè)頁(yè)的數(shù)據(jù)，現(xiàn)在分到兩個(gè)頁(yè)中，插入一條記錄竟然使得整體空間利用率降低大約 50%。

可以看到，由于 page 2 滿了，再插入一個(gè) ID 是 60 的數(shù)據(jù)時(shí)，就不得不再申請(qǐng)一個(gè)新的頁(yè)面 page 3 來(lái)保存數(shù)據(jù)了。

頁(yè)分裂完成后，page 2 的末尾就留下了空洞（注意：實(shí)際上，可能不止 1 個(gè)記錄的位置是空洞）。

另外，更新索引上的值，可以理解為刪除一個(gè)舊的值，再插入一個(gè)新值。不難理解，這也是會(huì)造成空洞的。

因此，大量的增刪改之后的表，都是可能存在很大的“數(shù)據(jù)空洞”的。

因此，我們就能解釋，為什么分表后的總存儲(chǔ)變大了。

因?yàn)榉直砗螅枰獜睦蠋?kù)全量同步數(shù)據(jù)到新庫(kù)，數(shù)據(jù)同步平臺(tái)開啟多個(gè)線程進(jìn)行同步，插入各個(gè)分表并不是按照遞增的順序插入的，因此，會(huì)產(chǎn)生巨量的“數(shù)據(jù)空洞”，造成存儲(chǔ)空間變大。

如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。而重建表就能達(dá)到這樣的目的。

4.重建表
如果我們手動(dòng)重建一張表，可以新建一個(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ù)、交換表名、刪除舊表的操作。顯然，花時(shí)間最多的步驟是往臨時(shí)表插入數(shù)據(jù)的過(guò)程，如果在這個(gè)過(guò)程中，有新的數(shù)據(jù)要寫入到表 A 的話，就會(huì)造成數(shù)據(jù)丟失。因此，在整個(gè) DDL 過(guò)程中，表 A 中不能有更新。也就是說(shuō)，這個(gè) DDL 不是 Online 的。

MySQL 5.6 版本開始引入的 Online DDL，對(duì)這個(gè)操作流程做了優(yōu)化。

• 建立一個(gè)臨時(shí)文件，掃描表 A 主鍵的所有數(shù)據(jù)頁(yè)；
• 用數(shù)據(jù)頁(yè)中表 A 的記錄生成 B+ 樹，存儲(chǔ)到臨時(shí)文件中；
• 生成臨時(shí)文件的過(guò)程中，將所有對(duì) A 的操作記錄在一個(gè)日志文件（row log）中；
• 臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表 A 相同的數(shù)據(jù)文件；(應(yīng)用row log的過(guò)程可能又回有頁(yè)分裂)
• 用臨時(shí)文件替換表 A 的數(shù)據(jù)文件。

可以看到，在這個(gè)過(guò)程中，由于日志文件記錄和重放操作這個(gè)功能的存在，這個(gè)方案在重建表的過(guò)程中，允許對(duì)表 A 做增刪改操作。這也就是 Online DDL 名字的來(lái)源。

需要補(bǔ)充說(shuō)明的是，上述的這些重建方法都會(huì)掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件。對(duì)于很大的表來(lái)說(shuō)，這個(gè)操作是很消耗 IO 和 CPU 資源的。因此，如果是線上服務(wù)，你要很小心地控制操作時(shí)間。

optimize table、analyze table 和 alter table 這三種方式重建表的區(qū)別：

• 從 MySQL 5.6 版本開始，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。

參考文獻(xiàn)：

《MySQL實(shí)戰(zhàn)45講》

《MySQL技術(shù)內(nèi)幕》