软考高级-系统分析师-案例分析-数据库设计

数据库基础

数据库设计过程

• 需求分析：通过调查研究，了解用户的数据和处理要求，并按一定格式整理形成需求说明书。
• 概念设计：在需求分析阶段产生的需求说明书的基础上，按照特定的方法将它们抽象为一个不依赖于任何数据库管理系统的数据模型，即概念模型。
• 逻辑设计：将概念模型转化为某个特定的数据库管理系统上的逻辑模型。设计逻辑结构时，首先为概念模型选定一个合适的逻辑模型（通常是关系模型），然后将其转化为由特定 DBMS 支持的逻辑模型，最后对逻辑模型进行优化。
• 物理设计：对给定的逻辑模型选取一个最适合应用环境的物理结构，所谓数据库的物理结构，主要是指数据库在物理设备上的存储结构和存取方法。

规范化理论-范式

逐步优化以解决：插入异常、更新异常、删除异常、数据冗余

• 1NF 属性值都是不可分的原子值
  第一范式（1NF）：在关系模式R中，当且仅当所有域中只包含原子值，即每个属性都是不可再分的数据项，则称关系模式R是第一范式。

• 2NF 消除非主属性对候选键的部分依赖，满足1NF，如果候选码只有单个属性，必然满足2NF
  第二范式（2NF）：当且仅当实体E是第一范式（1NF），且每一个非主属性完全依赖候选键（不存在部分依赖）时，则称实体E是第二范式。
  
  学分对课程号候选键有部分依赖，所以不满足2NF。

• 3NF 消除非主属性对候选键的传递依赖，满足1NF，如果没有非主属性，必然满足3NF
  第三范式（3NF）：当且仅当实体E是第二范式（2NF），且E中没有非主属性传递依赖于候选码时，则称实体E是第三范式。
  
  学生关系S（学号，姓名，系号，系名，系位置）。从各属性之间的联系可以判断出S的函数依赖有学号→(姓名，系号，系名，系位置)，系号→(系名，系位置)。显然，学号为候选键。在函数依赖中有学号→系号→系名与学号→系号→系位置，这便是传递函数依赖。由于系名与系位置为非键属性，同时传递依赖于候选键，因此，关系模式S不满足3NF。若要使S满足3NF，需要将其拆分为S1(学号，姓名，系号)和S2(系号，系名，系位置)。

• BCNF 消除主属性对候选键的部分和传递依赖
  所有的函数依赖左边部分都必须是候选键中的一个。

部分函数依赖和传递函数依赖

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

• 表太细，关联不方便（反规范化）

数据库事务

事务的4个属性（4个特性）:

• 原子性
  事务是数据库的逻辑工作单位，事务的原子性保证事务包含的一组更新操作是原子不可分的，也就是说，这些操作是一个整体，不能部分的完成。

• 一致性
  一致性是指使数据库从一个一致性状态变到另一个一致性状态。
  例如，在转账的操作中，各账户金额必须平衡。一致性与原子性是密切相关的，一致性在逻辑上不是独立的，它由事务的隔离性来表示。

• 隔离性
  隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。它要求即使有多个事务并发执行，但看上去每个事务按串行调度执行一样。这一性质也称为可串行性，也就是说，系统允许的任何交错操作调度等价于一个串行调度。

• 持久性
  持久性也称为永久性，是指事务一旦提交，改变就是永久性的，无论发生何种故障，都不应该对其有任何影响。

并发控制 - 数据不一致问题

• 丢失修改
  事务A与事务B从数据库中读入同一数据并修改，事务B的提交结果破坏了事务A提交的结果，导致事务A的修改被丢失。
  解决方法：对A加X锁（写锁），那么B只能在A解锁后读。

• 不可重复读
  不可重复读是指事务A读取数据后，事务B执行了更新操作，事务A使用的仍是更新前的值，造成了数据不一致性。
  解决方法：A加X锁（写锁）后，B对同一数据加X锁（写锁），这样A的锁解开后，才可以调用B

• 读“脏”数据
  事务A修改某一数据，并将其写回磁盘，事务B读取同一数据后，事务A由于某种原因被撤消，这时事务A已修改过的数据恢复原值，事务B读到的数据就与数据库中的数据不一致，是不正确的数据，称为“脏数据”。
  解决方法：修改时加排他锁，直到事务提交后才释放，读取时加共享锁，读取完释放。A读取数据时加上共享锁后，不允许任务事务操作该数据，只能读取。

