刪除大量數(shù)據(jù)后，數(shù)據(jù)庫文件為何紋絲不動(dòng)？MySQL 存儲(chǔ)機(jī)制大揭秘

作者：徐述 2025-06-13 09:33:45

數(shù)據(jù)庫

通過理解InnoDB存儲(chǔ)機(jī)制，合理運(yùn)用定期監(jiān)控碎片率、分區(qū)表，可有效避免刪除數(shù)據(jù)后表文件“虛胖”問題，提升數(shù)據(jù)庫存儲(chǔ)效率。?

一、問題背景

“刪了90%數(shù)據(jù)，數(shù)據(jù)庫文件為啥紋絲不動(dòng)？這是MySQL的bug嗎？”

上周一位讀者面試被問懵了，這個(gè)問題也戳中了很多人的痛點(diǎn)——明明刪了大把數(shù)據(jù)，硬盤空間死活不釋放！

你是不是也遇到過：

執(zhí)行DELETE后，磁盤空間未釋放
.ibd文件大小不變，運(yùn)維報(bào)警頻發(fā)
明明數(shù)據(jù)量減少，統(tǒng)計(jì)信息卻 “巋然不動(dòng)”

別慌，這真不是Bug！而是 InnoDB 存儲(chǔ)引擎的底層設(shè)計(jì)機(jī)制決定的。今天就來扒開 InnoDB 的底層邏輯，教你 3 招馴服 “頑固” 的數(shù)據(jù)庫文件。

二、刪數(shù)據(jù)≠丟空間：MySQL 的 “假刪除” 套路

先看一組顛覆認(rèn)知的實(shí)驗(yàn)：

Step 1：創(chuàng)建 200 萬條數(shù)據(jù)的表

-- 創(chuàng)建測試數(shù)據(jù)庫
CREATEDATABASEtest;
-- 創(chuàng)建測試表
CREATETABLE test_demo (
    idINT PRIMARY KEY AUTO_INCREMENT,
    nameVARCHAR(100),
    contentTEXT,
    create_time DATETIME
) ENGINE=InnoDB;

插入測試數(shù)據(jù)：

-- 插入200萬條測試數(shù)據(jù)
DELIMITER //
CREATEPROCEDURE insert_test_data()
BEGIN
    DECLARE i INTDEFAULT1;
    WHILE i <= 2000000 DO
        INSERTINTO test_demo (name, content, create_time)
        VALUES (
            CONCAT('name_', i),
            REPEAT('x', 1000),  -- 每條記錄約1KB
            NOW()
        );
        SET i = i + 1;
    ENDWHILE;
END //
DELIMITER ;

-- 執(zhí)行存儲(chǔ)過程
CALL insert_test_data();

Step 2：查看初始文件大小（約 1GB）

-- 查看表空間文件大小
SELECT 
    table_name,
    data_length/1024/1024 as data_size_mb,
    index_length/1024/1024 as index_size_mb
FROM information_schema.tables 
WHERE table_schema = 'test' 
AND table_name = 'test_demo';

Step 3：刪除 99% 數(shù)據(jù)（僅保留前 100 條）

-- 刪除id大于100的記錄
DELETE FROM test_demo WHERE id >100;

Step 4：查看文件大小

.ibd文件物理大小仍≈1GB（磁盤未釋放）
SELECT COUNT(*)返回 100 條（邏輯數(shù)據(jù)正確）

靈魂拷問：刪了 190 萬條數(shù)據(jù)，為啥空間沒釋放？

三、InnoDB 存儲(chǔ)的 3 個(gè) “反直覺” 設(shè)計(jì)

1. 數(shù)據(jù)頁：最小存儲(chǔ)單位的 “空間壟斷”

每個(gè)數(shù)據(jù)頁固定 16KB，相當(dāng)于圖書館的書架格子
刪除 1 條記錄（可能只有 KB 級(jí)），不會(huì)釋放整個(gè)數(shù)據(jù)頁（16KB）
頁內(nèi)空洞累積，導(dǎo)致文件 “虛胖”

InnoDB 數(shù)據(jù)頁的內(nèi)部結(jié)構(gòu)：

