数据库设计

最新推荐文章于 2022-03-01 08:56:44 发布

原创最新推荐文章于 2022-03-01 08:56:44 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

30 篇文章

订阅专栏

本文全面解析数据库设计过程，从需求分析到逻辑设计、物理设计及维护优化。覆盖数据库范式、ER图、表结构设计等内容。

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

慕课网视频笔记

第1部分.需求分析

数据库设计简介：

数据库设计：
简单来说，数据库设计就是根据业务系统的需要，结合选择的DBMS，设计最优的数据存储模型。同时建立好表的结构，以及表与表的关联关系。最后达成的效果是：有效的储存数据，并且可以高效的从数据库中取出已存储的数据。

数据库设计：
业务需求—>数据库建模—>实体及联系（有效存储，高效访问）。

如何设计：
减少冗余、避免数据库维护异常、节约存储空间、高效访问。

数据库设计步骤：

需求分析—逻辑设计—物理设计—维护优化

需求分析：数据是什么；数据有哪些属性；数据和属性各自的特点有哪些。

逻辑设计：使用ER图对数据库进行逻辑建模。

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

维护优化：新的需求进行建表；索引优化；大表拆分。

需求分析重要效：

为什么要进行需求分析：

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

需要搞清楚的一些问题：

实体及实体之间的关系（1对1，1对多，多对多）
实体所包含的属性有什么？
哪些属性或属性的组合可以唯一标识一个实体
实体的一些特性（存储上，增长量等）

实体：实体就是指现实世界中存在的具有区分其他事物的特性或属性并与其他实体有联系的实体。例如学生可以是一个实体，课程也是一个实体。属性可以理解为实体的特征。例如：“客人”这一实体的属性有入住日期，结账日期，和交付的押金等。属性对应表中的列。

需求分析实例：

电子商务网站核心模块：用户模块、商品模块、订单模块、购物车模块和供应商模块。

用户模块：（用于记录注册用户信息）

属性：用户名，密码，电话，邮箱，身份证号，地址，姓名，昵称……
可选唯一标识属性：用户名、身份证、电话
存储特点：随系统上线时间逐渐增加，需要永久存储

商品模块：（用于记录网站中所销售的商品信息）

属性：商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格……
可选唯一标识属性：（商品名称，供应商名称）、（商品编码）
存储特点：对于下线商品可以归档存储

订单模块：（用于用户订购商品的信息）

属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型……
可选唯一标识属性：(订单号)
存储特点：永久存储（分表、分库存储）

购物车模块：（用于保存用户购物时选购的商品）

属性：用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量…
可选唯一标识：（用户名、商品编号、加入时间）、（购物车编号）
存储特点：不用永久存储（设置归档、清理规则）

供应商模块：（用于保存所销售商品的供应商信息）

属性：供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人……
可选唯一标识：（供应商编号），（营业执照号）
存储特点：永久存储

电子商务各模块关系：

这里写图片描述

如何分库分表：
分库分表都会有两种，一种垂直，一种水平。垂直就是说，如果一张表的字段过多，会有一张扩展表分些字段；水平就是表结构不变，同样的两张表，分担数据。
具体的你可以看看https://www.cnblogs.com/wade-luffy/p/6096578.html ，或者http://www.infoq.com/cn/articles/key-steps-and-likely-problems-of-split-table

第2部分.逻辑设计

ER图：

逻辑设计是做什么的：

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

ER图中常用概念：

关系：一个关系对于通常所说的一张表。
元组：表中的一行即为一个元组。
属性：表中的一列即为一个属性；每一个属性都有一个名称，称为属性名。
候选码：表中的某个属性组，它可以唯一确定一个元组。
主码：一个关系有多个候选码，选定其中一个为主码。
域：属性的取值范围。
分量：元组中的一个属性值。

ER图实例：

这里写图片描述

设计范式概要：

常见数据库设计范式包括：第一范式，第二范式，第三范式及BC范式，主要就是这几种。

数据异常：

插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常。
更新异常：如果更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说这个表存在更新异常。
删除异常：如果删除表的某一行来反映实体实例，失效时导致另一个不同实体实例信息丢失，那么这个表就存在删除异常。

数据冗余：是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列得到，这样就说表中存在着数据冗余。

更新异常：如果需要将“客房类型”修改为“标间”而不是“标准间”就需要修改所有包含该值的行。如果由于某些原因，没有更新所有的行，而数据库中出现”标准间“”标间“俩种类型的客房，这种情况被称为更新异常。

删除异常：如果删除客房类型为”1001“的行，丢失”单人间“的账户信息，使数据库只剩下”标准间“”总统套房“俩种类型的情况成为删除异常。

插入异常：自己查询一下，解释比较麻烦。

数据冗余就是存在重复的信息造成存储空间的浪费或其他问题则称为数据冗余。

第一范式（1NF）：

定义：数据库表中的所有字段都是单一属性的，不可再分的。这个单一属性是由基本的数据结构所构成的，如整数，浮点数，字符串，等；换句话说，第一范式要求数据库中的表都是二维表。

第一范式：就是数据库的每个属性不可再分，当然，这也是关系数据库的基本要求；遵循第一范式也会有：数据冗余、插入异常、更新异常；这时就需要其他范式。

