MySQL表设计实战：数据类型与完整性约束的避坑指南-育师

2. 为什么数据类型选择会决定你的表建得好不好

提到MySQL,很多新手第一反应是装库、建表、写SQL,把数据类型当成填空题,给字符串就VARCHAR,给数字就INT,能用就行。但等你真的在线上跑过几年,经历过几次因为类型选错导致的慢查询、磁盘暴涨、数据被截断、锁等待超时,就会明白:数据类型不是一个格式选项,而是决定整张表性能、存储成本和一致性的地基。

完整性约束也一样,它不是用来给数据库"加规矩"的形式主义,而是在最底层帮你挡住脏数据、失误操作和业务逻辑漏洞的防线。我自己带过不少新人了,发现一个普遍的规律:建表的时候图省事,后面就要花十倍的精力还债。

这篇文章不谈虚的,只聊我实际用MySQL建表、调优、踩坑时总结出来的经验。核心围绕三个问题展开:数据类型的选型逻辑到底是什么?完整性约束怎么设计才不拖累性能、又能真正管住数据?以及,线上常见的类型和约束相关的故障,怎么提前避开。

适合谁看?正在学数据库原理的学生、刚接触后端开发的工程师、以及想系统梳理一下MySQL设计规范的开发者。如果你是DBA或资深后端,也可以把文中的案例当个参考,权当交流。

3. 数据类型选型:不只是"够用",而是"刚好"

3.1 整数类型:看着简单,实则处处是坑

MySQL提供TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT这五种整数类型,区别就一个字:存储字节数和表示范围。但你千万别小看这个差异,选错一个,轻则浪费空间,重则数据溢出直接报错。

我用一个表来表达,大家看得更清楚:

类型	字节数	有符号范围	无符号范围	适用场景
TINYINT	1	-128 ~ 127	0 ~ 255	状态码、开关量、小枚举
SMALLINT	2	-32768 ~ 32767	0 ~ 65535	端口号、小型计数器
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215	中量级积分、楼层数
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295	主键、常规计数
BIGINT	8	天文数字	更大	雪花ID、流水号、金额分值为单位的累计

这里有个很典型的坑:INT(11)和INT(3)的区别。很多老教材上说INT(11)表示最大长度,其实MySQL里的显示宽度(N)只影响某些客户端工具展示的补零效果,完全不限制存储的数值大小。也就是说你写INT(1)也能装下 2147483647。这个误区害了不少人,在面试里也经常被拿来当考察点。实际工作中,除非你用了ZEROFILL并且需要固定位数的展示(比如工号、编号),否则我一般主张统一INT不加显示宽度,免得误导后来维护的同事。

另一个坑是主键选型。很多业务表主键用的是INT自增,这在单机小业务没问题,但只要涉及到分库分表、多区域数据合并,INT就很容易溢出。我经历过一次真实事故:一个做终端设备ID的表,业务增长比预估快得多,主键冲到 21 亿,直接报警。当时还好提前做了监控,在溢出前紧急停机改表结构,用了在线DDL工具花了几个小时才把主键从INT改成BIGINT。那次之后,我给自己定了一条铁律:凡是可能长期增长的核心表主键,一律用BIGINT,不要吝啬那4个字节的存储空间。一条记录多4字节,一亿条才多400MB,换来的是好多年的安心。

3.2 浮点数与定点数:金额字段千万别用 FLOAT

浮点类型FLOAT和DOUBLE在MySQL里本质是近似值存储,基于IEEE 754标准。这意味着你在代码里写出0.1 + 0.2等于0.30000000000000004,在数据库里存 9.99 元,读出来可能变成 9.9899999999999998。如果你用FLOAT存价格、余额、利率这类对精度敏感的数据,早晚会对不上账。

我给大家推荐一个非常简单的原则:凡是金钱、费率、折扣等需要精确匹配的数据,一律用DECIMAL。DECIMAL(p, s)中的p是总位数,s是小数点后位数,比如DECIMAL(10, 2)表示整数部分最多8位、小数2位,能存储的最大金额是 99999999.99。有人问为什么不直接用BIGINT存“分”来解决?这当然也行,而且性能极好,但问题是业务代码里需要所有人都牢记乘以100和除以100的转换,但凡有一个人忘了一次,就会埋下计算错误的雷。相比之下,DECIMAL虽然在计算性能和存储空间上略有劣势,但它把精度问题交给数据库,业务代码可读性高得多,维护成本低。很多金融级系统,表中甚至用DECIMAL(16, 4)甚至更高的小数位来存中间计算过程,最后展示的时候再按需四舍五入。

