思想:硬盘如仓库,表如仓库中货架(常用与不常用等分类),字段如货物(尺寸是固定或变动),存取货物涉及到货架的占位、效率。
数据类型选用,建表思路,范式
数据类型特点
数据类型的速度关系
[ 最快 ] 整形 > 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。