SQL DELETE 语句背后不为人知的黑科技
上周，我从我们的生产数据库中删除了50万行数据。查询执行成功了，但磁盘使用率却没有变化。更糟糕的是，在接下来的几天里，我们的查询性能开始下降。这并非漏洞，而恰恰是数据库的设计工作方式。
核心摘要
- SQL
DELETE通常是逻辑删除，不等于立刻从磁盘物理移除数据。 - 在支持 MVCC 的数据库中，被删除的行可能仍需服务于并发事务，因此会先被标记为 dead tuple 或 tombstone。
- PostgreSQL 需要通过
VACUUM等维护流程清理失效元组；VACUUM FULL会更彻底，但需要排他锁。 - MySQL InnoDB 可以通过后台 purge 线程和
OPTIMIZE TABLE回收或重整空间。 - 大批量删除前，应考虑维护窗口、
TRUNCATE、分区删除和死行比例监控。
关键术语
- 逻辑删除：数据库先把行标记为删除，让查询忽略它，而不是立即重写磁盘文件。
- MVCC：多版本并发控制，用多个数据版本支持并发读写，避免正在运行的事务被破坏。
- Dead rows / Dead tuples：已经被删除或过期、但尚未被物理清理的数据版本。
- VACUUM / OPTIMIZE TABLE：用于清理、重整或回收数据库空间的维护操作。
删除的假象
当你执行DELETE语句时，你会期望数据立即消失。但实际情况是这样的：
DELETE FROM orders WHERE created_at < '2024-01-01';
-- Query OK, 500000 rows affected (12.34 sec)
数据库不会从磁盘中删除任何内容。相反，它会将这些行标记为已删除，本质上是设置一个墓碑标记，表明“忽略此行”。实际数据仍物理存储在磁盘上，会占用空间并影响性能。
数据库为何这样做
现代数据库使用多版本并发控制（MVCC）来处理并发事务。当你删除一行数据时，其他事务可能仍然需要查看它。数据库无法物理删除可能被正在进行的查询引用的数据。
以下是其工作原理的简化说明：
Before DELETE:
[Row 1: Active] [Row 2: Active] [Row 3: Active]
After DELETE (Row 2):
[Row 1: Active] [Row 2: Deleted-Marker] [Row 3: Active]
Physical Storage:
[Row 1: Data] [Row 2: Data (marked)] [Row 3: Data]
现实世界影响
我在PostgreSQL 16和MySQL 8.0上运行了基准测试来衡量这种效果：
PostgreSQL 测试
-- Create test table
CREATE TABLE test_data (
id SERIAL PRIMARY KEY,
data TEXT,
created_at TIMESTAMP
);
-- Insert 1M rows
INSERT INTO test_data (data, created_at)
SELECT md5(random()::text), NOW() - (random() * 365)::int
FROM generate_series(1, 1000000);
-- Check table size
SELECT pg_size_pretty(pg_total_relation_size('test_data'));
-- Result: 73 MB
-- Delete 50% of rows
DELETE FROM test_data WHERE id % 2 = 0;
-- Check size again
SELECT pg_size_pretty(pg_total_relation_size('test_data'));
-- Result: 73 MB (unchanged)
-- Check dead tuples
SELECT n_dead_tup FROM pg_stat_user_tables
WHERE relname = 'test_data';
-- Result: 500000 dead tuples
表的大小一点也没有减少。那500,000行“已删除”的记录仍然占用着空间。
清理流程
数据库提供维护命令来实际回收空间：
PostgreSQL：VACUUM
VACUUM FULL test_data;
SELECT pg_size_pretty(pg_total_relation_size('test_data'));
-- Result: 37 MB (50% reduction)
VACUUM扫描表，移除失效元组，并压缩数据。FULL选项会重写整个表，这样更彻底，但需要排他锁。
MySQL：OPTIMIZE TABLE
OPTIMIZE TABLE test_data;
-- Check table size
SELECT
table_name,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE table_name = 'test_data';
OPTIMIZE会重建表并回收未使用的空间。对于InnoDB表，它本质上是一种ALTER TABLE操作。
性能影响
死行（Dead rows）影响查询性能。以下是一个对比：
Table with 1M rows (no deletes):
SELECT * FROM test_data WHERE status = 'active';
-- Execution time: 145ms
Same table after deleting 500K rows (no VACUUM):
SELECT * FROM test_data WHERE status = 'active';
-- Execution time: 198ms (36% slower)
After VACUUM:
SELECT * FROM test_data WHERE status = 'active';
-- Execution time: 152ms (back to normal)
数据库在进行顺序扫描时仍需遍历死行，并在索引中维护这些死行。
这对不同数据库系统有何影响
PostgreSQL:
DELETE → Marks tuples as dead → VACUUM removes them
Auto-vacuum runs periodically but may lag behind
MySQL (InnoDB):
DELETE → Marks records deleted → Purge thread cleans up
Background purge usually keeps up
MongoDB:
DELETE → Removes document → Space marked reusable
WiredTiger engine reuses space automatically
Cassandra:
DELETE → Writes tombstone → Compaction removes
Tombstones persist until compaction runs
最佳实践
1. 安排维护时段
对于PostgreSQL，请在低流量时段运行VACUUM：
-- Autovacuum is enabled by default, but tune it:
ALTER TABLE high_churn_table
SET (autovacuum_vacuum_scale_factor = 0.05);
2. 对大量删除操作使用 TRUNCATE
如果您要删除所有行或整个分区：
TRUNCATE TABLE temp_data;
-- Much faster, reclaims space immediately
3. 分区你的表
删除整个分区而不是删除行：
ALTER TABLE orders DROP PARTITION p_2023_q1;
-- Instant, no cleanup needed
4. 监控无效元组（Tuple）
-- PostgreSQL monitoring query
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2)
AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
总结
DELETE是一种逻辑操作，而非物理操作。数据会一直存在，直到维护进程将其清理掉。这种设计选择支持多版本并发控制（MVCC）和并发访问，但这意味着你需要了解并管理清理过程。
下次执行大型DELETE操作时，请记住：你并不是在删除数据，而是将其标记为最终会被移除。要做好相应规划，监控无效元组的数量，并安排维护操作以保持数据库的健康状态。
你未来的自己和你的磁盘空间都会感谢你。
原文链接：https://medium.com/@kanishks772/the-dark-secret-behind-every-delete-statement-nobody-tells-you-1e7a44615ab4
FAQ
**Q1：为什么 DELETE 后磁盘空间没有立刻下降？**A：因为很多数据库会先做逻辑删除，把行标记为已删除。真正释放或复用空间，需要等待 VACUUM、purge、compaction 或 OPTIMIZE 等维护流程。
**Q2：什么时候应该用 TRUNCATE 而不是 DELETE？**A：如果目标是删除整张表或整个临时数据集，TRUNCATE 通常更快，也更容易立即回收空间。但它的语义和锁行为不同，使用前要确认业务是否允许。
**Q3：大型 DELETE 前最应该评估什么？**A：先评估删除范围、事务影响、死行比例、维护窗口和回收策略。删除只是第一步，后续清理和性能恢复同样重要。
