快捷導(dǎo)航

MySQL?13表數(shù)據(jù)刪掉一半表文件大小不變的原因分析

更新時(shí)間：2025年07月14日 09:31:19 作者：san-mu

這篇文章主要介紹了MySQL?13表數(shù)據(jù)刪掉一半表文件大小不變的原因分析,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧

一個(gè)InnoDB表包含兩部分：表結(jié)構(gòu)定義和數(shù)據(jù)。在MySQL 8.0版本前，表結(jié)構(gòu)存在以.frm為后綴的文件里。之后的版本允許把表結(jié)構(gòu)定義放在系統(tǒng)數(shù)據(jù)表中。由于表結(jié)構(gòu)定義占用空間很小，所以主要討論表數(shù)據(jù)。

接下來，先說明為什么簡(jiǎn)單刪除表數(shù)據(jù)達(dá)不到表空間回收的效果，再介紹正確回收空間的方法。

參數(shù)innodb_file_per_table

表數(shù)據(jù)既可以存在共享表空間里，也可以是單獨(dú)的文件，這由參數(shù)innodb_file_per_table控制：

設(shè)為OFF，表示表數(shù)據(jù)放在系統(tǒng)共享表空間，也就是跟數(shù)據(jù)字典放在一起；
設(shè)為ON，表示每個(gè)InnoDB表數(shù)據(jù)存儲(chǔ)在一個(gè)以.ibd為后綴的文件中。

從MySQL 5.6.6版本開始，默認(rèn)值為ON。建議也是使用ON，因?yàn)橐粋€(gè)表單獨(dú)存儲(chǔ)為一個(gè)文件更容易管理，而且在不需要該表時(shí)通過drop table命令，系統(tǒng)就會(huì)直接刪除文件；如果是放在共享表空間中，即使表刪除，空間也是不會(huì)回收的。

接下來的討論也是基于innodb_file_per_table=ON的設(shè)置。

在刪除整張表的時(shí)候，可以使用drop table命令回收表空間。但是，平時(shí)更多的場(chǎng)景是刪除某些行。

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

為了搞懂刪除部分行的場(chǎng)景，需要先從數(shù)據(jù)刪除流程開始說。

看一下InnoDB中一個(gè)索引的示意圖：

假設(shè)要?jiǎng)h除R4這個(gè)記錄，InnoDB只會(huì)把R4這個(gè)記錄標(biāo)記為刪除。如果之后插入一個(gè)ID在300-600間的記錄，可能會(huì)復(fù)用這個(gè)位置，但磁盤文件的大小不會(huì)縮小。

那么如果將一個(gè)數(shù)據(jù)頁(yè)上的所有記錄都刪除，會(huì)怎么樣呢？答案是整個(gè)數(shù)據(jù)頁(yè)可以復(fù)用。

但是數(shù)據(jù)頁(yè)的復(fù)用和記錄的復(fù)用還是不一樣的。記錄的復(fù)用只限于符合范圍條件的數(shù)據(jù)，而一旦一個(gè)數(shù)據(jù)頁(yè)可以復(fù)用，所有范圍的數(shù)據(jù)都可以使用。比如在上面的索引中，若page A是可復(fù)用的，ID=50這樣的記錄也能使用該頁(yè)。

如果相鄰兩個(gè)數(shù)據(jù)頁(yè)利用率都很小，系統(tǒng)會(huì)把這兩個(gè)頁(yè)上的數(shù)據(jù)合到其中一個(gè)頁(yè)上，另一個(gè)頁(yè)就會(huì)被標(biāo)記為可以復(fù)用。

進(jìn)一步地，如果用delete命令刪除整個(gè)表的數(shù)據(jù)，那么所有數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為可復(fù)用，而磁盤上的文件并不會(huì)變小。也就是說，delete命令不能回收表空間，這些可以復(fù)用卻沒被使用的空間，看起來就像“空洞”。

實(shí)際上不止刪除數(shù)據(jù)會(huì)造成空洞，插入數(shù)據(jù)也會(huì)。如果數(shù)據(jù)的插入是隨機(jī)的，可能造成索引的數(shù)據(jù)頁(yè)分裂。比如在上面的索引中，假設(shè)page A已滿，這時(shí)若要再插入一行數(shù)據(jù)ID=550：

當(dāng)page A已滿的情況下進(jìn)行插入，就必須再申請(qǐng)一個(gè)新的頁(yè)面page B來保存數(shù)據(jù)。由于頁(yè)分裂導(dǎo)致部分?jǐn)?shù)據(jù)移動(dòng)，page A就出現(xiàn)了空洞。

除了插入，由于更新可以看為刪除+插入，也可能造成空洞。即，增刪改都可能出現(xiàn)空洞。所以，如果能把這些空洞去掉，就能達(dá)到收縮表空間的目的。

重建表就可以達(dá)到這樣的目的。

