mysql数据库设计 14

数据库设计三大范式

在设计关系数据库的时候，一般来说我们都是需要遵从不同的规范要求来设计出合理的关系型数据库，这些不同的规范要 求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
范式分为：3大范式，以及BC范式，第四范式还有第五范式 一共六大范式通常来说满足与三大范式就基本足够 ；
注意：项目的数据库设计并不一定要完全满足与三大范式，有些时候我们会适量的冗余让Query尽两减少Join
误区:不是范式越高越就越好 好 => 结构清晰
早期:希望数据可以足够的小数据量不是问题主要分问题
现在:希望查询速度越快越好,同时操作越简单越好

第一范式（1NF）

简单地说，第一范式要求关系中的属性必须是原子项，即不可再分的基本类型，集合、数组和 结构不能作为某一属性出现，严禁关系中出现“表中有表”的情况在任何一个关系数据库系统中，第一范式是关系模 式的一个最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。

原始表中，其中”工程地址”列还可以细分为省份，城市等。在国外，更多的程序把”姓名”列也分成2列,即”姓”和“名”。

虽然第一范式要求各列要保存原子性，不能再分，但是这种要求和我们的需求是相关联的，如上表中我们对”工程地址”没有省份，城市这样方面的查询和应用需求，则不需拆分，”姓名”列也是同样如此。

原始表:

工程号	工程名称	工程地址	员工编号	员工名称	薪资待遇	职务
P001–	– 港珠澳大桥–	– 广东珠海–	– E0001–	– Jack –	–6000/月 –	–工人
P001	港珠澳大桥	广东珠海	E0002	Join	7800/月	工人
P001	港珠澳大桥	广东珠海	E0003	Apple	8000/月	高级技工
P002	南海航天	海南三亚	E0001	Jack	5000/月	工人

第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础建立起来的，既满足第二范式（2NF）就必须要 满足第一范式。第二范式（2NF）要求实体的属性完全依赖于主键字。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

例如：原始表中描述了工程信息，员工信息等。这样就造成了大量数据的重复。按照第二范式，我们可以将原始表分为工程信息表与员工信息表

工程信息表：

工程编号	工程名称	工程地址
P001	港珠澳大桥	广东珠海
P002	南海航天	海南三亚

员工信息表：

员工编号	员工姓名	职务	薪资水平
E0001	Jack	工人	3000/月
E0002	Join	工人	3000/月
E0003	Apple	高级技工	6000/月

第三范式（3NF）

第三范式（3NF）是第二范式的子集，既满足第三范式就必须满足第二范式。意思是不存在非 关键字段对任意候选关键字段的传递函数依赖

例如：
现在我们来看看在第二范式的讲解中，我们将表1-1拆分成了两张表。这两个表是否符合第三范式呢。在员工信息表中包含：”员工编号”、”员工名称”、”职务”、”薪资水平”，而我们知道，薪资水平是有职务决定，这里”薪资水平”通过”职务”与员工相关，则不符合第三范式。我们需要将员工信息表进一步拆分，如下:

员工信息表：

员工编号	员工姓名	职务编号
E0001	Jack	1
E0002	Join	1
E0003	Apple	2

工程信息表：

工程编号	工程名称	工程地址
P001	港珠澳大桥	广东珠海
P002	南海航天	海南三亚

职务表(Duty)

职务编号	职务名称	工资待遇
1	工人	3000/月
2	高级技工	6000/月

工程参与人员记录表：

编号	工程编号	人员编号
1	P001	E0001
2	P001	E0002
3	P002	E0003

通过对比我们发现，表多了，关系复杂了，查询数据变的麻烦了，编程中的难度也提高了，但是各个表中内容更清晰了， 重复的数据少了，更新和维护变的更容易了。

数据库表字段类型分析

字符串类型

char 和 varchar都是用来存储字符串类型的数据,但是他们保存和检索的方式不一样.
char属于固定长度的字符类型,
varchar属于可变长的字符类型

gbk,1个字符，两个字节

值	char(4)	字节	varchar(4)	字节
"	‘’	8个字节	"	1个字节
‘ab’	‘ab ’	8个字节	‘ab’	5个字节
‘abcd’	‘abcd’	8个字节	‘abcd’	9个字节
‘abcdefg’	‘abcd’	8个字节	‘abcd’	9个字节

utf8(1个字符，两个字节

值	char(4)	字节	varchar(4)	字节
"	‘’	12个字节	"	1个字节
‘ab’	‘ab’	12个字节	‘ab’	7个字节
‘abcd’	‘abcd’	12个字节	‘abcd’	13个字节
‘abcdefg’	‘abcd’	12个字节	‘abcd’	13个字节

由于char是固定长度的,所以它的处理速度比varchar快得多,但是其缺点是浪费存储空间,程序需要对尾行空格进行处理,所以对那些变化不打并且查询速度有较高的 要求的数据可以考虑使用char类型来存储

在mysql中,不同的存储引擎对char和varchar的使用原则有所不同

MyISAM存储引擎
建议使用固定长度的数列代替可变长度的数据列

InnoDB存储引擎
建议使用varchar类型,对于InnnoDB数据表,内部的行存储格式没有区分固定长度和可变长度,因此使用char列不一定比 可变长度的varchar性能好
由于char平均占用空间多余varchar,因此varchar来UI消化需要处理的数据航的存储总量和磁盘I/O是比较好的.

数字类型

数字类型解释 https://blog.csdn.net/zgmu/article/details/52118165

数据类型	范围	字节
bigint	-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)	8 字节
| 0到18446744073709551615	较大整数
int	-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)	4 个字节
| 0到4294967295	标准整数
smallint	-2^15 (-32,768) 到 2^15-1 (32,767)	2 字节
| 0到65535	较小整数
tinyint	-2^7 (-128) 到 2^7 - 1 (123)	1 字节
| 0到255	非常小的整数
float[(m,d)]	±1.175494351e - 38	4字节
double[(m, d)]	±2.2250738585072014e - 308	8字节
decimal (m,d)	可变；其值的范围依赖于m 和d	m+2字节

id 必为主键，类型为int bigint unsigned、单表时自增、步长为 1; 注意一下因为一些表可能因为数据量的关系导致主键会很大可能会超出int的范围这个时候就比较建议使用bigint通常int即可

注：不过当一个表数据量超过了500万的时候或者单表容量超过2GB的时候推介分库分表；这一步操作是需要实先对于数据量在项目上线之后的思考点

UNSIGNED属性就是将数字类型无符号化, unsigned的使用

注意 unsigned tinyint 的范围就是 0-255

CREATE TABLE `yyy` (
`id` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> desc yyy;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into yyy values(255);
Query OK, 1 row affected (0.06 sec)
mysql> select * from yyy;
+-----+
| id  |
+-----+
| 255 |
+-----+
1 row in set (0.00 sec)
mysql> insert into yyy values(256);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into yyy values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into yyy values(0);
Query OK, 1 row affected (0.09 sec)

人的年龄用 unsigned tinyint（ 表示范围 0-255，人的寿命不会超过 255 岁 ）

status,sexuality 相对来说在系统中我们更愿意使用数字作为表示，代替实际该字段所传单的意思，因为char所占用 的3个字节，相对来说比较适合于unsigned tinyint

时间类型

注意： 默认情况下，当MySQL遇到超出范围的日期或时间类型的值或该类型的其他无效值时，它会将该值转换为“零”值的值。唯 一的例外是超出了范围。TIME值被裁剪 到TIME范围。

MySQL允许在DATE或DATETIME列。这对于需要存储您可能不知道确切日期的生日的应用程序非常有用。在这种情况下， 您只需将日期存储为’2009-00- 00’或’2009-01-00’。如果存储这样的日期，就不应该期望得到正确的结果，例 如DATE_SUB()或DATE_ADD()需要完整的日期。若要在日期中不允许零个月或日部分，请启用NO_ZERO_IN_DATE模式 。

MySQL允许您存储“零”价值’0000-00-00’作为“假约会。”在某些情况下，这比使用NULL值，并使用较少的数据和索引空 间。不允许’0000-00-00’，启用NO_ZERO_DATE模式。

DATETIME

DATETIME 用于表示 年月日 时分秒，是 DATE 和 TIME 的组合，并且记录的年份（见上表）比较长久。如果实际应用 中有这样的需求，就可以使用 DATETIME 类型。
1、 DATETIME列可以设置为多个，默认可为null，可以手动设置其值。
2、 DATETIME列不可设定默认值。
3、 DATETIME列可以变相的设定默认值，比如通过触发器、或者在插入数据时候，将DATETIME字段值设置为now()，这样可以做到了，尤其是后者，在程序开 发中常常用到。

TIMESTAMP

TIMESTAMP 用于表示 年月日 时分秒，但是记录的年份（见上表）比较短暂，TIMESTAMP列必须有默认值，默认值可以 为“0000-00-00 00:00:00”，但不能为null。
TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为 本地时区后再存放；当查询日期时，会将日期转换为 本地时区后再显示。所以不同时区的人看到的同一时间是 不一样的 。表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。当插入 或更新一行，但没有明确给TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

注：一般建表时候，创建时间用datetime，更新时间用timestamp。

DATE

DATE 用于表示 年月日，如果实际应用值需要保存 年月日 就可以使用 DATE。

TIME

TIME 用于表示 时分秒，如果实际应用值需要保存 时分秒 就可以使用 TIME。

YEAR

YEAR 用于表示 年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。 默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类 型完全可以。不但能够节约存储空间，还能提高表的操作效率。

不推荐存储的数据类型

二进制多媒体数据 将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是 这些数据的存储很消耗数据库主机的CPU 资源。这种数据主要 包括图片，音频、视频和其他一些相关的二进制文件。 这些数据的处理本不是数据的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处理资源消耗 严重。

流水队列数据 我们都知道，数据库为了保证事务的安全性（支持事务的存储引擎）以及可恢复性，都是需要记录所 有变更的日志信息的。而流水队列数据的用途就决定了存放 这种数据的表中的数据会不断的被 INSERT，UPDATE 和 DELETE，而每一个操作都会生成与之对应的日志信息。在 MySQL 中，如果是支持事务的存储引擎，这 个日志的产生量 更是要翻倍。而如果我们通过一些成熟的第三方队列软件来实现这个 Queue 数据的处理功能，性能将会成倍的提升。

超大文本数据 对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需要存储 更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大 可存放 64KB）的字段，甚至是更大的LONGTEXT 类型 （最大 4GB）。而 TEXT 类型数据的处理性能要远比 VARCHAR 类型数据的处理性能低下很多。从 5.0.3 版 本开始 ，VARCHAR 类型的最大长度被调整到 64KB 了，但是当实际数据小于 255Bytes 的时候，实际存储空间和实际的数据长 度一样，可一旦长度超过 255 Bytes 之后，所占用的存储空间就是实际数据长度的两倍。 对于图片的存储，如果说是 特殊情况可以使用BLOB，但是通常来说跟推介使用varchar存图片路径，而图片会放在一个文件夹中

下一篇：mysql主从复制与备份 15
上一篇：mysql 问题处理 13