例如，id— 0001 city—中国北京就不满足第一范式，需要修改为id—0001，country—中国，city—北京。

再例如下图，更加直观了：

这里写图片描述

总结：
1NF：字段是最小的单元不可再分
2NF：满足1NF，表中的字段必须完全依赖于关键字中的全部依赖，而不是部分依赖。
3NF：满足2NF，非关键字段外所有字段必须互不依赖。
4NF：满足3NF，消除表中的多值依赖。

更详细的介绍：
https://www.zhihu.com/question/24696366/answer/29189700
https://zhidao.baidu.com/question/98317025.html?fr=ala0

第二范式（2NF）：

定义：数据库的表中不存在非关键字段对任一候选字段【因为可能事是多个字段作为一个主键，而非关键字段对其中一个依赖就是部分函数依赖】的部分函数依赖。部分函数依赖是指存在这组合关键字的某一关键字决定非关键字的情况。

这里写图片描述

换句话说：所有单关键字段的表都符合第二范式。

组合关键字：有两个或两个以上的字段来标识这行数据。

第二范式就是：不存在非关键字段对于候选关键字段的部分函数依赖；例如：表中的关键字段（商品名称）决定了非关键字段（价格、描述、重量、有效期、饮料）；关键字段（供应商名称）决定了非关键字段（供应商电话），所以关键字段和非关键字段之间存在着部分函数依赖；通俗的来说：就是第二范式要求表的主键和非主键之间“不能”有一毛钱的关系，这样才不会产生部分函数依赖；而属于完全函数依赖；这样就可以定义成：表中的非关键字段要和关键字段存在着完全函数依赖。

这里写图片描述

存在的问题：

插入异常
删除异常
更新异常
数据冗余

不符合第二范式解决方法：表的拆分

这里写图片描述

第三范式（3NF）：

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

BC范式：

第3部分.物理设计

数据库物理设计：

1.选择合适的数据库管理系统（Oracle、SQLServer、MySQL及PgSQL）【根据应用特点、成本】
2.定义库、表、字段的命名规范（数据库系统对此有限制）
3.根据系统选择合适的字段类型
4.反范式化设计：增加冗余，提高效率

选择哪种数据库：

Oracle、SQLServer、MySQL及PgSQL

MySQL常用存储引擎：

MyISAM
MRG_MYISAM
Innodb
Archive
Ndb cluster

这里写图片描述

数据库表及字段的命名规范：

对象命名应该遵循下述原则：

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

数据库字段类型选择原则：

列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次时日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

选择原则主要是从下面两个角度考虑：

在对数据进行比较（查询条件、JOIN条件及排序）操作时：同样的数据，字符处理往往比数字处理慢。
在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

数据库如何具体选择字段类型：

附注：Int存储四个字节，不是说Int只能存储四位数，只是说Int类型的数据是占用4各字节的存储空间。

char与varchar如何选择：

1.如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar。
2.如果列中的最大数据长度小于50Byte，则一般也考虑用char。（当然，如果这个列很实用，则基于节省空间和减少I/O的考虑，还是可以选择用varchar）
3.一般不宜定义大于50Byte的char类型列。

decimal与float如何选择：

1.decimal用于存储精确数据，而float只能用于存储非精确数据。故精确数据只能选择用decimal类型。
2.由于flaot的存储空间开销一般比decimal小（精确到7位数只需要4个字节，而精确到15位小数只需要8字节）
故非精确数据优先选择float类型。

时间类型如何存储：

使用int来存储时间字段的优缺点（优点：字段长度比datetime小。缺点：使用不方便，要进行函数转换。限制：只能存储到2038-1-19 11：14：07）

数据库设计其他注意事项：

如何选择主键：

1.区分业务主键和数据库主键。
2.根据数据库的类型，考虑主键是否要顺序增长（有些数据库是按主键的顺序逻辑存储的）。
3.主键的字段类型所占空间要尽可能的小（对于使用聚集索引方式存储的表，每个索引都会附加主键信息。）

业务主键和数据库主键：
业务主键用于标识业务数据，进行表与表之间的关联；
数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）
更加详细的介绍：http://www.cnblogs.com/sparkbj/p/6015690.html

避免使用外键约束：

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

避免使用触发器：

降低数据导入的效率。
可能会出现意想不到的数据异常。
使业务逻辑变的复杂。

关于预留字段：

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

反范式化表设计：

反范式化是针对范式化而言的，在前面介绍了数据库设计的第三范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。

这里写图片描述

为什么反范式化：

1.减少表的关联数量
2.增加数据的读取效率
3.反范式化一定要适度

第4部分.维护优化

数据库维护和优化要做什么：

维护和优化中要做什么：

维护数据字典
维护索引
维护表结构
在适当的适合对表进行水平拆分或垂直拆分

数据字典是指对数据的数据项、数据结构、数据存储、处理逻辑、外部实体等进行定义和描述，其目的是对数据流程图中的各个元素做出详细的说明，使用数据字典位简单的建模项目。

数据库如何维护数据字典：

如何维护数据字典：

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

以MySQL为例：

CREATE TABLE customer(
    cust_id INT AUTO_INCREMENT 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 = 'goods'