NCREⅢ数据库技术3

性能优化

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

答案：
[1]对登录日志表中在登录时间列上以月为单位创建右侧分区函数，将登录日志表分成12个分区，每个分区对应一年中一个月的值。因为操作仅限于查询，建立分区表可以有效的提高查询效率。
[2]釆用方案2。对于CPU、内存、磁盘等硬件设备在空闲时使用率超过90%，说明硬件资源匮乏。如果在高峰时使用率小于90%说明硬件资源充足。本题中，内存和磁盘的使用率都高于90%，而CPU的使用率不高于50%，说明内存、硬盘的资源匮乏，需要调整，而CPU的资源充足不需要调整。因此在硬件调整时，应提高内存的容量和硬盘的访问效率。RAID1相对于RAID0来说提高了读速度，加强了系统的可靠性，但是写效率没有提高。

触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来直接调用，而是对表中的数据进行UPDATE、INSERT和DELETE操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。触发器通常用在下列场合：完成比CHECK约束更复杂的数据约束，为保证数据库性能而维护的非规范化数据，可实现复杂的商业规则，触发器也可以评估数据修改前后的表状态，并根据差异釆取对策。在方案①中，只有在触发器设计时需要相关人员协调，数据量大时可能会影响人事系统的运行，但是总体而言是比方案②优秀的。

答案：
使用方案①，如果数据量大时会影响人事系统的运行，但对图书管理系统影响不大，系统整体运行效率能提高，相关开发人员协调复杂度也小。而使用方案②会影响人事系统和图书管理系统的运行，也会增加系统的开销。相关开发人员协调的复杂度，比方案①低。
总体而言，方案①优于方案②。原因是在方案①中，只有在触发器设计时需要相关人员协调，数据量大时可能会影响人事系统的运行，而方案②只有在开发人员协调的复杂度比方案①低以外，总体来说会增加图书管理系统的开销。因为新建的数据库会占用系统的开销，影响系统整体运行的效率。

本题考查的是索引相关知识，如索引定义，在什么地方建立索引，建立索引的条件以及RAID1磁盘阵列的特点。
（1）索引的知识
索引技术是一种快速数据访问技术，它将一个文件的每个记录在某个或某些领域（或称为属性）上的取值与该记录的物理地址直接联系起来，提供了一种根据记录域的取值快速访问文件记录的机制。索引的使用要恰到好处，其使用原则一般如下：
①经常在查询中作为条件被使用的列，应为其建立索引；
②频繁进行排序或分组（即进行group by或order by操作）的列，应为其建立索引；
③一个列的值域很大时，应为其建立索引；
④如果待排序的列有多个，应在这些列上建立复合索引。
（2）RAID1磁盘阵列的特点
RAID1磁盘阵列级，是一种镜像磁盘阵列，其原理就是将一块硬盘的数据以相同位置指向另一块硬盘的位置。RAID1又称为Mirror或Mirroring，它的宗旨是最大限度地保证用户数据的可用性和可修复性。RAID1的操作方式是把用户写入硬盘的数据百分之百地自动复制到另外一个硬盘上。由于对存储的数据进行百分之百的备份，在所有RAID级别中，RAID1提供最高的数据安全保障。同样，由于数据的百分之百备份，备份数据占了总存储空间的一半，因而，Mirror的磁盘空间利用率低，存储成本高。 Mirror虽不能提高存储性能，但由于其具有的高数据安全性，使其尤其适用于存放重要数据，如服务器和数据库存储等领域。
RAID1提高了读速度，加强了系统可靠性。但其磁盘的利用率低，冗余度为50%，同时写速度并未提高。RAID1经常要求保证用户数据的可用性和可修复性场所，体现系统的可靠性，而不是读写速度。
RAID1是将一个两块硬盘所构成RAID磁盘阵列，其容量仅等于一块硬盘的容量，因为另一块只是当作数据"镜像"。RAID1磁盘阵列显然是最可靠的一种阵列，因为它总是保持一份完整的数据备份。它的性能自然没有RAID0磁盘阵列那样好，但其数据读取确实较单一硬盘来的快，因为数据会从两块硬盘中较快的一块中读出。RAID1磁盘阵列的写入速度通常较慢，因为数据得分别写入两块硬盘中并做比较。RAID1磁盘阵列一般支持"热交换"，就是说阵列中硬盘的移除或替换可以在系统运行时进行，无须中断退出系统。RAID1磁盘阵列是十分安全的，不过也是较贵的一种RAID磁盘阵列解决方案，因为两块硬盘仅能提供一块硬盘的容量。RAID1磁盘阵列主要用在数据安全性很高，而且要求能够快速恢复被破坏的数据的场合。

