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

数据库结构优化的目的：

• 减少数据冗余（相同的数据在多个地方存在）；
• 尽量避免数据维护中出现更新，插入和删除异常（通过范式化设计解决）；
  • 插入异常：如果表中的某个实体随着另一个实体而存在，也就是说如果缺少了某个实体，就无法表示另一个实体，这样设计出来的表就存在插入异常。
  • 更新异常：如果更改表中的某个实体的单独属性时，需要对多行进行更新，这样设计出来的表就存在更新异常。
  • 删除异常：如果删除表中的某一个实体则会导致其他实体的消失，这样设计出来的表就存在删除异常。
• 节约数据存储空间；
• 提高查询效率。

数据库设计的步骤：

1. 需求分析：全面了解产品设计的存储需求、数据处理需求、数据的安全性和完整性有什么样需求；
2. 逻辑设计：设计数据的逻辑存储结构，搞清楚数据实体之间的逻辑关系，解决数据冗余和数据维护异常；
3. 物理设计：根据所使用的数据库（Oracle、SQLServer、MySQL、Redis）特点进行表结构设计；
4. 维护优化：根据实际情况对索引、存储结构等进行优化。

1.逻辑设计

数据库逻辑设计一般按照的三范式进行设计：

看一个示例：

产品经理要求设计一个电商网站的数据库结构，需要具备功能：用户登录及用户管理、商品展示及商品管理、供应商管理、在线销售。

我们按三范式要求进行表设计：

用户登录及用户管理 表设计：
用户信息表(用户名, 密码, 手机号, 姓名, 创建时间, 更新时间), 用户名为主键

商品展示及商品管理 表设计：
商品信息表(商品id, 商品名称, 商品价格, 图书描述, 作者, 创建时间, 更新时间), 商品id为主键
分类信息表(分类id, 分类名称, 分类描述, 创建时间, 更新时间), 分类id为主键
商品分类关联表(商品id, 分类id, 创建时间, 更新时间), 商品id、分类id组成联合主键

在线销售 表设计：
订单表(订单id, 下单用户名, 下单日期, 支付金额, 物流单号), 订单id为主键
订单商品关联表(订单id, 订单商品分类id, 订单商品id, 下单数量), 订单id、订单商品分类id、订单商品id组成联合主键

假设我们要查询出每一个用户的订单总金额，SQL 应该怎么写呢？

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

一共关联了四张表，对于 MySQL，join 表越多，性能越差。

假设我们要查询出下单用户和订单详情，SQL 应该怎么写呢？

select a.订单id,e.下单用户名,e.手机号,d.商品名称,c.下单数量,d.商品价格
from 订单表 a join 订单商品关联表 b on a.订单id=b.订单id
             join 商品分类关联表 c on c.商品id=b.商品id and c.分类id=b.分类id
             join 商品信息表 d on d.商品id=c.商品id
             join 用户信息表 e on e.用户名=a.下单用户名

比上面那个还要多 join 一张表，完全符合范式化的设计有时并不能得到良好的 SQL 查询性能。

如果你的应用要求有非常好的查询性能，那就需要对上面的设计进行一些优化（反范式化设计）。

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

在线销售 表设计：
订单表(订单id, 下单用户名, 下单日期, 支付金额, 物流单号), 订单id为主键
订单商品关联表(订单id, 订单商品分类id, 订单商品id, 下单数量), 订单id、订单商品分类id、订单商品id组成联合主键

在线销售 表设计 反范式化改造：
订单表(订单id, 下单用户名, 手机号, 下单日期, 支付金额, 物流单号, 订单金额), 订单id为主键
订单商品关联表(订单id, 订单商品分类id, 订单商品id, 下单数量, 下单商品单价), 订单id、订单商品分类id、订单商品id组成联合主键

此时再看下上面两个 SQL 改造后的写法：

查询出每一个用户的订单总金额：

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

查询出下单用户和订单详情：

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

SQL 简化了好多，这里也可以看出，进行数据库设计的时候，也不能完全按照范式化的要求进行设计，同时也要考虑以后如何使用表。

下面看一下范式化设计和反范式化设计的优缺点：

在范式化设计的基础上进行反范式优化，这样才能设计出最符合性能和业务要求的数据库表结构。

2. 物理设计

物理设计涉及的内容：

• 定义数据库、表及字段的命名规范；
• 选择合适的存储引擎；
• 为表中的字段选择合适的数据类型；
• 建立数据库结构。

1、我们在定义数据库、表及字段的命名时，要遵守以下几个原则：

• 可读性原则（比如使用下划线分割单词等）；
• 表意性原则；
• 长名原则。

2、选择合适的存储引擎：参考 https://blog.csdn.net/smartbetter/article/details/91492332

3、数据类型的选择：

数据类型的选择对数据库性能有着很大的影响，过大的数据类型往往会浪费更多的内存和磁盘I/O。

数据类型的选择原则：

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

选择正确的整数类型：

选择正确的整数类型：

如何选择varchar和char类型：

varchar长度的选择问题：

• 使用最小的符合需求的长度；
• varchar(5) 和 varchar(200) 存储 ‘MySQL’ 字符串性能不同（MySQL 为了优化查询，在内存对字符串使用的是固定的宽度，宽度定义的太长会消耗更多的内存）；

如何存储日期类型：

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

• 不要使用字符串类型来存储日期时间数据；
  • 日期时间类型通常比字符串占用的存储空间小；
  • 日期时间类型在进行查找过滤时可以利用日期来进行对比；
  • 日期时间类型有着丰富的处理函数，可以方便的对日期时间类型进行日期计算；
• 使用int存储日期时间不如使用timestamp类型。

如何为 Innodb 选择主键？

• 主键应该尽可能的小；
• 主键应该是顺序增长的，可以增加数据的插入效率；
• Innodb的主键和业务主键可以不同（Innodb的主键可以用自增id，业务主键增加唯一索引）；

最后

以上就是酷酷大米为你收集整理的MySQL 性能调优——库表结构优化1.逻辑设计2. 物理设计的全部内容，希望文章能够帮你解决MySQL 性能调优——库表结构优化1.逻辑设计2. 物理设计所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。