计算机三级数据库技术备考笔记（十）

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

• 数据库运行维护基本工作
  数据库运行维护的主要内容工作内容包括日常维护、系统监控与分析、系统性能优化调整、系统进化(升级)等工作。
  ​其中日常维护又包括数据的备份与恢复、数据的完整性维护、安全性维护、存储空间管理与并发控制等。
  • 数据库的转储与恢复
  • 数据库的安全性和完整性控制
  • 数据库性能的监控分析和改进
  • 数据库的重组和重构
• 运行状态监控与分析
  所谓数据库的监控分析,是指管理员借助相应工具在数据库运行过程中监测数据库系统的运行情况,掌握系统当前或以往的负荷,配置,应用和其他相关信息,并对监测数据进行分析,分析数据库的性能参数和环境信息,评估系统的整体运行状态,为系统的安全运行和性能调优提供依据,并提出相应的改善措施,帮助管理人员尽早清除数据库的性能隐患。
  • 自动监控机制，监控数据库运行情况，有问题是自动采取措施
  • 手动监测机制
    数据库管理员要随时观察数据库的动态变化,并在数据库出现错误、故障或产生不适应的情况(如数据库死锁、对数据库的误操作等)时能够随时采取有效措施保护数据库。
  • 对数据库构架体系的监控
    主要监控内容:空间基本信息、空间使用率与剩余空间大小,空间是否具有自动扩展的能力、哪些表的扩展将引起空间的扩展,以及段的占用空间与区间数等。
  • 对数据库性能的监控
    主要监控内容:数据缓冲区的命中率、库缓冲、用户锁,锁与等待、回滚段、临时段使用情况索引的使用情况、等待事件和共享池等。
• 数据库存储空间管理
  • 数据库的存储结构一般分为逻辑存储结构和物理存储结构,其物理存储结构决定了数据库存储数据时数据文件所占用空间的大小及分布。
• 数据库性能优化
  • 数据库运行环境与参数调整
    • 外部调整：CPU--（空闲时90%）、网络
    • 调整内存分配
    • 调整磁盘I/O（输入输出时间）
    • 调整竞争
      • ①修改参数以控制连接到数据库的最大进程数
      • ②减少调度进程的竞争
      • ③减少多线程服务进程竞争
      • ④减少重做日志缓冲区竞争
      • ⑤减少回滚段竞争
  • 数据库模式调整与优化
    • 增加派生性冗余列
      派生性冗余列是指表中增加的列由表中的一些数据项经过计算生成。它的作用是在查询时减少连接操作,避免使用聚合函数。
    • 增加冗余列
      增加冗余列是指在多个表中增加具有相同语义的列,它常用来在查询时避免连接操作。值得一提的是,主码与外码在多表中的重复出现不属于冗余列,这里所指的冗余列是非键字段的在多表中的重复出现。
    • 重新组表
      当用户经常查看的某些数据是由多个表连接之后才能得到,就可以考虑先把这些数据重新组成一个表,这样在查询时会减少连接从而提高性能。
    • 分割表，增加了维护数据完整性的代价，
      • 水平分割：水平分割是根据数据行的使用特点进行分割,分割之后所得的所有表的结构都相同,而存储的数据不同。（不同地区/不同时期），增加了磁盘的I/O开销，查询数据需要使用UNION操作。
      • 垂直分割：垂直分割是根据数据列的特点进行分割,分割之后所得的所有表中除了都含有主码列外其余列都不相同，增加了磁盘的I/O开销，可以达到最大化利用Cache的目的，查询数据需要JOIN操作
    • 新增汇总表
  • 数据库的存储优化
    • 物化视图
      物化视图是包括一个查询结果的数据库对象(可由系统定期刷新其中的数据),物化视图不是在使用时才读取,而是预先计算并保存表连接或聚集等耗时较多的操作结果,这样在查询时大大提高了读取速度,特别适用于多个数据量较大的表进行连接操作及分布式数据库中需要进行分布在多站点的表进行连接操作时使用。
    • 聚集
      聚集(Clusler)是物理存储表中数据的可选择的方法。一个聚集是一组表,可将经常一起使用的具有同一公共列值的多个表中的数据行存储在一起,由它们的公共列构成聚集码。
  • 数据库的查询优化
    • 合理使用索引
      • 索引的物理设计决策涉及到以下几个问题：
        • 是否为一个属性建立索引
        • 在哪些属性上建立索引
        • 是否建立聚簇索引，聚簇索引最适合用于范围查询
        • 使用散列索引还是树索引，通常，关系数据库管理系容中使用B+树作为索引，某些系统也提供散列索引。B+索引支持作为搜索码的属性上的等值查询和范围查询。散列索引适用于等值查询。
      • 索引的使用原则：
        • 经常在查询中作为条件被使用的列，应为其建立索引
        • 频繁进行排序或分组的列，应为其建立索引
        • 一个列的值域很大时，应为其建立索引
        • 如果待排序的列有多个，应在这些列上建立复合索引
        • 可以使用系统工具来检查索引的完整性，必要时进行修复
    • 避免或简化排序
    • 消除对大型表数据的顺序存取
    • 避免复杂的正则表达式
    • 使用临时表加速查询
    • 用排序来取代非顺序磁盘存取
    • 不充分的连接条件
    • 存储过程
    • 不要随意使用游标
    • 事务处理
  • SQL Server 性能工具
