如何设计一个软删除（is_deleted）方案？利弊？

考察要点

这道题考察的是候选人对数据库设计范式与反范式之间权衡的理解，以及对一个设计决策所带来的长期影响的预见能力。面试官想看你是否懂：

1. 方案设计与取舍（Trade-off）：是否能清晰阐述软删除（逻辑删除）与硬删除（物理删除）的优缺点，并根据业务场景做出合理选择。
2. 对数据库核心功能的影响：软删除方案如何影响索引、唯一约束、外键等数据库特性。
3. 性能优化与工程实践：如何设计高效的索引策略来配合软删除，以及如何处理因数据膨胀带来的性能问题。

核心概念

软删除（Soft Delete），也称逻辑删除，是指在删除数据时，并非真的从数据库中物理删除该行记录，而是通过一个状态字段来标记其为“已删除”。与之相对的是硬删除（Hard Delete），即物理删除，使用 DELETE 语句将数据行从磁盘上永久移除。

最常见的软删除实现方式是在表中增加一个标记列：

数据示例

假设我们有一个 users 表：

 1CREATE TABLE users (
 2    id INT PRIMARY KEY AUTO_INCREMENT,
 3    name VARCHAR(50) NOT NULL,
 4    email VARCHAR(100) NOT NULL UNIQUE,
 5    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 6    deleted_at TIMESTAMP NULL DEFAULT NULL
 7);

原始数据:

操作：软删除 id 为 2 的用户

 1UPDATE users SET deleted_at = NOW() WHERE id = 2;

操作后数据:

此时，对于业务代码来说，所有查询都必须带上 WHERE deleted_at IS NULL 条件，才能获取到“有效”的用户列表。

常见低分回答

1. 查询时忘记过滤已删除数据：在所有 SELECT 语句中都必须增加 WHERE deleted_at IS NULL (或 is_deleted = 0) 条件，否则会将已删除的数据查询出来，造成业务逻辑错误。这是最常见也最致命的错误。
2. 破坏唯一约束：当软删除一个带唯一键（如 email）的记录后，无法再插入一个具有相同 email 的新记录，因为旧记录依然存在于表中，导致唯一约束冲突。例如，用户 Bob 注销账户后，无法用同一个邮箱 [email protected] 重新注册。
3. 索引设计不当：仅在 is_deleted 或 deleted_at 上创建单列索引。这种列的基数（Cardinality）通常很低（比如 is_deleted 只有 0 和 1 两个值），MySQL 查询优化器很可能放弃使用该索引，导致查询效率低下。
4. 滥用 COUNT(列)：使用 COUNT(deleted_at) 来统计已删除的行数。虽然可行，但 COUNT(*) 或 COUNT(1) 通常效率更高，且能更清晰地表达意图。更重要的是，COUNT(列) 不会统计 NULL值的行，COUNT(deleted_at) 实际上统计的是已删除的行数，而 COUNT(*) 统计的是总行数，容易混淆。
5. 忽略对关联查询的影响：在 JOIN 操作中，不仅主表需要过滤 deleted_at IS NULL，被关联的表同样需要过滤，否则可能 JOIN 上一个已经被删除的记录。

工程实践与优化

1. 索引策略是关键：

   • 最佳实践：创建复合索引，将软删除标记作为索引的最后一列。对于高频查询，例如根据用户 ID 查找，应该创建 (user_id, deleted_at) 这样的索引。查询 WHERE user_id = ? AND deleted_at IS NULL 可以高效利用此索引。
   • 利用部分索引（Partial Index）：对于支持部分索引的数据库（如 PostgreSQL, SQLite, SQL Server，以及 MySQL 8.0 的表达式索引），这是最高效的方案。索引只包含未删除的行，体积更小，查询更快。
     sql

     ⧉⛶

      1-- PostgreSQL
      2CREATE INDEX idx_users_email_active ON users (email) WHERE deleted_at IS NULL;
      3
      4-- MySQL 8.0+ (通过函数索引模拟)
      5-- 创建一个虚拟列，然后在其上创建唯一索引
      6ALTER TABLE users ADD COLUMN is_active TINYINT(1) GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL;
      7CREATE UNIQUE INDEX idx_users_email_active ON users (email, is_active);

     这个 MySQL 方案可以完美解决唯一键冲突问题，因为它保证了 (email, 1) 的组合是唯一的，而 (email, NULL) 组合可以有多个。

2. 处理唯一约束冲突：

   • PostgreSQL: 使用上面提到的部分唯一索引。
   • MySQL 8.0+: 使用上面提到的生成列 + 唯一索引。
   • 通用但丑陋的方案: 在软删除时，将唯一键字段的值修改为一个特殊格式，例如 email_deleted_timestamp。如 [email protected] -> [email protected]_deleted_1667898900。不推荐，会污染数据，且恢复困难。

