数据库技术-数据库运行维护与优化

前言

在前面我们学习了数据库技术-安全管理（二），在完成数据库应用系统的设计并实施后,数据库系统则进入运行维护阶段。这个阶段的主要任务是保证数据库系统安全可靠且高效率的运行。
下面接着学习数据库运行维护与优化的内容，在计算机等级考试三级数据库技术中这部分经常会出现在选择题、填空题部分。
常考知识点有:熟悉数据库运行维护基本工作、理解运行状态监控与分析、熟悉数据库存储空间管理、掌握数据库的模式调整与优化、存储优化、查询优化等。

目录

• 数据库运行维护基本工作
• 运行状态监控与分析
• 数据库存储空间管理
• ★数据库性能优化★
• 数据库运行环境与参数调整
• 模式调整与优化
• 存储优化
• 查询优化
• SQL Server性能工具

数据库运行维护基本工作

数据库设计并试运行后的运行结果符合设计目标,就意味数据库可以真正投入运行,也标志开发任务的基本结束和维护工作的开始
。
在数据库系统运行过程中必需不断地对其进行监控、评价、调整与修改,以保证数据库系统正常和高效的运行。
维护工作包括的内容如下图：

• 数据库的转储与恢复
  1.数据库转储和恢复是系统正式运行后最重要的维护工作之一。
  2.作为数据库管理员，应该针对各种数据制定合理的转储计划，定期对数据库和日志文件进行备份，保证数据库一旦出现故障，也有能力恢复到正常状态。
  3.为了保证转储数据能正常进行恢复,数据库管理员需要定期对转储的数据进行恢复测试工作。
  4.恢复测试工作是在与正式运行环境类似的环境中,利用转储数据进行数据库数据的恢复,恢复完成后对数据库中数据进行检查，确认数据的完整性、可靠性和一致性。

• 数据库的安全性、完整控制性
  1.数据库管理员可以通过行政手段制定规范,通过权限管理、口令、跟踪及审计等来保证数据库的安全性。
  2.数据库管理员应该维护多个数据副本,可能的话将数据副本放在不同的安全地点。并采取有效措施预防和及时清除病毒，避免其对数据库的威胁。
  3.数据库管理员需要根据实际情况(数据库运行过程中的应用环境变化)来修改原有的安全控制策略。同样,数据库管理员也要不断修正数据库的完整性约束条件，以满足用户的需求。

• 检测并改善数据库的性能
  数据库管理员应经常检测数据库系统的运行,观查数据库的动态变化情况,以便在数据库出现故障时能够及时恢复或采取其他有效措施保护数据库。由于性能优化本身是无止境的，在进行系统性能调整过程中，数据库管理员要平衡性能调整的投入和产出比，以满足应用系统需求为基准进行性能优化。

• 数据库的重组与重构
  数据库管理员应定期对数据库进行重组，即按照系统设计要求对数据库存储空间进行全面调整。
  提高数据库性能的几种方法：调整磁盘分区方法和存储空间、重新安排数据的存储、整理回收碎块。

  数据库的重新组织并不修改逻辑结构和物理结构。而数据库的重构则不同，它是指部分修改数据库的模式和内模式。

  数据库的重构是指当前数据库的设计不能满足新的需求,需调整数据库的模式和内模式。但只能结合实际情况对部分修改，如果应用需求变动太大，则应该考虑重新设计数据库及相应的应用程序。

运行状态监控与分析

数据库的的监控分析是指管理员借助相应工具在数据库运行过程中监测数据库系统的运行情况,掌握系统当前或以往的负荷、配置、应用和其他相关信息,并对监测数据进行分析,分析数据库的性能参数和环境信息,评估系统的整体运行状态,为系统的安全运行和性能调优提供依据,并提出相应的改善措施，帮助管理人员尽早清除数据库的性能隐患。

根据监测分析实见方法的不同,监测分析机制分以下两种。

• 自动监控机制
  由数据库系统建立的,该机制由DBMS自动监测数据库的运行情况,并在数据库出现问题时自动采取有效措施,防止出现的问题对数据库运行产生不良影响。

• 手动监控机制
  由管理员建立的,要随时观察数据库的动态变化,并在数据库出现错误,故障或产生不适应的情况，如数据库死锁、对数据库的误操作等)时能够随时采取有效措施保护数据库。

根据监控对象的不同,监控分析又分为对数据库构架体系的监控和对数据库性能的监控。