答案：
[1]"销售单据编号"、"商品编号"和"是否有效"都是查询的条件，因此在这三列上建立索引，可以提高查询效率。
原因：经常在查询中作为条件被使用的列，应为其建立索引，可提高查询效率。
[2]从读的方面来看：当原始数据繁忙时，可直接从镜像拷贝中读取数据，因此RAID1可以提高读取性能，也提高了磁盘的存储空间。增加一个RAID1可以提高磁盘的存储空间，在一定程度上提高了读速度，加强了系统的可靠性。但是增加RAID1，系统变成四块硬盘，同时也就增加了系统的I/O开销，当数据量大时，并不能很好地提高系统的读的速度。
从写的方面来看：RAID1磁盘阵列的写入速度通常较慢，因为数据得分别写入两块硬盘中并做比较。现在增加了一块RAID1磁盘阵列，相当于有四块硬盘，在写入数据时，可能会做两个备份，写入的速度更慢。
因此从读写两个方面来说，增加一块RAID1磁盘阵列不是解决问题的好的方式，这种方法不建议使用。

[1]:能够提高性能。
原本的代码中使用了游标。游标是一种临时的数据库对象，常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生的，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。
但是，题中检索的是系所名中含有"电"字的，事实上所有学生被检索的概率理论上都是相同的，此处不符合"创建一次，使用多次"的前提条件。
另外，游标使用时会对行加锁，可能会影响其他业务的正常进行。同时数据量大时，游标会导致效率低下。
还有，游标其实是相当于把磁盘数据整体放入了内存中，可能会带来巨量的内存占用的情况。
综上所述，一般情况下尽量不要使用游标，因为会降低效率影响性能。除非遇到特殊情况。游标在使用完毕后应及时释放销毁，以释放资源。
[2]:第一条select语句可以使用此索引，第二条select语句无法使用此索引。
基于多个列来创建的索引是复合索引。以下列语句为例：
CREATE INDEX idx_test ON 学生表 (身份证号,姓名,性别)
此索引将优先对"身份证号"排序（第一顺序），若存在身份证号相同的多名学生，则对这些学生的"姓名"排序（第二顺序），若姓名也存在相同的，则按照"性别"排序（第三顺序）。

复合索引使用存在下列限制：

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

[1]:使用索引视图，即先建立针对销售明细表中的商品编号和总价的汇总建立视图，然后针对该视图建立索引。
建立视图：
    create view s(商品编号，总价)
    as
    select 商品编号，sum(总价) from 销售明细表
    where 单价>50
    group by 商品编号
