來源：www.toutiao.com/i6935264754059477542/
最近有個上位機獲取下位機上報資料的專案，由于上報頻率比較頻繁且資料量大，導致資料增長過快，磁盤占用多，
為了節約成本，定期進行資料備份，并通過delete洗掉表記錄，
明明已經執行了delete，可表檔案的大小卻沒減小，令人費解
專案中使用Mysql作為資料庫，對于表來說，一般為表結構和表資料，表結構占用空間都是比較小的，一般都是表資料占用的空間，
當我們使用 delete洗掉資料時，確實洗掉了表中的資料記錄，但查看表檔案大小卻沒什么變化，
Mysql資料結構
凡是使用過mysql，對B+樹肯定是有所耳聞的，MySQL InnoDB 中采用了 B+ 樹作為存盤資料的結構，也就是常說的索引組織表，并且資料時按照頁來存盤的，因此在洗掉資料時，會有兩種情況：
- 洗掉資料頁中的某些記錄
- 洗掉整個資料頁的內容
表檔案大小未更改和mysql設計有關
比如想要洗掉 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
最后，再說一下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 的操作，但最好是在業務低峰時使用
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2021最新版)
2.別在再滿屏的 if/ else 了，試試策略模式，真香！！
3.臥槽！Java 中的 xx ≠ null 是什么新語法？
4.Spring Boot 2.5 重磅發布，黑暗模式太炸了！
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/315862.html
標籤：Java