mysql数据库结构优化（笔记）

主要包括数据库结构方面优化的介绍：数据库范式设计，物理设计介绍，数据结构类型

<!--more-->

原文链接：http://fanqieto.top/2017/11/23/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BB%93%E6%9E%84%E4%BC%98%E5%8C%96/

数据库结构优化介绍

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

数据库结构优化的目的

• 减少冗余
• 尽量避免数据库中出现更新、插入和删除异常
  • 插入异常：如果表中的某个实体随着另一个实体而存在
  • 更新异常：如果更新表中的某个实体的单独属性时，需要对多行进行更新
  • 删除异常：如果删除表中的某一实体则会导致其他实体的消失
• 节约存储空间
• 提高查询效率

数据库结构设计

数据库结构设计的步骤

• 需求分析
  • 全面了解产品设计的存储需求
  • 存储需求
  • 数据处理需求
  • 数据的安全性和完整性
• 逻辑设计
  • 设计数据的逻辑存储结构
  • 数据实体之间的逻辑关系，解决数据冗余和数据维护异常
• 物理设计
  • 根据所使用的数据库特点进行表结构设计
    • 关系型数据库：oralce,sqlserver,mysql
    • 非关系型数据库：mongo,redis,hadoop
    • 存储引擎：innodb
• 维护优化
  • 根据实际情况对索引、存储结构等进行优化。

数据库范式设计

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

三范式

• 数据设计的第一范式
  • 数据表中的所有字段都只具有单一属性
  • 单一属性的列是由基本的数据类型所构成的
  • 设计出来的表都是简单的二维表
• 数据库设计的第二范式：要求表中具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系
• 数据库设计的第三范式
  • 指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖。（也就是说表中的字段和主键直接对应不依靠其它的中间字段）

需求分析及逻辑设计

• 关联表越多，性能越差
• 完全符合范式化的设计有时并不能得到良好的sql查询性能

反范式设计

什么叫反范式化设计

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

范式化设计和反范式化设计优缺点

范式化设计优缺点

优点

• 可以尽量减少数据冗余
• 范式化的更新操作比反范式化更快
• 范式化的表通常比反范式化更小

缺点

• 对于查询需要对多个表进行关联
• 更难进行索引优化

反范式化设计优缺点

优点

• 可以减少表的关联
• 可以更好的进行索引优化

缺点

• 存在数据冗余及数据维护异常
• 对数据的修改需要更多的成本

物理设计介绍

定义

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

物理设计涉及的内容

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

命名规范

• 可读性原则
• 表意性原则
• 长名原则

选择合适的存储引擎

数据类型的选择

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

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

如何选择整数类型

如何选择实数类型

注意：decimal(18,9)表示一共有18位数字，小数占9位数字

字符串类型

varchar

注意：定义的宽度是以字符为单位，而不是以字节为单位

varchar类型的存储特点

• varchar 用于存储变长字符串，只占用必要的存储空间
• 列的最大长度小于255则只占用一个额外字节用于记录字符串长度
• 列的最大长度大于255则要占用两个额外字节用于记录字符串长度

varchar长度的选择问题

• 使用最小的符合需求的长度
• varchar(5)和varchar(200)存储“mysql”字符串性能不同

varchar 使用场景

• 字符串列的最大长度比平均长度大很多
• 字符串列很少被更新

char

char类型的存储特点

• char类型是定长的
• 字符串存储在char类型的列中会删除末尾的空格
• char类型的最大宽度为255

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个字节的存储空间

• TIMESTAMP 类型

  • 存储由格林尼治时间1970年1月1日到当前时间的秒数
    • 以YYYY-MM-DD HH:MM:SS.[.fraction]的格式显示， 占用4个字节
    • 时间范围1970-01-01 到2038-01-19
  • timestamp类型显示依赖于所指定的时区
  • 在行的数据修改时可以自动修改timestamp列的值

• date类型的优点

  • 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
  • 使用date类型还可以利用日期时间函数进行日期之间的计算
  • date类型用于保存1000-01-01到9999-12-31之间的日期
• time类型
  • 格式：HH:MM:SS

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

• 不要使用字符串类型来存储日期时间数据
• 日期时间类型通常比字符串占用的存储空间小
• 日期时间类型在进行查找过滤时可以利用日期来进行比对

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

  • 使用int存储日期时间不如使用timestamp类型