系统分析师5-数据库特训专题

1 数据库设计概述

• 数据库设计关注的问题：性能、数据一致性、安全
  
• 数据库性能优化
  
• 数据库设计流程
  

2 规范化与反规范化

2.1 规范化

思考：范式级别提升带来了什么负面影响？

规范化理论是以模式分解的方式进行提升；
规范化程度越高，表的粒度越小，但是会增加多表联合查询，查询性能非常差。

2.2 反规范化

• 进行反规范化之前，要先做规范化
  
• 反规范化技术手段及说明
  
• 反规范化的优缺点
  
• 数据同步：物化视图可以解决一致性问题

2.3 案例分析例题1

某集团公司在各省均设有分公司，现欲建立全国统一的销售管理信息系统，以便总公司及时掌握各分公司的销售情况。公司成立专门的项目组进行该系统的研发工作，其中张工负责其中的数据库设计工作。
张工和需求分析小组紧密合作，在设计出数据流图和数据字典的基础上，给出了数据库关系模式和相应的索引设计。同时考虑到未规范化关系模式可能引起的各类数据错误，对关系模式进行了全面的规范化处理,使所有关系模式均达到了3NF或BCNF。
在项目实施过程中，应用开发小组认为该设计方案未考虑应用功能的实际需求。如果严格按照设计方案实施，会对应用系统中整体性能产生较大影响。主要的原因在于进行数据查询时，会产生大量的多表连接操作，影响性能。而设计方案中的索引设计，并不能完全满足数据查询的性能要求。
应用开发小组还认为，该设计方案未考虑到信息系统中核心销售数据处理的特点∶各分公司在使用该信息系统时只能操作自己分公司的销售数据，无权操作其它分公司的销售数据;只有总公司有权利操作所有销售数据,以便进行统计分析。
应用开发小组要求，在数据库设计方案中，必须针对实际应用功能的实现来考虑关系模式的规范化，必要时需要采用逆规范化或解除规范化的方法来保证性能要求。

【问题1]】(8分)
系统需要管理供应商和货物等信息，具体包括供应商姓名、地址以及货物名称、价格等，供应商可以提供0~n种货物，其公司地址也可能发生变化。请以供应商关系模式supplier(name,address,product,price)为例，解释不规范的关系模式存在哪些问题。
【问题2】(6分)
应用开发小组认为张工的规范化设计虽然解决了未规范化关系模式带来的问题，但实际实现功能时会造成系统性能的下降,请解释其原因。
【问题3】(5分)
请解释逆规范化方法,说明其优缺点。
【问题4】(6分)
针对该信息系统中核心销售数据处理的特点，如采用关系表水平分割的逆规范化方法，请给出具体的解决方案,并说明该方案存在的问题。

