Mysql表结构设计和数据类型优化（二）-优快云博客

数据库表设计

范式设计
数据库设计的第一范式
定义: 属于第一范式关系的所有属性都不可再分，即数据项不可分。
理解: 第一范式强调数据表的原子性，是其他范式的基础。例如下表
在这里插入图片描述
name-age 列具有两个属性，一个 name,一个 age 不符合第一范式，把它拆分成两列

数据库设计的第二范式
第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。通常在实现来说，需要为表加上一个列，以存储各个实例的惟一标识。
在这里插入图片描述
取而代之，用中间表。

数据库设计的第三范式
指每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键对主键的传递依赖。例如，存在一个部门信息表，其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式(3NF)也应该构建它，否则就会有大量的数据冗余。

反范式设计

完全符合范式化的设计真的完美无缺吗?很明显在实际的业务查询中会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能。
所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间。

反范式设计-商品信息
下面是范式设计的商品信息表
在这里插入图片描述

字段数据类型优化

尽量避免 NULL

很多表都包含可为 NULL(空值)的列，即使应用程序并不需要保存 NULL 也是如此，这是因为可为 NULL 是列的默认属性。通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值。
如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节。
通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小，所以(调优时)没有必要首先在现有 schema 中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

Int/整数类型

int(1)和int(11)在存储上没有区别，在磁盘上都是占用4byte。在有符号（允许负数）情况下int(1)和int(11)的最大值都是4294967295（共计10位）
无符号：unsigned
例子：id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键id’;
在这里插入图片描述根据上面的结论int(11)和int(1)表示的数字的范围是一样的，那么设置int(M)中的M的意义是什么呢？其实设置M得和zerofill结合起来才会生效。
如果勾上了zerofill这个参数,相当于sql脚本：id int(4) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT ‘主键id’;

然后我们先来插入几条数据。
INSERT INTO test (id) VALUES (1),(10),(100),(1000);
我们来查询下
在这里插入图片描述
通过数据可以发现 int(4) + zerofill实现了不足4位补0的现象，单单int(4)是没有用的。而且对于0001这种，底层存储的还是1，只是在展示的会补0。

总结
int(M)中的M并不能表示数字的长度,int(M)得和zerofill配合使用，才有效果，简单说就是零填充的作用

实数类型

实数是带有小数部分的数字。

MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多 65 个数字。

字符串类型

VARCHAR 类型
VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间(例如，越短的字符串使用越少的空间)。

CHAR 类型
CHAR 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL 会删除所有的末尾空格，CHAR 值会根据需要采用空格进行填充以方便比较。

小结
char最大长度255个字符，varchar最大长度是21845个字符（根据编码来算，如果是utf-b，则最大长度是21845，因为最大字节数是65535）。
在 CHAR 和 VARCHAR 的选择上，这些情况下使用 VARCHAR 是合适的:
字符串列的最大长度比平均长度大很多;列的更新很少;使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR 适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如， CHAR 非常适合存储密码的 MD5 值，还有手机号，身份证等。因为这是一个定长的值。对于经常变更的数据，CHAR 也比 VARCHAR 更好，因为定长的 CHAR 类型不容易产生碎片。
对于非常短的列，CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR( 1)来存储只有 Y 和 N 的值，如果采用单字节字符集只需要一个字节，但是 VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
另外，使用 VARCHAR(5)和 VARCHAR(200)存储’hello’在磁盘空间上开销是一样的。我们随便选择一个就好?应该使用更短的列，为什么?
事实证明有很大的优势。更长的列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
所以最好的策略是只分配真正需要的空间。

八大基本数据类型占用字节数
在这里插入图片描述
日期和时间类型