mysql 逻辑型字段_MySQL学习(二)：数据结构优化、字段类型

介绍了一些关于数据库设计的内容，以及关于字段类型选择的内容；

后面会介绍MySQL的索引优化、查询优化、分库分表、监控等；

优化目的假设有 课程选修表(一个学生同一门课一条记录)(学号(主键)、姓名、年龄、课程名(主键)、成绩、学分)减少数据冗余：同一学生选择多门课程，姓名、年龄就重复n次

避免在数据维护中出现更新、插入和删除异常

插入异常：表中的某个实体随着另一个实体而存在增加一门课程时，如果还没有学生选修，由于没有学号，则该课程无法插入

更新异常：更改表中的某条记录单个字段时，需要对多行进行更新修改一学生年龄，需要将该学生的所有记录的年龄都修改

删除异常：如果删除表中某一记录，会导致其他记录的消失如果删除了部分学生，导致没人选择 a课程，则引发插入异常

节约数据存储空间

提高查询效率

数据库结构设计的步骤需求分析：全面了解产品设计的存储需求

逻辑设计：设计数据的逻辑存储结构；数据实体间的逻辑关系，解决数据冗余和数据维护异常

物理设计：根据所使用的数据库特点进行表结构设计

维护优化：根据实际情况对索引、存储结构进行优化

数据库设计范式

设计出没有数据冗余和数据维护异常的数据库结构第一范式(基本都满足)：数据库表中所有字段只具有单一属性

且单一属性的列由基本数据类型组成

应该是简单的二维表

第二范式(单一主键都满足，复合主键下可能不满足)：

一个表中只有一个业务主键，也就是不能存在非主键字段对只对部分主键的依赖关系例如上面的表中，学分字段只和课程名有关系，一门课程多少学分和学生是无关的

可以通过将该表拆分为 学生表、课程表、学生课程关系表 来优化

第三范式：

在第二范式的基础上，还消除了非主键属性对主键的传递依赖例如拆分后的学生表如果还有 学校名、学校地址两个字段，则不符合， 应该继续拆分出学校表，学生表中只维护学校表id；

反范式化设计

(空间换时间)为了性能，不遵守范式化设计，允许存在少量的数据冗余完全符合范式化的设计，有时不能得到良好的SQL查询性能，往往一个查询需要关联过多的表

例如 商品表、商品分类表、商品-分类-关联表，在商品表中不仅保存商品分类id，还冗余商品分类名；

这样就将原本的三张表关联查询，减少到了一张

范式化优点数据冗余少

更新操作比反范式化快

表通常比反范式化小(单表字段少)

范式化缺点查询慢，查询需要关联多个表

更难索引优化

反范式化优点查询快，减少表的关联

更好的索引优化

反范式化缺点存在数据冗余和维护异常

对数据的修改需要更多的成本

物理设计的内容定义数据库、表及字段的命名规范

选择合适的存储引擎，如下：

为字段选择合适的数据类型一个列可以选择多种类型时，优先考虑数字类型，其次是日期或二进制类型，最后是字符类型；同类型选择空间占用少的

整数类型

(可以使用无符号整数类型，提高存储范围)(类似int(2)不会减少其真正存储空间，1只是表示显示的长度为1位),如下：

实数类型

(金额推荐用DECIMAL)，如下：

字符类型(其长度以字符为单位，而不是字节)：VARCHAR变长字符串，只占用必要的存储空间

列最大长度小于255，则只占用一个额外字节存储字符串长度；大于则占用2个

使用最小的符合需求的长度；因为该类型在MySQL内存中，存储的还是其定长长度

适用最大长度比平均长度大很多的字段

适用于更新少的字段(更新多会产生碎片)

适用使用多字节字符集存储字符串

CHAR定长

字符串存储其中，会删除末尾空格(varchar中不会)

最大宽度为255

适用于存储长度相近的字段

适用存储短字符串(varchar需要额外1字节存储长度)

适用于存储经常更新的列

日期类型DATATIME:以YYYY-MM-DD HH:MM:SS[.fraction] 格式存储时间(fraction:微秒)

默认没有微秒，如果需要存储微秒，需要定义 DATATIME(6)

与时区无关，只占用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个字节

如果需要存储微秒，需要定义 TIMESTAMP(6)

范围：1970-01-01 - 2038-01-19

显示依赖所指定的时区

在行的数据修改时可以自动修改timestamp类型列的值(修改时间字段无需自己维护)(只有一个timestamp能自动更新，默认第一个该类型列)

DATE:存储YYYY-MM-DD 只需3个字节

1000-01-01 - 9999-12-31

TIME:存储HH:MM:SS[.fraction]

存储日期时间数据注意事项：不要使用字符类型保存日期时间数据

日期时间类型比字符类型占用空间小

查询过滤时可以进行对比

有丰富的日期函数

使用int存储时间不如使用timestamp

为Innodb选择主键主键应该尽可能的小

主键应该是顺序增长的

主键和业务主键可以不同