sql server数据库性能的优化（转）

编者按：数据库性能优化和数据库管理系统密切相关，不同的数据库管理系统在具体操作上有很大不同。继本报连续在2003年第48期、49期上刊登《sybase数据库性能调优》和《oracle服务器性能调整攻略》，分别讨论了sybase和oracle数据库管理系统以后，本期我们将具体介绍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瓶颈等，在这种情况下，单纯进行数据库内部性能调整是不会改善系统性能的。我们可以通过windows nt的系统监视器(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子系统有关的问题也许是最容易的，多数情况下，增加磁盘驱动器就可以解决这个性能问题。 

　　当然，影响性能的因素很多，而应用又各不相同，找出一个通用的优化方案是很困难的，只能是在系统开发和维护的过程中针对运行的具体情况，不断加以调整。