11-2杨梅-计算机等级考试-三级数据库技术-精-第10章 数据库运行维护与优化

计算机等级考试三级数据库技术精讲班主讲老师：杨梅,（5）新增汇总表大量执行报表等汇总操作会影响性能。为降低汇总操作的时间，将频繁使用的统计中间结果或最终结果存储在汇总表中，从而降低数据访问量和汇总操作的CPU计算量。如：日销售额统计表,第四节数据库性能优化,3、存储优化（1）物化视图（索引视图）定义：包括一个查询结果的数据库对象，是预先计算并保存表连接或聚集等耗时较多的操作结果。（一个定期刷新数据的视图，自动刷新或人工刷新）适用于多个数据量较大的表进行连接操作及分布式数据库中在多站点的表进行连接时使用。物化视图还可以进行远程数据的本地复制（物化视图的存储也称为快照），用于实施数据库间的同步。,第四节数据库性能优化,（2）聚集聚集是物理存储表中数据的可选择的方法。一个聚集是一组表，将经常一起使用的具有同一公共列值的多个表中的数据行存储在一起，由公共列构成聚集码。作用：最小化必须执行的I/O次数。注意：将记录插入聚集的表之前，必须建立聚集索引，且按聚集码进行索引；对于剧集中的多个表，聚集值只存储一次。劣势：聚集表的插入、更新、删除性能差，具体使用要权衡。,第四节数据库性能优化,4、查询优化效率低下的SQL语句常常是系统效率不佳的主要原因。常用优化方法如下：（1）合理使用索引权衡：索引提高查询效率，索引增加系统开销。,第四节数据库性能优化,建立索引原则：是否为一个属性建索引：该属性是码或存在某个查询中被使用。在哪些属性建立索引：若一个关系的多个属性共同出现在若干个查询中，一般会采用多属性索引。是否建立聚簇索引：聚簇索引适合范围查询，可建立多属性索引。优点体现在数据记录存取过程中。使用散列还是树索引：散列适合等值查询；关系数据库多使用B+索引，支持作为搜索码的属性上的等值查询和范围查询。,第四节数据库性能优化,索引使用原则：经常在查询中作为条件被使用的列，应为其建立索引。频繁进行排序或分组的列，应为其建立索引。一个列的值域很大时，应为其建立索引。如果待排列的列有多个，建复合索引。可以使用系统工具来检查索引完整性，必要时进行修复。当数据表更新大量数据后，删除并重建索引以提高查询。,第四节数据库性能优化,索引建立完成后，运行期间还需调优。调优的目的：动态地评估需求。索引调整和修改的原因：由于缺少索引，某些查询语句执行时间过长。某些索引自始至终没有使用，却占用了较多磁盘空间。某些索引建立在被频繁改变的属性上，导致系统开销过大。,第四节数据库性能优化,（2）避免或简化排序ORDERBY和GROUPBY语句的执行涉及排序，磁盘排序开销很大，应利用索引自动以适当的次序产生输出。影响优化器的因素：由于现有索引不足，导致排序索引中不包括一个或几个待排序的列。Groupby和orderby子句中列的次序与索引次序不一致。排列的列来自不同的表。为避免不必要的排序，要正确地增建索引，合理合并数据库表，如排序不可避免，则简化它，如缩小排序列的范围。,第四节数据库性能优化,（3）消除对大型表数据的顺序存取嵌套查询中，对表的顺序存取严重影响查询效率。优化方法：对连接列进行索引，或使用并集来避免顺序存取。（4）避免复杂正则表达式原因：消耗较多CPU资源进行字符串匹配。（5）使用临时表加速查询将表的一个子集进行排序并创建临时表。,第四节数据库性能优化,（6）用排序来取代非顺序磁盘存取原因：非顺序磁盘存取最慢。使用以数据库排序功能为基础的SQL替代非顺序存取。（7）不充分的连接条件。原因：左（右）外连接包含与NULL数据匹配，相比内连接，代价可能很高。（8）存储过程尽量使用自带返回参数，而非自定义返回参数，减少不必要参数，避免数据冗余。,第四节数据库性能优化,（9）不要随意使用游标原因：占用较多系统资源。尤其是大规模并发情况下，很容易使得系统资源耗尽而崩溃。（10）事务处理一旦将多个处理放入事务，会降低系统处理速度。将频繁操作的多个可分割的处理过程放入多个存储过程中，这样就大大提高系统响应速度。,第四节数据库性能优化,5、SQLServer性能工具SQLServerProfiler用来监视SQLServer事件的多用途监控工具（性能、存储过程、T-SQL语句运行等监控）。结果存储在一个跟踪文件中，可通过分析文件诊断问题。数据库引擎优化顾问测试数据库工作负荷（一组在数据库中执行的T-SQL语句），给出优化建议。,第四节数据库性能优化,例题讲解,1、数据库管理员是数据库的主要维护者，设有如下针对数据库的工作：数据库的转储和恢复数据库的安全性、完整性控制数据库性能的监控分析和改进数据库的重组和重构以上工作属于数据库管理员职责的是（）A仅和B仅和C仅和D、和答案：D,例题讲解,2、某数据库应用系统在运行中，用户反映某操作很慢。系统工程师在用户的客户机及数据库服务器上使用数据库管理系统自带的客户端程序执行此操作所涉及的SQL语句，发现在两个环境下SQL语句执行的速度都很慢。为了提高效率，下列检查无需进行的是（）A检查数据库中是否存在大量锁B检查数据库服务器的CPU使用情况C检查这些操作涉及到的表是否已建立有效的索引D检查客户机到数据库服务器的网络情况答案：D,例题讲解,3、监控数据库系统运行状态是数据库管理员非常重要的职责。数据库管理员有如下工作：定期实施数据库完整备份并将备份数据传送到远端容灾中心定期查看数据库空间是否满足业务需求定期查看数据缓冲区命中率、数据库锁情况定期查看数据库用户会话情况使用自动化手段检查数据库CPU使用情况，出现异常情况时自动发短信通知系统管理员,例题讲解,上述工作中属于数据库监控工作的是（）A全部B仅、和C仅、和D仅、和答案：D,例题讲解,4、数据库管理员要随时观察数据库的动态变化，并在数据库出现错误、故障或产生不适应的情况时能够随时采取有效措施保护数据库。这种监控机制称为（）。答案：手动监控机制,例题讲解,5、在分布式数据库中，查询处理和优化比集中式数据库要复杂得多，其中查询优化需要考虑的主要因素包括I/O代价、CPU代价和通信代价。一般而言，分布式数据库查询优化的首要目标是（）A使查询执行时I/O代价最省B使查询执行时CPU代价最省C使查询执行时I/O和CPU代价最省D使查询执行时通信代价最省答案：D,例题讲解,6、设某全国性的运输企业建立了大型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的数据信息，从而可以进一步优化系统性能。,例题讲解,7、在成功导入历史数据后，此系统顺利上线运行，第一周，发现数据服务器CPU使用率高，达到近90%，高峰期间达到100%，且系统内存使用率90%，但系统I/O很轻。业务人员反映系统操作速度很慢。为了提高系统运行速度，在不修改应用程序的前提下，两位工程师提出了不同的解决方法：,例题讲解,1为服务器增加2颗CPU，缓解CPU使用率很高的问题。2为服务器增加一倍内存，缓解内存使用率很高的问题。考虑成本，现阶段只能按照一种方案实施，请指出在现有情况下，哪种方案更合理并给出理由。,例题讲解,【评分5分】第一种方案比较合理（1分）原因：由于SQLServer采用将数据缓冲在内存中的方式，因此内存使用率较高是正常情况，且现阶段I/O并不存在问题，说明内存满足需求。（2分）此阶段CPU使用率很高，说明CPU计算机资源不足，因此增加CPU数量对解决问题更具有效。（2分）,例题讲解,8、某商场商品经营管理系统使用SQLServer2008数据库管理系统，此系统上线运行1年后，业务人员使用某统计功能（此功能每月使用一次）时发现速度很慢。该统计功能主要执行的SQL语句如下：,例题讲解,SELECT商品号,SUM(销售数量*销售价格)销售额FROM销售明细GROUPBY商品号;该销售明细表的建表语句如下：CREATETABLE销售明细(序列号intIDENTITY(1,1)NOTNULL,商品号intNOTNULL,销售日期datetimeNULL,销售数量intNOTNULL,销售价格intNOTNULL);,例题讲解,并在销售明细表上建有如下索引：CREATEindexix_销售明细_商品号on销售明细(商品号);某技术人员提出通过执行下述语句以提高此查询的运行效率：CREATEVIEW商品销售额视图WITHSCHEMABINDINGASSELECT商品号,SUM(销售数量*销售价格)销售额,COUNT_BIG(*)cntFROMdbo.销售明细GROUPBY商品号;CREATEUNIQUECLUSTEREDINDEXix_商品销售额ON商品销售额视图(商品号);（10分）,例题讲解,（1）请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。（2）此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。,例题讲解,（1）【解题思路】该技术人员使用了带有索引的视图，将所关心的数据(商品号，销售额，该商品号在表中出现的次数)从销售明细表中提取出来建立视图，并对该视图建立按商品号排序的聚簇索引，这样大大减少了在搜索不同商品的销售额时调用的数据表的规模，从而提高了查询效率。由于表的数据规模很大，建立该视图后，同一种商品不会多次出现在表中，而是通过一个计数变量cnt表示，这样在检索时大大减少了检索规模。创建索引时，UNIQUE关键字表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。,例题讲解,【参考答案】语句功能：建立包含(商品号，销售额，该商品表中出现次数)带索引的视图，并建立按商品号对应销售额UNIQUE聚簇排序的索引，大大缩小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列（序列号，销售日期，商品号，销售数量，销售价格）转换成了目标属性列，减少了搜索空间；同时建立UNIQUECLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高