软删除 + 生命周期

以下是一套 “软删除 + 生命周期（Data Lifecycle Management）”的完整工程级方案。

这是大厂 / 金融 / 核心系统里非常成熟的一种设计方式。

内容：设计目标 -> 表结构 -> 状态流转 -> 查询与索引 -> 归档与清理 -> 常见坑

一、设计目标（先定原则）

数据不丢、可追溯、可回滚、可控制成本

核心思想不是“不删”，而是：

业务删除 ≠ 物理删除
删除只是生命周期中的一个状态
物理删除是最后一步、可控的后台行为

二、生命周期模型（推荐）

✅ 4 个状态足够覆盖 95% 业务

ACTIVE  ->  SOFT_DELETED  ->  ARCHIVED  ->  PURGED

状态	含义
ACTIVE	正常可用
SOFT_DELETED	业务不可见（回收站）
ARCHIVED	归档（只读、冷数据）
PURGED	物理删除

三、推荐表结构（生产级）

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,

    -- 业务字段
    user_id BIGINT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,

    -- 生命周期字段
    status SMALLINT NOT NULL DEFAULT 1,
    -- 1=active, 2=soft_deleted, 3=archived

    deleted_at TIMESTAMPTZ,
    deleted_by TEXT,

    archived_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

👉 不要只用 is_deleted，一定要有时间和状态

四、状态流转规则（非常关键）

1️⃣ ACTIVE -> SOFT_DELETED（业务删除）

UPDATE orders
SET
  status = 2,
  deleted_at = now(),
  deleted_by = 'user:123'
WHERE id = 1001
  AND status = 1;

规则

只允许从 ACTIVE 删除
不允许重复删除
可恢复

2️⃣ SOFT_DELETED -> ACTIVE（恢复）

UPDATE orders
SET
  status = 1,
  deleted_at = NULL,
  deleted_by = NULL
WHERE id = 1001
  AND status = 2;

3️⃣ SOFT_DELETED -> ARCHIVED（归档）

后台任务：

UPDATE orders
SET
  status = 3,
  archived_at = now()
WHERE status = 2
  AND deleted_at < now() - interval '30 days';

特点

不再参与在线业务
只读
访问频率极低

4️⃣ ARCHIVED -> PURGED（物理删除）

DELETE FROM orders
WHERE status = 3
  AND archived_at < now() - interval '1 year';

👉 这一步必须是后台任务 + 可审计

五、查询与索引设计（决定成败）

1️⃣ 所有在线查询只看 ACTIVE

PostgreSQL（最佳）

CREATE INDEX idx_orders_active_user
ON orders(user_id)
WHERE status = 1;

MySQL（退而求其次）

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

2️⃣ 唯一约束（非常常见需求）

例如：

👉 同一用户只能有一个“活跃订单”

PostgreSQL（优雅）

CREATE UNIQUE INDEX uniq_active_order
ON orders(user_id)
WHERE status = 1;

MySQL（应用层保证）

3️⃣ View 封装（强烈推荐）

CREATE VIEW orders_active AS
SELECT *
FROM orders
WHERE status = 1;

应用层只查 orders_active

六、归档设计（避免表无限膨胀）

方案一：归档表（最常见）

CREATE TABLE orders_archive (LIKE orders);

INSERT INTO orders_archive
SELECT *
FROM orders
WHERE status = 3;

DELETE FROM orders
WHERE status = 3;

方案二：分区表（更高级）

PARTITION BY RANGE (created_at);

ACTIVE 在新分区
老分区直接 DETACH + DROP

七、审计与合规（核心系统必备）

CREATE TABLE order_audit (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT,
    action TEXT,
    operator TEXT,
    action_time TIMESTAMPTZ DEFAULT now(),
    detail JSONB
);

delete
restore
archive
purge

八、常见坑（真实踩过）

❌ 只加 is_deleted

无法审计
无法归档
无法恢复

❌ 查询不统一

一次忘加条件 = 事故

❌ 没有索引

deleted 数据越多，查询越慢

❌ 永不 purge

表无限大
VACUUM / purge 压力暴增

九、PostgreSQL vs MySQL 的实践建议

维度	PostgreSQL	MySQL
生命周期设计	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
部分索引	强	无
View / RLS	强	一般
冷热数据	分区极强	可用

十、总结

软删除不是为了“不删”，而是为了“可控地删”。

生命周期设计的目标，是把“危险操作”变成“后台、可审计、可回滚的流程”。

核心数据，不做破坏性删除（delete from xxx），做逻辑删除(is_deleted)，可以这样设计吗？如果可以这样设计，优缺点是什么？