浮点数也不是完全不能用,像温度传感器数据、百分比估算值、GPS坐标这类本身就不需要精确到某一个固定小数位的,用FLOAT/DOUBLE也可以接受。判断的标准就是:这个值是否允许误差?是否会被用于等值查询或者累加?如果是,老老实实上DECIMAL。

3.3 字符串类型:VARCHAR和CHAR不是差一个字母那么简单

字符串类型里最常用的就是CHAR和VARCHAR。先说结论,大多数情况下我用VARCHAR,但CHAR在特定场景有它的价值。

CHAR(N)是定长,不足的部分会用空格填充。它的好处是没有额外的长度前缀,存储结构紧凑,CPU计算快;坏处是如果内容长短差异大,空间浪费严重。适合存储固定长度的编码,比如身份证号(老款18位)、手机号(11位)、MD5签名(32位)、订单号(固定长度生成的编号)。

VARCHAR(N)是变长,额外会用1到2个字节记录实际长度。它按实际内容占用存储,所以更省空间,但更新时如果数据变长,可能触发页分裂,产生碎片。而且注意,VARCHAR(N)中的N在MySQL 4.0 之前按字节计数,在 4.1 之后默认按字符计数。这个变化导致了不少历史遗留问题:老项目里字段定义的是VARCHAR(20),存的英文字符能20个,但换成中文就只能存6个左右(如果还是按字节场景)。现在新版本基本都是按字符个数,所以VARCHAR(20)可以放20个汉字。不过依然要提醒大家:不同字符集下,同一字符串占用的字节数不一样。utf8mb4下一个汉字最多占4字节,utf8下一个汉字最多占3字节。所以计算VARCHAR能达到的最大字节长度时,千万别忘了字符集的因素。utf8mb4下VARCHAR(255)最多占1020字节,已经接近某些索引前缀的长度上限了,建索引的时候要小心。

还有一个容易忽略的是VARCHAR长度上限。在旧版MySQL(5.6等),VARCHAR的最大行长度受65535字节限制,所以如果你定义VARCHAR(65535),肯定报错,因为每行还要预留其他字段和开销的字节。我见过有人建了个VARCHAR(20000)字段,以为能存两万字符,结果被MySQL拒绝,一脸疑惑。设计大数据量文本场景,优先选TEXT类型,注意TEXT不能有默认值,也不能直接建完整索引(需要前缀索引)。

TEXT和BLOB家族也是字符串绕不开的。TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT分别对应255字节、64KB、16MB、4GB。一般文章正文、JSON配置、长日志这类内容,TEXT或者MEDIUMTEXT就够用了。有一点必须提醒:TEXT类型的存储方式在InnoDB里比较特殊,如果数据量不大通常存在行内,数据大了会溢出到单独的页存储。所以如果你在一个带着TEXT字段的表上做频繁的大范围查询,SELECT *会额外读数据页,IO开销上升明显。优化技巧是这个表只保留主键和业务必须的短字段,TEXT大字段拆到附属表,需要时再按主键去查。

3.4 日期时间类型:DATETIME和TIMESTAMP的相爱相杀

日期时间是另一个高频踩坑区。DATETIME和TIMESTAMP都能表示日期+时间,但差别很大:TIMESTAMP存储范围是1970年至2038年,占用4字节,它跟时区有关,存储时会把当前会话时区转换成UTC,展示时再次转换回会话时区。DATETIME范围从1000年到9999年,占用8字节(5.6.4之后支持小数秒时占用更多),与时区无关,存什么就是什么。

我强烈建议:核心业务表时间字段直接用DATETIME,不要纠结那4字节的差距。为什么?因为绝大多数业务系统都跑在一个统一时区,而且数据要实现跨区域同步的话,DATETIME配合应用层传入的UTC时间更可控。如果用TIMESTAMP,一旦服务器的时区配置被改掉,所有历史时间展示都会错位。你可能说DBA不会乱改时区,但容器化时代,实例迁移、环境重建都可能把时区配置弄丢,到时候线上数据全部乱掉,排查成本极高。这个我吃过亏,所以主张选DATETIME。

