当前位置：首页 > news >正文

线上 Bug 排查与修复实录

news 2026/6/10 2:04:29

MySQL 唯一索引遇上软删除：一个隐蔽的线上 Bug 排查与修复实录

一次由 AI 辅助生成代码引发的线上事故，却成了我理解数据库物理约束设计原则的最佳教材。

背景

在开发一个海外房产平台的"房源收藏"功能时，我们采用了业界常见的**软删除（逻辑删除）**策略——即用户取消收藏时，不真正删除数据库记录，而是通过一个del_flag字段标记为删除状态（'0'= 正常，'2'= 已删除）。

功能上线后初期一切正常，直到某天运营同事反馈：有客户投诉无法取消某些房源的收藏，点击按钮一直提示"系统繁忙"。

查看后台错误日志，发现集中报出：

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '42-37-2' for key 'uk_user_listing'

一场排查之旅就此展开。

复现场景

在测试环境模拟用户操作后，我成功还原了 100% 必现的崩溃路径：

原始表结构

-- tb_property_favorite 收藏表CREATETABLEtb_property_favorite(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULLCOMMENT'用户ID',listing_idBIGINTNOTNULLCOMMENT'房源ID',del_flagCHAR(1)DEFAULT'0'COMMENT'删除标记: 0-正常 2-已删除',-- 其他字段省略...UNIQUEKEYuk_user_listing(user_id,listing_id,del_flag)-- ⚠️ 隐患所在);

崩溃四步曲

步骤	用户操作	SQL 执行	数据库状态
1	首次收藏	`INSERT INTO ... (42, 37, '0')`	✅ 1 条记录：`(42, 37, '0')`
2	取消收藏	`UPDATE ... SET del_flag='2' WHERE user_id=42 AND listing_id=37`	✅ 1 条记录：`(42, 37, '2')`
3	再次收藏	代码查询`WHERE del_flag='0'`→ 查不到 →`INSERT ... (42, 37, '0')`	⚠️2 条记录并存：`(42, 37, '0')`+`(42, 37, '2')`
4	再次取消	`UPDATE ... SET del_flag='2' WHERE user_id=42 AND listing_id=37`	💥Duplicate entry ‘42-37-2’

图解崩溃链路

(42, 37, '0') ──取消──▶ (42, 37, '2') ──再收藏──▶ (42, 37, '0') ──再取消──▶ 💥 冲突! (42, 37, '2') (42, 37, '2') 已存在!

MySQL 在第 4 步尝试将(42, 37, '0')更新为(42, 37, '2')时，发现表中已经存在一条(42, 37, '2')的记录——唯一索引uk_user_listing(user_id, listing_id, del_flag)直接拦下了这次 UPDATE。

根因分析：三重设计失误的叠加

失误一：AI 把"状态"混入了"身份"唯一索引（数据库反模式）

这就是本次 Bug 的根源——将三个字段全部塞进一个唯一索引：

UNIQUEKEYuk_user_listing(user_id,listing_id,del_flag)

这个设计的致命问题在于，它告诉了 MySQL：

“同一个用户 + 同一个房源，只要删除标记不同，就是两条合法的不同记录。”

而实际的业务规则应该是：

“一个用户对一个房源，永远只能有一条关系记录，不管它是收藏还是取消。”

user_id+listing_id= 记录的身份（identity）
del_flag= 记录的状态（state）

把可变的"状态"塞进唯一索引，是典型的数据库反模式（Anti-pattern）。

失误二：盲目 INSERT 的收藏逻辑

原始代码在收藏时的逻辑是：

1. SELECT * WHERE user_id=? AND listing_id=? AND del_flag='0' 2. 如果查不到 → INSERT 新行

这个逻辑完全忽略了"此用户可能曾经收藏又取消过"的场景。正确的做法应该是：

1. SELECT * WHERE user_id=? AND listing_id=? （忽略 del_flag） 2. 如果存在旧记录（无论 del_flag 是什么）→ UPDATE del_flag='0' 恢复 3. 如果完全不存在 → INSERT

失误三：取消收藏未限定有效记录

原始取消收藏的 SQL 缺少del_flag='0'条件：

-- ❌ 坏写法：会误伤已取消的记录UPDATEtb_property_favoriteSETdel_flag='2'WHEREuser_id=?ANDlisting_id=?-- ✅ 好写法：只操作有效记录UPDATEtb_property_favoriteSETdel_flag='2'WHEREuser_id=?ANDlisting_id=?ANDdel_flag='0'

为什么只改代码不行？

很多开发者的第一反应是：“那把代码的 if-else 补严实，不就可以了吗？”

如果只改代码、不改索引，你的系统依然埋着三颗定时炸弹：

炸弹一：脏数据导致死锁

假设数据库里已经同时存在了这两条记录（无论是历史遗留还是并发导致）：

记录 A: (42, 37, '0') 记录 B: (42, 37, '2')

即使用上了完美的"恢复式"代码，当用户点击取消收藏时：

UPDATEtb_property_favoriteSETdel_flag='2'WHEREuser_id=42ANDlisting_id=37ANDdel_flag='0'

MySQL 尝试把记录 A 变成'2'，但记录 B 的(42, 37, '2')已经存在 →再次触发 Duplicate entry。

除非 DBA 手动删数据，否则这个用户永远无法取消收藏这套房源。

炸弹二：高并发下的竞态条件（Race Condition）