• 对数据库构架体系的监控
  主要监控内容空间基本信息、 空间使用率与剩余空间大小空间是否具有自动扩展的能力、部些表的扩展将引起空间的扩展以及段的占用空间与区间数等。

• 对数据库性能的监控
  主要监控内容:临时段使用情况、索引的使用情况，等待事件和共享池等。

数据库存储空间管理

在数据库系统的运行过程中，对数据库空间使用情况，特别是空间的增长情况进行监控，并采取相应措施对数据库使用空间进行空间管理是一项非常重要的工作。

在数据库系统运行过程中，随着用户程序对数据库的操作，会带来数据量的变化，使数据库中出现许多数据碎片。数据量增大和碎片会导致数据库系统服务性能的降低。

数据库的存储结构一般分为逻辑存储结构和物理存储结构。物理存储结构决定了数据库存储数据时数据文件所占用空间的大小及分布。

针对不同的数据库产品，其逻辑存储结构及物理存储结构的对应方式是不同的。

数据库空间管理的内容主要包括创建数据库空间、更改空间大小、删除空间、修改空间状态、新建/移动/关联数据库文件。

数据库存储空间管理概述

• 在进行数据库设计时，需要根据数据库的数据保存期要求，数据结构信息和数据预期增长速度等因素，对数据库所需使用的空间进行预测。

• 随着数据库系统的不断运行,对数据库空间实际增长情况进行监控是非常重要的。应重点监控数据增长较快的数据表, 并统计这些表实际数据增长率,及时制定数据备份与转储策略,在数据量数据库存储能力前,及时增加这些表的存储空间。

• 如若不能进行硬件扩展,应考虑制定合理的数据备份和转储策略,调整数据保存期，将远期的历史数据,转移到其他廉价存储介质中去如光盘、磁带等,从而为新的业务数据腾出足够的存储空间。

★数据库性能优化★

数据库性能优化是数据库应用系统上线后最常见的运行维护任务之一。数据库性能优化对数据库管理员的要求也较高。

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

调整一个数据库应用系统的性能要求熟悉系统环境、数据库理系统、应用程序以及应用程序所使用的数据。一般可以从外部环境、调整内存分配、调整磁盘I/O、 调整资源竞争等几个方面来改变数据库的参数，提高其性能。

• 外部调整
  数据库并不是脱离于外部环境单独运行的。数据库的性能和外部环境有很大的关系,这些外部条件主要包括CPU和网络。

• 当数据库操作对CPU性能的要求超过数据库服务器的CPU性能时,数据库性能就受到CPU的限制,使数据库操作变慢。解决方法:为服务器适当增加CPU的数量或者终止需要许多资源的进程。

• 大量的SQL数据在网络上传输会导致网速变慢。网卡,交换机,集线器等网络设备的性能对网络的影响很明显。解决方法:通过调整网络设备。也可以在一定程度上提高数据库系统的性能。

• 调整内存分配
  通过调整相关参数控制数据库的内存分配，也可在一定程度上提高数据库系统的性能。

• 调整磁盘I/O
  数据库性能优劣的一个重要度量是响应时间,而I/O时间是响应时间的最大组成部分。因此,可以通过令I/O时间最小化，减少磁盘上的文件竞争带来的瓶颈等方法来改善数据库系统的性能。

• 竞争调整
  因资源有限，当多个进程对相同的资源发出申请,导致竞争的产生。解决这个问题可以采用以下方法。

• 修改参数以控制连接到数据库的最大进程数。
• 减少调度进程的竞争。
• 减少多线程服务进程竞争。
• 减少重做日志缓冲区竞争。
• 减少回滚段竞争。

2.模式调整与优化

在数据库设计阶段,主要强调的是高效率利用存储空间,减少数据的冗余,减少数据的不致,这个过程就是规范化过程。

注意：完全规范化的数据库通常会产生很多表。因为连接操作非常耗时，所以完全规范化数据库和部分规范化数据库就会产生很大差别。

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

常用的反规范化方法：增加派生性冗余列、增加冗余列、重新组表、分割表、新增汇总表。

• 增加派生性冗余列描述:派生性冗余列是指表中增加的列由表中的一些数据项经过计算生成。它的作用是在查询时减少连接操作,避免使用聚合函数。调优方法:增加冗余列的调优方法是以空间换时间的调优方法。

• 增加冗余列
  描述:增加冗余列是指在多个表中增加具有相同语义的列,它常用来在查询时避免连接操作。其中,主码与外码在多表中的重复出现不属于冗余列
  ,这里的冗余列是非关键字段的在多表中的重复出现。
  优劣:增加冗余列可以在查询时避免连接操作,但它需要更多的磁盘空间 ,同时也增加了维护表的工作量。
  使用前提条件:要保证冗余列及其对应列数据的一致性。

