跳转到主内容
趣航编程网 - 趣学编程,启航技术之路!

SQL 正规化与反规范化实践案例

反规范化适用于读多写少、JOIN导致I/O或连接数瓶颈的场景,如报表导出;需先用EXPLAIN ANALYZE确认扫描问题,再通过触发器或CDC同步冗余数据,避免应用层双写。 什么时候该用反规范化,而不是硬扛 JOIN 性能? 数据库查得慢,第一反应不是加索引,而是看 SQL 里嵌了多少层
JOIN
。尤其在报表、宽表导出、实时看板这类场景,
SELECT *
拉 5 张表,每张都带
WHERE
和
GROUP BY
,响应时间从 200ms 跳到 2s,这时候反规范化不是“偷懒”,是权衡。 真实瓶颈在磁盘 I/O 或连接数耗尽,而非 CPU,说明查询计划已经反复扫表 数据变更频率低(比如用户基础信息每天同步一次),但读取频次极高(每秒数百次) 应用层做缓存成本高、一致性难维护,不如在 DB 层预计算好字段
JSON
字段或冗余列能避免跨库关联(比如订单表里存
user_name
而非只存
user_id
) 别一上来就加冗余字段。先用
EXPLAIN ANALYZE
确认是不是
Nested Loop
在拖慢速度;如果执行计划里出现多次
Seq Scan
或
Bitmap Heap Scan
扫全表,再考虑反规范化。 冗余字段怎么同步才不丢数据? 最常见错误是靠应用层“写两次”:先插订单,再手动更新用户积分表。网络抖动、事务中断、代码分支遗漏,都会导致两边不一致。 用数据库原生机制:PostgreSQL 的
TRIGGER
+
FUNCTION
,MySQL 的
BEFORE INSERT/UPDATE
触发器,确保同一事务内完成 避免在触发器里调远程 API 或写日志文件——这些操作失败会导致主 SQL 失败 如果涉及多表聚合(比如把用户所有订单金额 sum 存进
user.total_spent
),触发器里必须检查
OLD
和
NEW
值,只对变化字段做 delta 更新,而不是每次都重算全量 对于无法用触发器覆盖的场景(如异构数据源同步),用 CDC 工具(如 Debezium)捕获 binlog,再由下游服务做幂等更新,比定时任务更可靠 注意:
TRIGGER
在大批量
INSERT ... SELECT
或
COPY
时可能显著拖慢导入速度,测试时务必用真实数据量压测。 JSON 字段是反规范化的捷径还是陷阱? 把地址、标签、配置项塞进
JSONB
(PostgreSQL)或
JSON
(MySQL 8.0+)看起来省事,但很快会遇到问题。 查询性能:想查“所有收货城市为北京的订单”,
WHERE address->>'city' = '北京'
无法走普通 B-tree 索引,得建表达式索引:
CREATE INDEX ON orders ((address->>'city'))
数据校验缺失:
JSON
字段不阻止你存
{"city": 123}
这种类型错乱的数据,应用层必须自己校验结构 更新粒度粗:改一个字段就得重写整个 JSON,可能引发 MVCC 膨胀(尤其 PostgreSQL 中大
JSONB
字段频繁更新) 不支持外键约束,没法保证
user_id
在 JSON 里引用的是真实存在的用户 适合放 JSON 的,仅限那些“只读、结构松散、不参与 WHERE/GROUP/JOIN”的数据,比如埋点日志、前端表单快照、第三方回调原始 payload。 正规化回滚时,哪些字段最容易被漏掉? 项目后期发现反规范化太重,想退回到第三范式,常卡在“不知道哪些字段其实是冗余的”。 查数据库注释:
COMMENT ON COLUMN
如果写过
"冗余自 users.name"
就很省事;没写的话,翻 Git 历史看建表语句的 commit message 检查唯一性约束:如果
order.user_name
和
users.name
总是一致,且没有单独修改
order.user_name
的业务逻辑,基本可判定为冗余 审计 UPDATE 日志:用
pg_stat_statements
或慢日志分析,看哪些字段几乎从不被单独更新(比如
product.category_name
自上线后只随
category_id
变更而批量更新) 最危险的是“表面冗余、实际承担业务逻辑”的字段,比如
user.cached_role
看似来自 role 表,但权限系统实际读它而非 join,删了就炸 真正难的不是技术还原,是厘清字段背后的语义契约——有些字段早就不只是“缓存”,而是成了新协议的一部分。

相关文章