另外,强烈建议每个表统一建created_at和updated_at两个字段,类型DATETIME,并且结合默认值精确到秒。MySQL 5.6 之后支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP,可以很方便地在插入和更新时自动维护时间。这里有个细节:如果你用了DATETIME(3),时间精度到毫秒,会占用额外存储,但也能避免同一秒内多条记录的排序错乱问题。

关于时间字段的索引,还有个经验:不要直接在updated_at上做范围查询后再排序,除非你建了联合索引。另外,时间字段的查询一定要配合索引才能高效,否则全表扫描是肯定的。

4. 完整性约束:数据库的"质量守门员"

4.1 主键必知:自增、UUID、雪花ID到底选哪个

主键约束是唯一标识一行记录的字段或者字段组合。最常见的方案是AUTO_INCREMENT自增主键,优点是插入性能好、索引紧凑、页分裂少、排序友好,缺点是分布式和分片场景容易冲突,且ID可被猜测(他人可通过自增ID推断业务量)。如果做分库分表,可以用全局发号器或者雪花算法生成ID。

雪花ID通常是BIGINT类型,由时间戳+机器ID+序列号组成。选雪花ID时要注意:不要在应用层每次插入都新生成一个完之后直接丢给数据库,而应该在插入语句里显式传入ID,或者用支持批量插入的框架。由于雪花ID是分布式生成的,它的插入顺序近似但不完全等于时间顺序,所以在InnoDB索引结构下可能造成随机插入,产生页分裂。缓解的办法是把BINARY(16)、UUID短化,或者使用键值表做局部有序,当然这取决于你的实际规模,不必过度设计。

无论用自增还是雪花,主键要有几个铁律:

不允许NULL,且必须唯一;
尽量单调或者近似有序,利于聚簇索引顺序写;
键值长度尽量小,因为InnoDB的二级索引叶子节点会存储主键值,主键越大,二级索引文件越大,查询时IO越多;
不要用业务字段做主键,比如手机号、身份证号。业务字段有变更的可能,一旦变更,所有引用它的外键、索引都要改,代价大。

4.2 外键约束:我为什么建议你用,但也建议你慎用

外键约束能保证两张表之间的引用完整性。比如订单表的user_id引用用户表的id,外键约束确保你无法插入一个不存在的用户ID,也无法在存在订单的情况下随意删除用户。

但是,在高并发、大流量、分库分表的场景下,外键几乎不可用。原因很简单:InnoDB在插入、更新、删除时需要对外键关联的父表加锁,锁范围会扩大,而且父表的每次变更都可能触发对子表的检查,这在分布式和高写入场景下造成严重的争用和不可控的锁等待。很多互联网大厂的核心交易库明确禁用外键,把引用完整性交给应用层去补偿和保障,用定时任务和消息队列对账。

我的取舍建议是这样:

中小项目、后台管理系统、内部工具,放心使用外键,它能挡住那些业务代码里漏掉的脏数据。
大流量系统、核心交易链路、涉及分库分表的场景,不用外键,但要在应用层代码里显式做存在性校验,并且设计对账任务。
是否用外键,在建表阶段就要定,不要上线之后再补,因为补外键需要对已有数据做全量校验,极其耗时,还可能造成锁表。

外键还有一个隐藏问题:它会让你的删除操作变得异常危险。如果你对一个被大量子表引用的父表执行DELETE,MySQL会逐个检查子表约束,速度很慢,还可能触发级联删除,把不该删的数据一起删掉。如果确定不想要外键的强约束,又怕遗漏孤儿数据,可以写定时SQL定期扫描子表中不存在的引用ID,把它记入告警队列。

4.3 唯一约束、非空约束、默认值、检查约束:细节决定稳定性

这一组约束虽然单看都很简单,组合起来却能发挥很大作用。

