MySql的高可用之路--4.数据库结构优化设计及数据库三范式设计和反范式化设计及数据库物理设计阶段及为表中的字段选择合适的数据类型

数据库结构优化目的：

减少数据冗余

尽量避免数据维护中出现更新，插入和删除异常

插入异常：如果表中的某个实体随着另一个实体而存在

更新异常：如果更改表中的某个实体的单独属性时，需要对多行进行更新

删除异常：如果删除表中的某一实体则会导致其他实体的消失

节约数据存储空间

 提高查询异常

 

数据库结构优化设计步骤：

需求分析：全面了解产品设计的存储需求、存储需求、数据处理需求、数据的安全性和完整性

逻辑设计：设计数据的逻辑存储结构、数据实体之间的逻辑关系，解决数据冗余和数据维护异常

物理设计：根据所使用的数据库特点进行表结构设计

关系型数据库：oralce，SQLServer，MySQL，postgresSQL

非关系型数据库：mongo，redis，hadoop

存储引擎：innodb

维护优化：根据实际情况对索引、存储结构等进行优化

数据库设计范式：

设计出没有数据冗余和数据维护异常的数据库结构

数据库三范式

数据库设计的第一范式

     数据库表中的所有字段都只具有单一属性

     单一属性的列是由基本的数据类型所构成的

     设计出来的表都是简单的二维表

数据库设计的第二范式

     要求一个表中具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系

数据库设计的第三范式

     指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖

 

数据库设计实例

需求说明

 需求分析及逻辑设计

设置它的业务主键为用户名

因此只有一个业务主键，一定是符合第二范式，没有属性和业务主键存在传递依赖的关系，符合第三范式

 

 

 

如果按照我们上面的方式划分表的话，SQL语句如下：

因此我们发现完全符合范式化的设计有时并不能得到良好的SQL查询性能。 

 反范式化设计

反范式化是针对范式化而言的，在前面介绍了数据库设计的范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。

简单来说就是通过少量的数据冗余，来换取数据库的执行效率。不能完全按照范式化的要求进行设计，要考虑以后是如何使用表，将相关查询较多的数据关联到一张表上，提高数据库的查询效率。

 

 

总结：

范式化设计的优缺点

优点：

可以尽量的减少数据冗余

范式化的更新操作比反范式化更快

范式化的表通常比反范式化更小

缺点：

对于查询需要对多个表进行关联

更难进行索引优化

反范式化设计的优缺点

优点：

可以减少表的关联

可以更好的进行索引优化

缺点：

存在数据冗余及数据维护异常

对数据的修改需要更多的成本

因此我们在设计数据的时候既要结合范式化也要结合反范式化设计。

 

数据库物理设计阶段

根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计

物理设计涉及的内容

定义数据库、表及字段的命名规范

选择合适的存储引擎

为表中的字段选择合适的数据类型

建立数据库结构

定义数据库、表及字段的命名规范

数据库、表及字段的命名要遵守可读性原则

数据库、表及字段的命名要遵守表意性原则

数据库、表及字段的命名要遵守长名原则

为表中的字段选择合适的数据类型

当一个列可以选择多种数据类型时，应该优先考虑数据类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

 

 decimal（18，9）：9表示小数点后面必须是9个数字，如果不够用0来补充，如果超过，报越界异常。18表示的是整数部分+小数分别，也就是整数部分的数字的数量为18-9=9个。

VARCHAR长度的选择问题：

使用最小的符合需求的长度。 

这里我们要考虑到一个问题，从以上信息我们发现varchar可变大小如果小于255只占用一个额外字节用于记录字符串长度。这样的话为什么还要使用最小的符合需求的长度呢？

主要原因在于数据库查询上，如果设置的可变长度越长，会导致在存储的时候产生更多的存储碎片，从而导致查询所消耗的性能就会变大。

VARCHAR的适用场景

字符串列的最大长度比平均长度大很多

字符串列很少被更新

使用了多字节字符集存储字符串

 

存储日期数据：

 

数据库操作对比两个日期值：

 d1为DATATIME，d2为TIMESTAMP，d3为TIMESTAMP

我们可以发现d2和d3自动添加了日期