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

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

数据库运行维护基本工作

• 数据库的转储与恢复
• 数据库的安全性、完整性控制
• 检测并改善数据库的性能
• 数据库的重组和重构
  • 重组并不修改数据库原有设计的逻辑结构和物理结构。
  • 重构部分修改数据库的模式和内模式。

运行状态监控与分析

• 自动监控机制
• 手动监控机制
  • 对数据库构架体系的监控：空间基本信息，空间使用率，剩余空间大小等
  • 对数据库性能的监控：缓冲区命中率，用户锁，索引使用情况，等待事件，共享池等

数据库性能优化

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

1. 外部调整
   • CPU
   • 网络
2. 调整内存分配
3. 调整磁盘I/O
4. 调整竞争
   • 修改参数以控制连接到数据库的最大进程数
   • 减少调度进程的竞争
   • 减少多线程服务进程竞争
   • 减少重做日志缓冲区竞争
   • 减少回滚段竞争

模式调整与优化

• 增加派生性冗余列：增加一个经常需要计算的列，比如“单价” * “数量” = “总价” ，如果单价和数量不经常变动，则可以增加一个总价列。
• 增加冗余列：在表中增加具有一个在别的表中有相同意思的列，用来避免连接操作。
• 重新组表：对于经常需要连接操作的表，可以考虑将它们重新组成一个表。
• 分隔表：表中数据过多，将它们分割开，提高查询速度。
  • 水平分割：分割之后表的结构不变。每个子表存储不同的数据。
  • 垂直分割：分割之后列发生变化，但是都必须含有主码属性。
• 新增汇总表：将频繁使用的统计操作的中间结果或最终结果存储在汇总表中。

存储优化

• 物化视图：预先计算并保存表连接或聚集等耗时多的操作结果。查询的时候不再执行查询语句，而是直接查询物化视图。
• 聚集

查询优化

• 合理使用索引
  • 树索引适合等值查询和范围查询。
  • 散列索引非常适合等值查询，但是不能范围查询。
• 经常在查询中作为条件被使用的列，应当建立索引。
• 频繁进行排序或分组的列，应当建立索引。
• 列的值域很大，应当建立索引。
• 待排序的列有多个，应该建立复合索引。
• 数据库更新大量数据时，可以先删除索引再建立索引。
• 避免或简化排序
• 消除对大型表数据的顺序存取
• 避免复杂的正则表达式
• 使用临时表加速查询
• 用排序来取代非顺序磁盘存取
• 不充分的连接条件：左右连接要匹配NULL数据，尽量使用自然连接。
• 存储过程：SQL脚本需要先编译再运行，存储过程已经编译过了，不需要再次编译。
• 不要随意使用游标
• 事务处理

SQL Server性能工具

• SQL Server Profiler：用来监视SQL Server中的事件的工具。
• 数据库引擎优化顾问：通过对数据库的工作负荷测试，由数据库引擎优化顾问提出优化建议，然后再通过管理员进行相应的修改。