MYSQL性能优化之Mysql数据库结构设计

数据库结构设置和SQL语句优化是最能提升性能的优化了，和项目进行持续优化

• 减少数据冗余（数据多次出现，或者一列数据可以通过其他列计算得到）
• 尽量避免数据维护中出现更新，插入和删除异常(可以利用范式化模型来解决)
  
  • 插入异常：表中的某个实体随着另一个实体而存在
  • 更新异常：如果更改表中的某个实例的单独属性时，需要对多行进行更新。（数据冗余）
  • 删除异常： 删除表中的某个实体，另一个实体也消失
• 节约数据存储空间
• 提高查询效率
  插入异常示例：
  
  更新异常示例：
  

如果没有学生选择这门课，就没法插入课程.

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异

MySQL查询过程：

这个过程的成本是非常高的。
客户端向MySQL服务器发送一条查询请求
服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
MySQL根据执行计划，调用存储引擎的API来执行查询
将结果返回给客户端，同时缓存查询结果

mysql最多允许关联61个表（建议：关联表最多控制在十个以内）
分区表是在物理存储上的拆分，但是逻辑是没有改变的（同一个数据库实例下进行）。分库分表两个都是需要改变的。
分区键的选择非常关键
分区表最好在联机分析处理OLAP（On-Line Analytical Processing）环境中使用，比如日志
外键最好不要使用。建立索引还是很有必要的。

数据库结构设计

1. 需求分析文档（技术和业务的沟通）
   
   1. （数据特点，数据库读取和修改完成产品设计的功能，对数据处理产生的相应时间，数据处理方式是批量处理还是连接处理）
2. 逻辑设计
3. 物理设计
4. 维护优化
   
   
   

范式化模型：

• 每个字段只包含最小的信息属性。如果某个字段名称为name-age，value为zhangsan-23，则这个模型不满足第一范式，需要将name-age分为两个属性name和age后才满足第一范式。
• （在满足第一范式基础上）模型含有主键，非主键字段依赖主键。比如订单这个模型，它的主键是订单ID，那么订单模型其它字段都应该依赖于订单ID，如商品ID和订单没有直接关系，则这个属性不应该放到订单模型而应该放到”订单-商品”中间表。
• （在满足第二范式基础上）模型非主键字段不能相互依赖。订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列”订单编号”相关，再细看你会发现”顾客姓名”和”顾客编号”相关，”顾客编号”和”订单编号”又相关，最后经过传递依赖，”顾客姓名”也和”订单编号”相关。为了满足第三范式，应去掉”顾客姓名”列，放入客户表中。

主键有两个，不符合第二范式要求。解决方法：拆分

第三种连接表非主键不存在对主键的依赖关系

注意学生信息表中学院名称，学院电话和学号的关系，存在非主键对主键的传递依赖。解决：拆分

设计练习：

需求：

需求分析和逻辑设计（和需求提出者进行分析）：

1. 唯一标识
2. 用户表中存在记录用户是否登陆的状态信息

分类名称存在数据库维护异常的，比如新增加一个分类，但是分类中没有图书，分类就无法进行记录。解决：拆分

查询测试加优化：
关联表越多，性能越差

而且，一旦商品价格变了，用户订单也变了，What’s fuck? 所以，从业务角度来看，设计符合规格。但是，如果从技术角度来看，则存在着很大的问题

反范式化设计

由于查询商品时，分类几乎每次都会用到，所以反范式化（违反了第二范式）

和订单表经常一起查询的数据：订单金额，用户名，用户手机号。（之前是关联用户表，不过，试想一下，如果用户手机号换了怎么办？如果商品单价变了怎么办）

范式化设计优缺点

反范式化设计优缺点（表关联很耗费资源，这是毋庸置疑的）

关联操作大部分是随机I/O,全表扫描大部分是顺序IO

物理设计阶段：

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

1

2

3

• 同样的处理，字符串（需要参照排序规则）往往要比数字与二进制（不需要参照排序规则）要慢。
• 在数据库中，数据处理是以页为单位的（存储数据量是一定的，Innodb是16k，列越小越快）

int(2)只会存储两位整数嘛（节省空间吗）？答：还是会占用4个字节的存储空间。所以我们如果要只存储两位数的话，就使用tinyint（节省空间）

分组汇总

- 当我们使用非精确类型进行汇总操作时，结果可能会有尽可能多的浮点数（结果不尽相同）
- DECIMAL(18,9)需要9个字节来存储（前面9位数需要4个字节，小数点一个字节，小数4个字节），可以保证精度，但是需要更多的空间
- mysql5.0版本之后，DECIMAL最多允许存储65个数字
分组汇总

• VARCHAR和CHAR和存储引擎也有很大的关系
• 一个字符是有可能有多个字节的（UTF8,一个字符占用一个字节）
• varchar和char是以字节为单位的，varchar（10）存储10个字符
• varchar的最大宽度是65535。对于InnoDB来说，这是一行所有varchar列共享的一个长度
  -前期一定要充分的考虑业务需求。 不要在业务上线后修改varchar的类型，因为这会造成锁表（mysql5.7之前），原来的宽度是225以内，改变后的宽度同样不能超过225，不锁表(mysql5.7).锁表：在一个繁忙的系统中，会造成很严重的性能问题的。
• 而且列的长度也会对查询产生影响
• varchar长度发生变化（更新），会造成存储页的分裂，从而造成很多的存储碎片
• varchar(N), 这里的Ｎ是指字符数，并不是字节数．占用的字节数与编码有关
  utf-8, 一个汉字3字节　英文字母1字节
• char类型适合存储MD5密码，身份证，手机号，日期。。。。。。
• 性别：男，女。varchar是四个字节,char是三个字节。

日期数据的存储

timastamp是以int型存储的

set time_zone = '+10:00';

当我们修改时区时

毫秒

修改数据，第一个timestamp自动更新

存储生日的三种方式：

5.7之后（日期格式函数辅助）

Innodb表中的逻辑顺序和主键顺序是相同的，如果主键不是顺序增长，每次插入数据之后可能会重新排序，增加IO消耗

自增ID，业务主键唯一性，增加一个唯一索引