一、問(wèn)題背景

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

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

你是不是也遇到過(guò)：

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

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

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

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

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

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

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

-- 插入200萬(wàn)條測(cè)試數(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ǔ)過(guò)程
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ù)正確）

靈魂拷問(wèn)：刪了 190 萬(wàn)條數(shù)據(jù)，為啥空間沒(méi)釋放？

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

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

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

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

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

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

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

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

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

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

為什么不直接物理刪除？

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

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

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

• 標(biāo)記刪除的記錄：數(shù)據(jù)頁(yè)空間被標(biāo)記為“空洞”，新數(shù)據(jù)可覆蓋寫(xiě)入（空間復(fù)用）。
• 碎片累積：頻繁增刪后，數(shù)據(jù)頁(yè)內(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ù)庫(kù) “瘦身成功”

我們看下執(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ù)場(chǎng)景選擇合適的空間釋放方案。

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