做数据库这么多年我接手过的最头疼的一张表是一张几千万行的订单流水表。上线前三个月一切岁月静好等数据量一上来各种问题接踵而至订单号重复、关联用户对不上、状态值永远是pending没法推进。后来排查根因所有问题都指向同一个源头——建表的时候对表的约束条件的考虑太随意了。约束条件这个词听起来像教科书里的基础概念但真正决定一张表能不能扛住业务、能不能撑到大数据的恰恰就是这些容易被忽略的边界规则。我见过太多团队把重心放在索引优化和分库分表上建表时却连主键都没想清楚更别提唯一约束、非空约束、外键约束的取舍。这篇文章我想把这些年和约束条件打交道的经验完整捋一遍主键、唯一、非空、外键、CHECK怎么选、怎么用、什么场景下必须放弃以及约束和索引、大表性能、跨表合并这些实操场景的纠葛。适合正在设计新表、或者正被脏数据和慢查询折磨的开发者参考。1. 约束条件到底是什么先想清楚再动手1.1 约束不只是必填项它是数据完整性的守门员很多人一提约束条件第一反应是某个字段能不能为空。这是最常见的误解。约束条件在数据库里是完整性的定义它管的不是好不好填而是数据是否可信。MySQL里常见的约束一共有五类主键约束PRIMARY KEY、外键约束FOREIGN KEY、唯一约束UNIQUE、非空约束NOT NULL和检查约束CHECK。它们的底层实现方式各不相同主键和唯一约束在InnoDB存储引擎里会生成B树索引非空约束是行级检查外键约束则涉及父子表之间的锁机制。这些差异直接决定了约束不只是建表时的几个关键字而是影响写入性能、锁冲突、甚至查询计划的物理结构。我用一个生活化的类比来理解主键约束就像每个人的身份证号唯一约束就像手机号或车牌号非空约束是表单里的星号必填项外键约束是这个订单一定属于某个已存在的用户的引用承诺CHECK约束则像年龄必须大于0的规则声明。这些规则越严谨业务代码里需要做的防御性判断就越少数据出错的概率就越低。1.2 动手建表前把约束当成需求评审来做我在实际项目里养成了一个习惯建表之前先不看SQL怎么写而是先回答几个问题。第一个问题这张表的唯一性维度是什么订单表的唯一性不能简单说是订单号因为多渠道接入时不同渠道的订单号可能重复。付费记录表呢同一笔订单可能分多次支付那唯一性就得落到订单号支付流水号。搞清楚唯一性主键和唯一约束的雏形就出来了。第二个问题哪些字段不允许为空这个不能靠感觉要沿着业务主流程走一遍。比如一张用户表手机号在注册流程里是必填的但如果运营后台允许创建未绑定手机号的用户那这个字段就不能简单设成NOT NULL。非空约束设计得比业务规则更严上线后会发现应用层疯狂报错设计得比业务规则更松脏数据就会悄悄进来。第三个问题将来这张表会不会被跨表合并、被关联查询、被做数据迁移我在第三篇部分会专门讲跨表合并的场景这里先记住一个结论如果一张表未来要以某个字段作为关联键、合并键那这个字段就值得认真考虑加唯一约束或者至少加索引。否则left join一出来重复行会把统计口径直接打爆。1.3 一张表到底该放多少约束我的判断标准有人会走向两个极端要么一张表从头到尾一个约束都没有全靠应用层把守要么把所有能加的约束全都加上结果每条写入都慢如蜗牛。我的判断标准很简单约束至少覆盖三件事实体完整性、业务唯一性、必要字段完整性。对应下来就是主键必须有、业务上的唯一键必须有、关键字段不能为空。外键和CHECK属于按需型约束不是必选但必须清楚地知道为什么不用它们。约束不是越多越好而是每一个约束都要能说清楚它挡住的到底是什么样的脏数据。2. 四大基础约束逐个拆解2.1 主键约束自增、UUID、还是业务键主键是约束条件里的头号玩家但很多开发者在选主键的时候全凭惯性。我看到最普遍的做法是有自增就一定用自增这在单库小业务下完全没毛病但做过几年大表之后你会发现主键的选择本质上是在为未来的数据分布做决策。自增主键最大的优点是写入顺序好。InnoDB的聚簇索引按主键顺序组织自增主键天然是尾部追加页分裂少写入性能高。缺点同样明显一旦走到分库分表、或者需要多套环境的数据合并自增ID会冲突得一塌糊涂。我经历过一次两套系统的用户数据合并两边都是自增ID合并前做了半小时ID映射那感觉简直是灾难。UUID主键正好反过来。它全局唯一天生适合数据合并和分布式生成但随机字符串带来的问题是聚簇索引频繁页分裂写入抖动明显表空间膨胀也更快。折中方案是使用雪花ID或类似的有序全局ID——既有全局唯一性又尽量保持递增趋势。如果非要用UUID我建议至少用UUID短版本或者把UUID转换成有序的结构不要在核心大表上直接存36位字符串当主键。还有一种情况是拿业务字段当主键比如身份证号、手机号。我的建议是坚决不要。业务字段天然是可变的——手机号可以换身份证号虽然不变但涉及隐私又不能随便暴露。主键一旦依赖于业务字段后面改业务规则的时候会非常被动。关系表、中间表用复合主键比如用户ID加角色ID倒是合理但如果是业务大表我更倾向于额外加一个自增id做主键业务唯一性用唯一约束去保证。2.2 非空与默认值最容易被忽略的两个小坑非空约束看起来最简单但坑都藏在细节里。最典型的一个唯一索引允许存在多个NULL值。我遇到过这样一个事故。一张用户绑定表设计时给设备号字段加了唯一约束但没加非空。当时想着有的用户可能没有绑定设备允许为空比较灵活。结果上线之后大量没有设备号的用户每个都可以插入一条记录因为MySQL里的唯一索引完全不拦多个NULL。应用层做判断的时候一查这个设备号是否存在返回没有然后业务就傻眼了。这个问题排查了整整半天根因就是唯一约束允许NULL的语义组合出了问题。所以我的经验是如果某个字段既要唯一、又要允许没有值那你得想清楚没有值用什么来表示。是空字符串还是特定的占位值在MySQL里空字符串和0是可以被唯一约束正常去重的NULL则不行。你完全可以用空字符串来表示未绑定让唯一约束真正生效。默认值的设计也一样有讲究。状态字段给默认值这是常识但要小心默认值本身会不会掩盖业务问题。比如状态默认为待审核结果业务代码忘了更新状态是不是就形成了看起来正常的脏数据时间字段建议直接默认当前时间降低应用层的负担。这里还要注意MySQL版本的差异MySQL 5.6.5之前DATETIME不能直接设置默认CURRENT_TIMESTAMP很多老项目不得不靠应用层塞时间。如果你还在维护老库查一下所有时间字段的默认值很可能会发现一片空白。2.3 唯一约束业务唯一性别全指望代码判断做后台系统的人大概率都写过类似的逻辑先查一下记录存不存在不存在才插入。这种先查后插的做法在并发量低的时候没问题一旦两个请求同时进来两个都查不到两个都插入成功唯一性就破了。正确解法是应用层校验数据库唯一约束双保险。数据库唯一约束是最后一道防线兜住并发场景下的漏网之鱼。比如用户手机号、订单号、支付流水号这些业务上必须唯一的字段不管应用层判断做得多好数据库层的唯一约束都该加上。唯一约束在带来安全性的同时也有代价每加一个唯一约束InnoDB就要多维护一棵B树写入时需要做唯一性检查在高并发下会有额外的锁开销。所以唯一约束不能乱加只加在业务语义上必须唯一的字段上。另外唯一约束创建的同时会自动创建一个唯一索引这个索引除了约束作用还能服务查询——比如订单号是唯一约束那按订单号查订单天然就走索引这就是一鱼两吃。2.4 CHECK约束新版MySQL终于支持了在MySQL 8.0.16之前CHECK约束是一个摆设——SQL可以写但MySQL不执行只解析后直接忽略。所以很多老开发根本不碰这个语法默认MySQL不支持CHECK。实际上从8.0.16开始MySQL已经完整支持CHECK约束的执行而且在数据写入时如果违反会直接报错。CHECK约束最常用的场景是枚举值校验。比如订单表的状态字段想限制只能存待支付、已支付、已取消、售后中这几类就可以用CHECK或者ENUM。用CHECK的好处是改约束比改ENUM灵活虽然MySQL修改CHECK同样需要重建表但语义上至少比ENUM直观坏处是CHECK只能做单行内的检查不能跨表、不能聚合。比如一个商品订单的总金额必须大于0这种单行检查它管得了一个订单的总金额必须等于所有明细之和这种跨行检查它就无能为力了。我个人对状态类字段的偏好是状态值很少且基本稳定时用ENUM可读性好开发一眼看得懂状态值未来可能扩展、或者需要跟代码里常量对得很齐时用TINYINT加字段注释。CHECK约束适合做数值范围检查比如年龄必须大于0、折扣必须在0到1之间这类规则定死在数据库层应用层哪怕手滑写错也拦得住。3. 外键约束用还是不用这是个问题3.1 外键的机制成本与锁风险外键约束是争议最大的一个约束大厂基本不用传统企业系统却很爱用。要理解这个分歧得先看清外键的成本。在InnoDB里外键约束不只是一个声明。当你要插入或更新一张子表时MySQL会去父表做一次引用检查这个检查会在父表的记录上加共享锁S锁。说白了每次写子表都牵扯到父表的行锁。如果父表正好有大批量更新或删除子表写入就会被堵住锁等待一多整个系统的吞吐量就下来了。几千万行的大表上这个问题会被放大得非常明显——你根本不敢在核心链路上放外键因为一次父表的批量更新就可能拖垮一串子表写入。还有运维层面的硬伤外键在DDL变更时特别碍事。想改父表结构得先看有没有外键引用想删父表数据还得担心子表会不会跟着出问题做分库分表、数据归档时外键直接让你寸步难行。MySQL的外键本身也不支持跨库跨实例一旦服务间拆分成微服务外键约束根本无从谈起。3.2 什么时候我必须用外键听到这里别急着把所有外键都删了。在特定场景下外键是真香。如果你的系统是单库单表、业务并发不高、数据可靠性要求反而很高比如内部工单系统、报销系统、小型CRM外键可以帮你挡住大量应用层漏洞。有人会忘记检查用户是否存在就插入订单有外键在数据库直接拒绝有人会写完子表忘写父表有外键在数据完整性有得兜。团队人少、中间层代码不够严谨的时候外键是一个廉价高效的守护者。使用外键时有一个需要小心的功能ON DELETE CASCADE。这个级联删除字面上看很贴心——删父表子表自动跟着删。实际操作中是灾难制造机。我有一次误删了父表的一批数据CASCADE几秒钟之内把关联的几十万条子表记录清得干干净净那种心凉的感觉至今记得。所以我的建议是即使使用外键引用关系上尽量只做约束、不做级联删除动作交由业务代码显式控制至少你在执行删操作前能看得见影响范围。3.3 没有外键怎么保证两张表的数据对得上互联网场景下放弃外键之后数据一致性不能纯靠祈祷。我在项目里常用的替代方案有四种。第一种应用层事务保证。在同一个数据库里一个事务里同时插入父表记录和子表记录靠本地事务保证原子性这是最基础的替代方案。如果涉及多个服务就要引入分布式事务方案或者本地消息表最终一致性。第二种对账补偿。交易系统里每天跑定时任务比对父表和子表的记录是否存在、状态是否匹配发现不一致的走告警或者补偿流程。这种方案不如外键及时但胜在灵活可靠。第三种写操作前置校验。插入子表前先查父表是否存在虽然在高并发下有先查后写的竞态问题但对于多数非核心场景已经够用了。第四种用唯一约束做软引用。比如子表里存了父表的业务编号就给这个编号建唯一约束如果业务上必须唯一同时确保两端编码规则、字符集一致。这样即使没有外键查询和关联的性能也有保障。热词里提到的账号匹配就是典型场景表A的银行账号要去匹配表B前提是表B的账号字段有个靠得住的唯一约束否则匹配出来一堆重复行数据就废了。4. 约束和索引、性能的那些事4.1 约束和索引是一回事吗这个问题的答案是一半一半。主键约束创建后InnoDB会基于主键列建立聚簇索引唯一约束创建后也会自动生成一棵唯一索引的B树。从这个角度说约束的物理形态就是索引。但约束和索引在语义上完全不是一回事。索引的目的只是加速查询它不限制数据的值约束的目的是限值数据的合法性只不过实现时借用了索引的结构。理解这一点对做性能分析很重要你会经常看到一条SQL明明走了某个唯一索引却不知道为什么这个索引会用不上或者为什么一个普通索引能造的查询速度比唯一索引慢——因为它们虽然长得很像用途完全不同。另外有个细节很多人不知道外键列在InnoDB里如果没有索引MySQL会尝试自动创建索引以保证外键检查时能快速定位父表记录。也就是说哪怕你只是声明了外键它都不只是约束还会悄悄改动表结构。这也提醒我们外键和索引在物理层面是纠缠在一起的删外键时如果留下一个自动生成的索引要注意判断它要不要顺手清理。4.2 辅助索引如何避免回表唯一约束的隐藏收益辅助索引如何避免回表这个热词近两年被问得特别多我在这里展开讲一讲因为它和约束设计直接相关。回表的概念是当查询走的是辅助索引二级索引时索引里只存了索引列的值和主键值所需要的其他字段并不在辅助索引上。MySQL要先找到主键再到聚簇索引里取整行数据这个过程就是回表多一次随机IO。避免回表的常见手段是覆盖索引也就是让查询需要的所有列都包含在索引里。这时候你之前建的唯一约束就能派上用场。比如你想快速判断一个用户是否买过某个商品查询条件是user_id和product_idSELECT需要的字段也只有这两个。如果表上已经有一个UNIQUE KEY(user_id, product_id)那这次查询直接在唯一索引里就拿到了全部需要的数据连回表都不用。既有唯一性的约束又有覆盖索引的加速效果这就是我说的一鱼两吃的典型场景。反过来说如果你给某个字段建了唯一约束但查询还要取其他字段那唯一索引只能帮到过滤取数据照样要回表。所以别迷信唯一约束能包治百病索引设计还是要根据查询模式来。4.3 几千万行大表上约束怎么设计不拖性能大表上的约束设计必须拿性能换安全所以每一处都要精打细算。主键自增仍然是最稳妥的选择因为顺序写入对大表的写入性能影响最大。如果因为未来的数据合并必须用分布式ID尽量选有序的雪花ID减少页分裂。给大表加多个唯一索引要极其谨慎每多一个唯一索引插入、更新时都要多维护一棵B树写入放大是实打实的。几千万行的表上每个唯一索引占用几个GB的空间不是开玩笑所以唯一约束只保留业务硬要求的那两三个。另一个关键则是约束和在线DDL的关系。给大表加约束不是随便执行一条ALTER TABLE就完事的。MySQL 8.0的在线DDL已经做得不错可以指定ALGORITHMINPLACE和LOCKNONE尽量不阻塞读写。但对几千万行的表即使加一个唯一索引也需要扫描全表建索引耗时可能几分钟到几十分钟期间的IO压力、主从延迟都不容忽视。稳妥的做法是放到业务低峰期执行先看执行计划评估影响必要时用gh-ost或pt-online-schema-change这类在线表结构变更工具处理。还要注意一点给大表加唯一约束之前数据里如果有重复值建约束会直接失败。正确顺序是先把重复数据清掉再加唯一索引。这个步骤看起来简单实际操作中往往会牵扯出上下游一堆系统因为去重不是删几条记录那么容易得先搞清楚业务上哪些记录该保留。5. 约束的运维管理建表只是开始5.1 后补约束的SQL与注意事项很多系统问题不是建表时埋下的而是上线后慢慢暴露才想着补约束。补约束是DBA和开发者的日常给几个最常用的SQL。-- 添加主键约束 ALTER TABLE t_order ADD PRIMARY KEY (id); -- 添加唯一约束 ALTER TABLE t_order ADD UNIQUE KEY uk_order_no (order_no); -- 添加外键约束 ALTER TABLE t_order_detail ADD CONSTRAINT fk_detail_order FOREIGN KEY (order_id) REFERENCES t_order(id); -- 添加CHECK约束MySQL 8.0.16 ALTER TABLE t_order ADD CONSTRAINT chk_amount CHECK (amount 0); -- 修改字段为非空 ALTER TABLE t_user MODIFY COLUMN mobile VARCHAR(20) NOT NULL;补约束和建表时写约束最大的区别在于存量数据。补唯一约束之前一定要先查重复SELECT mobile, COUNT(*) FROM t_user GROUP BY mobile HAVING COUNT(*) 1;查到重复数据后不是直接删除就行得先跟产品对清楚保留哪一条。这里我也建议先建普通索引让查询走索引加速去重等确认没有重复了再改成唯一索引这样对你操作大表时更安全。5.2 常见报错与排查实录约束相关的报错几乎每个做过MySQL的人都会遇到这里把最常见的几个列成速查表。报错信息可能原因排查方向Duplicate entry xxx for key uk_x唯一约束冲突查询该值已经存在的记录判断是重试还是人工处理Cannot add foreign key constraint类型不一致、字符集不一致、引用列没有索引、引用列非唯一检查两表字段类型、字符集、索引和唯一约束Data too long for column xxx隐式的长度约束被突破检查字符集编码和字段长度定义Check constraint xxx is violatedCHECK约束校验失败核对写入值的取值范围Incorrect table definition; there can be only one auto column自增列定义错误确认自增列必须是主键或者索引的一部分外键创建失败是我见过最多的一种报错根因经常是字符集对不上。父表用utf8mb4子表用utf8两个表的字段看上去类型一样但MySQL在外键检查时发现字符集不同直接拒绝。这种情况在导入导出、建表脚本复制时特别常见。排查外部键问题有一条实用方法论先查两表字段的类型、长度、字符集是否完全一致再看引用列有没有索引或者唯一约束最后看两表的存储引擎是否都是InnoDB。MyISAM不支持外键这也是老生常谈。5.3 约束、锁表与并发写入约束和锁的关系是运维排查中最容易踩坑的地方。唯一约束的存在意味着每次插入或更新都要做唯一性检查这个检查在并发场景下会涉及锁机制它不仅要锁住当前写入的记录还可能在索引相邻区间加锁gap lock防止其他事务插入范围冲突的数据。这就是为什么明明只是几条插入死锁日志里却能看出一堆锁等待。外键的锁风险前面已经讲过这里再补充一个场景批量跑任务时如果子表有外键且一次插入几万条每一条都会去父表做引用检查并加S锁。父表某一行恰好被另一个事务锁住时整个批量任务的吞吐量就瞬间掉到谷底。要排查当前有没有锁表冲突我常用的几个命令-- 查看当前正在执行的查询 SHOW FULL PROCESSLIST; -- 查看InnoDB锁等待MySQL 8.0 SELECT * FROM performance_schema.data_lock_waits; -- 查看锁相关统计 SELECT * FROM sys.innodb_lock_waits;排查到锁冲突后第一件事是确认事务是不是没及时提交。我见过太多次MySQL锁死的情况根因是应用代码里开了事务、处理完业务之后忘记commit连接就那样开着事务一直持锁不释放。这种情况下与其跟业务人员反复讨论SQL有没有问题不如先查一下长期未结束的事务。从设计上减少锁冲突我的习惯是写入顺序固定多个事务按同样的顺序操作表事务体量尽量小不把无关操作塞进一个事务REPLACE INTO和INSERT ... ON DUPLICATE KEY UPDATE这类依赖唯一约束的写入要特别注意并发下锁的竞争它们本质上都是靠唯一索引的锁机制来实现的——并发高时这些写法的死锁率反而比普通INSERT更高。6. 约束思维在其他场景里的延伸6.1 跨表合并、连接查询全靠约束兜底热词里有跨表合并vlookup跨表匹配left join多张表账号匹配这些操作碰上脏数据时有多痛苦做过的人都懂。跨表合并的本质是把两个来源的数据合到一张表里靠某些关键字段对齐。对齐的前提是什么这些关键字段在各自源表里必须可以唯一定位一条记录。换句话说如果没有唯一约束合并的结果要么多出一堆重复行要么张冠李戴把不同记录拼在一起。我见过有人在Excel里用VLOOKUP匹配银行账号VLOOKUP的经典bug是遇到重复值只返回第一个匹配项数据库里也一样——left join时ON字段有重复行结果是成倍膨胀。所以我的建议是涉及跨表合并、关联匹配的关键字段先确认源表的唯一性。源表是自己管理的就去加唯一约束源表不是自己管理的至少要加索引并在合并前做一次计数校验——两张表各自按关键字段去重后的记录数应该和目标表对得上。只有这样合并出来的数据才敢拿去出报表、做决策。6.2 表结构迁移到TDengine超级表子表里的约束逻辑近两年越来越多人把时序数据从MySQL迁到TDengine热词里mysql表结构自动转tdengine超级表子表被反复提及这里也聊两句约束在时序库里的重新理解。TDengine的核心建模方式是超级表加子表超级表定义Schema相当于模板子表通过标签tag区分不同实体比如一台设备、一个点位。每条时序记录的主键变成了时间戳这跟关系型数据库的主键逻辑完全不同。在TDengine里你几乎不用考虑唯一约束、外键约束这些问题因为它的写入模型天然是一个子表一个时间戳对应一条记录时序一致性靠子表和写入时间戳来保证。所以从MySQL迁到TDengine时不能照着原表结构照搬要做的是把原表里的业务标识比如设备ID、采集点编号转成tag把原表里的时间列指定为时间戳主键把需要存储的数值列作为普通字段。这个转换过程其实也是约束思维的延伸——换个平台约束表现形式不同但每条数据必须能被唯一识别这个底层逻辑没有变。6.3 没有备份的情况下误删表能恢复到什么程度热词里有一条生产库环境没有备份的情况下删除了某个用户的所有表如何恢复这是个让人后背发凉的问题。直接说结论没有备份恢复的难度陡增但也不是完全没有路。如果你开启了binlog且binlog_format是ROW模式可以从binlog里把删除事务找出来反向构造回滚SQL这条路径需要极其小心的操作和足够长的binlog保留时间。更惨的情况是连binlog都没配置或者已经被purge那基本只能靠文件系统层面的可能性了比如看还有没有老快照、磁盘上有没有残留的ibd文件碎片但全表恢复的把握微乎其微。从这个惨痛教训里我真正想强调的是备份意识。给核心库做全量备份加增量binlog备份是DBA的基本功给业务开发者的教训是删除数据这个动作一定要经过审批、要有影响面评估。另外要特别注意约束带来的连带删除如果有ON DELETE CASCADE的外键关系删一个表的数据可能会级联删掉好几个关联表。很多人以为删除用户就只是删除用户表结果订单、明细、日志全跟着没了。这种情况下约束本身也可能变成放大风险的工具使用时必须心里有数。7. 踩过的坑和我的习惯文章最后说几个我踩过的坑也是我现在做表结构设计时固定会做的事情。第一个坑是建表时图省事字段全用VARCHAR(255)唯一约束让加就加、不让加就不加。后来报表统计口径对不上排查了两天才发现是源表有重复记录。从那以后我给自己定了一条铁律任何能被当作关联键、合并键的字段先问一句它应不应该唯一该唯一就必须让数据库挡住重复而不是靠人肉保证。第二个坑是修改大表约束时直接在高峰期执行ALTER TABLE。加唯一索引时全表扫描把主库IO直接打满主从延迟飙到几百秒。现在我在生产环境做大表DDL前一定会看执行计划、评估影响、放到低峰期能走在线变更工具就尽量走工具。这个教训很贵花钱买的。第三个坑是关于默认值的。有一个老系统的字段用了0表示未设置后来需求变更说0也是有效状态结果一堆老数据和新逻辑全碰撞在一起。从那以后我设计状态字段时默认值一定会选明确的初始状态而不是一个看起来无害的数字。约束条件不只是限制别人也是在提醒未来的自己——当初为什么这么定义。最后分享一个实用小习惯每建一张新表我都会在最后把约束清单单独写一段注释放在建表语句里说明每个约束的意图。看起来有点啰嗦但半年后回来说这张表的时候你会非常感谢当初的自己。约束条件的核心价值从来不是让数据库更复杂而是让数据在混乱的业务里依旧值得信赖。 
长河君 · 2026/10/1 17:58:27 · 阅读 1.2k 数据库约束条件实战：主键、唯一、外键与CHECK如何选型
做数据库这么多年我接手过的最头疼的一张表是一张几千万行的订单流水表。上线前三个月一切岁月静好等数据量一上来各种问题接踵而至订单号重复、关联用户对不上、状态值永远是pending没法推进。后来排查根因所有问题都指向同一个源头——建表的时候对表的约束条件的考虑太随意了。约束条件这个词听起来像教科书里的基础概念但真正决定一张表能不能扛住业务、能不能撑到大数据的恰恰就是这些容易被忽略的边界规则。我见过太多团队把重心放在索引优化和分库分表上建表时却连主键都没想清楚更别提唯一约束、非空约束、外键约束的取舍。这篇文章我想把这些年和约束条件打交道的经验完整捋一遍主键、唯一、非空、外键、CHECK怎么选、怎么用、什么场景下必须放弃以及约束和索引、大表性能、跨表合并这些实操场景的纠葛。适合正在设计新表、或者正被脏数据和慢查询折磨的开发者参考。1. 约束条件到底是什么先想清楚再动手1.1 约束不只是必填项它是数据完整性的守门员很多人一提约束条件第一反应是某个字段能不能为空。这是最常见的误解。约束条件在数据库里是完整性的定义它管的不是好不好填而是数据是否可信。MySQL里常见的约束一共有五类主键约束PRIMARY KEY、外键约束FOREIGN KEY、唯一约束UNIQUE、非空约束NOT NULL和检查约束CHECK。它们的底层实现方式各不相同主键和唯一约束在InnoDB存储引擎里会生成B树索引非空约束是行级检查外键约束则涉及父子表之间的锁机制。这些差异直接决定了约束不只是建表时的几个关键字而是影响写入性能、锁冲突、甚至查询计划的物理结构。我用一个生活化的类比来理解主键约束就像每个人的身份证号唯一约束就像手机号或车牌号非空约束是表单里的星号必填项外键约束是这个订单一定属于某个已存在的用户的引用承诺CHECK约束则像年龄必须大于0的规则声明。这些规则越严谨业务代码里需要做的防御性判断就越少数据出错的概率就越低。1.2 动手建表前把约束当成需求评审来做我在实际项目里养成了一个习惯建表之前先不看SQL怎么写而是先回答几个问题。第一个问题这张表的唯一性维度是什么订单表的唯一性不能简单说是订单号因为多渠道接入时不同渠道的订单号可能重复。付费记录表呢同一笔订单可能分多次支付那唯一性就得落到订单号支付流水号。搞清楚唯一性主键和唯一约束的雏形就出来了。第二个问题哪些字段不允许为空这个不能靠感觉要沿着业务主流程走一遍。比如一张用户表手机号在注册流程里是必填的但如果运营后台允许创建未绑定手机号的用户那这个字段就不能简单设成NOT NULL。非空约束设计得比业务规则更严上线后会发现应用层疯狂报错设计得比业务规则更松脏数据就会悄悄进来。第三个问题将来这张表会不会被跨表合并、被关联查询、被做数据迁移我在第三篇部分会专门讲跨表合并的场景这里先记住一个结论如果一张表未来要以某个字段作为关联键、合并键那这个字段就值得认真考虑加唯一约束或者至少加索引。否则left join一出来重复行会把统计口径直接打爆。1.3 一张表到底该放多少约束我的判断标准有人会走向两个极端要么一张表从头到尾一个约束都没有全靠应用层把守要么把所有能加的约束全都加上结果每条写入都慢如蜗牛。我的判断标准很简单约束至少覆盖三件事实体完整性、业务唯一性、必要字段完整性。对应下来就是主键必须有、业务上的唯一键必须有、关键字段不能为空。外键和CHECK属于按需型约束不是必选但必须清楚地知道为什么不用它们。约束不是越多越好而是每一个约束都要能说清楚它挡住的到底是什么样的脏数据。2. 四大基础约束逐个拆解2.1 主键约束自增、UUID、还是业务键主键是约束条件里的头号玩家但很多开发者在选主键的时候全凭惯性。我看到最普遍的做法是有自增就一定用自增这在单库小业务下完全没毛病但做过几年大表之后你会发现主键的选择本质上是在为未来的数据分布做决策。自增主键最大的优点是写入顺序好。InnoDB的聚簇索引按主键顺序组织自增主键天然是尾部追加页分裂少写入性能高。缺点同样明显一旦走到分库分表、或者需要多套环境的数据合并自增ID会冲突得一塌糊涂。我经历过一次两套系统的用户数据合并两边都是自增ID合并前做了半小时ID映射那感觉简直是灾难。UUID主键正好反过来。它全局唯一天生适合数据合并和分布式生成但随机字符串带来的问题是聚簇索引频繁页分裂写入抖动明显表空间膨胀也更快。折中方案是使用雪花ID或类似的有序全局ID——既有全局唯一性又尽量保持递增趋势。如果非要用UUID我建议至少用UUID短版本或者把UUID转换成有序的结构不要在核心大表上直接存36位字符串当主键。还有一种情况是拿业务字段当主键比如身份证号、手机号。我的建议是坚决不要。业务字段天然是可变的——手机号可以换身份证号虽然不变但涉及隐私又不能随便暴露。主键一旦依赖于业务字段后面改业务规则的时候会非常被动。关系表、中间表用复合主键比如用户ID加角色ID倒是合理但如果是业务大表我更倾向于额外加一个自增id做主键业务唯一性用唯一约束去保证。2.2 非空与默认值最容易被忽略的两个小坑非空约束看起来最简单但坑都藏在细节里。最典型的一个唯一索引允许存在多个NULL值。我遇到过这样一个事故。一张用户绑定表设计时给设备号字段加了唯一约束但没加非空。当时想着有的用户可能没有绑定设备允许为空比较灵活。结果上线之后大量没有设备号的用户每个都可以插入一条记录因为MySQL里的唯一索引完全不拦多个NULL。应用层做判断的时候一查这个设备号是否存在返回没有然后业务就傻眼了。这个问题排查了整整半天根因就是唯一约束允许NULL的语义组合出了问题。所以我的经验是如果某个字段既要唯一、又要允许没有值那你得想清楚没有值用什么来表示。是空字符串还是特定的占位值在MySQL里空字符串和0是可以被唯一约束正常去重的NULL则不行。你完全可以用空字符串来表示未绑定让唯一约束真正生效。默认值的设计也一样有讲究。状态字段给默认值这是常识但要小心默认值本身会不会掩盖业务问题。比如状态默认为待审核结果业务代码忘了更新状态是不是就形成了看起来正常的脏数据时间字段建议直接默认当前时间降低应用层的负担。这里还要注意MySQL版本的差异MySQL 5.6.5之前DATETIME不能直接设置默认CURRENT_TIMESTAMP很多老项目不得不靠应用层塞时间。如果你还在维护老库查一下所有时间字段的默认值很可能会发现一片空白。2.3 唯一约束业务唯一性别全指望代码判断做后台系统的人大概率都写过类似的逻辑先查一下记录存不存在不存在才插入。这种先查后插的做法在并发量低的时候没问题一旦两个请求同时进来两个都查不到两个都插入成功唯一性就破了。正确解法是应用层校验数据库唯一约束双保险。数据库唯一约束是最后一道防线兜住并发场景下的漏网之鱼。比如用户手机号、订单号、支付流水号这些业务上必须唯一的字段不管应用层判断做得多好数据库层的唯一约束都该加上。唯一约束在带来安全性的同时也有代价每加一个唯一约束InnoDB就要多维护一棵B树写入时需要做唯一性检查在高并发下会有额外的锁开销。所以唯一约束不能乱加只加在业务语义上必须唯一的字段上。另外唯一约束创建的同时会自动创建一个唯一索引这个索引除了约束作用还能服务查询——比如订单号是唯一约束那按订单号查订单天然就走索引这就是一鱼两吃。2.4 CHECK约束新版MySQL终于支持了在MySQL 8.0.16之前CHECK约束是一个摆设——SQL可以写但MySQL不执行只解析后直接忽略。所以很多老开发根本不碰这个语法默认MySQL不支持CHECK。实际上从8.0.16开始MySQL已经完整支持CHECK约束的执行而且在数据写入时如果违反会直接报错。CHECK约束最常用的场景是枚举值校验。比如订单表的状态字段想限制只能存待支付、已支付、已取消、售后中这几类就可以用CHECK或者ENUM。用CHECK的好处是改约束比改ENUM灵活虽然MySQL修改CHECK同样需要重建表但语义上至少比ENUM直观坏处是CHECK只能做单行内的检查不能跨表、不能聚合。比如一个商品订单的总金额必须大于0这种单行检查它管得了一个订单的总金额必须等于所有明细之和这种跨行检查它就无能为力了。我个人对状态类字段的偏好是状态值很少且基本稳定时用ENUM可读性好开发一眼看得懂状态值未来可能扩展、或者需要跟代码里常量对得很齐时用TINYINT加字段注释。CHECK约束适合做数值范围检查比如年龄必须大于0、折扣必须在0到1之间这类规则定死在数据库层应用层哪怕手滑写错也拦得住。3. 外键约束用还是不用这是个问题3.1 外键的机制成本与锁风险外键约束是争议最大的一个约束大厂基本不用传统企业系统却很爱用。要理解这个分歧得先看清外键的成本。在InnoDB里外键约束不只是一个声明。当你要插入或更新一张子表时MySQL会去父表做一次引用检查这个检查会在父表的记录上加共享锁S锁。说白了每次写子表都牵扯到父表的行锁。如果父表正好有大批量更新或删除子表写入就会被堵住锁等待一多整个系统的吞吐量就下来了。几千万行的大表上这个问题会被放大得非常明显——你根本不敢在核心链路上放外键因为一次父表的批量更新就可能拖垮一串子表写入。还有运维层面的硬伤外键在DDL变更时特别碍事。想改父表结构得先看有没有外键引用想删父表数据还得担心子表会不会跟着出问题做分库分表、数据归档时外键直接让你寸步难行。MySQL的外键本身也不支持跨库跨实例一旦服务间拆分成微服务外键约束根本无从谈起。3.2 什么时候我必须用外键听到这里别急着把所有外键都删了。在特定场景下外键是真香。如果你的系统是单库单表、业务并发不高、数据可靠性要求反而很高比如内部工单系统、报销系统、小型CRM外键可以帮你挡住大量应用层漏洞。有人会忘记检查用户是否存在就插入订单有外键在数据库直接拒绝有人会写完子表忘写父表有外键在数据完整性有得兜。团队人少、中间层代码不够严谨的时候外键是一个廉价高效的守护者。使用外键时有一个需要小心的功能ON DELETE CASCADE。这个级联删除字面上看很贴心——删父表子表自动跟着删。实际操作中是灾难制造机。我有一次误删了父表的一批数据CASCADE几秒钟之内把关联的几十万条子表记录清得干干净净那种心凉的感觉至今记得。所以我的建议是即使使用外键引用关系上尽量只做约束、不做级联删除动作交由业务代码显式控制至少你在执行删操作前能看得见影响范围。3.3 没有外键怎么保证两张表的数据对得上互联网场景下放弃外键之后数据一致性不能纯靠祈祷。我在项目里常用的替代方案有四种。第一种应用层事务保证。在同一个数据库里一个事务里同时插入父表记录和子表记录靠本地事务保证原子性这是最基础的替代方案。如果涉及多个服务就要引入分布式事务方案或者本地消息表最终一致性。第二种对账补偿。交易系统里每天跑定时任务比对父表和子表的记录是否存在、状态是否匹配发现不一致的走告警或者补偿流程。这种方案不如外键及时但胜在灵活可靠。第三种写操作前置校验。插入子表前先查父表是否存在虽然在高并发下有先查后写的竞态问题但对于多数非核心场景已经够用了。第四种用唯一约束做软引用。比如子表里存了父表的业务编号就给这个编号建唯一约束如果业务上必须唯一同时确保两端编码规则、字符集一致。这样即使没有外键查询和关联的性能也有保障。热词里提到的账号匹配就是典型场景表A的银行账号要去匹配表B前提是表B的账号字段有个靠得住的唯一约束否则匹配出来一堆重复行数据就废了。4. 约束和索引、性能的那些事4.1 约束和索引是一回事吗这个问题的答案是一半一半。主键约束创建后InnoDB会基于主键列建立聚簇索引唯一约束创建后也会自动生成一棵唯一索引的B树。从这个角度说约束的物理形态就是索引。但约束和索引在语义上完全不是一回事。索引的目的只是加速查询它不限制数据的值约束的目的是限值数据的合法性只不过实现时借用了索引的结构。理解这一点对做性能分析很重要你会经常看到一条SQL明明走了某个唯一索引却不知道为什么这个索引会用不上或者为什么一个普通索引能造的查询速度比唯一索引慢——因为它们虽然长得很像用途完全不同。另外有个细节很多人不知道外键列在InnoDB里如果没有索引MySQL会尝试自动创建索引以保证外键检查时能快速定位父表记录。也就是说哪怕你只是声明了外键它都不只是约束还会悄悄改动表结构。这也提醒我们外键和索引在物理层面是纠缠在一起的删外键时如果留下一个自动生成的索引要注意判断它要不要顺手清理。4.2 辅助索引如何避免回表唯一约束的隐藏收益辅助索引如何避免回表这个热词近两年被问得特别多我在这里展开讲一讲因为它和约束设计直接相关。回表的概念是当查询走的是辅助索引二级索引时索引里只存了索引列的值和主键值所需要的其他字段并不在辅助索引上。MySQL要先找到主键再到聚簇索引里取整行数据这个过程就是回表多一次随机IO。避免回表的常见手段是覆盖索引也就是让查询需要的所有列都包含在索引里。这时候你之前建的唯一约束就能派上用场。比如你想快速判断一个用户是否买过某个商品查询条件是user_id和product_idSELECT需要的字段也只有这两个。如果表上已经有一个UNIQUE KEY(user_id, product_id)那这次查询直接在唯一索引里就拿到了全部需要的数据连回表都不用。既有唯一性的约束又有覆盖索引的加速效果这就是我说的一鱼两吃的典型场景。反过来说如果你给某个字段建了唯一约束但查询还要取其他字段那唯一索引只能帮到过滤取数据照样要回表。所以别迷信唯一约束能包治百病索引设计还是要根据查询模式来。4.3 几千万行大表上约束怎么设计不拖性能大表上的约束设计必须拿性能换安全所以每一处都要精打细算。主键自增仍然是最稳妥的选择因为顺序写入对大表的写入性能影响最大。如果因为未来的数据合并必须用分布式ID尽量选有序的雪花ID减少页分裂。给大表加多个唯一索引要极其谨慎每多一个唯一索引插入、更新时都要多维护一棵B树写入放大是实打实的。几千万行的表上每个唯一索引占用几个GB的空间不是开玩笑所以唯一约束只保留业务硬要求的那两三个。另一个关键则是约束和在线DDL的关系。给大表加约束不是随便执行一条ALTER TABLE就完事的。MySQL 8.0的在线DDL已经做得不错可以指定ALGORITHMINPLACE和LOCKNONE尽量不阻塞读写。但对几千万行的表即使加一个唯一索引也需要扫描全表建索引耗时可能几分钟到几十分钟期间的IO压力、主从延迟都不容忽视。稳妥的做法是放到业务低峰期执行先看执行计划评估影响必要时用gh-ost或pt-online-schema-change这类在线表结构变更工具处理。还要注意一点给大表加唯一约束之前数据里如果有重复值建约束会直接失败。正确顺序是先把重复数据清掉再加唯一索引。这个步骤看起来简单实际操作中往往会牵扯出上下游一堆系统因为去重不是删几条记录那么容易得先搞清楚业务上哪些记录该保留。5. 约束的运维管理建表只是开始5.1 后补约束的SQL与注意事项很多系统问题不是建表时埋下的而是上线后慢慢暴露才想着补约束。补约束是DBA和开发者的日常给几个最常用的SQL。-- 添加主键约束 ALTER TABLE t_order ADD PRIMARY KEY (id); -- 添加唯一约束 ALTER TABLE t_order ADD UNIQUE KEY uk_order_no (order_no); -- 添加外键约束 ALTER TABLE t_order_detail ADD CONSTRAINT fk_detail_order FOREIGN KEY (order_id) REFERENCES t_order(id); -- 添加CHECK约束MySQL 8.0.16 ALTER TABLE t_order ADD CONSTRAINT chk_amount CHECK (amount 0); -- 修改字段为非空 ALTER TABLE t_user MODIFY COLUMN mobile VARCHAR(20) NOT NULL;补约束和建表时写约束最大的区别在于存量数据。补唯一约束之前一定要先查重复SELECT mobile, COUNT(*) FROM t_user GROUP BY mobile HAVING COUNT(*) 1;查到重复数据后不是直接删除就行得先跟产品对清楚保留哪一条。这里我也建议先建普通索引让查询走索引加速去重等确认没有重复了再改成唯一索引这样对你操作大表时更安全。5.2 常见报错与排查实录约束相关的报错几乎每个做过MySQL的人都会遇到这里把最常见的几个列成速查表。报错信息可能原因排查方向Duplicate entry xxx for key uk_x唯一约束冲突查询该值已经存在的记录判断是重试还是人工处理Cannot add foreign key constraint类型不一致、字符集不一致、引用列没有索引、引用列非唯一检查两表字段类型、字符集、索引和唯一约束Data too long for column xxx隐式的长度约束被突破检查字符集编码和字段长度定义Check constraint xxx is violatedCHECK约束校验失败核对写入值的取值范围Incorrect table definition; there can be only one auto column自增列定义错误确认自增列必须是主键或者索引的一部分外键创建失败是我见过最多的一种报错根因经常是字符集对不上。父表用utf8mb4子表用utf8两个表的字段看上去类型一样但MySQL在外键检查时发现字符集不同直接拒绝。这种情况在导入导出、建表脚本复制时特别常见。排查外部键问题有一条实用方法论先查两表字段的类型、长度、字符集是否完全一致再看引用列有没有索引或者唯一约束最后看两表的存储引擎是否都是InnoDB。MyISAM不支持外键这也是老生常谈。5.3 约束、锁表与并发写入约束和锁的关系是运维排查中最容易踩坑的地方。唯一约束的存在意味着每次插入或更新都要做唯一性检查这个检查在并发场景下会涉及锁机制它不仅要锁住当前写入的记录还可能在索引相邻区间加锁gap lock防止其他事务插入范围冲突的数据。这就是为什么明明只是几条插入死锁日志里却能看出一堆锁等待。外键的锁风险前面已经讲过这里再补充一个场景批量跑任务时如果子表有外键且一次插入几万条每一条都会去父表做引用检查并加S锁。父表某一行恰好被另一个事务锁住时整个批量任务的吞吐量就瞬间掉到谷底。要排查当前有没有锁表冲突我常用的几个命令-- 查看当前正在执行的查询 SHOW FULL PROCESSLIST; -- 查看InnoDB锁等待MySQL 8.0 SELECT * FROM performance_schema.data_lock_waits; -- 查看锁相关统计 SELECT * FROM sys.innodb_lock_waits;排查到锁冲突后第一件事是确认事务是不是没及时提交。我见过太多次MySQL锁死的情况根因是应用代码里开了事务、处理完业务之后忘记commit连接就那样开着事务一直持锁不释放。这种情况下与其跟业务人员反复讨论SQL有没有问题不如先查一下长期未结束的事务。从设计上减少锁冲突我的习惯是写入顺序固定多个事务按同样的顺序操作表事务体量尽量小不把无关操作塞进一个事务REPLACE INTO和INSERT ... ON DUPLICATE KEY UPDATE这类依赖唯一约束的写入要特别注意并发下锁的竞争它们本质上都是靠唯一索引的锁机制来实现的——并发高时这些写法的死锁率反而比普通INSERT更高。6. 约束思维在其他场景里的延伸6.1 跨表合并、连接查询全靠约束兜底热词里有跨表合并vlookup跨表匹配left join多张表账号匹配这些操作碰上脏数据时有多痛苦做过的人都懂。跨表合并的本质是把两个来源的数据合到一张表里靠某些关键字段对齐。对齐的前提是什么这些关键字段在各自源表里必须可以唯一定位一条记录。换句话说如果没有唯一约束合并的结果要么多出一堆重复行要么张冠李戴把不同记录拼在一起。我见过有人在Excel里用VLOOKUP匹配银行账号VLOOKUP的经典bug是遇到重复值只返回第一个匹配项数据库里也一样——left join时ON字段有重复行结果是成倍膨胀。所以我的建议是涉及跨表合并、关联匹配的关键字段先确认源表的唯一性。源表是自己管理的就去加唯一约束源表不是自己管理的至少要加索引并在合并前做一次计数校验——两张表各自按关键字段去重后的记录数应该和目标表对得上。只有这样合并出来的数据才敢拿去出报表、做决策。6.2 表结构迁移到TDengine超级表子表里的约束逻辑近两年越来越多人把时序数据从MySQL迁到TDengine热词里mysql表结构自动转tdengine超级表子表被反复提及这里也聊两句约束在时序库里的重新理解。TDengine的核心建模方式是超级表加子表超级表定义Schema相当于模板子表通过标签tag区分不同实体比如一台设备、一个点位。每条时序记录的主键变成了时间戳这跟关系型数据库的主键逻辑完全不同。在TDengine里你几乎不用考虑唯一约束、外键约束这些问题因为它的写入模型天然是一个子表一个时间戳对应一条记录时序一致性靠子表和写入时间戳来保证。所以从MySQL迁到TDengine时不能照着原表结构照搬要做的是把原表里的业务标识比如设备ID、采集点编号转成tag把原表里的时间列指定为时间戳主键把需要存储的数值列作为普通字段。这个转换过程其实也是约束思维的延伸——换个平台约束表现形式不同但每条数据必须能被唯一识别这个底层逻辑没有变。6.3 没有备份的情况下误删表能恢复到什么程度热词里有一条生产库环境没有备份的情况下删除了某个用户的所有表如何恢复这是个让人后背发凉的问题。直接说结论没有备份恢复的难度陡增但也不是完全没有路。如果你开启了binlog且binlog_format是ROW模式可以从binlog里把删除事务找出来反向构造回滚SQL这条路径需要极其小心的操作和足够长的binlog保留时间。更惨的情况是连binlog都没配置或者已经被purge那基本只能靠文件系统层面的可能性了比如看还有没有老快照、磁盘上有没有残留的ibd文件碎片但全表恢复的把握微乎其微。从这个惨痛教训里我真正想强调的是备份意识。给核心库做全量备份加增量binlog备份是DBA的基本功给业务开发者的教训是删除数据这个动作一定要经过审批、要有影响面评估。另外要特别注意约束带来的连带删除如果有ON DELETE CASCADE的外键关系删一个表的数据可能会级联删掉好几个关联表。很多人以为删除用户就只是删除用户表结果订单、明细、日志全跟着没了。这种情况下约束本身也可能变成放大风险的工具使用时必须心里有数。7. 踩过的坑和我的习惯文章最后说几个我踩过的坑也是我现在做表结构设计时固定会做的事情。第一个坑是建表时图省事字段全用VARCHAR(255)唯一约束让加就加、不让加就不加。后来报表统计口径对不上排查了两天才发现是源表有重复记录。从那以后我给自己定了一条铁律任何能被当作关联键、合并键的字段先问一句它应不应该唯一该唯一就必须让数据库挡住重复而不是靠人肉保证。第二个坑是修改大表约束时直接在高峰期执行ALTER TABLE。加唯一索引时全表扫描把主库IO直接打满主从延迟飙到几百秒。现在我在生产环境做大表DDL前一定会看执行计划、评估影响、放到低峰期能走在线变更工具就尽量走工具。这个教训很贵花钱买的。第三个坑是关于默认值的。有一个老系统的字段用了0表示未设置后来需求变更说0也是有效状态结果一堆老数据和新逻辑全碰撞在一起。从那以后我设计状态字段时默认值一定会选明确的初始状态而不是一个看起来无害的数字。约束条件不只是限制别人也是在提醒未来的自己——当初为什么这么定义。最后分享一个实用小习惯每建一张新表我都会在最后把约束清单单独写一段注释放在建表语句里说明每个约束的意图。看起来有点啰嗦但半年后回来说这张表的时候你会非常感谢当初的自己。约束条件的核心价值从来不是让数据库更复杂而是让数据在混乱的业务里依旧值得信赖。