数据库设计

文本是对 慕课网 数据库那些事儿 课程的总结,并根据内容进行一定的解释和扩充.自用.如有不对请斧正.

简介

根据业务系统的具体需要,结合所选的DBMS(数据库管理系统),为这个业务系统构造出最优的数据存储模型.并建立好数据库中的表结构及表与表之间的关联关系的过程.使之能有效的对应用系统中的数据进行存储,并可以高效的对已经存储的数据进行访问.

优良的设计的特点

1. 减少数据冗余
2. 避免数据维护异常
3. 节约存储空间
4. 高效的访问

步骤

1. 需求分析

1. 了解系统中所要存储的数据
2. 了解数据的存储特点
3. 了解数据的生命周期

要搞清楚的一些问题

1. 实体及实体之间的关系(1对1,1对多,多对多)
2. 实体所包含的属性有什么
3. 哪些属性或属性的组合可以唯一标识一个实体

2. 逻辑设计

1. 将需求转化为数据库的逻辑模型
2. 通过ER图的形式对逻辑模型进行展示
3. 同所选用的具体的DBMS系统无关

名词	解释
关系	一个关系对应通常所说的一张表
元祖	表中一行即为一个元祖
属性	表中的一列即为一个属性,每一个属性都有一个属性名
候选码	表中的某个属性组,可以唯一确定一个元祖
主码	一个关系有多个候选码,选定一个为主码
域	属性的取值范围
分量	元祖中的一个属性值

设计范式(重点为前三个)

操作异常

• 插入异常:如果某实体随着另一个实体的存在而存在,即缺少某个实体无法表示这个实体,那么这个表存在插入异常
• 更新异常 如果更改表所对应的某个实体实例的单独属性时,需要将多行更新,那么就说这个表存在更新异常
• 删除异常: 如果删除表的某一行来反应某实体实例失效时导致另一个不同实体实例信息丢失,那么存在删除异常
  数据冗余:相同的数据在多个地方存在,或者某个列可以由其他列计算得到

第一范式(基本可忽略..大部分数据库都是二维表)

数据库表中的所有字段都是单一属性,不可再分的.这个单一属性是由基本的数据类型所构成的.即要求数据库中的表都是二维表

第二范式

数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖(存在着组合关键字中的某一关键字决定非关键字的情况),推导出=>所有单关键字段的表都符合第二范式

商品名称	供应商名称	价格	描述	重量	供应商电话	有效期	分类
可乐	饮料一厂	3.00		250g	88888888	2014.12	饮料
可乐	饮料二厂	3.00		250g	66666666	2014.12	饮料

如表格所示:表中元祖可由'商品名称'+'供应商名称'两列组合关键字决定.其中,非关键字段,'价格','描述','重量','有效期','分类'几列都由'商品名称'决定,同样的,'供应商电话'由'供应商名称'列所决定.我们就说这张表不符合第二范式要求.
举例他存在的操作异常:

1. 插入异常:如图表中饮料一厂不存在可乐这个商品,那么饮料一厂的信息在该表中就不存在了.缺少了对应的商品,供应商的信息也没有了
2. 更新异常:想要更新饮料一厂的供应商电话,需要把存在饮料一厂的每一个元祖都更新.
3. 删除异常:同插入异常,如图表中饮料一厂不存在可乐这个商品,那么饮料一厂的信息在该表中就不存在了.
4. 数据冗余:饮料一厂的信息在他的每个商品中都存在,且是相同的,占用了不必要的空间.
   第三范式

第三范式在第二范式的基础之上定义,数据表中不存在非关键字段对任意候选关键字段的传递函数依赖,则符合第三范式

商品名称	价格	描述	重量	有效期	分类	分类描述
可乐	3.00		250g	2014.12	酒水饮料	碳酸饮料
苹果	8.00		500g		生鲜食品	水果

此表中,商品名称决定了他的分类,分类呢又决定了分类描述,且分类无法确定商品名称,商品名称不包含分类,这张表存在传递函数依赖,所以这张表不满足第三范式
如果有分类'手机数码',但是因为没有对应的商品,那么这个分类就不存在于数据表中(插入异常),删除了可乐这一项,那么酒水饮料这个分类的信息也没了(删除异常),同理还有更新异常.
BC范式

第三范式基础之上,数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式,也就是说如果是符合关键字,则符合关键字之间也不能存在函数依赖关系

供应商	商品ID	供应商联系人	商品数量
饮料一厂	1	张三	10
饮料一厂	2	李四	20
饮料二厂	1	王五	20

存在供应商列决定了供应商联系人,或者说供应商联系人决定了供应商的情况,所以不满足BC范式.

3. 物理设计

根据数据库自身的特点把逻辑设计转换为物理设计

1. 选择合适的数据库管理系统
   比如:根据系统(SQLServer只能在windows下),后台代码的语言,(SQLServer更适合.NET),价格(oracle,SQLServer收费,MySQL免费),数据库的性能(Oracle处理大的事务,性能最好)等

mysql常用的存储引擎

存储引擎	事务	锁粒度	主要应用	忌用
MyISAM	不支持	支持并发插入的表级锁	select,insert	读写操作频繁
MRG_MYISAM	不支持	支持并发插入的表级锁	分段归档,数据仓库	全局查找过多的场景
Innodb	支持	支持MVCC的行级锁	事务处理	无
Archive	不支持	行级锁	日志记录,只支持insert,select	需要随机读取,更新,删除
Ndb cluster	支持	行级锁	高可用性	大部分应用

MRG_MYISAM把多个MyISAM表合成一个处理,基于MyISAM
MySQL集群时,数据存在内存中,数据量大时,不适合Ndb cluster存储引擎

