主表 扩展表设计模式一、解决什么问题随着业务迭代核心表的字段会不断膨胀初始建表 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_levelTINYINTDEFAULT0COMMENTVIP等级,vip_expire_timeDATETIMECOMMENTVIP过期时间,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 实体类/** * 用户主表实体. */DataEntityTable(namet_user)publicclassUserEntity{IdGeneratedValue(strategyGenerationType.IDENTITY)privateIntegerid;Column(nameusername,nullablefalse)privateStringusername;Column(namephone,nullablefalse)privateStringphone;Column(namestatus,nullablefalse)privateIntegerstatus;Column(namecreate_time)privateDatecreateTime;Column(nameupdate_time)privateDateupdateTime;}/** * 用户扩展表实体. */DataEntityTable(namet_user_extend)publicclassUserExtendEntity{IdGeneratedValue(strategyGenerationType.IDENTITY)privateIntegerid;/** 关联用户主表ID. */Column(nameuser_id,nullablefalse)privateIntegeruserId;/** 冗余用户名便于查询. */Column(nameuser_name)privateStringuserName;/** 头像URL. */Column(nameavatar_url)privateStringavatarUrl;/** 个人简介. */Column(namebio)privateStringbio;/** VIP等级. */Column(namevip_level)privateIntegervipLevel;/** VIP过期时间. */Column(namevip_expire_time)privateDatevipExpireTime;/** 最后登录IP. */Column(namelast_login_ip)privateStringlastLoginIp;/** 最后登录时间. */Column(namelast_login_time)privateDatelastLoginTime;/** 累计登录次数. */Column(namelogin_count)privateIntegerloginCount;Column(namecreate_time)privateDatecreateTime;Column(nameupdate_time)privateDateupdateTime;}5.3 RepositorypublicinterfaceUserRepositoryextendsJpaRepositoryUserEntity,Integer{UserEntityfindByPhone(Stringphone);}publicinterfaceUserExtendRepositoryextendsJpaRepositoryUserExtendEntity,Integer{/** 通过用户ID查找扩展信息. */UserExtendEntityfindByUserId(IntegeruserId);/** 批量查找扩展信息. */ListUserExtendEntityfindByUserIdIn(ListIntegeruserIds);}5.4 Service 层核心按需创建扩展记录ServicepublicclassUserServiceImplimplementsUserService{privatefinalUserRepositoryuserRepository;privatefinalUserExtendRepositoryuserExtendRepository;publicUserServiceImpl(UserRepositoryuserRepository,UserExtendRepositoryuserExtendRepository){this.userRepositoryuserRepository;this.userExtendRepositoryuserExtendRepository;}/** * 注册用户只创建主表记录扩展表按需延迟创建. */OverrideTransactional(rollbackForException.class)publicUserEntityregister(RegisterRequestrequest){UserEntityusernewUserEntity();user.setUsername(request.getUsername());user.setPhone(request.getPhone());user.setStatus(1);user.setCreateTime(newDate());returnuserRepository.saveAndFlush(user);// 注意注册时不创建扩展表记录首次需要时才创建}/** * 记录用户登录信息写入扩展表不存在则创建. */OverrideTransactional(rollbackForException.class)publicvoidrecordLogin(IntegeruserId,StringloginIp){UserExtendEntityextendgetOrCreateExtend(userId);extend.setLastLoginIp(loginIp);extend.setLastLoginTime(newDate());extend.setLoginCount(extend.getLoginCount()null?1:extend.getLoginCount()1);userExtendRepository.saveAndFlush(extend);}/** * 升级VIP写入扩展表. */OverrideTransactional(rollbackForException.class)publicvoidupgradeVip(IntegeruserId,Integerlevel,DateexpireTime){UserExtendEntityextendgetOrCreateExtend(userId);extend.setVipLevel(level);extend.setVipExpireTime(expireTime);userExtendRepository.saveAndFlush(extend);}/** * 查询用户详情主表 扩展表合并返回. */OverridepublicUserDetailResponsegetUserDetail(IntegeruserId){UserEntityuseruserRepository.findById(userId).orElseThrow(()-newRuntimeException(用户不存在));UserExtendEntityextenduserExtendRepository.findByUserId(userId);UserDetailResponseresponsenewUserDetailResponse();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){UserExtendEntityextenduserExtendRepository.findByUserId(userId);if(extendnull){extendnewUserExtendEntity();extend.setUserId(userId);UserEntityuseruserRepository.findById(userId).orElse(null);if(user!null){extend.setUserName(user.getUsername());}}returnextend;}}5.5 MyBatis 查询JOIN 方式一次查出resultMapidUserDetailResultMaptypecom.example.dto.UserDetailResponseidcolumnuser_idpropertyuserId/resultcolumnusernamepropertyusername/resultcolumnphonepropertyphone/resultcolumnstatuspropertystatus/resultcolumnavatar_urlpropertyavatarUrl/resultcolumnbiopropertybio/resultcolumnvip_levelpropertyvipLevel/resultcolumnlast_login_timepropertylastLoginTime//resultMap!-- 主表 LEFT JOIN 扩展表 --selectidgetUserDetailresultMapUserDetailResultMapSELECT 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!-- 批量查询用户列表含扩展信息 --selectidlistUsersresultMapUserDetailResultMapSELECT 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_infoJSON_SET(COALESCE(extra_info,{}),$.vipLevel,3,$.lastLoginIp,192.168.1.1)WHEREid1;-- 查询SELECTid,username,JSON_UNQUOTE(JSON_EXTRACT(extra_info,$.vipLevel))ASvip_levelFROMt_userWHEREid1;-- 条件过滤需要虚拟列索引才有好性能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 字段 │ ├── 已有对应维度的扩展表 │ └── 是 → 放已有扩展表 │ └── 无对应扩展表且字段不止一个 └── 是 → 新建扩展表