数据库运行维护与优化

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

10.1 数据库运行维护基本工作①数据库的转储与恢复②数据库的安全性、完整性控制③检测并改善数据库的性能④数据库的重组和重构

10.2 运行状态监控与分析：管理员借助相应工具在数据库运行过程中监测数据库系统的运行情况，掌握系统当前或以往的负荷、配置、应用和其他相关信息，并对监测数据进行分析。

①自动监控机制②手动监控机制③对数据库构架体系的监控④对数据库性能的监控

10.3 数据库存储空间管理：数据库的存储结构一般分为逻辑存储结构和物理存储结构，其物理存储结构决定了数据库存储数据时数据文件所占用空间的大小及分布。不同的数据库对空间的管理是不一样的。空间不足可扩大存储容量或合理备份转储。10.4 数据库性能优化：数据库应用系统最常见的运行维护任务之一。10.4.1 数据库运行环境与参数调整

1 外部调整：①CPU：在空闲是使用率超过90%，说明服务器缺乏CPU资源；若高峰期时CPU 使用率依然很低，说明CPU资源充足。②网络：大量SQL数据会使网络速度变慢，调整网络。

2 调整内存分配

3 调整磁盘I/O：响应时间的最大组成部分。

4 调整竞争：方法

①修改参数以控制连接到数据库的最大进程数②减少调度进程的竞争

③减少多线程服务进程竞争④减少重做日志缓冲区竞争⑤减少回滚段竞争。

10.4.2 模式调整与优化1 增加派生性冗余列：指表中增加的列由表中的一些数据项经过计算生成。2 增加冗余列：是指在多个表中增加具有相同语义的列，它常用来查询时避免连接操作。3 重新组表：经常查看的某些数据是由多个表连接之后才能得到，就可考虑重新组表。

4 分割表：表分割有两种方式，水平分割和垂直分割。水平分割是根据数据行的使用特点进行分割，分割之后所得的所有表结构都相同，而存储的数据不同。增加查询复杂度，查询数据时需用Union（并）操作。垂直分割根据数据列的特点进行分割，分割之后所得的所有表中除了都含有主码列外其余列都不同。数据行变小，减少I/O次数，而且大多数据操作只在少数或者一个表中进行，提高系统性能，查询所有数据需要Jion(连接)操作。

5 新增汇总表：为了降低这些操作的时间，可以根据统计操作的使用情况，将频繁使用的统计操作的中间结果或最终结果存储在汇总表中，当用户发出汇总需求时，即可直接从汇总表中获取数据，降低了数据访问量以及汇总操作的CPU计算量。

10.4.3 存储优化1物化视图：包括一个查询结果的数据库对象，是预先计算并保存表连接或聚集等耗时较多的操作结果，提高了读取速度，还可以进行远程数据的本地复制。特别适用抽取大数据量表中某些信息以及分布式环境中跨节点进行多表数据连接的场合。

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

10.4.4 查询优化1 合理使用索引：建立索引的目的就是为了提高查询效率。决策：①是否为一个属性建立索引②在哪些属性上建立索引③是否建立聚簇索引④使用散列索引还是树索引。使用原则：①经常在查询中作为条件被使用的列，应为其建立索引②频繁进行排序或分组的列，应为其建立索引③一个列的值域很大时，应为其建立索引④如果待排序的列有多个，应在这些列上建立复合索引⑤可以使用系统工具来检查索引的完整性，必要时进行修复。

2 避免或简化排序：避免或简化对大型表进行重复的排序。

3 消除对大型表数据的顺序存取：避免这种情况的主要方法就是对连接的列进行索引。

4 避免复杂的正则表达式

5 使用临时表加速查询：把表的一个子集进行排序并创建临时表，有时能加速查询。

6 用排序来取代非排序磁盘存取：非顺序磁盘存取是最慢的操作。

7 不充分的连接条件：左(右)连接消耗的资源非常之多。8 存储过程9 不要随意使用游标：游标会较占用多的系统资源。10 事务处理

10.4.5 SQL Server 性能工具 1 SQLServer Profiler

2 数据库引擎优化顾问：通过对数据库的工作负荷测试，由数据库引擎优化顾问给出优化建议，然后再通过管理员进行相应的修改，使数据库达到一个比较优化的状态。

小结

数据库建立并投入运行后，必须对系统的运行情况进行有效监控分析，以便在出现问题的时候对数据库进行维护和管理。本章结合数据库运行维护日常工作，简要介绍了包括监控分析、空间管理、参数调整、系统性能优化以及查询优化涉及的一些方法和技术。