答案：
【问题1】(8分)
(1）数据冗余︰关系模式中多次重复记录了同一供应商的地址。
(2）插入异常:如果还未确定一个供应商有哪些货物，只是想添加一个供应商的地址信息,则会产生产品与价格均为空的记录。
(3）修改异常:当修改一个供应商的地址时，需要将多条记录同时更新，若未同时更新，则数据产生不一致。
(4）删除异常:当删除一个供应商的货物时，其地址信息被一并删除。
【问题2】(6分)
数据库规范化的过程，实际是对数据表的不断拆分，以达到更高的规范程度。这样处理，带来的问题是∶系统中大量查询不能通过单表完成，而需要将多表进行连接查询，所以表拆分得越多，查询性能也就越差。
【问题3】(5分)
规范化设计后，数据库设计者希望牺牲部分规范化来提高查询性能，这种从规范化设计的回退方法称为反规范化技术。
逆规范化方法优点:提高统计、查询效率。
逆规范化方法缺点:增加了数据冗余，浪费存储空间，增、删、改操作的效率降低，可能导致数据不一致，可能产生添加、修改、删除异常。
【问题4】(6分)
解决方案:将各省的数据存放于各省分公司。该方案主要问题:
(1）在于总公司进行全国数据统计时，需要从各省服务器调取数据，效率较低。(2）执行应用功能时需要动态选择分公司的数据库表，增加了应用程序的复杂度。

3 数据库索引与视图的应用

3.1 数据库索引

• 数据库索引：提升查询效率，降低添加、修改、删除效率。采用B树，B+树等。

• 数据库索引示例
  

• B+树索引示例
  

3.2 数据库视图

• 视图(View)并不在数据库中实际存在，而是一种虚拟表。
• 数据库视图示例
  
• 视图的优点
  
• 视图不影响性能，既不提升性能，也不降低性能
• 物化视图
  

3.3 案例分析例题2

某软件企业开发一套类似于淘宝网上商城业务的电子商务网站。该系统涉及多种用户角色，包括购物用户、商铺管理员、系统管理员等。

在数据库设计中，该系统数据库的核心关系包括:
产品（产品编码，产品名称，产品价格，库存数量，商铺编码)
商铺（商铺编码，商铺名称，商铺地址，商铺邮箱，服务电话)
用户（用户编码，用户名称，用户地址，联系电话)
订单（订单编码，订单日期，用户编码，商铺编码，产品编码，产品数量，订单总价)

不同用户角色有不同的数据需求，为此该软件企业在基本数据库关系模式的基础上，定制了许多视图。其中，有很多视图涉及多表关联和聚集函数运算。

【问题1】(8分)
商铺用户需要实时统计本商铺的货物数量和销售情况，以便及时补货，或者为商铺调整销售策略。为此专门设计了可实时查看当天商铺中货物销售情况和存货情况的视图,商铺产品销售情况日报表（商铺编码，产品编码，日销售产品数量，库存数量，日期)
数据库运行测试过程中，发现针对该视图查询性能比较差，不满足用户需求。
请说明数据库视图的基本概念及其优点，并说明本视图设计导致查询性能较差的原因。

【问题2】(8分)
为解决该视图查询性能比较差的问题，张工建议为该数据建立单独的商品当天货物销售、存货情况的关系表。但李工认为张工的方案造成了数据不一致的问题，必须采用定的手段来解决。
1)说明张工方案是否能够对该视图查询性能有所提升，并解释原因。
2）解释说明李工指出的数据不一致问题产生的原因。

【问题3】(9分)
针对李工提出的问题，常见的解决手段有应用程序实现，触发器实现和物化视图实现等,请用300字以内的文字解释说明这三种方案。

答案：
【问题1】
视图是虚表，是从一个或几个基本表（或视图)中导出的表，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。
视图的优点:
1、视图能简化用户的操作
2、视图机制可以使用户以不同的方式查询同一数据
3、视图对数据库重构提供了一定程度的逻辑独立性
4、视图可以对机密的数据提供安全保护
查询性能较差的原因是视图中“日销售产品数量”需要针对订单表做统计分析，订单表中有数量庞大的历史销售记录，所以这种操作极为耗时。
【问题2】
1）张工方案能够对该视图查询性能有所提升，因为这样做能极大减少统计分析的数据量，对小数据量进行统计，性能是能得以保障的。
2)由于当日订单数据既存储在订单表中，又存储在单独的当天货物销售、存货情况表中。同一数据存储了两份，一旦出现修改，未同步修改，则会造成数据不一致。
【问题3】
应用程序实现：在进行订单的添加、修改、删除操作时，从应用程序中，控制对两个数据表都进行相关操作，以保障数据的一致性。
触发器实现：在应用程序中，只对订单表进行操作。但写触发器，当订单表发生变化时，把当日订单内容同步更新到当天货物销售、存货情况表中。
物化视图实现：建立“当天货物销售、存货情况”的物化视图，物化视图会把相应的数据物理存储起来，而且在订单表发生变化时，会自动更新。

4 分布式数据库系统

• 分布式数据库逻辑结构
  
• 分布式数据库的透明性分类

• 两阶段提交协议2PC
  

5 数据库分区分表分库

• 数据库分区分表分库示例
  

• 分区和分表的异同
  

• 分区的常见方式
  

• 分区的优点
  

5.1 案例分析例题3

阅读以下关于数据管理的叙述，在答题纸上回答问题1至问题3。
【说明】
某全国连锁药店企业在新冠肺炎疫情期间，紧急推出在线口罩预约业务系统。该业务系统为普通用户提供口罩商品查询、购买、订单查询等业务，为后台管理人员提供订单查询、订单地点分布汇总、物流调度等功能。该系统核心的关系模式为预约订单信息表。
推出业务系统后，几天内业务迅速增长到每日10万多笔预约订单，系统数据库服务器压力剧增，导致该业务交易响应速度迅速降低，甚至出现部分用户页面无法刷新、预约订单服务无响应的情况。为此，该企业紧急成立技术团队，由张工负责，以期尽快解决该问题。

