如何设计数据库表结构，多年实用经验汇总

摘要

能在设计初期解决的问题，就不要等到后期使用复杂的逻辑去解决。

为了更高效、规范、优雅的书写代码，需要我们在设计表时就打下良好的基础，使得可以应变后续多变的业务需求。在此鄙人根据这些年在开发过程中吐槽过的各种表设计，总结了一些不成文的规范，没有强制的约束，仅仅是为了可以帮助我们开发各种奇葩业务逻辑时，尽可能顺畅一些，规避掉一些繁琐的逻辑。**

映射

1. 表注释 ------映射-----> 实体类描述
2. 表字段 ------映射-----> 实体类属性
3. 字段类型 ------映射-----> 属性数据类型
4. 字段注释 ------映射-----> 属性说明
5. 字段长度 ------映射-----> 属性值长度校验限制

命名规范

1. 表： 项目名_实体类_做什么用的 ， 例：project_student_info(学生信息表), project_student_category(学生分类表)。
2. 主键：类名_id 最好使用这种命名方式，在作为外键时，可使逻辑更为清晰。所有表必须要有主键，尽量设计为单主键表。
3. 关联属性命名：类名_属性名。适用于可能会出现在其他表中的属性，作为关联属性的字段、冗余属性等等（为了减少多表联查而设计的）。不用担心字段命名过长会影响性能，只是个代号而已，最终都会被编译成二进制、机器码的，性能方面完全不用考虑。

    例：student_id、student_name、student_type、class_id、class_name、class_type等。

4. 独立属性命名：适用于只可能出现在本张表中的属性，命名可以保持统一，不用区分，因为这些属性仅作用于本表，也不会出现在其他表中。每个表中总是加上 status（本条数据的状态）、 created_time（创建时间）、modified_time（最后一次更新时间） 这两个字段，给以后分库分表，迁移历史数据带来可能。

    例：created_time（创建时间）、modified_time（最后一次更新时间）等。

5. 附属表主键：附属表id请与主表id保持一致。实现一一对应关系，不允许在附属表新增其他主键字段。
6. 禁用关键词：使用SQL关键词作为表字段，这样会给SQL语句的编译带来麻烦。

    例：key、value、desc、group、select... 等

数据类型选择

1. 主键：大多主键采用自增形式的可以提高性能，且非庞大的单一部署系统，自增主键完全可以满足要求，但也有些数据主键不推荐采用主键自增，比如：用户id…比较私密的数据，使用主键自增很容易猜测下一个id，可实现有规律的递推，此时可考虑SnowFlake雪花算法生成主键，毫秒级时间+机器码+标识+随机数，有序long型数据可提高查询性能（注意时钟回退问题的解决），UUID无序，长度长，唯一性强，但对数据的存储读写不太友好，碎片化严重，因此尽可能不考虑UUID作为主键。
2. 日期：日期推荐使用timestamp类型，可以避免时区问题，MySQL中，日期格式的字段使用datetime类型存储时（注意时区问题）。

---

类型	占用字节	表示范围	时区
timestamp	4字节	‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’	自动检索时区
datetime	8字节	‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’	不自动转换时区

4. 字典类：如状态、类型等可以枚举的属性，推荐使用tinyint(4)来存储，然后通过数据库字典表来解析其所代表的含义。
5. 禁用：禁止使用复杂数据类型（数组，自定义类型等）。数据类型尽量简洁，严谨，长度不要过长。
6. not null：尽可能的把字段定义为Not NULL，mysql比较难优化使用了可空列的查询，它会使索引，索引统计更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理，当可空列被索引时，每条记录都需要一个额外的字节。 即使要在表中存储"没有值"的字段，考虑使用0，特殊字段或者空字符串来代替。

索引

1. 存多个关联关系的表时，可以给关联字段加索引，提高查询性能，
2. 多个索引出现时，考虑组合索引，并且考虑业务复杂度尽可能简化
3. 尽量缩短索引长度，提高数据的写入性能。
4. 非重要业务，减少使用唯一索引。 但不建议在在代码中处理唯一关系，分布式或者多线程带来的数据问题，也会引起不必要查询。

设计思路

1. 可扩展性：在设计表时，若遇到可扩展类型的属性时，请设计成纵表，使逻辑更为清晰。
2. 过期概念：存在过期概念的表，在其设计之初就必须有过期机制，且有明确的过期时间。过期数据必须迁移至历史表中。防止因数据过多造成的麻烦。
3. 表变动：不再使用的表，必须通知DBA予以更名归档。
4. 字段变动：线上表中若有不再使用的字段，为保证数据完整，禁止删除。或者将数据备份留存，批处理到新表中等。
5. 移除外键：不要使用外键来强制数据关系绑定，提高数据库性能，也为后期数据表重构带来可能。
6. 冗余字段：在设计冗余字段时，请考虑数据的时效性，如果是历史性数据，需要将对象信息进行定格存储，若是关联性查询实时性高，变动频繁的，请不要在多个表中设置冗余字段，以免造成数据不一致现象。
7. 计算数据：当遇到计算复杂的逻辑时，可考虑将计算结果一并存储，避免重新计算，损耗性能。
8. 驱动：禁止使用OCI驱动，全部使用THIN驱动。（网上看到的，不懂，需要研究一下）。

---

以上规范仅为个人在平时工作中设计表结构是总结的一些经验，觉得挺实用的，分享出来和大家讨论讨论。有什么不足之处，需要补充的等等，欢迎在评论区讨论 😃 😃 😃