关于数据库设计的一点心得

数据库设计，首先想到什么？数据库三范式? 起初认为那几句话晦涩的很，不妨一起回忆一下。

第一范式：确保表中每一列数据的原子性，不可再分！

第二范式：在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。

第三范式设计表：再满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。

我现在的理解大概意思就是：

属性具有原子性，不可再分解，例如存地址不能用一个address字段概括，尽量拆分成省市区分别存储；

要求记录有惟一标识，即实体的惟一性，例如上面的省市区是不是存在另一张表，在address表关联省市区表的id即可呢；

任何字段不能由其他字段派生出来，它要求字段没有冗余，比如订单表包含了商品id 就不要冗余商品名称之类的；

但是三大范式只是一般设计数据库的基本理念，实际开发过程中，要具体需求具体对待，因此，需求>性能>表结构。所以不能一味的去追求范式建立数据库。现总结一下实际经验，不一定全对，记录一下吧

一、主键

1、尽量为每个表定义一个主键，主键选择要尽量选择自增或者数字类型

2、主键的名字不能直接叫id,都叫这个后期开发容易混乱，特别是在你做两张表连接查询，而他们都有一个叫做Id的主键时。名字尽量突出表特征，例如用户表可以直接叫user_id

在InnoDB的索引实现时，因为是聚簇索引【叶子节点data域保存完整数据记录的就是聚簇索引，叶子节点data域只保存主键值或数据地址的就是非聚簇索引】所以，键索引查询数据，只需根据主键值拿到叶子节点中data域的数据即可。而对于普通索引查询数据时，首先找到叶子节点data域中的主键值，然后再去主键索引中根据主键值去查数据。这同样也能说明什么尽量使用短的字段作为索引，由于每个B+树的节点大小是固定的，过大的字段会导致每个节点存储的key数量表少，从而使树的层级变高，增加IO消耗。

• 如果表定义了主键，则会以这个主键作为key，进行构建聚簇索引
• 如果没有定义主键，则会选择一个唯一索引作为key，进行构建聚簇索引
• 如果没有主键也没有唯一索引，那么就会创建一个隐藏的row-id作为key，进行构建聚簇索引。

二、适当冗余数据，

有时候多一些冗余字段能够对性能有所提高，通过空间换时间,往往事半功倍

上面虽然看起来违反数据库设计第三范式，因为订单商品详细表完全可以通过prod_id找到商品名字prood_name.那为啥还要存一份呢？实际开发中根据业务需求大有作用，比如以后这个商品换名字了，但是订单表缺没存储，那么查询订单时候商品名字也随之改变了【可能换成商品价格更具有说服力】，但是订单早就形成了，属于历史数据，这样一来新的修改就影响了已经完成了历史数据，显然不合适

三、主表和从表

一对多：当一条主表数据对应多条从表数据时候，大家都能很容易设计出字表包含主表主键的数据库，例如下图订单管理表对应多个订单商品详细表。

一对一：对于一份订单实际情况只有一个配送地址，那么会不会有人就把订单配送信息表主键order_addr_id放在了主表订单管理表呢？

这样做也能满足业务需求，但会造成影响主表结构，假如新增从表那么必定会改变主表结构，甚至还要改变表数据，那么你的主表将破烂不堪......所以尽量在从表中存主表ID,这样对主线业务影响最小

四、字段选择-建议

• 默认值： 数据库所有为NULL 的列需要额外的空间来存储，因此会占用更多的空间；也尽量不要使用数据库默认值，数据入口保持一致性，都是从java实体传递过来比较好，数据库默认值增加数据来源并且切换数据库容易出现问题
• 财务相关的金额类数据必须使用decimal 类型，Double涉及精度丢失问题
• 时间尽量不要用数据库函数来写，Mysql主从同步会造成时间差，推荐使用类型datime [timestamp的范围1970~2037年，并且读取时需要时区转换]
•  Boolean 类型的数据不能用 is 开头，例如is_check、is_pass,因为这样会造成部分框架解析引起序列化错误
• 尽量含有数据创建人、创建时间、更新人、更新时间等字段
• 优先选择符合存储需要的最小的数据类型。例如性别最好选用char 可以定长为1，
• 列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。
• 禁止在表中建立预留字段，预留字段的命名很难做到见名识义。预留字段无法确认存储的数据类型，所以无法选择合适的类型。对预留字段类型的修改，会对表进行锁定。
• 尽量减少视图、存储过程的使用，避免使用触发器，如果非要使用，也应该降低视图复杂度【接触过一个老项目，视图套视图套视图....】,查询慢，并且当一个表结构改变时候，一系列视图都需要改变。存储过程可调试性差、数据库压力大
• 尽量避免使用外键约束: 不建议使用外键约束(foreign key)，但一定要在表与表之间的关联键上建立索引；外键虽然可以保证数据的参照完整性，但外键也会影响父表和子表的写操作从而降低性能，还会使得表更耦合，建议在业务端实现。
• 避免使用TEXT、BLOB数据类型，禁止在数据库中存储图片,文件等大的二进制数据，MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，若查询中包含这样的数据，在执行排序等操作时就不能使用内存临时表，必须使用磁盘临时表执行操作，若需要使用，建议把BLOB或TEXT列分离到单独的的扩展表中
• 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

五、命名规范-建议

• 数据库所有业务表，前缀均使用项目名称或者模块首字母缩写；
•  数据库所有对象名称均使用小写字母，并且单词之间通过下划线分开；
•  数据库所有存储相同数据的列名和列类型必须保持一致。
• 表名不使用复数名词，表名应该仅仅表示表里面的实体内容，不应该表示实体数量

六、字符长度-建议

• varchar(255)：并非要8的倍数32，64，128，256，512，1024长度，因为char, varchar类型的值，会有一个长度标识位来存值长度。当定义varchar长度小于等于255时，长度标识位需要一个字节；当大于255时，长度标识位需要两个字节。官方文档所说，varchar有效的最大长度取决于行的容量，以及用的字符集，整行的所有列的定义长度不能超过65535字节(bytes)，text、blob等大字段类型除外，