mysql负荷曲线表设计_数据库设计结构及优化

什么影响了性能

数据库设计对性能的影响

过分的反范式化为表建立太多的列

过分的范式化造成太多的表关联

在OLTP环境中使用不恰当的分区表

使用外键保证数据的完整性

MySQL基准测试

什么是基准测试

定义：

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

基准测试是针对系统设置的一种压力测试

基准测试

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

压力测试

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

压力测试需要针对不同主题，所使用的数据和查询也是真实用到的

基准测试可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系

基准测试的目的

建立MySQL服务器的性能基准线

确定当前MySQL服务器运行情况

模拟比当前系统更高的负载，以找出系统的扩展瓶颈

增加数据库并发，观察QPS,TPS变化，确定并发量与性能最优的关系

测试不同的硬件、软件和操作系统配置

证明新的硬件设备是否配置正确

如何进行基准测试

对整合系统进行基准测试

从整个系统进行基准测试

从系统入口进行测试(如网站Web前端，手机APP前端)

优点：

能够测试整个系统的性能，包括web服务器缓存、数据库等

能反映出系统中各个组件接口间的性能问题体现真实性能状况

缺点：

测试设计复杂，消耗时间长

单独对MySQL进行基准测试

优点：

测试设计简单，所需耗费时间短

缺点：

无法全面了解整个系统的性能基线

MySQL基准测试的常见指标

单位时间内所处理的事务数(TPS)

单位时间内所处理的查询数(QPS)

响应时间

平均响应时间、最小响应时间、最大响应时间、各时间所占百分比

并发量：同时处理的查询请求的数量

正在工作中的并发的操作数或同时工作的数量

基准测试的步骤

计划和设计基准测试

对整个系统还是某一组件

使用什么样的数据

准备基准测试及数据收集脚本

CPU使用率、IO、网络流量、状态与计数器信息等

运行基准测试

保存及分析基准测试结果

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

使用生产环境数据时只使用了部分数据

在多用户场景中，只做单用户的测试

在单服务器上测试分布式应用

反复执行同一查询

推荐：使用相同架构进行测试

容易缓存命中，无法反应真实查询性能

MySQL基准测试工具之mysqlslap

下载及安装

MySQL服务器自带的基准测试工具，随MySQL一起安装

特点：

可以模拟服务器负载，并输出相关统计信息

可以指定也可以自动生成查询语句

常用参数说明

-auto-generate-sql 由系统自动生成SQL脚本进行测试

-auto-generate-sql-add-autoincrement 在生成的表中增加自增ID

-auto-generate-sql-load-type 指定测试中使用的查询类型

-auto-generate-sql-write-number 指定初始化数据时生成的数据量

-concurrency 指定并发线程的数量

-engine 指定要测试表的存储引擎，可以用逗号分隔多个存储引擎

-no-drop 指定不清理测试数据

-iterations 指定测试运行的次数

-number-of-queries 指定每一个线程执行的查询数量

-debug-info 指定输出额外的内存及CPU统计信息

-number-int-cols 指定测试表中包含的INT类型列的数量

-number-char-cols 指定测试表中包含的varchar类型的数量

-create-schema 指定了用于执行测试的数据库的名字

-query 用于指定自定义SQL的脚本

-only-print 并不运行测试脚本，而是把生成的脚本打印出来

数据库结构优化

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

数据库结构优化的目的

减少数据冗余

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

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

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

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

节约数据存储空间

提高查询效率

数据库结构设计的步骤

需求分析：

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

存储需求

数据处理需求

数据的安全性和完整性

逻辑设计：

设计数据的逻辑存储结构

数据实体之间的逻辑关系，解决数据冗余和数据维护异常

物理设计：

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

关系型数据库：Oralce、SQLServer、Mysql、postgresSQL

非关系型数据库：mongo、Redis、Hadoop

存储引擎：Innodb

维护优化：

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

数据库设计范式

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

数据库三范式

数据库设计的第一范式

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

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

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

数据库设计的第二范式

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

数据库设计的第三范式

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

需求说明

按下面的需求设计一个电子商务网站的数据库结构

1、本网站只销售图书类商品

2、需要具有以下功能

用户登陆 商品展示 供应商管理 用户管理 商品管理 在线销售

需求分析及逻辑设计

用户登陆及用户管理功能

用户必须注册并登陆系统才能进行网上交易

用户名来作为用户信息的业务主键

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

用户信息：{用户名、密码、手机号、姓名、注册日期、在线状态、出生日期}

只有一个业务主键，一定是符合第二范式

没有属性和业务主键存在传递依赖的关系，符合第三范式

商品展示及商品管理功能

商品信息：{商品名称、分类名称、出版社名称、图书价格、图书描述、作者}

拆分

商品信息：{商品名称、出版社名称、图书价格、图书描述、作者}

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

商品分类(对应关系表)：{商品名称、分类名称}

供应商管理功能

供应商信息：{出版社名称、地址、电话、联系人、银行账号}

在线销售功能

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

1、只有一个业务主键，负荷第二范式

2、订单商品单价，订单商品数量，订单编号存在着传递依赖关系，不符合第三范式

3、数据冗余=》订单商品信息和商品信息表中的数据

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

订单商品关联表：{订单编号、订单商品分类、订单商品名、商品数量}

编写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用于存储变长字符串，只占用必要的存储空间

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

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

VARCHAR长度的选择问题

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

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

VARCHAR的适用场景

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

字符串列很少被更新

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

CHAR类型的存储特点

CHAR类型是定长的

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

CHAR类型的最大宽度为255

CHAR类型的适用场景

CHAR类型适合存储所长度近似的值

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

存储日期时间数据的注意事项

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

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

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

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

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