Mysql数据库结构优化

影响Mysql数据库的因素

1. 服务器硬件
2. 操作系统
3. mysql服务器配置
4. 数据库结构（影响最大）

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

数据库结构优化的目的

1. 减少数据的冗余
2. 尽量避免数据维护中出现 更新、插入、删除异常
3. 节约数据存储空间

数据库结构设计的步骤

1. 需求分析：全面了解产品设计的存储需求
2. 逻辑设计：设计数据的逻辑存储结构
3. 物理设计：根据所使用的数据库特点进行表结构设计
4. 维护优化：根据实际情况对索引、存储结构等进行优化

数据库设计范式

数据库设计的第一范式：

• 数据库表中的所有字段都具有单一属性（即字段不能再分割了）
• 单一属性的列是由基本数据类型所构成的
• 设计出来的表都是简单的二维表

数据库设计的第二范式：

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

数据库设计的第三范式：

首先要满足第二范式，其次非主属性之间不存在函数依赖。由于满足了第二范式，表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖，就会存在传递依赖，这样就不满足第三范式。

总结：
第一范式就是原子性，字段不可再分割；
第二范式就是完全依赖，没有部分依赖；
第三范式就是没有传递依赖

反范式化设计

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

不能要求完全按照范式化的要求进行数据库设计，要考虑以后表的使用（查询等）
举个例子：比如订单表中应该保留当前购买商品的价格、商品的名称（商品的价格是会变动的，这很危险）

范式化设计的优缺点
优点：

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

缺点：

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

反范式化设计的优缺点
优点：

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

缺点：

1. 存在数据冗余和数据维护异常
2. 对数据修改需要更多的成本

数据库物理设计

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

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

• 数据库、表、字段的命名要遵守可读性原则
• 数据库、表、字段的命名要遵守表意性原则（采用英文，且意思准确）
• 数据库、表、字段的命名要遵守长名原则（尽量不要使用缩写）

选择合适的存储引擎

物理设计-数据类型的选择

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

如何选择正确的整数类型

---

如何选择正确的实数类型

---

如何选择varchar和char类型

varchar类型特点：

• varchar和char是以字符为单位的，不是字节。一个字符可能占用多个字节
• varchar用于存储变长字符串，只占用的必要的存储空间。

char类型特点：

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

varchar使用场景：

• 适用于存储很少被更新的字符串列

char使用场景：

• 适合存储长度近似的值
• 适合存储短字符串
• 适合存储经常更新的字符串

---

如何存储日期类型

datatime类型

• datatime类型与时区无关，占用8个字节的存储空间
• 存储范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• 如果需要存储微秒时间，则需要指定宽度，比如datetime(6)

timestamp类型

• timestamp类型占用4个字节，显示依赖于所指定的时区
• 范围为1970-01-01 08:00:01到2038-01-19 11:14:07
• TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况
• TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
• TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响

date类型和time类型

• 占用字节比字符串、datetime、int存储类型要少，使用date类型只需要3个 字节
• 使用date类型还可以利用日期和时间函数进行日期之间的计算
• 仅有日期值，没有时间部分。以’YYYY-MM-DD’格式显示DATE值，支持的范围是’1000-01-01’到’9999-12-31’。
• TIME数据类型表示一天中的时间。以”HH:MM:SS”格式显示TIME值。支持的范围是’00:00:00’到’23:59:59’。

使用日期时间存储类型注意事项

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

参考链接

数据库设计三大范式
第五章 SqlServer之数据库三大范式

MySQL之char、varchar和text的设计
Mysql时间字段格式如何选择，TIMESTAMP，DATETIME，INT？