数据库设计是最不值得返工、也最容易返工的环节。机器人租赁系统开发里的核心表——设备、订单、租期、押金——表面看就是几张 CRUD 表实际上藏着一个隐蔽的建模陷阱租赁这个词混用了太多含义。这篇把我们踩过之后重构成型的核心 ER 模型、建表 SQL 和索引设计分享出来帮你少走一次弯路。一、先拆概念四个词四张表别混着用第一次建模时我们犯过的错是把订单当成万能容器订单表里塞了租期起止、押金金额、设备状态。直到出现租了三个月的设备中途换了两次机押金部分扣款分三期这类需求才发现这个模型根本画不动。重构后的核心认知订单、租期、设备档期、押金是四个独立的业务概念各自有独立生命周期设备t_device资产实体寿命最长状态在线/离线/维修/报废间流转订单t_order一次交易契约生命周期从创建到完结/取消租期记录t_rental_period订单在时间维度上的展开一笔订单可以对应多段租期续租、换机都是新租期段押金流水t_deposit_flow资金维度冻结、续冻、部分扣款、分批退回每一笔都是独立流水。核心 ER 关系一句话说清机型(t_model) 1 ─── N 设备(t_device) 1 ─── N 档期记录(t_schedule) │ 订单(t_order) N ─── 1 设备主设备编队场景另有关系表 │ ├── 1 ─── N 租期记录(t_rental_period) └── 1 ─── N 押金流水(t_deposit_flow)注意订单对设备是下单时确定的主设备多设备编队场景通过 t_order_device 关联表解决不要在订单表里塞逗号分隔的 device_id 字符串——见过有人这么干后来统计设备利用率时哭都来不及。二、两个最容易返工的建模决策2.1 易错点一租期和档期必须分成两张表很多第一版设计会把客户租了哪段时间直接写在订单上。问题在于订单记录的是契约客户视角档期记录的是资源占用设备视角两者一致性需要维护但绝不能合并。反例场景客户下单租 5.1-5.7后来协商换机到另一台同型号设备租期不变。如果租期写在订单上你就要在订单、设备、档期三处同步改分开建模后换机只是旧档期释放 新档期预占的档期域操作订单和租期记录一个字段都不用动。另一个理由是并发控制档期表是全平台写竞争最激烈的表它必须保持最瘦的结构只有设备、区间、状态、订单引用任何塞进来的宽字段都会放大热点行的锁开销。CREATETABLEt_rental_period(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_idBIGINTNOTNULL,device_idBIGINTNOTNULL,start_dateDATENOTNULL,end_dateDATENOTNULL,period_typeVARCHAR(20)NOTNULLCOMMENTRENT/RENEW/REPLACE,statusVARCHAR(20)NOTNULLCOMMENTACTIVE/FINISHED/TERMINATED,versionINTNOTNULLDEFAULT0,KEYidx_order(order_id),KEYidx_device_range(device_id,start_date,end_date))COMMENT租期记录契约的时间展开一笔订单多段租期;2.2 易错点二押金必须独立流水表不要在订单上记余额第一版我们把押金金额、冻结状态写在订单表上直到遇到真实需求“押金 20000 元设备归还后先退 60%维修费用确认后再扣 15%剩余退回。”——订单字段根本表达不了多笔、多次、方向不同的资金操作。正确做法是把押金建成事件流水表余额是流水推算出来的投影CREATETABLEt_deposit_flow(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_idBIGINTNOTNULL,flow_noVARCHAR(64)NOTNULLCOMMENT业务流水号幂等键,flow_typeVARCHAR(20)NOTNULLCOMMENTFREEZE/DEDUCT/RELEASE/ADJUST,amountDECIMAL(12,2)NOTNULLCOMMENT正数方向由 flow_type 表达,balance_afterDECIMAL(12,2)NOTNULLCOMMENT操作后余额快照便于对账,channel_noVARCHAR(64)COMMENT支付渠道单号,statusVARCHAR(20)NOTNULLCOMMENTINIT/SUCCESS/FAILED,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,UNIQUEKEYuk_flow_no(flow_no),KEYidx_order(order_id,created_at))COMMENT押金流水每次资金动作一条记录;三个设计要点flow_no唯一键天然支持支付回调幂等balance_after冗余快照让对账从重算全部流水变成比对相邻流水amount 一律存正数、方向由类型表达避免了退款存负数还是扣款存负数这种团队分裂式争论。客户查我的押金时按 order_id 汇总流水即可退还进度一目了然。三、关键建表 SQL 摘录设备表重点是状态机和归属关系CREATETABLEt_device(idBIGINTPRIMARYKEYAUTO_INCREMENT,model_idBIGINTNOTNULLCOMMENT机型ID,owner_tenantBIGINTNOTNULLCOMMENT归属租赁商,sn_codeVARCHAR(64)NOTNULLCOMMENT出厂序列号,biz_statusVARCHAR(20)NOTNULLCOMMENTIDLE/LEASED/REPAIR/SCRAP,online_statusVARCHAR(10)COMMENTONLINE/OFFLINE由IoT服务维护,current_orderBIGINTCOMMENT当前生效订单冗余字段见下文,versionINTNOTNULLDEFAULT0,UNIQUEKEYuk_sn(sn_code),KEYidx_tenant_status(owner_tenant,biz_status))COMMENT设备档案;订单表摘要简化CREATETABLEt_order(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(32)NOTNULL,customer_idBIGINTNOTNULL,device_idBIGINTNOTNULLCOMMENT主设备,statusVARCHAR(20)NOTNULLCOMMENT状态机CREATED/SIGNED/PAID/DELIVERED/RETURNING/FINISHED/CANCELED,rent_amountDECIMAL(12,2)NOTNULL,deposit_amountDECIMAL(12,2)COMMENT仅作下单时快照实际以流水为准,versionINTNOTNULLDEFAULT0,UNIQUEKEYuk_order_no(order_no),KEYidx_customer(customer_id,status))COMMENT订单;四、索引设计与反范式权衡4.1 设备 日期区间的复合索引档期冲突检测是这个系统跑得最频繁的查询索引设计直接决定 P99-- 高频查询某设备与给定区间是否重叠SELECT1FROMt_scheduleWHEREdevice_id?ANDstatusIN(PRE_RESERVED,BOOKED,OCCUPIED)ANDstart_date?ANDend_date?;索引是KEY idx_device_range (device_id, start_date, end_date)。注意两点等值列 device_id 放最前两个范围列放后面——MySQL 中一个索引只能有效利用一个范围条件end_date 列主要靠回表后过滤但 device_id 等值过滤已把扫描范围缩小到单设备的少量行实测单次查询 2-5msstatus 不放进索引前缀因为可选值少且区分度低放进去反而让索引维护变贵。4.2 状态字段的字典设计与查询习惯状态列统一用 VARCHAR 存字典码而不是数字枚举看似浪费空间实际收益巨大排查问题时 DBA 直接SELECT status, COUNT(*) ... GROUP BY status就能看懂分布不用对着字典表翻译状态新增值时也不存在历史数据语义漂移的问题。配套纪律是字典码集中在一个枚举类里维护代码里禁止裸写字符串。另外所有表都保留 created_at/updated_atupdated_at 用数据库自动维护——听起来是常识但我们接手过的外部系统里缺了它导致数据修复时无从下手的情况不止一次。4.3 历史数据归档的提前设计订单、押金流水、档期记录都是只增不删的数据两年后量级很容易上千万。建表时就定好归档策略档期表按年份归档到历史表押金流水因为对账周期财务通常要求保留三年单独延迟归档设备遥测归时序库天然不占业务库。归档任务用低峰期批量小事务搬运每批 5000 条避免大事务锁表。归档键在建表时就定为 order 的完结时间而非创建时间——这个决定如果留到要做归档那天再改历史数据的回填会非常痛苦。4.4 反范式冗余 current_order 的得与失设备表冗余了 current_order 字段这是刻意为之设备详情页、IoT 控制台、工单系统都要频繁回答这台设备现在租给谁如果每次都跨表查档期或订单热点接口就要多一次 JOIN。代价是订单状态流转时要多维护这个字段同事务内更新 对账任务兜底校验。我们的反范式纪律只有一条冗余字段必须能被对账任务自动校验。校验不了的冗余宁可不做否则数据一脏就是事故。五、总结建模不返工的五条清单订单/租期/档期/押金是四个概念四张表换机不换租期、退押分多笔这类需求是试金石档期表保持最瘦它是写热点宽字段是性能毒药押金用流水表 幂等键 余额快照余额永远是推算的投影而非存储的事实复合索引等值在前范围在后别指望一个索引吃掉两个范围条件反范式可以有但每个冗余字段都必须有对账校验兜底。