數(shù)據(jù)庫(kù) delete 表數(shù)據(jù)磁盤(pán)空間還是被一直占用為什么-

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

TABLE

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

TABLE

表名

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

TABLE

tbl_name

ADD

COLUMN

col_name

col_type,

ALGORITHM=INPLACE,

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