首页 游戏天地文章正文

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

游戏天地 2025年08月10日 20:46 1 admin

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

一、InnoDB 存储架构概览

  • 逻辑结构
    • 表空间 (Tablespace)
    • 段 (Segment)
    • Extent（区）：每个 Extent 包含 32 个页 (Page)。
    • 页 (Page)：InnoDB 的最小 I/O 单位，默认 16KB。
  • 物理结构
    • 数据文件 (.ibd / ibdata1)：存储表、索引和字典元数据。
    • 日志文件 (ib_logfile*)：记录页的修改，用于崩溃恢复。

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

Extent 自动扩展策略

初始分配为 1 个 Extent 若总表空间 < 32MB，每次 +1 个 Extent 大于 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 13:22 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. 空间不回收
  2. .ibd 文件不缩小，Extents 保留
  3. 页碎片
  4. 随机删除/更新导致页分裂、空洞增加
  5. 后续写入难用
  6. 删除标记页只有在插入更小行时才会重用
  7. 碎片回收代价高
  8. 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 等)，才能既保证性能，也不丢失历史记录。

相关文章

发表评论

泰日号Copyright Your WebSite.Some Rights Reserved. 网站地图 备案号：川ICP备66666666号 由Z-BlogPHP强力驱动