可以，而且这是非常常见、也非常成熟的一种设计。

但它**不是“银弹”，必须配合正确的表设计、索引、生命周期管理，**否则后期一定会出问题。

从结论 -> 设计方式 -> 优缺点 -> 什么时候不该用 -> 最佳实践五个层次讲。

一、先给结论（直接回答你）

✅ 核心数据完全可以“不做物理删除”，统一用逻辑删除（is_deleted）

❗ 但前提是：你知道自己在承担什么成本，并且有配套方案

二、典型逻辑删除设计方式

1️⃣ 最基础版本（不推荐单独用）

is_deleted BOOLEAN NOT NULL DEFAULT false

问题

无法知道什么时候删的
无法知道谁删的
无法做回收站
无法做数据生命周期管理

2️⃣ 推荐版本（生产可用）

is_deleted BOOLEAN NOT NULL DEFAULT false,
deleted_at TIMESTAMPTZ,
deleted_by TEXT

行为约定

业务删除 = UPDATE
所有查询默认加 is_deleted = false
删除不是终态，只是“不可见”

3️⃣ 更高级版本（软删除 + 生命周期）[本文核心]

status SMALLINT NOT NULL DEFAULT 1
-- 1=active, 2=deleted, 3=archived

或者：

valid_from TIMESTAMPTZ,
valid_to   TIMESTAMPTZ

三、为什么核心数据“喜欢”逻辑删除？

✅ 优点（这是它存在的原因）

1️⃣ 数据安全（最重要）

防误删
支持回滚
支持审计
满足合规（金融、审计、风控）

delete 一条 = 永久破坏

update 一条 = 可控状态变更

2️⃣ 审计与追溯

谁删的？
什么时候删的？
删之前是什么状态？

👉 逻辑删除是审计的基础

3️⃣ 外键 & 业务一致性更容易

不破坏历史订单、账务、日志
避免大量 ON DELETE RESTRICT

4️⃣ 与 MVCC / WAL 机制天然适配

UPDATE 本质就是新版本
不触发大规模页清理
对 PostgreSQL / InnoDB 都友好

四、逻辑删除的代价（必须清楚）

❌ 缺点（非常现实）

1️⃣ 查询复杂度上升（最常见坑）

忘加条件 = 数据事故

SELECT * FROM orders;   -- ❌
SELECT * FROM orders WHERE is_deleted = false;  -- ✅

解决方式：

ORM 全局过滤
View
Row Level Security（PG）

2️⃣ 索引变复杂

如果你不设计好索引：

deleted 数据越来越多
索引膨胀
查询越来越慢

👉 必须有组合索引

CREATE INDEX idx_orders_active
ON orders (user_id)
WHERE is_deleted = false;

（PostgreSQL 部分索引极其重要）

3️⃣ 唯一约束变麻烦（高频踩坑）

例如：

UNIQUE (username)

如果逻辑删除后想允许重建同名用户？

❌ 行不通

正确做法（PG）

CREATE UNIQUE INDEX uniq_user_active
ON users(username)
WHERE is_deleted = false;

4️⃣ 表会“无限长”

deleted 数据永远存在
VACUUM 压力变大
冷数据拖慢热查询

👉 这不是 bug，是设计选择

五、什么时候不适合逻辑删除？

❌ 不推荐场景

场景	原因
临时数据	无价值
缓存表	可重建
中间计算表	可丢
高频写 + 无审计	纯性能优先
日志型表	append-only

这些表：

直接 DELETE 或 TRUNCATE
或直接分区 DROP

六、成熟系统的最佳实践（非常重要）

1️⃣ 分层处理（强烈推荐）

数据类型	策略
核心业务数据	逻辑删除
历史归档	物理迁移
冷数据	分区 + DROP
中间表	物理删除

2️⃣ 配套机制（缺一不可）

🔹 统一查询入口

ORM 默认加 is_deleted=false
禁止裸 SQL（或 code review）

🔹 部分索引 / 过滤索引

PostgreSQL：必须用
MySQL：只能靠组合索引

🔹 定期归档

deleted 超过 6 / 12 个月
迁移到 history 表
或冷库

3️⃣ PostgreSQL vs MySQL 差异

项目	PostgreSQL	MySQL
部分索引	✅ 强	❌
查询控制	RLS / View	应用层
VACUUM	重要	purge
适合逻辑删除	非常适合	可用但要谨慎

七、总结

逻辑删除适合核心、可审计、不可丢的数据，但它是“空间换安全、复杂度换可控”的设计，必须配合索引、查询约束和生命周期管理，否则一定会拖慢系统。