并发控制 - 封锁协议

• 处理并发控制的主要方法是采用封锁技术，主要有X封锁（写锁）和S封锁（读锁）。

• 排他型封锁（X封锁）又叫写锁、独占锁：对于T1如果对A加了写锁，其他事务不能对A加任何锁。只允许事务T读取和修改数据A，其他事务要等事务T解除X封锁以后，才能对数据A实现任何类型的封锁。可见，X封锁只允许一个事务独锁某个数据，具有排他性。

• 共享型封锁（S封锁）又叫读锁：对于T1如果对A加了读锁，其他事务只能在A上加读锁，不能加写锁，事务T1自己可以升级S锁至X锁。允许事务T读取数据A，但不能修改数据A，在所有S封锁解除之前，决不允许任何事务对数据A实现X封锁。

• 一级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。可防止丢失修改，并保证事务T是可恢复的，但不能保证可重复读和不读“脏数据”。

• 二级封锁协议：一级封锁协议加上事务T在读取数据R之前先对其加S锁，读完后即可释放S锁。可防止丢失修改、读“脏数据”，但不能保证可重复读。

• 三级封锁协议。一级封锁协议加上事务T在读取数据R之前先对其加S锁，直到事务结束才释放。可防止丢失修改、读“脏数据”、防止数据重复读。

• 两段锁协议：对这些事务的任何并发调度策略都是可串行化的可能发生死锁。

案例题

某航空公司要开发一个订票信息处理系统，以方便各个代理商销售机票。开发小组经过设计，给出该系统的部分关系模式如下：航班（航班编号，航空公司，起飞地，起飞时间，目的地，到达时间，剩余票数，票价）代理商（代理商编号，代理商名称，客服电话，地址，负责人）机票代理（代理商编号，航班编号，票价）旅客（身份证号，姓名，性别，出生日期，电话）购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）在提供给用户的界面上，其核心功能是当用户查询某航班时，将该航班所有的代理商信息及其优惠票价信息，返回给用户，方便用户购买价格优惠的机票。在实现过程中发现，要实现此功能，需要在代理商和机票代理两个关系模式上进行连接操作，性能很差。为此开发小组将机票代理关系模式进行了扩充，结果为：机票代理（代理商编号，航班编号，代理商名称，客服电话，票价）这样，用户在查找信息时只需对机票代理关系模式进行查询即可，提高了查询效率。

【问题 1】
机票代理关系模式的修改，满足了用户对代理商机票价格查询的需求，提高了查询效率。但这种修改导致机票代理关系模式不满足 3NF，会带来存储异常的问题。
1）请具体说明其问题，并举例说明。
2）这种存储异常会造成数据不一致，请给出解决该存储异常的方案。

1）不满足 3NF 会产生函数的传递依赖，造成数据冗余和修改异常等问题。
① 数据冗余，一个代理商会代理多家航班的机票销售业务，在机票代理模式中，该代
理商的代理商名称，客服电话就会被存储多次，造成数据的冗余。
② 修改异常，当需要修改该代理商的名称或客服电话时，就要修改所有相应元组中的
名称或客服电话，否则就会出现客服电话值不一致的现象，产生修改异常。

【问题 2】
在机票销售信息处理系统中，两个代理商的售票并发执行，可能产生的操作序列如下表所示。

假设两个代理商执行之前，该航班仅剩 1 张机票。
1）请说明上述两个代理商操作的结果。
2）并发操作会带来数据不一致的问题，请具体说明 3 种问题。

1）2 个代理商都成功售出 1 张票，剩余票数为 0。
2）数据库的并发操作会带来一些数据不一致问题。例如，丢失修改、读脏数据和不可
重复读等。
① 丢失修改。事务 A 与事务 B 从数据库中读入同一数据并修改，事务 B 的提交结果
破坏了事务 A 提交的结果，导致事务 A 的修改被丢失。
② 读脏数据。事务 A 修改某一数据，并将其写回磁盘，事务 B 读取同一数据后，事
务 A 由于某种原因被撤消，从而导致事务 B 读到的数据是无效数据。
③ 不可重复读。事务 A 读取数据后，事务 B 又修改了该数据，但事务 A 使用的仍是
修改之前的值。因此。事务 A 与实务 B 分别得到不同的结果，产生了数据的不一致
性。

