数据库查询优化方法的研究与探索

刘玉红，王俊峰

（1.乌鲁木齐市教育招生考试中心 新疆 乌鲁木齐 830002；2.重庆电子工程职业学院 重庆 401331）

关系数据库以其简明的结构 （即维一的数据库结构关系）和严密的理论（关系代数）成为整个数据库领域最重要的组成部分，SQL Server数据库又是关系数据库中应用最广的数据库。他功能强大、操作简便，日益为广大数据库用户所喜爱，越来越多的开发工具提供了与SQL Server的接口。了解和掌握SQL Server的功能，对于一个数据库开发管理人员来说非常必要。SQL Server是一种高性能的大型关系型数据库管理系统，广泛的应用在C/S和B/S体系结构的数据库系统中。评价系统性能优化的标准有：吞吐量、响应时间、并行能力等。文中主要探讨如何优化SQL，以便取得最快的系统响应速度[1]。

数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。在设计阶段进行数据库性能优化的成本最低，收益最大。在成品阶段进行数据库性能优化的成本最高，收益最小。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来只占数据库系统性能提升的40%左右，其余的60%系统性能提升来自对应用程序的优化。许多优化专家认为，对应用程序的优化可以得到80%的系统性能的提升[2]。

1 数据库性能良好的标准

对于同一个系统的实施可以设计出多个数据库模型，这些模型由于性能目标需求差异而不同。事实上，对于同一需求，不同数据库设计人员也会设计出不同的模型，虽然只要它们能提供所需的性能，则就是正确的模型，但是作为数据库设计人员应该尽可能地采用各种技术在最大程度上提高数据库的整体性能。数据库性能的高低一般用两个方面的指标来衡量：响应时间和吞吐量。响应越快，吞吐量越大，数据库性能越好。不过，响应时间和吞吐量并不是都能一起得到改善的。下面是一般大型系统数据库的性能标准[5]：

1）单条记录的更新应当在1 s之内；

2）多条记录的更新不超过10 s；

3）对于于4个表的、数据有一定限度的查询应在5 s内完成；

4）对于有一定限度的查询多表查询应在10 s内完成；

5）整个表的查询时间应在30 s内完成。

以下针对以上目标就影响数据库性能的一些技术予以分析，当然不管什么技术对于数据库性能的优化都必须以提高整体性能为原则，而不是仅仅提高个别查询的速度。

2 数据库性能优化的分析

2.1 影响数据库性能的因素

SQL Server数据库查询速度慢的原因有很多，常见的有以下几种：

1）没有索引或者没有用到索引（这是查询慢最常见的问题，是程序设计的缺陷）

2）I/O吞吐量小，形成了瓶颈效应

3）没有创建计算列导致查询不优化

4）内存不足

5）网络速度慢

6）查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）

7）锁或者死锁（这也是查询慢最常见的问题，是程序设计的缺陷）

8）返回了不必要的行和列

9）查询语句不好，没有优化

2.2 数据库优化分类

1）应用层——大部分性能的获得来自于对SQL应用中查询的优化，这必须是以好的数据库设计为基础的。

2）数据库层——应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据cache。

3）服务器层——在服务器层有许多共享的资源，包括数据高速缓存，存储过程高速缓存，锁，CPU等。

4）设备层——指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的I／O。

5）网络层——指连接用户和SQL Server的网络。

6）硬件层——指可利用的CPU。

7）操作系统层——理想地，SQL Server是一台机器的唯一主要应用，它必须和操作系统以及其他软件如Backup Server或SQL Server Monitor共享处理器、内存以及其他资源。

在大多数情况下面，对应用层进行优化，因为对应用性能的优化是设计和编程人员乐于接受的功能，其结果能被观测及检验。查询的性能是SQL应用的整个性能的一个关键[6]。

3 数据库优化策略研究

3.1 应用层优化基本策略

1）事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取

2）关联一致性保证的策略。对数据查询修改时需要考虑join操作对性能的影响

3）索引可以改善查询性能，但也会增加修改数据的时间

4）为了安全而设立的审计限制了性能

5）远程处理或复制处理能够把决策支持从OLTP机器中分离出来

6）利用存储过程来减少编译时间和网络的利用

7）利用最少量的锁去满足你的应用需要

3.2 数据库层优化基本策略

1）建立优化的备份和恢复方案

2）在设备上分布存储数据

3）审计操作影响性能；仅审计你所需要的

4）日常的维护活动将导致性能的降低和导致用户不能操作数据库表在数据库层上优化选择包括：

①利用事务日志的阈值来自动转储事务日志防止其超出使用空间

②在数据段中用阈值来监视空间的使用③利用分区来加速数据的装入

④对象的定位以避免硬盘的竞争

⑤把重要表和索引放入cache中，保证随时取得

3.3 服务器层优化策略

1）应用的类型——服务器是支持OLTP还是DSS，或者两者都支持

2）所支持的用户数影响优化决策——随着用户数的增加，对资源的竞争会发生改变

3）当用户数和事务数达到一定的数量时复制服务器或其他分布式处理是一个解决的方法

4）优化内存——一个关键的配置参数和其他方面的参数

5）决策是客户端处理还是服务器端处理

6）配置cache的大小和I／O的大小

7）增加多个CPU

