数据设计的个人总结

今天药忘吃喽~ 2022-08-07 12:57 78阅读 0赞

### 一、什么是数据库设计 ###

1.  数据库设计概念  
    数据库设计就是根据业务系统的具体需求，结合所选择的DBMS，为这个业务系统构造出最优的数据存储模型。并建立好数据库中表的结构，以及表与表之间的关联关系的过程。使之能**有效**的对应用系统中的数据**进行存储**，并可以**高效**的对已经存储的数据**进行查询访问**。
2.  设计对比

<table> 
 <thead> 
  <tr> 
   <th align="center">优良的设计</th> 
   <th align="center">槽糕的设计</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">减少数据冗余</td> 
   <td align="center">存在大量的数据冗余</td> 
  </tr> 
  <tr> 
   <td align="center">避免数据维护异常</td> 
   <td align="center">存在数据插入、更新、删除异常</td> 
  </tr> 
  <tr> 
   <td align="center">节约存储空间(偶尔会用空间换时间)</td> 
   <td align="center">浪费大量存储空间</td> 
  </tr> 
  <tr> 
   <td align="center">高效的访问</td> 
   <td align="center">访问数据低效</td> 
  </tr> 
 </tbody> 
</table>

### 二、数据库设计的步骤 ###

1.  设计前的唠叨  
    在设计一个数据库前，我们知道数据库整体需要遵循几个范式，但是实际情况中会进行灵活的调整。总体来说，设计的数据库要做到：无操作异常，访问高效，相对解决空间(就目前而言，空间的成本是最低的)  
    *操作异常的概念补充：*  
    ![操作异常概念解释][20150411161403930]  
    *数据冗余的概念补充*  
    是指相同的数据在多个地方存在，或者说表中的某个列可以由其它列计算得到，这样就说表中的存在数据冗余。（对一致性造成不便）
2.  设计的基本步骤  
    1). 需求分析  
    需求分析主要完成的内容：业务系统中有哪些数据？这些数据又有哪些属性？数据和属性的各自特点有哪些？  
    2). 逻辑设计  
    使用ER图设计工具对数据库进行ER图逻辑建模：首先将需求转化为数据库的逻辑模型，其次，通过ER图形式将逻辑模型描述展示出来，最后这个逻辑设计与所选择的DBMS无关，也就是说这个逻辑设计应该适合所有的DBMS。  
    3). 物理设计  
    这个阶段是进入到与DBMS相关的阶段，因此首先需要选择DBMS，并且将第二步的逻辑模型转为物理模型。  
    这个阶段会涉及到数据库中建表，选择字段类型的问题。在MySql中遵循以下原则：  
    列的数据类型一方面影响相应数据的存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制，最后是字符串类型。对于相同级别的数据类型，在满足业务的情况下，应该优先选择占用空间小的数据类型。  
    mysql中部分字段空间占用情况：  
    ![数据库字段说明][20150411170612734]  
    额外注意几点：
    
     *  在对数据进行比较(where、join 、order by)操作时，同样的数据，字符串处理比数字处理更慢。
     *  在MySql中，UTF-8占用3个字节。
    
    4). 维护优化
    
     *  针对新的需求建立新的数据库表(*这里补充说明下，在进行数据库最初的设计过程中，不建议在表中预留不确定的字段，这并不能实现数据库良好的扩展性问题，它的代价与后期新加一个字段一样，甚至更大*)
     *  索引优化
     *  大表拆分：拆分又分为两种方式  
        
        
        1.  水平拆分：控制表的长度，即数据的行数。会将每个表中的数据量减少。
        2.  垂直拆分：数据库中的存储是以页为单位的，当每一行的宽度比较小时(列数比较少时)，每页中存储的内容就多，IO效率就高(数据中的数据是存放在磁盘上，每次IO的内容越多越好)。因此常常对于非常宽的表，进行表的垂直拆分。拆表后的数据量不应该发生变化，但是表的数量增加，每张表的宽度减少。

### 三、数据库中的几大范式 ###

1.  第一范式(1NF)  
    概念：数据库表中的所有字段值都是**不可分解**的原子值，就说明该数据库表满足了第一范式。  
    作用：**确保每列保持原子性**  
    举例：本来正常情况下，设计地址，只需要一个字段，但是若在实际业务系统中需要访问其中的国家、省、市时，此时就应该将地址这个字段进行拆分才符合1NF。
