MySQL为什么不建议使用delete删除数据？

这篇文章，我将从 InnoDB 存储空间分配、DELETE 对性能的影响 以及 最佳实践建议 三个角度，逐步剖析为什么不推荐直接使用 DELETE 删除大批量数据。

---

一、InnoDB 存储架构概览

• 逻辑结构

  • 表空间 (Tablespace)
  • 段 (Segment)
  • Extent（区）：每个 Extent 包含 32 个页 (Page)。
  • 页 (Page)：InnoDB 的最小 I/O 单位，默认 16KB。

• 物理结构

  • 数据文件 (.ibd / ibdata1)：存储表、索引和字典元数据。
  • 日志文件 (ib_logfile*)：记录页的修改，用于崩溃恢复。

Extent 自动扩展策略

1. 初始分配为 1 个 Extent
2. 若总表空间 < 32MB，每次 +1 个 Extent
3. 大于 32MB，则每次 +4 个 Extent

---

二、InnoDB 表空间类型

1. 系统表空间 (ibdata1)，保存内部字典等元数据。
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;

冷热分离

• 热数据 (用户、订单) → SSD 表空间
• 冷数据 (日志、归档) → HDD 表空间

---

三、实际演示：空间分配 & 回收

1. 创建空表

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 只打 删除标记 (delete\_flag)，不进行物理回收。

---

四、DELETE 对查询性能的影响

初始查询（100W 条＋索引）

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

• 执行时间：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%';

• 执行时间：50ms
• COST：10.499
• 物理/逻辑读：同上
• 扫描行：22,226
• 返回行：0

结论：大表删除半数数据后，查询成本和 I/O 基本不变，只是返回结果不同。

---

五、为什么不推荐大批量 DELETE

1. 空间不回收

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

2. 页碎片

   • 随机删除/更新导致页分裂、空洞增加

3. 后续写入难用

   • 删除标记页只有在插入更小行时才会重用

4. 碎片回收代价高

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

---

六、最佳实践与优化建议

1. 逻辑删除（标记删除）

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

UPDATE user
   SET is_deleted = 1
 WHERE id = 123456;

-- 查询时统一过滤：
SELECT * 
  FROM user
 WHERE is_deleted = 0
   AND name LIKE 'lyn12%';

• 优点：无需大规模物理删除，不引入碎片。

2. 分区归档

• 按时间分区，定期交换分区、归档历史数据。
• 在线 DDL + 元数据交换：零或极低阻塞。

ALTER TABLE ota_order_bak
  EXCHANGE PARTITION p202301
  WITH TABLE ota_order_mid;

通过分区操作，瞬间移动大块数据，无需耗时 DELETE。

3. 权限隔离

• 对业务账号仅授 SELECT, INSERT, UPDATE，禁用 DELETE 权限。
• 拆分微服务数据库，每个服务独立账号，避免误删。

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

4. 专用归档系统

• 对冷数据、历史日志，可考虑 ClickHouse、Elasticsearch 存储与清理。
• 利用 TTL 自动淘汰旧数据。

---

七、总结

• DELETE 大量数据不会缩减空间，反倒留下一堆碎片，影响索引与性能。
• 逻辑删除 + 分区归档 才是大规模数据清理的良方。
• 结合 权限控制、专用归档系统 (ClickHouse 等)，才能既保证性能，也不丢失历史记录。

本文由博客一文多发平台 OpenWrite 发布！