【MySQL数据库开发设计规范】之表设计规范

欢迎点开这篇文章，自我介绍一下哈，本人姑苏老陈 ，是一名JAVA开发老兵。

本文收录于 《MySQL数据库开发设计规范》专栏中，该专栏主要分享一些关于MySQL数据库开发设计相关的技术规范文章，定期更新，欢迎关注，谢谢 :-）

一、前言

在我们对数据库技术方案设计的时候，我们是否有自己的设计理念或者原则，还是更多的依据自己的直觉去设计，是否曾经懊悔线上发生过的一次低级故障，可能稍微注意点就可以避免，是否想过怎么才能很好的避免，规范的价值正是我们工作的检查清单，需要我们不断从错误中积累有效经验来指导未来的工作。

本文介绍MySQL数据库开发设计规范之数据库表设计规范以及库设计规范。

PS：以下规范在大型互联网公司经过了充分的验证，尤其适用于并发量大、数据量大的业务场景。

二、表设计规范

1.【推荐】建表规范示例；

CREATE TABLE `student_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',
  `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分',
  `stu_num` int(11) NOT NULL COMMENT '学号',
  `stu_fee` decimal(8,2) DEFAULT '0.00' COMMENT '学费',
  `status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,
  KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='学生信息表;

说明：

A、表设计必须存在的三个字段分别是主键id、创建时间create_time、更新时间update_time；

B、关于日期时间字段，推荐使用datetime类型，其中datetime和timestamp都是用于表示日期和时间的数据类型，区别如下：

数据范围：
    datetime数据类型的有效范围是从1000年到9999年，精度为秒。
    timestamp数据类型的有效范围是从1970年到2038年，精度为秒。

存储空间：
    datetime数据类型需要8个字节的存储空间。
    timestamp数据类型需要4个字节的存储空间。

存储方式：
    datetime以固定的时区存储，不受时区设置的影响。
    timestamp以UTC时间（协调世界时）存储，并根据时区设置进行转换。

默认值和自动更新：
    datetime可以设置默认值和自动更新值，但不会记录变更的时间。
    timestamp可以设置默认值和自动更新值，并会记录变更的时间。

2.【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制；

说明：当使用了外键后，每次级联delete或update的时候,都要级联操作相关的外键表,不论有没有这个必要,由其在高并发的场景下,这会导致性能瓶颈，增加数据库压力，所以，不建议使用数据库的外键约束；

3.【强制】每个Innodb 表必须有一个主键id；
说明：Innodb 是一种索引组织表，其数据存储的逻辑顺序和索引的顺序是相同的。每张表可以有多个索引，但表的存储顺序只能有一种，Innodb 是按照主键索引的顺序来组织表的，因此不要使用更新频繁的列如UUID、MD5、HASH和字符串列作为主键，这些列无法保证数据的顺序增长，主键建议使用自增ID 值。

4.【推荐】单表列数目最好小于50；

说明：如果单表中列数目过多，对数据库性能影响比较大，而且也不利于管理。

5.【强制】禁止使用分区表；
说明：分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据；

6.【推荐】拆分大字段和访问频率低的字段，分离冷热数据；

说明：拆分大字段和访问频率低的字段，分离冷热数据，有利于有效利用缓存，防止读入无用的冷数据，减少磁盘IO操作。

7.【推荐】采用合适的分库分表策略，例如千库十表、十库百表等（建议表大小控制在2G），即大表分库、长表拆表；

说明：如果软件项目前期数据量不大，慎重进行分库分表设计，会带来很多麻烦问题，比如分页查询、跨库查询、分布式事务等等，严重影响开发效率。

一般当单表数据量超过500W或者2G的情况下，读写性能会变差一些。此时，如果常规的优化手段已经不起作用，比如：SQL调优、添加索引、主从复制、读写分离，这时候可以考虑一下进行分库分表。

8.【推荐】单表不超过50个int字段；不超过20个char字段，不超过2个text字段；

9.【推荐】日志类型的表可以考虑按创建时间水平切割，定期归档历史数据；

10.【强制】禁止使用order by rand()随机排序函数；
说明：order by rand()会为表增加一个伪列，然后用rand()函数为每一行数据计算出rand()值，基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。

11.【参考】可以结合使用hash、range、lookup table进行散表；

12.【推荐】每张表数据量建议控制在500w以下，超过500w可以使用历史数据归档或分库分表来实现；

说明：500万行并不是MySQL数据库的限制。过大对于修改表结构，备份，恢复都会有很大问题。MySQL没有对存储有限制，取决于存储设置和文件系统；

13.【强制】禁止在表中建立预留字段；
说明：预留字段的命名很难做到见名识义，预留字段无法确认存储的数据类型，所以无法选择合适的类型；对预留字段类型的修改，会对表进行锁定；

三、库数据规范

1.【推荐】数据库使用InnoDB存储引擎；
说明：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高；

2.【推荐】数据库和表的字符集统一使用UTF8；
说明：utf8号称万国码，其无需转码、无乱码风险且节省空间。若是有字段需要存储emoji表情之类的，则将表或字段设置成utf8mb4，utf8mb4向下兼容utf8。

3.【推荐】不同业务，使用不同的数据库，避免互相影响；

说明：如果是大型软件项目，推荐不同业务，使用不同的数据库；如果是中小型软件项目，不建议这样做，这样会增加代码的复杂度，降低开发效率，增加项目成本；

4.【强制】所有线上业务库均必须搭建MHA高可用架构，避免单点问题。

说明：如果是购买云数据库，可以选择高可用版本的；

四、总结

以上介绍了在进行MySQL数据库表设计时，可以遵循的一些技术规范。希望对大家有帮助，谢谢 :-）

附本文参考资料：

• 阿里云官方知乎：https://www.zhihu.com/org/a-li-yun-97-77
• 阿里云开发者官方社区：https://developer.aliyun.com/
• 阿里开发者官方CSDN社区：https://blog.csdn.net/alitech2017?type=blog
• 阿里云云栖号CSDN：https://blog.csdn.net/yunqiinsight/category_10231626.html
• 阿里巴巴技术团队发布的《JAVA开发手册》泰山版
• 阿里云开发者官方微信公众号

如果您对文章中内容有疑问，欢迎在评论区进行留言，我会尽量抽时间给您回复。如果文章对您有帮助，欢迎点赞、收藏。您的点赞，是对我最大的支持和鼓励，谢谢 :-）