【问题1】(9分)
经过分析，张工认为当前预约订单信息表存储了所有订单信息，记录已达到了百万级别。系统主要的核心功能均涉及对订单信息表的操作，应首先优化预约订单信息表的读写性能,建议针对系统中的SQL语句，建立相应索引，并进行适当的索引优化。
针对张工的方案，其他设计人员提出了一些异议，认为索引过多有很多副作用。请用100字以内的文字简要说明索引过多的副作用。
【问题2】(10分)
作为团队成员之一，李工认为增加索引并进行优化并不能解决当前问题，建议采用物理分区策略，可以根据预约订单信息表中“所在城市”属性进行表分区，并将每个分区分布到独立的物理磁盘上，以提高读写性能。常见的物理分区特征如表4-1所示。李工建议选择物理分区中的列表分区模式。
请填补表4-1中的空(a) ~(d)处，并用100字以内的文字解释说明李工选择该方案的原因。
【问题3】(6分)
在系统运行过程中，李工发现后台管理人员执行的订单地址信息汇总等操作，经常出现与普通用户的预约订单操作形成读写冲突，影响系统的性能。因此李工建议采用读写分离模式，采用两台数据库服务器，并采用主从复制的方式进行数据同步。请用100字以内的文字简要说明主从复制的基本步骤

