Mysql数据库设计规范和技巧

1. 数据库设计的目的

    有效的存储

    高效的操作

2. 数据库设计的四个步骤

2.1 需求分析

1. 了解数据的属性，有哪些字段，主键的可选项
2. 了解数据之间的对应关系，一对一、一对多、多对多  
3. 了解数据的生命周期，是临时存储、是永久存储、还是定期归档

2.2 逻辑设计

2.2.1 逻辑设计失败会导致的问题：

   插入异常：如果某个实体随着另一个实体的存在而存在，即缺少某个实体无法表示这个实体，那么这个表就存在插入异常

   更新异常：如果更改表的某个单独属性时，需要更新多行数据，那么这个表存在更新异常

   删除异常：如果删除表的某一行来反映实体失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常

   数据冗余：相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，那么这个表就存在数据冗余

PS:一般存在插入异常的表都存在更新异常和删除异常

2.2.2 逻辑设计的方法：

 用ER图表示表与表之间的对应关系及表中的字段属性

      矩形：表示实体集，代表一张表

      菱形：表示表与表之间的联系

      椭圆：表示实体的属性

      线段：表示对象之间存在关联

2.2.3 逻辑设计的原则：

第一范式：列不可再分，数据属性尽量拆分到不可再分

第二范式：所有非主键字段只能依赖主键，而不能依赖主键的一部分，即所有的单主键表都满足第二范式

第三范式：非主键字段之间不能相互依赖，如果存在非主键字段之间的依赖，应当拆表

BC范式：联合主键之间的字段不 能相互依赖，如果存在，就不应该使用联合主键。

第四范式：一个表的非主键属性相互独立时，非主键属性不能有多个值。例如，职工表(职工编号，职工孩子姓名，职工选修课程)，在这个表中，同一个职工可能会有多个职工孩子姓名，同样，同一个职工也可能会有多个职工选修课程，即这里存在着多值事实，不符合第四范式。如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如职工表一(职工编号，职工孩子姓名)，职工表二(职工编号，职工选修课程)，两个表都只有一个多值事实，所以符合第四范式。

第五范式：尽可能将表拆分成较小的表，以减少数据冗余

PS: 数据库范式层层往后依赖，即后一范式一定满足前一范式，设计通常满足第三范式即可，越往后付出价值越大，若追求性能，不一定遵循这些范式。

反范式设计

完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中会大量存在着表的 关联查询，而大量的表关联很多的时候非常影响查询的性能。所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间。

2.3 物理设计

    目的：建立数据库的表结构

2.3.1 存储引擎的选择：

功能	MyISAM	Innodb	Memory	Archive
存储限制	256TB	64TB	RAM	None
支持事务	No	Yes	No	No
支持全文索引	Yes	Yes	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	Yes	No
支持数据缓存	No	Yes	N/A	No
支持外键	No	Yes	No	No

    MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开 始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。

  选择原则：

1. 绝大多数情况下使用Innodb
2. 如果一个表需要支持事务，考虑并发的安全和性能，选择InnoDB
3. MyISAM,只支持表级锁，InnoDB在走索引时使用行级锁。InnoDB支持事务，在读写时需要损耗更多的性能和磁盘资源开销。综上特点，在不考虑并发上锁的前提下，MyISAM的查询和插入效率高于InnoDB，并发时则性能差异不大。如果一个表不需要支持事务，也不需要考虑并发场景下的安全和性能影响，选择MyISAM，如数据字典、用户角色、菜单表等系统结构表优先选择MyISAM。
4. 临时表并数据量不大优先考虑Memory
5. Archive虽不安全，但支持高并发的插入操作。如果一个表只要插入和查询操作，没有修改。优先考虑Archive，如日志信息记录

2.3.2 主键的设计

自增主键：

1. 因为按顺序存储数据，存储时能大概率避免数据的移动，存储空间小，性能较好。
2. 主键容易被探测，不安全，分表和合表数据容易发生冲突。
3. 很多文章说高并发下，使用自增主键会导致间隙锁的竞争，这个结论是不对的。间隙锁的竞争问题是Innodb引擎层面产生的，与是否使用自增主键没有关系。

UUID:

1. 全球唯一性，数据随机插入，导致mysql产生磁盘随机IO，每次插入数据必然引起数据的移动，也可能导致mysql的页分裂，存储空间大，性能较差。

设计原则：

1. 如果并发量较大、没有分表、合表需求时优先考虑自增主键。反之选择UUID主键。
2. 尽量不使用业务相关字段。
3. 同一个表中主键不重用。
4. 只要是约束就会影响性能，对于MyISAM存储引擎，如果表不与其他表做关联查询，则不需要创建主键。对于Innodb存储引擎，最好自定义主键，不然mysql会为表自动生成一个6B的主键。

2.3.3 字段类型的选择

     1. 根据业务的需要先确定大的字段类型方向，如是使用字符串？还是整形？还是浮点型？还是时间类型。确定后再来细化选择。

     2.  如果一个字段既各种类型都可以满足业务需求，优先选择数字类型，其次是时间类型，最后考虑字符串类型。因为相对而言，计算机更擅长数字计算。

     3.  在满足业务的前提下尽量使用存储空间较小的数字类型，适当考虑日后数据的增长。如果没有负数，应使用无符号数字类型。 

     4. 对于有小数的字段，优先考虑decimal定点型，它的精确度更高。但相对于float、double来讲，它的存储空间更大。

      5. 日期尽量用日期类型，不要用数字类型，避免带来不必要的麻烦。

  更多细节参考：MYSQL字段类型