建立索引：
    create unique index 商品编号 on s(商品编号，总价）
视图是从一个或多个表中导出来的虚拟表，就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。
如有需要，可以在视图上派生出虚拟列，例如本题中的视图s的总价字段，就是由sum（总价）计算而来。
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
如此操作，既能提高查询效率，又在基本表中数据发生更改时不会影响查询结果。
[2]:能够提高可靠性，但无法提高性能。
Active-Standby模式指的是一种服务器容错技术，具体方式是部署两台相同的服务器，共享存储设备，其中一台正常工作，当该服务器出现问题时，另一台（备用服务器）接管数据库。
由于在服务器出现问题的时候，备用服务器能够接替主服务器进行工作，对其他任何配置均不存在任何影响，所以Active-Standby模式能够提高可靠性。但是由于无论何时，都是一台服务器提供服务，所以Active-Standby模式对性能没有任何的改善。

[1]union有一个distinct的动作，需要进行并运算的数据量越大，其操作执行越慢（union对两个表进行联合查询时会进行一个去重的操作，而union all进行联合查询的时候，会将所有数据都给罗列出来）只需把union改为union all，就会取消这个distinct操作，速度将会大幅增加，SQL性能得到加强。
[2]由于题目已提及I/O很高，方案1的RAID1方案对于I/O没有帮助，所以否决。
方案2的RAID0方案虽然对磁盘I/O的提升最大，但是由于RAID0对于数据的可靠性没有任何帮助，暂时候选。
方案3采用RAID5方案。RAID5的读取性能与RAID0接近，但写入速度比单个磁盘稍慢，而数据的可靠性高于RAID0。

RAID5是RAID0和RAID1的折中方案，RAID5可以为系统提供数据安全保障，可靠性高。RAID5具有与RAID0相近似的数据读取速度，只是多了一个奇偶校验信息导致写入数据速度对比单个磁盘较慢。RAID5磁盘空间利用率比RAID1高，存储成本相对较低，是目前运用较多的一种解决方案。
结合题目提及对于磁盘写入性能要求非常高，所以只能选方案2，因为方案1对读写性能没有提升，而方案3本题中十分看重的降低了写入性能。

1、由于运行一段时间后，就有了近千万条数据，说明数据的写入量很大，这种情况下不能使用索引，因为写入量大，所以索引的维护开销将非常巨大，不仅无法提高效率，反而会降低性能，所以不适合查询优化。
由于题目不允许改变SQL语句，所以无法使用反规范化或使用临时表的方式，以空间换取时间。题目同样不允许硬件调整，不允许进行硬件升级。
本题最适合的方案是数据表的水平分割，由于商品编号有很多，所以应该按照单价区间进行水平分割。

2、根据题目表述，两种方案都能够提高系统运行速度。但是，即便是平峰时CPU的负载也很高，高峰时甚至达到100%，而内存使用率低于CPU使用率，迫切程度低于CPU的需求。所以在成本有限的前提下，应该选择方案1，增加一颗CPU以缓解CPU的性能压力。选择此方案所得到的效果将高于方案2。

(1)由于是针对商品编号进行检索，所以为商品编号建立索引可以提高检索效率。
另外如果只针对本条select语句进行优化，那么还可以使用分区表的方式，将单价按照<=200和>200的关系分为两张表，也可以提高检索效率。
(2)由题可知，该触发器名叫AutoCountSum，是后触发型触发器，引发触发器执行的操作是插入（insert）和更新（update）。
该触发器执行时，会定义两个变量@UnitPrice和@Quantity，分别用来存储刚刚插入的单价和数量（因为是inserted表），并将销售单据明细表中对应记录的总价更新为@UnitPrice * @Quantity ，即单价*数量的值。
触发器的缺点是会消耗系统资源，如果频繁触发，对系统性能影响将会非常大。根据题中描述，系统运行一段时间后表中有近一千万条数据，说明插入操作非常频繁，所以触发器是导致插入速度慢的直接原因。只需要使用存储过程来替代触发器即可解决此问题，优化此表的插入操作。

（1）如果数据量大，且数据是分段的，并且对不同段的数据使用的操作不同，则适用于使用分区表。
题中数据已达千万条，且查询总价大于200的，并且总价已经提前计算完毕，只需查询不必计算，此种情况适宜使用分区表。参考sql语句如下：
CREATE PARTITION FUNCTION total_price(INT)
AS RANGE LEFT FOR VALUES (200);
其中小写部分可以是任意符合SQL语法的函数名。SQL语句中不区分大小写，此处大小写仅做内容区分使用。
（2）服务器的CPU和内存资源利用率很低，仅仅I/O高，说明硬件性能还有很大的富余量，采用甲工程师的方案花费甚巨但效果有限，乙工程师的方案0花费且有针对性，乙方案更合理。
题中的"总价"字段属于派生性冗余列，目的是牺牲存储空间，换取查询统计的处理速度（一次性计算完毕，之后不需再次计算）。由题中"运行一段时间后数据已达千万条"可确认，该系统的写入操作十分频繁，结合题中"CPU使用不超过30%"可确认，将总价字段从表中删除，之后的查询条件改为"单价*数量>200"，以CPU性能为代价，降低I/O。

（1）SQL语言中的并运算可以将多个查询语句的结果集合合并为一个结果集。UNION默认为删除合并后结果中重复的记录，如果是UNION ALL则包含全部记录，包括重复的记录。由于学生档案中本身就不允许存在重复记录，故将UNION改为UNION ALL，可以省掉检查重复的操作，提高执行速度，并且不会影响执行结果。

（2）并不能提高执行速度。非顺序磁盘存取是最慢的操作。建立索引后，依旧需要对磁盘进行读取才能取出姓名字段的值。建立索引并没有从根本上解决这一问题，可以使用数据库排序功能来取代非顺序磁盘存取以改进查询效率。

(1)由于数据表中数据量很大，运行一段时间后便已近千万条，所以不适合按照商品编号创建索引，这样会导致维护索引的开销太大。
因为检索单价大于100的商品，所以可以从创建分区的角度下手，具体命令如下：
Create partition function part_func_name(int)
As range left for values(100)
（其中"part_func_name"可以被替换成任意内容）
(2)RAID0：磁盘读写性能更高，但是存在致命的问题--没有任何冗余容错，一旦数据损毁，无法进行任何补救，一般用于对读写性能要求高、对数据安全性要求不高的环境下。
RAID1：磁盘写入速度较慢，读取速度不受影响或者略有提升。由于存在冗余备份，即便是磁盘损毁，也存在另一块磁盘上的数据备份，适合使用在对数据安全性要求很高的环境下。
RAID5：兼顾存储性能、数据安全和存储成本等各方面因素，它可以理解为 RAID0 和 RAID1 的折中方案，读取效率很高，写入效率一般，通常是目前综合性能最佳的数据保护解决方案。
综上所述，写性能方面，RAID0相较于RAID5将会有大幅提升，RAID1则近乎相同。数据可靠性方面，RAID1则远高于RAID0。两种方案各有利弊。
由于题目中要解决的问题是磁盘IO问题，那么显然RAID0方案对症下药。只要解决了数据备份问题，配合上适宜的备份策略，那么方案1更为合理。

（1）在不改变SQL语句且不做分区的前提下，提高查询效率的方法首先应该考虑使用索引。
本题检索条件为“今天（不含）之前体温>=37.3摄氏度”的学工信息。
因为要检索的时间并非某时间段，是近乎所有字段（截止至今天零时），所以针对申报日期建立索引基本没有帮助，此处应该为体温建立索引。
因为不发烧的师生占绝大多数，为体温建立索引后，要检索的内容只占整个数据库中一小部分，大幅减少了检索范围。
（2）应该根据申报日期字段做分区。由于数据是每日申报，已经申报过的往期数据几乎仅限于查询操作，此种情况最适合做分区表。

（1）为表增加索引"出校日期"，增加索引提高查询效率。
（2）根据"出校日期"作为字段分区，因为该字段经常被使用作为查询条件，可以根据该字段将表分成若干分区，查询时，根据该字段快速定位到某个分区进行查询，提高查询效率。而若根据学工号字段分区的话，不能提高查询执行效率，所以甲工程师给出的建议更合理。