1. 先从一张“看着正常”的表说起设计初心与业务代价我见过太多项目毁在数据库设计这一关。有个真实例子订单系统上线一年后核心表已经长到 80 多个字段有的是为了某个临时需求加的冗余有的是当初觉得“以后可能会用到”的预留列还有几个字段连当初写这段 DDL 的人都已经说不清含义。整张表没有主键之外的任何索引唯一的索引还因为字段类型是 varchar 但查询传的是数字而常年失效。后来一次需求变更要给这张表加一个状态字段结果 DDL 跑了半小时业务停了五分钟——因为这张表被各种外键、定时任务、报表查询死死绑住谁都不敢碰。这不是运维的锅也不是 DBA 的锅是设计阶段的账迟早要还。数据库设计原则不是大学课本里拿来背的三范式定义而是一整套跟业务、查询、并发、扩展性绑在一起的决策方法。你设计的每一张表都是在给未来的运维和开发写“使用说明”。写得好后面每个人都在帮你加分写得烂后面每个人都在替你填坑。这篇文章不打算讲那种“凡是违反第二范式就是设计失败”的教条而是想从一个经历过各种烂表、好表、从单表走到分库分表的普通后端开发视角把数据库设计这件事拆开揉碎讲清楚。适合正在做课程设计的在校生、刚接触真实业务的后端开发以及那些被线上慢查询和死锁折磨得想转行的运维同学。你会发现所谓设计原则归根结底就一句话让数据在正确的时间、以正确的结构、被正确的人以正确的成本拿到。2. 范式与反范式教科书和应用场的分水岭2.1 三大范式到底在说什么——用订单表拆解上过《数据库原理》课程的同学应该都背过这三个概念第一范式要求字段不可再分第二范式要求非主键列必须完全依赖主键第三范式要求非主键列之间不能有传递依赖。定义背得滚瓜烂熟一落到业务场景就不知道怎么用了。我习惯用一个订单系统的例子把这三个范式串起来。假设你要做一张订单表第一版长这样CREATE TABLE orders_v1 ( id INT PRIMARY KEY, customer_info VARCHAR(255), -- 张三,13800138000,上海市浦东新区 product_names VARCHAR(500), -- 苹果,香蕉,橘子 product_prices VARCHAR(200), -- 5.50,3.20,4.00 total_amount DECIMAL(10, 2) );这种设计customer_info里塞了姓名、电话、地址三个信息product_names和product_prices又是多值字段完全不符合第一范式。带来的问题非常具体你想统计“有多少用户来自上海”只能把整张表捞出来在应用层做字符串切割数据库层的索引、聚合函数全都帮不上忙。第一范式的本质就是让每一个字段都成为“原子的、可独立检索的最小单元”否则数据库的查询能力就是废的。接着往上走。第二范式解决的是“部分依赖”问题出现在复合主键的表里。比如订单明细表用 (order_id, product_id) 作为联合主键如果这张表里还放了 product_name、product_category这些字段只依赖 product_id不依赖 order_id那就是部分依赖。结果是什么同一个商品在 1 万张订单明细里出现了 1 万次商品改名的时候你要 UPDATE 这 1 万行。你也许觉得这也能忍但第三范式的坑会更隐蔽——订单表里放了 customer_id又冗余了 customer_name 和 customer_level这些字段依赖的是 customer_id 这个“非主键列”不是订单主键本身。一旦用户的姓名的级别变了历史订单里记录的信息就跟着变了你连“当时这个用户是什么等级”都查不出来了。2.2 什么时候必须打破范式——冗余字段的胆量与分寸听到这里有同学可能会说那所有表都严格三范式不就行了现实世界的答案是很多场景下你要主动违反范式而且违反得越“彻底”业务反而越稳。最经典的例子就是订单快照。严格三范式的设计下订单表只存 product_id查询的时候 JOIN 商品表拿商品名称和价格。看起来没冗余但你想过没有如果三个月后商品改名叫“智障手机壳”或者价格从 99 涨到 199用户翻开历史订单看到的应该是哪个名字、哪个价格正确答案是下单那一刻的商品名称和成交价而不是当下的。所以成熟的订单设计一定会做字段冗余把下单时的商品名、商品快照、成交单价直接存进订单明细表这就是反范式。当业务要求保留历史事实时冗余不是浪费而是忠实。另一个高频场景是计数器冗余。文章表放一个 comment_count 字段每次有人评论就 1而不是每次都 SELECT COUNT(*) FROM comments WHERE article_id ?。后者在数据量小的时候没问题文章量到百万级、评论量到千万级的时候这个 COUNT 会成为你接口里最慢的一行 SQL。用一张单独的统计表或者直接冗余在业务表里是典型的用空间换时间的取舍。反范式要拿捏分寸不能一上来就“全表冗余”。我自己的判断标准是冗余的字段必须是读多写少、并且能容忍极短时间不一致的字段。比如商品名称、订单快照、文章评论数都属于这一类。如果一个字段的更新频率比查询还高那它就不适合做冗余比如用户的实时余额还是老老实实放在正经字段里靠事务保证吧。2.3 雪花模型与星型模型的设计取舍范式与反范式之争到数据仓库场景会更明显。在 OLAP 场景传统的规范化设计会形成雪花模型维度表拆得非常细地区维度拆成国家表、省表、市表产品维度拆成类别表、品牌表、产品表事实表通过多层外键关联取数。好处是存储省、更新一致性好坏处是一张报表要 JOIN 七八张表查询慢到怀疑人生。所以大多数数据仓库会选择星型模型把维度表做成一张包含全部维度属性的宽表事实表直接关联这一张宽表。比如地区就不拆了一张地区维度表里有 country、province、city、district 四个字段报表统计的时候只 JOIN 一次。这是典型的“面向查询”的设计思路——先想清楚报表怎么查再决定表怎么建而不是先按范式把表拆得精精细细再硬着头皮去查。相比之下雪花模型的“规范性”更多是对存储成本和更新一致性负责星型模型的“扁平化”更多是对查询性能和易用性负责。在真实数仓建设里除非存储成本极其敏感否则我见过的大多数团队都会倾向星型模型再用宽表、物化视图去兜底复杂指标。一个核心原则OLTP 系统把一致性当命根子OLAP 系统把查询性能当命根子设计原则必须跟着场景走。3. 主键设计一个不当选择埋下的好几年的坑3.1 自增 ID、UUID、雪花 ID 的适用边界主键设计是数据库设计里最容易被低估的一环但它决定了未来几年你分不分库、扩不扩容、迁不迁移数据。很多新手觉得“主键不就是加个 AUTO_INCREMENT 吗”真到了线上规模第一批哭的就是他们。自增 ID是中小项目的默认选择优点是简单、有序、写入性能好。InnoDB 的聚簇索引物理上就是按主键排序的 B 树自增 ID 顺序写入新记录直接追加到页尾不用频繁做页分裂写入效率最高。但自增 ID 有两个隐患。第一分库分表的时候多个库各自生成自增 ID 必然撞车解决方案要么是设置不同的 auto_increment_offset 和 auto_increment_increment要么引入分布式 ID 中心要么干脆一开始就不用自增。第二ID 直接暴露在 URL 和 API 里竞争对手和爬虫只需要遍历 ID 就可以把你订单量、用户量摸得一清二楚这在 C 端业务里是很大的信息泄露面。UUID看起来完美解决了全局唯一问题应用层生成、不依赖数据库但它有几个非常现实的坑字符串存储占空间是 bigint 的好几倍完全无序插入时随机落在 B 树的各个位置页分裂频繁写入性能可能差一个数量级索引体积暴涨内存缓冲池放不下更多索引页查询性能也跟着下滑。如果你实在要用 UUID至少转换成 BINARY(16) 存储或者用 UUIDv7 这种有序版本别直接用 varchar(36) 存原样字符串那是最差的选择。雪花 ID是目前分布式场景的主流方案64 位 long前 41 位存毫秒时间戳中间 10 位存机器 ID后 12 位存序列号单机每毫秒可以生成 4096 个不重复 ID。它既全局唯一又带时间有序性写入性能接近自增 ID。缺点是需要引入额外的生成组件并且要注意时钟回拨问题——如果服务器时间往回调同一毫秒内可能生成重复 ID必须做回拨等待或备用方案。我见过不少团队自己写雪花算法以为是件小事结果时钟回拨导致线上数据主键冲突这种事故在设计阶段就要规避。3.2 复合主键与业务主键的取舍关于主键还有一个经常被争论的话题到底该用“无意义的代理主键”还是直接用业务字段当主键我的结论很明确在业务表里绝大多数情况下用代理主键把业务唯一键单独加唯一索引。原因有三条。第一业务字段不稳定手机号会换、身份证号涉及隐私合规、邮箱可以修改主键一旦变所有外键引用和索引都要跟着改代价巨大。第二业务字段可能不够短用 varchar 长字符串当主键索引体积变大、写入性能下降。第三业务字段的生成规则不在你掌控中比如第三方系统的订单号也可能出现重复或格式不一致拿来说主键很容易翻车。复合主键也不是完全不能用它最自然的场景就是多对多关系的中间表比如用户-角色表、订单-商品明细表(user_id, role_id) 或者 (order_id, product_id) 作为联合主键既能保证唯一性又能省掉一个没必要的自增主键。但如果你的业务表里需要大量用“某个字段 另一个字段”才能唯一定位一行这时候往往是建模出了问题——大概率说明你缺了一张更高层的实体表。先把模型理顺再谈主键设计。3.3 主键选择的检查清单结合上面这些我每次设计表结构的时候都会过一遍主键检查清单你可以直接抄走是否满足唯一性这是主键的底线任何情况下都不能重复。是否绝对稳定一旦生成就不会被修改业务字段通常不合格。是否有序或趋势递增有序的主键对 InnoDB 聚簇索引最友好。是否尽量短小主键字段越小二级索引的体积也越小查询越快。是否支持未来的水平扩展全局唯一是分库分表的前提自增 ID 做不到。一句话总结单机小项目自增 ID 随便用分布式项目直接上雪花 ID 或类似的有序分布式 IDUUID 除非你清楚知道自己在做什么否则别碰。4. 字段类型与字段设计那些一夜之间不够用的设计4.1 整数、小数、金额的类型选择字段类型选错破坏力是滞后但猛烈的。我见过一个最典型的教训项目早期用 INT 存用户 ID 和订单 ID数据量到了一定规模ID 超过 21 亿INT 溢出线上直接报主键冲突。凌晨三点爬起来改表整个团队生不如死。所以从第一天开始主键和可能增长到千万级以上的 ID 字段直接用 BIGINT别吝啬那四个字节。金额字段的坑同样深。不要用 FLOAT 或 DOUBLE 存金额IEEE 754 浮点数在二进制里无法精确表示 0.1累加误差在财务账单上是不可接受的。正确的方案有两种一是用 DECIMAL(10, 2) 直接存元简单直观二是用 BIGINT 存“分”把金额换算成最小货币单位避免数据库层的精度问题。我比较推荐第二种因为 DECIMAL 在 MySQL 里同样是变长定点数存储和计算开销都不小整数类型永远是性价比最高的。对外展示时再除以 100 就好。状态类和计数类字段能用 TINYINT 就别用 INT。TINYINT 占 1 字节存储 0-255 的值表达几十个状态位绰绰有余。年龄、数量、星级这类有限取值范围的字段也是同理。国内很多教程喜欢用 INT 做一切整数不是不能用就是纯属浪费存储和索引空间。一张表几十个字段每个都多几字节乘以几千万行就是几个 GB 的差距。4.2 时间字段的设计时间字段看着不起眼踩坑的人一点都不少。MySQL 里的 DATETIME 和 TIMESTAMP 有区别TIMESTAMP 范围从 1970 到 2038 年有时区转换机制会随数据库时区变化而变化DATETIME 范围更大不涉及时区转换存进去是什么就是什么。如果你的业务只在国内跑用 DATETIME 最省心如果系统面向全球用户或者由多机房部署建议统一用 TIMESTAMP配合数据库层面设置 UTC 时区应用层再按用户时区转换展示。还有一个方案是直接用 BIGINT 存毫秒时间戳。好处是非常干净不依赖数据库的时区设置跨 MySQL、PostgreSQL、达梦、人大金仓这类异构数据库迁移时完全不用考虑时区差异。坏处是你在数据库工具里看这个字段的时候看到的是一串数字不能直观读出来调试体验比较差。我个人的习惯是业务上不需要直接读时间的场景比如日志表、流水表、埋点表用 BIGINT业务上要频繁查询时间范围且要人肉看数据的场景用 DATETIME。关键提醒时间字段尽量用数据库层默认值比如 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP避免应用层忘记传值导致写入 NULL 或者字段不更新。这个设计可以帮你省掉大量排查“这个记录是什么时候创建的”“为什么修改时间没变”的时间。4.3 NULL 值、默认值、枚举的坑NULL 在数据库里是个“薛定谔的值”——它不等于 0不等于空字符串也不等于 FALSE。它会让索引失效、让 COUNT(*) 和 COUNT(col) 结果不一致、让应用层 Java 代码冒出 NPE、让前端显示一个“undefined”。举个最常见的例子一个用户备注字段有的人是 NULL有的人是空字符串 有的人填了文字。你想统计“有多少用户没填备注”写 COUNT(*) WHERE remark IS NULL 永远统计不全还得加上 remark 的判断写出来的 SQL 又丑又容易漏。所以设计字段的时候我的默认原则是能不 NULL 就不 NULL一律 NOT NULL DEFAULT 默认值。字符串类型默认空字符串数字类型默认 0时间类型默认 CURRENT_TIMESTAMP。真要表达“无意义”用业务上的特殊值比如 -1、N/A会更清晰可控。枚举值的设计也是一门学问。很多新手喜欢把状态直接写死成字符串比如 status 字段存 pending、paid、shipped可读性好但存储和索引效率低。更规范的做法是状态字段用 TINYINT 存数值把中文含义和维护规则放到字典表里业务代码里再转换成常量或枚举。-- 不建议 status VARCHAR(20) NOT NULL DEFAULT pending -- 更推荐 status TINYINT NOT NULL DEFAULT 0 COMMENT 0-pending, 1-paid, 2-shipped, 3-cancelled注意一点如果这组枚举含义在未来可能增加要把状态字段的容量余量留足哪怕现在只有 5 种状态TINYINT 的 0-255 也够用很久。别因为“现在只有几个值”就用 BIT 或者更窄的类型后期扩展会非常痛苦。4.4 预留字段与扩展字段的争议几乎每个项目里都会有人提出“要不在表里加几个预留字段吧万一以后要用呢”。我的回答一直是别加。预留字段是数据库设计里最经典的伪需求——你既不知道它将来要存什么类型也不知道它未来代表什么含义等到真的需要的时候它的名字和注释都给不了任何线索反而像埋了一颗地雷。我见过一张老表里预留了 10 个 VARCHAR(255) 字段名字叫 RESERVE1 到 RESERVE10实际项目黄了都没用上 3 个。另外一个团队接手的时候根本不敢删只能一直带着白白占着存储空间和索引体积虽然没索引但行大小被撑大页能放的记录数变少查询性能一样受影响。那如果确实有“未来可能加各种自定义属性”的需求呢合理的方案不是预留列而是用 JSON 字段。MySQL 5.7 之后支持 JSON 类型可以存动态的结构化数据也支持 JSON 函数查询。比如商品表加一个 attributes JSON 字段将来商品要加“是否支持礼品包装”“保质期多少天”这些非通用属性直接写进 JSON 就行不用频繁 ALTER TABLE。另一个更规范的做法是拆子表用“实体-属性-值”模型去表达动态属性但 EAV 模型的查询特别绕除非是配置类页面否则我一般不推荐。5. 索引设计的“二八定律”放对位置的索引才是好索引5.1 索引为什么有用——B 树的数据访问逻辑要理解索引先要理解没有索引时数据库如何找数据。InnoDB 的表数据本身是一棵以主键为序的 B 树叶子节点存储整行数据也就是你看到的 .idb 文件。如果查询条件不是主键数据库只能从第一个叶子节点开始沿着链表把整张表所有数据页都扫一遍逐个判断是否满足条件这就是全表扫描。数据量小的时候无所谓到了千万行级别一次全表扫描可能扫掉 20 万个数据页每页一次磁盘 IO性能直接崩。索引本质上就是额外维护的一颗“按索引键排序的 B 树”叶子节点存的是主键值。有了它数据库可以像查字典一样从根节点开始二分定位几层之内就找到目标记录的主键然后再回到主键索引上取整行数据这就是“回表”。为什么互联网公司天天盯着慢查询就是因为慢查询背后往往是某个查询忘记建索引把全表扫描变成了线上常态。这里要特别说明一个索引设计原则索引排序规则与查询条件是否匹配决定了索引能不能被使用。比如你建了一个 (status, create_time) 联合索引查询条件是 WHERE status 1 AND create_time 2024-01-01那数据库可以顺着 status 定位到一批数据再用 create_time 做范围过滤。但如果你查询条件只有 create_time没有带上 status那这个联合索引就用不上因为最左前缀被跳过了。5.2 哪些字段最值得建索引索引不是越多越好但下面这几类字段只要出现在高频查询里几乎都值得建索引等值查询频繁的字段比如 user_id、order_no、mobile这类字段加索引能让 WHERE 条件直接用索引定位而不是全表扫。JOIN 关联字段一条 JOIN 语句要关联两张表被驱动表的关联字段必须有索引否则每次匹配都要全表扫一遍。这就是为什么订单表上的 user_id、商品表上的 category_id 这类外键字段几乎必须建索引。排序和分组字段ORDER BY、GROUP BY、DISTINCT 背后都是排序操作如果字段有索引B 树本身就是有序的可以直接顺序读取跳过了额外排序。唯一性约束字段手机号、身份证号、业务单号这类需要保证不重复的字段直接建唯一索引既约束了数据又加速了查询。还有一条经验覆盖索引是性能优化里性价比最高的手段之一。如果查询只需要访问索引字段本身不需要回表读整行性能会提升一大截。怎么看有没有覆盖在 EXPLAIN 的 Extra 列看到 Using index 就说明走了覆盖索引。比如一张订单表高频查询只关心状态和金额那就建一个 (status, amount) 的索引查询 SELECT status, amount FROM orders WHERE status 1 时连回表都省了。5.3 索引失效与“帮倒忙”的索引很多开发遇到过“明明建了索引查询还是慢”的诡异问题大概率是踩了索引失效的坑。我总结几个最常见的失效场景。对索引字段使用函数比如 WHERE DATE(create_time) 2024-01-01或者 WHERE YEAR(create_time) 2024。这种情况下索引里的值是原始的时间戳你拿函数处理过的值去匹配B 树没法定位只能全量扫描。正确的写法是 WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00让索引按范围查找。隐式类型转换手机号字段建成 VARCHAR查询条件却传了一个数字比如 WHERE mobile 13800138000。MySQL 的规则是字符串字段跟数字比较时会把字符串转成数字再比较这一转索引就失效了。正确做法是查询条件永远跟字段类型保持一致传字符串就加引号。模糊查询LIKE abc% 可以用索引LIKE %abc% 不能用——因为 B 树只能从左往右匹配开头就是通配符无法定位起点。文本搜索需求优先用全文索引或者专门的搜索组件别指望普通 B 树索引解决所有模糊匹配。低选择性字段一个只有 true/false 两个值的字段建了索引之后每次匹配都可能返回一半的数据行数据库优化器会自动放弃索引改走全表扫描反而更慢。性别、删除标记这类字段通常不需要单独建索引除非跟其他字段组成联合索引的组成部分。还有一种“帮倒忙”的索引是冗余索引。建了 (a, b) 联合索引又建了单独的 (a) 索引后者就是纯粹的多余——联合索引的最左前缀已经覆盖了单字段 a 的查询场景。这种冗余索引白白占用空间还会拖慢写入。建议定期用工具检查重复索引比如 MySQL 的 sys.schema_redundant_indexes能直接找出这类冗余。6. 并发、锁与事务设计阶段就该想清楚的运行规则6.1 事务隔离级别怎么选——可重复读 vs 读已提交数据库设计除了表结构还包括事务和并发模型的设计。新人经常忽略这一点两个事务同时改同一行数据会发生什么事务隔离级别就决定了这个问题。SQL 标准定义了四种隔离级别读未提交、读已提交、可重复读、串行化隔离强度从弱到强并发性能从高到低。MySQL InnoDB 默认是可重复读允许事务内多次读取同一数据结果一致通过 MVCC多版本并发控制实现快照读读操作不会阻塞写操作。PostgreSQL 和 Oracle 默认是读已提交每次 SELECT 都读最新已提交数据。作为设计决策隔离级别不是越高越好。可重复读这一档虽然解决了不可重复读但在当前读场景下需要用间隙锁来防幻读而间隙锁正是很多死锁事故的根源。互联网高并发业务里超卖、对账这类问题往往通过业务逻辑和幂等设计解决而不是依赖隔离级别。我见过不少团队在生产环境把隔离级别调成读已提交配合 ROW 格式的 binlog 做主从复制实测下来死锁少了很多。如果你正在设计新系统可以认真评估一下业务真的需要可重复读吗还是读已提交 应用层补偿就够了6.2 死锁的成因与设计层面的预防死锁的经典场景是两个事务各自持有一把锁同时等待对方释放另一把锁互相僵持。在数据库里最常见的触发方式有两个。第一个是多行更新顺序不一致。事务 A 先更新订单 1 再更新订单 2事务 B 先更新订单 2 再更新订单 1当两个事务交错执行时就会出现 A 持有订单 1 的锁等订单 2B 持有订单 2 的锁等订单 1双方谁也不让谁。解决方案说起来很简单所有事务统一按相同顺序更新资源比如都按订单 ID 升序处理。但在真实业务代码里这个顺序往往不是显式控制的所以要在设计评审阶段就强调这个约束。第二个是单个 SQL 涉及范围很大间隙锁互相覆盖。在可重复读级别下一个 UPDATE 或 DELETE 如果 WHERE 条件走的是范围索引InnoDB 除了锁定命中的记录还会锁定范围内的间隙防止幻读。两个事务如果各自锁了一段有交集的间隙就可能互相等待。这也是为什么我前面建议评估读已提交级别的另一个原因——间隙锁的范围更小死锁概率几何级下降。设计层面防死锁还有几个朴素但有效的原则事务尽量短小锁持有时间就越短绝对不要在事务里调用远程接口或 RPC那等于把锁拽在手里去干别的事大事务拆成小事务批量操作控制每个批次的行数。锁是数据库引擎在执行的但高并发下能不能不撞车从表结构和事务设计那一刻就注定了。6.3 乐观锁与悲观锁在表结构上的体现谈到并发控制必须提乐观锁和悲观锁。悲观锁就是 SELECT ... FOR UPDATE查出来的时候就把行锁住其他事务想改只能等。它的前提是锁住的记录必须能被数据库的正向索引命中否则 InnoDB 会锁全表性能直接崩。乐观锁则完全不一样它不锁数据库资源而是靠一个版本号字段在 update 时做条件校验。乐观锁的表结构设计就是必须预留一个 version 字段ALTER TABLE orders ADD COLUMN version INT NOT NULL DEFAULT 0;更新的时候带上版本条件影响行数判断是否冲突UPDATE orders SET status 2, version version 1 WHERE id 123 AND version 0;如果返回的影响行数为 0说明这条订单在本次读取之后已经被别人改过了应用层拿到这个信号后再决定重试还是报错。这个 version 字段必须在表设计初期就加上别等上线之后再去加虽然能加但那时存量数据都要回填、所有写路径都要改测试成本翻倍。我自己的经验分享余额扣减、库存扣减这种强一致场景用悲观锁或 SQL 层面的原子更新更稳妥而状态流转、配置更新这种冲突概率低的场景用乐观锁足够性能好得多。加 version 字段是一件成本极低、收益极高的事情坚决推荐。7. 迁移与演进设计得再好也躲不过“改表”这一天7.1 用版本化工具管理表结构——别再手工执行 SQL很多创业团队和课程设计的数据库管理方式就是一个共享 SQL 文件谁改表谁去执行一下或者干脆 DBA 手工跑再在群里吼一声“我改了订单表”。这种方式在前三个月勉强能撑等团队规模和表数量上来之后就会变成事故现场有人本地改了表没同步、生产环境跟开发环境的表结构不一致、上线时漏执行了某条 DDL导致应用启动直接报错。成熟的团队会用版本化迁移工具比如 FlywayJava 生态和 Liquibase。它的核心思路很简单所有表结构变更都写成一个带版本号的脚本提交到代码仓库部署时自动执行未执行过的脚本并且在数据库里维护一张记录表来跟踪版本。-- V1__create_orders.sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL UNIQUE, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, total_amount BIGINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_create_time (created_at) ); -- V2__add_order_version.sql ALTER TABLE orders ADD COLUMN version INT NOT NULL DEFAULT 0;好处是显而易见的变更可追溯、环境一致性有保证、结合 CI/CD 还能在流水线里自动执行。无论你是个人做课程设计还是公司开发用上这类工具之后“工程发布时怎么配数据库”就不再是玄学而是一条自动化流水线。如果你用的是 IDEA 这类 IDE也可以直接用它集成的数据库迁移功能但独立工具的可控性更强推荐优先考虑。7.2 大表结构变更的低风险姿势即使有了迁移工具把迁移脚本跑在千万行的大表上仍然是高危操作。试想一下ALTER TABLE 加字段的时候MySQL 5.6 之前的行为是拷贝整张表、期间锁表、业务写全部阻塞几千万行的表可能要锁几十分钟这在任何时候都是不可接受的。现在的 MySQL 8.0 对很多 ALTER 操作已经做了优化加字段如果加在表末尾且字段可空或有默认值走的是 INSTANT 算法秒级完成不锁表。但是修改字段类型、修改字段顺序、删除字段这类操作仍然需要 COPY 或 INPLACE 算法期间可能锁表或占用大量磁盘空间。因此线上大表变更要遵循几个原则加字段优先加末尾的可空字段避免触发 COPY 算法。非空字段先以空字段加上回填完数据再改约束避免一次性锁表太久。大表加索引用在线工具比如 Percona Toolkit 的 pt-online-schema-change或者 GitHub 开源的 gh-ost它们通过临时表 触发器/ binlog 同步的方式完成变更业务基本无感。变更安排在业务低峰期并且提前观察磁盘空间和主从延迟。真实演变案例一张 2000 万行的用户表现在要加一个 is_vip 字段。如果直接跑 ALTER TABLE 加 NOT NULL DEFAULT 0大概率引起长时间锁表正确姿势是先用 INSTANT 算法加一个默认为 0 的可空字段后端发版上线然后写个脚本分批回填存量数据虽然默认值已经是 0不需要回填但如果要按用户维度差异化初始化就要分批 UPDATE最后再改成 NOT NULL 和最终默认值。每一步都是小额操作业务感知为零。7.3 从单表到分库分表演进路线上的关键设计数据库设计最高级的考验不是一开始就设计得面面俱到而是设计出“允许你活下去并且能顺利演进”的结构。大多数系统都是从单库单表起步的演进路线一般是加缓存 → 读写分离 → 垂直拆分 → 水平分表。加缓存热点数据放到 Redis数据一致性靠过期时间或主动淘汰这一步可以撑住大部分读多写少的场景。读写分离主库负责写从库扛读注意主从延迟问题。垂直拆分按业务域拆成订单库、用户库、商品库每个库独立部署各自扛压力。水平分表单表数据量过大时按用户 ID、订单 ID 这类分片键把数据拆到多个表甚至多个库。水平分表这条路上分片键的选择是最核心的决策它必须跟你的业务查询模型高度匹配。比如订单表按 user_id 分片用户查询自己的订单就特别快一条 SQL 直接路由到对应分片但商家想查自己店铺的订单就不知道订单在哪个分片需要额外维护一份“商家-订单号”的映射关系或者用中间件做全分片广播查询性能大打折扣。另一个例子是日志流水表按天分片查询时间范围和某一天的数据就非常自然。从设计原则角度看分库分表的前置条件是主键必须全局唯一——自增 ID 在这里就直接出局所以前面反复强调初始设计就要用雪花类分布式 ID就是这个原因。还有一条建议除非业务已经有明确的量级增长预期否则没必要在一开始就引入分库分表中间件那是给最复杂的情况准备的复杂度。先把索引建好、慢查询清干净、用了缓存大部分系统根本走不到拆库那一步。8. 两个真实的数据库设计复盘从需求到落地8.1 案例一订单中心表的设计迭代第一个复盘来自一个电商订单中心它经历了三版设计每一版都是被现实逼着往前走。V1 版本订单数据全堆在一张表里用户信息、商品信息、收货地址、支付流水、优惠明细全部冗余在 order 表里一张表 60 多个字段。刚开始团队只有一个后端联调方便写 SQL 不用 JOIN。结果半年后订单量上来单表 500 万行查询接口开始变慢。更严重的是商品促销规则调整后历史订单的优惠明细已经和当前的规则完全对不上数据对账天天出问题。V2 版本做了垂直拆分把订单拆成订单主表和订单明细表两张主表存订单级别的信息用户、金额、状态、收货人明细表存商品维度的信息商品 ID、数量、成交单价、快照名称。这次调整让订单详情和订单列表的查询都清晰了也顺势建了 user_id、create_time、status 的联合索引慢查询从几十个降到个位数。V3 版本是并发和扩展冲击下的产物。营销大促期间频繁出现订单状态更新的并发冲突于是加了 version 字段做乐观锁。同时商品改价频繁历史订单快照必须独立存储明细表里冗余了一个 product_snapshot_json 字段把下单时刻的商品图片、参数、赠品信息都打了快照。最后因为热数据量大上线了冷热归档机制把超过 90 天的已关闭订单迁移到归档表实时查询性能进一步释放。这个案例想说明的其实是设计原则允许你犯一次错但你要有演进的能力。V1 不是完全错误的它让你快速跑通了业务但如果你长期停留在 V1不引入 V2 的拆分和 V3 的并发控制、快照机制早晚会被数据规模和业务复杂度压垮。8.2 案例二物联网设备数据表的设计第二个案例来自一个物联网设备监测系统场景是设备每 5 秒上报一次状态数据一天单台设备产生 17280 条记录一万台设备一天就是 1.7 亿条。第一版设计是把所有记录插进一张大表字段包括 device_id、timestamp、温度、湿度、电压、信号强度。上线第一天没问题一个月后单表已经几亿行按设备和时间范围查询直接全表扫描一条 SQL 跑几十秒。走的弯路包括给 timestamp 单独加了索引但查询条件是 WHERE device_id ? AND timestamp ?联合索引的缺失导致只能先按设备过滤或按时间过滤效果都很差。第二版核心做了三件事。第一按天分区PARTITION BY RANGE (TO_DAYS(timestamp))每天一个分区时间范围查询只需要扫描对应分区历史分区数据还可以直接做归档或 DROP。第二建了 (device_id, timestamp) 联合索引设备维度加时间范围的查询走完美命中索引响应时间从几十秒降到百毫秒级。第三设计了数据保留策略原始明细数据保留 30 天DOWN 采样保留 180 天更早的聚合成小时级甚至天级统计表。这个案例也可以跟时序数据库做对比如果是更纯粹的时序场景用专门的 TSDB 往往比通用关系库更省心但关系库加上分区索引一样能撑住相当量级的写入和查询。8.3 复盘之后的几条铁律经历这么多项目踩过无数坑之后我总结出几条可以打印出来贴在工位上的设计铁律没有文档的表就是没人敢动的表每个字段必须有注释枚举含义写清楚变更记录留痕。先设计查询再设计表先罗列出这个模块将来最高频的 10 条查询语句再反推表结构和索引。主键用代理键业务唯一键单独建索引不要在自然键上做文章。能不加索引就不加高频查询必须覆盖宁缺毋滥但该建的一个都不能少。表结构演进要像代码一样可回滚、可追踪用版本化迁移工具永远不要手工执行线上 SQL。最后再分享一个小技巧每次建新表之前拿一分钟在脑海里把这张表“运行”一年——想象一年后这张表会有多少行数据主要的查询条件是什么哪些字段会被更新会不会有并发写入冲突。如果这些问题的答案都是清晰的那你设计出来的表基本不会出大问题如果这些问题一个都想不清楚那说明业务还没梳理明白别急着建表先回去跟产品聊清楚再说。 数据库设计避坑指南：从范式、索引到分库分表的实战原则
1. 先从一张“看着正常”的表说起设计初心与业务代价我见过太多项目毁在数据库设计这一关。有个真实例子订单系统上线一年后核心表已经长到 80 多个字段有的是为了某个临时需求加的冗余有的是当初觉得“以后可能会用到”的预留列还有几个字段连当初写这段 DDL 的人都已经说不清含义。整张表没有主键之外的任何索引唯一的索引还因为字段类型是 varchar 但查询传的是数字而常年失效。后来一次需求变更要给这张表加一个状态字段结果 DDL 跑了半小时业务停了五分钟——因为这张表被各种外键、定时任务、报表查询死死绑住谁都不敢碰。这不是运维的锅也不是 DBA 的锅是设计阶段的账迟早要还。数据库设计原则不是大学课本里拿来背的三范式定义而是一整套跟业务、查询、并发、扩展性绑在一起的决策方法。你设计的每一张表都是在给未来的运维和开发写“使用说明”。写得好后面每个人都在帮你加分写得烂后面每个人都在替你填坑。这篇文章不打算讲那种“凡是违反第二范式就是设计失败”的教条而是想从一个经历过各种烂表、好表、从单表走到分库分表的普通后端开发视角把数据库设计这件事拆开揉碎讲清楚。适合正在做课程设计的在校生、刚接触真实业务的后端开发以及那些被线上慢查询和死锁折磨得想转行的运维同学。你会发现所谓设计原则归根结底就一句话让数据在正确的时间、以正确的结构、被正确的人以正确的成本拿到。2. 范式与反范式教科书和应用场的分水岭2.1 三大范式到底在说什么——用订单表拆解上过《数据库原理》课程的同学应该都背过这三个概念第一范式要求字段不可再分第二范式要求非主键列必须完全依赖主键第三范式要求非主键列之间不能有传递依赖。定义背得滚瓜烂熟一落到业务场景就不知道怎么用了。我习惯用一个订单系统的例子把这三个范式串起来。假设你要做一张订单表第一版长这样CREATE TABLE orders_v1 ( id INT PRIMARY KEY, customer_info VARCHAR(255), -- 张三,13800138000,上海市浦东新区 product_names VARCHAR(500), -- 苹果,香蕉,橘子 product_prices VARCHAR(200), -- 5.50,3.20,4.00 total_amount DECIMAL(10, 2) );这种设计customer_info里塞了姓名、电话、地址三个信息product_names和product_prices又是多值字段完全不符合第一范式。带来的问题非常具体你想统计“有多少用户来自上海”只能把整张表捞出来在应用层做字符串切割数据库层的索引、聚合函数全都帮不上忙。第一范式的本质就是让每一个字段都成为“原子的、可独立检索的最小单元”否则数据库的查询能力就是废的。接着往上走。第二范式解决的是“部分依赖”问题出现在复合主键的表里。比如订单明细表用 (order_id, product_id) 作为联合主键如果这张表里还放了 product_name、product_category这些字段只依赖 product_id不依赖 order_id那就是部分依赖。结果是什么同一个商品在 1 万张订单明细里出现了 1 万次商品改名的时候你要 UPDATE 这 1 万行。你也许觉得这也能忍但第三范式的坑会更隐蔽——订单表里放了 customer_id又冗余了 customer_name 和 customer_level这些字段依赖的是 customer_id 这个“非主键列”不是订单主键本身。一旦用户的姓名的级别变了历史订单里记录的信息就跟着变了你连“当时这个用户是什么等级”都查不出来了。2.2 什么时候必须打破范式——冗余字段的胆量与分寸听到这里有同学可能会说那所有表都严格三范式不就行了现实世界的答案是很多场景下你要主动违反范式而且违反得越“彻底”业务反而越稳。最经典的例子就是订单快照。严格三范式的设计下订单表只存 product_id查询的时候 JOIN 商品表拿商品名称和价格。看起来没冗余但你想过没有如果三个月后商品改名叫“智障手机壳”或者价格从 99 涨到 199用户翻开历史订单看到的应该是哪个名字、哪个价格正确答案是下单那一刻的商品名称和成交价而不是当下的。所以成熟的订单设计一定会做字段冗余把下单时的商品名、商品快照、成交单价直接存进订单明细表这就是反范式。当业务要求保留历史事实时冗余不是浪费而是忠实。另一个高频场景是计数器冗余。文章表放一个 comment_count 字段每次有人评论就 1而不是每次都 SELECT COUNT(*) FROM comments WHERE article_id ?。后者在数据量小的时候没问题文章量到百万级、评论量到千万级的时候这个 COUNT 会成为你接口里最慢的一行 SQL。用一张单独的统计表或者直接冗余在业务表里是典型的用空间换时间的取舍。反范式要拿捏分寸不能一上来就“全表冗余”。我自己的判断标准是冗余的字段必须是读多写少、并且能容忍极短时间不一致的字段。比如商品名称、订单快照、文章评论数都属于这一类。如果一个字段的更新频率比查询还高那它就不适合做冗余比如用户的实时余额还是老老实实放在正经字段里靠事务保证吧。2.3 雪花模型与星型模型的设计取舍范式与反范式之争到数据仓库场景会更明显。在 OLAP 场景传统的规范化设计会形成雪花模型维度表拆得非常细地区维度拆成国家表、省表、市表产品维度拆成类别表、品牌表、产品表事实表通过多层外键关联取数。好处是存储省、更新一致性好坏处是一张报表要 JOIN 七八张表查询慢到怀疑人生。所以大多数数据仓库会选择星型模型把维度表做成一张包含全部维度属性的宽表事实表直接关联这一张宽表。比如地区就不拆了一张地区维度表里有 country、province、city、district 四个字段报表统计的时候只 JOIN 一次。这是典型的“面向查询”的设计思路——先想清楚报表怎么查再决定表怎么建而不是先按范式把表拆得精精细细再硬着头皮去查。相比之下雪花模型的“规范性”更多是对存储成本和更新一致性负责星型模型的“扁平化”更多是对查询性能和易用性负责。在真实数仓建设里除非存储成本极其敏感否则我见过的大多数团队都会倾向星型模型再用宽表、物化视图去兜底复杂指标。一个核心原则OLTP 系统把一致性当命根子OLAP 系统把查询性能当命根子设计原则必须跟着场景走。3. 主键设计一个不当选择埋下的好几年的坑3.1 自增 ID、UUID、雪花 ID 的适用边界主键设计是数据库设计里最容易被低估的一环但它决定了未来几年你分不分库、扩不扩容、迁不迁移数据。很多新手觉得“主键不就是加个 AUTO_INCREMENT 吗”真到了线上规模第一批哭的就是他们。自增 ID是中小项目的默认选择优点是简单、有序、写入性能好。InnoDB 的聚簇索引物理上就是按主键排序的 B 树自增 ID 顺序写入新记录直接追加到页尾不用频繁做页分裂写入效率最高。但自增 ID 有两个隐患。第一分库分表的时候多个库各自生成自增 ID 必然撞车解决方案要么是设置不同的 auto_increment_offset 和 auto_increment_increment要么引入分布式 ID 中心要么干脆一开始就不用自增。第二ID 直接暴露在 URL 和 API 里竞争对手和爬虫只需要遍历 ID 就可以把你订单量、用户量摸得一清二楚这在 C 端业务里是很大的信息泄露面。UUID看起来完美解决了全局唯一问题应用层生成、不依赖数据库但它有几个非常现实的坑字符串存储占空间是 bigint 的好几倍完全无序插入时随机落在 B 树的各个位置页分裂频繁写入性能可能差一个数量级索引体积暴涨内存缓冲池放不下更多索引页查询性能也跟着下滑。如果你实在要用 UUID至少转换成 BINARY(16) 存储或者用 UUIDv7 这种有序版本别直接用 varchar(36) 存原样字符串那是最差的选择。雪花 ID是目前分布式场景的主流方案64 位 long前 41 位存毫秒时间戳中间 10 位存机器 ID后 12 位存序列号单机每毫秒可以生成 4096 个不重复 ID。它既全局唯一又带时间有序性写入性能接近自增 ID。缺点是需要引入额外的生成组件并且要注意时钟回拨问题——如果服务器时间往回调同一毫秒内可能生成重复 ID必须做回拨等待或备用方案。我见过不少团队自己写雪花算法以为是件小事结果时钟回拨导致线上数据主键冲突这种事故在设计阶段就要规避。3.2 复合主键与业务主键的取舍关于主键还有一个经常被争论的话题到底该用“无意义的代理主键”还是直接用业务字段当主键我的结论很明确在业务表里绝大多数情况下用代理主键把业务唯一键单独加唯一索引。原因有三条。第一业务字段不稳定手机号会换、身份证号涉及隐私合规、邮箱可以修改主键一旦变所有外键引用和索引都要跟着改代价巨大。第二业务字段可能不够短用 varchar 长字符串当主键索引体积变大、写入性能下降。第三业务字段的生成规则不在你掌控中比如第三方系统的订单号也可能出现重复或格式不一致拿来说主键很容易翻车。复合主键也不是完全不能用它最自然的场景就是多对多关系的中间表比如用户-角色表、订单-商品明细表(user_id, role_id) 或者 (order_id, product_id) 作为联合主键既能保证唯一性又能省掉一个没必要的自增主键。但如果你的业务表里需要大量用“某个字段 另一个字段”才能唯一定位一行这时候往往是建模出了问题——大概率说明你缺了一张更高层的实体表。先把模型理顺再谈主键设计。3.3 主键选择的检查清单结合上面这些我每次设计表结构的时候都会过一遍主键检查清单你可以直接抄走是否满足唯一性这是主键的底线任何情况下都不能重复。是否绝对稳定一旦生成就不会被修改业务字段通常不合格。是否有序或趋势递增有序的主键对 InnoDB 聚簇索引最友好。是否尽量短小主键字段越小二级索引的体积也越小查询越快。是否支持未来的水平扩展全局唯一是分库分表的前提自增 ID 做不到。一句话总结单机小项目自增 ID 随便用分布式项目直接上雪花 ID 或类似的有序分布式 IDUUID 除非你清楚知道自己在做什么否则别碰。4. 字段类型与字段设计那些一夜之间不够用的设计4.1 整数、小数、金额的类型选择字段类型选错破坏力是滞后但猛烈的。我见过一个最典型的教训项目早期用 INT 存用户 ID 和订单 ID数据量到了一定规模ID 超过 21 亿INT 溢出线上直接报主键冲突。凌晨三点爬起来改表整个团队生不如死。所以从第一天开始主键和可能增长到千万级以上的 ID 字段直接用 BIGINT别吝啬那四个字节。金额字段的坑同样深。不要用 FLOAT 或 DOUBLE 存金额IEEE 754 浮点数在二进制里无法精确表示 0.1累加误差在财务账单上是不可接受的。正确的方案有两种一是用 DECIMAL(10, 2) 直接存元简单直观二是用 BIGINT 存“分”把金额换算成最小货币单位避免数据库层的精度问题。我比较推荐第二种因为 DECIMAL 在 MySQL 里同样是变长定点数存储和计算开销都不小整数类型永远是性价比最高的。对外展示时再除以 100 就好。状态类和计数类字段能用 TINYINT 就别用 INT。TINYINT 占 1 字节存储 0-255 的值表达几十个状态位绰绰有余。年龄、数量、星级这类有限取值范围的字段也是同理。国内很多教程喜欢用 INT 做一切整数不是不能用就是纯属浪费存储和索引空间。一张表几十个字段每个都多几字节乘以几千万行就是几个 GB 的差距。4.2 时间字段的设计时间字段看着不起眼踩坑的人一点都不少。MySQL 里的 DATETIME 和 TIMESTAMP 有区别TIMESTAMP 范围从 1970 到 2038 年有时区转换机制会随数据库时区变化而变化DATETIME 范围更大不涉及时区转换存进去是什么就是什么。如果你的业务只在国内跑用 DATETIME 最省心如果系统面向全球用户或者由多机房部署建议统一用 TIMESTAMP配合数据库层面设置 UTC 时区应用层再按用户时区转换展示。还有一个方案是直接用 BIGINT 存毫秒时间戳。好处是非常干净不依赖数据库的时区设置跨 MySQL、PostgreSQL、达梦、人大金仓这类异构数据库迁移时完全不用考虑时区差异。坏处是你在数据库工具里看这个字段的时候看到的是一串数字不能直观读出来调试体验比较差。我个人的习惯是业务上不需要直接读时间的场景比如日志表、流水表、埋点表用 BIGINT业务上要频繁查询时间范围且要人肉看数据的场景用 DATETIME。关键提醒时间字段尽量用数据库层默认值比如 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP避免应用层忘记传值导致写入 NULL 或者字段不更新。这个设计可以帮你省掉大量排查“这个记录是什么时候创建的”“为什么修改时间没变”的时间。4.3 NULL 值、默认值、枚举的坑NULL 在数据库里是个“薛定谔的值”——它不等于 0不等于空字符串也不等于 FALSE。它会让索引失效、让 COUNT(*) 和 COUNT(col) 结果不一致、让应用层 Java 代码冒出 NPE、让前端显示一个“undefined”。举个最常见的例子一个用户备注字段有的人是 NULL有的人是空字符串 有的人填了文字。你想统计“有多少用户没填备注”写 COUNT(*) WHERE remark IS NULL 永远统计不全还得加上 remark 的判断写出来的 SQL 又丑又容易漏。所以设计字段的时候我的默认原则是能不 NULL 就不 NULL一律 NOT NULL DEFAULT 默认值。字符串类型默认空字符串数字类型默认 0时间类型默认 CURRENT_TIMESTAMP。真要表达“无意义”用业务上的特殊值比如 -1、N/A会更清晰可控。枚举值的设计也是一门学问。很多新手喜欢把状态直接写死成字符串比如 status 字段存 pending、paid、shipped可读性好但存储和索引效率低。更规范的做法是状态字段用 TINYINT 存数值把中文含义和维护规则放到字典表里业务代码里再转换成常量或枚举。-- 不建议 status VARCHAR(20) NOT NULL DEFAULT pending -- 更推荐 status TINYINT NOT NULL DEFAULT 0 COMMENT 0-pending, 1-paid, 2-shipped, 3-cancelled注意一点如果这组枚举含义在未来可能增加要把状态字段的容量余量留足哪怕现在只有 5 种状态TINYINT 的 0-255 也够用很久。别因为“现在只有几个值”就用 BIT 或者更窄的类型后期扩展会非常痛苦。4.4 预留字段与扩展字段的争议几乎每个项目里都会有人提出“要不在表里加几个预留字段吧万一以后要用呢”。我的回答一直是别加。预留字段是数据库设计里最经典的伪需求——你既不知道它将来要存什么类型也不知道它未来代表什么含义等到真的需要的时候它的名字和注释都给不了任何线索反而像埋了一颗地雷。我见过一张老表里预留了 10 个 VARCHAR(255) 字段名字叫 RESERVE1 到 RESERVE10实际项目黄了都没用上 3 个。另外一个团队接手的时候根本不敢删只能一直带着白白占着存储空间和索引体积虽然没索引但行大小被撑大页能放的记录数变少查询性能一样受影响。那如果确实有“未来可能加各种自定义属性”的需求呢合理的方案不是预留列而是用 JSON 字段。MySQL 5.7 之后支持 JSON 类型可以存动态的结构化数据也支持 JSON 函数查询。比如商品表加一个 attributes JSON 字段将来商品要加“是否支持礼品包装”“保质期多少天”这些非通用属性直接写进 JSON 就行不用频繁 ALTER TABLE。另一个更规范的做法是拆子表用“实体-属性-值”模型去表达动态属性但 EAV 模型的查询特别绕除非是配置类页面否则我一般不推荐。5. 索引设计的“二八定律”放对位置的索引才是好索引5.1 索引为什么有用——B 树的数据访问逻辑要理解索引先要理解没有索引时数据库如何找数据。InnoDB 的表数据本身是一棵以主键为序的 B 树叶子节点存储整行数据也就是你看到的 .idb 文件。如果查询条件不是主键数据库只能从第一个叶子节点开始沿着链表把整张表所有数据页都扫一遍逐个判断是否满足条件这就是全表扫描。数据量小的时候无所谓到了千万行级别一次全表扫描可能扫掉 20 万个数据页每页一次磁盘 IO性能直接崩。索引本质上就是额外维护的一颗“按索引键排序的 B 树”叶子节点存的是主键值。有了它数据库可以像查字典一样从根节点开始二分定位几层之内就找到目标记录的主键然后再回到主键索引上取整行数据这就是“回表”。为什么互联网公司天天盯着慢查询就是因为慢查询背后往往是某个查询忘记建索引把全表扫描变成了线上常态。这里要特别说明一个索引设计原则索引排序规则与查询条件是否匹配决定了索引能不能被使用。比如你建了一个 (status, create_time) 联合索引查询条件是 WHERE status 1 AND create_time 2024-01-01那数据库可以顺着 status 定位到一批数据再用 create_time 做范围过滤。但如果你查询条件只有 create_time没有带上 status那这个联合索引就用不上因为最左前缀被跳过了。5.2 哪些字段最值得建索引索引不是越多越好但下面这几类字段只要出现在高频查询里几乎都值得建索引等值查询频繁的字段比如 user_id、order_no、mobile这类字段加索引能让 WHERE 条件直接用索引定位而不是全表扫。JOIN 关联字段一条 JOIN 语句要关联两张表被驱动表的关联字段必须有索引否则每次匹配都要全表扫一遍。这就是为什么订单表上的 user_id、商品表上的 category_id 这类外键字段几乎必须建索引。排序和分组字段ORDER BY、GROUP BY、DISTINCT 背后都是排序操作如果字段有索引B 树本身就是有序的可以直接顺序读取跳过了额外排序。唯一性约束字段手机号、身份证号、业务单号这类需要保证不重复的字段直接建唯一索引既约束了数据又加速了查询。还有一条经验覆盖索引是性能优化里性价比最高的手段之一。如果查询只需要访问索引字段本身不需要回表读整行性能会提升一大截。怎么看有没有覆盖在 EXPLAIN 的 Extra 列看到 Using index 就说明走了覆盖索引。比如一张订单表高频查询只关心状态和金额那就建一个 (status, amount) 的索引查询 SELECT status, amount FROM orders WHERE status 1 时连回表都省了。5.3 索引失效与“帮倒忙”的索引很多开发遇到过“明明建了索引查询还是慢”的诡异问题大概率是踩了索引失效的坑。我总结几个最常见的失效场景。对索引字段使用函数比如 WHERE DATE(create_time) 2024-01-01或者 WHERE YEAR(create_time) 2024。这种情况下索引里的值是原始的时间戳你拿函数处理过的值去匹配B 树没法定位只能全量扫描。正确的写法是 WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00让索引按范围查找。隐式类型转换手机号字段建成 VARCHAR查询条件却传了一个数字比如 WHERE mobile 13800138000。MySQL 的规则是字符串字段跟数字比较时会把字符串转成数字再比较这一转索引就失效了。正确做法是查询条件永远跟字段类型保持一致传字符串就加引号。模糊查询LIKE abc% 可以用索引LIKE %abc% 不能用——因为 B 树只能从左往右匹配开头就是通配符无法定位起点。文本搜索需求优先用全文索引或者专门的搜索组件别指望普通 B 树索引解决所有模糊匹配。低选择性字段一个只有 true/false 两个值的字段建了索引之后每次匹配都可能返回一半的数据行数据库优化器会自动放弃索引改走全表扫描反而更慢。性别、删除标记这类字段通常不需要单独建索引除非跟其他字段组成联合索引的组成部分。还有一种“帮倒忙”的索引是冗余索引。建了 (a, b) 联合索引又建了单独的 (a) 索引后者就是纯粹的多余——联合索引的最左前缀已经覆盖了单字段 a 的查询场景。这种冗余索引白白占用空间还会拖慢写入。建议定期用工具检查重复索引比如 MySQL 的 sys.schema_redundant_indexes能直接找出这类冗余。6. 并发、锁与事务设计阶段就该想清楚的运行规则6.1 事务隔离级别怎么选——可重复读 vs 读已提交数据库设计除了表结构还包括事务和并发模型的设计。新人经常忽略这一点两个事务同时改同一行数据会发生什么事务隔离级别就决定了这个问题。SQL 标准定义了四种隔离级别读未提交、读已提交、可重复读、串行化隔离强度从弱到强并发性能从高到低。MySQL InnoDB 默认是可重复读允许事务内多次读取同一数据结果一致通过 MVCC多版本并发控制实现快照读读操作不会阻塞写操作。PostgreSQL 和 Oracle 默认是读已提交每次 SELECT 都读最新已提交数据。作为设计决策隔离级别不是越高越好。可重复读这一档虽然解决了不可重复读但在当前读场景下需要用间隙锁来防幻读而间隙锁正是很多死锁事故的根源。互联网高并发业务里超卖、对账这类问题往往通过业务逻辑和幂等设计解决而不是依赖隔离级别。我见过不少团队在生产环境把隔离级别调成读已提交配合 ROW 格式的 binlog 做主从复制实测下来死锁少了很多。如果你正在设计新系统可以认真评估一下业务真的需要可重复读吗还是读已提交 应用层补偿就够了6.2 死锁的成因与设计层面的预防死锁的经典场景是两个事务各自持有一把锁同时等待对方释放另一把锁互相僵持。在数据库里最常见的触发方式有两个。第一个是多行更新顺序不一致。事务 A 先更新订单 1 再更新订单 2事务 B 先更新订单 2 再更新订单 1当两个事务交错执行时就会出现 A 持有订单 1 的锁等订单 2B 持有订单 2 的锁等订单 1双方谁也不让谁。解决方案说起来很简单所有事务统一按相同顺序更新资源比如都按订单 ID 升序处理。但在真实业务代码里这个顺序往往不是显式控制的所以要在设计评审阶段就强调这个约束。第二个是单个 SQL 涉及范围很大间隙锁互相覆盖。在可重复读级别下一个 UPDATE 或 DELETE 如果 WHERE 条件走的是范围索引InnoDB 除了锁定命中的记录还会锁定范围内的间隙防止幻读。两个事务如果各自锁了一段有交集的间隙就可能互相等待。这也是为什么我前面建议评估读已提交级别的另一个原因——间隙锁的范围更小死锁概率几何级下降。设计层面防死锁还有几个朴素但有效的原则事务尽量短小锁持有时间就越短绝对不要在事务里调用远程接口或 RPC那等于把锁拽在手里去干别的事大事务拆成小事务批量操作控制每个批次的行数。锁是数据库引擎在执行的但高并发下能不能不撞车从表结构和事务设计那一刻就注定了。6.3 乐观锁与悲观锁在表结构上的体现谈到并发控制必须提乐观锁和悲观锁。悲观锁就是 SELECT ... FOR UPDATE查出来的时候就把行锁住其他事务想改只能等。它的前提是锁住的记录必须能被数据库的正向索引命中否则 InnoDB 会锁全表性能直接崩。乐观锁则完全不一样它不锁数据库资源而是靠一个版本号字段在 update 时做条件校验。乐观锁的表结构设计就是必须预留一个 version 字段ALTER TABLE orders ADD COLUMN version INT NOT NULL DEFAULT 0;更新的时候带上版本条件影响行数判断是否冲突UPDATE orders SET status 2, version version 1 WHERE id 123 AND version 0;如果返回的影响行数为 0说明这条订单在本次读取之后已经被别人改过了应用层拿到这个信号后再决定重试还是报错。这个 version 字段必须在表设计初期就加上别等上线之后再去加虽然能加但那时存量数据都要回填、所有写路径都要改测试成本翻倍。我自己的经验分享余额扣减、库存扣减这种强一致场景用悲观锁或 SQL 层面的原子更新更稳妥而状态流转、配置更新这种冲突概率低的场景用乐观锁足够性能好得多。加 version 字段是一件成本极低、收益极高的事情坚决推荐。7. 迁移与演进设计得再好也躲不过“改表”这一天7.1 用版本化工具管理表结构——别再手工执行 SQL很多创业团队和课程设计的数据库管理方式就是一个共享 SQL 文件谁改表谁去执行一下或者干脆 DBA 手工跑再在群里吼一声“我改了订单表”。这种方式在前三个月勉强能撑等团队规模和表数量上来之后就会变成事故现场有人本地改了表没同步、生产环境跟开发环境的表结构不一致、上线时漏执行了某条 DDL导致应用启动直接报错。成熟的团队会用版本化迁移工具比如 FlywayJava 生态和 Liquibase。它的核心思路很简单所有表结构变更都写成一个带版本号的脚本提交到代码仓库部署时自动执行未执行过的脚本并且在数据库里维护一张记录表来跟踪版本。-- V1__create_orders.sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL UNIQUE, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, total_amount BIGINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_create_time (created_at) ); -- V2__add_order_version.sql ALTER TABLE orders ADD COLUMN version INT NOT NULL DEFAULT 0;好处是显而易见的变更可追溯、环境一致性有保证、结合 CI/CD 还能在流水线里自动执行。无论你是个人做课程设计还是公司开发用上这类工具之后“工程发布时怎么配数据库”就不再是玄学而是一条自动化流水线。如果你用的是 IDEA 这类 IDE也可以直接用它集成的数据库迁移功能但独立工具的可控性更强推荐优先考虑。7.2 大表结构变更的低风险姿势即使有了迁移工具把迁移脚本跑在千万行的大表上仍然是高危操作。试想一下ALTER TABLE 加字段的时候MySQL 5.6 之前的行为是拷贝整张表、期间锁表、业务写全部阻塞几千万行的表可能要锁几十分钟这在任何时候都是不可接受的。现在的 MySQL 8.0 对很多 ALTER 操作已经做了优化加字段如果加在表末尾且字段可空或有默认值走的是 INSTANT 算法秒级完成不锁表。但是修改字段类型、修改字段顺序、删除字段这类操作仍然需要 COPY 或 INPLACE 算法期间可能锁表或占用大量磁盘空间。因此线上大表变更要遵循几个原则加字段优先加末尾的可空字段避免触发 COPY 算法。非空字段先以空字段加上回填完数据再改约束避免一次性锁表太久。大表加索引用在线工具比如 Percona Toolkit 的 pt-online-schema-change或者 GitHub 开源的 gh-ost它们通过临时表 触发器/ binlog 同步的方式完成变更业务基本无感。变更安排在业务低峰期并且提前观察磁盘空间和主从延迟。真实演变案例一张 2000 万行的用户表现在要加一个 is_vip 字段。如果直接跑 ALTER TABLE 加 NOT NULL DEFAULT 0大概率引起长时间锁表正确姿势是先用 INSTANT 算法加一个默认为 0 的可空字段后端发版上线然后写个脚本分批回填存量数据虽然默认值已经是 0不需要回填但如果要按用户维度差异化初始化就要分批 UPDATE最后再改成 NOT NULL 和最终默认值。每一步都是小额操作业务感知为零。7.3 从单表到分库分表演进路线上的关键设计数据库设计最高级的考验不是一开始就设计得面面俱到而是设计出“允许你活下去并且能顺利演进”的结构。大多数系统都是从单库单表起步的演进路线一般是加缓存 → 读写分离 → 垂直拆分 → 水平分表。加缓存热点数据放到 Redis数据一致性靠过期时间或主动淘汰这一步可以撑住大部分读多写少的场景。读写分离主库负责写从库扛读注意主从延迟问题。垂直拆分按业务域拆成订单库、用户库、商品库每个库独立部署各自扛压力。水平分表单表数据量过大时按用户 ID、订单 ID 这类分片键把数据拆到多个表甚至多个库。水平分表这条路上分片键的选择是最核心的决策它必须跟你的业务查询模型高度匹配。比如订单表按 user_id 分片用户查询自己的订单就特别快一条 SQL 直接路由到对应分片但商家想查自己店铺的订单就不知道订单在哪个分片需要额外维护一份“商家-订单号”的映射关系或者用中间件做全分片广播查询性能大打折扣。另一个例子是日志流水表按天分片查询时间范围和某一天的数据就非常自然。从设计原则角度看分库分表的前置条件是主键必须全局唯一——自增 ID 在这里就直接出局所以前面反复强调初始设计就要用雪花类分布式 ID就是这个原因。还有一条建议除非业务已经有明确的量级增长预期否则没必要在一开始就引入分库分表中间件那是给最复杂的情况准备的复杂度。先把索引建好、慢查询清干净、用了缓存大部分系统根本走不到拆库那一步。8. 两个真实的数据库设计复盘从需求到落地8.1 案例一订单中心表的设计迭代第一个复盘来自一个电商订单中心它经历了三版设计每一版都是被现实逼着往前走。V1 版本订单数据全堆在一张表里用户信息、商品信息、收货地址、支付流水、优惠明细全部冗余在 order 表里一张表 60 多个字段。刚开始团队只有一个后端联调方便写 SQL 不用 JOIN。结果半年后订单量上来单表 500 万行查询接口开始变慢。更严重的是商品促销规则调整后历史订单的优惠明细已经和当前的规则完全对不上数据对账天天出问题。V2 版本做了垂直拆分把订单拆成订单主表和订单明细表两张主表存订单级别的信息用户、金额、状态、收货人明细表存商品维度的信息商品 ID、数量、成交单价、快照名称。这次调整让订单详情和订单列表的查询都清晰了也顺势建了 user_id、create_time、status 的联合索引慢查询从几十个降到个位数。V3 版本是并发和扩展冲击下的产物。营销大促期间频繁出现订单状态更新的并发冲突于是加了 version 字段做乐观锁。同时商品改价频繁历史订单快照必须独立存储明细表里冗余了一个 product_snapshot_json 字段把下单时刻的商品图片、参数、赠品信息都打了快照。最后因为热数据量大上线了冷热归档机制把超过 90 天的已关闭订单迁移到归档表实时查询性能进一步释放。这个案例想说明的其实是设计原则允许你犯一次错但你要有演进的能力。V1 不是完全错误的它让你快速跑通了业务但如果你长期停留在 V1不引入 V2 的拆分和 V3 的并发控制、快照机制早晚会被数据规模和业务复杂度压垮。8.2 案例二物联网设备数据表的设计第二个案例来自一个物联网设备监测系统场景是设备每 5 秒上报一次状态数据一天单台设备产生 17280 条记录一万台设备一天就是 1.7 亿条。第一版设计是把所有记录插进一张大表字段包括 device_id、timestamp、温度、湿度、电压、信号强度。上线第一天没问题一个月后单表已经几亿行按设备和时间范围查询直接全表扫描一条 SQL 跑几十秒。走的弯路包括给 timestamp 单独加了索引但查询条件是 WHERE device_id ? AND timestamp ?联合索引的缺失导致只能先按设备过滤或按时间过滤效果都很差。第二版核心做了三件事。第一按天分区PARTITION BY RANGE (TO_DAYS(timestamp))每天一个分区时间范围查询只需要扫描对应分区历史分区数据还可以直接做归档或 DROP。第二建了 (device_id, timestamp) 联合索引设备维度加时间范围的查询走完美命中索引响应时间从几十秒降到百毫秒级。第三设计了数据保留策略原始明细数据保留 30 天DOWN 采样保留 180 天更早的聚合成小时级甚至天级统计表。这个案例也可以跟时序数据库做对比如果是更纯粹的时序场景用专门的 TSDB 往往比通用关系库更省心但关系库加上分区索引一样能撑住相当量级的写入和查询。8.3 复盘之后的几条铁律经历这么多项目踩过无数坑之后我总结出几条可以打印出来贴在工位上的设计铁律没有文档的表就是没人敢动的表每个字段必须有注释枚举含义写清楚变更记录留痕。先设计查询再设计表先罗列出这个模块将来最高频的 10 条查询语句再反推表结构和索引。主键用代理键业务唯一键单独建索引不要在自然键上做文章。能不加索引就不加高频查询必须覆盖宁缺毋滥但该建的一个都不能少。表结构演进要像代码一样可回滚、可追踪用版本化迁移工具永远不要手工执行线上 SQL。最后再分享一个小技巧每次建新表之前拿一分钟在脑海里把这张表“运行”一年——想象一年后这张表会有多少行数据主要的查询条件是什么哪些字段会被更新会不会有并发写入冲突。如果这些问题的答案都是清晰的那你设计出来的表基本不会出大问题如果这些问题一个都想不清楚那说明业务还没梳理明白别急着建表先回去跟产品聊清楚再说。