【问题 3】
为了避免问题 2 中的问题，开发组使用库的读写锁机制，操作序列变为下表所示。

（1）加写锁
（2）加读锁
（3）加写锁
（4）等待
（5）查询剩余票数
（6）加写锁

数据库安全性知识

• 用户标识和鉴定
  最外层的安全保护措施，可以使用用户帐号、口令及随机数检验等方式

• 存取控制（数据授权）
  对用户进行授权，包括操作类型（如查找、插入、删除、修改等运作）和数据对象（主要是数据范围）的权限

• 密码存储和传输
  对远程终端信息用密码传输

• 视图的保护
  对视图进行授权

• 审计
  使用一个专用文件或数据库，自动将用户对数据库的所有操作记录下来

视图

视图是保存在数据库中的 SELECT 查询，其内容由查询定义，因此，视图不是真实存在的基础表，而是从一个或者多个表中导出的虚拟的表。同真实的表一样，视图包含一系列带有名称的列和行数据，但视图中的行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图优点如下：

• 视点集中，视图只展示与用户相关的数据。

• 简化操作，在每一次执行相同的查询时，不必重新写查询语句，只要一条简单的查询视图语句即可。

• 定制数据，视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。

• 合并分割数据，在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。

• 安全性，视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。

物化视图（快照）

在物化视图中数据查询结果被物理固化起来，其数据随着原始表变化，同步更新。物化视图也可以称为快照。

存储过程

• 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

• 存储过程是数据库所提供的一种数据库对象，通过存储过程定 义一段代码，提供给应用程序调用来执行。 从安全性的角度考虑，更新数据时，通过提供存储过程让第三方调用，将需要更新的数据传入存储过程，而在存储过程内部用代码分别对需要的多个表进行更新，从而避免了向第三方提供系统的表结构，保证了系统的数据安全。

触发器

• 触发器是一种特殊的存储过程，当数据发生变化时，触发器会产生某种动作。使用触发器有助于强制保持数据库的数据完整性。

案例题

某软件企业开发一套类似于淘宝网上商城业务的电子商务网站。该系统涉及多种用户角色，包括购物用户，商铺管理员，系统管理员等。
在数据库设计中，该系统数据库的核心关系包括：
产品（产品编码,产品名称，产品价格，库存数量，商铺编码）；
商铺（商铺编码,商铺名称，商铺地址，商铺邮箱，服务电话）；
用户（用户编码，用户名称,用户地址，联系电话）；
订单（订单编码，订单日期，用户编码，商铺编码，产品编码,产品数量,订单总价）；
不同用户角色也有不同的数据需求，为此该软件企业在基本数据库关系模式的基础上，定制了许多视图。其中，有很多视图涉及到多表关联和聚集函数运算。

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

视图是一个虚拟表，并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。视图优点如下：

• 视点集中，视图只展示与用户相关的数据。

• 简化操作，在每一次执行相同的查询时，不必重新写查询语句，只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。

• 定制数据，视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。

• 合并分割数据，在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。

• 安全性，视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。

由于日销售产品数量基于订单统计而得，而订单表是一张大表，数据量可能非常大，导致统计耗时。

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

1）能有提升。张工的方案能减少统计分析的数据量。
2）日订单数据存储在订单表和每日货物统计表（销售、存货统计表）两张表中，同一数据存储了两份，很容易产生数据不同步，也就是数据不一致问题。

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

• 程序实现。在订单的增删改操作时，对两张表的都进行相关操作。

• 触发器实现。如订单发生变化时，通过触发器把当日订单同步到每日货物统计表中。

• 物化视图。建立“当日销售、存货”物化视图，通过物化视图把相关联的数据关联起来，当订单发生变化时，自动更新，保证数据一致性。

数据库设计主要包括概念设计、逻辑设计和物理设计三个阶段，请用 200 字以内文字说明这三个阶段的主要任务。

（1）概念设计也称为概念结构设计，其任务是在需求分析阶段产生的需求说明书的基础上，按照特定的方法将它们抽象为一个不依赖于任何 DBMS 的数据模型，即概念模型。概念模型的表现形式即 ER 模型。

（2）逻辑设计也称为逻辑结构设计，其主要任务是将概念模型转换为某个特定的DBMS 上的逻辑模型。

