主頁(yè) > 知識(shí)庫(kù) > MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因

MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因

熱門標(biāo)簽:百度地圖圖標(biāo)標(biāo)注中心 石家莊電商外呼系統(tǒng) 芒果電話機(jī)器人自動(dòng)化 湖南人工外呼系統(tǒng)多少錢 廣東人工電話機(jī)器人 日照旅游地圖標(biāo)注 信陽(yáng)穩(wěn)定外呼系統(tǒng)運(yùn)營(yíng)商 申請(qǐng)外呼電話線路 南通自動(dòng)外呼系統(tǒng)軟件

對(duì)于運(yùn)行很長(zhǎng)時(shí)間的數(shù)據(jù)庫(kù)來(lái)說(shuō),往往會(huì)出現(xiàn)表占用存儲(chǔ)空間過(guò)大的問(wèn)題,可是將許多沒(méi)用的表刪除之后,表文件的大小并沒(méi)有改變,想解決這個(gè)問(wèn)題,就需要了解 InnoDB 如何回收表空間的。

對(duì)于一張表來(lái)說(shuō),占用空間重要分為兩部分,表結(jié)構(gòu)和表數(shù)據(jù)。通常來(lái)說(shuō),表結(jié)構(gòu)定義占用的空間很小。所以空間的問(wèn)題主要和表數(shù)據(jù)有關(guān)。

在 MySQL 8.0 前,表結(jié)構(gòu)存儲(chǔ)在以 .frm 為后綴的文件里。在 8.0,允許將表結(jié)構(gòu)定義在系統(tǒng)數(shù)據(jù)表中。

關(guān)于表數(shù)據(jù)的存放

可以將表數(shù)據(jù)存在共享表空間,或者單獨(dú)的文件中,通過(guò) innodb_file_per_table 來(lái)控制。

  • 如果為 OFF ,表示存在系統(tǒng)共享表空間中,和數(shù)據(jù)字典一起
  • 如果為 ON,每個(gè) InnoDB 表結(jié)構(gòu)存儲(chǔ)在 .idb 為后綴的文件中

在 5.6.6 以后,默認(rèn)值為 ON.

建議將該參數(shù)設(shè)置為 ON,這樣在不需要時(shí),通過(guò) drop table 命令,系統(tǒng)就會(huì)直接刪除該文件。

但在共享表空間中,即使表刪掉,空間也不會(huì)回收。

truncate = drop + create 

數(shù)據(jù)刪除流程

但有時(shí)使用 delete刪除數(shù)據(jù)時(shí),僅僅刪除的是某些行,但這可能就會(huì)出現(xiàn)表空間沒(méi)有被回收的情況。

我們知道,MySQL InnoDB 中采用了 B+ 樹作為存儲(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)容

比如想要?jiǎng)h除 R4 這條記錄:

InnoDB 直接將 R4 這條記錄標(biāo)記為刪除,稱為可復(fù)用的位置。如果之后要插入 ID 在 300 到 700 間的記錄時(shí),就會(huì)復(fù)用該位置。由此可見(jiàn),磁盤文件的大小并不會(huì)減少。

而且記錄的復(fù)用,只限于符合范圍條件的數(shù)據(jù)。之后要插入 ID 為 800 的記錄,R4 的位置就不能被復(fù)用了。

再比如要是刪除了整個(gè)數(shù)據(jù)頁(yè)的內(nèi)容,假設(shè)刪除 R3 R4 R5,為 Page A 數(shù)據(jù)頁(yè)。

這時(shí) InnoDB 就會(huì)將整個(gè) Page A 標(biāo)記為刪除狀態(tài),之后整個(gè)數(shù)據(jù)都可以被復(fù)用,沒(méi)有范圍的限制。比如要插入 ID=50 的內(nèi)容就可以直接復(fù)用。

并且如果兩個(gè)相鄰的數(shù)據(jù)頁(yè)利用率都很小,就會(huì)把兩個(gè)頁(yè)中的數(shù)據(jù)合到其中一個(gè)頁(yè)上,另一個(gè)頁(yè)標(biāo)記為可復(fù)用。

綜上,無(wú)論是數(shù)據(jù)行的刪除還是數(shù)據(jù)頁(yè)的刪除,都是將其標(biāo)記為刪除的狀態(tài),用于復(fù)用,所以文件并不會(huì)減小。對(duì)應(yīng)到具體的操作就是使用 delete 命令.

