1. 为什么“设计原则”不是给理论爱好者看的1.1 一个订单模块的返工经历胜过十页规范文档前几年我带过一个电商类的模拟项目订单表设计得极其随意。当时业务方说“很简单就是用户买商品记录一下”于是建表时把所有信息塞进一张大宽表用户ID、用户名、商品名称、商品单价、收货地址、下单时间全挤在一起。上线前三个月一切正常查询也快大家都觉得“数据库设计嘛能跑就行”。第四个月开始出问题。商品增加了多规格比如同一件衣服要区分S、M、L码原来的商品名称字段彻底不够用了订单开始支持拆单一个订单拆成多个子包裹主表外键关系乱成一团售后流程要引用下单那一刻的商品快照可商品改价之后历史订单的金额也跟着“变了”财务对账对不上。每一次改动都要走“改表结构 写数据迁移脚本 修历史数据”的流程发布一次上线像拆一次炸弹。这个经历让我明白一件事数据库设计原则的本质不是让你画出漂亮的ER图而是让你在业务还没爆发之前就为一两年后的变化留好余地。任何一个设计决策当时看都是“多花十分钟”的问题事后看都是“省几周返工”的投资。本文想聊的就是这些年做设计时真正管用的那些原则以及我在实战中踩过的具体坑。1.2 数据库设计真正的产出物是什么很多新人拿到需求就开写建表语句这其实是一开始就走错了方向。数据库设计的产出物从来不是那一堆DDL脚本而是一套完整的决策记录至少包括四样东西实体关系模型哪些是核心实体哪些是关联实体一对多、多对多关系怎么拆。索引策略哪些字段会被高频查询和排序联合索引怎么设计哪些索引属于“想当然”的无效索引。数据生命周期数据保留多久冷热数据怎么分离什么时候该归档或分表。读写路径一个请求进来之后是先走缓存再查库还是直接命中主键索引数据是从主库读还是从只读副本读。这四个要素里任何一个没想清楚后面都会拿生产事故来提醒你。我见过最典型的问题是团队只关注表结构和字段类型完全不讨论数据量级和增长趋势结果上线半年后一张核心表超过千万行所有按非索引字段的查询全部慢查询告警这时候再谈“要不要分表”就已经很被动了。所以你把这篇文章当作一份“设计checklist”来读也行。它不解决某个具体数据库的产品差异问题讲的是通用方法论但每一条基本都可以直接落到你正在做的表设计里去。2. 设计第一步先把业务盘问清楚再谈ER图2.1 需求澄清阶段必问的四组问题我见过太多设计评审变成“表结构批斗会”因为业务需求压根没聊透。正式开始建表前我建议无论如何都要问清楚下面四组问题否则后面必然返工。第一组这个实体的读写画像是什么样的传统订单系统是“写多读多”商品详情页是“读多写极少”操作日志是“写多读少但要定期清理”。这些画像决定了主从架构、缓存策略、归档方案甚至字段的冗余方向。比如一个字段如果只在写入时用到、读取时几乎不展示那就没有冗余的必要但如果每次列表页都要展示且来源表的查询很贵那就该考虑冗余。第二组数据量级和留存周期是多少“我们每天大概十万订单订单详情想保留三年。”这句话的信息量比任何建表规范都大。十万订单意味着一个月三百万行三年超过一千万行按这个量级设计时就要考虑索引怎么控制数量、历史订单是否需要独立归档表、是否要按时间分表分区。反过来如果每天只有几百条数据那你的设计可以完全不用考虑分区老老实实做规范反而更重要。第三组谁有权限看和改这些数据如果系统是面向多组织的比如不同租户的数据需要隔离那设计上就要预留组织标识字段后续所有查询都要带上这个条件索引设计也要把它放在联合索引的关键位置。等上线之后再补这个字段往往意味着全表扫描、逻辑改造和权限校验重构代价大得多。第四组哪些字段未来可能变化商品名称会变用户昵称会变但订单里的商品快照不该变。业务上“可变”和“不可变”的字段在数据库里要区别对待。可变的用关联表冗余缓存不可变的下单时直接写入冗余字段。这个思路在后面“快照与一致性”部分还会仔细展开。2.2 容量评估与增长预判分表时机不是拍脑袋在需求澄清时就能拿到预估量级的人设计会从容很多。我给一个我在实践中常用的粗略判断标准单表行数到达500万到1000万、且单表容量接近50GB的区间就值得认真考虑归档、分区或分表了。这只是一个经验参考值具体阈值和机器配置、索引数量、查询复杂度都有关但至少可以让你在评审阶段有个预警意识。判断要不要分表之前先做两步估算年增长量用日均写入量乘以留存天数得出存量水位。模拟热点查询把预想的WHERE条件和排序字段整理出来看现有索引在1000万行下能不能撑住。如果发现主表一年后可能到2000万行核心查询却还是非索引字段那就要及早做方案而不是等告警出来再救火。分表方式上优先考虑垂直拆分——把不常读的大字段如详情JSON、备注内容拆到附属表水平拆分则要考虑按用户维度还是按时间维度这一点必须根据业务真实的查询规律来定不能看网上说“接取模就分表”就照搬。2.3 多租户与数据隔离设计上的一步到位做B端业务时多租户几乎避不开。这里的原则是如果产品明确是多客户独立的就一定要在核心业务表设计时把tenant_id / organization_id这类字段建出来并纳入所有联合索引。最痛苦的情况是产品先做单租户后面客户多了再改多租户这时所有表要加字段、所有接口要加过滤条件、所有索引要调整改动面非常大。我见过一个项目硬拖到第三年才做租户隔离结果核心表几千万行数据加租户字段和重建索引花了一个周末还差点出数据事故。所以我的建议向来是哪怕只做单租户用不到隔离字段也可以在表里预留一个默认组织标识。这个字段平时是常量不影响查询性能但以后启用多租户时就能省掉一次伤筋动骨的改造。3. 范式与反范式该守规矩就守该越界就越3.1 两句话讲清楚三大范式每次聊数据库设计都绕不开范式但很多人把范式背成了考试题实际建表时又凭感觉。我习惯用一句话理解范式第一范式字段不能再拆成多个独立含义也就是“原子性”。比如收货地址如果经常要按省份统计就别只存一个字符串该拆省市区分开。第二范式非主键字段要完整依赖主键不能只依赖主键的一部分。比如订单明细表的主键是“订单ID商品ID”那“商品名称”就只依赖商品ID放在这张表里就违反了第二范式应该拆到商品表。第三范式非主键字段之间不能有传递依赖。比如订单表存了“用户ID”又存了“用户等级”而等级本来是通过用户ID去用户表查询的这就是传递依赖。这三个范式说白了就是解决“数据冗余导致的不一致”问题。同一份数据只存一份修改时只改一处天然不会产生冲突。可现实业务里完全不冗余根本做不到因为性能、历史快照和查询便利性都需要冗余。这就引出下面的关键问题什么样的冗余是健康的。3.2 哪些冗余是健康的冗余先给结论冗余本身不是坏事坏的是冗余之后没有设计同步机制。拿订单系统举例订单表里冗余商品名称、商品单价、商品图片从范式角度看完全“不干净”但这是故意的。因为商品信息是会变的用户三个月后看历史订单时商品可能已经下架、改名、调价。订单需要记录的是“下单那一刻的商品快照”而不是实时去join商品表。这种冗余不是为了省一次join而是为了对抗“源数据变化造成的历史失真”。判断一个冗余是否健康我通常问三个问题谁能修改源头数据源头是商品后台、用户中心还是别的系统源头多久改一次几天改一次的和一年改一次的风险完全不同。冗余不一致的后果是什么是展示错乱、对账失败还是权限判断错误如果三个问题的答案都可控那这个冗余就值得做。比如用户昵称冗余到评论表里用户改了昵称之后评论展示旧昵称这种误差大多数产品可以接受但如果在订单表里冗余“用户是否会员”来判断订单折扣用户会员过期后冗余数据没更新导致订单金额算错这就是不可接受的。3.3 反范式设计最常见的翻车点反范式最经典的翻车点是“冗余字段被当成普通字段随意更新”。举个例子产品要求在用户表里冗余“最近一次下单时间”方便运营做用户分层。刚开始数据是对的后来有人写了一个定时任务顺带更新用户表最近下单时间但并发场景下订单已提交、用户表还没更新运营看一眼数据以为这个用户很久没下单了发了不该发的优惠券。这就是冗余字段缺少“写路径约束”造成的。正确的做法是给冗余字段明确一个唯一的写入口比如由订单服务在事务内更新或者通过异步消息由固定的消费者更新绝不允许业务代码到处改。再退一步如果实在没有办法保证强一致那就干脆不要做这个冗余用实时统计或缓存去扛。4. 字段级设计类型选错数据库再快也白搭4.1 主键选择自增ID、雪花ID还是UUID主键是表设计的灵魂。很多表最后被“折腾死”不是因为业务复杂而是主键没选对。下面这张表是我常用的对比方案优点缺点适合场景自增ID写入性能好、占空间小、索引友好分布式下生成有压力、可被猜到数据量单库单表、内部系统雪花ID全局唯一、趋势递增、无需连库依赖时钟、占空间比自增ID大分布式系统、对外不暴露真实量级UUID字符串生成简单、完全随机索引页分裂严重、存储开销大几乎不推荐除非客户端离线生成实际项目中我见过很多团队一边用雪花ID当业务主键一边又在表里加一个自增主键列。原因是InnoDB的聚簇索引需要依赖主键自增列做聚簇索引写入更平滑雪花ID则作为业务唯一键再加唯一约束。这个做法可行但要记得给雪花ID列建唯一索引否则业务上的“唯一”就落不到数据库层面。有没有必要两张表都用雪花ID如果团队已经有全局发号器且运维成熟直接用雪花ID做主键也没问题只是要注意B树写入时的页分裂问题以及主键索引占用的空间比自增ID更大。4.2 金额、时间、布尔值三个最容易翻车的字段类型金额字段永远不要用float或double。二进制浮点数在加减比较时会产生精度误差这在涉及钱的系统里是绝对不可接受的。我推荐直接用整数存“分”也就是把金额乘以100之后以int或bigint存储。如果必须保留小数点后的更多精度比如一些积分场景要用到小数点后四位那可以用decimal但一定要明确指定小数位数并且全程统一。时间字段datetime、timestamp和bigint三种都有适用场景。datetime可读性好但占用空间略大且没有时区概念timestamp有时区转换特性但2038年问题在大数据量系统里不算遥远bigint直接存毫秒级Unix时间戳可比较、可排序、无时区歧义缺点是肉眼不可读。我个人的习惯是需要人直接看的历史表用datetime面向机器计算和汇总的表用bigint。设计时还要统一“存UTC、展示转本地”还是“直接存本地时间”团队里定死一条规则否则后面排查问题时区会比排查业务逻辑更痛苦。布尔字段大部分数据库没有原生的布尔类型实际都在用tinyint(1)配合0/1表示。这里要强调命名习惯——用is_deleted、is_active这种“is_”前缀没问题但默认值一定要显式声明。否则程序漏写这个字段时数据库行为可能是NULL而NULL在条件判断里特别是 WHERE is_deleted 0会比想象中更麻烦因为NULL既不等于0也不等于1。4.3 命名规范与默认值字段名统一后面少吵很多架命名规范看起来是非功能性需求但它直接影响协作效率和后续维护成本。我见过混乱的命名同一张表里既有create_time又有createdAt字段既有“user_name”又有“username”维护脚本的时候每次都要猜。推荐三个简单规则统一使用snake_case表名单数、字段小写加下划线。表达同一含义的字段在不同表里必须同名比如统一用user_id而不是userNo、uid混用。布尔字段用is_前缀时间字段统一用at结尾冗余字段加_snapshot后缀或注释标明来源。默认值是一个容易被忽略的设计点。在建表时几乎每个非必填字段都应该有默认值尤其是数字类型的统计字段默认0比默认NULL更友好因为业务代码里大量使用SUM、COUNT聚合时NULL会带来很多条件判断的心智负担。但有一个例外时间字段不建议默认使用类似“1970-01-01”这样的固定值因为这会给程序误判“数据从未更新”的机会不如用NULL去表达“从未发生”更准确。5. 索引设计的原则与最常踩的坑5.1 最左前缀与覆盖索引的实战意义索引设计是数据库性能的核心也是设计原则中最容易被“事后补救”的一环。很多团队上线时不建索引出现慢查询才临时补结果语句已经跑了几分钟生产环境直接被拖垮。正确的做法是表结构评审时就把索引设计作为必审项。联合索引有一个绕不开的概念叫最左前缀原则。你可以把联合索引想象成一本电话簿先按照姓排序同姓再按照名排序。WHERE条件只有“名”的时候这本电话簿帮不上忙只有“姓”或者“姓名”的时候才能快速定位。所以设计联合索引时字段顺序非常重要。一个典型场景订单列表页经常按“用户ID 订单状态 创建时间”查询那联合索引就应该是(user_id, status, created_at)顺序按等值条件放前面、范围条件放后面。如果你把它设计成(created_at, user_id, status)查询条件带了user_id也走不了这个索引因为最左边的created_at没有被等值条件约束。覆盖索引是另一个容易被忽视但收益很大的设计。所谓覆盖索引就是查询需要的所有字段都包含在同一个索引里查询时只需要扫描索引页不需要回表查数据行。比如查询只需要返回“用户ID 订单状态 创建时间”那上面那个联合索引就能直接覆盖减少一次随机IO。在统计报表类SQL里覆盖索引带来的性能提升非常明显。5.2 索引失效与无效索引三个惯犯和一个隐藏杀手再好的索引设计如果SQL写得不配合也会失效。我总结最常见的三个“索引杀手”对索引列做函数运算或隐式转换比如WHERE DATE(created_at) 2024-01-01只要套了函数索引就废了因为数据库要先算出函数结果才能比较。正确写法是WHERE created_at 2024-01-01 AND created_at 2024-01-02。隐式转换也很常见字符串字段和数字比较时数据库会做类型转换索引也会失效。OR条件跨字段WHERE user_id 123 OR status 1如果两个字段不是同一个索引优化器可能选择全表扫描。妥善做法是用UNION拆分或者把OR改成两个查询结果的合并。区分度过低的字段比如status只有“成功、失败、处理中”三个值单独建索引几乎没用。优化器扫一遍全表可能比走索引更快所以不会选这个索引。低区分度字段只有在和其他高区分度字段联合使用时才有意义。最后一个隐藏杀手是“无效索引太多”。每个索引都会占用磁盘空间每次写入和更新都要同步维护索引索引越多写入越慢。我看到过有人为了“查询快”在表上建了十几个索引结果写入性能一塌糊涂而且大部分索引在业务中根本不会被用到。我的判断标准很简单把生产环境真实的查询日志拉出来看哪些字段组合真正走了索引凡是三个月内没有命中过的索引都是冗余索引可以删掉。5.3 用EXPLAIN验证设计别等慢了才救火建完索引不代表设计完成还要验证SQL是否能真正命中。我习惯在评审阶段就直接跑一遍EXPLAIN重点看四列type是否达到ref或range级别起码不能是all全表扫描、possible_keys是否列出了预期索引、key是否真正使用了目标索引、rows扫描行数是否在合理范围。有人问“设计阶段没数据EXPLAIN看着都是空表怎么判断”我的做法是给表灌一批模拟数据量级贴近预估的1/10然后把核心查询语句放到真实环境里测。尤其是那种“三个条件、两个排序字段”的复杂查询设计期测试一次比上线后排查省太多事。6. 高并发业务下一致性问题的设计取舍6.1 软删除和唯一约束打架的经典场景与解法业务设计里有一个非常经典的冲突我几乎每次评审都会遇到需求要求“手机号唯一”但用户注销后要允许其他人注册同一个手机号。如果走硬删除直接把记录删掉那历史订单、用户行为数据的外键关系就断了如果走软删除用is_deleted1标记那这个手机号在唯一约束下永远被占着新用户无法注册。这个问题有几种解法我推荐的是最简单可控的“改写删除值”方案删除时不要把is_deleted直接改成1而是把唯一键字段改成原值 #deleted# 当前时间戳这样唯一约束不会冲突历史数据也还在。查询时通过is_deleted过滤展示时再还原原值。这个方案的缺点是删除时多一步拼字符串的操作但相比“建一张注销映射表”或者“去掉唯一约束依赖程序判断”而言成本和风险都更可控。核心经验是只要表里有唯一约束任何软删除方案都要先想清楚“被删掉的那条记录唯一键怎么处理”而不是等到上线后由用户反馈来告诉你。6.2 冗余字段的一致性问题与快照思想前面提到订单表要冗余商品快照这里展开讲一下怎么落地。快照思想在电商、金融系统里非常常见凡是涉及钱、库存、状态变化这类核心数据的展示都不能依赖“当前值”而要依赖“发生时点的值”。比如订单表里的商品单价来自商品表当前价格。下单那一刻你把它复制到订单明细里之后商品再涨再跌历史订单都不受影响。如果当时不复制而是每次查询都实时join商品表一旦后台改价历史订单和对账数据就会全部错乱。这道题的严重性不在于“展示错了”而在于“财务对不上账”。快照字段的维护只需要一次写入在订单创建事务内顺手完成不需要额外机制。真正要小心的是“半快照”比如只冗余了商品名没冗余商品缩略图商品换图之后历史订单展示就残缺了。设计快照时最好列一个清单把展示、报表、归档会用到的字段一次性冗余完整别留半截。6.3 并发控制乐观锁与悲观锁的取舍一致性设计的另一个重点是并发控制。经典的库存扣减场景两个用户同时下单同一件商品只剩一件库存如果不做控制就超卖了。两种主流方案乐观锁UPDATE stock SET count count - 1, version version 1 WHERE id ? AND version ?。受影响行数为0说明版本冲突重试或提示失败。适合并发冲突概率较低、以读为主的场景。悲观锁SELECT ... FOR UPDATE先把行锁住再更新适合冲突概率高、写入路径短且需要强一致的场景。我的建议是优先乐观锁因为它不需要长事务也不会出现死锁。使用悲观锁时一定要控制事务粒度绝对不要在锁内做远程接口调用或长耗时计算否则锁等待会把数据库连接池打满。另一个容易被忽略的点是“事务里不要做多件事”。我见过一个下单事务里同时做了查库存、减库存、发消息、调积分服务。结果积分服务超时整个事务回滚库存虽然减了又被回滚用户却收到了“下单失败”的提示体验极其糟糕。事务边界一定要小远程调用必须放到事务提交之后通过MQ等异步机制去完成。7. 设计评审与文档同步让原则真正落到团队协作中7.1 评审环节容易被忽视的三类问题数据库设计评审通常只看“表结构是不是规范”但真正导致上线后出问题的往往是另外三类百万行级数据下的索引推演缺失。评审时表是空的性能问题看不出来。需要把预想的查询SQL逐条拿出来模拟数据量后确认索引命中情况。历史数据迁移方案没有预案。只要是改现有表就一定涉及老数据怎么办。是写脚本回填还是新老表并行双写没有预案的设计评审等于没评。下游消费方没有同步确认。很多表不只是给主业务服务读的还同步到ES、数仓、消息队列。字段改了、类型变了下游解析JSON或做宽表的任务就可能挂掉。评审时要明确这张表的消费者都有谁变更是否兼容。7.2 文档与结构同步的最佳实践最后说一个团队协作里最实用但也最容易被放弃的动作维护数据库设计文档。很多团队认为“表结构都在MySQL里文档无所谓”可一旦业务复杂起来字段几百上千注释又写得稀烂后来的人根本不敢动。我的习惯是每张表维护三段式文档表用途及业务边界说明、字段清单名称、类型、含义、来源、是否冗余、变更记录变更时间、原因、影响范围。变更记录尤其重要它能让你半年后回顾时明白当初为什么加字段、为什么改索引。文档和结构同步有几个小技巧DDL脚本统一入版本库每次变更走代码评审在结构评审通过之后再更新在线画图工具里的ER图把表结构变更与一次版本发布绑定避免“代码上了但库还没改”的错位。坚持半年后这个文档会成为团队最强的工作资产比任何口头传承都可靠。说了这么多其实每一句话背后都有对应的真实教训。数据库设计原则不是背出来的是改出来的。下次接到新需求时不妨先花一个小时把业务模型盘清楚把字段类型、主键策略、索引路径逐一过一遍再开始写建表语句。你会发现真正的省力不是写得更快而是写得对让后续每一次业务迭代都只是“加一张表”或者“加一个字段”的事而不是“改结构、搬数据、修bug”的三连击。