数据库结构的优化

数据库设计的步骤：

需求分析：全面了解产品设计的存储需求

逻辑设计：设计数据的逻辑存储结构

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

维护优化：根据实际的情况对索引、存储引擎的优化

###数据库的范式：

第一范式：数据库中的所有字段都只是具有单一的属性

第二范式：在第一范式的条件下，要求一个表中只具有一个业务主键，每个表只做一件事情

第三范式：在第二范式基础上，消除表中的传递依赖关系

一、需求分析及逻辑设计

用户模块

用户必须注册并等咯系统才能进行网上的交易

同一时间一个用户只能在一个地方登陆

用户的信息:{用户名，密码，手机号，姓名，注册日期，在线状态，出生日期}商品模块

商品信息:{商品名称，出版社名称，图书的价格，图书描述，作者}

分类信息：{分类名称，分类描述}

商品分类信息（对应关系表）：{商品名称，分类名称}供应商模块

供应商的信息：{出版社名称，地址，电话，联系人，银行账号}在线销售模块

在线销售所需要的数据：{订单编号，下单用户名，下单日期，订单金额，订单商品分类，订单商品名，订单商品的单价，订单商品数量，支付金额，物流单号}

订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}

订单商品的关联表：{订单编号，订单商品分类，订单商品名，商品数量}考虑性能的问题及商品价格发生变化：

商品信息表的反范式化设计： 商品信息:{商品名称，分类名称，出版社名称，图书描述，作者}分类信息：{分类名称，分类描述}

在线销售表的反范式化设计： 订单表：{订单编号，下单用户名，手机号，下单日期，支付金额，物流单号，订单金额}订单商品的关联表：{订单编号，订单商品分类，订单商品名，商品数量，商品单价}

反范式化后的查询每个用户的订单的总金额的SQL语句： select 下单用户名,sum(订单金额) from 订单表 group by 下单用户名；

反范式化后的查询下单用户和订单详情的SQL语句：

select a.订单编号，a.用户名，a.手机号，b.商品名称，b.商品的单价，b.商品数量 from 订单表 a join 订单商品的关联表 b on a.订单编号=b.订单编号；

###范式化的优缺点

范式化的优点：

可以尽量的减少冗余数据

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

范式化的表通常比反范式化的表更小范式化的缺点：

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

更难进行索引优化

###反范式化的优缺点反范式化的优点：

可以很好的减少表的关联

可以对查询进行索引优化反范式化的缺点：

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

对数据的修改需要更多的成本二、数据库的物理设计阶段

定义数据库、表及字段的命名规范（可读性原则、表意性原则、长名原则）

选择合适的存储引擎

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

整数类型：

tinyint(1个字节)、smallint(2个字节)、

mediumint(3个字节)、int(4个字节)、bigint(8个字节)

实数类型：java学习交流群：478052716

float(4个字节，不为精确类型)、double(8个字节，不为精确类型)、

decimal(每4个字节存9个数字，小数点占一个字节，为精确类型)

varchar和char类型：java学习交流群：478052716

varchar类型存储特点：

用于存储变长字符串，只占用必要的存储空间；

列的最大长度小于255时则只占用一个额外字节用于记录字符串长度；

列的最大长度大于255则，要占用两个额外字节用于记录字符串长度

如何对varchar列选择合适的宽度：

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

varchar(5)和varchar(200)存储MySQL字符串性能不同

varchar的适用场景：

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

字符串列很少被更新的字符串的列；

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

char类型存储特点：

char类型是定长的；

字符串存储在char类型的列中会删除末尾的空格；

char类型存储的最大的宽度是255

char类型的适用的场景：

适合存储所长度近似的值（eg:md5的值、手机号、身份证号）

适合存储长度短小的字符串

适合存储存储经常更新的字符串

日期类型：java学习交流群：478052716

datatime类型以YYYY-MM-DD HH:MM:SS[.fraction]格式存储数据

datatime类型与时区无关，占用8个字节存储空间

存储的时间范围：1000-01-01 00：00:00到9999-12-31 23:59:59

timestamp类型存储从1970年1月1日到当前的秒数，以YYYY-MM-DD HH:MM:SS[.fraction]显示，占用4个字节存储空间

timestamp类型显示依赖于所指定的时区

timestamp类型在行数据修改时可以自动修改timestamp列的值

timestamp存储的时间范围1970-01-01到2038-01-19

date类型和time类型（mysql5.7之后加入）：

date类型占用的字节数比使用字符串、datatime、int存储要少，使用date类型只需要3个字节；

date类型使用Date类型还可以利用日期时间函数进行日期之间的计算；

date类型存储的日期范围1000-01-01到9999-12-31之间的日期

time类型用于存储时间数据：HH:MM:SS

存储日期时间类型的注意事项：

不要使用字符串类型来存储日期时间数据；

日期时间类型通常比字符串类型所占用的存储空间小；

日期时间类型在进行查找过滤时可以利用日期来进行对比；

日期时间类型有丰富的处理函数，可以方便的对时间类型的进行日期计算

使用Int存储日期时间不如使用Timestamp类型

InnoDB如何选择主键：

主键应该尽可能的小；

主键应该是顺序增长的（以减少随机IO），增加数据的插入效率；

InnoDB的主键和业务主键可以不同

建立数据库结构

维护优化数据库