二、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
3. DELETE 50K 条
DELETE FROM user LIMIT 50000;
$ ls -lh user.ibd
-rw-r----- 1 mysql mysql 14M Nov 6 13:22 user.ibd
四、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. 逻辑删除（标记删除）
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 等)，才能既保证性能，也不丢失历史记录。
参考文章：原文链接
400 186 1886