(1) 記錄在頁中的存儲(chǔ)

還記得之前我們介紹的InnoDB 記錄結(jié)構(gòu)嗎？

從圖中我們可以看到，InnoDB 的 COMPACT 行格式確實(shí)分為兩個(gè)主要部分：

記錄的額外信息
記錄的真實(shí)數(shù)據(jù)

關(guān)于刪除的秘密其實(shí)藏在記錄頭信息中。

2. DELETE 的本質(zhì)：標(biāo)記刪除而非物理刪除

操作	本質(zhì)行為	空間釋放
`DELETE FROM t`	將記錄頭信息中的`delete_mask`標(biāo)記為1（標(biāo)記為“可復(fù)用”）	? 不釋放
`TRUNCATE TABLE`	清空所有數(shù)據(jù)頁，重建表空間	? 釋放

為什么不直接物理刪除？

事務(wù)安全優(yōu)先：寧肯占空間，不能丟數(shù)據(jù)。

若物理刪除數(shù)據(jù)，事務(wù)回滾時(shí)無法恢復(fù)（違反 ACID）
標(biāo)記刪除是 “軟刪除”，數(shù)據(jù)頁可隨時(shí)恢復(fù)（通過 undo 日志）
這就是為什么ROLLBACK能秒級(jí)恢復(fù)數(shù)據(jù) —— 因?yàn)閿?shù)據(jù)根本沒被物理刪除

空間復(fù)用 vs 碎片累積

標(biāo)記刪除的記錄：數(shù)據(jù)頁空間被標(biāo)記為“空洞”，新數(shù)據(jù)可覆蓋寫入（空間復(fù)用）。
碎片累積：頻繁增刪后，數(shù)據(jù)頁內(nèi)空洞增多，導(dǎo)致.ibd文件“虛胖”（實(shí)際數(shù)據(jù)量小，但文件占用大）。

3. 預(yù)分配策略：空間只增不減的 “霸道總裁”

InnoDB 按innodb_autoextend_increment（默認(rèn) 64MB）自動(dòng)擴(kuò)展表空間
擴(kuò)展后即使數(shù)據(jù)刪除，空間也不會(huì)還給系統(tǒng)（文件系統(tǒng)不支持收縮）
就像買房時(shí)買了 120㎡，住了 50㎡后想退 70㎡—— 不可能

四、實(shí)戰(zhàn)攻略：三招讓數(shù)據(jù)庫 “瘦身成功”

場景	方案	命令	原理	注意事項(xiàng)
緊急清空全表（數(shù)據(jù)可丟）	`TRUNCATE TABLE`	`TRUNCATE TABLE your_table;`	銷毀并重建表空間，釋放所有空間	不可逆，適用于日志表等場景
重建表清理碎片（可停機(jī)）	`ALTER TABLE ... ENGINE=InnoDB`	`ALTER TABLE your_table ENGINE=InnoDB;`	重建表空間，回收空洞和碎片	鎖表，大表需在低峰期操作
分區(qū)表刪除（歷史數(shù)據(jù)歸檔）	分區(qū)刪除	`ALTER TABLE orders DROP PARTITION p_old;`	刪除指定分區(qū)，釋放對(duì)應(yīng)空間	需提前設(shè)計(jì)分區(qū)策略

我們看下執(zhí)行后的效果：

ALTER TABLE test_demo ENGINE=INNODB;

五、總結(jié)

本質(zhì)原因：DELETE是邏輯刪除，空間釋放需依賴重建表或分區(qū)操作。
核心認(rèn)知：MySQL優(yōu)先保證事務(wù)安全和性能，而非實(shí)時(shí)回收空間。
面試要點(diǎn)：需清晰區(qū)分“標(biāo)記刪除”與“物理刪除”，并能結(jié)合業(yè)務(wù)場景選擇合適的空間釋放方案。

通過理解InnoDB存儲(chǔ)機(jī)制，合理運(yùn)用定期監(jiān)控碎片率、分區(qū)表，可有效避免刪除數(shù)據(jù)后表文件“虛胖”問題，提升數(shù)據(jù)庫存儲(chǔ)效率。

責(zé)任編輯：趙寧寧來源：程序員徐述