『SQL Server』考三

第一套 性能优化

设某全国性的运输企业创建了大型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的被访问频率太高，致使系统总体性能降低，而其它汇总表被访问频率较低。在不增长汇总表和索引的状况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。

【解题思路】
　　计算机系统中存在着两类不同的数据处理工作：操作型处理和分析型处理，也称作OLTP(联机事务处理)和OLAP(联机分析处理)。操作型处理也叫事务处理，是指对数据库联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。这些系统要求快速响应用户请求，对数据的完全性、完整性以及事务吞吐量要求很高。结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。因此本题考察了联机事务处理中的资源调度问题。
【参考答案】
　　由于汇总表1和视图的模式访问频率都很高，而且视图的数据源来自汇总表1，又因为其他汇总表的访问率较低，所以只需要将视图的数据源绑定为汇总表3，因为汇总表3也可以满足视图的输出模式。这样不仅提升了汇总表3的数据访问率，而且降低了汇总表1的数据访问率，系统性能和服务性能得到了很大的优化。又因为货物约有500种，共10类， 可以再建立一个视图绑定数据源为汇总表4，这样就可以充分利用汇总表4的数据信息，从而可以进一步优化系统性能。

第二套 性能优化 索引

在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：
系(系号，系名)，系号为主码
学生(学号，姓名，所在系号)，学号为主码
课程(课程号，课程名，开课系号)，课程号为主码
选课(学号，课程号，选课时间)，学号和课程号为主码
开发人员在将关系模式实施到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学生0N学生.学号=选课.学号
JOIN系ON系.系号=学生.所在系号
JOIN课程0N课程.课程号=选课.课程号
WHERE系.系号=ˊ012 ˊ
AND convert(vvarchar(10)，选课时间，120)>=ˊ2010-01-01ˊ
(1)在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。(4分)
(2)设已在“选课”表的“选课时间”列及“学生”表的“所在系号”列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率?如果不能，请说明原因。(4分)

4、建表时没有设置主键，也没有说明外键，但不会影响此查询语句的执行效率。
[解析]本题中查询语句的功能是得到12系全体学生在2010年1月1日后的选课情况的汇总表。在每个数据表的定义时都必须严格定义表中的完整性约束条件，包括主键的设置，否则之后会出现主键有相同值的情况，破坏了数据的完整性。
5、”选课”表的”选课时间”可以建立索引，从而提高查询效率，而”学生w表的”所在系号w建立索引不会提高查询效率。索引的意义就是将记录按目标关键字顺序排列，这样查找某个目标关键字的对应值的位置就缩小了查找范围。”选课时间”的重复率低，所以可以作为索引，而学生”所在系号”的重复率太高，则不会提高查询效率。
[解析]”选课”表的”选课时间”列上建立了索引，从而能够提高执行效率。经常出现在where子句中的字段，特别是大表的字段，应该建立索引。索引的作用就类似于书的目录，即会按照章节的顺序排列。因此如果在一本数百页的书里面查找某个章节位置的时候，就可以只扫描书的目录。扫描的范围缩了n倍，查询的效率自然就会提高。另外，在sQL Server内存够用的情况下，索引会被放到内存中，在内存中查找自然又会提高效率，所以必须合理利用索引。

第三套 性能优化

