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

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

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

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

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

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

Extent 自動擴展策略

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

二、InnoDB 表空間類型

1. 系統(tǒng)表空間 (ibdata1)，保存內(nèi)部字典等元數(shù)據(jù)。
2. 獨立表空間（innodb_file_per_table=ON），每個表一個 .ibd 文件。
3. Undo 表空間，存儲 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 表空間

三、實際演示：空間分配 & 回收

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

說明：空表首個 Extent（32 頁）占用約 96KB。

2. 插入 10W 條

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

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

• 分配了更多 Extent，總計約 896 頁（≈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)，不進行物理回收。

四、DELETE 對查詢性能的影響

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

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

• 執(zhí)行時間：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í)行時間：50ms
• COST：10.499
• 物理/邏輯讀：同上
• 掃描行：22,226
• 返回行：0

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

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

1. 空間不回收

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

2. 頁碎片

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

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

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

4. 碎片回收代價高

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

六、最佳實踐與優(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;

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

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

2. 分區(qū)歸檔

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

ALTER TABLE ota_order_bak
  EXCHANGE PARTITION p202301
  WITH TABLE ota_order_mid;

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

3. 權(quán)限隔離

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

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

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

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

七、總結(jié)

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

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

最新評論