浅谈如何优化SQL Server数据库

赵灼

[摘 要] 本文从数据库系统优化的目标、原则入手，通过实例研讨SQL Server数据库优化的解决方法，以提高数据库系统的运行效能。

[关键词] 数据库； 优化； SQL Server

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2014 . 03. 059

[中图分类号] TP392 [文献标识码] A [文章编号] 1673 - 0194（2014）03- 0125- 03

数据库技术是计算机科学技术发展最快、应用最广泛的领域之一，在信息管理自动化程度日益提高的今天，数据库技术已经成为现代计算机信息系统和应用系统的基础与核心。近年来，随着多媒体技术、空间数据库技术和计算机网络的飞速发展，数据库系统的发展十分迅速，应用领域越来越广，要保障在这些领域中应用的信息系统高效、正常运行，必须处理好数据库性能优化问题。大多数数据库在运行一段时间后都会产生一定的性能问题，而数据库系统的性能决定了数据库的可用性和生命力。本文主要探讨SQL Server数据库性能优化方面的问题。

1 数据库优化的目标和基本原则

数据库优化的目标是避免磁盘I/O瓶颈、减少CPU利用率，减少资源竞争。一般来说应该遵循以下原则：

（1） 合理设计数据库，关键字段建立索引。

（2） 合理的数据库对象放置策略。

（3） SQL语句语法的优化。

（4） 合理使用外部工具，使SQL变得更加灵活和高效。

（5） 清理删除日志，备份数据库和清除垃圾数据。

2 数据库优化的实现方法

2.1 数据库设计的优化

设计阶段是决定系统性能的关键阶段，而且关系到以后几乎所有性能调优的过程数据库设计。

2.1.1 索引设计

索引需要根据预计的数据量和查询来设计，可能与将来实际使用会有所区别。关于索引，应该注意以下几个方面：

（1） 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

（2） 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

（3） 把经常出现的字段组台在—起，组成组合索引，组合索引的宇段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

（4） 一个表不要加太多索引。

（5） 要注意索引的维护，周期性重建索引，重新编译存储过程。

2.1.2 字段的设计

字段是数据库最基本的单位，其设计对性能的影响很大：

（1） 数据类型尽量用数字型。

（2） 数据类型尽量小（在满足可预见未来需求的前提下）。

（3） 尽量不要允许NULL（可以用NOT NULL+DEFAULT代替）。

（4） 少用TEXT和IMAGE。

（5） 自增字段要慎用，不利于数据迁移。

（6） 数据库物理存储和环境的设计。在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使系统在将来能适应比较多的用户并发和比较大的数据量。这里需要注意文件组的作用，使用文件组可以有效地将I/O操作分散到不同的物理硬盘，提高并发能力。

2.2 合理的数据库对象放置策略

数据库对象放置策略是均匀地把数据分布在系统磁盘中，平衡I/O访问，避免I/O瓶颈。

（1） 访问分散到不同的磁盘，使用户数据尽可能跨越多个设备，多个I/O运转，避免I/O竞争，克服访问瓶颈，分别放置随机访问和连续访问数据。

（2） 分离系统数据库I/O和应用数据库I/O。把系统审计表和临时库表放在不忙的磁盘上。

（3） 把事务日志放在单独的磁盘上，减少磁盘I/O开销。

（4） 把频繁访问的“活性”表放在不同的磁盘上；把频繁用的表、频繁做Join操作的表分别放在单独的磁盘上，甚至把频繁访问的表的字段放在不同的磁盘上，把访问分散到不同的磁盘上，避免I/O争夺。

（5） 利用段分离频繁访问的表及其索引（非聚族的）分离文本和图像数据。段的目的是平衡I/O，避免瓶颈，增加吞吐量，实现并行扫描，提高并发度，最大化磁盘的吞吐量。利用逻辑段功能，分别放置“活性”表及其非聚族索引以平衡I/O。当然最好利用系统的默认段。另外，利用段可以使备份和恢复数据更加灵活，使系统授权更加灵活。

2.3 编码阶段的优化

下面罗列一些编程阶段需要注意的事项。

2.3.1 只返回需要的数据

返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：

（1） 横向来看，不要写SELECT *的语句，而是选择需要的字段。

（2） 纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。

（3） 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，堵塞其他进程。

（4） 对于聚合查询，可以用HAVING子句进一步限定返回的行。

2.3.2 尽量少做重复的工作

这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的事项如下：

（1） 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

（2） 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

（3） 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

（4） 合并对同一表同一条件的多次UPDATE，比如：

UPDATE EMPLOYEE SET FNAME=′ZHUOXI′ WHERE EMP_ID=′790113′

UPDATE EMPLOYEE SET LNAME=′ZHAO WHERE EMP_ID=′790113′

这两个语句应该合并成以下一个语句：

UPDATE EMPLOYEE SET FNAME=′ZHUOXI′，LNAME=′ZHAO′WHERE EMP_ID='790113'

（5） UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

（6） 不要写一些没有意义的查询，比如 SELECT * FROM EMPLOYEE WHERE 1=2。

2.3.3 注意事务和锁

事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这4个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体需要特别注意以下方面：

