软删除需deleted_at时间戳字段、复合索引及显式查询过滤三者协同;用is_deleted布尔字段在MySQL中会导致唯一索引失效,因多个NULL被视为不重复;COUNT变慢源于deleted_at IS NULL条件引发全表扫描,应改用EXISTS或函数索引优化。

软删除不是加个字段就完事,关键在标记方式、索引设计和查询习惯三者必须咬合。否则 COUNT 变慢、UNIQUE 失效、WHERE 漏条件,问题会集中爆发。
软删除字段选 deleted_at 还是 is_deleted?
优先用 deleted_at TIMESTAMP NULL,而不是 is_deleted TINYINT(1)。前者语义清晰、支持时间追溯、GORM/SQLAlchemy 等主流 ORM 原生适配;后者在 MySQL 中容易引发唯一索引失效(因为多个 NULL 被视为不相等,导致同一邮箱可重复插入多条“未删”记录)。PostgreSQL 和 SQL Server 对 NULL 的唯一性处理更严格,但 MySQL 是重灾区。
如果必须用布尔字段,MySQL 下得配合函数索引或改用 0000-00-00 00:00:00 占位(不推荐,破坏语义且易出错)。
为什么 COUNT 查询突然变慢?
GORM 等 ORM 在启用软删除后,所有 SELECT 和 COUNT 都自动追加 deleted_at IS NULL 条件。这个条件会让原本能走索引的 COUNT(*) 变成全表扫描——尤其当表大、软删比例高时。
- 不要依赖
COUNT(*)判断存在性,改用EXISTS:SELECT EXISTS(SELECT 1 FROM users WHERE id = ? AND deleted_at IS NULL) - 为高频查询字段建复合索引,例如:
CREATE INDEX idx_users_id_deleted ON users(id, deleted_at) WHERE deleted_at IS NULL(PostgreSQL 支持部分索引) - MySQL 8.0+ 可用函数索引:
CREATE INDEX idx_users_active ON users((IF(deleted_at IS NULL, id, NULL)))
触发器拦截 DELETE 时最容易踩的坑
想用 BEFORE DELETE 触发器把物理删转成更新标记,必须注意三点:
- 只能用
OLD.id(不是NEW.id),因为DELETE没有NEW行 -
WHERE条件必须精确匹配主键,复合主键要写全:WHERE id = OLD.id AND tenant_id = OLD.tenant_id - SQLite 不支持触发器内更新原表,得换
INSTEAD OF视图;SQL Server 推荐直接用INSTEAD OF DELETE
另外,触发器里的 UPDATE 会再次触发 BEFORE UPDATE,若没控制好可能死循环。
外键级联和查询条件漏写是线上事故高发区
ON DELETE CASCADE 对软删除完全无效。子表不会自动标记,必须在业务层或定时任务中手动同步更新关联记录的 deleted_at 字段,否则出现“父已删、子还在”的数据不一致。
更隐蔽的问题是:开发时写了 db.Where("email = ?", email).First(&u),以为 GORM 会自动加 deleted_at IS NULL——但它只在调用 Find/First 等方法时才加;如果手写原生 SQL 或用 Raw,这个条件就彻底消失,已“删”数据直接暴露。
所以所有查询入口(包括后台管理、导出、统计脚本)都得显式检查是否加了软删除过滤,不能依赖 ORM 的“自动行为”。


















