【系统分析师】-案例篇-数据库

1、分布式数据库

分布式数据库是由一组数据组成的，这组数据分布在计算机网络的不同计算机上，网络中的每个节点具有独立处理的能力，即场地自治，它可以执行局部应用，同时，每个节点也能通过网络通信子系统执行全局应用。

1）请用300字以内的文字简述分布式数据库跟集中式数据库相比的优点。

 （1）坚固性好。由于分布式数据库系统在个别结点或个别通信链路发生故障的情况下，它仍然可以降低级别继续工作，系统的坚固性好，可靠性和可用性好。

    （2）可扩充性好。可根据发展的需要增减结点，或对系统重新配置，这比用一个更大的系统代替一个已有的集中式数据库要容易得多。

 （3）可改善性能。在分布式数据库中可按就近分布，使大部分数据可以就近访问，避免了集中式数据库中的瓶颈问题，减少了系统的响应时间，提高了系统的效率，而且也降低了通信费用。

    （4）自治性好。数据可以分散管理，统一协调，即系统中各结点的数据操纵和相互作用是高度自治的，不存在主从控制。

2）分布透明性是指用户不必关心数据的逻辑分片，不必关心数据存储的物理位置分配细节，也不必关心局部场地上数据库的数据模型。分布透明性包括哪三种透明性。

①　分片透明性是分布透明性的最高层次。所谓分片透明性是指用户或应用程序只对全局关系进行操作而不必考虑数据的分片。

②　位置透明性是分布透明性的下一层次。所谓位置透明性是指，用户或应用程序应当了解分片情况，但不必了解片段的存储场地。

③　局部数据模型（逻辑透明）透明性是指用户或应用程序应当了解分片及各片断存储的场地，但不必了解局部场地上使用的是何种数据模型。

3）数据表进行分片设计。一般有四种分片方法：

（1）水平分片【元组】

（2）垂直分片【属性列】

（3）导出分片 【水平分片的条件不是本关系属性的条件，而是其他关系属性的条件。】

（4）混合分片 【水平 + 垂直】

数据分片原则：

（1）完整性。全局关系的所有数据都必须分配到各个片段中，不允许某些数据属于全局关系但不属于任务片段。

（2）重构性。各个片段可以重构原来的全局关系。

（3）不相交性。全局关系中的每个元组仅属于一个片段，不能在多个片段中重复出现。

4）分布式数据库特点：

（1）数据独立性。包括逻辑独立性与物理独立性，还有数据分布独立性。

（2）集中与自治共享结合的控制结构。各局部的DBMS可以独立地管理局部数据库，具有自治的功能，同时有设有集中控制机制，协调各局部DBMS的工作，执行全局应用。

（3）适当增加数据冗余度。在不同场地存储同一数据的多个副本，这样，可以提高系统的可靠性和可用性，同时也能提高系统性能。

（4）全局数据的一致性、并发操作的可串行和故障的全局可恢复性。

参考文章 软考笔记--分布式数据库_软考高级 分布式数据库-CSDN博客

2、数据库索引

1）针对每条SQL语句都建立索引的建议是否合适请简要说明理由。

①如果建立索引不当，数据库管理系统将不利用已经建立的索引，而采取全表扫描。

②当更新操作成为系统瓶颈时，因为每次更新操作会重建表的索引，则需要考虑删除某些索引。

③应该针对不同应用情况选择适当的索引类型。例如，如果经常使用范围查询，则B树索引比散列索引更加高效。

④应该将有利于大多数据查询和更新的索引设为聚类索引。

⑤需要对建立的索引进行实际的测试，因为索引的使用是由数据库管理系统(数据库优化器)决定的。

过多索引问题：

（1）过多索引会占用大量存储空间；

（2）更新语句会引发索引更新，过多索引更新开销较大；

（3）过多索引导致索引的历史统计信息过多。

（4）索引过多导致查询优化器需要评估组合增多。

（5）聚集索引的变化会导致非聚集索引的同步变化。

 2）列举出4条SQL语句优化的基本策略

