数据库设计结构及优化

2017-05-01  本文已影响0人  天道酬勤_FUN

什么影响了性能

数据库设计对性能的影响

MySQL基准测试

什么是基准测试

定义：
基准测试是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响
基准测试是针对系统设置的一种压力测试

基准测试

直接、简单、易于比较，用于评估服务器的处理能力

压力测试

对真实的业务数据进行测试，获得真实系统所能承受的压力

基准测试的目的

如何进行基准测试

对整合系统进行基准测试

从整个系统进行基准测试

从系统入口进行测试（如网站Web前端，手机APP前端）
优点：

单独对MySQL进行基准测试

优点：

MySQL基准测试的常见指标

基准测试的步骤

计划和设计基准测试

基准测试中容易忽略的问题

MySQL基准测试工具之mysqlslap

下载及安装
MySQL服务器自带的基准测试工具，随MySQL一起安装
特点：

数据库结构优化

良好的数据库逻辑设计和物理设计是数据库获得高性能的基础

数据库结构优化的目的

数据库结构设计的步骤

需求分析：

逻辑设计：

物理设计：

维护优化：

数据库设计范式

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

数据库三范式

数据库设计的第一范式

数据库设计的第二范式

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

数据库设计的第三范式

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

需求说明

按下面的需求设计一个电子商务网站的数据库结构
1、本网站只销售图书类商品
2、需要具有以下功能
用户登陆 商品展示 供应商管理 用户管理 商品管理 在线销售

需求分析及逻辑设计

用户登陆及用户管理功能

商品展示及商品管理功能

供应商管理功能

在线销售功能

编写SQL查询出每一个用户的订单总金额

select 下单用户名,sum(d.商品价格*b.商品数量）
from 订单表 a join 订单商品关联表 b on a.订单编号=b.订单编号
join 商品分类关联表 c on c.商品名称=b.商品名称 and c.分类名称=b.订单商品分类
join 商品信息表 d on d.商品名称=c.商品名称
group by 下单用户名

假设下单用户就是商品的收货人，我们在发货前一定要查询出每个订单的下单人的信息，而这些信息全部记录在用户信息表中

# 编写SQL查询出下单用户和订单详情

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

反范式化设计

什么叫做反范式化设计

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

图书在线销售网站数据库的反范式化改造

商品信息：{商品名称，出版社名称，图书价格，图书描述，作者}
分类信息：{分类名称，分类描述}
商品分类关系：{商品名称，分类名称}
after
商品信息：{商品名称，分类名称，出版社名称，图书价格，图书描述，作者}
分类信息：{分类名称，分类描述}
销售相关表
订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}
订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量}
after
订单表：{订单编号，下单用户名，手机号，下单日期，支付金额，物流单号，订单金额}
订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量，商品单价}

反范式化改造后的查询

**编写SQL查询出每一个用户的订单总金额

select 下单用户名, sum(订单金额)
from 订单表
group by 下单用户名;

**编写SQL查询出下单用户和订单详情

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

不能完全按照范式化的要求进行设计
考虑以后如何使用表

范式化设计的优缺点

优点：

缺点：

反范式化设计的优缺点

优点：

缺点：

物理设计

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

物理设计设计的内容

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

选择合适的存储引擎

存储引擎 事务 锁粒度 主要应用 忌用
MyISAM 不支持 支持并发插入的表级锁 SELECT, INSERT 读写操作频繁
MRG_MYISAM 不支持 支持并发插入的表级锁 分段归档，数据仓库 全局查找过多的场景
Innodb 支持 支持MVCC的行级锁 事务处理 无
Archive 不支持 行级锁 日志记录，只支持insert,select 需要随机读取，更新，删除
Ndb cluster 支持 行级锁 高可用性 大部分应用

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

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

如何选择正确的整数类型

|列类型|存储空间|signed取值范围|unsigned取值范围|
|:--:|:--:|:--:|:--:|:--:|
|tinyint|1字节|-128127|0255|
|smallint|2字节|-3276832767|065535|
|mediumint|3字节|-83886088388607|016777215|
|int|4字节|-21474836482147483647|04294967295|
|bigint|8字节|。。。。|。。。。。|

如何选择正确的实数类型

列类型 存储空间 是否精确类型
FLOAT 4个字节 否
DOUBLE 8个字节 否
DECIMAL 每4个字节存9个数字，小数点占一个字节 是

如何选择VARCHAR和CHAR类型

VARCHAR类型的存储特点
VARCHAR长度的选择问题
VARCHAR的适用场景
CHAR类型的存储特点
CHAR类型的适用场景

如何存储日期类型

DATATIME类型

以YYYY-MM-DD HH:MM:SS[.fraction] 格式存储日期时间
datetime = YYYY-MM-DD HH:MM:SS
datetime(6) = 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个字节
时间范围1970-01-01 到2038-01-19
timestamp类型显示依赖于指定的时区
在行的数据修改时可以自动修改timestamp列的值

date类型和time类型

1、使用date类型只需要3个字节
2、使用Date类型还可以利用日期时间函数进行日期之间的计算
date类型用于保存1000-01-01 到 9999-12-31之间的日期
time类型用于存储时间数据，格式为HH:MM:SS

存储日期时间数据的注意事项
上一篇 下一篇

猜你喜欢

热点阅读