浅议SQL Server数据库的优化

[摘要]设计一个应用系统似乎并不难,但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择,这取决于特定的应用需求以及开发队伍的技能。以SQL Server为例,从后台数据库的角度讨论应用程序性能优化技巧,并且给出一些有益的建议。

[关键词]SQL Server数据库优化高效索引

中图分类号:TP3文献标识码:A文章编号:1671-7597(2009)1210114-01

一、使用良好的数据库设计方案

(一)逻辑数据库规范化问题。一般来说,逻辑数据库设计会满足规范化的前3级标准:第1规范:没有重复的组或多值的列;第2规范:每个非关键字段必须依赖于主关键字,不能依赖于一个组合式主关键字的某些组成部分;第3规范:一个非关键字段不能依赖于另一个非关键字段。遵守这些规则的数据库设计会产生较少的列和更多的表,因而也就减少了数据冗余,也减少了用于存储数据的页。

(二)生成物理数据库。要想正确选择基本物理实现策略,必须了解和利用好数据库访问格式和硬件资源的操作特点,特别是内存和磁盘子系统I/O。以下是一些常用技巧:与每个表列相关的数据类型应该反映数据所需的最小存储空间,特别是对于被索引的列更是如此。用SQLServer段把一个频繁使用的大表分割开,并放在多个单独的智能型磁盘控制器的数据库设备上,这样也可以提高性能。因为有多个磁头在查找,所以数据分离也能提高性能。

二、合理使用索引并简化排序

索引是数据库中重要的数据结构,它的根本目的就是提高查询效率。索引的使用要恰到好处,其使用原则如下:在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引;在频繁进行排序或分组(即进行groupby或orderby操作)的列上建立索引;在条件表达式中经常用到的不同值较多的列上建立索引,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。如果待排序的列有多个,可以在这些列上建立复合索引。

在数据库应用设计阶段应当尽量简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序这个步骤。为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。

三、设计高效的查询

1.如果有独特的索引,那么带有“=”操作符的WHERE子句性能最好,其次是封闭的区间(范围),再其次是开放的区间。

2.从数据库访问的角度看,含有不连续连接词(OR和IN)的WHERE子句一般来说性能不会太好。所以,优化器可能会采用R策略,这种策略会生成1个工作表,其中含有每个可能匹配的执行的标识符,优化器把这些行标志符(页号和行号)看做是指向1个表中匹配的行的“动态索引”。优化器只需扫描工作表,取出每一个行标志符,再从数据表中取得相应的行,所以R策略的代价是生成工作表。

3.包含NOT、、或!=的WHERE子句对于优化器的索引选择来说没有什么用处。因为这样的子句是排斥性的,而不是包括性的,所以在扫描整个原来数据表之前无法确定子句的选择性。

4.限制数据转换和串操作,优化器一般不会根据WHERE子句中的表达式和数据转换式生成索引选择。例如:

paycheck * 12>36000 or substring(lastname,1,1)=“L”

如果该表建立了针对paycheck和lastname的索引,就不能利用索引进行优化,可以改写上面的条件表达式为:

paycheck

5.如果没有包含合并子句的索引,那么优化器构造1个工作表以存放合并中最小的表中的行。然后再在这个表上构造1个分簇索引以完成一个高效的合并。这种作法的代价是工作表的生成和随后的分族索引的生成,这个过程叫REFORMATTING。所以应该注意RAM中或磁盘上的数据库tempdb的大小(除了SELECT INTO语句)。

四、创造良好的SQLServer数据库应用环境

(一)操作系统。操作系统性能的好坏直接影响数据库的使用性能,如果操作系统存在问题,如CPU过载、过度内存交换、磁盘I/O瓶颈等,在这种情况下,单纯进行数据库内部性能调整是不会改善系统性能的。我们可以通过WindowsNT的系统监视器(SystemMonitor)来监控各种设备,发现性能瓶颈。

(二)CPU。一种常见的性能问题就是缺乏处理能力。系统的处理能力是由系统的CPU数量、类型和速度决定的。如果系统没有足够的CPU处理能力,它就不能足够快地处理事务以满足需要。我们可以使用System Monitor确定CPU的使用率,如果以75%或更高的速率长时间运行,就可能碰到了CPU瓶颈问题,这时应该升级CPU。而当确定需要更强的处理能力,可以添加CPU或者用更快的CPU替换。

(三)内存。SQLServer可使用的内存量是SQLServer性能最关键因素之一。而内存同I/O子系统的关系也是一个非常重要的因素。例如,在I/O操作频繁的系统中,SQLServer用来缓存数据的可用内存越多,必须执行的物理I/O也就越少。这是因为数据将从数据缓存中读取而不是从磁盘读取。同样,内存量的不足会引起明显的磁盘读写瓶颈,因为系统缓存能力不足会引起更多的物理磁盘I/O。

(四)I/O子系统。由I/O子系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件有关的问题。配置很差的I/O子系统引起性能问题的严重程度仅次于编写很差的SQL语句。I/O子系统问题是这样产生的,一个磁盘驱动器能够执行的I/O操作是有限的,一般一个普通的磁盘驱动器每秒只能处理85次I/O操作,如果磁盘驱动器超载,到这些磁盘驱动器的I/O操作就要排队,SQL的I/O延迟将很长。解决I/O子系统有关的问题也许是最容易的,多数情况下,增加磁盘驱动器就可以解决这个性能问题。

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

参考文献:

[1]邵远山,基于DB2数据库应用系统的性能优化[D].安徽大学,2004年.

[2]刘博,Oracle数据库性能调整与优化[D].大连理工大学,2007年.

[3]李学强、罗省贤,基于ORACLE系统的数据库性能优化设计[J].北京印刷学院学报,2006年06期.

作者简介:

张捷,男,湖北省武穴市人,黄冈职业计算机学院计算机系教师,软件设计师,研究方向:软件设计、数据库管理。