①建立物化视图或尽可能减少多表查询。

②以不相干子查询替代相干子查询。

③只检索需要的列。

④用带IN的条件子句等价替换OR子句。

⑤经常提交COMMIT，以尽早释放锁。

⑥避免嵌套的游标(Cursor)和多重循环等

3、数据架构

张工认为应该采用集中式的数据架构，给出的理由是结构简单、易维护且开发及运行成本低；

刘工建议采用分布式的数据架构，并提出在开发中通过“局部数据库+缓存”的读写分离结构实现，具有较好的运行性能和可扩展性。

1）说明张工和刘工提出的数据架构的基本思想

（1）张工提出的集中式数据架构是由一个处理器、与它相关联的数据存储设备以及其他外围设备组成，它被物理地定义到单个位置。系统提供数据处理能力，用户可以在同样的站点上操作，也可以在地理位置隔开的其他站点上通过远程终端来操作。系统及其数据管理被某个或中心站点集中控制。

（2）刘工提出的分布式数据架构使用多个计算机系统上的多个局部数据库系统构成，数据可以在多个不同的局部数据库中进行传送，由不同的数据库管理系统软件进行管理，运行在多种不同的计算机上，支持多种不同的操作系统。这些机器位于（或分布在）不同的地理位置并通过多种通信网络连接在一起。企业数据可以分布在不同的计算机上，一个应用程序可以操作位于不同地理位置的机器上的数据。

2）如何实现刘工建立CRSS的数据库系统，数据可以分布在不同的计算机上，一个应用程序可以操作位于不同地理位置的机器上的数据。

分布式数据库系统需要由多个局部数据库系统、多个热备份数据库系统和多个数据缓存组成。局部数据库负责数据的写入，多个热备份数据库系统用以解决单点故障的问题，数据缓存负责为应用提供所读取的数据。

3）对于数据的读取、添加、更改和删除操作分别如何实现。

（1）读取数据：应用访问缓存，如果命中则返回，否则从局部数据库系统中读取数据并将数据加载到缓存后返回。

（2）添加数据：采用延迟加载策略，应用将数据直接写入局部数据库。

（3）更改数据：应用更改局部数据库中的数据，将缓存中的数据标记为失效。

（4）删除数据：应用删除局部数据库中的数据，将缓存中的数据标记为失效。

4）分别说明在集中式和分布式数据架构下，可以采用哪些方法提升系统的可扩展性。

张工提出的集中式数据架构通过向上扩展（Scale Up）提升系统的可扩展性。具体的实现方式包括硬件扩容（增加CPU数量、内存容量、磁盘数量）和硬件升级（更换为高端主机或高速磁盘等）。

 刘工提出的分布式数据架构通过向外扩展（Scale Out）提升系统的可扩展性。具体的实现方式包括数据复制、数据垂直切分（或/和）水平切分、缓存和全文搜索。

4、读写分离

1）简述主从复制给系统带来的好处。

①避免数据库单点故障：主服务器实时、异步复制数据到从服务器，当主数据库宕机时，可在从数据库中选择一个升级为主服务器，从而防止数据库单点故障。

 ②提高查询效率：根据系统数据库访问特点，可以使用主数据库进行数据的插入、删除及更新等写操作，而从数据库则专门用来进行数据查询操作，从而将查询操作分担到不同的从服务器以提高数据库访问效率。

2）MySQL 数据库中，主从复制是通过 binary log 来实现主从服务器的数据同步。请简述主从复制的过程。

当在从库上启动复制时，首先创建I/O线程连接主库，

主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，

I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，

从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用。

3）主从复制可以采用同步、异步、半同步复制。请简述每种复制技术的特点。

（1）同步复制主数据库需要等待所有备数据库均操作成功才可以响应用户，影响用户体验。这种方式保证了系统的一致性，但牺牲了数据的可用性。