• 重新组表
  当用户经常查看的**某些数据是由多个表连接之后才能得到,**就可考虑先把这些数据重新组成一个表。
  优点:在查询时会减少连接从而提高性能。
  劣势:要关注组表的数据变化。且需要更多的磁盘空间来存储数据。

• 分割表
  在一些数据库中有些表的数据过于庞大 ,或表中的数据具有使用独立性,有时为了提高性能,对表进行分割,这虽然破坏了数据表的整体性,但却使系统得到优化。表分割有下面两种方式:

• 水平分割
  水平分割是根据数据行的使用特点进行分割,分割之后所得的所有表的结构都相同,而存储的数据不同。当表中分别记录各个地区的数据或不同时期的数据的情况下可对表进行水平分割。
  缺点:水平分割会给应用增加复杂度,特别在查询所有数据时需要**Union(并)**操作。

• 垂直分割
  垂直分割是根据数据列的特点进行分割,分割之后所得的所有表中除了都含有主码列外其余列都不相同。一般在一个表中某些列常用，另一些不常用可以采用垂直分割放入不同的表中，这使得数据行变小，一个数据页就能存放更多的数据,在查询时就会减少I/O次数。
  缺点:查询所有数据是需要(Join)连接操作。

• 新增汇总表
  为了降低对数据进行加和、取平均等操作时间，可根据统计操作的使用情况，将频繁使用的统计操作的中间结果或最终结果存储在汇总表中，当用户发出汇总需求时,可以直接从汇总表中获取数据,降低了数据访问量以及汇总操作的CPU计算量。

注意

“

数据库的反规范化是为了减少表间的连接，提高查询性能，但并非所有经反规范化的数据库都是高效的，这与实际的应用有关。只有满足一定条件的数据库采用反规范化方法才能提高。无论使用何种反规范化技术都可能破坏数据的完整性，因此反规范化技术需要维护数据的完整。

3.存储优化

1.物化视图

