這篇文章，我將從 InnoDB 存儲(chǔ)空間分配、DELETE 對(duì)性能的影響 以及 最佳實(shí)踐建議 三個(gè)角度，逐步剖析為什么不推薦直接使用 DELETE 刪除大批量數(shù)據(jù)。

一、InnoDB 存儲(chǔ)架構(gòu)概覽

• 邏輯結(jié)構(gòu)

  • 表空間 (Tablespace)
  • 段 (Segment)
  • Extent（區(qū)）：每個(gè) Extent 包含 32 個(gè)頁(yè) (Page)。
  • 頁(yè) (Page)：InnoDB 的最小 I/O 單位，默認(rèn) 16KB。

• 物理結(jié)構(gòu)

  • 數(shù)據(jù)文件 (.ibd / ibdata1)：存儲(chǔ)表、索引和字典元數(shù)據(jù)。
  • 日志文件 (ib_logfile*)：記錄頁(yè)的修改，用于崩潰恢復(fù)。

Extent 自動(dòng)擴(kuò)展策略

1. 初始分配為 1 個(gè) Extent
2. 若總表空間 < 32MB，每次 +1 個(gè) Extent
3. 大于 32MB，則每次 +4 個(gè) Extent

二、InnoDB 表空間類型

1. 系統(tǒng)表空間 (ibdata1)，保存內(nèi)部字典等元數(shù)據(jù)。
2. 獨(dú)立表空間（innodb_file_per_table=ON），每個(gè)表一個(gè) .ibd 文件。
3. Undo 表空間，存儲(chǔ) MVCC 的回滾段。

從 MySQL 8.0 起，支持自定義通用表空間：

CREATE TABLESPACE tbs_hot
  ADD DATAFILE '/hot_data/tbs_hot01.dbf'
  INITIAL_SIZE = 10G
  AUTOEXTEND_SIZE = 1G
  MAX_SIZE = 32G
  ENGINE = InnoDB;

冷熱分離

• 熱數(shù)據(jù) (用戶、訂單) → SSD 表空間
• 冷數(shù)據(jù) (日志、歸檔) → HDD 表空間

三、實(shí)際演示：空間分配 & 回收

1. 創(chuàng)建空表

CREATE TABLE user (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  name         VARCHAR(20) NOT NULL,
  age          TINYINT      NOT NULL,
  gender       CHAR(1)      NOT NULL,
  phone        VARCHAR(16)  NOT NULL,
  create_time  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time  DATETIME     NOT NULL
) ENGINE=InnoDB;

$ ls -lh user.ibd
-rw-r----- 1 mysql mysql 96K Nov  6 12:48 user.ibd

說(shuō)明：空表首個(gè) Extent（32 頁(yè)）占用約 96KB。

2. 插入 10W 條

CALL insert_user_data(100000);  -- 自定義存儲(chǔ)過(guò)程批量插入

$ ls -lh user.ibd
-rw-r----- 1 mysql mysql 14M Nov  6 10:58 user.ibd

• 分配了更多 Extent，總計(jì)約 896 頁(yè)（≈14MB）。

3. DELETE 50K 條

DELETE FROM user LIMIT 50000;

$ ls -lh user.ibd
-rw-r----- 1 mysql mysql 14M Nov  6 13:22 user.ibd

• 空間未釋放，仍然保持 14MB。
• InnoDB 只打 刪除標(biāo)記 (delete_flag)，不進(jìn)行物理回收。

四、DELETE 對(duì)查詢性能的影響

初始查詢（100W 條＋索引）

SELECT id, age, phone
  FROM user
 WHERE name LIKE 'lyn12%';

• 執(zhí)行時(shí)間：30ms
• COST：10.499
• 物理讀：7,868,409
• 邏輯讀：7,855,239
• 掃描行：22,226
• 返回行：11,111

刪除 50W 后再查

DELETE FROM user LIMIT 500000;
ANALYZE TABLE user;

SELECT id, age, phone
  FROM user
 WHERE name LIKE 'lyn12%';

• 執(zhí)行時(shí)間：50ms
• COST：10.499
• 物理/邏輯讀：同上
• 掃描行：22,226
• 返回行：0

結(jié)論：大表刪除半數(shù)數(shù)據(jù)后，查詢成本和 I/O 基本不變，只是返回結(jié)果不同。

五、為什么不推薦大批量 DELETE

1. 空間不回收

   • .ibd 文件不縮小，Extents 保留

2. 頁(yè)碎片

   • 隨機(jī)刪除/更新導(dǎo)致頁(yè)分裂、空洞增加

3. 后續(xù)寫入難用

   • 刪除標(biāo)記頁(yè)只有在插入更小行時(shí)才會(huì)重用

4. 碎片回收代價(jià)高

   • ALTER TABLE … ENGINE=InnoDB：全表重建，I/O 密集、阻塞 DML

六、最佳實(shí)踐與優(yōu)化建議

1. 邏輯刪除（標(biāo)記刪除）

ALTER TABLE user
  ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0;

UPDATE user
   SET is_deleted = 1
 WHERE id = 123456;

-- 查詢時(shí)統(tǒng)一過(guò)濾：
SELECT * 
  FROM user
 WHERE is_deleted = 0
   AND name LIKE 'lyn12%';

• 優(yōu)點(diǎn)：無(wú)需大規(guī)模物理刪除，不引入碎片。

2. 分區(qū)歸檔

• 按時(shí)間分區(qū)，定期交換分區(qū)、歸檔歷史數(shù)據(jù)。
• 在線 DDL + 元數(shù)據(jù)交換：零或極低阻塞。

ALTER TABLE ota_order_bak
  EXCHANGE PARTITION p202301
  WITH TABLE ota_order_mid;

通過(guò)分區(qū)操作，瞬間移動(dòng)大塊數(shù)據(jù)，無(wú)需耗時(shí) DELETE。

3. 權(quán)限隔離

• 對(duì)業(yè)務(wù)賬號(hào)僅授 SELECT, INSERT, UPDATE，禁用 DELETE 權(quán)限。
• 拆分微服務(wù)數(shù)據(jù)庫(kù)，每個(gè)服務(wù)獨(dú)立賬號(hào)，避免誤刪。

CREATE USER 'svc_user'@'%'
  IDENTIFIED BY '…';
GRANT SELECT, INSERT, UPDATE
  ON db_user.*;

4. 專用歸檔系統(tǒng)

• 對(duì)冷數(shù)據(jù)、歷史日志，可考慮 ClickHouse、Elasticsearch 存儲(chǔ)與清理。
• 利用 TTL 自動(dòng)淘汰舊數(shù)據(jù)。

七、總結(jié)

• DELETE 大量數(shù)據(jù)不會(huì)縮減空間，反倒留下一堆碎片，影響索引與性能。
• 邏輯刪除 + 分區(qū)歸檔 才是大規(guī)模數(shù)據(jù)清理的良方。
• 結(jié)合 權(quán)限控制、專用歸檔系統(tǒng) (ClickHouse 等)，才能既保證性能，也不丟失歷史記錄。

到此這篇關(guān)于為什么說(shuō)MySQL不建議使用delete刪除數(shù)據(jù)的文章就介紹到這了,更多相關(guān)MySQL不使用delete刪除數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論