（3）物理设计也称为物理结构设计，其任务是对给定的逻辑模型选取一个最适合应用环境的物理结构。所谓数据库的物理结构，主要是指数据库在物理设备上的存储结构和存取方法。

反规范化

反规范化技术

技术手段	说明
增加派生性列	增加派生列指增加的列可以通过表中其他数据计算生成。它的作用是在查询时减少计算量，从而加快查询速度。例如：已有“单价”和“数量”列，增加“总价”列
增加冗余列	增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作。例如：已有“学号”列，增加“姓名”列
重新组表	重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
分割表	水平分割，按记录进行分割，把数据放到多个独立的表中，主要用于表数据规模很大或表中数据相对独立或数据需要存放到多个介质上时使用。垂直分割，对表进行分割，将主键与部分列放到一个表中，主键与其它列放到另一个表中， 在查询时减少 I/0 次数。

反规范化的优点：

• 连接操作少，检索快、统计快。
• 需要查的表减少，检索容易。

缺点：

• 数据冗余，需要更大存储空间
• 插入、更新、删除操作开销更大
• 数据不一致，可能产生添加、修改、删除异常。可借助触发器数据同步，应用程序数据同步，物化视图解决。
• 更新和插入代码更难写

案例题

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

【问题 1】
系统需要管理供应商和货物等信息，具体包括供应商姓名、地址以及货物名称、价格等，供应商可以提供 0~n 种货物，其公司地址也可能发生变化。请以供应商关系模式 supplier（name，address，product，price）为例，解释不规范的关系模式存在哪些问题。

（1）数据冗余，关系模式中多次重复记录了同一供应商的地址。
（2）插入异常，如果还未确定一个供应商有哪些货物，只是想添加一个供应商的地址
信息,则会产生产品与价格均为空的记录。
（3）修改异常，当修改一个供应商的地址时，需要将多条记录同时更新，若未同时更
新,则数据产生不一致。
（4）删除异常，当删除一个供应商的货物时，其地址信息被一并删除。

【问题 2】
应用开发小组认为张工的规范化设计虽然解决了未规范化关系模式带来的问题，但实际实现功能时会造成系统性能的下降,请解释其原因。

数据库规范化的过程，实际是对数据表的不断拆分，以达到更高的规范程度。这样处理，带来的问题是：系统中大量查询不能通过单表完成，而需要将多表进行连接查询，所以表拆分得越多，查询性能也就越差。

【问题 3】
请解释逆规范化方法，说明其优缺点。

规范化设计后，数据库设计者希望牺牲部分规范化来提高性能，这种从规范化设计的回退方法称为反规范化技术。

逆规范化方法优点：提高统计、查询效率。

逆规范化方法缺点：增加了数据冗余，浪费存储空间，增、删、改操作的效率降低，可能导致数据不一致，可能产生添加、修改、删除异常。

【问题 4】
针对该信息系统中核心销售数据处理的特点，如采用关系表水平分割的逆规范化方法，请给出具体的解决方案，并说明该方案存在的问题。

解决方案：将各省的数据存放于各省分公司。

该方案主要问题：
（1）在于总公司进行全国数据统计时，需要从各省服务器调取数据，效率较低。

（2）执行应用功能时需要动态选择分公司的数据库表，增加了应用程序的复杂
度。

数据库性能优化

集中式数据库

• 硬件升级：处理器、内存、磁盘子系统和网络。
• 数据库设计：反规范化技术（逻辑）、数据库分区（物理）。
• 索引优化策略：选择经常查询不常更新的属性、数据量小的不设置索引等。
• 查询优化：建立物化视图或尽可能减少多表查询等。

分布式数据库

• 主从复制、读写分离
• 数据库分片（分表）、分库
• 分布式缓存技术

数据库设计（分布式）

分布式数据库系统

• 全局外模式
  全局外模式是全局应用的用户视图，是全局概念模式的子集，该层直接与用户（或应用程序）交互。

• 全局概念模式
  全局概念模式定义分布式数据库中数据的整体逻辑结构，数据就如同根本没有分布一样，可用传统的集中式数据库中所采用的方法进行定义。全局概念模式中所用的数据模型应该易于向其他层次的模式映射，通常采用关系模型。

• 分片模式
  在某些情况下，需要将一个关系模式分解成为几个数据片，分片模式正是用于完成此项工作的。

• 分布模式
  分布式数据库的本质特性就是数据分布在不同的物理位置。

