數據庫 delete 表數據磁盤空間還是被一直占用為什么-

面試官問：數據庫delete表數據，磁盤空間還是被一直占用，為什么?最近有個上位機獲取下位機上報數據的項目，由于上報頻率比較頻繁且數據量大，導致數據增長過快，磁盤占用多。為了節(jié)約成本，定期進行數據備份，并通過delete刪除表記錄。明明已經執(zhí)行了delete，可表文件的大小卻沒減小，令人費解項目中使用Mysql作為數據庫，對于表來說，一般為表結構和表數據。表結構占用空間都是比較小的，一般都是表數據占用的空間。當我們使用delete刪除數據時，確實刪除了表中的數據記錄，但查看表文件大小卻沒什么變化。Mysql數據結構凡是使用過mysql，對B+樹肯定是有所耳聞的，MySQLInnoDB中采用了B+樹作為存儲數據的結構，也就是常說的索引組織表，并且數據時按照頁來存儲的。因此在刪除數據時，會有兩種情況：刪除數據頁中的某些記錄刪除整個數據頁的內容表文件大小未更改和mysql設計有關比如想要刪除R4這條記錄：InnoDB直接將R4這條記錄標記為刪除，稱為可復用的位置。如果之后要插入ID在300到700間的記錄時，就會復用該位置。由此可見，磁盤文件的大小并不會減少。通用刪除整頁數據也將記錄標記刪除，數據就復用用該位置，與刪除默寫記錄不同的是，刪除整頁記錄，當后來插入的數據不在原來的范圍時，都可以復用位置，而如果只是刪除默寫記錄，是需要插入數據符合刪除記錄位置的時候才能復用。因此，無論是數據行的刪除還是數據頁的刪除，都是將其標記為刪除的狀態(tài)，用于復用，所以文件并不會減小。那怎么才能讓表大小變小DELETE只是將數據標識位刪除，并沒有整理數據文件，當插入新數據后，會再次使用這些被置為刪除標識的記錄空間，可以使用OPTIMIZETABLE來回收未使用的空間，并整理數據文件的碎片。OPTIMIZE

TABLE

表名;注意：OPTIMIZETABLE只對MyISAM,BDB和InnoDB表起作用。另外，也可以執(zhí)行通過ALTERTABLE重建表ALTER

TABLE

表名

ENGINE=INNODB有人會問OPTIMIZETABLE和ALTERTABLE有什么區(qū)別？altertabletengine=InnoDB（也就是recreate），而optimizetablet等于recreate+analyzeOnlineDDL最后，再說一下OnlineDDL，dba的日常工作肯定有一項是ddl變更，ddl變更會鎖表，這個可以說是dba心中永遠的痛，特別是執(zhí)行ddl變更，導致庫上大量線程處于“Waitingformetadatalock”狀態(tài)的時候。因此在5.6版本后引入了OnlineDDL。在公眾號后端架構師后臺回復“架構整潔”，獲取一份驚喜禮包。OnlineDDL推出以前，執(zhí)行ddl主要有兩種方式copy方式和inplace方式，inplace方式又稱為(fastindexcreation)。相對于copy方式，inplace方式不拷貝數據，因此較快。但是這種方式僅支持添加、刪除索引兩種方式，而且與copy方式一樣需要全程鎖表，實用性不是很強。Online方式與前兩種方式相比，不僅可以讀，還可以支持寫操作。執(zhí)行onlineDDL語句的時候，使用ALGORITHM和LOCK關鍵字，這兩個關鍵字在我們的DDL語句的最后面，用逗號隔開即可。示例如下：ALTER

TABLE

tbl_name

ADD

COLUMN

col_name

col_type,

ALGORITHM=INPLACE,

LOCK=NONE;ALGORITHM選項INPLACE：替換：直接在原表上面執(zhí)行DDL的操作。COPY：復制：使用一種臨時表的方式，克隆出一個臨時表，在臨時表上執(zhí)行DDL，然后再把數據導入到臨時表中，在重命名等。這期間需要多出一倍的磁盤空間來支撐這樣的操作。執(zhí)行期間，表不允許DML的操作。DEFAULT：默認方式，有MySQL自己選擇，優(yōu)先使用INPLACE的方式。LOCK選項SHARE：共享鎖，執(zhí)行DDL的表可以讀，但是不可以寫。NONE：沒有任何限制，執(zhí)行DDL的表可讀可寫。EXCLUSIVE：排它鎖，執(zhí)行DDL的表不可以讀，也不可以寫。DEFAULT：默認值，也就是在DDL語句中不指定LOCK子句的時候使用的默認值。如果指定LOCK的值為DEFAULT，那就是交給MySQL子句去覺得鎖還是不鎖表。不建議使用，如果你確定你的DDL語句不會鎖表，你可以不指定lock或者指定它的值為default，否則建議指定它的鎖類型。執(zhí)行DDL操作時，ALGORITHM選項可以不指定，這時候MySQL按照INSTANT、INPLACE、COPY的順序自動選擇合適的模式。也可以指定ALGORITHM=DEFAULT，也是同樣的效果。如果指定了ALGORITHM選項，但不支持的話，會直接報錯。OPTIMIZETABLE和ALTERTABLE表名ENGINE=INNODB都支持OlineDDL，但依舊建議在業(yè)務訪問量低的時候使用總結delete刪除數據時，其實對應的數據行并不是真正的刪除，僅僅是將其標記成