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。