一、什么是数据建模数据建模就是将现实世界中复杂、无序的业务数据通过一套标准化的流程概念→逻辑→物理和科学的方法论如关系模型、维度模型转化为结构化、高内聚、低冗余、易使用的数据资产的过程。数据建模的核心三要素定义“有什么”识别出所有业务相关的核心对象比如“客户”、“产品”、“订单”、“交易”。定义“什么特征”明确每个对象的具体属性。例如“客户”有“姓名”、“电话”、“注册日期”。定义“什么关系”说明不同对象之间的联系。例如一个“客户”可以下多个“订单”一个“订单”包含多个“产品”。二、 数据建模的三个阶段概念、逻辑、物理模型这是从业务需求到最终物理实现的渐进过程是数据建模实践的三个标准阶段。它们构成了一个从抽象到具体、从业务到技术的完整工作流1. 概念模型核心与业务人员沟通画出“业务草图”只关注核心业务实体和它们之间的关系不涉及任何技术细节。 即定义“有什么”“什么关系”。产出物实体关系图ER图即Entity-Relationship Diagram的缩写描述实体及其关系的图形化工具只有实体和关系没有属性或主键。2. 逻辑模型核心在概念模型基础上将草图细化为“详细设计图”。详细定义每个实体的具体属性字段、数据类型、主键、外键等但不管数据库技术。即定义“什么特征”。在这个阶段你可以选择采用关系模型、维度模型等作为你的设计蓝图。产出物详细的ER图或表结构定义字段名、类型、主外键关系。3. 物理模型核心在逻辑模型基础上针对具体使用的数据库和硬件环境进行最优化设计。即定义“怎么存”。产出物如分区策略按日期分区、分桶、索引、压缩方式、存储格式Parquet/ORC、数据生命周期等。三、逻辑建模的几种主流方法模型核心思想典型结构适用场景代表领域关系模型基于数学集合论通过外键关联遵循范式通常3NF消除冗余大量规范化的小表像一张网OLTP在线事务处理如银行交易、电商订单等高频增删改业务。MySQL, PostgreSQL, Oracle维度模型分为事实表可度量数值和维度表描述事实的文本反范式化便于分析星型模型、雪花模型、星座模型OLAP在线分析处理如数据仓库、BI 报表、商业智能分析Hive, ClickHouse, Doris, TableauData Vault模型混合范式强调历史追溯和可扩展性。由中心表Hub、链接表Link、附属表Satellite组成类似分布式系统的图谱企业级数据仓库EDW需要完整历史变化、适应源系统频繁变更大型金融、电信企业数仓四、关系模型关系模型由E.F. Codd于1970年提出是主流关系型数据库MySQL、PostgreSQL、Oracle等的理论基础。其核心思想是将所有数据基于实体和关系表示为二维表表与表之间通过外键关联并严格遵守范式通常到第三范式确保数据无重复、无歧义。术语通俗解释例子学生表关系一张二维表Student表元组表中的一行一条学生记录属性表中的一列学号,姓名,班级域属性的取值范围性别域{‘男’, ‘女’}候选键能唯一标识一行的一个或多个属性学号每个学生唯一主键选定的一个候选键学号外键一个表中的属性引用另一个表的主键选课表中的学号引用学生表的学号三范式是规范化过程的核心标准目的是消除数据冗余、避免插入、更新、删除异常。从第一范式到第三范式要求逐渐严格。注意还有BCNF巴斯-科德范式是3NF的改进、第四、第五范式等但实际工程中通常满足3NF就足够了。1. 第一范式1NF所有字段原子性定义关系中的每个属性都必须是不可再分的原子值不允许表中套表或集合、数组等复合结构。解决的问题保证字段的原子性使每个字段只存储单一值。违规示例学号姓名所选课程001张三数学, 英语002李四语文这里“所选课程”列存储了多个课程用逗号分隔不是原子值。符合1NF的改造拆成多行每行一个课程。学号姓名课程001张三数学001张三英语002李四语文2. 第二范式2NF所有非主属性完全依赖于整个候选键定义满足1NF且不存在部分函数依赖。即所有非主属性必须完全依赖于整个候选键而不是候选键的一部分。解决的问题消除“部分依赖”导致的冗余和异常常见于联合主键表。违规示例学生选课成绩表联合主键为(学号, 课程号)。学号课程号课程名称成绩001C01数学90001C02英语85002C01数学92这里候选键是(学号, 课程号)但课程名称只依赖于课程号而不依赖于学号→ 这就是部分函数依赖。存在的问题冗余数学课程名称在每个选该课的学生记录中都重复存储。更新异常若把“数学”改成“高等数学”需要更新多行。删除异常若某课程只有一名学生且该生退课课程信息会丢失。符合2NF的改造将表拆分为两个表。选课表(学号, 课程号, 成绩)主键(学号, 课程号)。课程表(课程号, 课程名称)主键课程号。3. 第三范式3NF每个非主属性直接依赖于主键无传递依赖定义满足2NF且不存在传递函数依赖。即非主属性不能依赖于其他非主属性必须直接依赖于主键。解决的问题消除“传递依赖”导致的冗余和异常。违规示例学生表如下主键为学号。学号姓名班级编号班级名称班主任001张三B01计算机1班王老师002李四B01计算机1班王老师003王五B02计算机2班李老师这里学号 → 班级编号 → 班级名称, 班主任。非主属性班级名称和班主任依赖于班级编号而班级编号依赖于学号→传递依赖。存在的问题冗余班级信息重复存储每个学生都存一遍班级名称、班主任。更新异常若计算机1班班主任换成张老师需要更新所有该班学生的记录。符合3NF的改造拆分为两个表。学生表(学号, 姓名, 班级编号)主键学号外键班级编号引用班级表。班级表(班级编号, 班级名称, 班主任)主键班级编号。4. 三范式总结范式核心要求违规特征解决方法1NF列不可再分某列包含多个值逗号列表、数组拆分为多行或拆分新表2NF消除部分依赖联合主键下某列只依赖部分主键拆分出依赖该部分主键的新表3NF消除传递依赖非主键列依赖于其他非主键列拆分出依赖的非主键列作为新表主键五、维度模型维度模型是数据仓库大师 Ralph Kimball 提出的一种面向分析的逻辑模型。其核心思想是将数据组织成“事实”和“维度”两部分通过星型或雪花型结构提供高性能、易理解的查询体验。1. 事实表Fact Table事实表存储业务过程的可度量的事件或指标如销售额、数量、金额以及关联维度表的外键。每一行对应一个业务事件或一个度量快照。1.1 事实表的类型按粒度划分类型说明示例示例字段事务事实表记录每个业务事务/事件的一行。它是粒度最细、最原子化的事实表通常反映一个可度量的业务操作如一次订单下单一次交易。电商订单事务表记录每笔订单中每个商品的销售明细订单ID、商品ID、单价、数量、金额、时间戳。周期快照事实表在固定时间间隔如每天、每月对某个业务状态进行快照它记录的是某个时间点的度量值而不是事务的发生过程。常用于累积度量如账户余额、库存量。每日库存快照表记录每个仓库中每个商品在每天结束时的库存量。日期、仓库ID、商品ID、库存量。累积快照事实表记录一个完整业务流程从开始到结束的各个里程碑如订单的创建、支付、发货、完成通常有多个时间外键。订单生命周期表每个订单一行记录从创建到完成的关键时间点。如待支付、已支付、已发货等。订单ID、创建日期、支付日期、发货日期、订单金额、状态。无事实的事实表是一种特殊的事实表它没有数值度量只包含维度的外键组合用于记录事件是否发生。每一行代表一个事实某个组合的维度在某个时间点存在。学生考勤表记录某天某学生是否出勤。日期、学生ID、课程ID、出勤标志 (可省略)事务明细 → 事务事实表。周期性状态 → 周期快照事实表。业务流程跟踪 → 累积快照事实表。仅记录存在性 → 无事实事实表。1.2 事实表的实现方式类型数据来源要点事务事实表来源于业务系统的事务表ODS层通常是按时间戳增量抽取加载方式追加不做历史更新。分区策略按业务日期如dt字段分区提升查询性能。周期快照事实表方式一基于事务事实表聚合如计算每月的交易情况汇总。方式二直接从源系统的状态表抽取如每日末的账户余额表。通常需要全量或增量计算可维护 前一日快照 当日增量 的方式拉链思想。累积快照事实表事务事实表或业务流程日志如订单的创建、支付、发货等事件。需要能够跟踪同一业务流程实例的多个里程碑状态。每当流程推进到新里程碑时更新该行的对应里程碑日期字段和状态字段。无事实的事实表来源A维度组合的“覆盖关系”。例如学生选课学生维度 × 课程维度但只保留实际选课的组合。来源B事件日志中仅记录“发生”而无数值的事件如页面浏览无金额。没有度量列或仅有一个常量列如dummy 1以方便计数。加载方式追加事件型或 全量覆盖关系型。2. 维度表Dimension Table维度表是存储描述性信息的表用于解释事实表记录的业务属性 。比如客户维度描述的是客户的信息像客户信息、身份证等。2.1 缓慢变化维Slowly Changing Dimensions, SCD缓慢变化维是数据仓库中处理维度属性随时间变化的一类方法。维度表里的数据并非一成不变例如客户搬家、产品重分类但变化频率相对较低不像事实表每秒都在增长因此称为“缓慢变化”。那么当维度的属性发生变化时如何管理历史事实与维度属性的关联呢针对此问题SCD 提供了以下策略SCD类型处理方式适用场景Type 0保留原始值不变属性具有不可变语义如“原始注册日期”、“身份证号”。Type 1直接覆盖旧值历史信息将丢失不需要保留历史例如修正错误信息Type 2当属性变化时插入一行新记录旧行保留。通过生效日期、失效日期、当前标志等字段区分。通过拉链表实现需要完整保留历史例如客户地址变化情况Type 3增加新列上一个值同时保留当前值和前值只需要知道变化前后且变化次数有限如一次比如当天余额和前一天余额Type 4微型维度 / 历史表将频繁变化的属性如客户信用分单独拆出形成一个微型维度或称为历史拉链表主维度只保留当前值。与Type2的区别都是通过拉链表实现但是type2是在表内实现的type4是单独拆表的Type 6混合类型结合 Type 1、2、3 的特性使用 Type 2 保留完整历史每次变化增加一行记录生效/失效日期。同时增加 Type 3 风格的“当前属性列”让历史行也能直接关联到当前的属性值。对于历史行当前属性列会随着现实世界的变化而更新Type 1 风格但历史属性值保留不变。2.2 维度模型类型2.2.1 星型模型 (Star Schema)星型模型是最简单、最常用的维度模型。它由一个中心事实表和多个直接连接的维度表组成形似星星。如上图销售事实表中心事实表关联了多个直连的维度表产品、时间、商店维度。这种方式查询比较简单只需要连接事实表与相关维度表但是可能存在数据冗余的情况。2.2.2 雪花模型 (Snowflake Schema)雪花模型是星型模型的规范化版本。它将维度表进一步拆分成多个子维度表如将产品维度拆分为产品类别表、品牌表形成类似雪花的形状。如上图在星型模型的基础上将产品维度表进一步拆分成了产品分类维度表。这种方式可以消除维度表中的数据冗余如产品类别名称只存一份。2.2.3 星座模型 (Constellation Schema / Fact Constellation)星座模型又称星系模型包含多个事实表这些事实表共享部分维度表。它是对星型模型的扩展用于处理多个业务流程或不同粒度的事实。多事实表支持跨业务过程的分析如同时分析销售和库存。共享维度减少冗余保证一致性同一日期维度在两个事实表中含义相同。如上图在星型模型的基础上新增了库存情况事实表。2.2.4 三者对比总结特性星型模型雪花模型星座模型事实表数量1个1个≥2个维度表形式单层非规范化多层规范化可共享维度每个事实表可能是星型或雪花存储空间较大冗余较小介于两者之间共享维度减少冗余查询性能高低多连接高每个事实表独立查询跨事实查询复杂易用性高低中需理解多个事实表维护复杂度低中高六、Data Vault模型七、数仓分层架构八、数据表类型增量表、全量表、拉链表