SQL DELETE 语句背后不为人知的黑科技

The Latency Gambler 2025-10-15 11:47:55

上周，我从我们的生产数据库中删除了50万行数据。查询执行成功了，但磁盘使用率却没有变化。更糟糕的是，在接下来的几天里，我们的查询性能开始下降。这并非漏洞，而恰恰是数据库的设计工作方式。

删除的假象

当你执行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;