设计数据库表结构时数据库范式是我们经常考虑的策略。但往往很多时候我们会违反范式以获得更好的性能。今天来聊一聊这个话题。1.范式第一范式表中字段是原子的不可以再拆分。一个经典的例子就是地址字段记录了省市区id地址1北京市海淀区如果要符合范式可以对地址进行拆分。id省市区1北京市北京市海淀区当然这里只是为了举例说明实际项目中会使用标准的行政地区码来进行保存。第二范式首先要满足第一范式在此基础上主键以外的字段必须完全依赖主键不能依赖其他字段。比如下面的订单表订单id产品id产品数量购买价格订单金额100p201220130100p202330130订单表如果单独用“订单id”不能标记唯一记录只能使用“订单id产品id”做联合主键但是订单金额这个字段跟“产品id”没有关系这就不符合第二范式。可以对订单表进行优化去除“订单金额”订单id产品id产品数量购买价格100p201220100p202330订单金额表订单id订单金额币种100130RMB查询的时候使用订单id 做 JOIN 查询。第三范式首先要满足第二范式在此基础上表中的字段不能间接依赖主键也就是需要通过依赖传递来对主键形成依赖。再看下面的订单表主键是订单id订单id用户id用户姓名100C10tom101C11json用户姓名不能直接依赖主键“订单id”而是通过“用户id”间接依赖的这就不符合第三范式。可以拆分出用户表查询的时候使用用户id 进行 JOIN。 订单表订单id用户id100C10101C11用户表用户id用户姓名C10tomC11jsonBCNF 范式也称 BC 范式它是在满足第三范式的基础上不允许一个表中存在两个可以做主键的字段。比如下面这个仓库表仓库id管理员id存储商品id存储商品数量S100M10P200200S100M10P300230如果一个仓库只能由一个管理员管理而一个管理员也只能管理一个仓库那主键可以选择{仓库id存储商品id}也可以选择{管理员id存储商品id}。这就不符合 BCNF 范式。 可以把上表拆分出两个表仓库管理表仓库id管理员idS100M10S100M10仓库表仓库id存储商品id存储商品数量S100P200200S100P3002304NF 范式4NF 范式是在第三范式的基础上不允许表中字段由多对多的依赖。比如下面这个表符合第三范式但是订单id 和产品id 存在多对多的关系。订单id产品id产品名称100p201产品1100p202产品2可以拆分成三个表订单产品关系表订单id产品id100p201100p202订单表订单id订单金额100130产品表产品id产品名称p201产品1p202产品2查询的时候做三张表的 JOIN 查询。2.优缺点从上面范式的介绍可以看出范式是数据库设计的规约主要有以下优点减少存储空间同一个实体的属性只在表中存储一次减少了数据冗余节省了存储空间写数据性能高每个属性的插入、更新通常只需要操作一张表操作的数据集小效率更高数据完整性遵守范式设计表中需要保存关联实体的主键通过主键关联来保证数据完整性。去重操作少没有冗余数据也就很少会用到类似 distinct 和 group by 这样的耗时语句。但过度遵循范式设计也会存在一些缺点查询性能受到影响查询通常需要 JOIN 多个表JOIN 的表数量较多时JOIN 语句会成为性能瓶颈SQL 语句复杂度升高多张表 JOIN 往往使查询语句可读性差遇到重构、迁移之类的工作会带来很多额外工作量对索引依赖更多为了提高 JOIN 语句性能往往需要在连接字段上建立索引。因为遵循范式可能存在的缺点在实际设计和开发中我们往往会引入反范式通过增加数据冗余将不遵循范式但是需要的字段放到一个表中通过增加冗余来避免复杂的 JOIN通过对冗余字段增加索引来提高查询效率。核心思想也是时间换空间。反范式带来的优点是简化查询语句提高 SQL 执行效率对并发读的场景更加合适。但在写多的场景下也会存在一些问题比如因为要写多张表增删改操作更复杂 很容易造成锁竞争降低写入性能。同时也更容易导致数据不一致维护难度增加。3.使用建议在我们的实际项目开发中一般都使用混合范式。对 OLTP联机事务处理类型的使用场景比如电商、ERP 等写入比较多的系统可以考虑使用范式设计。而对于 OLAP联机分析处理的使用场景比如报表、数仓等需要处理复杂查询都是读取操作可以考虑采用反范式设计。通常使用 ELT 工具把业务数据从关系型数据库抽取到湖仓在湖仓构建反范式化的数据模型用于业务数据查询和报表生成。
