优雅永不过时：数据库表设计的 12 个关键点

数据库表设计是一项系统工程,设计的好坏直接影响到后续业务功能的实现难度和系统性能的发挥程度。俗话说"工欲善其事,必先利其器",只有打好数据基础,才能为上层建筑提供有力支撑。然而要想设计出优雅、高效且可扩展的表结构却并非易事,需要考虑诸多方面的因素。笔者结合多年的开发经验,总结了以下几点心得体会,希望能给大家一些启发。

1. 命名要规范。表名、字段名和索引名的命名规范是数据库设计的第一步。名称要能见名知意,言简意赅,不宜过长。比如用户表可以命名为 user,而不是 u 或者 user_info_detail。多个单词间建议使用下划线分隔,统一使用全小写格式,如 order_detail。每个业务模块可使用统一前缀,如订单相关的表都以 order_为前缀。时间字段可统一使用_time 结尾,如 create_time。主键索引以 pk_开头,普通索引以 idx_开头,唯一索引以 uk_开头。条理清晰的命名规范是提高数据库可读性的基础。

2. 合理选择字段类型和长度。字段的类型和长度要根据实际业务需求来选择,不能一味追求大而全。如用户名可以使用 varchar(32),评论内容可以使用 varchar(512)。针对固定长度的内容,要优先考虑使用 char 型。日期时间毫秒精度可使用 bigint 存 Unix 时间戳。选择恰当的类型和长度,可以节省存储空间,提高查询效率。

3. 谨慎增加字段。表中字段数目并非越多越好,建议单表字段数不超过 20 个。如字段过多时要考虑垂直拆分,将不常用的字段拆到扩展表中。对于一些可选字段,如用户的昵称、头像等,更建议单独建立一张用户扩展信息表。必要时也可以通过冗余字段来提高查询效率,如在订单表中冗余用户名称。在充分考虑实际需求的前提下,尽可能保持表的字段数精简。

4. 主键不可或缺。每张表都必须有主键,并且要尽量使用单一主键。因为主键查询效率最高,是天然的聚簇索引。常见的主键设置方式有以下几种:自增长型(auto_increment)、全局唯一 ID(UUID)、雪花算法(SnowFlake)等。尽量不要使用联合主键,因为其不仅占用更多空间,还会影响查询性能。另外主键要避免存储业务相关的值,如学号、员工号等,以便日后业务变动时更易调整。

5. 常用字段要建索引。合理的索引设计可以大幅提升数据查询效率。但索引并非越多越好,因其会占用额外的存储空间,并影响写操作的效率。单表索引数建议控制在 5 个以内。针对区分度不高的字段如性别,不建议单独创建索引。针对多个查询条件,可通过建立联合索引来减少索引数量。联合索引还需要考虑字段顺序,遵循最左前缀原则。必要时还可针对字符串采用前缀索引等高级索引技术。

6. 外键须谨慎。虽然外键可以保证数据的参照完整性,但其会影响删除和更新效率。互联网业务中对性能要求较高,且数据库外键与程序逻辑之间很容易产生耦合。因此实际项目中往往在程序层面来保证数据的一致性,很少使用外键这种强约束。同理像触发器、存储过程等也要尽量避免使用。

7. NULL 值要明确。建表时就要明确每个字段是否可空。对可为空的字段一定要在创建表时显式指定为 NULL,否则字段将隐式地被指定为 NOT NULL。选择 NULL 还是 NOT NULL 要根据实际的业务语义。比如用户的邮箱字段可以为 NULL,但性别字段可能就不允许为 NULL 值。NOT NULL 字段在保存时可以少一个字节的存储空间,且 NULL 值会导致索引失效。因此除非有很明确的业务需求,否则尽量将字段都设置为 NOT NULL。

8. 用好 JSON。MySQL 5.7 引入了原生 JSON 数据类型的支持,为我们存储非结构化数据提供了便利。比如商品的规格参数千变万化,就可以直接以 JSON 格式存入数据库,避免动辄增删字段的麻烦。JSON 字段的数据本身虽然不能建索引,但其内部的字段是可以建索引的。相比将数据拆表存储,使用 JSON 不仅可读性更好,而且对开发也更友好。

9. 大字段当心。在表中存储大字段会占用大量的存储空间,也会影响查询和备份的效率。因此图片、合同等大数据建议存到文件服务器或对象存储,数据库中只保存其地址信息即可。如产品的详情页内容可以存为 HTML 文件,数据库只存储 URL。即便必须存储在数据库中,也要将其拆分为独立的扩展表,千万不要和主表存在一起。

10. 冷热分离。对于一些历史归档数据,如過期的订单、日志等,可以定期从主表中移出,存入单独的历史表。历史表可以采用分区、分表等技术,并适当降低其维护成本。冷热数据分离不仅可以控制主表的数据量,还能提高线上查询的效率。冷热表还可根据各自的特点来设置不同的存储介质、备份策略等。

11. 注释要完善。表和字段都要添加清晰、准确的注释说明,这是软件工程的基本要求。注释要用通俗易懂的语言描述其业务含义,避免使用拼音、拼音首字母缩写等。如果某个字段存在枚举值,一定要将每个值所代表的含义都列出来。注释要与时俱进,代码有改动时要同步更新注释,否则就是无效注释。完善的注释是保证系统可维护性的重要一环。

12. 同类字段尽量统一。设计数据库表时要站在整个系统的高度,对每张表、每个字段都要有全局意识。同一类型的数据要采用同样的字段名和数据类型。如不同表中的时间字段都采用 datatime 类型,且以_time 结尾。用户 ID 都采用 user_id 的方式命名,且都定义为 int 型。这样不仅能提高系统的易读性,也有利于日后做数据分析和数据迁移。

总之,精心的数据库设计是软件系统成功的关键因素之一。每个字段、每张表、每个索引都要经过反复斟酌和权衡,才能设计出优雅、高效且灵活的数据库。设计之初多花一些心思,开发过程中就可以事半功倍。数据库设计是一门需要经验和练习的学问,同学们在实践中还需要多多总结提炼。

代码开发及数据库设计规范