（2）异步复制：当用户请求更新数据时，主数据库处理完请求后可直接给用户响应，而不必等待备数据库完成同步，备数据库会异步进行数据的同步，用户的更新操作不会因为备数据库未完成数据同步而导致阻塞。这种方式保证了系统的可用性，但牺牲了数据的一致性。

（3）半同步复制：用户发出写请求后，主数据库会执行写操作，并给备数据库发送同步请求，但主数据库不用等待所有备数据库回复数据同步成功便可响应用户，也就是说主数据库可以等待一部分备数据库同步完成后响应用户写操作执行成功。

4）数据物理分区

数据库分区是将数据量大的表的数据均摊到不同的硬盘、系统或不同服务器存储介质中，但实际上还是一张表。数据库分区表可以减少SQL操作中数据读写的总量，减少响应时间，提升查询效率。

5、缓存一致性

1）数据库缓存同步过程

1.根据key读缓存；

2.读取成功则直接返回；

3.若key不在缓存中时，根据key （读数据库） ；

4.读取成功后，（更新缓存） ；

5.成功返回。

写数据操作的基本步骤：

1.根据key值写 （数据库） ；

2.成功后 （更新/删除缓存Key值/使缓存Key值失效） ；

3.成功返回。 .

2）Redis分布式存储方案

主从复制模式、哨兵（sentinel）模式、集群（Cluster）模式。

String类型可用于缓存层或计数器，如视频播放量、文章浏览量等。

Hash类型可用于描述用户信息。

list类型可用于回复评论、点赞。

Zset类型可用于数据排名。

3）解决Redis与Mysql数据一致性问题

1）同步双写

2）数据库插件，类似于从库同步方式

3）消息中间件，异步处理数据同步任务

4）触发器同步缓存

6、反规范化

1）反规范化设计方法

常见的反规范化方法有：

1、增加冗余列：增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作。

2、增加派生列：增加派生列指增加的列可以通过表中其他数据计算生成。它的作用是在查询时减少计算量，从而加快查询速度。

3、重新组表：重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。

4、水平分割表：按记录进行分割，把数据放到多个独立的表中，主要用于表数据规模很大、表中数据相对独立或数据需要存放到多个介质上时使用。

5、垂直分割表：对表进行分割，将主键与部分列放到一个表中，主键与其它列放到另一个表中， 在查询时减少 I/0 次数。

解决反规范化一致性问题：

1）触发器同步

2）应用程序数据同步

3）物化视图

7、NoSQL

1）分类：

①　键值(Key-Value)数据库，每一条记录就是一个键/值对。键是一个字符串对象，而值是可以任意类型的数据。优点是扩展性好，灵活性好，大量写操作时性能高。

②　列族数据库，每个列可以有多个值和时间戳。优点是查找速度快，可扩展性强，容易进行分布式扩展，复杂性低。

③　文档数据库，其记录格式是版本化的半结构化的文档。优点是性能好（高并发），灵活性高，复杂性低，数据结构灵活，将经常查询的数据存储在同一个文档中，既可以根据键来构建索引，也可以根据内容构建索引。

④　图形数据库，使用灵活的图形模型，专门用于处理具有高度相互关联关系的数据。优点是灵活性高，支持复杂的图形算法，可用于构建复杂的关系图谱。

8、云数据库

1）简要说明什么是云数据库以及云数据库的特点。

云数据库是指被优化或部署到一个虚拟计算环境中的数据库，具有按需付费、按需扩展、高可用性以及存储整合等能力。

云数据库的特点有：实例创建快速、支持只读实例、读写分离、故障自动切换、数据备份、Binlog备份、SQL审计、访问白名单、监控与消息通知等。

（1）实例创建快速：根据需求配置参数，可在几分钟内，配置好数据库实例。

（2）支持只读实例：对数据库有大量读请求而非写请求的读写场景，可以创建多个只读实例进行系统扩展。

（3）故障自动切换：主库发生不可预知的故障（如硬件故障）时，可在短时间内进行自动切换。

（4）数据备份：具有自动数据库实例备份、手动数据库实例备份（数据快照）功能，并可以快速从数据快照恢复数据库实例。