• 局部概念模式
  局部概念模式是局部数据库的概念模式。

• 局部内模式
  局部内模式是局部数据库的内模式。

透明性分类

两阶段提交协议2PC

2PC 事务提交的两个阶段：

• 表决阶段，目的是形成一个共同的决定
• 执行阶段，目的是实现这个协调者的决定

两条全局提交规则：

• 只要有一个参与者撤销事务，协调者就必须做出全局撤销决定
• 只有所有参与者都同意提交事务，协调者才能做出全局提交决定

分区、分表、分库

分区 分表
共性：
1 都针对数据表
2 都使用了分布式存储
3 都提升了查询效率
4 都降低数据库的频繁 I/O 压力值

差异
分区：逻辑上还是一张表
分表：逻辑上已经是多张表

分区技术

分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介质中，实际上还是一张表。

使用分区技术的优点：

• 减少维护工作量。独立管理每个分区比管理单张大表要轻松得多。
• 增强数据库的可用性。如果表的一个或几个分区由于系统故障而不能被使用，那
  么表其余的分区仍然可以使用；如果系统故障只影响表的一部分分区，那么，只
  有这部分分区需要修复，这就比修复整张大表耗费的时间少许多。
• 均衡 I/O，减少竞争。通过把表的不同分区分配到不同的磁盘来平衡 I/O 改善性
  能。分区对用户保持透明。最终用户感觉不到分区的存在。
• 提高查询速度。对大表的查询、增加、修改等操作可以分解到表的不同分区中来
  并行执行。

数据分区技术一般分为水平分区和垂直分区，数据库中常见的是水平分区。水平分区分为范围分区、哈希分区、列表分区等。

• 范围分区（Range），按数据范围值来做分区。例：按用户编号分区，0-999999 映射到分区 A；1000000-1999999 映射到分区 B。
• 哈希分区（Hash），通过对 key 进行 hash 运算分区。例，可以把数据分配到不同分区，这类似于取余操作，余数相同的，放在一个分区上。
• 列表分区（List），根据某字段的某个具体值进行分区。例，长沙用户分成一个区，北京用户分成一个区。

*	范围分区	哈希分区	列表分区
数据值	连续	连续、离散均可	离散
数据管理能力	强	弱	强
实施难度与可维护性	差	好	差
数据分布	不均匀	均匀	不均匀

分区的优点：

• 相对于单个文件系统或是硬盘，分区可以存储更多的数据。
• 数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可。
• 精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率。
• 可跨多个分区磁盘查询，来提高查询的吞吐量。
• 在涉及聚合函数查询时，可以很容易进行数据的合并。

数据库主从复制

主从数据库结构特点：

1. 一般：一主多从，也可以多主多从。
2. 主库做写操作，从库做读操作。

主从复制步骤：

1. 主库（Master）更新数据完成前，将操作写 binlog 日志文件。
2. 从库（Salve）打开 I/O 线程与主库连接，做 binlogdump process，并将事件写入中继日志。
3. 从库执行中继日志事件，保持与主库一致。

NoSQL非关系型数据库

NoSQL，Not-only SQL，泛指非关系型数据库。

NoSQL 的使用场景	NoSQL 的缺点不足
数据模型比较简单	成熟度不够，大量关键特征有待实现
需要灵活性更强的 IT 系统	开源数据库产品的支持力度有限
对数据库性能要求较高	数据挖掘与商务智能支持不足
不需要高度的数据一致性	现有的产品无法直接使用 NoSQL
对于给定 key 容易映射复杂值的环境	擅长 NoSQL 的专家较少

与关系型数据库对比

*	关系型数据库模式	NoSQL
并发支持	支持并发，但效率低	并发性能高
存储与查询	关系表方式存储，SQL 查询	海量数据存储，查询效率高
扩展方式	向上扩展	向外扩展
索引方式	B 树，哈希等	键值索引
应用领域	通用领域	特定应用领域
成本	花费巨大，成本高	部署简单，成本低，开源
查询速度	数据存储于硬盘中，SQL 查询慢	数据存储于缓存中，查询速度快
存储数据类型	只支持基础数据类型，结构化	支持基础和对象，集合等非结构化类型
扩展性	多表查询，扩展复杂	基于键值对，容易水平扩展
持久存储	支持海量数据存储	数据在缓存中，不适用于持久存储
数据一致性	需要事务，强调数据的强一致性	不提供、弱事务处理，数据一致性较弱
数据容量	数据有限	海量数据
标准化	是	否

