SQL Server数据库性能优化

数据库是企业信息的核心，其应用水平的高低直接影响到企业管理水平。选择了 一个高性能的数据库产品不等于就有一个好的数据库应用系统，如果数据库系统 设计不合理，不仅会增加客户端和服务器端程序的编程和维护的难度，而且还会 影响系统实际运行的性能。一般来讲，在一个管理信息系统的分析、设计、测试 和试运行阶段，因为数据量较小，设计人员和测试人员往往只注意到功能的实 现，而很少会注意到性能的不足，等到系统投入实际运行一段时间后，才发现系 统的性能在降低，这时再来考虑提高系统性能则要花费更多的人力、物力，而其 最终结果就是给整个系统又打上了一个补丁，所以设计阶段是优化的重点。本文 以SQL Server数据库为例，从以下几个方面讨论如何实现数据库系统的性能优 化。 数据库设计 实现SQL Server数据库的优化，首先要有一个好的数据库设计方案。在实际工作 中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。实现良好 的数据库设计必须考虑这些问题: 1. 逻辑数据库规范化问题 一般来说，逻辑数据库设计会满足规范化的前3级标准： 第1规范：没有重复的组或多值的列; 第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字 的某些组成部分; 第3规范: 一个非关键字段不能依赖于另一个非关键字段。 遵守这些规则的数据库设计会产生较少的列和更多的表，因而也就减少了数据冗 余，也减少了用于存储数据的页。 2. 生成物理数据库 要想正确选择基本物理实现策略，必须了解和利用好数据库访问格式和硬件资源 的操作特点，特别是内存和磁盘子系统I/O。以下是一些常用技巧： 与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索 引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段 可以被更快地读取，而且可以在一个数据页上放置更多的数据行，因而也就减少 了I/O操作。 把一个表放在某个物理设备上，再通过SQL Server的段把它的不分簇索引放在一 个不同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制 器和数据分离技术的情况下，这样做的好处更加明显。 用SQL Server段把一个频繁使用的大表分割开，并放在多个单独的智能型磁盘控 制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据 分离也能提高性能。 用SQL Server段把文本或图像列的数据存放在一个单独的物理设备上可以提高性 能。一个专用的智能型的控制器能进一步提高性能。 应用系统设计 在应用系统的设计中，要着重考虑以下几点： 1．合理使用索引 索引是数据库中重要的数据结构，它的根本目的就是提高查询效率。索引的使用 要恰到好处，其使用原则如下： 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则 由优化器自动生成索引；在频繁进行排序或分组（即进行group by或order by操 作）的列上建立索引；在条件表达式中经常用到的不同值较多的列上建立索引， 在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两 个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而 会严重降低更新速度。 如果待排序的列有多个，可以在这些列上建立复合索 引。 2. 避免或简化排序 应当尽量简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次 序产生输出时，优化器就避免了排序这个步骤。为了避免不必要的排序，就要正 确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于 效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序 的列的范围等。 3．消除对大型表行数据的顺序存取 在嵌套查询中，表的顺序存取对查询效率可能产生致命的影响。我们有时可以使 用并集来避免顺序存取。尽管也许在所有的检查列上都有索引，但某些形式的 where子句会强迫优化器使用顺序存取，这一点也应注意。 4. 避免相关子查询 如果一个列同时在主查询和where子句中出现，很可能当主查询中的列值改变之 后，子查询必须重新查询一次。而且查询嵌套层次越多，效率越低，因此应当尽 量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。 5．避免困难的正规表达式 Mathes和Like关键字支持通配符匹配，但这种匹配特别耗时。例如：SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”，即使在zipcode字段上已建立 了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为：SELECT * FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查 询，显然会大大提高速度。 6．使用临时表加速查询 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排 序操作，而且在其他方面还能简化优化器的工作。临时表中的行要比主表中的行 少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得 到大幅减少。但要注意，临时表创建后不会反映主表的修改。在主表中数据频繁 修改的情况下，注意不要丢失数据。 操作系统相关优化 操作系统性能的好坏直接影响数据库的使用性能，如果操作系统存在问题，如 CPU过载、过度内存交换、磁盘I/O瓶颈等，在这种情况下，单纯进行数据库内部 性能调整是不会改善系统性能的。我们可以通过WindowsNT的系统监视器 (System Monitor)来监控各种设备，发现性能瓶颈。 CPU 一种常见的性能问题就是缺乏处理能力。系统的处理能力是由系统的CPU数 量、类型和速度决定的。如果系统没有足够的CPU处理能力，它就不能足够快地 处理事务以满足需要。我们可以使用System Monitor确定CPU的使用率，如果以 75%或更高的速率长时间运行，就可能碰到了CPU瓶颈问题，这时应该升级 CPU。但是升级前必须监视系统的其他特性，如果是因为SQL语句效率非常低， 优化语句就有助于解决较低的CPU利用率。而当确定需要更强的处理能力，可以 添加CPU或者用更快的CPU 替换。 内存 SQL Server可使用的内存量是SQL Server性能最关键因素之一。而内存同 I/O子系统的关系也是一个非常重要的因素。例如，在I/O操作频繁的系统中， SQL Server用来缓存数据的可用内存越多，必须执行的物理I/O也就越少。这是 因为数据将从数据缓存中读取而不是从磁盘读取。同样，内存量的不足会引起明 显的磁盘读写瓶颈，因为系统缓存能力不足会引起更多的物理磁盘I/O。 可以利用System Monitor检查SQL Server的Buffer Cache Hit Ratio计数器，如果 命中率经常低于90%，就应该添加更多的内存。 I/O子系统 由I/O子系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件 有关的问题。配置很差的I/O子系统引起性能问题的严重程度仅次于编写很差的 SQL语句。I/O子系统问题是这样产生的，一个磁盘驱动器能够执行的I/O操作是 有限的，一般一个普通的磁盘驱动器每秒只能处理85次I/O操作，如果磁盘驱动 器超载，到这些磁盘驱动器的I/O操作就要排队，SQL的I/O延迟将很长。这可能 会使锁持续的时间更长，或者使线程在等待资源的过程中保持空闲状态，其结果 就是整个系统的性能受到影响。 解决I/O子系统有关的问题也许是最容易的，多数情况下，增加磁盘驱动器就可 以解决这个性能问题。 当然，影响性能的因素很多，而应用又各不相同，找出一个通用的优化方案是很 困难的，只能是在系统开发和维护的过程中针对运行的具体情况，不断加以调 整