重建表

假設(shè)現(xiàn)在有一個(gè)表A，需要去除其中的空洞，有什么辦法呢？

可以新建一個(gè)與表A結(jié)構(gòu)相同的表B，然后按照主鍵ID遞增的順序，把數(shù)據(jù)逐行從表A讀取出來再插入到表B中。由于表B是新建的表，所以沒有表A上的空洞。把表B作為臨時(shí)表，數(shù)據(jù)從表A導(dǎo)入表B后，再用表B替換表A，從效果上就是表A沒有空洞了。

可以使用alter table A engine=InnoDB的命令重建表。在MySQL 5.5版本前，這個(gè)命令的執(zhí)行流程和上面描述的差不多，區(qū)別只是不需要自己創(chuàng)建臨時(shí)表，MySQL會(huì)自動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。

在往臨時(shí)表插入數(shù)據(jù)的過程中，如果有新的數(shù)據(jù)要寫入表A，會(huì)造成數(shù)據(jù)損失，因此整個(gè)DDL的過程中，表A不能有更新，即DDL不是Online的。

而MySQL 5.6開始的版本引入了Online DDL，對(duì)這個(gè)操作流程做了優(yōu)化。新的流程為：

建立一個(gè)臨時(shí)文件；
掃描表A主鍵的所有數(shù)據(jù)頁(yè)，用里面的記錄生成B+樹，存儲(chǔ)到臨時(shí)文件中；
生成臨時(shí)文件的過程中，將所有對(duì)A的操作記錄在一個(gè)日志文件（row log）中，對(duì)應(yīng)下圖中state 2的狀態(tài)；
臨時(shí)文件生成以后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表A相同的臨時(shí)文件；
用臨時(shí)文件替換表A。

該操作流程由于日志文件和重放操作的功能，在重建表的過程中允許對(duì)表A做增刪改操作。

當(dāng)然，由于對(duì)表做改動(dòng)，會(huì)有MDL鎖的存在。alter語(yǔ)句在啟動(dòng)時(shí)會(huì)獲取MDL寫鎖，但這個(gè)鎖在真正拷貝數(shù)據(jù)之前就會(huì)退化成讀鎖，目的是禁止其他線程對(duì)這個(gè)表同時(shí)做DDL，又不會(huì)阻塞增刪改操作。

對(duì)于一個(gè)大表來說，Online DDL最耗時(shí)的過程就是拷貝數(shù)據(jù)到臨時(shí)表的過程，所以相對(duì)整個(gè)DDL過程來說，寫鎖鎖住的時(shí)間非常短，可以認(rèn)為是Online的。

需要說明的是，上述這些重建方法都會(huì)掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件，對(duì)于很大的表來說，該操作很消耗IO和CPU資源。因此，如果是線上服務(wù)需要控制操作時(shí)間，推薦使用開源的gh-ost來做。

Online和inplace

說到Online，再講一個(gè)容易混淆的概念inplace。

在早版本的重建表過程中，表A數(shù)據(jù)導(dǎo)出來的存放位置叫做tmp_table，這個(gè)臨時(shí)表是在Server層創(chuàng)建的。

而在后面的版本，表A重建出來的數(shù)據(jù)是放在tmp_file里的（見前面的圖），這個(gè)臨時(shí)文件是InnoDB在內(nèi)部創(chuàng)建出來的。由于整個(gè)DDL過程在InnoDB內(nèi)部完成，對(duì)于Server層來說，沒有把數(shù)據(jù)挪動(dòng)到臨時(shí)表，是一個(gè)“原地”操作，因此叫inplace。

那么假如表大小為1TB，磁盤空間為1.2TB，是否能做inplace的DDL呢？答案是不行的，因?yàn)閠mp_file會(huì)占用臨時(shí)空間。

重建表的完整語(yǔ)句其實(shí)是下面這樣：

alter table t engine=innodb,ALGORITHM=inplace;
alter table t engine=innodb,ALGORITHM=copy;

其中，copy表示強(qiáng)制拷貝表，即使用臨時(shí)表；inplace表示使用臨時(shí)文件。

那是否表示，inplace就是Online？也不是，只是在重建表這個(gè)邏輯中剛好是這樣。

如果說這兩個(gè)邏輯之間的關(guān)系是什么，可以概括為：

DDL過程如果是Online的，就一定是inplace的；
反之不正確，inplace的DDL，不一定是Online的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空間索引 (SPATIAL index) 就屬于這種情況。比如要給InnoDB表的一個(gè)字段加全文索引，過程是inplace的，但會(huì)阻塞增刪改。

到此這篇關(guān)于MySQL 13 為什么表數(shù)據(jù)刪掉一半，表文件大小不變？的文章就介紹到這了,更多相關(guān)mysql表數(shù)據(jù)刪掉一半表文件大小不變內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: