mysql建表思路_MySQL 建表思路

思想：硬盘如仓库，表如仓库中货架(常用与不常用等分类)，字段如货物(尺寸是固定或变动)，存取货物涉及到货架的占位、效率。

数据类型选用，建表思路，范式

数据类型特点

数据类型的速度关系

[ 最快 ] 整形 > date,time > char,enum > varchar > text ,blob [ 最慢 ]

char 与 varchar 选择

一般情况下，在100个字符内，可以使用 char 定义。(少量字符在使用率上，定长比变长更具有硬盘性能和低损耗【变长需要根据具体长度计算偏移量】的优势)

数据类型的特点

char(1)：需要经过字符集的转换(排序时涉及字符集校对)。

enum('男','女')：内部使用数值存储，多了一个数值转换

tinyint：无需字符集转换，直接参与算术运算(CPU内部最基础的就是算术及逻辑运算)，速度更快

举例应用：性别(tinyint > enum > char)

够用就好

越大的字段越浪费内存，从而影响性能(无论从磁盘加载到内存的时间，以及内存管理)

列如：varchar(10)与varchar(300)在存储的字符数量一样的情况下，连表查询时以varchar(300)为准，所以花费更多的内存及性能。

null 尽量避免少用

不利于索引

不利于查询：需要专用的关键字实现(is)。例如 where name is NULL 或 where name != NULL

不利用逻辑：select NULL = NULL ;

小数类

double，float 精度有误差(有四舍五入的特点)，不适合作为金额存储

decimal 没有精度误差，适合作为金额存储

日期时间 时间戳 整形

性能：整形(int，bigbit) > 时间日期(datetime) > 时间戳(timestamp)

跨时区：

datetime --> 时间戳 --> 指定时区。不做任何改变，存储与查询一致。

timestamp 按当前环境的时区存储，查询按客户端当前时区返回。(查看MySQL时区变量：select variables like '%time_zone%';)

时间支持：

datetime           支持 9999-12-31 23:59:59

timestamp / int  支持 2038-01-19 03:14:07

综合：使用 datetime 比较省心

建表思路

定长与变长分离。

常用与不常用分离。

高频率用到的信息优先思考效率，不常用的信息优先考虑空间占用。

1对多的关系中，添加冗余字段(这与范式有点违背，但能解决联表查询的性能消耗)。如：联表。

列 类型 注释

id int unsigned primary key

username varchar(20) -- 优化 --> char(20)

gender char(1) -- 优化 --> tinyint

weight tinyint unsigned

birth date

salary decimal(8,2)

lastlogin datetime

intro varchar(1500) -- 优化 --> 将 intro 字段分离该表，另外形成一个新的表(id,username,is_del,intro)

is_del tinyint 是否逻辑删除(1：真)

范式

范式：1NF -> 2NF -> 3NF -> BCNF -> 4NF -> 5NF

实际应用中，使用 3NF 或 BCNF(BCNF可理解为 3NF的补丁版) 已经能够得到很好的结果。4NF以上的范式是特殊情况下才使用。

1NF：若数据表 R 的每一个字段的值为单一的，则 R 属于【第一阶规范化形式】(first normal form)

函数依赖：R(列1，列2，…，列n)为一数据表，且 X，Y 为 {列1，列2，… ，列n}的部分集合，若找不到两处记录，其 X 值相同，Y值不同，则 X 功能上确定 Y 或 Y 函数依赖于 X 。(即 X 字段内容相同记录，Y 字段内容也一样)。如：身份证号(X) 确定了 姓名(Y)

主键：R(列1，列2，…，列n)为一数据表，且 X 为{列1，列2，…，列n}的部分集合；若数据表内所有其他列字段都函数依赖与 X，则 X 为 R 的【主键】。

如：身份证的姓名，地址，出生年月日，等等信息都完全函数依赖于身份证号。也就是说 身份证号是可以代表身份证中的所有字段信息。

1NF ——> 2NF

2NF：若数据表R属于1NF，且所有非主键的字段皆【完全函数依赖】于主键，则R属于【第二阶规范化形式】(second normal form)

2NF ——> 3NF

3NF：若数据表R属于2NF，且所有的非主键的字段无【传递函数依赖】于主键，则R属于【第三阶规范化形式】(Third Normal Form)

2NF，3NF 都是对非主属性(主键)的函数依赖提出的限定，并没有要求消除主属性(主关键字)对候选关键字的传递依赖。BCNF为此而生。

BCNF：数据表 R 的所有属性(含主键，非主键)都不传递依赖于 R 的任何候选关键字，则 R 属于 BCNF。