（5）Binlog备份：自动备份Binlog日志。

（6）访问白名单：可通过设置IP白名单的方式来控制数据库访问权限。

（7）监控与消息通知：可根据数据库运行状态，定制监控策略。超过监控阈值或者出现变更时，可通过短信、邮件等方式告警。

2）层次型架构的优势如下：

1）开发分工明确，可以专注理解一层开发。

2）层与层之间是低耦合，层内是高内聚。

3）每一层通过接口为其他层提供服务，因此可修改性高。

4）可复用性强。

9、主题数据库

1）设计要求：

保证稳定性；

确保能加快应用系统开发；

保持主题数据库逻辑结构的独立性，不因计算机的软硬件不同而受到影响。

2）基本特征：

（1）面向业务主题。主题数据库是面向业务主题的数据组织存储，围绕需要解决的问题展开。

（2）信息共享。主题数据库彻底否定各个系统“自建自用”数据库，强调各个系统“共建共享”数据库。

（3）一次一处输入系统。同一数据必须一次、一处进入系统，确保数据的准确性、完整性、及时性，保证可以多次、多处使用。

（4）由基本表组成。基本表具有原子性、演绎性、基本表中数据满足三范式（3NF）要求等特性。

10、数据同步案例

某大型企业在全国各城市共有40个左右的分支机构，这些机构已经建设了基于大型关系数据库的信息管理系统，每天负责独立地处理本区域内的业务并实时存储业务数据。 

每个机构的数据库服务器从PC Server（最小512MB内存）到RISC小型机（最大4GB内存）配置不一，操作系统也不一样，数据库产品类型也不统一。 

每个机构平均每天产生约4MB的数据，数据表中包括TEXT字段；每季度一次性产生约100MB的统计和报表数据。 

为了加强管理，实现对下属机构业务数据的异地存储备份，保证数据的安全及恢复，同时对全国业务数据进行挖掘分析，该企业拟在总部建设数据中心。数据中心通过公共广域网与各个分支机构组成VPN，每个分支机构到数据中心的逻辑信道带宽最高为128Kb/s。 

1） 为保证分支机构可靠、高效地向数据中心汇总业务数据，避免单点故障，除了考虑广域网线路采用备份外，在数据中心还应采用什么技术？

数据中心数据库服务器采用多机集群Cluster

数据库并行处理技术，

存储设备采用全冗余的SAN结构

2）假设各个分支机构的历史数据已经通过某种方式顺利地存储到了数据中心。对于增量数据的汇总更新，总部的王副总工提议采用数据库复制技术和数据库的触发器技术获取数据更新日志的方式来完成，但张总认为这样做不仅增加了成本，而且不能解决全部问题。

采用数据库复制技术，各地需要安装专门的复制服务器，增加成本，维护管理较为复杂，同时，太多分支机构使得中心的数据库复制服务器压力大，因此在本题要求的条件下，不宜采用数据库复制技术。 

采用数据库触发器技术虽然能够实时记录数据库的数据变化，但不能捕获数据表中的TEXT字段的UPDATE动作，并且对于每季度一次的统计报表数据，采用数据库的触发器技术来记录数据库的变化，占用数据库资源太多，可能影响某些机构的日常业务处理。

3）如何解决数据中心数据更新与同步问题

 （1） 针对多数业务数据的更新，各地数据库采用触发器技术，通过触发器捕获记

录或文字的增删改操作，以标准的SQL命令保存到数据更新日志中； 

（2） 改造各地原有业务系统，当发生数据表TEXT字段修改时，在修改字段的同

一事务中，将该动作增加到数据更新日志中，数据中心根据记录抽取该字段指向的内容； 

（3） 对每季度产生的报表统计数据，改造各地原有业务系统，在数据更新日志中

保存生产数据的条件，数据中心根据记录一次性抽取满足条件的数据。 

（4）最后，针对个别机构数据库服务器配置较低，采用触发器技术可能造成资源不足的情况，升级该机构的数据库服务器，比如将内存增加到1GB等。