第十章、数据库运行维护与优化

第十章、数据库运行维护与优化

版权来源233网校,若有侵权联系shaoyayu0419@qq.com删除
版权地址：www.233.com

内容提要：

1、了解数据库运行维护的基本原理

2、了解运行状态监控与分析

3、了解数据库存储空间管理

4、掌握数据库性能优化的方法

第一节 数据库运行维护基本工作

1、DBAS进入运行维护阶段的主要任务：

1. 保证数据库系统安全、可靠且高效率地运行。

2. 数据库的运行除了DBMS与数据库外，还需要各种系统部件协同工作。

3. 首先必须有各种相应的应用程序

4. 其次各应用程序与DBMS都需要在操作系统　　（OS）支持下工作。

2、维护工作包括：

1. 数据库转储与恢复

2. 数据库安全性、完整性控制

3. 检测并改善数据库性能

4. 数据库的重组和重构

5. 重组不修改数据库原有设计的逻辑结构和物理结构

6. 重构部分修改模式和内模式

第二节 运行状态监控与分析

　　数据库的监控分析：指管理员借助工具监测DBMS的运行情况，掌握系统当前或以往的负荷、配置、应用等信息，并分析监测数据的性能参数和环境信息，评估DBMS的整体运行状态。

根据监控分析实现不同，分为：

1. 数据库系统建立的自动监控机制，由DBMS自动监测数据库的运行情况。
2. 管理员手动实施的监控机制

根据监控对象不同，分为：

1. 数据库构架体系的监控

   　　监控空间基本信息、空间使用率与剩余空间大小等。

2. 数据库性能监控
   　　监控数据缓冲区命中率、库缓冲、用户锁、索引使用、等待事件等。

第三节 数据库存储空间管理

对数据库使用空间进行管理是一项非常重要的工作。

1. 空间使用情况变化带来的问题：

　　降低数据库系统服务性能

　　空间溢出导致灾难停机事故

2. 数据的存储结构分为：

　　逻辑存储结构

　　物理存储结构

　　SQL Server数据库中 一个逻辑上的数据库直接和一组物理上的数据文件对应，没有表空间概念。
　　DBMS对空间的管理包括：创建数据库空间、更改空间大小、删除空间、修改空间状态，新建、移动、关联数据文件等。

第四节 数据库性能优化

　　数据库性能优化是DBAS系统上线后最常见的运行维护任务之一。

　　进行数据库性能优化时，首先要确定优化目标，一般从数据库运行环境、参数调整、模式调整、数据库存储优化、查询优化几个方面考虑。

1、数据库运行环境与参数调整

　　一般来说，可以从外部环境、调整内存分配、调整磁盘I/O、调整资源竞争等几个方面着手改变数据库参数，提高其性能。

　　外部调整：数据库性能和外部环境有很大关系，主要外部条件包括：CPU（CPU的处理能力是衡量计算机性能的一个标志）、网络（大量的SQL数据在网络上传输会导致网速变慢）。

　　CPU使用情况判断依据：对于一台数据库服务器，如业务空闲时使用率超过90%。说明服务器缺乏CPU资源，如高峰时CPU使用率仍然低，说明服务器CPU资源充足。

　　解决方案；增加CPU数量或者终止需要许多资源的进程。

　　调整内存分配：调整相关参数控制数据库内存分配，很大程度改善数据库系统性能。

　　调整磁盘I/O：数据库性能优劣的重要度量是响应时间。

　　改善方法：令I/O时间最小化，减少磁盘上文件竞争带来的瓶颈。

调整竞争：

1. 修改参数以控制连接到数据库的最大进程数。

2. 减少调度进程的竞争

3. 减少多线程服务进程竞争

4. 减少重做日志缓冲区竞争

5. 减少回滚段竞争。

2、模式调整与优化

　　数据库的规范化过程：高效率利用存储空间，减少数据的冗余，减少数据的不一致性。

