1. 数据库结构优化介绍

影响数据库性能的因素

- 服务器硬件
- 操作系统
- MySQL 服务器配置

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

数据库结构优化的目的

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

2. 数据库结构设计

  1. 需求分析：全面了解产品设计和存储需求
    存储需求
    数据处理需求
    数据的安全性和完整性
  2. 逻辑设计：设计数据的逻辑存储结构
    数据实体之间的逻辑关系，解决数据冗余和数据维护异常
  3. 物理设计：根据所使用的数据库特点进行表结构设计
    关系型数据库：Oralce,SQLServer,MySQL,postgresSQL
    非关系型数据库：mongo,Redis,Hadoop
    存储引擎：Innodb
  4. 维护设计：根据实际情况对索引、存储结构等进行优化

数据库设计范式：设计出没有数据冗余和数据维护异常的数据库结构
数据库三范式：考虑实际的业务使用情况
第一范式：
1. 数据库中的所有字段都只具有单一属性
2. 单一属性的列是由基本的数据类型所构成的
3. 设计出来的表都是简单的二维表
第二范式：
要求一个表中只具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列只对部分主键的依赖关系
第三范式：
指每一个非主属性既不部分依赖于也不传递依赖与业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖

3. 需求分析及逻辑设计

无

4. 需求分析及逻辑设计–反范式化设计

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

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

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

  • 范式化：
    • 优点：
      • 可以尽量的减少数据冗余
        数据表更新快体积小
      • 范式化的更新操作比反范式化更快
      • 范式化的表通常比反范式化更小
    • 缺点：
      • 对于查询需要对多个表进行关联
      • 更难进行索引优化
  • 反范式化：
  • 优点：
  • 可以减少表的关联
  • 可以更好的进行索引优化
  • 缺点：
    • 存在数据冗余及数据维护异常
    • 对数据的修改需要更多的成本

高性能的数据库结构：反范式化 + 范式化结合

6. 物理设计介绍

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

物理设计涉及的内容：

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

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

  1. 数据库、表及字段的命名要遵守 可读性原则
  2. 数据库、表及字段的命名要遵守 表意性原则
  3. 数据库、表及字段的命名要遵守 长名原则

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

7. 数据类型的选择

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

整数类型：
整数类型.png

实数类型：
实数类型.png

VARCHAR和CHAR类型：
VARCHAR类型的存储热点：

  • varchar 用于存储变长字符串，只占用必要的存储空间

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

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

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

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

VARCHAR的适合场景：

  • 字符串列的最大长度比平均长度大很多
  • 字符串列很少被更新
  • 使用了多字节字符串集存储字符串

CHAR类型的存储特点：

  • CHAR类型时定长的
  • 字符串存储在CHAR类型的列中会删除末尾的空格
  • CHAR类型的最大宽度是255
    CHAR类型的适合场景
  • CHAR类型适合存储所长度近似的值
  • CHAR类型适合存储短字符串
  • CHAR类型适合存储经常更新的字符串列

8.如何存储日期数据：

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类型（mysql5.6+以后）
date类型用于保存1000-01-01到9999-12-31之间的日期
time类型用于存储时间数据，格式为HH:MM:SS

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

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

9. 总结

数据库物理设计

  • 存储空间
  • 存储引擎
    • 没有特殊需求建议使用 Innodb
      • 为表中的每个列选择合适的类型
      • 如何选择表的主键
        • 主键应该尽可能的小
        • 主键应该是顺序增长的
        • Innodb的主键和业务主键可以不同
  • 数据类型