3. 控制表体积膨胀：

   • 定期归档：编写定时任务（如每晚执行的脚本），将 deleted_at 早于特定时间（如 90 天前）的记录从主表移动到 users_archive 归档表中，然后从主表物理删除。
   • 表分区（Partitioning）：如果业务允许，可以按状态（例如用 LIST 分区，分为 active 和 deleted 分区）或按删除日期（RANGE 分区）对表进行分区。清理数据时，可以直接 DROP 或 TRUNCATE 整个分区，远比 DELETE 高效。

4. 简化应用层逻辑：

   • 使用视图（View）：创建一个视图 v_users，其定义中包含了 WHERE deleted_at IS NULL。应用代码只查询这个视图，可以避免在各处重复写过滤条件。
     sql

     ⧉⛶

      1CREATE VIEW v_users AS SELECT id, name, email, created_at FROM users WHERE deleted_at IS NULL;

   • ORM/框架层面：大多数现代 ORM 框架（如 GORM, Django ORM, Hibernate）都内置了对软删除的支持，会自动在查询中添加过滤条件。这是在应用层解决该问题的最佳实践。

常见追问与延伸题目

1. 追问：软删除的唯一键冲突问题，你有什么方案？

   • 回答要点 1 (PostgreSQL 最佳实践)：首选方案是使用部分索引（Partial Index），CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;。这从根本上解决了问题，索引只对活跃用户（deleted_at 为 NULL）的 email 强制唯一，简洁高效。
   • 回答要点 2 (MySQL 8.0+ 最佳实践)：使用生成列（Generated Column）和复合唯一索引。添加一个虚拟列 is_active AS (IF(deleted_at IS NULL, 1, NULL))，然后创建 UNIQUE INDEX (email, is_active)。这样，对于所有活跃用户，is_active 都是 1，唯一性由 (email, 1) 保证；对于已删除用户，is_active 是 NULL，而唯一索引不限制 NULL 值的重复，从而解决冲突。
   • 回答要点 3 (通用兼容方案)：如果数据库版本过低，可以在应用层处理。软删除时，将唯一键字段值修改为包含时间戳或随机数的唯一字符串，但这会污染数据，是不得已的方案。

2. 追问：数据量越来越大，软删除的表性能下降怎么办？

   • 回答要点 1 (数据归档)：核心思路是“冷热分离”。定期（如每天凌晨）运行一个批处理任务，将超过一定期限（如 180 天）的软删除数据从主业务表迁移到一张结构相同的归档表（users_archive）。迁移完成后，再从主表物理删除这些数据。这样可以保持主表的“瘦身”，提升查询性能。
   • 回答要点 2 (表分区)：如果数据库支持且业务场景合适，可以考虑使用表分区。例如，可以按 deleted_at 的年份进行范围分区。清理旧数据时，可以直接删除整个分区（DROP PARTITION），这比逐行 DELETE 快几个数量级，并且产生的数据库日志和锁争用也少得多。
   • 回答要点 3 (索引优化)：持续监控查询计划（EXPLAIN），确保复合索引被有效利用。随着数据分布的变化，可能需要调整索引列的顺序或添加新的复合索引来应对性能衰退。

3. 追问：如果换成 PostgreSQL / OceanBase / TiDB，答案有什么不同？

   • PostgreSQL: 主要区别在于对唯一键和索引的支持更友好。它原生支持部分索引，是解决软删除场景下唯一性与索引效率问题的“银弹”。可以直接创建 ... WHERE deleted_at IS NULL 的索引，这是比 MySQL 方案更简洁优雅的实现。
   • TiDB: TiDB 兼容 MySQL 协议，因此 MySQL 的方案大多适用。但 TiDB 作为分布式数据库，其架构特性（LSM-Tree 存储模型）决定了 DELETE 操作本身也是一种“软删除”（写入一个删除标记），后续由垃圾回收（GC）过程物理清理。因此，在 TiDB 中使用业务层软删除，会加剧数据膨胀和 GC 压力。需要更积极地配置 GC 策略和 TTL（Time-To-Live）属性来管理数据生命周期。
   • OceanBase: 同样兼容 MySQL，但其底层是为大规模分布式场景设计的。它也存在类似 TiDB 的内部删除标记和合并清理机制。在 OceanBase 中设计软删除，除了考虑 MySQL 的通用实践外，还需要结合其分区、存储和合并机制来综合考虑。例如，利用其分区能力做数据归档会非常高效。