最近有個上位機獲取下位機上報數據的項目，由于上報頻率比較頻繁且數據量大，導緻數據增長過快，磁盤占用多。

為了節約成本，定期進行數據備份，并通過delete删除表記錄。

項目中使用mysql作為數據庫，對于表來說，一般為表結構和表數據。表結構占用空間都是比較小的，一般都是表數據占用的空間。

當我們使用 delete删除數據時，确實删除了表中的數據記錄，但查看表文件大小卻沒什麼變化。

凡是使用過mysql，對B 樹肯定是有所耳聞的，MySQL INNODB 中采用了 B 樹作為存儲數據的結構，也就是常說的索引組織表，并且數據時按照頁來存儲的。因此在删除數據時，會有兩種情況：

• 删除數據頁中的某些記錄
• 删除整個數據頁的内容

比如想要删除 R4 這條記錄：

InnoDB 直接将 R4 這條記錄标記為删除，稱為可複用的位置。如果之後要插入 ID 在 300 到 700 間的記錄時，就會複用該位置。由此可見，磁盤文件的大小并不會減少。

通用删除整頁數據也将記錄标記删除，數據就複用用該位置，與删除默寫記錄不同的是，删除整頁記錄，當後來插入的數據不在原來的範圍時，都可以複用位置，而如果隻是删除默寫記錄，是需要插入數據符合删除記錄位置的時候才能複用。

因此，無論是數據行的删除還是數據頁的删除，都是将其标記為删除的狀态，用于複用，所以文件并不會減小。

DELETE隻是将數據标識位删除，并沒有整理數據文件，當插入新數據後，會再次使用這些被置為删除标識的記錄空間，可以使用OPTIMIZE TABLE來回收未使用的空間，并整理數據文件的碎片。

OPTIMIZE TABLE 表名;

注意：OPTIMIZE TABLE隻對MyISAM, BDB和InnoDB表起作用。

另外，也可以執行通過ALTER TABLE重建表

ALTER TABLE 表名 ENGINE=INNODB

有人會問OPTIMIZE TABLE和ALTER TABLE有什麼區别？

alter table t engine = InnoDB（也就是recreate），而optimize table t 等于recreate analyze

最後，再說一下Online DDL，dba的日常工作肯定有一項是ddl變更，ddl變更會鎖表，這個可以說是dba心中永遠的痛，特别是執行ddl變更，導緻庫上大量線程處于“Waiting for meta data lock”狀态的時候。因此在 5.6 版本後引入了 Online DDL。

Online DDL推出以前，執行ddl主要有兩種方式copy方式和inplace方式，inplace方式又稱為(fast index creation)。相對于copy方式，inplace方式不拷貝數據，因此較快。但是這種方式僅支持添加、删除索引兩種方式，而且與copy方式一樣需要全程鎖表，實用性不是很強。Online方式與前兩種方式相比，不僅可以讀，還可以支持寫操作。

執行online DDL語句的時候，使用ALGORITHM和LOCK關鍵字，這兩個關鍵字在我們的DDL語句的最後面，用逗号隔開即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM選項

• INPLACE：替換：直接在原表上面執行DDL的操作。
• COPY：複制：使用一種臨時表的方式，克隆出一個臨時表，在臨時表上執行DDL，然後再把數據導入到臨時表中，在重命名等。這期間需要多出一倍的磁盤空間來支撐這樣的 操作。執行期間，表不允許DML的操作。
• DEFAULT：默認方式，有MySQL自己選擇，優先使用INPLACE的方式。

LOCK選項

• SHARE：共享鎖，執行DDL的表可以讀，但是不可以寫。
• NONE：沒有任何限制，執行DDL的表可讀可寫。
• EXCLUSIVE：排它鎖，執行DDL的表不可以讀，也不可以寫。
• DEFAULT：默認值，也就是在DDL語句中不指定LOCK子句的時候使用的默認值。如果指定LOCK的值為DEFAULT，那就是交給MySQL子句去覺得鎖還是不鎖表。不建議使用，如果你确定你的DDL語句不會鎖表，你可以不指定lock或者指定它的值為default，否則建議指定它的鎖類型。

執行DDL操作時，ALGORITHM選項可以不指定，這時候MySQL按照INSTANT、INPLACE、COPY的順序自動選擇合适的模式。也可以指定ALGORITHM=DEFAULT，也是同樣的效果。如果指定了ALGORITHM選項，但不支持的話，會直接報錯。

OPTIMIZE TABLE 和 ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL，但依舊建議在業務訪問量低的時候使用

delete 删除數據時，其實對應的數據行并不是真正的删除，僅僅是将其标記成可複用的狀态，所以表空間不會變小。

可以重建表的方式，快速将delete數據後的表變小（OPTIMIZE TABLE 或ALTER TABLE），在 5.6 版本後，創建表已經支持 Online 的操作，但最好是在業務低峰時使用

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!