[关闭]
@EncyKe 2016-10-26T14:31:49.000000Z 字数 3667 阅读 4849

数据库设计基础

数据库



1. 基础

1.1. 数据库设计步骤

  1. 需求分析(根据数据的属性和特点设置数据类型);
  2. 逻辑设计(ER 图);
  3. 物理设计(选择开发环境);
  4. 维护优化(新建表、索引、拆分);

1.2. 术语

关系
一个关系对应通常所说的一张表;
元组
表中的一行为一个元组;
属性
表中的一列为一个属性;每一个属性有一个名称为属性名(表字段);
候选码
表中的某个属性级,它可以唯一确定一个元组;
主码
一个关系有多个候选码,选定其中一个为主码;
属性的取值范围(e.g.: 真假:Y/N...);
分量
元组中的一个属性值;

1.3. ER 图注

矩形:实体集;
菱形:关系集;
椭圆:属性;
线:关系;

2. 需求分析

2.1. 要点

2.2. 表的实体关系

合理分库、分表。按期归档,使数据库高效运转。

3. 逻辑设计

3.1. 数据冗余

数据冗余
指相同的数据在多个地方存在(多个表存在,一个表多个字段意义相同),或者说某个列可以由其他列计算得到,这样就说表中存在着数据冗余(不符合范式要求既有数据冗余)。

可包括——

3.2. 设计范式

3.2.1. 第一范式 (1NF)

定义
数据库表中所有字段均为 单一属性,且 不可再分
补充
单一属性指由基本的数据类型(如:整数、浮点数、字符串等)构成;
释义
表都必须是二维表;不可表中套表;

3.2.2. 第二范式 (2NF)

定义
数据库表中不存在非关键字段对任一候选关键字段的 部分函数依赖
补充
部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况;
释义
表都必须是单关键字段的表;
问题
不符合 2NF 时可能产生的问题:插入异常、更新异常、删除异常、数据冗余;
解决
拆分成单关键字的表(两个表 + 一个关系表);

3.2.3. 第三范式 (3NF)

定义
若数据表不存在非关键字段、也不存在对任意候选关键字段的 传递函数依赖 则符合 3NF;
释义
不存在非主属性部分函数依赖于码,同时不传递依赖与码;
问题
不符合 3NF 时可能产生的问题:插入异常、更新异常、删除异常、数据冗余;
解决
拆分成单关键字的表(两个表 + 一个关系表);

3.2.4. BC 范式 (BCNF)

定义
在 3NF 基础上,若不存在任何字段对任一候选关键字段的传递函数依赖则符合 BCNF;
释义
若有复合关键字,则复合关键字之间不能存在函数依赖关系(不可一词多义);

3.2.5. 第四范式

暂无简介

3.2.6. 第五范式

暂无简介

4. 物理设计

4.1. 设计步骤

  1. 选择合适的数据库管理系统:
    a. 商业数据库(企业级项目):Oracle、SQL Server;
    b. 开源数据库(互联网项目):MySQL、PgSQL;
  2. 设定数据库表及字段命名、数据库设计规范;
  3. 由所选 DBMS 选用合适的数据类型;
  4. 反范式设计:过分要求范式设计必定会增加关系度的复杂,应在范式与简约节时的原则上找到平衡;

4.2. MySQL 初步

4.2.1. 存储引擎

MySQL常用存储引擎
大多数互联网应用建议使用 Innodb;

4.2.2. 命名规范

4.2.3. 字段类型选用

字段类型选用原则

  1. 数据查询性能:对数据进行比较(查询条件、JOIN 条件及排序分组等)操作时,同样的数据,数字比字符处理要快;
  2. 存储空间开销:在数据库中,数据处理以页为单位,列的长度越小,字节越小,利于 I/O 性能提升(单页 SQL Server: 8K、MySQL: 16K);
  3. 数据类型优先级:数字 > 日期 = 二进制类型 > 字符类型;相同级别的数据类型,优先选择占用空间小的数据类型;

4.2.4. 字符:char/varchar

  1. 如果列中要存储的数据长度差不多一致,则使用 char;否则考虑 varchar(e.g.: 手机号/身份证号码);
  2. 如果列中的最大数据长度小于 50 Byte,一般考虑用 char(如果这个列很少用,则基于节省空间和减少 I/O 的考虑,也可以用 varchar);
  3. 一般不宜定义大于 50 Byte 的 char 类型列;

4.2.5. 数字:decimal/float

  1. decimal 用于存储精确数据,而 float 只能用于存储非精度数据;
  2. 由于 float 的存储空间开销一般比 demimal 小;故非精度数据优先选择 float

4.2.6. 时间:int/datetime**

  1. int:字段长度比 datetime 小;使用不方便,要进行函数转换,且只能存储到 2038-01-19 11:14:07;使用、查询少宜用 int
  2. datetime:查询频繁的时间戳;
  3. 需要存储的时间粒度:年月日时分秒周;

4.2.7. 其它

字符
计算机中使用的文字和符号;
字节
计量单位;
  1. ASCII 编码中,一个英文字母(不分大小写)占一个字节的空间,一个中文汉字占两个字节的空间。一个二进制数字序列,在计算机中作为一个数字单元,一般为 8 位二进制数,换算为十进制。最小值 0,最大值 255。
  2. UTF-8 编码中,一个英文字符等于一个字节,一个中文(含繁体)等于三个字节。
  3. Unicode 编码中,一个英文等于两个字节,一个中文(含繁体)等于两个字节。符号:英文标点占一个字节,中文标点占两个字节。举例:英文句号“.”占 1 个字节的大小,中文句号“。”占 2 个字节的大小。
  4. UTF-16 编码中,一个英文字母字符或一个汉字字符存储都需要 2 个字节(Unicode 扩展区的一些汉字存储需要4个字节)。
  5. UTF-32 编码中,世界上任何字符的存储都需要 4 个字节。

4.2.8. 主键选用

选择原则
避免使用外键约束
避免使用触发器
关于预留字段

4.2.9. 反范式化设计

为了性能和读取效率的考虑而适当的对第三范式的要求进行违反,允许存在少量的数据冗余;即以空间换时间。

5. 维护优化

5.1. 维护要点

5.2. 维护数据字典

5.3. 维护索引

5.4. 维护表结构

注意事项

  1. 使用在线变更表结构工具;
    MySQL 5.5 之前以使用 pt-online-schema-change;
    MySQL 5.6 之后本身支持在线表结构的变更;
  2. 同时对数据字典进行维护;
  3. 控制表的宽度和大小(表字段的大小控制,表数据量的分区,拆分处理等);

适合的操作

  1. 批量操作和逐条操作;
  2. 禁止使用 select * 这样的查询(把不必要的字段也查询出来,浪费 I/O);
  3. 控制使用用户自定义函数(索引失效);
  4. 不要使用数据库中的全文索引;

5.5. 表的拆分

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注