主表 + 扩展表设计模式

主表 + 扩展表设计模式

一、解决什么问题

随着业务迭代，核心表的字段会不断膨胀：

• 初始建表 20 个字段
• 第一年迭代加到 50 个
• 第二年加到 80 个
• 第三年加到 100+ 个

带来的问题：

主表 + 扩展表通过垂直拆分，将高频核心字段和低频扩展字段分离，解决以上问题。

注：

博客：

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 按业务维度拆分

3.3 按数据生命周期拆分

四、与其他方案的对比

五、代码示例（通用：用户主表 + 扩展表）

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>

六、扩展表的创建时机策略

七、多扩展表的组织方式

当一个主表需要多个维度的扩展时：

┌─────────────────┐ │ order_master │ └───────┬─────────┘ │ ┌────┼────────────────┐ │ │ │ ▼ ▼ ▼ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ order_extend │ │ order_logist │ │ order_finance│ │ (业务扩展) │ │ (物流状态) │ │ (财务信息) │ └──────────────┘ └──────────────┘ └──────────────┘

命名建议：

八、注意事项

九、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 字段 │ ├── 已有对应维度的扩展表？ │ └── 是 → 放已有扩展表 │ └── 无对应扩展表且字段不止一个？ └── 是 → 新建扩展表