设计数据库中常见的规范

文章目录

• 命名规范
• 合适的字段类型
• 主键设计要合理
• 合适的字段长度
• 优先考虑逻辑删除，而不是物理删除
• 每个表都需要添加一些通用字段
• 表的字段不要太多
• 尽可能使用not null 定义字段
• 评估哪块要加索引
• 避免使用MySQL保留字
• 不要外键关联，一般用代码维护
• 一般都选择INNODB存储引擎
• 选择合适的字符集
• 如果数据库字段是枚举，就在comment注释清楚
• 时间类型的选择
• 不建议使用存储过程，触发器
• 1：N关系的设计
• 大字段
• 分库分表

命名规范

数据库表名，字段名，索引名等都要命名规范，可读性高

• 表名，字段名必须使用小写字母或者数字，禁止使用数字开头，禁止使用拼音，一般还不使用英文缩写
• 主键索引名为pk_字段名，唯一索引名为uk_字段名，普通索引名为idx_字段名

合适的字段类型

设计表时候，要选择合适的字段类型

• 尽可能的选择存储空间小的字段类型，比如数字类型：从tinyint， smallint，int，bigint开始选择
• 小数类型如金额，则使用decimal，禁止使用float和double
• 如果存储的字符串长度几乎相等，使用char定长字符串类型
• archar是可变长字符串，不预先分配空间，长度不要超过500
• 如果存储的值太大，建议字段类型修改为text,同时抽出单独一张表，用主键与之对应
• 同一表中，所有varchar字段的长度加起来，不能大于65535，如果又这样的需求，请使用text/longtext类型

主键设计要合理

主键的设计最好不要与业务逻辑有所关联，主键最后是一串毫无意义，独立不重复的数字，比如：UUID，Auto_increment，又或者是雪花算法生成的主键等等

合适的字段长度

在MySQL中， varchar 和 char 类型表示字符长度，而其他类型表示的长度都表示字节长度。比如 char(10) 表示字符长度是10，而 bigint (4) 表示显示长度是 4 个字节但是因为bigint实际长度是 8 个字节，所以bigint (4) 的实际长度就是8个字节。

我们在设计表的时候，需要充分考虑一个字段的长度，比如一个用户名字段(它的长度5~20个字符) ，你觉得应该设置多长呢? 可以考虑设置为 username varchar (32) 。:字段长度一般设置为2的幂哈(也就是 2的n 次方)

优先考虑逻辑删除，而不是物理删除

• 物理删除：把数据从硬盘删除，可释存储空间
• 逻辑删除：给数据添加一个字段，is_deleted，以标记数据已经逻辑删除了

# 物理删除
delete from account_info_tab where account_no = '11';


# 逻辑删除
update account_info_tab set is_deleted = 1 where account_no = '11';

Why?

• 恢复数据困难
• 物理删除会使自增的主键不再连续
• 核心业务表不建议做物理删除，只适合做状态变更

每个表都需要添加一些通用字段

• id：主键，必须
• creat_time：创建时间，必须
• update_time：修改时间，必须
• version：数据记录的版本号，用于乐观锁，非必须
• remark：数据记录备注，非必须
• modified_by：修改人，非必须
• creator：创建人，非必须

表的字段不要太多

一张表的字段不要太多，一般尽量不要超过20个字段

Why? 如果一张表的字段过多，表中保存的数据可能就会很大，查询效率就会低

当表的字段数很多时，可以将表分为俩张表，一张作为条件查询表，一张作为详细内容

尽可能使用not null 定义字段

如果没有特殊的理由，一般都建议将字段定义为 NOT NULL

why?

• 首先，NOT NULL 可以防止出现空指针问题
• 其次， NULL 值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。
• NULL 值有可能会导致索引失效
• 如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑，那就可以将这个字段设置为 NOT NULL

评估哪块要加索引

首先，评估表的数据量，如果数据量只有几十行，就没必要加索引。否则设计表的时候，如果有查询条件的字段，一般就要加索引

索引使用的注意事项：

• 索引不要建的太多，一般单索引个数不要超过5个
• 去分度不高的字段，不能加索引，如：性别
• 索引建立完成后，还是要避免索引失效的情况
• 索引过多的话，可以通过联合索引的方式来优化，然后的话，索引还有一些规则，如覆盖索引，最左匹配原则等等

避免使用MySQL保留字

如果有MySQL的保留着，可能会使得SQL语句编写，SHELL脚本中变量的转义变得非常复杂

不要外键关联，一般用代码维护

这个在阿里的java规范也有提到：

【强制】不得使用外键与级联，一切外键概念必须在业务层解决

• 使用外键存在性能问题、并发死锁问题、使用起来不方使等等。每次做 DELETE 或者 UPDATE 都必须考虑外键约束，会导致开发的时候很难受,测试数据造数据也不方便。
• 还有一个场景不能使用外键，就是分库分表。

一般都选择INNODB存储引擎

image-20221222122102243

选择合适的字符集

一般中英文环境使用utf8mb4

• utf8：支持中英文混合，国际通用，3个字节长度
• utf8mb4：完全兼容utf8，4个字节长度，一般存储emoji表情要用到
• GBK：支持中文，但不支持国际通用字符集，2个字节长度
• latin1：MySQL默认字符集，1个字节长度

如果数据库字段是枚举，就在comment注释清楚

时间类型的选择

• date：表示日期值
• time：表示时间值
• datetime：表示日期时间值，跟时区无关
• timestamp：表示时间戳值，跟时区有关
• year：年份值

推荐使用datetime来保存日期和时间，存储范围更大，且和时区无关

不建议使用存储过程，触发器

存储过程：已预编译为一个可执行过程的一个或多个sql语句

触发器：指一段代码，当触发某个事件的时候，自动执行这些代码

原因：对于MYSQL来说，这俩个东西不是很成熟

1：N关系的设计

img

有时候俩张表存在N:N关系时候，我们应该消除这种关系，通过增加第三张表，把N:N修改为俩个1：N

大字段

当碰到很大的数据的时候，不建议直接把表字段设计为text类型，可以保存到mongodb中，然后，在业务表保存对应的mongodb的id即可

分库分表

• 分表：就是一个数据库分为多个表

image-20221222141251783

原因：数据量太大的话，SQL的查询就会变慢。

又或者是把商品需要频繁增删改查的数据和基本不怎么用的数据给分离开来。

image-20221222143227846

参考视频：21个MySQL表设计的经验准则