某商场商品经营管理系统使用SQL Server 2008数据库管理系统，此系统上线运行1年后，业务人员使用某统计功能（此功能每月使用一次)时发现速度很慢。该统计功能主要执行的SQL语句如下:
SELECT商品号,SUM(销售数量销售价格)销售额FROM销售明细
GROUP BY商品号;
该销售明细表的建表语句如下:CREATE TABLE销售明细(
序列号intlIDENTITY(1,1) NOT NULL,商品号intNOT NULL,
销售日期datetime NULL,销售数量intNOT NULL,销售价格intNOT NULL);
并在销售明细表上建有如下索引:
CREATE index ix_销售明细_商品号on销售明细(商品号);某技术人品提出涌过执行下述语句以提高此杳询的运行效率:
CREATE VIEW商品销售额视图
WITH SCHEMABINDINGAS
SELECT商品号,SUM(销售数量销售价格)销售额,COUNT_BIG(*) cnt
FROM dbo.销售明细GROUP BY商品号;
CREATE UNIQUE CLUSTERED INDEX ix_商品销售额ON商品销售额视图(商品号);(10分)
(1）请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。
(2）此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。

语句功能:建立包含(商品号，销售额，该商品表中出现次数)带索引的视图，并建立按商品号对应销售额UNIQUE聚簇排序的索引，大大缩小」含询语可的食诩汇围，提高了查询效率。原因:税图中将间按相大周2小了推索空间，同时建商品号，销售数量，销售价格）转换成了目标属性列，减少了搜索空间;同时建立UNIQUE CLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。

不合适，每天大量的插入操作使得在修改表的同时也要对视图进行修改，增加了系统的负担，然而该统计功能一个月才用一次，这样导致系统的利用率也较为低下。

第四套 性能优化

某教务管理系统使用SQL Server 2008数据库管理系统，数据库软硬件配置信息如下：
Ⅰ．数据库运行在两路Intel Xeon E5-2609 2.4GHz CPU(每路CPU4核心)，128GB内存、2块300GB 15000转SAS硬盘(RAID 1)的服务器上；
Ⅱ．服务器操作系统为Windows 2003 32位企业版，安装SP2补丁；
Ⅲ．数据库为SQL Server 2008 Enterprise(32 位)，数据库中总数据量近130GB。
近一个学期以来，用户反映系统运行缓慢，经程序员定位，确定为数据库服务器响应缓慢，需要进行调优。

根据SQL Server 2008数据库的特性以及题目中的条件，综合给出以下的调优方案。
(1）表结构优化:重新优化数据库设计结构，规范数据库逻辑设计;设计主键和外键;设计合适大小的字段。
(2）硬件优化:购买一块同样大小的硬盘，将硬盘做成RAID5,用以提高数据库读写速度;增加服务器
CPU
个数;扩大服务器的内存。
(3）索引优化:采用对经常作为条件查询的列设计索引，在查W中经吊尺到的入操作的表不要建立过多的繁进行范围查询、排序、分组的列上建立.策族索引，对了有从系选i索引。
(4)采用视图:合理使用视图和分区视图，在需要更新和刎陈探作个夕、亘闲抹止十从乐PB长向拉一的再句兹(5)sQL语句优化:选择运算应尽可能先做，开仕X问一个衣应1多件想出数据较小的信息，再在这些信息在前面，较弱的选择条件写在后面，这样就可以无根伯权加充询，把子奋询转换成联结来实现。字段中根据后面较弱的条件得到满足条件的信息。应避免使用相关子查询，把子查询转换成联结来实现。字段
提取按照”需多少，提多少”的原则，避免”SELECT *”，”SELECT *”需要数据库返回相应表的所有列信息，这对于一个列较多的表无疑是一项费时的操作，采用存储过程，使用存储过程提高数据处理速度。

第五套 性能优化

某采购中心采购了一套商品批发查询管理系统，此系统采用SQL Server 2008数据库管理系统，该系统需要经常处理百万级以上的数据查询。同时该系统提供了第三方人员开发的SQL接口，第三方人员可以根据自己的需要开发自己的应用程序来访问数据库中的相关数据。
(1)系统在使用的过程中，业务人员反应系统操作速度很慢。经过工程师检查测试后，数据库系统本身及网络传输过程中存在着一些问题，请给出针对数据库系统本身及网络传输过程中可能的一些调优方案。
(2)工程师在完成数据库系统本身和网络问题的优化后，发现第三方开发人员的SQL查询语句存在很多没有优化的问题，请从第三方开发人员角度给出一些优化方案。

①把数据、日志、索引放到不同的I/0设备上，增加读取速度，数据量（尺寸)越大，提高I/0越重要。②纵向、横向分割表，减少表的尺寸。
③升级硬件，扩大服务器的内存 ,Windows 2000和SQL Server 2000能支持4-8G的内存。配置虚拟内存，虚拟内存大小应基于计算机上并发运行的服务进行配置。增加服务器CPU个数。
分布式分区视图可用于实现数据库服务器联合体。
联合体是一组分开管理的服务器，但它们相互协作
分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层Web 站点的处理需要。
⑤重建索引: DBCC REINDEX ,DBCC INDEXDEFRAG;收缩数据和日志:DBCC SHRINKDB,DBCC SHRINKFILE。设置自动收缩日志，对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。
优化锁结构。
2)
①对查询进行优化，尽量避免全表扫描，首先应考虑在
where 及 order by 涉及的列上建立索引。
②应尽量避免在 where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。③任何地方都不要使用select * from进行全表扫描
，用具体的字段列代替”*”，不要返回冗余字段。
④避免频繁创建和删除临时表，以减少系统表资源的消耗。
⑤尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。⑥尽量避免大事务操作，提高系统并发能力。

第六套 恢复 性能优化

某网上商城因为业务发展，原有的系统不能很好的满足需要，因此采用了一套新的经营管理系统，此系统使用SQL Server 2008数据库管理系统。此系统上线运行前，需将商场原有的数据导入到新系统中。原有系统使用SQL Server 2000，数据结构与新系统不完全一致。
因此需要把数据从SQL Server 2000导入到SQL Server 2008中，为了保证数据一致性，数据导人过程中要求暂停业务且必须在3小时内完成。
(1)在原有数据导入新系统的过程中，实施人员发现原有数据量很大，导人数据需要四小时，业务无法接受。经分析某工程师认为，数据导入过程中的数据库I／O很高，但导人数据的程序本身对系统资源占用率很低。该工程师建议将数据导人过程中的数据恢复模式从“完整”模式改为“简单”模式以提高数据导入速度；而另一位工程师则认为此方法未必能提高数据导人速度，而且还可能导致数据丢失，不建议使用此方法。
请分析此方法是否能够提高数据导入速度并给出理由，同时分析此操作的数据丢失风险。
(2)在成功导入历史数据后，此系统顺利上线运行。在上线运行的第一周，发现数据库服务器的CPU使用率很高，达到近90％，高峰期间达到100％，且系统内存占用率达到90％，但系统I／O很轻。业务人员反应系统操作速度很慢。为了提高系统运行速度。
在不修改应用程序的前提下，两位工程师提出了不同的解决办法：
Ⅰ 为服务器增加2颗CPU，缓解CPU使用率很高的问题；
Ⅱ 为服务器增加一倍内存，缓解内存使用率很高的问题。
考虑成本，现阶段只能按照一种方案实施。请指出在现有情况下，哪种方案更合理并给出理由。

(1）此方法能够提高数据导入速度。
原因:此系统I/o很高，修改恢复模式后，系统最大限度减少日志开销，可提高导入速度。由于仅在数据导入过程中修改恢复模式，所以并无数据丢失风险。
(2）第一种方案比较合理。原因:SQL Server 2008采用将数据缓冲在内存的方式，因此内存的使用率比较高是正常情况，且现阶段Ⅰ/O并不存在问题，表明内存满足需求。此阶段CPU使用率很高，表明CPU计算资源不足，因此增加CPU数量对解决问题有效。

第八套 性能优化

设某超市经营管理系统使用SQL Server 2008数据库管理系统，此数据库服务器有2颗CPU、16GB内存、2TB磁盘。上线运行1年后，用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。经技术人员分析，发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到60GB。已知这两个表结构如下：
销售单据表(销售单据编号，销售时间，销售人员编号，收银台编号)
销售单据明细表(销售单据编号，商品编号，单价，数量)
在进行此报表计算时数据库服务器CPU消耗非常高。为了优化此操作，某工程师建议在销售单据表中增加”付款总金额”属性，取值由触发器自动计算。请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方案优劣。请判断此方案是否可行，并从时空代价和优化效果方面分析原因。

此方案不可行。
①触发器可以通过数据库中的相关表进行层叠更改，这比直接把代码写在前
台的做法更安全合理，保证了数据的一致性，但同时增加了磁盘空间的消耗。在超市经营中要批量操作、多次触发的情况下，触发器的效率低，因为
它相当于每次都执行一段SQL语句，使CPU的消耗更高。因而从时空代价角度来说并不能达到优化的效果。
②在数据单据表中增加”付款总金额”不是正确的优化方法，应该在数据单据明细表中增加”付款总金额”，这样才能提高查询效率
【解题思路】
在数据库设计阶段，主要强调的是高效率利用存储空间，减少数据的冗余，减少数据的不一致性，这个过程也就是规范化的过程。但是在数据库运行阶段要考虑到高效率的进行数据处理。
完全规划化的数据库会产生很多表，对于一个频繁使用的查询，如果它要求操作多个相关表中的数据，则每次为生成需要的查询结果而在连接多个表中相关行时，数据库管理系统就会消耗更多的计算资源，因为连接操作非常耗时。
而反规范化是将规范化的关系转换为非规范化的关系的过程，目的是提高查询的效率。常见的方法有增加派生冗余列，增加冗余列，重新组表，分割表和新增汇总表等方法。
该题解决的方式就是增加派生冗余列–”付款总金额”。派生性冗余列是指表中增加的列由表中的一些数据项经过计算而成，它的作用是查询时减少连接操作，避免使用聚合函数。例如销售单据明细表（销售单据编号，商品编号，单价，数量)中增加”付款总金额”，因为付款总金额=单价*数量得到，说明”付款总金额”是派生性冗余列。如果不要该字段，那么每次使用总价时，都要先执行代码计算后才能使用，如果商品数量较多，而且要频繁使用”付款总金额”时，计算”付款总金额”时执行的次数也会随着增加，这显然会影响数据库的执行效率。若增加”付款总金额”这个派生性冗余字段，虽然破坏了规范化原则，但只要执行一次计算”付款总金额”就可以把商品金额存在数据库中，以后不管什么时候使用”付款总金额”字段，只需要提取其值就可以了，不必在执行代码了。因此增加”付款总金额”，虽然提高了磁盘空间的使用，但是可以提高系统执行的效率，达到以空间换时间的目的。

第十一套 性能优化

3.设某超市经营管理系统(C/S结构)使用SQL Server 2008数据库管理系统。系统上线运行1年后，用户反映系统运行速度不稳定，不定期会出现速度变慢的问题。
数据库管理员在数据库服务器上采集了一段时间系统运行状态数据，情况如下：
CPU最高使用率：30%
内存最高使用率：80%
磁盘IO很低
同时，数据库管理员在客户端使用ping命令对数据库服务器进行测试，结果如下
192.168.1.100 的Ping统计信息：
数据包：已发送=100，已接收=90，丢失=10
往返行程的估计时间（以毫秒为单位）：
最短=10ms，最长=502ms，平均=100ms
(5分)请根据以上数据，判断最有可能的问题，并给出判断的理由。
在以上问题解决后，用户反映每日的定时统计汇总SQL语句仍然速度比较慢。数据库管理员在系统无任何人员使用时运行该语句，同时在数据库服务器上采集系统运行状态数据，情况如下：
CPU最高使用率：12.5%(CPU配置：2路4核，共8核，关闭超线程)
内存最高使用率：80%(内存配置：128GB
磁盘IO很低(磁盘配置：5块300GB磁盘，RAID5)
(5分)请分析在进行统计汇总时，数据库服务器最有可能的瓶颈是CPU、内存还是磁盘，并给出判断的理由。

【参考答案】
(1）产生掉线的最有可能的原因是网络产生问题。因为通过ping命令测试，发现丢包率达到10%。而CPU，内存、磁盘I/O使用率没有达到100%，说明还是有—定的富余，引起网络掉线的可能性较小。
(2）数据库服务器最有可能的瓶颈是内存。因为内存的使用率在无任何操作的情况下达到80%，使用率较高。说明内存容量不足以完全支持服务器活动。
对于一台数据库服务器来说，如果其在业务空闲时使用率超过90%，说明服务器缺乏CPU资源;如果高峰时CPU使用率仍然很低，则说明服务器CPU资源充足。据此，可以判断一个数据库系统CPU的使用情况。本题中CPU的最高使用率只有12.5%，说明CPU不可能是数据库服务器的瓶颈。磁盘/O很低，说明也不可能是服务器的瓶颈。
【解题思路】
数据库系统运行需要依赖服务器、操作系统、存储、网络等多种因素。当这些数据库环境出现瓶颈时就会导致数据库运行变慢，甚至是无法完成正常业务。
一般情况下，数据库访问代价模型由四部分组成:CPU处理数据的时间(即CPU代价)，磁盘读取的时间(即I/O代价)，查询的内存开销(即内存代价)，对于网络中的数据库还要加上数据在网络上传输的时间(即通信代价)。因此数据库访问的代价模型是:总代价=CPU代价+l/O代价+内存代价+通信的代价。
(1) CPU。当数据库操作对CPU性能的要求超过数据库服务器的CPU性能时，数据库性能就会受到CPU的限制。对于一台数据库服务器来说，如果其在业务空闲时使用率超过90%，说明服务器缺乏CPU资源;如果高峰时CPU使用率仍然很低，则说明服务器CPU资源充足。据此，可以判断一个数据库系统CPU的使用情况。解决的方法是为服务器适当增加CPU的数量或者终止需要许多资源的进程。
(2）数据库性能优劣的一个重要度量是响应时间，而I/O时间是响应时间的最大组成部分。因此，可以通过令I/O时间最小化，减少磁盘上的文件竞争带来的瓶颈等方法来改善数据库系统的性能。
(3)通过调整相关参数控制数据库的内存分配，也可以在很大程度上改善数据库系统的性能。
(4)大量的SQL数据在网络上传输会导致网速变慢。网卡、交换机、集线器等网络设备的性能对网络的影响很明显，所以，通过调整网络设备，也可以在—定程度上提高数据库系统的性能。

第十三套 性能优化

设某学校选课系统使用SQL Server 2008数据库管理系统，在学生选课期间，用户反映速度缓慢，大部分时间内，学生无法正常选课（选课学生近1万人）。经技术人员分析，发现速度缓慢的原因为数据库服务器响应缓慢。在选课期间，数据库服务器CPU使用率为100%，内存使用率95%，磁盘IO占用10%，网络带宽占用20%。在进—步分析后发现，学生在选课过程中需要在每次选定课程前校验相关课程的成绩信息，而校验成绩的SQL语句在业务不繁忙时仍需要1-2分钟才能执行完成。现有服务器硬件情况如下:CPU:两颗英特尔至强处理器E5-2640 2.50GHz内存:128GB，主频1600MHz 磁盘:6块600GB 15000转6Gbps SAS硬盘，RAID5网卡:千兆网卡
为了保证系统稳定运行，现有如下优化方案:(1)将CPU更换为两颗英特尔至强处理器E5-2690 2.90GHz。(2）将内存提高到256GB，主频1600MHz。(3）将磁盘更换为6块800GB读取密集型MLC 6Gpbs硬盘，保持RAID5。(4）将网卡更换为万兆网卡。
(5)优化校验成绩SQL语句并建立相关索引，校验完成时间降低到1秒。根据题目描述，请从是否能解决选课运行速度缓慢问题的角度逐个评价以上5个方案并给出原因。

【参考答案】
(1)）提高CPU的主频，可以稍微缓解选课速度慢的问题。因为CPU主频提高，单位时间内处理的数据多一些，但不能根本解决服务器响应慢的问题。本题中的CPU在选课期间使用率达到100%，增加CPU的频率不能从根本解决问题。大量的SQL数据在网络上传输可能会导致网速变慢。
(2)内存提高，可以改善选课速度慢的问题，内存的增加可以从一定程度上提高数据处理的效率。本题中，选课期间内存使用率达到95%，说明内存没有完全使用，因此内存容量不是产生服务器响应慢的原因。
(3) SAS的特点是成本高、容量小、速度快，而MLC的特点是容量大、成本低,但是速度慢。因此替换原有的SAS硬盘不能提高数据存取的速度，不能解决选课运行速度慢的问题。
(4）更换网卡不能解决选课运行速度慢的问题，因为本题中网络带宽的使用率占用20%，说明网络问题不是选课慢的主要原因。
(5）可以从根本上解决选课慢的问题。原有校验成绩的SQL语句需要1-2分钟才能执行完成，当大量的学生选课时，CPU和内存主要用于处理SQL语句，大量的资源被消耗，引起服务器反应减慢。本题中，SQL语句的效率低下，是产生服务器响应慢的根本原因，应当对SQL语句优化。一般来说，效率低下的SQL语句常常是系统效率不佳的主要原因。
【解题思路】
数据库系统运行需要依赖服务器、操作系统、存储、网络等因素。当这些数据库环境出现瓶颈时会导致数据库运行缓慢，甚至无法完成业务。一般来说，可以从外部环境、调整内存分布、调整磁盘/O、调整资源竞争等几方面来改变数据库的参数，提高其性能。
(1)外部调整包括CPU调整和网络调整。当数据库操作对CPU性能的要求超过数据库服务器的CPU性能时，数据库性能就受到CPU的限制，使数据库操作变慢。
(2)通过调整相关参数控制数据库的内存分配，也可以在很大程度上改善数据库系统的性能。
(3）调整磁盘/O。数据库性能优劣的一个重要度量是响应时间，而I/O时间是响应时间的最大组成部分。因此，可以通过令I/O时间最小化，减少磁盘上的文件竞争带来的瓶颈等方法来改善数据库系统的性能。
(4)调整竞争。多个进程对相同的资源发出申请时，导致了竞争的发生，从而引起服务器的反应缓慢。
(5）效率低下的SQL语句常常是系统效率不佳的主要原因。因此采用高效率的SQL语句，可以显著减少服务器的反应时间，提高服务器处理的速度。
本题中，SQL语句的效率低下是产生服务器响应慢的根本原因，提高SQL语句的执行效率，可以有效提高服务器的响应速度。

第十四套 索引 性能优化

在数据库中存在销售单据明细表，其建表语句为:
CREATE TABLE销售单据明细表 (
销售单据编号 varchar(20),
商品编号 varchar(8),
单价 money,
数量 int,
总价 money
);
为了保持数据一致性，此表中存在如下触发器：
CREATE TRIGGER AutoCountSum
ON 销售单据明细表 FOR INSERT,UPDATE
AS
DECLARE @UnitPrice money,@Quantity int,@Amount money
SELECT @UnitPrice = 单价,@Quantity = 数量 FROM inserted
SET @Amount=@UnitPrice * @Quantity
UPDATE 销售单据明细表 SET 总价=@Amount
WHERE 销售单据编号=(SELECT 销售单据编号 FROM inserted)
AND 商品编号=(SELECT 商品编号 FROM inserted)
在系统运行一段时间后，此表中有近千万条数据，此时发现对此表以“销售单据编号”和“商品编号”为条件，对其他字段进行修改时操作速度很慢。请分析原因，并在不改变硬件和SQL语句的前提下，给出优化方法。
在解决了①中的问题系统平稳运行3年后，用户反映系统速度很慢，管理员考虑更换硬件。经过一段时间的监控，系统资源使用情况如下:CPU使用率一般不超过70%内存使用率95%磁盘IO使用率高峰期90%-95%，主要为读操作请根据以上数据，说明在此次更换硬件过程中，应该着重提高哪方面硬件性能，并给出原因。

(1）对字段修改时操作速度很慢的原因是大量数据的查询速度慢，从而引起修改慢。优化的方法是在“销售单据编号”和“商品编号”上建立索引。(2）更换硬件时应该提高内存容量，磁盘在经济条件许可的条件下才有RAID10磁盘阵列。

(1）索引技术是一种快速数据访问技术，建立索引的目的就是为了提高查询的效率。查询的性能在很大程度上取决于存在什么样的索引来加快选择和联接的处理。对于大星数据，只有快速的定位、减少查询的时间就会增加修改的速度。(2)对于CPU、内存、磁盘等硬件设备在空闲时使用率超过90%，说明硬件资源匮乏。如果在高峰时使用率小于90%说明硬件资源充足。本题中，内存和磁盘的使用率都高于90%，而CPU的使用率不高于70%，说明内存、硬盘的资源匮乏，需要调整，而CPU的资源充足不需要调整。因此在硬件调整时，应提高内存的容量和硬盘的访问效率。使用RAID时，可以将基本表和建立在表上的索引分别放在不同的磁盘上，这样访问基本表时，存放数据和存放索引的磁盘驱动器并行工作，可以得到较快的文件读写速度;类似的，日志文件与数据对象（表、索引等)也可分别存放在不同磁盘上以改善系统的I/O性能。在RAID中，RAID10是一个RAIDO与RAID1的组合体，它继承了RAIDO的快速和RAID1的安全，同时读写速度均提高。

第十五套 分区 性能优化

某学校网络计费系统使用SQL Server 2008作为数据库管理系统，系统中需保存每个用户的每次登录和退出时间，该表的结构如下：
CREATE TABLE 登录日志表(
序列号 int identity(1,1),
帐号 varchar(8),
登录时间 datetime,
退出时间 datetime,
本次上网费用 real
);
在系统运行一段时间后，此表中有近5千万条数据。此时，用户按照登录时间查询自己近期上网情况时速度缓慢。为了提高用户体验，某工程师提出建议对此表进行分区，请分析其建议合理性（若合理请给出最优的分区方案），并说明原因。
在解决了①中的问题系统平稳运行3年后，管理员考虑升级硬件。经过监控，系统资源使用情况如下：
•CPU使用率一般不超过50%
•内存使用率90%
•磁盘IO使用率高峰期90%-95%，主要为读操作
某工程师给出两种升级后的硬件配置方案：
•配置一：2颗处理器，6核心，主频2.4GHz；64GB内存；300GB 10K转SAS硬盘，3块，RAID0
•配置二：2颗处理器，6核心，主频1.9GHz；128GB内存；800GB 固态硬盘 SAS 读取密集型 MLC，2块，RAID1
请根据以上数据，在不考虑费用因素的前提下，说明哪种配置更合理，并给出原因。

【参考答案】
(1）因为操作仅限于查询，建立分区表可以有效的提高查询效率。对登录日志表中在登录时间列上以月为单位创建右侧分区函数，将登陆日志表分成12个分区，每个分区对应一年中一个月的值。
(2）采用方案2。对于内存、磁盘等硬件设备在空闲时使用率超过90%，说明硬件资源匮乏。如果在高峰时使用率小于90%说明硬件资源充足。本题中，内存和磁盘的使用率都高于90%，而CPU的使用率不高于50%，说明内存、硬盘的资源匮乏，需要调整，而CPU的资源充足不需要调整。因此在硬件调整时，应提高内存的容量和硬盘的访问效率。RAID1相对于RAIDO来说提高了读速度，加强了系统的可靠性，但是写效率没有提高。
【解题思路】
本题考查的是分区表的相关知识和数据库运行环境和参数调整知识。
(1）分区表是将表中的数据按照水平方式分成不同的子集，这些数据子集存储在数据库的一个或多个文件组中。合理使用分区会在很大程度上提高数据库的性能。是否创建分区表主要取决于表当前的数据星大小以及将来数据星大小，同时还取决于对表中数据进行的操作特点。通常，如果某个大型表同时满足下列条件，则比较适合于进行分区∶①该表包含(或将包含)以多种不同形式使用的大星数据，②数据是分段的，比如数据以年份分隔。然而，数据量大并不是创建分区表的唯一条件，如果表中大星的数据都是经常使用的数据，而且他们的操作方式基本是一样的，则最好不要使用分区表。而对数据的操作只涉及一部分数据而不是所有数据时，可以考虑建立分区表。
(2)对于CPU、内存、磁盘等硬件设备在空闲时使用率超过90%，说明硬件资源匮乏。如果在高峰时使用率小于90%说明硬件资源充足。
对于分区表来说，如果对某表中当前分段数据，经常进行的操作是添加、修改、删除、查询，而对于其他的数据，则几乎不操作，或者操作仅限于查询，那么就可以对表进行分区。
使用RAID时，可以将基本表和建立在表上的索引分别放在不同的磁盘上，这样访问基本表时，存放数据和存放索引的磁盘驱动器并行工作，可以得到较快的文件读写速度;类似的，日志文件与数据对象（表、索引等)也可分别存放在不同磁盘上以改善系统的I/O性能。RAID1相对于RAIDO来说提高了读速度，加强了系统的可靠性，但是写效率没有提高。在RAID中，如果不考虑价格因素，其实RAID10最适合的，它是一个RAIDO与RAID1的组合体，它继承了RAIDO的快速和RAID1的安全，同时读写速度均提高。但题目中主要考查的读能力，因此RAID1RAIDO更适合些。

第十七套 索引 性能优化

设在SQL Server 2008某数据库中存在销售单据明细表，其建表语句为：
CREATE TABLE 销售单据明细表(销售单据编号 varchar(10),商品编号 varchar(8),是否有效 varchar(4),单价 money,数量 int,总价 money)
在系统运行一段时间后，此表中有近千万条数据。该系统中存在如下操作：
select * from 销售单据明细表
where 销售单据编号 = ‘135671445’ and 商品编号 = ‘021’ and 是否有效 = ‘是’;
在系统运行中发现此操作的效率比较低，系统工程师提出如下建议：
由于查询中使用了“销售单据编号”、“商品编号”和“是否有效”三个条件，因此可以分别在这三列上建立索引，提高查询效率。
系统工程师建议中建立的三个索引是否能够提高查询效率？请简要说明原因。
随着系统运行时间增长，系统运行速度逐渐变慢，经分析为磁盘IO不能满足数据库系统频繁读写的要求。工程师建议增加一块相同磁盘，将两块磁盘调整为RAID1模式。请从读写两方面分析，此操作是否能够改善磁盘IO问题。

【解题,思路】
本题考查的是索引相关知识，如索引定义，在什么地方建立索引，建立索引的条件以及RAID1磁盘阵列的特点。
(1）索引的知识
索引技术是一种快速数据访问技术，它将一个文件的每个记录在某个或某些领域（或称为属性)上的取值与该记录的物理地址直接联系起来，提供了一种根据记录域的取值快速访问文件记录的机制。索引的使用要恰到好处，其使用原则一般如下:
1经常在查询中作为条件被使用的列，应为其建立索引;
②频繁进行排序或分组（即进行group by或order by操作）的列，应为其建立索弓3一个列的值域很大时，应为其建立索引;
④如果待排序的列由多个，应在这些列上建立复合索引。(2) RAID1磁盘阵列的特点
RAID1磁盘阵列级，是一种镜像磁盘阵列，其原理就是将一块硬盘的数据以相同位置指向另一块硬盘的位置。RAID1又称为Mirror或Miroring，它的宗旨是最大限度地保证用户数据的可用性和可修复性。RAID1的操作方式是把用户写入硬盘的数据百分之百地自动复制到另外一个硬盘上。由于对存储的数据进行百分之百的备份，在所有RAID级别中，RAID1提供最高的数据安全保障。同样，由于数据的百分之百备份，备份数据占了总存储空间的一半，因而，Mirror的磁盘空间利用率低，存储成本高。Mirror虽不能提高存储性能，但由于其具有的高数据安全性，使其尤其适用于存放重要数据，如服务器和数据库存储等领域。
RAID1提高了读速度，加强了系统可靠性。但其磁盘的利用率低，冗余度为50%，同时写速度并未提高。RAID1经常要求保证用户数据的可用性和可修复性场所，体现系统的可靠性，而不是读写速度。
RAID1是将一个两块硬盘所构成RAID磁盘阵列，其容量仅等于一块硬盘的容量
因为另一块只是当作数
据”镜像””。RAID1磁盘阵列显然是最可靠的一种阵列，因为它总是保持一份完整的数据备份。它的性能自然没有RAIDO磁盘阵列那样好，但其数据读取确实较单一硬盘来的快，因为数据会从两块硬盘中较快的一块中读出。RAID1磁盘阵列的写入速度通常较慢，因为数据得分别写入两块硬盘中并做比较。RAID1磁盘阵列一般支持”热交换”，就是说阵列中硬盘的移除或替换可以在系统运行时进行，无须中断退出系统。RAID1磁盘阵列是十分安全的，不过也是较贵一种RAID磁盘阵列解决方案，因为两块硬盘仅能提供一块硬盘的容量。RAID1磁盘阵列主要用在数据安全性很高，而且要求能够快速恢复被破坏的数据的场合。
【参考答案】
(1“销售单据编号”、“商品编号和“是否有效”都是查询的条件，因此在这三列上建立索引，可以提高查询效率。
原因:经常在查询中作为条件被使用的列，应为其建立索引，可提高查询效率。
(2):RAID1通常被称为Mirror，RAID中的硬盘分成相同而两组，互为镜像，当其中一块磁盘出现故障时，可以利用其镜像上的数据恢复，从而提高系统的容错能力。RAID1对数据的操作仍采用分块后并行传输方式。RAID1提高了读速度，加强了系统的可靠性。但其缺点是硬盘的利用率低，冗余度为50%，同时写速度并未提高。根据题意，磁盘IO不能满足数据库系统频繁读写的要求，如果增加一块相同磁盘，并调整为RAID1模式，能提高读的速度，但是写的速度并未提高，所以不能改善磁盘IO的问题。
或者
从读的方面来看：当原始数据繁忙时，可直接从镜像拷贝中读取数据，因此RAID1可以提高读取性能，也提高了磁盘的存储空间。增加一个RAID1可以提高磁盘的存储空间，在一定程度上提高了读速度，加强了系统的可靠性。但是增加RAID1，系统变成四块硬盘，同时也就增加了系统的I/O开销，当数据量大时，并不能很好地提高系统的读的速度。
从写的方面来看：RAID1磁盘阵列的写入速度通常较慢，因为数据得分别写入两块硬盘中并做比较。现在增加了一块RAID1磁盘阵列，相当于有四块硬盘，在写入数据时，可能会做两个备份，写入的速度更慢。
因此从读写两个方面来说，增加一块RAID1磁盘阵列不是解决问题的好的方式，这种方法不建议使用。

第十八套 性能优化 索引·复合

在SQL Server 2008的数据库中存在如下两张关系表：
学生表(学号，姓名，性别，身份证号，系所号)，学号为主码
系所表(系所号，系所名，系所简称)，系所号为主码
在数据库中执行如下T-SQL代码：
DECLARE @DeptID varchar(10)
DECLARE @cnt int
SET @cnt= 0
DECLARE cursor1 cursor FOR SELECT 系所号 FROM 系所表
WHERE 系所名 LIKE ‘%电%’
OPEN cursor1
FETCH NEXT from cursor1 into @DeptID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @temp_cnt int
SELECT @temp_cnt = COUNT() FROM 学生表 WHERE 系所号 = @DeptID
SET @cnt = @cnt + @temp_cnt
FETCH NEXT from cursor1 into @DeptID
END
CLOSE cursor1
DEALLOCATE cursor1
SELECT @cnt AS 学生总数
执行过程中发现速度比较缓慢，为了解决性能问题，需在功能不变的情况下，将此T-SQL代码改为一条SQL语句：
SELECT COUNT() FROM 学生表 JOIN 系所表 ON 学生表.系所号=系所表.系所号
WHERE 系所名 LIKE ‘%电%’
请分析此改变是否可以提高性能，并给出原因。
学生表的身份证号和姓名上建有一个复合索引，索引建立语句为
CREATE INDEX idx_身份证号_姓名 ON 学生表 (身份证号,姓名)
请指出如下两个查询语句能否使用此索引并说明原因。
SELECT * FROM 学生表 WHERE 身份证号 = ‘123456789012345678’;
SELECT * FROM 学生表 WHERE 姓名 = ‘小明’;

【解题思路】
本题考查的是T-SQL与SQL语句使用的区别，以及索引相关知识，如索引定
入
义，在什么地方建立索引，建立索引的条件等等。
(1)T-SQL即Transact-SQL，是SQL在Microsoft SQL Server 上的增强版，它是用来让应用程序与SQL Server沟通的主要语言。T-SQL提供标准SQL的DDL和DML功能，加上延伸的函数、系统预存程序以及程式设计结构（例如IF和WHILE)让程式设计更有弹性。
SQL是集DDL、DML、DCL为—体的标准关系数据库语言。其中DDL数据定义语言用于定义和管理SQL数据库中的所有对象的语言;DML数据操纵语言，SQL中处理数据等操作统称为数据操纵语言。
SQL作为结构化查询语言，是标准的关系型数据库通用的标准语言;T-SQL是在SQL基础上扩展的SQL Server中使用的语言。
(2)索引的知识
索引技术是—种快速数据访问技术，它将一个文件的每个记录在某个或某些领域(或称为属性)上的取值与该记录的物理地址直接联系起来，提供了一种根据记泉域的取值快速访问文件记录的机制。索引的使用要恰到好处，其使用原则—般如下:
①经常在查询中作为条件被使用的列，应为其建立索引;
②频繁进行排序或分组(即进行group by或order by操作)的列，应为其建立索引;
③一个列的值域很大时，应为其建立索引;
④如果待排序的列由多个，应在这些列上建立复合索引。【参考答案】
(1) SELECT COUNT(*)FROM学生表JOIN系所表ON学生表.系所号=系所表.系所号WHERE系所名LIKE’%电%’性能更好。
原因:T-SQL的代码需要先扫描—遍系所表，然后根据扫描得到的每个记录重新扫描—遍学生表得出结果。使用所写的SQL语句实现同样的功能，将学生表和系所表连接之后按条件过滤，只需要扫描一遍连接之后的表即可。(2）创建的复合索引对第二条语句的查询效率提高不明显，因为其是复合索引且身份证号排在前面，对查找姓名的查询效率提高不明显，而明显会提高第—条语句的查询效率。
或者
：[1]:能够提高性能。
原本的代码中使用了游标。游标是一种临时的数据库对象，常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生的，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。
但是，题中检索的是系所名中含有”电”字的，事实上所有学生被检索的概率理论上都是相同的，此处不符合”创建一次，使用多次”的前提条件。
另外，游标使用时会对行加锁，可能会影响其他业务的正常进行。同时数据量大时，游标会导致效率低下。
还有，游标其实是相当于把磁盘数据整体放入了内存中，可能会带来巨量的内存占用的情况。
综上所述，一般情况下尽量不要使用游标，因为会降低效率影响性能。除非遇到特殊情况。游标在使用完毕后应及时释放销毁，以释放资源。
[2]:第一条select语句可以使用此索引，第二条select语句无法使用此索引。
基于多个列来创建的索引是复合索引。以下列语句为例：
CREATE INDEX idx_test ON 学生表 (身份证号,姓名,性别)
此索引将优先对”身份证号”排序（第一顺序），若存在身份证号相同的多名学生，则对这些学生的”姓名”排序（第二顺序），若姓名也存在相同的，则按照”性别”排序（第三顺序）。

在使用上述示例的复合索引时，存在下列限制：

复合abc索引中，可以查询只可以a，ab，abc
所以，题中第一条select语句是单独查询第一顺序的身份证号，可行；题中第二条select语句是单独查询第二顺序的姓名，无法使用该复合索引

第二十套 并、交、差运算 性能优化

某商品经营管理系统使用SQL Server数据库。在数据库中存在销售单据表，建表语句为：
CREATE TABLE 销售单据表(
销售单据编号 char(20),
付款总金额 money,
销售日期 datetime,
销售人员编号 varchar(8),
收银台编号 varchar(6)
);
由于该表数据量巨大，因此建立了销售单据历史表，将1年以前的数据从销售单据表转入销售单据历史表。销售单据历史表的建表语句如下：
CREATE TABLE 销售单据历史表(
销售单据编号 char(20),
付款总金额 money,
销售日期 datetime,
销售人员编号 varchar(8),
收银台编号 varchar(6)
);
其中销售单据编号为主键，在收银台编号和销售人员编号上有索引。
[1]在系统运行一段时间后，发现如下语句执行速度缓慢：
SELECT 销售单据编号,付款总金额,销售日期 FROM 销售单据表 WHERE 收银台编号= ‘001000’
union
SELECT 销售单据编号,付款总金额,销售日期 FROM 销售单据历史表 WHERE 收银台编号= ‘001000’
请给出该语句执行速度缓慢的原因，并给出SQL语句的优化方案。
[2]在系统运行过程中，磁盘I/O很重，数据库数据量近200G且磁盘写入性能要求非常高。考虑对硬件进行升级，3位工程师给出了不同配置方案：
•方案1：CPU保持不变，内存16G，2块300GB SAS硬盘，使用硬件RAID卡做RAID1
•方案2：CPU保持不变，内存8G，4块300GB SAS硬盘，使用硬件RAID卡做RAID0
•方案3：CPU保持不变，内存16G，3块146GB SAS硬盘，使用硬件RAID卡做RAID5
在不考虑成本的情况下，请指出哪种方案更适合此应用并给出原因。

本题考查的是sQL语言支持对查询结果在进行并、交、差运算以及不同磁盘阵列RAID的特点。
(1）并运算可将两个或多个查询语句的结果集合并为一个结果集，这个运算可以使用UNTON运算符直符实现。UNION是一个特殊的运算符，通过它可以实现让两个或更多的查询产生单一的结果集。
使用UNION谓词的语法格式为:
SELECT语句1
UNION [ALL ]SELECT语句2UNION [ALL]…
SELECT语句n
其中:ALL表示在结果集中包含所有查询语句产生的全部记录，包括重复的记录。如果没有指定ALL，则系统默认是删除合并后结果集中的重复记录。UNION和UNION ALL的区别是:UNION会自动压缩多个结果集合中的重复结果，而UNION ALL则将所有的结果全部显示出来。
(2）磁盘阵列RAID所采用的方法不同，可以将其分为很多级别，常见的RAID级别有RAIDO、RAID1、RAID5等。下面分别对不同级别RAID的特点进行介绍。
①RAID O向RAID O写入数据时，RAID将数据分成许多块，然后并行地将它们写到RAID中各个硬盘上﹔读出数据时，RAID从各个硬盘上读取数据，把这些数据恢复为原来顺序后传给主机。RAID O的优点是采用数据分块、并行传送方式，能够提高读写速度。但RAID中存储空间没有冗余，对系统的可靠性没有任何提高，任一个硬盘介质出现故障时，数据将无法恢复。
②RAID 1:RAID 1通常也被称为Mimor，RAID中的硬盘分为相同的两组，互为镜像，当其中一块磁盘出现故障时，可以利用其镜像上的数据恢复，从而提高系统的容错能力。RAID1对数据的操作仍然采用分块后并行传输方式。RAID 1提高了读速度，加强了系统的可靠性。但其缺点是硬盘利用率低，冗余度为50%，同时写速度并未提高。
③RAID 5、RAID 5可以为系统提供数据安全保障，但保障程序要比RAID 1低而磁盘空间利用率要比RAID 1高。RAID) 5具有和RAID O相近似的数据读取速度，只是多了一个奇偶校验信息，写入数据的速度比对单个磁盘进行写入操作稍慢。同时由于多个数据对应一个奇偶校验信息.，RAID 5的磁盘空间利用率要比RAID 1高，存储成本相对较低。
【参考答案】
(1)在执行完Union语句后，需要对表中数据逐个查询，该表数据量较大，所以执行速度缓慢。sQL语句的优化方案:
SELECT销售单据编号,付款总金额,销售日期FROM销售单据表WHERE收银台编号=’001000’Union All
SELECT销售单据编号,付款总金额,销售日期FROM销售单据历史表WHERE收银台编号=’001000
这个执行效率较高。在执行完Umion语句后，执行alti语句，根据由销售日期和销售人员编号建立的索引，直接查询，故效率较高。
(2)RAID O并行读写于多个磁盘上，具有很高的数据传输率，但它只是单纯地提高性能，并没有为数据的可靠性提供保障。RAD 1高数据可靠性，通过磁盘数据镜像实现数据冗余，在成对的独立磁盘上产生互为备份的数据。当原始数据繁忙时，可直接从镜像拷贝中读取数据，因此RAID 1可以提高读取性能，但是CPU占用率同样也很高，而磁盘的利用率比较低。RAID 5是一种存储性能、数据安全和存储成本兼顾的存储解决方案。故方案3更合适。
或者
[1]union有一个distanct的动作，需要进行并运算的数据量越大，其操作执行越慢。只需把union改为union all，就会取消这个distanct操作，速度将会大幅增加。

[2]由于题目已提及I/O很高，方案1的RAID1方案对于I/O没有帮助，所以否决。
方案2的RAID0方案虽然对磁盘I/O的提升最大，但是由于RAID0对于数据的可靠性没有任何帮助，暂时候选。
方案3采用RAID5方案。RAID5的读取性能与RAID0接近，但写入速度比单个磁盘稍慢，而数据的可靠性高于RAID0。
结合题目提及对于磁盘写入性能要求非常高，所以只能选方案2，因为方案1对读写性能没有提升，而方案3本题中十分看重的降低了写入性能。

第二十一套 索引 性能优化

在SQL Server某数据库中存在销售单据明细表，其建表语句为：
CREATE TABLE 销售单据明细表（销售单据编号 varchar(20),商品编号 varchar(8),单价 money,数量 int);
系统运行一段时间后，此表中有近千万条数据，程序员在数据库中执行如下SQL语句：
SELECT 商品编号,sum(单价*数量) FROM 销售单据明细表 WHERE 单价>150 GROUP BY 商品编号
1.为提高此查询的执行效率，在不改变SQL语句且不进行硬件调整的情况下，请给出提高查询执行效率的方案。
2.在运行过程中，发现此系统数据库CPU使用率很高，达到近95%，高峰期间达到100%，且系统内存使用率达到90%，但系统I/O很轻。业务人员反映系统操作速度慢。为了提高系统运行速度，在不修改应用程序的前提下，两位工程师提出了两种不同的解决方案：
方案一：为服务器增加1颗CPU
方案二：为服务器增加一倍内存
考虑成本因素，现阶段只能选择一种方案实施。请指出在现有情况下，哪种方案更合理并给出原因。

【参考答案】
(1）在不改变SQL语句且不进行硬件调整的情况下，可以为销售单据明细表“商品编号”建立一个索引来提高查询的效率。
(2)方案一合理。提高数据库性能的方法，一般是从外部环境、调整内存分配、调整磁盘IO、调整竞争资源等几方面着手来改变数据库的参数。SQL Server采用将数据缓冲在内存的方式，因此在数据库系统运行的过程中会占用一定的内存，又因为IO并不存在问题, 说明内存上满足需求。CPU使用率很高，说明CPU计算能力不足，应增加CPU的数量。
或者
1、由于运行一段时间后，就有了近千万条数据，说明数据的写入量很大，这种情况下不能使用索引，因为写入量大，所以索引的维护开销将非常巨大，不仅无法提高效率，反而会降低性能，所以不适合查询优化。
由于题目不允许改变SQL语句，所以无法使用反规范化或使用临时表的方式，以空间换取时间。题目同样不允许硬件调整，不允许进行硬件升级。
本题最适合的方案是数据表的水平分割，由于商品编号有很多，所以应该按照单价区间进行水平分割
2、根据题目表述，两种方案都能够提高系统运行速度。但是，即便是平峰时CPU的负载也很高，高峰时甚至达到100%，而内存使用率低于CPU使用率，迫切程度低于CPU的需求。所以在成本有限的前提下，应该选择方案1，增加一颗CPU以缓解CPU的性能压力。选择此方案所得到的效果将高于方案2。

第二十二套 索引 触发器

某商品经营管理系统使用SQL Server数据库管理系统。在数据库中存在销售单据明细表，其建表语句为:
CREATE TABLE销售单据明细表 (
销售单据编号 varchar(20),
商品编号 varchar(8),
单价 money,
数量 int,
总价 money
);
为了保持数据一致性，此表中存在如下触发器：
CREATE TRIGGER AutoCountSum ON 销售单据明细表 FOR INSERT,UPDATE
AS
DECLARE @UnitPrice money,@Quantity int
SELECT @UnitPrice = 单价,@Quantity = 数量 FROM inserted
UPDATE 销售单据明细表 SET 总价=@UnitPrice * @Quantity
WHERE 销售单据编号=(SELECT 销售单据编号 FROM inserted) AND 商品编号=(SELECT 商品编号 FROM inserted)
在系统运行一段时间后，此表中有近一千万条数据。
程序员在数据库中执行如下SQL语句：
SELECT 商品编号,sum(数量) FROM 销售单据明细表 WHERE 单价>200 GROUP BY 商品编号
为提高此查询的执行效率，在不改变SQL语句的情况下，请给出提高查询执行效率的方案并阐明理由。
对此表的插入操作速度很慢，请分析插入速度慢的原因，并在不改变数据库服务器硬件、不改变表基本结构、不清理数据的情况下，说明应如何优化此表的插入操作。

【参考答案】
(1）在不改变SQL语句且不进行硬件调整的情况下，可以为销售单据明细表“商品编号”建立一个索引来提高查询的效率。
另外如果只针对本条select语句进行优化，那么还可以使用分区表的方式，将单价按照<=200和>200的关系分为两张表，也可以提高检索效率。
(2）在一个插入或更新事务处理中，新建行被同时添加到INSERTED表和触发器表中。INSERTED表中行是触发器表中新行的副本。当表中有一千万条数据时，INSERTED表中也存储着大量的数据，而触发器要在INSERTED表中进行查询，所以插入的速度会很慢。可以通过删除INSERTED表中数据的方法来优化插入的速度,不会影响数据库服务器硬件、不改变表基本结构、不清理数据的情况下。
或者
(2)由题可知，该触发器名叫AutoCountSum，是后触发型触发器，引发触发器执行的操作是插入（insert）和更新（update）。
该触发器执行时，会定义两个变量@UnitPrice和@Quantity，分别用来存储刚刚插入的单价和数量（因为是inserted表），并将销售单据明细表中对应记录的总价更新为@UnitPrice * @Quantity ，即单价*数量的值。
触发器的缺点是会消耗系统资源，如果频繁触发，对系统性能影响将会非常大。根据题中描述，系统运行一段时间后表中有近一千万条数据，说明插入操作非常频繁，所以触发器是导致插入速度慢的直接原因。只需要使用存储过程来替代触发器即可解决此问题，优化此表的插入操作

第九套 备份

设某超市经营管理系统使用SQL Server 2008数据库管理系统。为了保证数据库可靠运行，数据库管理员设置了每天夜间对数据库一次全备份，备份数据保留2个月的备份策略。上线运行1年后，SQL Server数据库中数据已经达到近200GB。每天夜间要运行3个小时才能将数据库进行一次全备份，影响了夜间统计等业务正常运行。同时，备份空间也非常紧张。请解释出现此现象的原因，并提出优化的方法。(10分)

产生此现象的原因是系统采用了全备份策略，随着业务的开展，需要备份数据量逐渐增大，备份时间越来越长，占用了系统的资源，从而影响了其它业务。解决的方法是采用全备份+差异备份+日志备份组合策略备份数据库。全备份+差异备份+日志备份组合策略是指在全备份中加—些差异备份，比如每周日0:00进行一次全备份，然后每天0:00点进行一次差异备份，然后再两次差异备份之间增加一些日志备份。这样做备份和恢复的速度都比较快，而当系统出现故障时，丢失的数据也很少。
备份示意图如下:
目志备份1目志备份2
日志备份3目志备份4
全各份1
差异各份1
差异各份2
周o:oo12:0018:00周一0:0o
12:0018:00周二0:0o
如果系统在周二的差异备份之前出现故障，则应首先尝试备份活动日志（(日志尾部)，然后再按顺序恢复全备份1，差异备份1，日志备份3和日志备份4，然后再恢复备份的尾部日志。如果尾部日志备份成功，则数据库可以还原到故障点。
这种备份策略虽然备份频率高，但是备份时间短，占用的备份空间也小，而且不会产生数据丢失。
【解题思路】
全备份(Full Backup)就是对整个系统进行完全备份，包括系统和数据。这种备份方式的好处就是很直观，容易被人理解，当数据丢失时容易恢复。然而它也有不足之处:首先由于每天都对系统进行完全备份，因此在备份数据中有大量重复的，这些重复的数据占用大量的存储空间;其次，由于需要备份的数据量相当大，因此备份所需的时间比较长。
差异备份(Differential Backup)就是每次备份的数据是相对于上一次全备份之后增加和修改过的数据。它以前一次全备份为基准点，备份全备份之后变化的数据文件、日志文件以及数据库中其它被修改的内容。因此，差异备份通常比全备份占用的时间小且执行速度快，带回增加备份的复杂程度。日志备份(Log Bakup) :日志备份并不备份数据库本身，它只是备份日志记录，从而只备份上次备份之后到当前备份时间发生变化的日志内容。-般情况下，日志备份比完整备份和差异备份使用的资源少，因此可以更频繁的使用日志备份，以减少数据丢失的风险。
全备份+差异备份+日志备份组合策略组合了三种备份方式的优点，可以缩短备份时间、节约备份空间，提高备份的效率。

第十套

设某社交网站使用SQL Server 2008数据库管理系统，上线运行1年后，用户操作速度明显变慢，数据库服务器负荷很高。经技术人员分析，发现速度缓慢的原因为用户频繁访问网站，查看自己是否有新消息。某工程师建议利用数据库复制技术将相关数据表定期（例如1分钟)分发到另外一台服务器的只读数据库中，以便用户查询使用。请从数据─致性、用户体验、可扩展性三个方面逐一分析此方案优劣。说明此方案是否可行，并简要给出理由。

优点:该方案能满足数据一致性。因为利用数据库快照复制技术，可以将相关表分发给另外一台服务器，可以满足数据的一致性。
数据的更新可以采用自动更新和手动更新方式。更新时间可以在建立分发数据库时设置，设置时间小于1分钟。通过这种方式可以满足用户体验。
数据库的分发只是在软件层面进行，对于已经建好的发布和定阅可以直接用delete按钮删除，然后重新进行设置，可扩展性强。
缺点:增加了一台服务器的开销。
对于本题，该方案可行。存放网站系统的服务器被设为发布服务器，另外一台服务器可以作为分发服务器和订阅服务器。发布服务器以事务复制的方式向分发服务器发送信息。客户端通过订阅服务器获取数据，实现信息同步更新。这种方式不会影响网站系统服务器的运行，又能保证客户端可以随时刷新信息。
【解题思路】
SQL Server主要采用发布、分发、订阅的方式来处理复制。源数据所在的服务器是发布服务器，复制发布数据。发布服务器把要发布的数据的所有改变情况复制到分发服务器，分发服务器包含一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变发给订阅服务器。
SQL Server复制技术包括快照复制、事务复制和合并复制。
快照复制:发布服务器会定时向订阅服务器传送数据，如果订阅服务器修改过数据，定时传递后订阅服务器的数据修改会全部丢失，全部以发布服务器为准。事务复制:发布服务器会定时检查数据是否有修改过，如果有则向订阅服务器传送数据（记录行级)。
合并复制:发布服务器与订阅服务器可以独立修改，会定时合并。

【康解释】

第十二套 分割表 分区表

设某超市经营管理系统使用SQL Server 2008数据库管理系统，上线运行一年后，用户反映速度缓慢。经技术人员分析，发现速度缓慢的原因为销售单据表和销售单据明细表数据量巨大，当进行这两个表相关查询时就会造成SQL语句执行缓慢。已知这两个表结构如下：销售单据表(销售单据编号，付款总金额，销售时间，销售人员编号，收银台编号)，销售单据明细表(销售单据编号，销售时间，商品编号，单价，数量，总价)从业务上看，程序经常会查询1个月内和最近6个月内销售相关数据。为了保证系统稳定运行，两位工程师分别给出了两种优化方案：甲工程师：按照销售时间将两张表分别拆分为每月一张表，在系统进行查询时按照给定条件到相关的表中直接查询，降低查询涉及到的数据。乙工程师：使用SQL Server 2008的分区功能，将两张表变为分区表，按照销售时间每年一个分区，降低查询涉及到的数据。请从程序修改的难易程度、查询1个月内数据的执行速度、查询最近6个月内数据的执行速度3个方面比较甲乙工程师方案。根据上述分析结果，请基于甲乙工程师的方案给出最合理的优化方案并说明理由。

【康解释】
表分割对于所有的数据库都适用，而表分区只能用于特定的数据库;表分区属于数据库物理设计，表分割属于逻辑设计。
从程序修改的难易程度：甲工程师易于实现。
查询1个月内数据的执行速度：甲工程师速度快
查询最近6个月内数据的执行速度：乙工程师快

采用乙工程师的优化方案比较好。因为分区表的特点是∶如果数据量大，而且数据是分段的，而且对不同段的数据使用的操作不同，则适于使用分区表。本题中对表中当前月的数据，经常进行的操作是添加、修改、删除和查询，而对于以往的数据，则几乎不需要操作，或者操作仅限于查询，那么就可以按月份对表进行分区，从而提高系统的执行效率。

第十六套 触发器 临时表

某学校图书管理系统保存了能够借书的教师数据，该数据来源于人事系统(另一家开发商开发)的教师数据。现需要将教师数据自动同步到图书管理系统中，教师数据总计约1万条，每天数据修改量约20条。
为了实现自动数据同步，图书管理系统新上线了一数据同步程序。在同步程序上线后，数据库管理员发现每天数据备份量大幅度增加，尤其是数据库日志大幅度增加。经核查，该数据同步程序操作为每10分钟将所有从人事系统中同步的数据删除，而后将数据重新插入。
现需要对同步程序进行优化，某工程师提出了两套方案：
①在人事系统相关表中新增触发器，提取新增数据到增量数据表，同步程序每10分钟将增量数据表中数据同步至图书管理系统中。
②在图书管理系统中新建一临时数据库，在其中新增与人事系统结构相同的表，同步程序每10分钟将人事的数据传输到图书管理系统对应表中，在图书管理系统数据库中使用SQL语句对比图书管理系统数据，获取增量后修改图书管理系统数据。
请从对人事系统影响、对图书管理系统影响、系统整体运行效率、相关开发人员协调复杂度四方面比较两套方案，并给出理由。

使用方案①，如果数据量大时会影响人事系统的运行，但对图书管理系统影响不大，系统整体运行效率能提高，相关开发人员协调复杂度也小。而使用方案②会影响人事系统和图书管理系统的运行，也会增加系统的开销，相关开发人员协调的复杂度，比方案①低。
总体而言，方案①优于方案②。
原因是在方案①中，只有在触发器设计时需要相关人员协调，数据量大时可能会影响人事系统的运行，而方案②只有在开发人员协调的复杂度比方案①低以外，总体来说会增加图书管理系统的开销。因为新建的数据库会占用系统的开销，影响系统整体运行的效率。
【解题思路】
触发器是―种特殊的存储过程，其特殊性在于它不需要由用户来直接调用，而是对表中的数据进行UPDATE、INSERT和DELETE操作时自动触发执行的。触发器通常用于保证业务规则和数据完成性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。触发器通常用在下列场合:完成比CHECK约束更复杂的数据约束，为保证数据库性能而维护的非规范化数据，可实现复杂的商业规则，触发器也可以评估数据修改前后的表状态，并根据差异采取对策。在方案①中，只有在触发器设计时需要相关人员协调，数据量大时可能会影响人事系统的运行，但是总体而言是比方案②优秀的。

【康解释】
方案1，在数据量大时会影响人事系统相关表的运行，但对图书管理系统影响不大，系统的整体效率会提高，相关开发人员协调复杂度小
方案2，会影响人事系统和图书管理系统的运行，新建数据库会增加系统的开销，影响系统整体运行效率，相关人员协调的复杂度相比较方案1，更低
综上所述，方案1优于方案2.因为而方案2仅仅在开发人员协调的复杂度比方案1低

第十九套 索引 A-S架构

在SQL Server 2008数据库中存在销售单据明细表，其建表语句为：
CREATE TABLE 销售单据明细表(
销售单据编号 varchar(8),
商品编号 varchar(8),
单价 money,
数量 int,
总价 money
);
在系统运行一段时间后，此表中有近千万条数据，程序员在数据库中执行如下SQL语句：
SELECT 商品编号, sum(总价) FROM 销售单据明细表 WHERE 单价 > 50 GROUP BY 商品编号
为提高此查询的执行效率，需在此表中建立索引，请给出建立索引的方案。
为了提高系统的可靠性和性能，某工程师提出将原有单机运行的SQL Server 2008改为Active-Standby架构的数据库运行模式：在不考虑硬件性能提升因素的前提下，请分析此架构是否能够提高系统的性能和可靠性，并说明原因。

本题考查的是索引相关知识，如索引定义，在什么地方建立索引，建立索引的条件等等，以及Active-Standby架构的数据库运行
模式的机
制。

(1)【参考答案】
CREATE UNIQUE CLUSTERED INDEX ix_商品编号on销售单据明细表(商品编号)
(2)【参考答案】
此架构为数据库服务器容错架构，双机热备份方案中的双机热备模式。在不考虑硬件性能提升因素的前提下，此架构是能够提高系统的性能和可靠性，
原因:
双机热备份技术是一种软硬件结合的较高容错应用方案。该方案是由两台服务器系统和一个外接共享磁盘阵列柜及相应的双机热备份软件组成。
在这个容错方案中，操作系统和应用程序安装在两台服务器的本地系统盘上，整个网络系统的数据是通过磁盘阵列集中管理和数据备份的。
数据集中管理是通过双机热备份系统，将所有站点的数据直接从中央存储设备读取和存储，并由专业人员进行管理，极大地保护了数据的安全性和保密性。用户的数据存放在外接共享磁盘阵列中，在一台服务器出现故障时，备机主动替代主机工作，保证网络服务不间断。

(2) Active-Standby架构的数据库运行模式
双机热备特指基于高可用系统中的两台服务器的热备(或高可用)，因两机高可用在国内使用较多，故得名双机热备，双机高可用按工作中的切换方式分为:主-备方式(Active-sStandby方式)和双主机方式(Active-Active方式)，主-备方式即指的是一台服务器处于某种业务的激活状态(即Active状态)，另一台服务器处于该业务的备用状态(即Standby状态)。而双主机方式即指两种不同业务分别在两台服务器上互为主备状态(即Active-Standby和Standby-Active状态)。
双机热备有两种实现方式，一种是两台服务器通过一个共享的存储设备（一般是共享的磁盘阵列或存储区域网SAN)，通过安装双机软件实现双机热备，称为共享方式。另一种方式是没有共享的存储设备，数据同时存放于各自服务器中，称为纯软件方式或软件同步数据方式。基于存储共享的实现方式是双机热备的最标准的方案，在主从模式工作中，两台服务器以一个虚拟的IP地址对外提供服务，服务请求发送给主服务器(active server) 承担。同时，两台服务器通过心跳线(heartbeat line)侦测另一台服务器的工作状况。一旦主服务器出.见故障，备服务器(standby server)根据心跳侦测的情况做出判断，在较短时间内完成切换，接管主机上的所有资源，成为新的主服务器。由于使用共享的存储设备，因此两台服务器使用的实际上是一样的数据，由双机或集群软件对其进行管理。