2. 定义数据库、表及字段的命名规范

1. 可读性原则
   使用大小写来格式化的库对象名字以获得良好的可读性
2. 表意性原则
   对象的名字应该能够描述它所标识的对象
3. 长名原则
   尽可能少使用或不使用缩写

3. 根据所选的DBMS系统选择合适的字段类型
   选择原则
   优先选择数字类型,其次是日期或二进制类型,最后是字符类型.
   对于相同级别的数据类型,优先选择占用空间小的数据类型

列类型	存储空间
TINYINT	1个字节
SMALLINT	2个字节
MEDIUMINT	3个字节
INT	4个字节
BINGINT	8个字节
DATE	3个字节
DATETIME	8个字节
TIMESTAMP	4个字节
CHAR(M)	M字节,1<=M<=255
VARCHAR(M)	L+1字节,在此L<=M和1<=M<=255

主要考虑角度:

1. 在对数据进行比较(查询条件,join条件及排序)操作时,同样的数据,字符处理往往比数字处理慢
2. 在数据库中,数据处理以页为单位(Mysql为16k一页),列的长度越小,越有利于性能提升.目前数据库的性能瓶颈主要在数据IO上

char 与varchar如何选择

1. 如果列中要存储的数据长度差不多是一致的,应该考虑char,否则考虑varchar
2. 如果列中的最大数据长度小于50Byte,则一般考虑char(当然,如果这个列很少用,则基于节省空间和减少IO的考虑,还是可以选用varchar)
3. 一般不宜定义大于50Byte的char类型列
   (mysql一个字符是占用3个字节)

decimal 与float如何选择

1.decimal用于存储精确数据,而float 只能用于存储非精确数据.故精确数据类型只能选用decimal类型

2. 由于float 的存储空间开销一般比decimal小(精确到小数7位只需要4个字节,而精确到15位小数只需要8字节),故非精确数据优先选择float类型

时间类型如何存储

1.使用int来存储时间字段的优缺点

• 优点:字节长度比datetime小
• 缺点:使用不方便,要进行函数转换
• 限制,只能存储到2038-1-19 11:14:07即2^32

2. 考虑需要存储的时间粒度
   年月日小时分秒周
   比如年(year)这类数据,只占一个字节,如果只需要保存年份信息,那么用它更好

其他注意点
如何选择主键

1. 区分业务主键和数据库主键
   业务主键用于标识业务数据,进行表与表之间的关联;
   数据库主键为了优化数据存储(innodb会生成6字节的隐含主键,建议人为定义主键),通常业务主键和数据库主键是相同的
2. 根据数据库的类型,考虑主键是否要顺序增长
   有些数据库是按主键的顺序逻辑存储的
3. 主键的字段类型所占用空间要尽可能小
   对于使用聚集索引方式存储的表,每个索引后都会附加主键信息

避免使用外键约束

1. 降低数据导入的效率
2. 增加维护成本
3. 虽然不建议使用外键约束,但是相关联的列上一定要建立索引

避免使用触发器

1. 降低数据导入的效率
2. 可能会出现意想不到的数据异常
3. 使业务逻辑变得复杂.新来的程序员不知道触发器等等

关于预留字段

1. 无法准确的知道预留字段的类型
2. 无法准确的指导预留字段中所存储的内容
3. 后期维护预留字段所要的成本,同增加一个字段所需要的成本是相同的
4. 严禁 使用预留字段

4. 反范式化设计
为了性能和读取效率的考虑,而适当的违反第三范式的要求,允许存在少量数据冗余,使用空间换时间.

1. 减少表的关联数量
2. 增加数据的读取效率
3. 反范式化一定要适度
   实际使用中,读写比例大概3:1,4:1

4. 维护优化

要做什么
1. 维护数据字典

• .使用第三方工具对数据字典进行维护
• .利用数据库本身的备注字段来维护数据字典

CREATE TABLE customer(cust_id INT AUTO_INCREAMENT NOT NULL 
COMMENT '自增ID',cust_name VARCHAR(10) NOT NULL COMMENT 
'客户姓名',PRIMARY KEY (cust_id))COMMENT '客户表';

导出数据字典

SELECT a.table_name,b.TABLE_COMMENT,a.COLUMN_NAME,
a.COLUMN_TYPE,a.COLUMN_COMMENT FROM 
information_schema.COLUMNS a JOIN information_schema.TABLES b 
ON a.table_schema= b.table_schema AND a.table_name = 
b.table_name WHERE a.table_name='customer';

2. 维护索引
如何选择合适的列建立索引

1. 出现在where从句,group by从句,order by 从句中的列
2. 可选择性高的列要放在索引的前面
3. 索引中不要包括太长的数据类型

注意事项

1. 索引不是越多越好,过多的索引不但会降低写效率,而且会降低读的效率
2. 定期维护索引碎片
3. 在SQL语句中不要使用强制索引关键字

3. 维护表结构

1.使用在线变更表结构的工具
MySQL5.5之前使用pt-online-schema-change

MySQL5.6之后本身支持在线表结构的变更

2. 同样对数据字典进行维护
3. 控制表的宽度和大小

数据库中适合的操作

1. 批量操作>逐条操作
2. 禁止使用select * 这样的查询
3. 控制使用用户自定义函数(使用了函数时索引就不起作用了)
4. 不要使用数据库中的全文索引

4. 在适当的时候对表进行水平拆分或垂直拆分

为了控制表的宽度可以进行表的垂直拆分

1. 经常一起查询的列放到一起
2. text,blob等大字段拆分出到附加表中

为了控制表的大小可以进行表的水平拆分

主键哈希的方式来拆分