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

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

一、InnoDB 存储架构概览

Extent 自动扩展策略

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

二、InnoDB 表空间类型

系统表空间 (ibdata1)，保存内部字典等元数据。独立表空间（innodb_file_per_table=ON），每个表一个 .ibd 文件。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%'; 执行时间：30msCOST：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%'; 执行时间：50msCOST：10.499物理/逻辑读：同上扫描行：22,226返回行：0

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

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

空间不回收

.ibd 文件不缩小，Extents 保留

页碎片

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

后续写入难用

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

碎片回收代价高

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. 分区归档

ALTER TABLE ota_order_bak EXCHANGE PARTITION p202301 WITH TABLE ota_order_mid;

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

3. 权限隔离

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

4. 专用归档系统

七、总结

到此这篇关于为什么说MySQL不建议使用delete删除数据的文章就介绍到这了,