• 错题笔记
  • 常见能提高连接操作性能或者减少连接操作的是:
    • 增加冗余列，指在多个表中增加具有相同语义的列，它常用来在查询时避免连接操作;
    • 反规范化，也就是降低表的规范化程度，减少表间的连接，提高查询性能;
    • 对于经常需要进行查询、连接、统计操作，且数据量大的基本表可以建立索引提高查询效率;
    • 聚集文件可以提高频繁进行多表连接操作的查询效率。
  • 数据库关于锁的优化原则为：
    • 所示并行处理的重要机制，能保持数据的一致性，即按事务进行处理
    • 系统利用锁保证数据的完整性，死锁不可避免，但是可以通过在设计时充分考虑如何避免长事务，减少排他锁时间，减少事务与用户的交互时间，杜绝让用户长时间控制事务，还要避免批量数据同时执行，尤其是耗时长并使用相同的数据表。
  • 如果数据库服务器中运行队列数量超过逻辑CPU数量说明CPU已经饱和
  • 当采用多磁盘存储系统时，可以将基本表和建立在表上的索引分别放在不同的闻 磁盘上，这样在访问基本表时，存放数据和存放索引的磁盘驱动器并行工作，可以得到较快的文件读写速度。
  • 一个表只能有一个聚集索引，数据行以此聚集索引的顺序进行存储，一个表却能有多个非聚集索引。非聚集索引和聚集索引有一样的B树结构。非聚集索引键不会对主体表的数据排序做任何改变，因为聚集索引强制SQLServer将数据以聚集索引键的顺序存储。本题向表中插入大量数据后，大量数据导入正确的做法应该先删除索引，待数据导完后再创建索引。
  • 大部分关系数据库管理系统使用B+树作为索引，如果在索引码上执行大量删除操作，索引页可能会出现空间的浪费，而这些被浪费的空间可能需要在重建索引后才可以被重用。对于一个数据量巨大的表，删除大量数据后，一般需要进行的操作是需重建该表上的B树索引。
  • 数据库系统运行过程中必须不断地对其进行监控、评价、调整与修改，以保证数据库系统正常和高效的运行。一般来说，数据库系统运行维护工作主要包括:数据库的转储和恢复;数据库的安全性和完整性控制;数据库性能的监控分析和改进;数据库的重组和重构。服务器操作系统补丁安装属于操作系统的维护工作
  • 数据库性能优化是数据库应用系统上线后最常见的运行维护任务之一。数据库性能优化对数据库管理员的要求也较高。调整一个数据库应用系统的性能要求熟悉系统环境、数据库管理系统、应用程序以及应用程序所使用的数据。单纯增加数据库CPU数量和增加服务器内存未必会提高数据库查询速度，另外数据库性能优化需要开发人员参与，也需要了解系统的业务功能和业务流程。网络性能也会影响数据库的性能，但未必一定会提高查询速度。将系统的存储冗余方案从RAIDO改为RAID1，可以加强系统的可靠性，但没有提高数据库的读写能力。根据查询条件，在适合的字段上面建立索引，一般会提高查询速度。
  • 数据库事务的四大特性
    • 原子性(Atomicity)：事务是一个不可分割的单一单位，要么全部执行成功，要么全部回滚到事务开始前的状态，不能有部分成功部分失败。
    • 一致性(Consistency)：事务执行前后，数据库的数据必须保持一致性，不会破坏数据的完整性和业务规则。
    • 隔离性(Isolation)：事务的执行应该相互隔离，即使多个事务同时并发执行，每个事务也应该感觉不到其他事务的存在，通过锁机制和隔离级别来确保。
    • 持久性(Durability)：事务一旦提交，其对数据库的修改应该是永久性的，即使系统发生故障，数据也不会丢失，通过数据库的备份、恢复和日志机制来保证。
  • 在数据库调优过程中，有时需要对数据库锁粒度进行控制。封锁粒度与系统的并发度和并发控制的开销密切相关。封锁的粒度越大，并发度就越小，同时系统的开销就越小。封锁的粒度越小，并发度就越大，同时系统的开销就越大
  • 外模式/模式映像定义了数据库中不同用户的外模式与数据库逻辑模式之间的对应关系。当数据库模式发生变化时，通过调整外模式/模式间的映像关系，使得应用程序不必随之修改，从而保证数据与应用程序间的逻辑独立性，简称数据的逻辑独立性
  • 当多个用户并发地存取数据时就会产生多个事务同时存取一个数据的情况，若对并发操作不加控制就可能会存取不正确的数据，破坏事务的一致性和数据库的一致性。并发操作带来的数据不一致性主要包括丢失修改、不可重复读和读“脏”数据，产生上述三类数据的不一致性主要原因就是并发操作破坏了事务的隔离性，所以数据库管理系统必须提供并发控制机制。
  • 事务等待图是一个有向图G=(T，U)，T为结点的集合，每个结点表示正在运行的事务;U为边的集合，每条边表示事务等待的情况。若事务T1等待事务T2，则T1、T2之间有一条有向边，从T1指向T2。如果发现图中存在回路，则表示系统中出现了死锁。