数据库设计

最新推荐文章于 2024-06-21 10:50:13 发布

帅的一库

最新推荐文章于 2024-06-21 10:50:13 发布

阅读量316

点赞数

分类专栏：数据库

数据库专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍数据库设计的四个主要阶段：需求分析、逻辑设计、物理设计和维护优化，并深入探讨了关系数据库的基本概念、ER图的设计原理、数据冗余的问题及解决办法、不同范式的要求与运用、以及数据库性能优化的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 数据库设计步骤

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

2. 术语

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

3. ER图注

矩形：实体集；
菱形：关系集；
椭圆：属性；
线：关系；

一、需求分析

1. 要点：

存储数据是什么；
数据存储特点（时效性数据【定期清理机制】；没有时效性的数据）；
数据的生命周期（增长速度快，量大，应采用拆表分表方式设计）

2. 表的实体关系：

实体及实体关系（1：1，1：N，N：N）；
实体所包含属性（存储特点，1次1行、N行，DIU结合操作存储...）；
那些属性或属性组合可以唯一标识一个实体（主键，外键关系）；
存储特性（是否永久存储）；

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

二、逻辑设计

1. 数据冗余

插入异常 (insert)；
更新异常 (update)；
删除异常 (delect)；
以上三者均反映了数据的冗余。
数据冗余：指相同的数据在多个地方存在（多个表存在，一个表多个字段意义相同），或者说某个列可以由其他列计算得到，这样就说表中存在着数据冗余（不符合范式要求既有数据冗余）。

2. 设计范式

第一范式 (1NF)

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

第二范式 (2NF)

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

第三范式 (3NF)

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

BC范式 (BCNF)

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

第四范式

暂无简介

第五范式

暂无简介

三、物理设计

1. 设计步骤

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

2. MySQL初步

1) 存储引擎

大多数互联网应用建议使用Innodb；

2) 命名规范

某些DBMS对大小写敏感，可以设置；语义化；长命名以消除歧义；

3) 字段类型选用

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

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

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

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

其它：

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

4) 主键选用

选择原则：
1. 区分业务主键和数据库主键：业务主键用于标识业务数据，进行表与表之间的关联；数据库主键为了优化数据存储。
2. 根据数据库的类型，考虑主键是否要顺序增长：有些数据库是按主键的顺序逻辑存储的。
3. 主键的字段类型所占用空间要尽可能的小：对于使用聚集索引方式存储的表，每个索引后都会附加主键信息。

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

避免使用触发器：
1. 降低数据导入的效率；
2. 可能会出现意想不到的数据异常；
3. 使业务逻辑变的复杂；

关于预留字段：
1. 无法准确的知道预留字段的类型；
2. 无法准确的知道预留字段中所存储的内容；
3. 后期维护预留字段需要成本；
4. 严禁使用预留字段；