分析：
问题1：从以下角度进行解答：空间上；开销上；优化评估；统计；索引相互影响
问题2：(a)属性的离散值；(b)周期数据；©能力强；(d)均匀
问题3：中继日志
答案：
【问题1】
索引过多的副作用有:
(1) 过多的索引会占用大量的存储空间;
(2) 更新开销,更新语句会引起相应的索引更新;
(3) 过多索引会导致查询优化器需要评估的组合增多;
(4) 每个索引都有对应的统计信息，索引越多则需要的统计信息越多;
(5) 聚集索引的变化会导致非聚集索引的同步变化；
(6) 增加运维工作量，导致运维困难。
【问题2】
(a) 属性的离散值
(b) 周期性数据/周期数据
(c ) 能力强
(d) 均匀
李工建议根据预约订单所在城市进行表分区，而所在城市属性为离散值，根据所在城市属性建立列表分区，也方便不同城市处理自己的数据，方便数据管理，方便按城市汇总数据。
【问题3】
主从复制的基本步骤:
(1）主服务器将所做修改通过自己的I/O线程，保存在本地二进制日志中;
(2）从服务器上的I/O线程读取主服务器上面的二进制日志，然后写入从服务器本地的中继日志;
(3) 从服务器上同时开启一个SQL thread，定时检查中继曰志，如果发现有更新则立即把更新的内容在本机的数据库上面执行一遍。

6 分布式事务增补

• 两阶段提交协议2PC
  
• 两阶段提交协议活动图
  
• 两阶段提交协议对故障的恢复
  
  例题1
  分布式数据库系统中的两阶段提交协议(Two Phase Commit Protocol,2PC协议)包含协调者和参与者，通常有如下操作指令。满足2PC的正常序列是（)
  ①协调者向参与者发prepare消息；②参与者向协调者发回ready消息
  ③参与者向协调者发回abort消息；④协调者向参与者发commit消息
  ⑤协调者向参与者发rollback消息
  A ①②④
  B ①②⑤
  C ②③④
  D ②③⑤

分析：
如果是正常提交序列为：①②④；
如果是异常回滚序列为：①③⑤；
答案：选A

例题2
分布式事务的执行可能会涉及多个站点上的数据操作，在两阶段提交协议中，当事务Ti的所有读写操作执行结束后，事务Ti的发起者协调器Ci向所有参与Ti的执行站点发送< prepare Ti>的消息，当收到所有执行站点返回的< ready Ti>消息后，Ci再向所有执行站点发送< commit Ti>消息。若参与事务Ti执行的某个站点故障恢复后日志中有< ready Ti>记录，而没有< commit Ti>记录，则( ）。
A 事务Ti已完成提交，该站点无需做任何操作
B 事务Ti已完成提交，该站点应做REDO操作
C 事务Ti未完成提交，该站点应做UNDO操作
D 应向协调器询问以决定Ti的最终结果

分析：
没有< commit Ti>记录说明事务没有提交，在事务没有提交的情况下，还要再询问协调者到底做不做，因此应向协调器询问以决定Ti的最终结果
正确选项：D。

6.1 案例分析例题4

题干:阅读以下关于微服务架构中的数据管理的叙述，在答题纸上回答问题1至问题3。
【说明】
某大型电商平台构建了一个在线B2B商店系统。该系统采用微服务架构，将系统功能分解为多个松散耦合且可独立部署的较小组件或服务。最终设计的系统包括了电商系统中常见的服务:客户服务、订单服务、支付服务等，其中:
1、客户服务负责对客户相关的信息进行管理和维护;
2、订单服务负责对订单信息的管理和维护;
3、支付服务负责对在线支付功能和信息的管理和维护等。
为了确保微服务之间的松耦合，每个服务都有自己的数据，其中，订单服务使用了NoSQL数据库，客户服务和支付服务使用了关系数据库。
李工认为由于不同服务使用了各自的不同数据库，使得跨服务操作可能存在数据不一致。比如订单与支付的数据一致性问题，系统通过订单服务在本地NoSQL数据库中创建订单记录，同时在支付服务的关系数据库中创建支付记录，且必须保证订单记录和支付记录的一致性，该问题在系统构建时需要考虑。

【问题1】(7分)
李工建议采用两阶段提交协议(2PC）来解决服务数据的一致性问题。请用200字以内的文字简要说明2PC;说明2PC是否能解决该问题，并简要解释原因。
【问题2】(8分)
王工建议采用分布式数据管理方案，用事件驱动架构来解决服务数据的一致性问题，在订单服务和支付服务之间通过可靠的消息队列实现事件的传递，其基本操作步骤如下，请填写其中的空白处。
(1）订单服务接收订购请求，创建一个订单，该记录状态为(a) ，发布一个“创建订单”事件;(2)(b)接收“创建订单”事件，记录©，发布一个“支付完成”事件;
(3）订单服务接收“支付完成”事件，修改订单记录状态为(d) 。
【问题3】(10分)
李工提出王工的方案会有数据库更新和发布事件的原子性问题，例如订单服务创建订单记录和友布“创建订单”事件需要原子性保障，否则会出现数据不一致状态。
王工认为可以使用本地事务发布事件的方法来解决该问题。请给出使用本地事务发布事件的基本方法，并说明该方法的缺点。

答案:
【问题1】
1、两阶段提交协议2PC经常用来管理分布式事务。
(1)2PC包含协调者和参与者两类站点，只有协调者才拥有提交或撤销事务的决定权，而其他参与者各自负责在其本地数据库中执行写操作，并向协调者提出撤销或提交事务的意向。
(2)2PC分为两个阶段:表决阶段和执行阶段。
①表决阶段，目的是形成一个共同的决定。协调者给所有参与者发送“准备提交”消息，并进入等待状态，所有参与者给与回复“建议提交”或“建议撤销”。只要有一个结点选择撤销，则整体事务撤销，否则，执行该事务。
②执行阶段，目的是实现这个协调者的决定。根据协调者的指令，参与者或者提交事务，或者撤销事务，并给协调者发送确认消息。
2、两阶段提交协议2PC不能解决当前问题。
(1)分布式数据库遵循的是CAP原则，会在一定程度上牺牲一致性。()大多数NoSQL 数据库并不支持2PC。
(3)分布式两阶段提交协议2PC一般针对的对象在逻辑上是一个整体，对某一个整体事务需要在多个物理节点上执行时，进行表决和执行，对多个数据库的不同服务并不是很合适。
【问题2】
(a)未支付 (b)支付服务 (c )支付信息 (d)已支付
【问题3】
使用本地事务发布事件:
由一个独立进程来发布事件。具体来说，就是在存储业务实体状态的数据库中，使用一个事件表来充当消息队列。应用启动一个(本地〉数据库事务，更新业务实体的状态，在事件表中插入一个事件，并提交该事务。一个独立的消息发布线程或进程查询该事件表，将事件发布到消息代理，并标注该事件为已发布。
缺点:
由于开发者必须牢记发布事件，因此有很大可能出错。此外这一方法对于某些使用NoSQL数据库的应用是个挑战，因为NosQL本身交易和查询能力有限。

7 NoSQL

• NoSQL (Not-only sQL)∶不仅仅只是SQL，泛指非关系型的数据库。
• 关系数据库和NoSQL的对比
  
• NoSQL分类
  
• NoSQL分类及优缺点
  

8 附录：思维导图