本文主要是介绍【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开发手册》泰山版
- 阿里云开发者官方微信公众号
如果您对文章中内容有疑问，欢迎在评论区进行留言，我会尽量抽时间给您回复。如果文章对您有帮助，欢迎点赞、收藏。您的点赞，是对我最大的支持和鼓励，谢谢 :-）
这篇关于【MySQL数据库开发设计规范】之表设计规范的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
