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)DEFAULT0COMMENT删除标记: 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_flag2 WHERE user_id42 AND listing_id37✅ 1 条记录(42, 37, 2)3再次收藏代码查询WHERE del_flag0→ 查不到 →INSERT ... (42, 37, 0)⚠️2 条记录并存(42, 37, 0)(42, 37, 2)4再次取消UPDATE ... SET del_flag2 WHERE user_id42 AND listing_id37Duplicate 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_idlisting_id 记录的身份identitydel_flag 记录的状态state把可变的状态塞进唯一索引是典型的数据库反模式Anti-pattern。失误二盲目 INSERT 的收藏逻辑原始代码在收藏时的逻辑是1. SELECT * WHERE user_id? AND listing_id? AND del_flag0 2. 如果查不到 → INSERT 新行这个逻辑完全忽略了此用户可能曾经收藏又取消过的场景。正确的做法应该是1. SELECT * WHERE user_id? AND listing_id? 忽略 del_flag 2. 如果存在旧记录无论 del_flag 是什么→ UPDATE del_flag0 恢复 3. 如果完全不存在 → INSERT失误三取消收藏未限定有效记录原始取消收藏的 SQL 缺少del_flag0条件-- ❌ 坏写法会误伤已取消的记录UPDATEtb_property_favoriteSETdel_flag2WHEREuser_id?ANDlisting_id?-- ✅ 好写法只操作有效记录UPDATEtb_property_favoriteSETdel_flag2WHEREuser_id?ANDlisting_id?ANDdel_flag0为什么只改代码不行很多开发者的第一反应是“那把代码的 if-else 补严实不就可以了吗”如果只改代码、不改索引你的系统依然埋着三颗定时炸弹炸弹一脏数据导致死锁假设数据库里已经同时存在了这两条记录无论是历史遗留还是并发导致记录 A: (42, 37, 0) 记录 B: (42, 37, 2)即使用上了完美的恢复式代码当用户点击取消收藏时UPDATEtb_property_favoriteSETdel_flag2WHEREuser_id42ANDlisting_id37ANDdel_flag0MySQL 尝试把记录 A 变成2但记录 B 的(42, 37, 2)已经存在 →再次触发 Duplicate entry。除非 DBA 手动删数据否则这个用户永远无法取消收藏这套房源。炸弹二高并发下的竞态条件Race ConditionMySQL请求2收藏请求1收藏MySQL请求2收藏请求1收藏两个请求同时发现没有记录SELECT ... WHERE user42 AND listing37SELECT ... WHERE user42 AND listing37INSERT (42, 37, 0) ✅INSERT (42, 37, 0) Duplicate!对于收藏这种高频操作用户因为网络卡顿连击按钮两个请求可能在 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_idt2.user_idANDt1.listing_idt2.listing_idANDt1.idt2.id;-- Step 2: 删除旧的唯一索引ALTERTABLEtb_property_favoriteDROPINDEXuk_user_listing;-- Step 3: 创建新的唯一索引只包含身份字段不含 del_flagALTERTABLEtb_property_favoriteADDUNIQUEKEYuk_user_listing(user_id,listing_id);2. 业务代码改造// PropertyFavoriteServiceImpl.java — 收藏逻辑改为恢复式写入OverridepublicvoidfavoriteListing(LonguserId,LonglistingId){// 先查询是否存在任何记录不论 del_flagPropertyFavoriteexistingfavoriteMapper.selectByUserAndListing(userId,listingId);if(existing!null){// 记录存在 → 恢复将 del_flag 从 2 改回 0favoriteMapper.recoverFavorite(existing.getId());}else{// 完全不存在 → 插入新记录PropertyFavoritefavoritenewPropertyFavorite();favorite.setUserId(userId);favorite.setListingId(listingId);favorite.setDelFlag(0);favoriteMapper.insert(favorite);}}!-- PropertyFavoriteMapper.xml — 取消收藏严格限定 del_flag0 --updateidcancelFavoriteUPDATE tb_property_favorite SET del_flag 2 WHERE user_id #{userId} AND listing_id #{listingId} AND del_flag 0!-- 只取消当前有效的收藏 --/update3. 涉及文件清单文件改动内容PropertyFavoriteServiceImpl.java收藏逻辑改为恢复式写入PropertyFavoriteController.java调用新方法PropertyFavoriteMapper.xml取消收藏 SQL 增加del_flag0条件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_flag2 WHERE identity_fields AND del_flag0 查询操作所有查询默认带 WHERE del_flag0对 AI 辅助开发的反思这个 Bug 的初始代码由 AI 生成——它写增删改查极快但它不懂并发安全Race Condition数据库物理约束的设计原则Anti-pattern 识别业务语义和数据库约束的一致性AI 是极好的代码生成工具但人的核心价值在于用扎实的计算机基础去审查和纠偏 AI 的产出。这次经历后我在团队中沉淀了一条协作规范所有涉及软删除的关联表收藏、点赞、关注、好友关系等唯一索引绝对不允许包含del_flag且写入操作必须使用存在则恢复、不存在则插入的模式。适用场景扩展这套设计模式适用于所有具备反向操作 软删除特征的业务模块业务模块身份字段唯一索引收藏user_id listing_idUNIQUE(user_id, listing_id)点赞user_id post_idUNIQUE(user_id, post_id)关注follower_id followee_idUNIQUE(follower_id, followee_id)好友关系user_a_id user_b_idUNIQUE(user_a_id, user_b_id)评价/评分user_id order_idUNIQUE(user_id, order_id)如果你的项目中还有这些表建议立即检查它们的唯一索引是否也错误地包含了del_flag。发布于 2026-06-09 | 标签MySQL, 数据库设计, 软删除, 唯一索引, Bug 复盘, AI 协作
线上 Bug 排查与修复实录
发布时间：2026/6/10 2:04:46
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)DEFAULT0COMMENT删除标记: 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_flag2 WHERE user_id42 AND listing_id37✅ 1 条记录(42, 37, 2)3再次收藏代码查询WHERE del_flag0→ 查不到 →INSERT ... (42, 37, 0)⚠️2 条记录并存(42, 37, 0)(42, 37, 2)4再次取消UPDATE ... SET del_flag2 WHERE user_id42 AND listing_id37Duplicate 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_idlisting_id 记录的身份identitydel_flag 记录的状态state把可变的状态塞进唯一索引是典型的数据库反模式Anti-pattern。失误二盲目 INSERT 的收藏逻辑原始代码在收藏时的逻辑是1. SELECT * WHERE user_id? AND listing_id? AND del_flag0 2. 如果查不到 → INSERT 新行这个逻辑完全忽略了此用户可能曾经收藏又取消过的场景。正确的做法应该是1. SELECT * WHERE user_id? AND listing_id? 忽略 del_flag 2. 如果存在旧记录无论 del_flag 是什么→ UPDATE del_flag0 恢复 3. 如果完全不存在 → INSERT失误三取消收藏未限定有效记录原始取消收藏的 SQL 缺少del_flag0条件-- ❌ 坏写法会误伤已取消的记录UPDATEtb_property_favoriteSETdel_flag2WHEREuser_id?ANDlisting_id?-- ✅ 好写法只操作有效记录UPDATEtb_property_favoriteSETdel_flag2WHEREuser_id?ANDlisting_id?ANDdel_flag0为什么只改代码不行很多开发者的第一反应是“那把代码的 if-else 补严实不就可以了吗”如果只改代码、不改索引你的系统依然埋着三颗定时炸弹炸弹一脏数据导致死锁假设数据库里已经同时存在了这两条记录无论是历史遗留还是并发导致记录 A: (42, 37, 0) 记录 B: (42, 37, 2)即使用上了完美的恢复式代码当用户点击取消收藏时UPDATEtb_property_favoriteSETdel_flag2WHEREuser_id42ANDlisting_id37ANDdel_flag0MySQL 尝试把记录 A 变成2但记录 B 的(42, 37, 2)已经存在 →再次触发 Duplicate entry。除非 DBA 手动删数据否则这个用户永远无法取消收藏这套房源。炸弹二高并发下的竞态条件Race ConditionMySQL请求2收藏请求1收藏MySQL请求2收藏请求1收藏两个请求同时发现没有记录SELECT ... WHERE user42 AND listing37SELECT ... WHERE user42 AND listing37INSERT (42, 37, 0) ✅INSERT (42, 37, 0) Duplicate!对于收藏这种高频操作用户因为网络卡顿连击按钮两个请求可能在 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_idt2.user_idANDt1.listing_idt2.listing_idANDt1.idt2.id;-- Step 2: 删除旧的唯一索引ALTERTABLEtb_property_favoriteDROPINDEXuk_user_listing;-- Step 3: 创建新的唯一索引只包含身份字段不含 del_flagALTERTABLEtb_property_favoriteADDUNIQUEKEYuk_user_listing(user_id,listing_id);2. 业务代码改造// PropertyFavoriteServiceImpl.java — 收藏逻辑改为恢复式写入OverridepublicvoidfavoriteListing(LonguserId,LonglistingId){// 先查询是否存在任何记录不论 del_flagPropertyFavoriteexistingfavoriteMapper.selectByUserAndListing(userId,listingId);if(existing!null){// 记录存在 → 恢复将 del_flag 从 2 改回 0favoriteMapper.recoverFavorite(existing.getId());}else{// 完全不存在 → 插入新记录PropertyFavoritefavoritenewPropertyFavorite();favorite.setUserId(userId);favorite.setListingId(listingId);favorite.setDelFlag(0);favoriteMapper.insert(favorite);}}!-- PropertyFavoriteMapper.xml — 取消收藏严格限定 del_flag0 --updateidcancelFavoriteUPDATE tb_property_favorite SET del_flag 2 WHERE user_id #{userId} AND listing_id #{listingId} AND del_flag 0!-- 只取消当前有效的收藏 --/update3. 涉及文件清单文件改动内容PropertyFavoriteServiceImpl.java收藏逻辑改为恢复式写入PropertyFavoriteController.java调用新方法PropertyFavoriteMapper.xml取消收藏 SQL 增加del_flag0条件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_flag2 WHERE identity_fields AND del_flag0 查询操作所有查询默认带 WHERE del_flag0对 AI 辅助开发的反思这个 Bug 的初始代码由 AI 生成——它写增删改查极快但它不懂并发安全Race Condition数据库物理约束的设计原则Anti-pattern 识别业务语义和数据库约束的一致性AI 是极好的代码生成工具但人的核心价值在于用扎实的计算机基础去审查和纠偏 AI 的产出。这次经历后我在团队中沉淀了一条协作规范所有涉及软删除的关联表收藏、点赞、关注、好友关系等唯一索引绝对不允许包含del_flag且写入操作必须使用存在则恢复、不存在则插入的模式。适用场景扩展这套设计模式适用于所有具备反向操作 软删除特征的业务模块业务模块身份字段唯一索引收藏user_id listing_idUNIQUE(user_id, listing_id)点赞user_id post_idUNIQUE(user_id, post_id)关注follower_id followee_idUNIQUE(follower_id, followee_id)好友关系user_a_id user_b_idUNIQUE(user_a_id, user_b_id)评价/评分user_id order_idUNIQUE(user_id, order_id)如果你的项目中还有这些表建议立即检查它们的唯一索引是否也错误地包含了del_flag。发布于 2026-06-09 | 标签MySQL, 数据库设计, 软删除, 唯一索引, Bug 复盘, AI 协作