而且,我們還可以發(fā)現(xiàn),對(duì)于第一種刪除記錄的情況,由于復(fù)用時(shí)會(huì)有范圍的限制,所以就會(huì)出現(xiàn)很多空隙的情況,比如刪除 R4,插入的卻是 ID=800.

插入操作也會(huì)造成空隙

在插入數(shù)據(jù)時(shí),如果數(shù)據(jù)按照索引遞增順序插入,索引的結(jié)構(gòu)會(huì)是緊湊的。但如果是隨機(jī)插入的,很可能造成索引數(shù)據(jù)頁(yè)分裂。

比如給已滿的 Page A 插入數(shù)據(jù)。

由于 Page A 滿了,所以要申請(qǐng) Page B,調(diào)整 Page A 的過(guò)程到 Page B,這也稱為頁(yè)分裂。

結(jié)束后 Page A 就有了空隙。

另外對(duì)于更新操作也是,先刪除再插入,也會(huì)造成空隙。

進(jìn)而對(duì)于大量進(jìn)行增刪改的表,都有可能存在空洞。如果把空洞去掉,自然空間就被釋放了。

使用重建表

為了把表中的空隙去掉,這時(shí)就可以采用重新建一個(gè)與表 A 結(jié)構(gòu)相同的表 B,然后按照主鍵 ID 遞增的順序,把數(shù)據(jù)依次插入到 B 表中。

由于是順序插入,自然 B 表的空隙不存在,數(shù)據(jù)頁(yè)的利用率也更高。之后用表 B 代替表 A,好像起到了收縮表 A 空間的作用。

具體通過(guò):

alter table A engine=InnoDB

在 5.5 版本后,該命令和上面提到的流程差不多,而且 MySQL 會(huì)自己完成數(shù)據(jù),交換表名,刪除舊表的操作。

但這就有一個(gè)問(wèn)題,在 DDL 中,表 A 不能有更新,此時(shí)有數(shù)據(jù)寫入表 A 的話,就會(huì)造成數(shù)據(jù)丟失。

在 5.6 版本后引入了 Online DDL。

Online DDL

Online DDL 在其基礎(chǔ)上做了如下的更新:

重建表的過(guò)程如下:

  1. 建立一個(gè)臨時(shí)文件,掃描表 A 主鍵的所有數(shù)據(jù)頁(yè)。
  2. 用生成的數(shù)據(jù)頁(yè)生成 B+ 樹,存儲(chǔ)到臨時(shí)文件中。
  3. 生成臨時(shí)文件時(shí),如果有對(duì) A 的操作,將其記錄在日志文件中,對(duì)應(yīng)圖中 state 2 的狀態(tài)。
  4. 臨時(shí)文件生成后,將日志文件應(yīng)用到臨時(shí)文件中,得到與 A 表相同的數(shù)據(jù)文件,對(duì)應(yīng) state 3 狀態(tài)。
  5. 用臨時(shí)文件替換 A 表的數(shù)據(jù)文件。

由于 row log 日志文件存在,可以在重建表示,對(duì)表 A 進(jìn)行 DML 操作。

需要注意的是,在 alter 語(yǔ)句執(zhí)行前,會(huì)先申請(qǐng) MDL 寫鎖,但在拷貝數(shù)據(jù)前會(huì)退化成 MDL 讀鎖,從而支持 DML 操作。

至于為什么不大 MDL 去掉,是防止其他線程對(duì)這個(gè)表同時(shí)做 DDL 操作。

對(duì)于大表來(lái)說(shuō),該操作很耗 IO 和 CPU 資源,所以在線上操作時(shí),要控制操作時(shí)間。如果為了保證安全,推薦使用 gh-ost 來(lái)遷移。

Online 和 inplace

首先說(shuō)一下 inplace 和 copy 的區(qū)別:

在 Online DDL 中,表 A 重建后的數(shù)據(jù)放在 tmp_file 中,這個(gè)臨時(shí)文件是在 InnoDB 內(nèi)部創(chuàng)建出來(lái)的。整個(gè) DDL 在 InnoDB 內(nèi)部完成。進(jìn)而對(duì)于 Server 層來(lái)說(shuō),并沒(méi)有數(shù)據(jù)移動(dòng)到臨時(shí)表中,是一個(gè) "原地" 操作,所以叫 "inplace" .

而在之前普通的 DDL 中,創(chuàng)建后的表 A 是在 tmp_table 是 Server 創(chuàng)建的,所以叫 "copy"