对于"收藏"这种高频操作，用户因为网络卡顿连击按钮，两个请求可能在 SELECT 和 INSERT 之间形成竞态窗口。只有把唯一索引收紧为(user_id, listing_id)，才能利用 MySQL 的行级锁和唯一约束彻底防住并发穿透。

炸弹三：违反最少惊讶原则

-- 这条 SQL 的语义是什么？UNIQUEKEYuk_user_listing(user_id,listing_id,del_flag)-- 它实际上允许：-- (42, 37, '0') ← 正常收藏-- (42, 37, '2') ← 已取消（但同时存在！）-- (42, 37, '1') ← 如果还有别状态呢？

任何一个新加入团队的后端开发者，读到这个索引，都会天然地认为"同一个 user_id + listing_id 组合只能存在一条"。让代码逻辑和数据库约束的语义保持一致，是工程可靠性的底线。

代码逻辑是"防君子"，数据库约束是"防小人"。两者兼备，才是工业级方案。

修复方案

1. 数据库迁移脚本

-- tb_property_favorite_alter_fix_uk_user_listing_20260608.sql-- Step 1: 清理脏数据 —— 同一用户+房源只保留最新一条记录-- （保留 id 最大的那条，即用户最新操作的那条）DELETEt1FROMtb_property_favorite t1INNERJOINtb_property_favorite t2WHEREt1.user_id=t2.user_idANDt1.listing_id=t2.listing_idANDt1.id<t2.id;-- Step 2: 删除旧的唯一索引ALTERTABLEtb_property_favoriteDROPINDEXuk_user_listing;-- Step 3: 创建新的唯一索引（只包含身份字段，不含 del_flag）ALTERTABLEtb_property_favoriteADDUNIQUEKEYuk_user_listing(user_id,listing_id);

2. 业务代码改造

// PropertyFavoriteServiceImpl.java — 收藏逻辑改为"恢复式写入"@OverridepublicvoidfavoriteListing(LonguserId,LonglistingId){// 先查询是否存在任何记录（不论 del_flag）PropertyFavoriteexisting=favoriteMapper.selectByUserAndListing(userId,listingId);if(existing!=null){// 记录存在 → 恢复（将 del_flag 从 '2' 改回 '0'）favoriteMapper.recoverFavorite(existing.getId());}else{// 完全不存在 → 插入新记录PropertyFavoritefavorite=newPropertyFavorite();favorite.setUserId(userId);favorite.setListingId(listingId);favorite.setDelFlag("0");favoriteMapper.insert(favorite);}}

<!-- PropertyFavoriteMapper.xml — 取消收藏严格限定 del_flag='0' --><updateid="cancelFavorite">UPDATE tb_property_favorite SET del_flag = '2' WHERE user_id = #{userId} AND listing_id = #{listingId} AND del_flag = '0'<!-- 只取消当前有效的收藏 --></update>

3. 涉及文件清单

文件	改动内容
`PropertyFavoriteServiceImpl.java`	收藏逻辑改为"恢复式写入"
`PropertyFavoriteController.java`	调用新方法
`PropertyFavoriteMapper.xml`	取消收藏 SQL 增加`del_flag='0'`条件
`tb_property_favorite_alter_fix_uk_user_listing_20260608.sql`	清理脏数据 + 修改唯一索引

验证结果

在测试环境执行修复后的验证流程：

收藏 → ✅ 正常 取消 → ✅ 正常 再收藏 → ✅ 正常（走恢复逻辑，不 INSERT） 再取消 → ✅ 正常（无冲突）

日志中不再出现Duplicate entry错误，受影响的客户恢复正常操作。

经验总结

对数据库设计的启示

唯一索引只应包含"身份"字段，不应包含"状态"字段。
- user_id + listing_id定义了"谁收藏了哪套房"——这是身份。
- del_flag描述了"当前是收藏还是取消"——这是状态。
- 把状态加进唯一索引 = 允许同一身份存在多条不同状态的记录 = 反模式。

软删除场景的正确范：

表结构：UNIQUE KEY (identity_fields)，del_flag 不参与唯一约束 创建操作：先查是否存在（忽略 del_flag），存在则恢复，不存在才插入 删除操作：UPDATE SET del_flag='2' WHERE identity_fields AND del_flag='0' 查询操作：所有查询默认带 WHERE del_flag='0'

对 AI 辅助开发的反思

这个 Bug 的初始代码由 AI 生成——它写增删改查极快，但它不懂：

并发安全（Race Condition）
数据库物理约束的设计原则（Anti-pattern 识别）
业务语义和数据库约束的一致性

AI 是极好的代码生成工具，但人的核心价值在于用扎实的计算机基础去审查和纠偏 AI 的产出。这次经历后，我在团队中沉淀了一条协作规范：

所有涉及软删除的关联表（收藏、点赞、关注、好友关系等），唯一索引绝对不允许包含del_flag，且写入操作必须使用"存在则恢复、不存在则插入"的模式。

适用场景扩展

这套设计模式适用于所有具备"反向操作 + 软删除"特征的业务模块：

业务模块	身份字段	唯一索引
收藏	user_id + listing_id	`UNIQUE(user_id, listing_id)`
点赞	user_id + post_id	`UNIQUE(user_id, post_id)`
关注	follower_id + followee_id	`UNIQUE(follower_id, followee_id)`
好友关系	user_a_id + user_b_id	`UNIQUE(user_a_id, user_b_id)`
评价/评分	user_id + order_id	`UNIQUE(user_id, order_id)`