缓存技术

常见缓存技术：

• MemCache：Memcache 是一个高性能的分布式的内存对象缓存系统，用于动态 Web 应用以减轻数据库负载。Memcache 通过在内存里维护一个统一的巨大的 hash 表，它能够用来存储各种格式的数据,包括图像、视频、文件以及数据库检索的结果等。

• Redis：Redis 是一个开源的使用 ANSIC 语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value 数据库,并提供多种语言的 APl。

• Squid：squid 是一个高性能的代理缓存服务器，Squid 支持 FTP、gopher、HTTPS 和 HTTP 协议。

Redis

参考另一篇文章的Redis内容
链接: 软考高级-系统分析师-案例分析-系统设计

缓存预热

系统上线后，将相关需要缓存的数据直接加到缓存系统中。

解决：

• 直接写个缓存刷新页面，上线时手工操作。
• 数据量不大时，可以在项目启动的时候自动进行加载。
• 定时刷新缓存。

缓存更新

除 Redis 系统自带的缓存失效策略，常见采用以下两种：

• 定时清理过期的缓存。
• 当有用户请求过来时，再判断这个请求所用到的缓存是否过期，过期的
  话就去底层系统得到新数据并更新缓存。

缓存降级

降级的目的是保证核心服务可用，即使是有损的，而且有些服务是无法降级的（如电商的购物流程等)。

在进行降级之前要对系统进行梳理，从而梳理出哪些必须保护，哪些可降级。

案例

某电子商务企业随着业务不断发展，销售订单不断增加，每月订单超过了 50万笔，急需开发一套新的互联网电子订单系统。同时该电商希望建立相应的数据中心，能够对订单数据进行分析挖掘，以便更好地服务用户。王工负责订单系统的数据库设计与开发，初步设计的核心订单关系模式为：orders（order_no，customer_no，order_date，product_no，price，……）。考虑订单数据过多，单一表的设计会对系统性能产生较大影响，仅仅采用索引不足以解决性能问题。因此，需要将订单表拆分，按月存储。王工采用反规范化设计方法来解决，给出了相应的解决方案。李工负责数据中心的设计与开发。李工认为王工的解决方案存在问题，建议采用数据物理分区技术。在解决性能问题的同时，也为后续的数据迁移、数据挖掘和分析等工作提供支持。

【问题 1】
常见的反规范化设计包括增加冗余列、增加派生列、重新组表和表分割。为解决题干所述需求，王工采用的是哪种方法？请用 300 字以内的文字解释说明该方法，并指出其优缺点。

王工采用的是表分割方式中的水平分割（分割参数是：“月”，不同的月份使用不同的关系表）。

表分割包括水平分割与垂直分割两种形式：

• 水平分割：按记录进行分割，不同的记录可以分开保存，每个子表的列数相同。分割的条件可能是某列或多列数据的值，如时间参数。
• 垂直分割：按进行分割，即把一条记录分开多个地方保存，每个子表的行数相同。把主键和一些行放到一个表，然后把主键和另外的列放到另一个表中，通过主键进行关联。

王工采用水平分割的优点：水平分割后可以降低在查询时需要读取的数据和索引页数，同时也降低了索引的层数，提高查询速度。同时，按月存储有利于数据迁移、备份和管理。

缺点：逻辑上破坏了关系概念的完整性，由一个关系变为多个关系，在进行历史数据挖掘和分析时，需要执行集合并操作，处理起来比单表操作更加复杂。

【问题 2】
根据需求，李工宜选择物理水平分区中的哪种分区方法？请用 300 字以内的文字分别解释说明该方法的优缺点。

李工宜选择范围分区方式。

范围分区优点如下：
（1） 分区表可以将表存储到多个表空间内，各个分区维护各自的本地索引，查询语句可以根据索引进行分区范围查找，提高了查询速度。
（2） 范围分区提供良好的数据迁移、备份和管理能力，利于维护。
（3） 实现容易，而且可以方便地对表的分区进行添加、删除、拆分和合并操作。

范围分区缺点：
随着时间的增加，日期数据发生变化，DBA 需要对分区进行维护，以增加新的分区。数据在分区上不均匀。可维护性上比较差，所以可以与哈希分区组合应用。