（1） 事务操作过程要尽量小，能拆分的事务要拆分开来。

（2） 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。

（3） 事务操作过程要按同一顺序访问对象。

（4） 提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

（5） 尽量不要指定锁类型和索引，SQL Server允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL Server优化器选择的锁类型和索引在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更优，但是数据量和数据分布在将来是会变化的。

（6） 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

2.3.4 注意临时表和表变量的用法

在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

（1） 如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

（2） 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

（3） 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

（4） 其他情况下，应该控制临时表和表变量的使用。

（5） 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

2.4 利用SQL Server新增功能和自带工具优化数据库

SQL Server数据库本身自带了一些好的数据库管理工具，熟练运用这些工具能够提高优化工作的效率。如在SQL Server 2005中，SQL Server Profiler和数据库引擎优化顾问是比较常用的工具，SQL Server Profiler能够查找并诊断运行慢的查询，监视SQL Server的性能以优化工作负荷。分析数据库的工作负荷效果后，数据库引擎优化顾问会提供在SQL Server数据库中添加、删除或修改物理设计结构的建议，而且可以使用数据库引擎优化顾问进行探索性分析，在不用首先实现这些结构的情况下评估它们对数据库的影响。

3 数据库优化技术在政务办公系统中具体应用实例

下面，笔者以常见的政务办公系统为例，详细介绍数据库优化的常规步骤。目前，绝大多数政务办公系统都是浏览器/服务器（Browser/Server）模式的应用程序，大量的数据处理和逻辑运算通过数据库和应用服务器进行，要找出数据库服务器的瓶颈，就要从数据库服务器硬件配置情况、服务器性能状态和SQL数据库性能与设计等方面同时着手，一般常用工具为微软Windows NT提供的性能监视器（Performance Monitor）和SQL事件探查器（SQL Server Profiler）。通过使用这两种监测工具，采取各种可能影响服务器和数据库的指标，发现政务办公系统一般主要有以下常见问题：

（1） 数据库日志和操作日志记录表数据量大，数据增长速度过快。主要的业务数据表数据量巨大，个别表数据量达到千万级别。

（2） 数据库查询响应时间慢。通过SQL事件探查器（SQL Server Profiler）对数据库的运行脚本进行几个周期的监剥，以3分钟为一个周期，每次获取5 000条左右SQL语句，一般会找到一些运行时间超过5秒的查询语句，这就是查询中的瓶颈。

（3） 通过SQL事件探查器（SQL Server Profiler）监测，如发现部分查询效率低，查询语句语法可以进行进一步的修改。

（4） 通过性能监视器（Performance Monitor）监视相应硬件的负载Memory：PageFaults/sec计数器，如发现该值偶尔走高，表明当时有线程竞争内存；如在高峰期该值持续很高，表明数据库服务器内存可能是瓶颈。

（5） 通过性能监视器（Performance Monitor）监视磁盘，如发现平均数据读取队列（Avg .Disk Read Queue Length）存在积压，则磁盘每秒读取的数据量（Disk Read Bytes/sec）较大。

针对这些问题，利用数据库优化的原则和技术，我们采取以下措施：

（1） 清除日志，清除个别数据库和表数据的数据。

（2） 定期执行归档和转历史库操作，将历史数据归档，转历史库，减少数据量。

（3） 重新建立索引，利用数据库事件探查器SQL Profiler，找CPU或Duration列值大的语句，根据其查询条件建立相应索引。但是在建立索引时，要注意本文前面提到的建立索引的原则。

（4） 进行索引的优化，建立后台作业程序，定期对整个数据库的所有表重建索引，优化数据库。

（5） 查询语句的优化，对数据库的个别存储过程和查询语句进行优化。

（6） 优化数据库，提高硬件性能，升级操作系统也是很有效的手段之一。

（7） 开启SQL Server 2005 Enterprise Edition支持地址窗口化扩展插件（AWE），该插件开启后可以允许在32位版本的操作系统上使用4 GB以上的物理自存，最多可支持6 GB的物理内存。这样可以有效地利用内存，提高数据库性能。

4 结束语

本文只是简单地从数据库管理和系统管理方面阐述数据库优化的一些原则和方法。实际上，影响SQL Server性能的因素很多，远远不止本文中列出的这些。在Windows NT下，文件系统的选择、网络协议、开启的服务、SQL Server的优先级等选项也不同程度上影响SQL Server的性能。影响性能的因素如此之多，而应用又不尽相同，因此找出一个通用的优化方案几乎是不可能的，在系统开发和维护的过程中必须针对运行的情况，不断加以调整。

主要参考文献

[1] 李聪慧. 试论数据库系统的优化举措[J]. 信息安全与技术，2012（6）.

[2] 魏琦，于林林，宋旭东. 关系数据库查询优化策略研究[J]. 电脑知识与技术，2010（31）.

[3] 杜刚强，姜丹. 数据库系统的优化[J]. 硅谷，2011（22）.

[4] 张水平. 数据库原理及SQL Server应用[M]. 西安：西安交通大学出版社，2008.

[5] 闪四清. 数据库系统原理与应用教程[M]. 北京：清华大学出版社，2008.