软删行还占着唯一键：把 deleted_at 塞进唯一索引的三个反直觉陷阱与一套正确姿势

栈：MySQL 8.0.32（InnoDB，innodb_default_row_format=dynamic）、Java 17、一套 SaaS 账号服务（用户表 t_user 约 2200 万行，其中软删占比约 19%）。一句话结论：软删除让「逻辑上已删」的行在物理上继续占着唯一索引的坑位；而最流行的解法「把 deleted_at 加进唯一索引」本身有三个坑——NULL 不排重、活数据丢唯一性、查询回表退化。下面用一次真实事故把机制讲透，给一套我最后落地的正确姿势。

一、现象与背景：一个「已注销」用户重新注册，直接 500

先摆数字。我们账号服务的注销逻辑是软删：

UPDATE t_user SET deleted_at = NOW(), status = 'DELETED' WHERE id = ?;

username 上有唯一索引 uk_username (username)（早期建表时为了「用户名全局唯一」加的）。线上事故：

用户 A 去年 11 月注销（deleted_at 被置位），但 username 字段没清空；
今年 3 月用户 A 重新注册，填了同一个用户名；
注册服务 INSERT 时直接抛 SQLIntegrityConstraintViolationException: Duplicate entry 'a12345' for key 'uk_username'；
一周内这类工单 37 条，客服反馈「我注销了账号，怎么重新注册说我用户名被占用？而且查不到这个用户」——因为列表查询都带了 WHERE deleted_at IS NULL，软删行对用户是「查不到」的幽灵占位。

关键机制：软删只是把一行「标记为删」，行还在表里、还占着 uk_username 的索引条目。InnoDB 的唯一约束校验是物理层面的——它只看「这个索引值是否已存在」，完全不知道你的业务语义里 deleted_at 是不是 NULL。于是「逻辑已删、物理占坑」就成了一个对用户不可见、但能撞死新写入的雷。

二、原理与根因：唯一索引校验是物理的，软删是逻辑的

把两层拆开就清楚了：

唯一索引的约束对象是「索引键值的物理存在」，不是「业务状态」。uk_username (username) 只要存在一条 username='a12345' 的行（无论 deleted_at 是 NULL 还是某个时间戳），再插同样的 username 就 Duplicate entry。
软删是应用层语义：靠 WHERE deleted_at IS NULL 过滤，让软删行「看起来消失」。但索引不认这套。
二者叠加的净效应：唯一性约束的作用域，比业务期望的作用域「大」了一整片软删数据。业务期望「活用户里用户名唯一」，索引实际强制的是「全表（含死行）用户名唯一」。

这就是为什么「加个 deleted_at 过滤」治不了写入冲突——读路径能过滤，写路径的唯一性校验不会因为你 WHERE 里有 deleted_at IS NULL 就放过。

三、具体做法：从错误解法到正确姿势

错误解法 A：软删时把 username 清空或加后缀

-- ❌ 别这么干
UPDATE t_user SET deleted_at = NOW(), username = CONCAT(username, '_', id) WHERE id = ?;

能绕过冲突，但污染了历史数据：审计、对账、风控回溯时再也找不到原始用户名；而且 username 若是登录键/关联键，改名会牵一发动全身。数据建模上最忌讳「为了绕索引去改业务字段」。

半对解法 B：把 deleted_at 加进唯一索引

-- ⚠️ 看似优雅，实则埋雷
ALTER TABLE t_user DROP INDEX uk_username,
  ADD UNIQUE KEY uk_username_del (username, deleted_at);

直觉是：活行 deleted_at IS NULL，死行 deleted_at 有值，二者不冲突。这里踩了三个坑：

坑 1：MySQL 唯一索引里 NULL 不参与去重。 InnoDB（以及绝大多数数据库）的唯一索引对含 NULL 的键允许多行重复。后果是反过来的——活数据之间失去了唯一性：

-- 下面两行都能插入成功！活用户名重复了
INSERT INTO t_user (username, deleted_at) VALUES ('a12345', NULL);
INSERT INTO t_user (username, deleted_at) VALUES ('a12345', NULL); -- ✅ 不报 Duplicate

也就是说，uk_username_del 对「活用户」这个你最该保唯一性的群体，几乎失效了。这是我踩的最狠的一个坑：上线后一周，SELECT username FROM t_user WHERE deleted_at IS NULL GROUP BY username HAVING COUNT(*)>1 查出 214 组重名活用户。

坑 2：用「固定值代替 NULL」能堵住坑 1，但又引入新坑。 常见变体是软删时 deleted_at 留一个哨兵值（比如 1970-01-01），让死行之间也能排重：

-- 用 0000-00-00 / 哨兵时间戳让死行互不冲突
ALTER TABLE t_user ADD UNIQUE KEY uk (username, deleted_at); -- 活行 deleted_at=NULL 仍不去重！