物化视图是包括一个查询结果的数据库对象(可由系统定期刷新其中的数据）,物化视图不是在使用时才读取,而是预先计算并保存表连接或聚集等耗时较多的操作结果,提高了在查询时的读取速度。物化视图还可进行远程数据的本地复制,此时物化视图的存储也称快照。主要用于实施数据库间的同步。

物化视图的刷新分为自动刷新方式和人工刷新方式。具体的刷新方式和刷新时间在定义物化视图的时候可以定义。

物化视图优点:提高了读取速度,适用抽取大数据量表中某些信息以及分布式环境中跨节点进行多表数据连接的场合。

2.聚集

描述:聚集是物理存储表中数据的可选择方法。一个聚集是组表,可将经常一起使用的具有同一公共列值的多个表****中的数据行存储在一起，由它们的公共列构成聚集码。

优化的方法:创建一个聚集,通过聚集码存储或直询这些表中的数据，从而最小化必须执行的I/O次数，改善系统性能

聚集码：多表中相关的列称为聚集码，在将记录插入聚集的表中之前，必须要为聚集对象建立一个渠集索引且按聚集码进行索引:对于聚集中的多个表,聚集值只存储一次。

4.查询优化

效率低下的SQL语句常常是系统效率不佳的主要原因。以下是几种优化直询的常用方法。

• 1.合理使用索引
  索引是数据库中重要的数据结构,建立索引的目的就是为了提高直询效率，查询的性能在很大程度上取决于存在什么样的索引来加快选择和连接的处理。在插入删除和更新操作中,索引的存在增加系统的开销。因此,要在加快查询与事务处理效率方面获得的好处与增加开销之间进行权衡。

  索引的物理设计决策涉及的问题

  索引使用原则如下所示

  索引调整与修改原因
  在索引建立完成后,数据库系统运行期间可能还需要对索引进行调整与修改,索引调整与修改主要有以下原因:

  通过分析从系统获得的关于直询执行过程的信息,可以得到上述问题的原因。基于调优分析,可能会删除某些索引,也可能增加新的索引。调优的目的是动态地评估需求,需求随时间变化,要对索弓进行重组以获得最好的整体性能。

• 由于缺少索引，某些查询语句执行时间过长。
• 某些索引自始至终没有使用,而索引占用较多的磁盘空间。
• 某些索引建立在被频繁改变的属性上，导致系统的开销过大。
• 经常在查询中作为条件被使用的列，应为其建立索引。
• 频整进行排序或分组进行group by或order by操作的列，应为其建立索引。
• 一个列的值域很大时，应为其建立索引。
• 如果待排序的列有多个，应在这些列上建立复合索引。

• 是否为一个属性建立索引

  该属性必须是一个码，或必须存在某个直询，其选择条件(等值或的围)或连接条件中使用了该属性。

• 在哪些属性上建立索引

  可以在一个或多个属性上构建索引。而多属性索引中的属性顺序可以考虑按照其区分度进行排列。

• 是否建立聚集索引

  一个表中最多只能有一个主索引或聚簇索引，因为该表必须按照这个属性物理排序。聚簇索引最适合范围查询。

• 使用散列索引还是树索引

  关系数据库管理系统中使用B+树作为索引，散列索引适应于等值查询，尤其是在连接操作中查找匹配记录时

• 2.避免或简化排序
  通常,在运行group by和order by的SQL语句时,会涉及排序的操作,应当简化或避免对大型表进行重复的排序，因为磁盘排序的开销是很大的。与内存排序相比,磁盘排序操作很慢,会花费很长时间,降低数据库性能,而且磁盘排序会消耗临时表空间中的资源。

  当能够利用索引自动以适当的次序生输出时,优化器就可避免不必要的排序步骤。以下是一些影响因素。

• 由于现有索引的不足,导致排序时索引中不包括一个或几个带排序的列。
• group by或order by子句中列的次序与索引的次序不一样。
• 排序的列来自不同的表。

• 3.消除对大型表数据的顺序存取
  在嵌套查询时,对表的顺序存取对查询效率可能产生致命的影响。避免这种情况的主要方法就是对连接的列进行索引。还可使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。

• 4.避免复杂的正则表达式
  在使用正则表达式进行条件查询时可能会消耗较多的CPU资源进行字符串匹配工作，因此应尽量避免。

• 5.使用临时表加速查询
  把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。临时表中的行比主表中的行少,顺序就是要求的顺序，减少磁盘I/O操作，所以查询工作量可得到大幅减少。

• 6.用排序来取代非顺序的盘存取
  非顺序磁盘存取是最慢的操作。但写SQL语句时往往忽略了，使得在写应用程序时很容易写出要求存取大量非顺序页的查询。导致效率降低。有时可以使用以数据库排序功能为基础的SQL来取代非顺序的存取，以改进查询效率。

• 7.不充分的连接条件
  左(右)连接消耗的资源非常之多,因它们包含于NULL(不存在)数据匹的数据,其代价可能非常高。左(右)连接比内连接消耗资源更多，所以尽可能不使用左(右)连接的查询。

• 8.存储过程
  平时每次向数据库发送的SQL脚本,都需要先编译后执行。这样当使用相同的语句时效率就会很低,而存储过程则不需要编译就能接执行,因此速度可以很快。

• 9.不要随意使用使用游标
  游标会占用较多的系统资源，尤其是在大规模并发的情况下很容易使系统因资源耗尽崩溃。所以不要随意使用游标，且游标使用后及时关闭和销毁，以释放资源。

• 10.事务处理
  为了同时操作多个表而保证数据库的一致性,往往会用到事务。

SQL Server性能工具

• SQL Server Profiler

  SQL Server Profiler是用来监视SQL Server中事件的工具,其监视事件的结果可以存储到一个跟踪文件中。SQL Server Profiler可以监视几乎所有在SQL Server中可能发生的事件，因此SQL Server Profiler是个 多用途的性能监控工具，除监视系统性能状态外还可监事存储过程。

• 数据库引擎优化顾问

  数据库引擎优化顾问在不精通数据库结构的情况下，通过对数据库的工作负荷测试,由数据库引擎优化顾问给出优化建议,然后再通过管理员进行相应的修改,使数据库达到一个比较优化的状态。数据库引擎优化顾问通过分析一个或多个数据库的工作负荷和物理建构,然后提出优化建议。数据库引擎优化顾问对工作负荷进行分析后,数据库引擎优化顾问会建议添加、修改或删除数据库中的某些对象,如索引、视图等。

习题

1.数据库管理系统一般通过周期性检查________图来实现死锁检测。

2.优化SQL语句执行效率属于数据库应用系统_________阶段的任务。

参考资料及声明

• 全国计算机等级考试三级教程.数据库技术 2019.12.
• 三级数据库技术：全国计算机等级考试上机考试题库 2017.10.