一直以来，系统的数据模型设计都是摆在研发面前的坎，我甚至觉得它是最大的那个难关，除非你对数据模型设计重视程度不够。
    数据模型对系统稳定性起到了决定性的作用，为什么这么说？你可以回想一下自己的经历，是不是经常有初始数据表设计不合理，业务发展后被迫重构返工的案例？是不是有因为表的设计别扭，导致业务逻辑特别复杂，性能差，维护难度指数级增长？是不是有数据模型考虑不全面，导致一些关键业务快照没有留存，无法回溯？这些，都在说明，数据模型设计重中之重。我希望通过这样一篇文章，能让你在数据设计时不再犯错，纠结。

一、核心指导思想

在讨论具体表类型设计之前，先明确几个贯穿所有设计的基本原则：

1. 范式与反范式的平衡：

范式化（如3NF）：目的是消除冗余，保证数据一致性。适用于核心业务数据（如用户、商品）。

反范式化：

  • 适当引入冗余，以减少关联查询，提升查询性能。适用于那些几乎不太变更的字段，否则会造成写入失控。

  • 适当使用json串合并多个字段存储：

    • 适用场景：

      • 动态属性：商品的不同品类有完全不同的属性（如手机的“CPU”、书籍的“ISBN”）。主表存通用信息，JSON字段存动态属性。

      • 配置信息：存储用户的自定义设置、系统的动态配置。

      • API请求/响应日志：完整记录请求体和响应体，结构可能经常变化。

      • 轻度关联的数据：如一篇文章的“点赞用户ID列表”，查询时不需要关联user表，只在程序内解析使用。

    • 不适用场景：

      • 需要作为查询条件（WHERE）或需要索引的字段。虽然MySQL支持对JSON字段建函数索引，但性能和维护性不如原生字段。

      • 需要参与关联查询（JOIN）的数据。

      • 数据需要频繁更新其中某个特定键。更新整个JSON列的效率较低。

      • 数据具有稳定、明确的Schema。这种情况应优先使用规范化的表结构。

    2. 读写权衡：根据表的读写比例（如 读:写 = 9:1 还是 1:9）来决定是优化查询还是优化写入。

    3. 数据生命周期：明确数据的“热度”（热数据、温数据、冷数据），并制定相应的存储、归档和清理策略。举个例子：①对于数据量大的表，业务上经常要使用的数据，放在在线业务库的业务表；②而用的不那么频繁，但是业务上还是会有些场景使用，比如说，一段时间内的数据有可能重新拉回到在线业务表，这种可以存储到在线业务库的历史表；③如果是很久的数据，业务上只是做查询使用或者不要求快速拉回的，则可存放在大数据的存储查询介质，如drios；④而对于业务上已不再需要使用的数据，则可归档到数仓。

    4. 业务驱动：一切设计都要回归业务需求。为什么这么设计？是为了满足哪个业务场景？未来它会不会有其他的扩展可能？

    5. 拆分合并原则：

      • 单一职责原则

        • 合：当多个字段描述的是同一个实体的不同方面，且访问模式（指的是查询频率，更新频率等）相似时；

        • 拆：当字段属于不同的业务概念，或访问模式差异很大时；

      • 访问模式原则

        • 合：字段经常在同一个查询中被同时访问；

        • 拆：字段的访问频率和模式差异很大（冷热数据分离）；

      • 数据量原则

        • 合：数据量小，增长缓慢的表；

        • 拆：数据量大，快速增长的表；

        • 合：拥有同样的字段，但数据量级差异不是特别大，如机构统计指标（百级）及分组（千级或万级）统计指标，量级差异不大，可以合；

        • 拆：拥有同样的字段，但数据量级差异很大，如用户统计指标（千万级别）及地域统计指标（百级别），数据不是一个量级，需要拆，避免查询地域统计指标时，效率极低；

      • 变更频率原则

        • 合：字段变更频率相似；

        • 拆：字段变更频率差异很大，且表字段特别多（超过50个）；

      • 大字段隔离原则

        • 合：如果数据量并不大，数据每日增量不大，表中text或者BLOB大字段可不做拆分；

        • 拆：如果数据量大，单表每日增量接近万级或以上，那text或者BLOB大字段需拆子表；

    6. 设计模板：我们一开始就要对数据模型设计有明确的模板，让研发能明确知道他们应该在表设计的时候考虑哪些点，下面就是一个我在公司推行的一个模板：

    二、确认业务表之间的关系

    关系分为一对一、多对一、一对多、多对多、继承；

    1、一对一关系

    • 一个大量属性的业务实体，根据更新访问频率、后期扩展性、大字段等划分成多个表；

    • 例如根据更新访问频率划分客户基本信息及客户扩展信息。文章实体因为文章内容是大字段，划分为文章基础信息和文章内容信息。这些方法，能有效避免数据页频繁分裂，对查询，插入性能，规避binlog延迟都有很大的用处；

    • 一个申请审批流程，业务刚开始只需要一轮审批，那申请和审批是一对一的，但是为了兼容后续有多轮审批需求，将申请信息和审批信息拆成2个表；

    2、多对一关系

    • 多对一关系在数据库设计上比较简单，在下面案例中，一个过错行为对应一个税务人员、一个纳税人与一个过错类型；同时，一个税务人员，或纳税人，或过错类型，都可以对应多个过错行为。它们就形成了“多对一”关系。多对一关系一般都是在“多”对应的表增加关联字段；

      3、一对多关系

      • 譬如，下边案例中的“申辩申请单”与“申辩申请单明细”就是“一对多”关系。除此之外，订单与订单明细、表单与表单明细，都是一对多关系。一对多关系在数据库设计上比较简单，就是在子表中增加一个外键去引用主表中的主键；

      4、多对多关系

      • 比较典型的例子就是“用户角色”与“功能权限”。一个“用户角色”可以申请多个“功能权限”；而一个“功能权限”又可以分配给多个“用户角色”使用，这样就形成了一个“多对多”关系。这种多对多关系在对象设计时，可以通过一个“功能–角色关联类”来详细描述。因此，在数据库设计时就可以添加一个“角色功能关联表”，而该表的主键就是关系双方的主键进行的组合，形成的联合主键；

      5、继承关系

      • 方案一：见下面案例。“执法行为”通过继承分为“正确行为”和“过错行为”。如果这种继承关系的子类不多（一般就2 ~ 3 个），并且每个子类的个性化字段也不多（3 个以内）的话，则可以使用一个表来记录整个继承关系。在这个表的中间有一个标识字段，标识表中的每条记录到底是哪个子类或者哪个场景，哪个类型；

      • 采用这个方案的优点是简单，整个继承关系的数据全部都保存在这个表里。但是，它会造成“表稀疏”。在该案例中，如果是一条“正确行为”的记录，则字段“过错类型”与“扣分”永远为空；如果是一条“过错行为”的记录，则字段“加分”永远为空。假如这个继承关系中各子类的个性化字段很多，就会造成该表中出现大量字段为空，称为“表稀疏”。在关系型数据库中，为空的字段是要占用空间的。因此，这种“表稀疏”既会浪费大量存储空间，又会影响查询速度，是需要极力避免的。所以，当子类比较多，或者子类个性化字段多的情况是不适合该方案的；

      • 方案二：如果执法行为按照考核指标的类型进行继承，分为“考核指标1”“考核指标2”“考核指标3”……。并且每个子类都有很多的个性化字段，则采用前面那个方案就不合适了。这时候我们可以将每个子类都对应到一个表，有几个子类就有几个表，这些表共用一个主键，即这几个表的主键生成器是一个，某个主键值只能存在于某一个表中，不能存在于多个表中。每个表的前面是父类的字段，后面罗列各个子类的字段，如图所示；

      • 如果业务需求是在前端查询时，每次只能查询某一个指标，那么采用这种方案就能将每次查询落到某一个表中，方案就最合适。但如果业务需求是要查询某个过错责任人涉及的所有指标，则采用这种方案就必须要在所有的表中进行扫描，那么查询效率就比较低，并不适用。当然我们有时候是可以让业务妥协，查询时让指标类型成为一个必选项；

      • 方案三：如果业务需求是要查询某个过错责任人涉及的所有指标，则更适合采用以下方案，将父类做成一个表，各个子类分别对应各自的表（如图所示）。这样，当需要查询某个过错责任人涉及的所有指标时，只需要查询父类的表就可以了。如果要查看某条记录的详细信息，再根据主键与类型字段，查询相应子类的个性化字段。这样，这种方案就可以完美实现该业务需求；

          

      三、表分类及设计原则、使用场景

      1. 批次表

      设计原则：将一组离散但相关的操作抽象为一个逻辑单元，并记录这个单元的元信息（状态、时间、操作人）。核心是 “管控” 和 “溯源”。

      使用场景：

      • 批量任务：如批量导入用户、批量生成优惠券、批量发货。需要跟踪整个批量操作的成功与否。

      • 对账与结算：金融场景中，每日的结算跑批会生成一个结算批次，记录结算的日期、范围和状态。

      • 数据同步或ETL：记录每次数据同步的批次，如果同步失败，可以追溯到是哪一批次的数据出了问题。

      示例：

      2. 日志表 / 流水表

      这两者非常相似，都强调 “记录事实”，一旦生成，绝不允许修改（Update）。它们的区别在于：

      • 日志表：更偏向于 “记录系统行为”，如操作日志、系统错误日志、API调用日志。

      • 流水表：更偏向于 “记录业务状态的变化”，是核心业务的一部分，如账户交易流水、订单状态流水。

      设计原则：

      • 只追加，不修改。

      • 通常没有唯一主键，或主键是自增ID，与业务无关。

      • 对查询性能要求高，通常需要按时间范围进行查询。

      • 数据量巨大，必须有归档和清理策略。

      使用场景：

      • 操作日志表：记录用户（或管理员）的关键操作，用于安全审计。（操作人、操作时间、操作类型、IP、请求参数、结果）

      • 交易流水表：记录每一笔资金的进出。（流水号、账户ID、交易类型、金额、交易前余额、交易后余额、对手方信息、时间）。这是对账的黄金标准。

      • 订单状态流水表：记录订单从创建、付款、发货到收货的每一个状态变化节点和时间。（订单ID、前状态、新状态、操作人/系统、时间）。用于追踪订单生命周期。

      示例（交易流水）：

      3. 拉链表

      设计原则：处理 “缓慢变化维（SCD）” 问题，高效存储历史快照。它记录一个事实在生命周期内，各个时间段的状态。核心字段是start_date和end_date。

      使用场景：

      • 会员等级变化：需要查询用户在某个历史时间点（例如去年双十一）的等级。

      • 员工部门调动：需要统计某个员工在任意时间段内在哪个部门。

      • 商品价格变化：需要分析某个商品在促销期间的价格。

      设计要点：

      • start_date：该记录生命周期的开始。

      • end_date：该记录生命周期的结束。通常用一个极大的值（如9999-12-31）代表当前有效记录。

      • 当数据发生变化时，不是修改原记录，而是关闭旧记录（更新其end_date），并插入一条新的当前有效记录。

      示例（会员等级拉链）：

      4. 配置表 / 字典表

      设计原则：存储系统运行所需的静态配置数据、枚举值和业务字典，实现配置与代码分离。

      使用场景：

      • 系统参数配置：如超时时间、重试次数、开关配置等

      • 业务字典：订单状态、支付方式、国家地区代码等

      • 枚举值存储：性别、用户类型、优先级等

      • 前端下拉选项：分类选项、标签选项等

      示例：

      5. 汇总表 / 统计表

      设计原则：预计算和存储聚合结果，用空间换时间，提升复杂统计查询性能。

      使用场景：

      • Dashboard数据：实时展示的运营数据、KPI指标

      • 报表系统：日/月/年统计报表

      • 排行榜：用户积分榜、商品销量榜

      • 缓存复杂查询结果：需要多表关联和复杂计算的统计结果

      示例：

      6. 队列表 / 任务表

      设计原则：基于数据库实现简单的消息队列或任务调度，保证任务的可持久化和重试。

      使用场景：

      • 异步任务处理：发送邮件、短信、推送通知

      • 分布式任务调度：定时批处理任务

      • 工作流引擎：流程审批、状态机流转

      • 数据同步：跨系统数据同步任务

      示例：

      7. 版本表 / 历史表

      设计原则：存储核心数据的版本历史，支持数据追溯和版本回滚。

      使用场景：

      • 合同/协议版本：存储每次修改的完整版本

      • 配置变更历史：系统配置的每次变更记录

      • 文档版本管理：Wiki、知识库的版本控制

      • 审核流程：存储每次审核的完整快照

      示例：

      8. 地理空间表

      设计原则：存储和查询地理空间数据，支持位置相关业务。

      使用场景：

      • 地理位置服务：门店位置、配送范围

      • 区域划分：行政区域、商圈划分

      • 距离计算：附近的人、附近的商家

      • 地理围栏：电子围栏、区域监控

      示例：

      9. 全文检索表

      设计原则：优化文本搜索性能，支持复杂的搜索需求。

      使用场景：

      • 搜索引擎：商品搜索、内容搜索

      • 标签搜索：多标签组合查询

      • 模糊匹配：名称、描述的模糊查询

      示例：

      10. Key-Value竖表

      设计结构：键值对竖表，也称为垂直表、属性表或EAV（Entity-Attribute-Value）模型。其基本结构如下：

      使用场景：

      • 动态属性系统

        • 电商平台中不同品类的商品有完全不同属性

        • CRM系统中客户自定义字段

        • 配置管理系统

          一篇文章彻底掌握数据模型设计

        • 标签，变量等；

      • 稀疏数据存储

        • 大多数实体只有少数几个属性有值

        • 避免传统表中大量NULL字段

      • 元数据驱动系统

        • 需要运行时动态添加字段

        • 多租户SaaS应用中不同租户需要不同字段

      现代替代方案：

      随着数据库发展，目前MYSQL8.0已经很好的支持了JSON数据类型，现在更推荐使用 JSON字段 作为 EAV 的替代：

      适用场景：

      • 标签是动态的，可能经常变化。

      • 标签的结构不一致，不同用户可能有不同的标签集合。

      • 不需要对标签进行复杂的关联查询（如多表连接），而是通过用户ID直接获取所有标签。

      • 读取频率远高于更新频率。

      不适用场景：

      • 需要频繁地对单个标签进行增删改（因为更新整个JSON字段成本较高）。

      • 需要根据标签值进行复杂的查询（如范围查询、多条件组合查询等）。

      • 需要跨用户对标签进行聚合查询（如统计拥有某个标签的用户数量）。

      如何提高JSON字段的检索效率：

      • 使用JSON索引：如果你需要根据某个特定的标签键值对进行查询，可以考虑使用生成列（Generated Column）并在其上创建索引。

      例如，假设我们有一个users表，其中有一个tags的JSON字段，我们想根据标签department的值来查询用户。

      如果一个用户的标签非常多，几百上千个，而且我们每次都只是使用某类标签，那我们可以对标签进行分类，如用户画像标签，用户还款标签，用户等级标签等，采用对象嵌套的方式存储到json，一次性检索出一类标签：
      当然，如果你需要不固定的经常性检索某个标签的值，而标签又特别多，也可以采用混合方案（JSON + 关系表）：
      如果你的系统要求极致性能，就可以采用Bitmap 位图方案了，这个下面会讲到。

      11. Bitmap位图表

      设计结构：位图表利用位运算来存储多个布尔值或状态值，通常用一个整数类型的字段（如BIGINT）来存储多个标志位。

      使用场景：

      • 多状态标志存储

        • 用户权限系统

        • 功能开关配置

        • 标签系统

      • 高效集合运算

        • 用户分群（具有某些特征的用户集合）

        • 商品标签筛选

      • 实时统计计数

        • 在线用户统计

        • 实时数据分析

      示例：

      12. 树形结构表

      设计原则：高效存储和查询层次结构数据。

      常用四种设计模式：

      ①邻接表模式：

      • 结构：每个节点存储其父节点的ID。

      • 推荐使用场景：

        • 深度固定的浅层树

          • 组织架构（公司-部门-小组）

          • 评论系统（通常限制2-3级回复）

          • 菜单导航（通常3-4级深度）

        • 频繁的增删改操作

          • 需要经常添加、删除、移动节点

          • 业务变动频繁的树形结构

        • 简单父子关系查询

          • 主要查询直接父节点或直接子节点

          • 不需要复杂的层级遍历

      • 不适用场景：

        • 需要查询所有子孙节点

        • 需要查询完整路径

        • 树的深度很大且需要高效查询

      ②路径枚举模式：

      • 结构：每个节点存储从根节点到当前节点的路径（如用字符串存储像’/1/2/3’这样的路径）。

      • 推荐使用场景：

        • 频繁的路径查询

          • 需要快速获取节点的完整路径

          • 面包屑导航、位置显示

        • 固定深度的分类系统

          • 商品分类（如: 家电/大家电/空调）

          • 文章分类、文件目录

        • 基于路径的权限控制

          • 需要根据路径模式进行权限验证

        • 数据库不支持递归查询的环境

          • 老版本MySQL等

      • 不适用场景：

        • 树结构频繁变动（移动节点成本高）

        • 路径长度可能很长（受限于字段长度）

        • 需要频繁查询所有子孙节点

      ③闭包表模式：

      • 结构：使用一个额外的表来存储节点之间的关系，包括直接和间接的父子关系，每条记录存储祖先节点和后代节点以及它们之间的深度（可选）。

      • 推荐使用场景：

        • 复杂的层级关系查询

          • 需要频繁查询任意深度的子孙节点

          • 需要频繁查询任意深度的祖先节点

          • 需要计算节点间的距离

        • 树结构稳定，查询频繁

          • 权限菜单系统

          • 知识库分类

          • 地区行政区划

        • 需要高性能的树遍历

          • 对查询性能要求极高的场景

      • 不适用场景：

        • 树结构频繁变动（维护成本高）

        • 数据量极大（关系表会快速增长）

        • 简单的父子关系查询（杀鸡用牛刀）

      ④混合模式：
      • 方案1：邻接表 + 路径枚举

      • 邻接表 + 闭包表（查询优化）

      四、数据模型设计的一些高阶规范

      1. 字段设计规范

      1.1 选择合适的数据类型

      • 尽可能小：在满足业务需求的情况下，选择尽可能小的数据类型。例如，如果年龄范围在0-200，使用TINYINT UNSIGNED（0-255）而不是INT。

      • 避免使用NULL：除非必要，否则将字段设置为NOT NULL。因为NULL值会使索引、索引统计和值比较都更复杂。如果业务上允许，可以使用默认值（如空字符串、0等）代替NULL。

      • 字符串类型：

        • 固定长度使用CHAR，可变长度使用VARCHAR。

        • VARCHAR长度不要超过实际需要，避免过度分配。

        • 对于大量文本，使用TEXT类型，并考虑与主表分离（避免影响主表查询性能）。

      • 数值类型：

        • 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，根据范围选择。

        • 小数类型：DECIMAL用于精确计算（如金额），FLOAT和DOUBLE用于近似计算。

      • 时间类型：

        • DATE、TIME、DATETIME、TIMESTAMP。注意TIMESTAMP的范围（1970-2038）和时区问题。

        • 通常推荐使用DATETIME（范围更大）或TIMESTAMP（自动时区转换，但注意范围）。

      1.2 每个表必须有主键

      • 主键最好是单列、简单（如整数类型）且不变（不使用可能会改变的列，如姓名）。

      • 推荐使用自增主键（AUTO_INCREMENT）或业务无关的唯一标识（如UUID，但注意UUID的存储和索引性能问题）。

      • 如果使用自增主键，注意在分布式环境下可能使用雪花算法等生成唯一ID。

      1.3 大字段分离

      • 将TEXT、BLOB等大字段单独存放到扩展表中，避免在查询主表时由于大字段导致性能下降。

      1.4 关联字段统一

      • 表之间关联的字段需要保证数据类型，长度，排序规则（这个在表级别统一设置即可，不要在每个字段指定）都一致。

      2. 索引设计规范

      2.1 索引选择原则

      • 索引不是越多越好，每个索引都会降低写操作（INSERT、UPDATE、DELETE）的速度。

      • 为查询条件（WHERE）、连接条件（JOIN）、排序（ORDER BY）和分组（GROUP BY）的列创建索引。

      2.2 索引类型选择

      • 主键索引：每张表都必须有主键索引。

      • 唯一索引：保证数据唯一性，如手机号、邮箱等。

      • 普通索引：加速查询。

      • 复合索引：注意索引列的顺序，遵循最左前缀原则。

      • 覆盖索引：索引包含查询所需的所有字段，避免回表。

      2.3 复合索引设计

      • 将选择性高的列放在前面（选择性=不重复的值数量/总记录数）。

      • 考虑查询条件的使用频率和顺序。

      • 避免创建重复索引，如已有索引(a,b)，再创建索引(a)就是多余的。

      2.4 唯一索引设计

      • 可根据业务需要，适当建单字段唯一索引，且单字段是全局整体增长的，如订单号，序列号，流水号。能不建就不建，在应用程序层保证数据不重复。

      • 尽量避免建复合唯一索引和多个唯一索引，即使事务级别是RC，理论上基本都是加行锁，但是多字段联合的唯一索引，在批量插入的场景下，“唯一索引冲突检测”，会让你在 RC 级别“意外”碰到间隙锁/Next-Key Lock。因为每次多线程并行批量插入，多字段组成的唯一键，它并没有顺序性，导致互相等待间隙释放，一般数据库死锁很多都是它引起的。

      2.5 索引禁忌

      • 不要在索引列上使用函数或表达式，会导致索引失效。

      • 避免在索引列上使用范围查询（如<>、NOT IN、LIKE以通配符开头），这会导致索引失效。

      • 避免在索引列上使用NULL值，如果字段可能为NULL，考虑使用默认值。

      2.6 索引数量控制

      • 单张表索引数量建议不超过5个。

      2.7 长字符串索引

      • 对长字符串列建立索引，可以考虑使用前缀索引（例如：INDEX (column_name(10))），但注意选择性。

      3. 其他规范

      3.1 字符集和排序规则

      • 统一使用UTF8MB4字符集，支持所有字符包括emoji。

      • 排序规则根据业务选择，如utf8mb4_general_ci或utf8mb4_unicode_ci。

      3.2 避免使用触发器、存储过程、分区

      • 除非必要，否则避免使用触发器、存储过程、分区，因为不利于维护和扩展。