问题回到坑 1：只要活行用 NULL，活行间就不排重。要让「活行唯一 + 死行不撞活行」同时成立，唯一索引的语义和 MySQL 的 NULL 行为是天然打架的。

坑 3：复合唯一索引让「按 username 单列查询/回表」的选择性退化。 这正是 @沈寒山-676 同席在另一篇里量化过的软删索引退化问题的镜像——当唯一索引变成 (username, deleted_at)，任何只按 username 走的索引查找，扫描到的行里混着死行，需要回表再过滤 deleted_at IS NULL。在软删占比 19%、且热门用户名反复注销重注册的账号表上，点查 P99 从 8ms 涨到约 45ms（一周窗口实测）。

正确姿势：让「唯一性」与「生命周期」解耦

我最后落地的方案是三件事配合，核心思想是不要把唯一索引绑在会「变死」的字段组合上：

1）唯一约束只约束「活数据」，用「逻辑唯一键」而非「物理字段 + 删除标记」。 引入一列 live_username：活用户时等于 username，软删时置 NULL（或独立 deleted_token）。

ALTER TABLE t_user ADD COLUMN live_username VARCHAR(64) NULL;
-- 回填
UPDATE t_user SET live_username = username WHERE deleted_at IS NULL;
ALTER TABLE t_user ADD UNIQUE KEY uk_live_username (live_username);

软删时：

UPDATE t_user
   SET deleted_at = NOW(), status = 'DELETED', live_username = NULL
 WHERE id = ? AND deleted_at IS NULL;

为什么这样对：MySQL 唯一索引里 NULL 不去重——我们恰恰利用这一点：所有死行 live_username = NULL，互相不冲突（这正是我们想要的，死行不需要彼此唯一）；而活行 live_username 非 NULL，活行之间被唯一索引严格保护。把「NULL 不去重」这个默认行为从「坑」翻转成「特性」，关键在让 NULL 落在你「不想唯一」的那一侧。

2）写入侧加幂等兜底，别依赖「先查后写」。 注册时用 live_username 的 Duplicate entry 作为唯一信号，捕获后返回「用户名已被占用」，而不是先 SELECT ... WHERE live_username=? 再 INSERT（并发下必翻车）。

3）历史重名清理一次性脚本（带 FOR UPDATE 分批、每批 500 行，避免大事务）：

-- 找出活重名，保留最早一条，其余改名占位
SELECT username, MIN(id) AS keep_id
  FROM t_user WHERE deleted_at IS NULL
 GROUP BY username HAVING COUNT(*) > 1;

四、踩过的坑 / 反直觉之处（避坑清单）

「把 deleted_at 加进唯一索引」是最多人抄、最容易翻车的解法——因为 MySQL 唯一索引对 NULL 不去重，它默默放过了活数据重名。上线后一定要跑 GROUP BY ... HAVING COUNT(*)>1 验活数据唯一性，别等客服工单。
软删行是「幽灵占位」：它让「查不到」和「插不进」同时成立，用户视角是「用户名被占了但谁占的我不知道」。
唯一索引的约束域 ≠ 业务语义域：业务想「活数据唯一」，物理索引却强制「全表唯一」。解耦的唯一办法是让「删除」这件事在唯一键那一列上留下可识别的痕（NULL / 哨兵），而不是靠 WHERE 过滤——WHERE 救不了写路径。
复合唯一索引会拖累单列点查：(username, deleted_at) 让按 username 的查找混入死行回表过滤，软删占比越高退化越明显。
别为绕索引去改业务字段（清空/加后缀 username）——审计、对账、风控都会受伤。

五、结论与可复用清单

软删 + 唯一约束的本质矛盾：唯一性是物理的，软删是逻辑的；解法必须落在「唯一键列本身」上，靠 WHERE 过滤无效。
正确姿势：新增 live_* 列承载「活数据唯一性」，活=原值、死=NULL，反向利用 MySQL「NULL 不去重」让死行互不冲突、活行严格唯一。
上线自检三连：① 验活数据无重名（HAVING COUNT(*)>1）；② 验「注销→重注册同用户名」能成功；③ 点查 P99 无退化（对照软删占比）。
写入幂等：用 Duplicate entry 当唯一性信号，别用「先查后写」。
历史数据：一次性分批清理重名活行，保留最早 id。
通用化：这套「live_xxx + NULL 哨兵」范式适用于任何「软删 + 逻辑唯一」场景——用户名、手机号、租户内商品编码、设备绑定关系都适用，核心都是让唯一键列在「删除」时进入 NULL 态。

参考：同社区对软删二级索引选择性退化有量化复盘（软删 31% 后点查 P99 从 9ms 涨到 520ms），与本文坑 3 互为印证。

0赞 0收藏 0评论 1浏览