1、應用背景

在日常工作開發中，在MySQL中，如果我們對大表頻繁進行insert和delete操作，那麼時間一長，這個表中會出現很多"空洞"，也就是表碎片。

碎片產生的原因是insert隨機值作為主鍵id，會產生很多資料頁分裂操作；而delete掉一些排列有序的主鍵值，這些被delete的空間不會直接釋放，而是僅僅進行delete的標記，這些空間如果不能被利用，那就會變成"空洞"。

2、重建表

關於重建表，這時候新建一張結構一樣的臨時表，把表內的資料匯入到臨時表，直接刪除舊錶，然後將臨時表替換為舊錶，從而釋放這些空餘的空間，讓資料變得"緊湊些"，完成重建操作。

我們其實可以透過如下命令來重建表：

alter table tableName engine=innodb

在MySQL5.5版本之前，這個命令的執行流程跟1操作差不多，區別只是在於這個臨時表不需要你直接建立，MySQL會自動完成轉存資料、交換表名、刪除舊錶的操作。

這個重建表的過程，在MySQL5.5之前，它的執行邏輯是下面這樣的：

1、假設原表是A，新建一個表table B，和表A的表結構保持一致；
2、按照主鍵順序，將表A的資料一行一行的讀出來，插入到表B裡面；
3、交換表A和表B的名稱。

3、重建實現最佳化

透過上面的介紹可以發現，花時間最多的步驟是往臨時表插入資料的過程，如果在這個過程中，有新的資料要寫入到表 A 的話，就會造成資料丟失。

因此，在整個 DDL 過程中，舊錶中不能有更新（也就是說，這個 DDL 不是 Online 的）。

在MySQL5.6及以後的版本里面，引入了Online DDL的方法，Online DDL的引入，使得上面的過程有了一點點不同，當執行如下命令的時候，

alter table tableName engine=innodb

MySQL5.6版本開始引入的Online DDL，對這個操作流程做了最佳化：

1、建立一個臨時檔案，掃描表A主鍵的所有資料頁；
2、用資料頁中表A的記錄生產B+樹，儲存到臨時檔案中；
3、生產臨時檔案的過程中，將所有對A的操作記錄在一個日誌檔案（row log）中，對應的是圖中state2的狀態；
4、臨時檔案生成後，將日誌檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表A相同的資料檔案，對應的就是圖中state3的狀態；
5、用臨時檔案替換表A的資料檔案。

執行alter語句時，需要獲取MDL寫鎖，但是這個寫鎖在真正複製資料之前就退化成讀鎖。為了實現Online，MDL讀鎖不會阻塞增刪改操作。

那為什麼要從寫鎖退化成讀鎖而不乾脆直接解除鎖呢？為了保護自己，禁止其他執行緒對這個表同時做DDL。

4、答疑解惑

關於重建表，相信大家還會有其他的疑惑，一起來總結下。

Q1、在MySQL5.5之前，我們使用臨時表作為重建的中間介質，在MySQL5.6之後，我們使用臨時檔案作為重建的中間介質，臨時表和臨時檔案的區別是？

A：臨時表是建立在server層面的，臨時檔案是建立在innodb層面的，所以Online DDL的整個過程都是在Innodb內部完成的，這種方法也稱之為"inplace"，相對應的，需要藉助server層面臨時表的過程，稱之為"Copy"。

Q2、假設我們有一個1TB的表，磁碟只有1.2TB，那麼還可以做inplace的DDL呢？

A：不可以，因為inplace方案中的臨時檔案也要佔用一定的空間。

Q3、inplace 方案進行的表重建操作，都是Online DDL麼？

A：不一定，例如增加全文索引的操作，這個操作是inplace的，但是會阻塞增刪改查操作，因此不是Online DDL。應該說：Online DDL一定是inplace的，但是inplace方案進行的操作，不一定是Online的。

Q4、某個表的大小是1TB，進行alter table A engine=Innodb之後，表的空間沒有縮小，反而增大了一點，這是為什麼？

A：可能是因為表之前剛剛進行過一次alter table的操作，而且表上面的併發增刪改比較多，在進行alter table 的過程中，這些操作都寫進了log中，從而導致表的實際大小會增加。

透過alter table 來實現重建表，同事大呼開眼界了

2、重建表

3、重建實現最佳化

4、答疑解惑

相關文章