唯一约束的价值不用多说,但要区分它与普通索引的区别:唯一约束不仅提升查询性能,还能阻止重复值的产生。如果你是在业务逻辑里先查再插来防重,那在高并发下就会出现竞态条件,两个线程都查到"不存在",然后同时插入成功。所以,业务上的唯一性需求,尽量在数据库层用唯一索引兜底,这是我最推荐的"底线防御"。

非空约束不是为了给开发添麻烦,而是为了避免在代码里写一大堆NULL判断。回想一下查了多少次"为什么这行数据是空的"问题,十有八九是因为当初建表时没加NOT NULL,然后某个接口传入空值,或者某个更新操作没覆盖该字段。把字段设计成NOT NULL DEFAULT xxx是最省心的做法。对于实在允许空值的字段,也要明确NULL的业务含义——它到底是"无"还是"未知"?这两者对查询语句的影响完全不同,比如WHERE col != 1是永远查不出NULL的行。这是一个SQL初学者都会犯的错,在真实数据中排查起来特别隐蔽。

默认值约束同样体现了业务逻辑下沉的思想。例如状态字段status TINYINT NOT NULL DEFAULT 1,这样插入时不需要显式传值,减少代码遗漏。再比如created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,可以保证每一条数据都有准确的落库时间。

检查约束是MySQL 8.0.16 之后才真正强制生效的功能。之前版本里CHECK是能被解析但被忽略的摆设,很多人不知道这一点。8.0.16 之后,它真正可以用,比如限定订单状态status必须在枚举集合里、库存数量quantity必须大于0。建议使用场景:简单的、纯数据层面的规则,用CHECK挡住;复杂的、跨表的、需要查询其他表才能判断的规则,不要用CHECK(它本来也无法实现),放到存储过程或者应用层去判断。

5. 实操案例:从零设计一张订单表

5.1 需求与字段规划

为了把上面这些原则串起来,我用一个典型的电商订单表作为示例。假设业务是一个B2C商城,订单包含用户、商品快照、金额、状态、收货信息等。

创建表之前先做字段清单评审:

主键:订单号,考虑到数据量大和后续分库分表可能,采用BIGINT类型的雪花ID,不用自增。
用户ID:BIGINT,需要给用户加索引。
订单状态:TINYINT,0=已创建、1=已支付、2=已发货、3=已完成、4=已取消、5=售后中。
商品快照:因为订单要保存下单时的商品信息(价格、名称、图片),不适合直接引用商品表(商品可能改价、改名),所以冗余一份JSON或独立快照表。这里我选择把核心快照字段拆到订单项子表,主表保留结算金额。
金额:必须DECIMAL(12, 2)。总金额、优惠金额、运费分开存,避免每次都要算。
时间字段:created_at、paid_at、shipped_at、finished_at都用DATETIME,前两个不允许为空。
收货信息:手机号、姓名、地址。手机号存VARCHAR(20),因为可能存在国际号码;地址用VARCHAR(255)。

5.2 建表SQL与约束设计

下面给出一段可直接参考的建表语句,重点是看约束和索引的搭配:

