主表 + 扩展表设计模式
主表 + 扩展表设计模式
一、解决什么问题
随着业务迭代，核心表的字段会不断膨胀：
- 初始建表 20 个字段
- 第一年迭代加到 50 个
- 第二年加到 80 个
- 第三年加到 100+ 个
带来的问题：
| 问题 | 影响 |
|---|---|
| 单行数据过宽 | InnoDB 页（16KB）能存的行数减少，查询扫描 IO 增大 |
| DDL 风险 | ALTER TABLE 加字段可能锁表（MySQL 5.6 以前），大表加字段耗时长 |
| 职责不清 | 50 个字段混在一起，哪些是核心字段、哪些是扩展功能不清晰 |
| 查询性能 | SELECT * 拉取大量不需要的字段，浪费网络和内存 |
| 并发冲突 | 不同业务更新同一行的不同字段，行锁竞争加剧 |
主表 + 扩展表通过垂直拆分，将高频核心字段和低频扩展字段分离，解决以上问题。
注：
博客：
https://blog.csdn.net/badao_liumang_qizhi
二、核心设计思路
┌────────────────────────────┐ 1:1 ┌──────────────────────────────┐ │ 主表 (master) │ ──────────→ │ 扩展表 (extend) │ ├────────────────────────────┤ ├──────────────────────────────┤ │ id (PK) │ │ id (PK) │ │ order_no │ │ master_id (FK/UK) │ │ status │ │ master_code │ │ amount │ │ extra_field_1 │ │ create_time │ │ extra_field_2 │ │ ... (核心高频字段) │ │ ... (扩展低频字段) │ └────────────────────────────┘ └──────────────────────────────┘关键约束
- 一对一关系：扩展表的
master_id加唯一索引 - 可选关系：不是每条主表记录都有扩展记录（按需创建）
- 关联字段冗余：通常同时存
master_id和master_code，方便按 ID 或编号查询
三、拆分策略
3.1 按更新频率拆分
| 主表（高频读写） | 扩展表（低频更新） |
|---|---|
| 状态、金额、时间 | 审计信息、重试次数、外部系统状态 |
| 每次操作都会更新 | 只在特定场景更新 |
3.2 按业务维度拆分
| 主表（核心业务） | 扩展表A（物流相关） | 扩展表B（财务相关） |
|---|---|---|
| 订单号、客户、金额 | 物流公司、运单号、签收状态 | 发票号、税率、开票状态 |
3.3 按数据生命周期拆分
| 主表（创建时确定） | 扩展表（后续补充） |
|---|---|
| 下单时的固定数据 | 发货后才产生的数据 |
| 修改极少 | 异步回写 |
四、与其他方案的对比
| 方案 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| 垂直拆分（扩展表） | 结构清晰、核心表轻量、独立维护 | JOIN 查询、分布式事务 | 字段多且可按维度拆分 |
| JSON 字段 | 灵活、不改表结构 | 索引困难、无类型校验、查询复杂 | 动态属性、不确定字段 |
| EAV 模型 | 极度灵活、无限扩展 | 查询极慢、无类型安全、代码复杂 | CMS/表单引擎 |
| 宽表不拆 | 简单、无 JOIN | 表膨胀、DDL 风险、职责不清 | 字段少且稳定 |
| 水平分表 | 解决数据量大 | 不解决字段多的问题 | 行数超千万级 |
五、代码示例（通用：用户主表 + 扩展表）
5.1 数据库表结构
-- 用户主表：核心高频字段CREATETABLEt_user(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(64)NOTNULLCOMMENT'用户名',phoneVARCHAR(20)NOTNULLCOMMENT'手机号',statusTINYINTNOTNULLDEFAULT1COMMENT'状态 1启用 0禁用',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_phone(phone),INDEXidx_username(username))COMMENT'用户主表';-- 用户扩展表：低频/后补充字段CREATETABLEt_user_extend(idINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULLCOMMENT'用户ID',user_nameVARCHAR(64)COMMENT'用户名（冗余，便于按名称查询）',avatar_urlVARCHAR(512)COMMENT'头像URL',bioVARCHAR(1000)COMMENT'个人简介',vip_levelTINYINTDEFAULT0COMMENT'VIP等级',vip_expire_timeDATETIMECOMMENT'VIP过期时间',last_login_ipVARCHAR(50)COMMENT'最后登录IP',last_login_timeDATETIMECOMMENT'最后登录时间',login_countINTDEFAULT0COMMENT'累计登录次数',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,UNIQUEINDEXuk_user_id(user_id))COMMENT'用户扩展表';5.2 实体类
/** * 用户主表实体. */@Data@Entity@Table(name="t_user")publicclassUserEntity{@Id@GeneratedValue(strategy=GenerationType.IDENTITY)privateIntegerid;@Column(name="username",nullable=false)privateStringusername;@Column(name="phone",nullable=false)privateStringphone;@Column(name="status",nullable=false)privateIntegerstatus;@Column(name="create_time")privateDatecreateTime;@Column(name="update_time")privateDateupdateTime;}/** * 用户扩展表实体. */@Data@Entity@Table(name="t_user_extend")publicclassUserExtendEntity{@Id@GeneratedValue(strategy=GenerationType.IDENTITY)privateIntegerid;/** 关联用户主表ID. */@Column(name="user_id",nullable=false)privateIntegeruserId;/** 冗余用户名，便于查询. */@Column(name="user_name")privateStringuserName;/** 头像URL. */@Column(name="avatar_url")privateStringavatarUrl;/** 个人简介. */@Column(name="bio")privateStringbio;/** VIP等级. */@Column(name="vip_level")privateIntegervipLevel;/** VIP过期时间. */@Column(name="vip_expire_time")privateDatevipExpireTime;/** 最后登录IP. */@Column(name="last_login_ip")privateStringlastLoginIp;/** 最后登录时间. */@Column(name="last_login_time")privateDatelastLoginTime;/** 累计登录次数. */@Column(name="login_count")privateIntegerloginCount;@Column(name="create_time")privateDatecreateTime;@Column(name="update_time")privateDateupdateTime;}5.3 Repository
publicinterfaceUserRepositoryextendsJpaRepository<UserEntity,Integer>{UserEntityfindByPhone(Stringphone);}publicinterfaceUserExtendRepositoryextendsJpaRepository<UserExtendEntity,Integer>{/** 通过用户ID查找扩展信息. */UserExtendEntityfindByUserId(IntegeruserId);/** 批量查找扩展信息. */List<UserExtendEntity>findByUserIdIn(List<Integer>userIds);}5.4 Service 层（核心：按需创建扩展记录）
@ServicepublicclassUserServiceImplimplementsUserService{privatefinalUserRepositoryuserRepository;privatefinalUserExtendRepositoryuserExtendRepository;publicUserServiceImpl(UserRepositoryuserRepository,UserExtendRepositoryuserExtendRepository){this.userRepository=userRepository;this.userExtendRepository=userExtendRepository;}/** * 注册用户（只创建主表记录，扩展表按需延迟创建）. */@Override@Transactional(rollbackFor=Exception.class)publicUserEntityregister(RegisterRequestrequest){UserEntityuser=newUserEntity();user.setUsername(request.getUsername());user.setPhone(request.getPhone());user.setStatus(1);user.setCreateTime(newDate());returnuserRepository.saveAndFlush(user);// 注意：注册时不创建扩展表记录，首次需要时才创建}/** * 记录用户登录信息（写入扩展表，不存在则创建）. */@Override@Transactional(rollbackFor=Exception.class)publicvoidrecordLogin(IntegeruserId,StringloginIp){UserExtendEntityextend=getOrCreateExtend(userId);extend.setLastLoginIp(loginIp);extend.setLastLoginTime(newDate());extend.setLoginCount(extend.getLoginCount()==null?1:extend.getLoginCount()+1);userExtendRepository.saveAndFlush(extend);}/** * 升级VIP（写入扩展表）. */@Override@Transactional(rollbackFor=Exception.class)publicvoidupgradeVip(IntegeruserId,Integerlevel,DateexpireTime){UserExtendEntityextend=getOrCreateExtend(userId);extend.setVipLevel(level);extend.setVipExpireTime(expireTime);userExtendRepository.saveAndFlush(extend);}/** * 查询用户详情（主表 + 扩展表合并返回）. */@OverridepublicUserDetailResponsegetUserDetail(IntegeruserId){UserEntityuser=userRepository.findById(userId).orElseThrow(()->newRuntimeException("用户不存在"));UserExtendEntityextend=userExtendRepository.findByUserId(userId);UserDetailResponseresponse=newUserDetailResponse();response.setUserId(user.getId());response.setUsername(user.getUsername());response.setPhone(user.getPhone());response.setStatus(user.getStatus());// 扩展信息可能不存在if(extend!=null){response.setAvatarUrl(extend.getAvatarUrl());response.setBio(extend.getBio());response.setVipLevel(extend.getVipLevel());response.setLastLoginTime(extend.getLastLoginTime());}returnresponse;}/** * 获取或创建扩展记录（核心模式：懒创建）. */privateUserExtendEntitygetOrCreateExtend(IntegeruserId){UserExtendEntityextend=userExtendRepository.findByUserId(userId);if(extend==null){extend=newUserExtendEntity();extend.setUserId(userId);UserEntityuser=userRepository.findById(userId).orElse(null);if(user!=null){extend.setUserName(user.getUsername());}}returnextend;}}5.5 MyBatis 查询（JOIN 方式一次查出）
<resultMapid="UserDetailResultMap"type="com.example.dto.UserDetailResponse"><idcolumn="user_id"property="userId"/><resultcolumn="username"property="username"/><resultcolumn="phone"property="phone"/><resultcolumn="status"property="status"/><resultcolumn="avatar_url"property="avatarUrl"/><resultcolumn="bio"property="bio"/><resultcolumn="vip_level"property="vipLevel"/><resultcolumn="last_login_time"property="lastLoginTime"/></resultMap><!-- 主表 LEFT JOIN 扩展表 --><selectid="getUserDetail"resultMap="UserDetailResultMap">SELECT u.id AS user_id, u.username, u.phone, u.status, e.avatar_url, e.bio, e.vip_level, e.last_login_time FROM t_user u LEFT JOIN t_user_extend e ON e.user_id = u.id WHERE u.id = #{userId}</select><!-- 批量查询用户列表（含扩展信息） --><selectid="listUsers"resultMap="UserDetailResultMap">SELECT u.id AS user_id, u.username, u.phone, u.status, e.vip_level, e.last_login_time FROM t_user u LEFT JOIN t_user_extend e ON e.user_id = u.id WHERE u.status = #{status} ORDER BY u.id DESC LIMIT #{offset}, #{pageSize}</select>六、扩展表的创建时机策略
| 策略 | 说明 | 优点 | 缺点 |
|---|---|---|---|
| 主表创建时同步创建 | INSERT 主表后立即 INSERT 扩展表 | 后续无需判空 | 浪费空间（很多记录扩展字段全为 null） |
| 首次使用时懒创建 | 第一次需要写扩展字段时才创建 | 节省空间 | 读取时需判空、写入时需查是否存在 |
| 特定流程触发创建 | 如发货后由物流流程创建 | 职责清晰 | 其他流程想写入时需二次判断 |
七、多扩展表的组织方式
当一个主表需要多个维度的扩展时：
┌─────────────────┐ │ order_master │ └───────┬─────────┘ │ ┌────┼────────────────┐ │ │ │ ▼ ▼ ▼ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ order_extend │ │ order_logist │ │ order_finance│ │ (业务扩展) │ │ (物流状态) │ │ (财务信息) │ └──────────────┘ └──────────────┘ └──────────────┘命名建议：
| 命名 | 含义 | 适用 |
|---|---|---|
xxx_extend | 通用扩展（杂项字段） | 单一扩展表 |
xxx_subtable | 子表（可能一对多） | 需要区别于严格一对一 |
xxx_logistics | 物流维度 | 按业务领域命名 |
xxx_finance | 财务维度 | 按业务领域命名 |
xxx_extra | 额外信息 | 同 extend |
八、注意事项
| 问题 | 解决 |
|---|---|
| JOIN 性能 | 扩展表master_id加唯一索引，LEFT JOIN 性能接近单表查询 |
| 事务一致性 | 主表和扩展表在同一个事务中操作，用@Transactional |
| 扩展表不存在记录 | 用 LEFT JOIN 查询，Service 层对 null 做防御处理 |
| 字段归属不明确 | 建立规范：创建时确定的放主表，后续补充的放扩展表 |
| 扩展表也膨胀了 | 按业务维度继续拆分为多个扩展表，或考虑 JSON 字段 |
| 删除级联 | 删除主表记录时同步删除扩展表（或用外键 CASCADE） |
| 冗余字段同步 | 扩展表冗余的master_code等字段，在主表变更时注意同步 |
九、JSON 字段方案（替代方案对比）
MySQL 5.7+ 支持 JSON 类型，可以作为轻量级扩展方案：
-- 不建扩展表，在主表加 JSON 字段ALTERTABLEt_userADDCOLUMNextra_info JSONDEFAULTNULLCOMMENT'扩展信息';-- 写入UPDATEt_userSETextra_info=JSON_SET(COALESCE(extra_info,'{}'),'$.vipLevel',3,'$.lastLoginIp','192.168.1.1')WHEREid=1;-- 查询SELECTid,username,JSON_UNQUOTE(JSON_EXTRACT(extra_info,'$.vipLevel'))ASvip_levelFROMt_userWHEREid=1;-- 条件过滤（需要虚拟列+索引才有好性能）ALTERTABLEt_userADDCOLUMNvip_levelINTGENERATED ALWAYSAS(JSON_EXTRACT(extra_info,'$.vipLevel'))VIRTUAL;CREATEINDEXidx_vip_levelONt_user(vip_level);| 维度 | 扩展表 | JSON 字段 |
|---|---|---|
| 类型安全 | 强（列有类型） | 弱（运行时解析） |
| 索引能力 | 天然支持 | 需要虚拟列 |
| ORM 映射 | 标准 Entity | 需要 TypeHandler 或手动解析 |
| 灵活性 | 加字段需 DDL | 直接写入新 key |
| 可读性 | SQL 直观 | JSON 函数嵌套复杂 |
| 适用 | 字段确定、需要索引/查询 | 字段动态、纯展示不查询 |
十、决策流程图
新增一个业务字段 │ ├── 核心业务必须字段？（如订单号、金额） │ └── 是 → 放主表 │ ├── 需要频繁查询/过滤？ │ └── 是 → 放扩展表（可加索引） │ ├── 纯展示、不需要查询过滤？ │ └── 是 → 考虑 JSON 字段 │ ├── 已有对应维度的扩展表？ │ └── 是 → 放已有扩展表 │ └── 无对应扩展表且字段不止一个？ └── 是 → 新建扩展表