- 相关推荐
2016计算机三级数据库分析真题
2016年上半年计算机考试已经过去了，yjbys小编第一时间为大家分享的是本次计算机三级数据库的考试真题，希望对大家有所帮助!
Q1:
设某全国性的运输企业建立了大型OLTP系统，并在该系统之上建立了数据仓库。OLTP系统和数据仓库中有如下数据表：
运输明细表(运输单ID，发送站ID，终到站ID，货物ID，货物重量，运输价格，发货日期)
汇总表1(发送站ID，终到站ID，货物ID，发货日期，总重，总运价)
汇总表2(发送站ID，终到地区ID，货物ID，发货日期，总重，总运价)
汇总表3(发送站ID，终到站ID，货物ID，发货月份，总重，总运价)
汇总表4(发送地区ID，终到地区ID，货物类别ID，发货日期，总重，总运价)
该企业管理的货运站约有100个，货物约有500种共10类，各汇总表都建有主码，且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其查询结果模式为(发送地区ID，终到站ID，发货月份，总重，总运价)，该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。
A1:
由于汇总表1和视图的模式访问频率都很高，而且视图的数据源来自汇总表1，又因为其他汇总表的访问率较低，所以只需要将视图的数据源绑定为汇总表3，因为汇总表3也可以满足视图的输出模式。这样不仅提升了汇总表3的数据访问率，而且降低了汇总表1的数据访问率，系统性能和服务性能得到了很大的优化。又因为货物约有500种，共10类，可以再建立一个视图绑定数据源为汇总表4，这样就可以充分利用汇总表4的数据信息，从而可以进一步完善系统性能的优化。
Q2:
在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：
系(系号，系名)，系号为主码
学生(学号，姓名，所在系号)，学号为主码
课程(课程号，课程名，开课系号)，课程号为主码
选课(学号，课程号，选课时间)，学号和课程号为主码(8分)
开发人员在将关系模式实施到SQL Server 2008的"教务"数据库时，使用了如下表结构定义语句：
CREATE TABLE 系 (
系号 varchar(10) NOT NULL ,
系名 varchar(100))
CREATE TABLE 学生 (
学号 varchar(50) NOT NULL ,
姓名 varchar(50),
所在系号 varchar(10))
CREATE TABLE课程 (
课程号 varchar(50) NOT NULL ,
课程名 varchar(100),
开课系号 varchar(10))
CREATE TABLE 选课 (
学号 varchar(50) NOT NULL ,
课程号 varchar(50) NOT NULL ,
选课时间 datetime )
在执行如下查询语句时发现执行效率很低：
SELECT * FROM 选课 JOIN 学生 ON 学生.学号= 选课.学号
JOIN 系 ON 系.系号 = 学生.所在系号
JOIN 课程 ON 课程.课程号 = 选课.课程号
WHERE 系.系号=′012′
AND convert(varchar(10),选课时间,120)>= ′2010-01-01′
(1)在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。(4分)
(2)设已在"选课"表的"选课时间"列及"学生"表的"所在系号"列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率?如果不能，请说明原因。(4分)
A2:
(1) 【解题思路】
本题中查询语句的功能是得到12系全体学生在2010年1月1日后的选课情况的汇总表。在每个数据表的定义时都必须严格定义表中的完整性约束条件，包括主键的设置，否则之后会出现主键有相同值的情况，破坏了数据的完整性。
【参考答案】
建表时没有设置主键，也没有说明外键。但不会影响此查询语句的执行效率
(2)【解题思路】
建立索引是加快查询速度的有效手段，用户可以根据应用环境的需要建立一个或多个索引，以提供多种存取路径，加快查找速度。索引就像书的目录一样为我们将记录按规定的列进行了排序，这样当我们要访问满足这些列的某些条件的记录时，索引会为我们减少查询数据库中的地址范围，大大节省了时间。所以根据WHERE条件后的字段对表建立索引对于提高查询效率是有帮助的。
【参考答案】
"选课"表的"选课时间"列上建立了索引，从而能够提高执行效率。经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。索引的作用就类似于书的目录，即会按照章节的顺序排列。因此如果在一本数百页的书里面查找某个章节位置的时候，就可以只扫描书的目录。扫描的范围缩了n倍，查询的效率自然就会提高。另外，在SQLServer内存够用的情况下，索引会被放到内存中，在内存中查找自然又会提高效率，所以必须合理利用索引。
Q3:
某商场商品经营管理系统使用SQL Server 2008数据库管理系统，此系统上线运行1年后，业务人员使用某统计功能(此功能每月使用一次)时发现速度很慢。该统计功能主要执行的SQL语句如下：
SELECT 商品号,SUM(销售数量*销售价格) 销售额
FROM 销售明细
GROUP BY 商品号;
该销售明细表的建表语句如下：
CREATE TABLE 销售明细(
序列号 intIDENTITY(1,1) NOT NULL,
商品号 intNOT NULL,
销售日期 datetime NULL,
销售数量 intNOT NULL,
销售价格 intNOT NULL
);
并在销售明细表上建有如下索引：
CREATE index ix_销售明细_商品号 on 销售明细(商品号);
某技术人员提出通过执行下述语句以提高此查询的运行效率：
CREATE VIEW 商品销售额视图
WITH SCHEMABINDING
AS
SELECT 商品号,SUM(销售数量*销售价格) 销售额,
COUNT_BIG(*) cnt
FROM dbo.销售明细
GROUP BY 商品号;
CREATE UNIQUE CLUSTERED INDEXix_商品销售额
ON 商品销售额视图(商品号);(10分)
(1)请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。
(2)此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。
A3:
(1)【解题思路】
该技术人员使用了带有索引的视图，将所关心的数据(商品号，销售额，该商品号在表中出现的次数)从销售明细表中提取出来建立视图，并对该视图建立按商品号排序的聚簇索引，这样大大减少了在搜索不同商品的销售额时调用的数据表的规模，从而提高了查询效率。由于表的数据规模很大，建立该视图后，同一种商品不会多次出现在表中，而是通过一个计数变量cnt表示，这样在检索时大大减少了检索规模。创建索引时，UNIQUE关键字表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。
【参考答案】
语句功能：建立包含(商品号，销售额，该商品表中出现次数)的带索引的视图，并建立按商品对应销售额UNIQUE聚簇排序的索引，大大缩小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列(序列号，销售日期，商品号，销售数量，销售价格)转换成了目标属性列，减少了搜索空间;同时建立UNIQUE CLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。
(2)【解题思路】
由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。而用户通过视图对数据进行增加、删除、修改时，有意或无意地对不属于视图范围内的基本表数据进行操作，会破坏数据的一致性。而且视图中的数据本身就是冗余的，每次对表进行修改时，同时也要对相应的视图进行修改，这大大增加了系统的负担。
【参考答案】
不合适，每天大量的插入操作在修改表的同时也要对视图进行修改，增加了系统的负担，然而该统计功能一个月才用一次，这样导致系统的利用率也较为低下。
Q4:
某教务管理系统使用SQL Server 2008数据库管理系统，数据库软硬件配置信息如下：
Ⅰ.数据库运行在两路Intel Xeon E5-2609 2.4GHz CPU(每路CPU4核心)，128GB内存、2块300GB15000转SAS硬盘(RAID 1)的服务器上;
Ⅱ.服务器操作系统为Windows 2003 32位企业版，安装SP2补丁;
Ⅲ.数据库为SQL Server 2008 Enterprise(32 位)，数据库中总数据量近130GB。
近一个学期以来，用户反映系统运行缓慢，经程序员定位，确定为数据库服务器响应缓慢，需要进行调优。
A4：
【解题思路】
数据库性能优化的基本原则就是通过尽可能少的磁盘访问获得所需要的数据。SQL SERVER性能优化一般从数据库设计、应用程序编码、硬件优化、数据库索引、SQL语句、事务处理几个方面入手考虑问题。
(1) 分析阶段：在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求吸引了我们大部分的注意力，但必须注意的是，性能往往是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求量化的指标。
(2) 设计阶段：例如数据库逻辑设计规范化;合理的冗余;主键的设计;外键的设计 ;字段的设计 ;数据库物理存储和环境的设计;数据库的物理存储、操作系统环境及网络环境的设计，皆使得我们的系统在将来能适应较多用户的并发操作和较大的数据处理量。 这里需要注意文件组的作用，适用文件组可以有效的把I/O操作分散到不同的物理硬盘，提高并发能力。
(3) 系统设计：整个系统的设计，特别是系统结构的设计对性能具有很大的影响。对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。系统设计阶段应归纳些业务逻辑在数据库编程阶段实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并能更充分利用数据库的预编译和缓存功能;索引设计阶段可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用时有所区别。
(4) 编码阶段：编码阶段首先需要所有程序员具备优化意识，也就是在实现功能的同时具备考虑优化性能的思想。数据库是能进行集合运算的工具，所谓集合运算实际是批量运算，即是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。这个阶段主要是注意在SQL语句等方面的优化，如：尽量少做重复的工作，用SELECT后跟需要的字段代替SELECT*语句，注意事务和锁 ，注意临时表和表变量的用法，慎用游标和触发器，尽量使用索引等。
(5) 硬件优化：RAID (独立磁盘冗余阵列)是由多个磁盘驱动器(一个阵列)组成的磁盘系统。通过将磁盘阵列当作一个磁盘来对待，基于硬件的RAID允许用户管理多个磁盘。使用基于硬件的RAID与基于操作系统的RAID相比较可知，基于硬件的RAID能够提供更佳的性能，如果使用基于操作系统的RAID，那么它将占据其他系统需求的CPU周期，通过使用基于硬件的RAID，用户在不关闭系统的情况下能够替换发生故障的驱动器。利用数据库分区技术，可均匀地把数据分布在系统的磁盘中，平衡I/O 访问，避免I/O 瓶颈等。
(6) 事务处理调优：数据库的日常运行过程中，可能面临多个用户同时对数据库的并发操作而带来的数据不一 致的问题，如：丢失更新、脏读和不可重复读等。并发控制的主要方法是封锁，锁的含义即是在一段时间内禁止用户做某些操作以避免产生数据不一致。对于事务性能的调优，要考虑到事务使用的锁的个数(在所有其他条件相同的情况下，使用的锁个数越少，性能越好)、锁的类型 (读锁对性能更有利)以及事务持有锁的时间长短(持有时间越短，性能越好)等情形。
【参考答案】
(1)表结构优化：重新优化数据库设计结构，数据库逻辑设计的规范化;设计主键和外键;设计合适大小的字段。
(2)硬件优化：购买一块同样大小的硬盘，将硬盘做成RAID5,用以提高数据库相应速度;增加服务器CPU个数;扩大服务器的内存。
(3)索引优化：采用对经常作为条件查询的列设计索引，查询经常用到的列上建立非聚簇索引，在频繁进行范围查询、排序、分组的列上建立聚簇索引，对于有频繁进行删除、插入操作的表不要建立过多的索引。
(4)采用视图：合理使用视图和分区视图，在需要更新和删除操作不多、查询操作频繁的表上建立索引视图。
(5)SQL语句优化：择运算应尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语 句放在前面;较弱的选择条件写在后面，这样就可以先根据较严格的条件得出数据较小的信息，再在这些信息中根据后面较弱的条件得到满足条件的信息;应避免使用相关子查询。把子查询转换成联结来实现;字段提取按照"需多少，提多少"的原则，避免"SELECT*"。"SELECT *"需 要数据库返回相应表的所有列信息，这对于一个列较多的表无疑是一项费时的操作，采用存储过程，使用存储过程提高数据处理速度。
Q5：
某采购中心采购了一套商品批发查询管理系统，此系统采用SQLServer 2008数据库管理系统，该系统需要经常处理百万级以上的数据查询。同时该系统提供了第三方人员开发的SQL接口，第三方人员可以根据自己的需要开发自己的应用程序来访问数据库中的相关数据。
(1)系统在使用的过程中，业务人员反应系统操作速度很慢。经过工程师检查测试后，数据库系统本身及网络传输过程中存在着一些问题，请给出针对数据库系统本身及网络传输过程中可能的一些调优方案。
(2)工程师在完成数据库系统本身和网络问题的优化后，发现第三方开发人员的SQL查询语句存在很多没有优化的问题，请从第三方开发人员角度给出一些优化方案。
A5：
【解题思路】
从数据库本身和网络传输的角度出发，数据库性能低下的因素一般有：I/O吞吐量小，形成了瓶颈效应;没有创建计算列导致查询不优化;SQLServer数据库内存不足;网络速度慢;查询出的数据量过大(可以采用多次查询或其他的方法降低数据量)以及锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)。
从SQL查询语句角度来考虑优化响应时间，应该从查询条件、临时表、游标、返回数据量、大事务操作等方面进行调优。
【参考答案】
(1)
①把数据、日志、索引放到不同的I/O设备上，增加读取速度，数据量(尺寸)越大，提高I/O越重要。
②纵向、横向分割表，减少表的尺寸。
③升级硬件，扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存，虚拟内存大小应基于计算机上并发运行的服务进行配置。增加服务器 CPU个数。
④分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。
⑤重建索引 DBCC REINDEX ,DBCCINDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCCSHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。
⑥优化锁结构。
(2）
①对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及 order by 涉及的列上建立索引。
②应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
③任何地方都不要使用 select * from 进行全表扫描，用具体的字段列表代替"*"，不要返回冗余字段
④避免频繁创建和删除临时表，以减少系统表资源的消耗。
⑤尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
⑥尽量避免大事务操作，提高系统并发能力。
Q6：
某网上商城因为业务发展，原有的系统不能很好的满足需要，因此采用了一套新的经营管理系统，此系统使用SQLServer 2008数据库管理系统。此系统上线运行前，需将商场原有的数据导入到新系统中。原有系统使用SQL Server 2000，数据结构与新系统不完全一致。因此需要把数据从SQLServer 2000导入到SQL Server 2008中，为了保证数据一致性，数据导入过程中要求暂停业务且必须在3小时内完成。
(1)在原有数据导入新系统的过程中，实施人员发现原有数据量很大，导入数据需要四小时，业务无法接受。经分析某工程师认为，数据导入过程中的数据库I/O很高，但导入数据的程序本身对系统资源占用率很低。该工程师建议将数据导入过程中的数据恢复模式从"完整"模式改为"简单"模式以提高数据导入速度;而另一位工程师则认为此方法未必能提高数据导入速度，而且还可能导致数据丢失，不建议使用此方法。
请分析此方法是否能够提高数据导入速度并给出理由，同时分析此操作的数据丢失风险。(5分)
(2)在成功导入历史数据后，此系统顺利上线运行。在上线运行的第一周，发现数据库服务器的CPU使用率很高，达到近90%，高峰期间达到100%，且系统内存占用率达到90%，但系统I/O很轻。业务人员反应系统操作速度很慢。为了提高系统运行速度。在不修改应用程序的前提下，两位工程师提出了不同的解决办法：
Ⅰ.为服务器增加2颗CPU，缓解CPU使用率很高的问题;
Ⅱ.为服务器增加一倍内存，缓解内存使用率很高的问题。
考虑成本，现阶段只能按照一种方案实施。请指出在现有情况下，哪种方案更合理并给出理由。(5分)
A6：
【解题思路】
(1)SQL Server 2008的数据恢复模式有三种：
①简单恢复模式，此方法可以最大幅度减少事务日志的管理开销，因为恢复模式不备份事务日志。但是如果数据库损坏，则简单恢复模式将面临极大的数据丢失风险。在这种恢复模式下，数据只能恢复到最新备份状态。因此对于用户数据库，简单恢复模式只适用于测试和开发数据库，或用于主要包含只读数据的数据库。
②完整恢复模式，此方法可以完整的记录所有事务，并将事务日志记录保留到对其备份完毕为止。此方法相对简单恢复模式来说，更占用时间。
③大容量日志恢复模式，此方法只对大容量操作进行最小记录，使事务日志不会被大容量加载操作所填充。
在数据导入过程中，所有的业务都是暂停的，因此可以采用简单恢复模式提高数据导入速度。且只在数据导入的过程中暂时的修改恢复模式，因此数据并不会丢失。
(2)提高数据库性能的方法一般是从外部环境、调整内存分配、调整磁盘I/O、调整竞争资源等几方面着手来改变数据库的参数。SQLServer 2008采用将数据缓冲在内存的方式，因此在数据库系统运行的过程中会占用一定的内存，又因为I/O并不存在问题，说明内存尚满足需求。CPU使用率很高，表明CPU的计算能力不足，应该增加CPU的数量。
【参考答案】
(1)此方法能够提高数据导入速度。
原因：此系统I/O很重，修改恢复模式后，系统最大限度减少日志开销，可提高导入速度。由于仅在数据导入过程中修改恢复模式，并无数据丢失风险。
(2)第一种方案比较合理。
原因：由于SQL Server 2008采用将数据缓冲在内存的方式，因此内存的使用率比较高是正常情况，且现阶段I/O并不存在问题，说明内存满足需求。此阶段CPU使用率很高，说明CPU计算资源不足，因此增加CPU数量对解决问题有效。
Q7：
3.设某超市经营管理系统(C/S结构)使用SQL Server 2008数据库管理系统。系统上线运行1年后，用户反映系统运行速度不稳定，不定期会出现速度变慢的问题。
数据库管理员在数据库服务器上采集了一段时间系统运行状态数据，情况如下：
CPU最高使用率：30%
内存最高使用率：80%
磁盘IO很低
同时，数据库管理员在客户端使用ping命令对数据库服务器进行测试，结果如下
(5分)请根据以上数据，判断最有可能的问题，并给出判断的理由。
在以上问题解决后，用户反映每日的定时统计汇总SQL语句仍然速度比较慢。数据库管理员在系统无任何人员使用时运行该语句，同时在数据库服务器上采集系统运行状态数据，情况如下：
CPU最高使用率：12.5%(CPU配置：2路4核，共8核，关闭超线程)
内存最高使用率：80%(内存配置：128GB)
磁盘IO很低(磁盘配置：5块300GB磁盘，RAID5)
(5分)请分析在进行统计汇总时，数据库服务器最有可能的瓶颈是CPU、内存还是磁盘，并给出判断的理由。
A：
【解题思路】
数据库系统运行需要依赖服务器、操作系统、存储、网络等多种因素。当这些数据库环境出现瓶颈时就会导致数据库运行变慢，甚至是无法完成正常业务。
一般情况下，数据库访问代价模型由四部分组成：CPU处理数据的时间(即CPU代价)，磁盘读取的时间(即I/O代价)，查询的内存开销(即内存代价)，对于网络中的数据库还要加上数据在网络上传输的时间(即通信代价)。因此数据库访问的代价模型是：总代价=CPU代价+I/O代价+内存代价+通信的代价。
(1)CPU。当数据库操作对CPU性能的要求超过数据库服务器的CPU性能时，数据库性能就会受到CPU的限制。对于一台数据库服务器来说，如果其在业务空闲时使用率超过90%，说明服务器缺乏CPU资源;如果高峰时CPU使用率仍然很低，则说明服务器CPU资源充足。据此，可以判断一个数据库系统CPU的使用情况。解决的方法是为服务器适当增加CPU的数量或者终止需要许多资源的进程。
(2)数据库性能优劣的一个重要度量是响应时间，而I/O时间是响应时间的最大组成部分。因此，可以通过令I/O时间最小化，减少磁盘上的文件竞争带来的瓶颈等方法来改善数据库系统的性能。
(3)通过调整相关参数控制数据库的内存分配，也可以在很大程度上改善数据库系统的性能。
(4)大量的SQL数据在网络上传输会导致网速变慢。网卡、交换机、集线器等网络设备的性能对网络的影响很明显，所以，通过调整网络设备，也可以在一定程度上提高数据库系统的性能。
【参考答案】
(1)产生掉线的最有可能的原因是网络产生问题。因为通过ping命令测试，发现丢包率达到10%。而CPU，内存、磁盘I/O使用率没有达到100%，说明还是有一定的富余，引起网络掉线的可能性较小。
(2)数据库服务器最有可能的瓶颈是内存。因为内存的使用率在无任何操作的情况下达到80%，使用率较高。说明内存容量不足以完全支持服务器活动。
对于一台数据库服务器来说，如果其在业务空闲时使用率超过90%，说明服务器缺乏CPU资源;如果高峰时CPU使用率仍然很低，则说明服务器CPU资源充足。据此，可以判断一个数据库系统CPU的使用情况。本题中CPU的最高使用率只有12.5%，说明CPU不可能是数据库服务器的瓶颈。磁盘I/O很低，说明也不可能是服务器的瓶颈。
【计算机三级数据库分析真题】相关文章：
公共英语三级真题09-11
三级营销师考试真题及答案08-21
计算机三级数据库技术考试试题及答案10-08
职称计算机考试真题及答案09-25
2017年公共英语三级考试真题及答案06-25
造价工程师案例分析考试真题09-11
2016最新计算机等级考试真题及答案06-01
2016年计算机等级考试真题及答案05-20
2016年计算机VF模拟真题及答案08-28
计算机二级C语言真题及答案07-11