关系数据库设计优化示例及最佳实践总结

同事设计的数据表结构

我们需要实现的是大文件的切片上传功能，下面是同事设计的两个相关的MySQL数据表，一个用来记录文件信息，一个用来记录文件的分片信息：

表一：ext_big_file

说明：updated_at字段是设置了自动更新的，下同。

表二：ext_big_file_part

关于这两个表

先说说比较好的地方（只针对上截图看得见的，其他如索引不在考虑范围内）：

• 表名有统一的前缀；
• 表名字段名命名方式统一，都是使用下划线分隔，这点看着很简单，但是见过不少人就有意无意地混用多种命名方式，又驼峰又下划线的；
• 注释相对比较完善，状态类字段在注释也有说明每个值是什么意思。其实字段注释都写不明白的人，基本上是对业务不理解的，自己可能都想不明白为什么要这样的字段，字段为什么要这样设计；
• 没有滥用varchar类型，不少人在设计的时候，碰到字符串就varchar，不经大脑，明明定长字符串，不用char搞啥飞机；
• 没有滥用int类型，除了varchar，int也是经常被滥用的类型，看见整数就设置成了int，其实可能tinyint/smallint是更适合的类型；
• 每个表都有设置主键；

再说说不好的地方：

• 很多字段都设置了允许空值，这并不是一个好的设计，我们应该尽量避免允许空值的情况出现；
• 字段默认值和字段类型没有匹配，主要是默认值大都偷懒设置成了NULL，默认值的类型最好是和字段类型一致的，例如如果是字符串类型，那默认值可以设置为空字符串，如果是整型则默认值可以设置为0（根据实际情况而定，有时可能设置为-1更合适），如果是datetime类型默认值可以设置为当前时间，或者“1970-01-01 00:00:00”等；
• 整型的无符号属性没有考虑，我们使用整型的时候，应该要明白业务上这个值是否会出现负数，如果明确不会的，那就应该加上无符号属性；

优化后的表结构

从表结构看，主要改动如下：

• 字段全部设置为非空；
• 默认值跟字段类型保持一致；
• 把非负整型的字段增加无符号属性；
• 增加了一些业务上需要的字段；

还有一个比较大的改动，从这两个表是看不出来的，把上传信息从大文件信息表ext_big_file分离了出来，同一个大文件应该可以被上传多次，这时的上传信息有多条，但是对应大文件记录只有一条。

最佳实践总结

我们设计数据库时，应该考虑以下几点来确保最佳的设计：

• 最匹配原则：字段类型及属性应该是要和业务最匹配的，例如状态值通常是无符号的tinyint，定长字符串使用的应该是char（长度应该也是匹配的），整型除了int，还有tinying/smallint/bigint等（当然还有三个字节的整型，只是比较少用）可以考虑，varchar长度也不应该全设置为255，应该理解业务再设置；
• 统一使用下划线命名方式：不是说驼峰不好，只是驼峰确实不适合数据库设计；
• 表名务必使用同一前缀；
• 尽量避免字段允许空值：在数据库设计中，空值通常表示缺失或不确定的数据，如果字段在写入的时候可能没有值，则设置相匹配的默认值，这样就可以避免使用空值；
• 默认值的类型应该和字段类型是一致的：
  • 字符串类型，那默认值可以设置为空字符串；
  • 整型，则默认值可以设置为0（根据实际情况而定，有时可能设置为-1更合适）；
  • datetime类型，默认值可以设置为当前时间，或者“1970-01-01 00:00:00”
• 注释要清晰简洁：注释是要写的，而且要写好，这非常重要。通常应该包含以下要素：
  • 中文字段名；
  • 如果是状态值等枚举类型，要列出每个值及其说明；
  • 时间类字段通常应该说明应该什么时候更新该字段；
  • 一些关联字段应该说明关联的是哪个表的哪个字段；
  • 一些字段比较复杂或者特别，应该说明该字段的值是怎么生成的，用在什么场景下，有什么作用等；
  • 关联字段应该说明，例如错误信息字段通常和状态字段的某个值关联；
• 通常不要使用外键设计；
• 每个表都应该要有主键；
• 设计前先画ER图：这个通常很关键，实体关系如果都理不清楚，很可能会变成一团乱麻；
• 合理安排索引：这个也是设计数据库时要重要考虑的，例如做文件识别任务的时候，文件信息表应该跟任务表分离等。

其实，所有设计的前提和关键都是：理解业务。脱离业务的设计就是瞎扯，表设计不是把字段设计出来就可以了，而是要理解业务的逻辑。