逻辑删除时唯一索引冲突如何避免？

Question

在逻辑删除（如 `is_deleted = 1`）场景下，若对业务字段（如 `email`、`username`）建立了唯一索引，当某条记录被逻辑删除后，再次插入相同业务值的新记录时，将因唯一索引约束触发冲突——数据库仍视其为“存在”，导致插入失败。根本原因在于：传统唯一索引不区分 `is_deleted` 状态，所有行（含已逻辑删除的）均参与索引校验。该问题高频出现于用户注册、订单号生成、租户资源命名等强唯一性保障场景，尤其在高并发或数据复用策略下尤为突出。开发者常误以为“删了就能重用”，却未意识到索引机制与业务语义的错配。如何在保留逻辑删除优势（审计、关联完整性、软恢复）的同时，解除唯一性校验对已删除记录的束缚？这是分布式系统与中台化架构中亟待解决的经典数据建模矛盾。

Answer 1

```html

一、现象层：逻辑删除与唯一索引的“静默冲突”

当用户表 users 含字段 email（唯一索引）、is_deleted TINYINT DEFAULT 0，执行以下操作链：

1. INSERT INTO users (email, is_deleted) VALUES ('[e-mail]', 0);
2. UPDATE users SET is_deleted = 1 WHERE email = '[e-mail]';
3. INSERT INTO users (email, is_deleted) VALUES ('[e-mail]', 0); → 唯一索引报错 Duplicate entry '[e-mail]'

该现象在 MySQL 5.7+/PostgreSQL 12+/Oracle 12c+ 中均复现——数据库引擎不感知业务语义，仅按物理行校验索引键值。

二、机理层：B+树索引的“无状态性”本质

传统唯一索引构建于全量数据之上，其底层 B+ 树节点存储的是 (email, PK) 键值对，is_deleted 字段未参与索引结构。即使添加 WHERE 条件过滤，索引本身仍强制覆盖所有行。

索引类型	是否跳过 is_deleted=1 行	是否支持事务一致性
普通唯一索引	否	是
函数索引（MySQL 8.0+）	是（需构造表达式）	是
部分索引（PostgreSQL/SQL Server）	是（WHERE is_deleted = 0）	是

三、解法层：四维技术路径对比分析

以下为生产环境验证有效的主流方案，按兼容性、性能、可维护性加权评估：

• 方案A：部分索引（Partial Index） —— PostgreSQL/SQL Server 首选
  CREATE UNIQUE INDEX idx_email_active ON users(email) WHERE is_deleted = 0;
• 方案B：函数索引（Functional Index） —— MySQL 8.0.13+ / Oracle 12c+
  CREATE UNIQUE INDEX idx_email_func ON users((IF(is_deleted = 0, email, NULL)));
• 方案C：组合唯一索引 + 业务兜底
  ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL;
CREATE UNIQUE INDEX idx_email_status ON users(email, is_deleted);（但需应用层保证插入时 is_deleted=0）

四、架构层：中台化场景下的治理增强模式

在多租户 SaaS 架构中，单一数据库难以满足租户级命名空间隔离，需叠加逻辑分片语义：

-- 租户ID + 业务字段联合唯一（兼顾逻辑删除）
CREATE UNIQUE INDEX idx_tenant_email ON users(tenant_id, email) 
WHERE is_deleted = 0;

配合应用层幂等注册流程：
① 先 SELECT COUNT(*) WHERE tenant_id=? AND email=? AND is_deleted=0；
② 若为 0，再 INSERT；
③ 利用数据库唯一索引做最终防线（防并发竞态）。

五、演进层：从“索引修补”到“语义建模升维”

真正可持续的解法，是将「逻辑删除状态」纳入领域模型契约。例如在 DDD 分层中：

graph TD
  A[应用服务] -->|调用| B[领域服务]
  B --> C{检查邮箱可用性}
  C -->|is_deleted=0存在？| D[拒绝注册]
  C -->|不存在或全is_deleted=1| E[允许注册]
  E --> F[持久化 with is_deleted=0]
  F --> G[事件总线：UserRegistered]
  

六、陷阱层：被忽视的隐式约束与并发雷区

即便采用部分索引，仍需警惕：
✅ 索引列不能含 NULL（MySQL 函数索引需显式处理 NULL）；
⚠️ PostgreSQL 的 WHERE is_deleted = 0 不匹配 is_deleted IS NULL；
❗ 高并发下，两次 SELECT + INSERT 存在 TOCTOU（Time-of-Check to Time-of-Use）风险，必须搭配 INSERT ... ON CONFLICT DO NOTHING 或重试机制。

七、验证层：可落地的测试用例矩阵

场景	SQL 操作序列	预期结果	验证命令
软删后重注册	INSERT→UPDATE is_deleted=1→INSERT same email	第二 INSERT 成功	SELECT COUNT(*) FROM users WHERE email='x' AND is_deleted=0;
跨租户重名	T1插入tenant1@a、T2插入tenant2@a	均成功	SELECT tenant_id,email FROM users WHERE email='a';

八、演进展望：数据库内核与 ORM 的协同进化

MyBatis-Plus 3.4.3+ 支持 @TableLogic 自动追加 AND is_deleted = 0；
Hibernate 6.0 引入 @Where(clause = "is_deleted = 0")；
但二者均不解决 DDL 层索引问题——真正的破局点在于：数据库提供 SEMANTIC UNIQUE INDEX 语法提案，将业务生命周期状态作为索引元信息注册。

九、反模式警示：五种典型错误实践

1. 【禁用索引】ALTER TABLE users DROP INDEX idx_email；→ 完全丧失数据一致性保障
2. 【延迟清理】定时任务物理删除旧数据；→ 违背审计合规要求，破坏外键引用完整性
3. 【应用层锁表】SELECT FOR UPDATE + 业务判断；→ 严重降低吞吐，引入死锁风险
4. 【UUID 替代】用 UUID 做 email 字段；→ 违背业务语义，丧失可读性与搜索能力
5. 【忽略时序】未在事务中保证 UPDATE/INSERT 原子性；→ 导致中间态数据污染

十、终极原则：以“状态可枚举、索引可裁剪、变更可追溯”为设计铁律

逻辑删除不是“伪删除”，而是状态机的第一阶跃迁；
唯一性约束不应绑定在实体上，而应绑定在「有效生命周期实例」上；
每一个索引定义，都应明确回答三个问题：
① 此索引服务于哪个业务状态？
② 是否覆盖全部历史状态？
③ 状态变更时，索引是否自动失效/重建？

```