8）为空闲时间排定批处理任务和生成报表

9）工作负荷发生改变，重新配置特定参数

10）决定是否可能把DSS移到另一个SQL服务器中设备层

3.4 设备层优化策略

1）主设备、包含用户数据库的设备，用户数据设备，或数据库日志是否要镜像

2）怎样在设备之间分布系统数据库、用户数据库和数据库日志

3）为获得对堆表插入操作的高性能，是否有必要进行分区设备层上优化的选项包括

4）用多个中等大小的设备及多个控制器可能比用少量的大设备有更好的I／O性能

5）分布数据库，表和索引以在不同的设备上进行I／O装载

3.5 网络层优化策略

实际上，SQL Server的所有用户都是通过网络存取他们的数据。网络层上的配置包的大小，以使其与应用的需要相匹配

1）配置子网

2）分隔出繁忙的网络运用

3）创建一个高容量的网络

4）配置多个网络引擎

5）更好地设计应用，限制所需的网络传输

3.6 在硬件层上优化策略

1）增加CPU以适应工作负荷

2）配置调度程序以提高CPU利用率

3）遵循多处理器应用设计指导以减少竞争

4）配置多个数据cache操作系统层

3.7 操作系统层优化策略

1）文件系统——是否被SQL Server独占使用

2）内存管理——精确估算操作系统和其他程序的内存占用

3）CPU的利用——整个系统共有多少处理器可用?有多少分配给SQL Server

4）在文件和原始分区之间选择

5）增加内存

6）把客户操作和批处理移到其他机器上

7）SQL Server利用多个 CPU

4 与应用程序相关的数据库查询优化

查询优化技术在DBMS性能提高方面有着非常重要的地位。实际系统对查询优化具体实现不尽相同，但一般来说可以归纳为4个步骤：

1）将查询转换成某种内部表示，通常是语法树；

2）根据一定的等价变换规则把语法树转换成标准 （优化）技术；

3）选择低层的操作算法；

4）生成查询计划。

绝大多数性能的获得来自于优秀的数据库设计、精确的查询分析和适当的索引。为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据cache，过程cache，系统资源和CPU的竞争。SQL优化的大部分性能的获得来自于对SQL应用中查询的优化[3]。

目前的DBMS大都采用基于代价的优化算法，下面给出的优化策略一般能提高数据库查询效率：选择运算应尽可能先做；应尽可能经常地编译存储过程，使存储过程的查询计划和数据库的数据存放结构保持一致；有效使用索引。查询条件和索引的配合使用，对SQL语句的性能至关重要。下面是两种常见的情况：

其一，如果查询条件中包括索引的第一个列，而且结果列都在索引列中，系统使用匹配索引定位，会定位到索引的页级，这时可从索引页中直接提取结果，不需要使用数据页。其二，如果查询条件中不包括索引的第一个列，而且结果列都在索引列中，系统使用非匹配索引扫描，不扫描数据页，从索引页中直接提取结果。这种情况也不使用数据页。

创建高效率查询，可以充分利用索引的where条件书写格式为“column operator expression”，这里的 operator一般是：=，>，<，>=，<=，is null。 而如果 operator是！=、！，便不能充分利用索引。如果要充分利用索引，在column中就不要包括函数和其他操作。expression必须是常量或可以转化成常量。查询优化器认为，between 相当于“>=”和“<=”，“like‘Ger%”’相当于“>=‘Ger’and<‘Ges”’。 但是“‘like’%ebr”’因为没有给出首字母，就不能转化成这种结果。

在书写SQL语句时，对于表连接的情况，注意尽量少写冗余条件。一般要在SARG（搜索参数）的列上放置一个索引。如果被查询列都包括在索引列中，这种查询叫索引覆盖查询。这种查询效率比较高，应尽量使用这种查询。在做表连接查询时，在外表的连接列上建立索引，可以大大加快速度。而且，查询速度也和表的排列顺序有关，如果行数大的表放在后面，可以提高速度。

5 结束语

数据库的优化是一个系统工程，在数据库实施过程当中影响性能优良的因素很多，而不同项目的应用要求又各不相同，所以要找出所有完全通用的优化技术是不现实的，在数据库开发和维护的过程中，必须针对数据库运行的具体情况加以分析和调整。

[1]陈佳.基于SQL server数据库优化查询的分析[J].企业导报，2010（8）:179-180.CHEN Jia.The analysis of database optimization querying based on SQL Server[J].Enterprise Guide，2010（8）:179-180.

[2]曾实.ORACLE数据库优化技术研究[J].科技信息，2011（27）:84，56.ZENG Shi.The research in ORACLE database optimization technology[J].Technology Information，2011（27）:56-84.

[3]徐鑫涛.浅谈数据库优化 [J].中国科技信息，2008（4）:111-115.XU Xin-tao.About database optimization[J].Chinese Technology Information，2008（4）:111-115.

[4]刘姝.DB2数据库设计及优化技术研究[J].信息安全与技术，2011（11）:38-40 LIU Shu.DB2 database design and optimization technology tesearch[J].Information Safe and Technology，2011 （11）:38-40.

[5]三味工作室编著.SQL Server 7.5管理指南[M].北京：中国水利水电出版社，1998.

[6]Oracle数据库管理与开发[M].大连：东软电子出版社，2009.