對(duì)應(yīng)到語(yǔ)句其實(shí)就是:

-- alter table t engine=InnoDB 默認(rèn)為下面
alter table t engine=innodb,ALGORITHM=inplace;

-- 走的就是 server 拷貝的過(guò)程
alter table t engine=innodb,ALGORITHM=copy;

需要注意的是 inplace 和 Online 并不是對(duì)應(yīng)關(guān)系:

  1. DDL 過(guò)程是 Online,則一定是 inplace
  2. 如果是 inplace 的 DDL 不應(yīng)當(dāng)是 Online,如在 = 8.0, 添加全文索引和空間索引就屬于這種情況。

拓展

說(shuō)一下 optimize,analyze,alter table 三種重建表之間的區(qū)別:

  1. alter table t engine = InnoDB(也就是 recreate)默認(rèn)的是 Oline DDL 過(guò)程。
  2. analyze table t 不是重建表,僅僅是對(duì)表的索引信息做重新統(tǒng)計(jì),沒(méi)有修改數(shù)據(jù),期間加 MDL 讀鎖。
  3. optimize table t 等于上兩步的操作。

在事務(wù)里面使用 alter table 默認(rèn)會(huì)自動(dòng)提交事務(wù),保持事務(wù)一致性

如果有時(shí),在重建某張表后,空間不僅沒(méi)有變小,甚至還變大了一點(diǎn)點(diǎn)。這時(shí)因?yàn)?,重建的這張表本身沒(méi)有空隙,在 DDL 期間,剛好有一些 DML 執(zhí)行,引入了一些新的空隙。

而且 InnoDB 不會(huì)把整張表填滿,每個(gè)頁(yè)留下 1/16 給后續(xù)的更新用,所以可能遠(yuǎn)離是緊湊的,但重建后變成的稍有空隙。

總結(jié)

現(xiàn)在我們知道,在使用 delete 刪除數(shù)據(jù)時(shí),其實(shí)對(duì)應(yīng)的數(shù)據(jù)行并不是真正的刪除,InnoDB 僅僅是將其標(biāo)記成可復(fù)用的狀態(tài),所以表空間不會(huì)變小。

通常來(lái)說(shuō),在標(biāo)記復(fù)用空間時(shí)分為兩種,一種是僅將某些數(shù)據(jù)頁(yè)中的位置標(biāo)記為刪除狀態(tài),但這樣的位置只會(huì)在一定范圍內(nèi)使用,會(huì)出現(xiàn)空隙的情況。

另一種是將整個(gè)數(shù)據(jù)頁(yè)標(biāo)記成可復(fù)用的狀態(tài),這樣的數(shù)據(jù)頁(yè)沒(méi)有限制,可直接復(fù)用。

為了解決這個(gè)問(wèn)題,我們可以采用重建表的方式,其中在 5.6 版本后,創(chuàng)建表已經(jīng)支持 Online 的操作,但最后是在業(yè)務(wù)低峰時(shí)使用

以上就是MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因的詳細(xì)內(nèi)容,更多關(guān)于MySQL表文件大小的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • MyBatis批量插入/修改/刪除MySql數(shù)據(jù)
  • mysql利用mysqlbinlog命令恢復(fù)誤刪除數(shù)據(jù)的實(shí)現(xiàn)
  • mysql5.7.33誤刪除ibdata文件找回?cái)?shù)據(jù)的方法
  • mysql數(shù)據(jù)庫(kù)刪除重復(fù)數(shù)據(jù)只保留一條方法實(shí)例
  • mysql 大表批量刪除大量數(shù)據(jù)的實(shí)現(xiàn)方法
  • 淺談為什么MySQL不建議delete刪除數(shù)據(jù)
  • Python批量刪除mysql中千萬(wàn)級(jí)大量數(shù)據(jù)的腳本分享
  • Mysql刪除數(shù)據(jù)以及數(shù)據(jù)表的方法實(shí)例
  • MySQL 快速刪除大量數(shù)據(jù)(千萬(wàn)級(jí)別)的幾種實(shí)踐方案詳解
  • MySQL Delete 刪數(shù)據(jù)后磁盤空間未釋放的原因

標(biāo)簽:牡丹江 惠州 沈陽(yáng) 天津 阿里 公主嶺 呼和浩特 合肥

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因》,本文關(guān)鍵詞  MySQL,刪除,數(shù)據(jù),表,文件,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章