　　问题：规范化关系解决了数据维护的异常，并使数据冗余最小化，但会导致数据处理性能下降。

　　反规范化：将规范化关系转换为非规范化的关系的过程。

　　反规范化方法：增加派生冗余列、增加冗余列、重新组表、分割表和新增汇总表等方法。都会破坏数据完整性。　

　　采用反规范化技术从实际出发均衡利弊。

2.1、反规范化的手段：

（1）增加派生性冗余列

　　增加的列由表中的一些数据项经过计算生成。
　　作用：查询时减少连接操作，避免使用聚合函数。
　　例如：销售单据明细表（单据编号，商品编号，单价，数量，总价），总价=单价*数量，属于派生性增加冗余列。

（2）增加冗余列

　　在多个表中增加具有相同语义的列，常用来在查询时避免连接操作。（外码不属于这种情况）

（3）重新组表

　　当用户经常查看的某些数据是由多个表连接之后才能得到，就可以考虑先把这些数据重新组成一个表，这样在查询时会减少连接提高效率。

（4）分割表（重点）

　　水平分割：根据行的使用特点进行分割，分割之后所有表的结构都相同。而存储的数据不同。使用并（Union）操作。

　　垂直分割：根据列的特点分割，分割后所得表除了都包含主码外其他列都不相同。通常将常用列与不常用列分别放在不同表中，查询减少I/O次数。缺点是使用连接（Join）操作

（5）新增汇总表

　　大量执行报表等汇总操作会影响性能。　

　　为降低汇总操作的时间，将频繁使用的统计中间结果或最终结果存储在汇总表中，从而降低数据访问量和汇总操作的CPU计算量。

　　如：日销售额统计表

3、存储优化

3.1、物化视图（索引视图）

　　定义：包括一个查询结果的数据库对象，是预先计算并保存表连接或聚集等耗时较多的操作结果。（一个定期刷新数据的视图，自动刷新或人工刷新）

　　适用于多个数据量较大的表进行连接操作及分布式数据库中在多站点的表进行连接时使用。

　　物化视图还可以进行远程数据的本地复制（物化视图的存储也称为快照），用于实施数据库间的同步。

3.2.、聚集

　　聚集是物理存储表中数据的可选择的方法。

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。

　　一个聚集是一组表，将经常一起使用的具有同一公共列值的多个表中的数据行存储在一起，由公共列构成聚集码。

　　作用：最小化必须执行的I/O次数。

　　注意：将记录插入聚集的表之前，必须建立聚集索引，且按聚集码进行索引；对于剧集中的多个表，聚集值只存储一次。

　　劣势：聚集表的插入、更新、删除性能差，具体使用要权衡。

4、查询优化

　　效率低下的SQL语句常常是系统效率不佳的主要原因。常用优化方法如下：

4.1、合理使用索引

　　权衡：索引提高查询效率，索引增加系统开销。

建立索引原则：

1. 是否为一个属性建索引：该属性是码或存在某个查询中被使用。
2. 在哪些属性建立索引：若一个关系的多个属性共同出现在若干个查询中，一般会采用多属性索引。
3. 是否建立聚簇索引：聚簇索引适合范围查询，可建立多属性索引。优点体现在数据记录存取过程中。
4. 使用散列还是树索引：散列适合等值查询；关系数据库多使用B+索引，支持作为搜索码的属性上的等值查询和范围查询。

索引使用原则：

1. 经常在查询中作为条件被使用的列，应为其建立索引。

2. 频繁进行排序或分组的列，应为其建立索引。

3. 一个列的值域很大时，应为其建立索引。

4. 如果待排列的列有多个，建复合索引。

5. 可以使用系统工具来检查索引完整性，必要时进行修复。当数据表更新大量数据后，删除并重建索引以提高查询。

6. 索引建立完成后，运行期间还需调优。

7. 调优的目的：动态地评估需求。

索引调整和修改的原因：