2.  第二范式(2NF)  
    概念：基于1NF基础之上，第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。此外：所有单关键字段的表都符合第二范式。  
    作用：**确保表中的每列都和主键相关**  
    举例：在一个订单表中，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示：

问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。  
解决办法：将以上的表进行拆分，订单信息保存一张表，订单中所拥有的商品以后咱表，最后商品信息一张表  
*订单信息*

*订单商品*

*商品信息*

3. 第三范式(3NF)  
概念：基于2NF基础上的，如果数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。  
作用：**确保每列都和主键列直接相关,而不是间接相关**  
举例：在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。这样会减少数据冗余（*但在实际开发中，为便于查询，往往会在订单表中增加一些客户信息的冗余*）  
4. BC范式(BCNF)  
概念：在第三范式的基础之上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。  
作用：**确保任意列都和主键列直接相关,而不是间接相关**  
举例：在设计一个供应商与供应商联系人表时，供应商联系人只能在一家供应商中上班，供应商可以供应多个商品。因此选用：供应商+联系人来作为联合主键，然而这二者之间存在传递依赖关系  
问题表：

<table> 
 <thead> 
  <tr> 
   <th align="center">供应商</th> 
   <th align="center">商品ID</th> 
   <th align="center">供应商联系人</th> 
   <th align="center">商品数量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">饮料一厂</td> 
   <td align="center">1</td> 
   <td align="center">张三</td> 
   <td align="center">10</td> 
  </tr> 
  <tr> 
   <td align="center">饮料一厂</td> 
   <td align="center">2</td> 
   <td align="center">李四</td> 
   <td align="center">20</td> 
  </tr> 
  <tr> 
   <td align="center">饮料一厂</td> 
   <td align="center">1</td> 
   <td align="center">王五</td> 
   <td align="center">10</td> 
  </tr> 
 </tbody> 
</table>

这个表存在以下符合主键的依赖关系：供应商->供应商联系人 供应商联系人->供应商  
解决办法：  
*供应商*

*供应商联系人*

<table> 
 <thead> 
  <tr> 
   <th align="center">供应商</th> 
   <th align="center">供应商联系人</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">饮料一厂</td> 
   <td align="center">张三</td> 
  </tr> 
  <tr> 
   <td align="center">饮料一厂</td> 
   <td align="center">李四</td> 
  </tr> 
  <tr> 
   <td align="center">饮料一厂</td> 
   <td align="center">王五</td> 
  </tr> 
 </tbody> 
</table>

### 四、高手的境界都是无招胜有招 ###

为了设计出优良的数据库，我们需要遵循数据库的范式，但是有时候如果你设计的数据库完全遵循了这些范式，反而会降低你的某些性能。这里提几点：  
\- 数据库连接会带来一部分性能损失，因此有时候为了减少冗余，将数据存放在多张表中，往往会降低查询性能，而互联网的世界，读写比例大概是3:1甚至4:1  
\- 减少表与表之间的关联数量(减少了对磁盘的IO)，增加数据的读取效率。  
\- 反范式化一定要适度。凡事过度适得其反。  
举例反范式化设计：  
**遵循范式设计表：**  
用户表(用户id， 姓名， 电话， 地址， 邮编)  
订单表(订单id， 用户id， 下单时间， 支付类型， 订单状态)  
订单商品表(订单id， 商品id， 商品数量， 商品价格)  
商品表(商品id， 名称， 描述， 过期时间)  
**反范式设计表**  
用户表(用户id， 姓名， 电话， 地址， 邮编)  
订单表(订单id， 用户id， 下单时间， 支付类型， 订单状态， *订单价格*， *姓名*， *地址*， *电话*)  
订单商品表(订单id， 商品id， 商品数量， 商品价格， *商品名称*，*过期时间*)  
商品表(商品id， 名称， 描述， 过期时间)

--------------------

通过以上反范式设计带来的好处：  
当需要查询订单购买者相关信息时，以前需要拿到订单号，在订单表中找到用户id，然后去用户表查询用户信息，再到订单商品表中找到商品id，再去商品表中找到商品信息。这就需要查询4张表，通过反范式的设计，现在只需要在订单表可以获得订单与用户的信息，再到订单商品表中，即可获得订单与商品的信息。从4张表的查询变为2张表，用空间换时间，是值得的。  
*当然这里在进行更新操作的时候就需要注意了。*

[20150411161403930]: /images/20220805/839d9cf799204c079f567361d84110f1.png
[20150411170612734]: /images/20220805/74e4949548954ecdba6dea56f4f82658.png