CREATE TABLE `order_main` ( `order_id` BIGINT NOT NULL COMMENT '订单ID,雪花算法生成', `user_id` BIGINT NOT NULL COMMENT '下单用户ID', `order_status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态:0已创建,1已支付,2已发货,3已完成,4已取消,5售后中', `total_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '订单商品总金额', `discount_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额', `freight_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '运费', `pay_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '实付金额', `receiver_name` VARCHAR(50) NOT NULL COMMENT '收货人姓名', `receiver_phone` VARCHAR(20) NOT NULL COMMENT '收货人手机号', `receiver_address` VARCHAR(255) NOT NULL COMMENT '收货地址', `remark` VARCHAR(500) DEFAULT NULL COMMENT '订单备注', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `paid_at` DATETIME DEFAULT NULL COMMENT '支付时间', `shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间', `finished_at` DATETIME DEFAULT NULL COMMENT '完成时间', PRIMARY KEY (`order_id`), KEY `idx_user_created` (`user_id`, `created_at`), KEY `idx_status_created` (`order_status`, `created_at`), CONSTRAINT `ck_order_status` CHECK (`order_status` BETWEEN 0 AND 5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单主表';

说几个关键设计的考虑:

第一,联合索引的创建是有讲究的。idx_user_created(user_id, created_at)是为了支持"查某个用户最近的订单"这类高频查询;idx_status_created(order_status, created_at)是为了后台运营按状态筛选,并兼顾按时间排序。如果只建单列索引user_id和created_at,查询时可能只能利用一个索引,另一个条件在回表后过滤,效率低不少。这里就是典型的"索引设计跟着查询模式走"。

第二,CHECK约束在订单状态字段上是纯数据层面的规则,可靠且低成本。注意美团等一些公司不用CHECK而用枚举字符串,但字符串占存储多且维护烦琐,不如TINYINT配合注释直观。

第三,金额字段所有都精确到分,DECIMAL(12, 2)的最大容量是百万亿级别,对绝大多数电商足够了。如果你预估订单金额可能超过这个数量级,再调大前面的位数即可。注意,DECIMAL的精度位数不建议太高,因为每增加9位数字,存储会增加4字节,而且运算开销上升。能存得下业务最大值加上一个合理的增长余量就够了,没必要动辄DECIMAL(30, 10)。

5.3 订单项子表:快照与冗余的正确姿势

订单项表保留每件商品的快照信息:

CREATE TABLE `order_item_snapshot` ( `item_id` BIGINT NOT NULL COMMENT '订单项ID', `order_id` BIGINT NOT NULL COMMENT '订单ID', `product_id` BIGINT NOT NULL COMMENT '商品ID', `product_name` VARCHAR(200) NOT NULL COMMENT '商品名称快照', `product_image` VARCHAR(500) DEFAULT NULL COMMENT '商品图片快照', `price_snapshot` DECIMAL(12, 2) NOT NULL COMMENT '下单时单价', `quantity` INT NOT NULL DEFAULT 1 COMMENT '购买数量', `subtotal_amount` DECIMAL(12, 2) NOT NULL COMMENT '小计金额', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`item_id`), KEY `idx_order_id` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品快照表';

这里有两个值得展开的点:

为什么不直接关联商品表?因为电商里的商品价格、名称都可能随时间变化,如果订单明细只存商品ID,以后订单列表要展示当时的商品名称和价格,就得回溯历史数据,代价高且不准。所以商品快照是必须的。你存的product_name和price_snapshot虽然看着冗余,但它们保障了订单数据的"历史上确定性"。

关于冗余的原则:在OLTP系统里,适当冗余换取查询性能和业务正确性是非常常见的。不要一听到"范式"就觉得要把表拆得七零八落。合理的冗余字段要遵循"来源稳定、变更可控"的原则。比如商品快照就是下单那一刻的稳定值,冗余后不随源数据变化而变;反过来,如果你在订单表里冗余用户的当前等级,而这个等级后续会变,那么你的订单历史数据就会显示新的等级,这就是设计问题了。

6. 常见问题诊断与排查技巧

6.1 明明加了对索引,查询还是特别慢

这个问题几乎每周都能遇到。多数情况是:索引建了,但SQL写法导致索引失效。看几个高频场景:

-- 错误示例1:对索引列使用函数 SELECT * FROM order_main WHERE DATE(created_at) = '2024-01-01'; -- 正确做法:改成范围条件 SELECT * FROM order_main WHERE created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00';

-- 错误示例2:隐式类型转换 SELECT * FROM order_main WHERE order_id = '123456789012345678'; -- 说明:如果字符串与BIGINT比较,MySQL会把字符串转成数字再比较,可能导致索引列上有隐式转换,索引失效

-- 错误示例3:前导模糊查询 SELECT * FROM order_main WHERE product_name LIKE '%手机%'; -- 这种查询无法使用B-Tree索引优化,只能全表扫描。如果需要这种搜索,用全文索引或者搜索引擎。

排查方法:先EXPLAIN看执行计划,重点看type列。如果出现ALL或者INDEX,说明走了全表或全索引扫描;ref和range是正常的范围查找;const是主键等值查找,非常快。再看Extra里的Using filesort/Using temporary,如果出现了,说明排序或去重没有利用索引,可能需要调整索引顺序。

6.2 数据被截断、溢出,原因到底出在哪里

这类问题多发生在从应用层写入时,比如:

往VARCHAR(20)写入25个字符,MySQL在非严格模式下会自动截断,严格模式下报Data too long。
往TINYINT写入200,严格模式下报Out of range value。
写入'10.999'到DECIMAL(10,2),MySQL会四舍五入成11.00,严格模式同样可能告警或报错。

排查手段:连接数据库时,查一下sql_mode。

SELECT @@sql_mode;

如果包含STRICT_TRANS_TABLES,MySQL会严格执行类型和长度检查;如果不包含,很多错误会被降级为警告,数据被静默修改或截断。生产环境强烈建议开启严格模式,宁可写入报错,也不要静默丢数据。我发现很多线上脏数据问题,都是因为某台测试机的sql_mode被改成了非严格模式,数据导入生产后直接乱掉。

另外,InnoDB对超长行有预期管理,如果你把大量VARCHAR定义过大(比如每个字段都VARCHAR(1000)),可能超过单行大小限制,报Row size too large。解决方案就是拆字段、用TEXT,或者拆分表。

6.3 外键导致的死锁和锁等待

外键带来的锁问题很隐蔽。A表是父表,B表是子表,存在外键关系。当两个事务并发执行时,顺序不当就会死锁。比如事务1先插入B表(需要校验外键,对A表加S锁),事务2同时删除A表中的某行(需要加X锁),S锁和X锁冲突,可能引发锁等待或死锁。

排查死锁有两种方法:一是直接看MySQL错误日志,里面会有InnoDB: transactions deadlock detected的信息,包含事务的SQL和持锁明细;二是使用SHOW ENGINE INNODB STATUS\G查看当前的锁信息和事务等待关系。遇到这类问题,根治方案就是在高并发链路中去掉外键约束,改为应用层校验。实在要去外键又担心一致性,可以在应用层用事务里先SELECT ... FOR UPDATE锁定父表行,再做子表插入,不过这还是要看业务并发量,别一刀切。

6.4 字符集混乱,页面和数据乱码

乱码问题虽然老生常谈,但每年都能碰到。核心是保持三个统一:数据库实例字符集、表字符集、连接字符集。在JDBC连接串里显式指定characterEncoding=utf8mb4(驱动版本不同写法略有差异),在MySQL配置文件里设置character-set-server=utf8mb4,在创建库表时显式声明DEFAULT CHARSET=utf8mb4。

另外注意,utf8在MySQL里不是真正完整的UTF-8,它只能存储最多3字节的字符,像emoji表情、某些生僻汉字(4字节)就存不进去,会报Incorrect string value错误。所以任何新项目,直接用utf8mb4,这是血泪教训。utf8mb4_0900_ai_ci是8.0的默认排序规则,兼容性、排序性能都更好。要支持中文按拼音排序,用utf8mb4_zh_0900_as_cs或者考虑业务层排序。

7. 设计与开发习惯:长效稳定的基石

在建表这个环节,补充几个我自己坚持了很久的习惯:

第一,每个表都要写COMMENT。字段级别的注释尤其重要,因为半年后你自己都会忘记status=3到底代表什么。一张表几十个字段,如果没有注释,新接手的人全靠猜,成本极高。用一句话讲明白这个字段的取值含义和业务口径,这是对团队最大的友善。

第二,变更字段尽量用在线DDL。MySQL 8.0 支持ALGORITHM=INPLACE的很多操作,比如增加索引、添加字段(某些条件下)可以在线完成,不会长时间锁表。但在5.7及更早版本,很多操作会锁表,对大表影响很大。如果有条件,提前用Percona Toolkit的pt-online-schema-change这类工具做变更,比我当年直接ALTER TABLE导致生产卡了好几秒要稳妥太多。

第三,核心表加数据版本字段version(通常用INT,默认0),在做乐观锁更新时用UPDATE ... SET version = version + 1 WHERE version = ?,可以防止并发更新时的互相覆盖。这个字段本身也是一个完整性约束的最佳实践,虽然它不属于MySQL内置约束,却是应用层并发控制里最通用的约束之一。

第四,规范和边界要想清楚。每个表都放created_at和updated_at,但不要在事务里手工更新它们;让数据库自动维护,避免应用层时间不一致。这个看着是小事,年份久了,分析数据时会发现有的记录创建时间比更新时间还晚,那就是当初手改导致的,数据审计时非常痛苦。