经过分析，张工认为当前预约订单信息表存储了所有订单信息，记录已达到了百万级别。系统主要的核心功能均涉及对订单信息表的操作，应首先优化预约订单信息表的读写性能，建议针对系统中的 SQL 语句，建立相应索引，并进行适当的索引优化。针对张工的方案，其他设计人员提出了一些异议，认为索引过多有很多副作用。请用 100 字以内的文字简要说明索引过多的副作用。

索引过多的副作用有：
（1）过多的索引会占用大量的存储空间。
（2）更新开销，更新语句会引起相应的索引更新。
（3）过多索引会导致查询优化器需要评估的组合增多。
（4）每个索引都有对应的统计信息,索引越多则需要的统计信息越多。
（5）聚集索引的变化会导致非聚集索引的同步变化。

在系统运行过程中，李工发现后台管理人员执行的订单地址信息汇总等操作，经常出现与普通用户的预约订单操作形成读写冲突，影响系统的性能。因此李工建议采用读写分离模式，采用两台数据库服务器，并采用主从复制的方式进行数据同步。请用 100 字以内的文字简要说明主从复制的基本步骤。

主从复制的基本步骤：
（1）主服务器将所做修改通过自己的 I/O 线程，保存在本地二进制日志中。
（2）从服务器上的 I/O 线程读取主服务器上面的二进制日志，然后写入从服务器本地的中继日志。
（3）从服务器上同时开启一个 SQL thread，定时检查中继日志，如果发现有更新则立即把更新的内容在本机的数据库上面执行一遍。

刘工提出的方案采用了 Key-Value 数据库 + MySQL 数据库的混合方案，是根据数据的读写特点将数据分别部署到不同的数据库中。但是由于部分数据可能同时存在于两个数据库中，因此存在数据同步问题。请用 200 字以内的文字简要说明解决该数据同步问题的三种方法。

（1）实时同步方案，先查缓存，查不到再从 DB 查询，并保存到缓存；更新缓存时先更新数据库，再将缓存设置过程期更新缓存。
（2）异步队列方式同步，可采用消息中间件处理。
（3）通过数据库插件完成数据同步。
（4）利用触发器进行缓存同步。

请用 100 字以内的文字解释分布式数据库的概念，并给出提高分布式数据库系统性能的 3 种常见实现技术。

分布式数据库是由一组数据组成的，这组数据分布在计算机网络的不同计算机上，网络中的每个节点具有独立处理的能力（称为场地自治），它可以执行局部应用，同时，每个节点也能通过网络通信子系统执行全局应用。
（1）采用数据分片技术，提高访问的局部性，提升系统性能。
（2）采用查询优化技术（包括：全局查询树的变换、副本的选择与多副本的更新策略、查询树的分解、半连接与直接连接） 提高查询速度。
（3）读写分离技术。
（4）负载均衡技术。
（5）分布式缓存技术

针对 B2C 商务购物平台的数据浏览操作远远高于数据更新操作的特点，指出该系统应采用的分布式数据库实现方式，并分析原因。

可以采用一主多从的主从复制技术进行读写分离。在本题所涉及到的环境中，浏览操作远远高于数据更新操作，可以在分布式数据库中采用主从复制技术实现读写分离。主数据库负责写操作，从数据库进行读操作，在大数据量访问数据库时，能很大程度上提升性能。

刘工认为李工的方案存在数据可靠性和一致性的问题，请说明原因。为避免数据可靠性和一致性的问题，刘工的方案采用 Redis 作为数据库缓存，请说明基本的 Redis 与原有关系数据库的数据同步方案。

（1）Memcache 没有持久化功能，所以掉电数据会全部丢失，而且无法直
接恢复，这存在可靠性问题。
（2）Memcache 不支持事务，所以操作过程中可能产生数据的不一致性。

同步方案：
读取数据时，先读取 Redis 中的数据，如果 Redis 没有，则从原数据库中读取，并同步更新 Redis 中的数据。写回时，写入到原数据库中，并同步更新至 Redis 中。

请给出 Redis 分布式存储的 2 种常见方案和 Redis 集群切片的几种常见方式。

Redis 分布式存储的常见方案：
主从模式（Master/Slave），哨兵模式（Sentinel），集群模式（Cluster）

Redis 集群切片的常见方式：
（1）客户端分片，（2）中间件实现分片，（3）客户端服务端协作分片