1. 由于缺少索引，某些查询语句执行时间过长。

2. 某些索引自始至终没有使用，却占用了较多磁盘空间。

3. 某些索引建立在被频繁改变的属性上，导致系统开销过大。

4.2、避免或简化排序

　　ORDER BY和GROUP BY语句的执行涉及排序，磁盘排序开销很大，应利用索引自动以适当的次序产生输出。

影响优化器的因素：

　　由于现有索引不足，导致排序索引中不包括一个或几个待排序的列。

　　Group by和order by子句中列的次序与索引次序不一致。

　　排列的列来自不同的表。

　　为避免不必要的排序，要正确地增建索引，合理合并数据库表，如排序不可避免，则简化它，如缩小排序列的范围。

4.3、消除对大型表数据的顺序存取

　　嵌套查询中，对表的顺序存取严重影响查询效率。　
　　优化方法：对连接列进行索引，或使用并集来避免顺序存取。

4.4、避免复杂正则表达式

　　原因：消耗较多CPU资源进行字符串匹配。

4.5、使用临时表加速查询

　　将表的一个子集进行排序并创建临时表。

4.6、用排序来取代非顺序磁盘存取

　　原因：非顺序磁盘存取最慢。使用以数据库排序功能为基础的SQL替代非顺序存取。

4.7、不充分的连接条件。

　　原因：左（右）外连接包含与NULL数据匹配，相比内连接，代价可能很高。

4.8、存储过程

　　尽量使用自带返回参数，而非自定义返回参数，减少不必要参数，避免数据冗余。

4.9、不要随意使用游标

　　原因：占用较多系统资源。尤其是大规模并发情况下，很容易使得系统资源耗尽而崩溃。

4.10、事务处理

　　一旦将多个处理放入事务，会降低系统处理速度。将频繁操作的多个可分割的处理过程放入多个存储过程中，这样就大大提高系统响应速度。

5、SQL Server 性能工具

SQL Server Profiler
　　用来监视SQL Server事件的多用途监控工具（性能、存储过程、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分）
　　原因：由于SQL Server采用将数据缓冲在内存中的方式，因此内存使用率较高是正常情况，且现阶段I/O并不存在问题，说明内存满足需求。（2分）
　　此阶段CPU使用率很高，说明CPU计算机资源不足，因此增加CPU数量对解决问题更具有效。（2分）

8、某商场商品经营管理系统使用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 INDEX ix_商品销售额 
ON 商品销售额视图(商品号);（10分）
（1）请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。
（2）此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。
（1）【解题思路】
　　该技术人员使用了带有索引的视图，将所关心的数据(商品号，销售额，该商品号在表中出现的次数)从销售明细表中提取出来建立视图，并对该视图建立按商品号排序的聚簇索引，这样大大减少了在搜索不同商品的销售额时调用的数据表的规模，从而提高了查询效率。由于表的数据规模很大，建立该视图后，同一种商品不会多次出现在表中，而是通过一个计数变量cnt表示，这样在检索时大大减少了检索规模。创建索引时，UNIQUE关键字表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。
【参考答案】
　　语句功能：建立包含(商品号，销售额，该商品表中出现次数)带索引的视图，并建立按商品号对应销售额UNIQUE聚簇排序的索引，大大缩小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列（序列号，销售日期，商品号，销售数量，销售价格）转换成了目标属性列，减少了搜索空间；同时建立UNIQUE CLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。
（2）【解题思路】
　　由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。而用户通过视图对数据进行增加、删除、修改时，有意或无意地对不属于视图范围内的基本表数据进行操作，会破坏数据的一致性。而且视图中的数据本身就是冗余的，每次对表进行修改时，同时也要对相应的视图进行修改，这大大增加了系统的负担。
【参考答案】
　　不合适，每天大量的插入操作使得在修改表的同时也要对视图进行修改，增加了系统的负担，然而该统计功能一个月才用一次，这样导致系统的利用率也较为低下。