谈关系数据库查询优化方法

摘要：以数据库为中心的管理信息系统，查询操作最多。查询操作的优劣直接影响数据库应用程序的性能，因此必须对数据库的查询进行优化。提出了一些如何利用关系数据查询技术实现数据库查询优化的方法。

关键词：关系数据库；查询优化；方法

中图分类号：TP311文献标识码：A 文章编号：1009-3044(2008)16-21188-02

On Optimization Method for Query in Relational Database

YIN Mei-gui

(Heyuan Polytechnic, Heyuan 517000, China)

Abstract: In the database application MIS, query process is the most frequent. Whether query process is good or bad will directly affect the performance of database application system. Therefore, query in database should be optimized. This article suggests some methods of how to realize the optimization by making use of the technology of query in relational database.

Key words: RDBMS; query optimization; methods

1 引言

数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）以及联机分析处理（OLAP）是企业、银行、政府部分最为重要的计算机应用之一。从大多数数据库系统的应用实例来看，查询操作是所有数据库操作中所占据比重最大的操作。当数据库系统积累到一定程度（如税务系统的账户达到上百万甚至上千万条记录），全表扫描一次往往需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，往往能降低查询时间。如何设计数据库，采取什么样的查询方法，提高查询效率，这就是查询优化要解决的问题。

2 合理使用索引

索引是数据库一个常用的数据库对象，优化查询的重要方法是建立索引，也是数据库同时预先将数据分类导入到多表格的方式。在关系数据库的表上建立合适的索引，可以提高数据库数据查询的速度，改善数据库的性能。除了集簇索引，每一索引的使用都以磁盘容量作为代价，当使用一个索引，数据库引擎必须执行两个数据读取，这两个数据读取是数据库记录所必需的，第一个数据被读取到实际数据指针的索引，第二个数据被读入到指针指定的位置。因此创建索引时必须要与实际应用系统的查询需求密切结合，才能达到优化查询的目的。

2.1 建索引的必要性

判断索引必要性的最终标准是判断这些索引是否对数据库的工作效率有所帮助。在实际的应用过程中，应该为优化工作做以下几点准备：

首先观察数据库应用程序中所有的SQL语句，并从中统计出常用且可能对性能有影响的部分语句，然后分析、归纳出作为Where条件子句的字段及其各种组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次，必须了解应用程序，要了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；哪些表中的数据量可能很大；数据量大的表中各个字段的数据分布情况如何等等。对于满足上述条件的这些表，必须重点关注。因为建立在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。

2.2 使用索引的规则

索引的使用要恰到好处，其使用原则如下：

（1）在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。

（2）在主键索引方面，不应有超过25%列成为主键，而普通列很少，这会浪费索引空间。

（3）在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。

（4）在频繁进行排序或分组（即进行group by 或 order by 操作）的属性上建立索引。

（5）在作为最小值等聚集函数的属性上考虑建立索引。

索引的建立、维护和使用都需要付出代价，应合理使用索引。错误的索引不会使数据库性能得到预期的提高，往往还会产生一些负面影响。

3 SQL语句优化

要对查询进行优化，一个简单直接有效的方法是对SQL语句进行调整，减少计算量，提高查询的效率。以下是一些书写SQL的一些经验。

3.1 避免相关子查询

查询嵌套层数每增加一层，查询的效率成几何级的降低。要想提高嵌套语句的执行效率，则应减少嵌套语句的嵌套的层次。所以在实际应用中，若可以用连接查询代替的子查询，则用连接查询实现。

例：查询选修了“3-105”号课程的学生基本信息

用子查询的方法如下所示：

SELECT * FROM student WHERE SNO IN (SELECT sno FROM sc WHERE cno=’3-105’)

改写成连接查询如下：

SELECT student.* FROM student,sc WHERE stuent.sno=sc.sno AND cno=’3-105’

3.2 用UNION替换OR

合理建立索引有助于提高查询效率。有时尽管在所有的检索列上都有索引，但有些形式SELECT查询语句可能不会促使查询优化器使用索引，从而降低查询效率。如果对查询语句进行改写，用UNION替换OR，可以强迫优化器按索引路径处理。如：假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，对以下SQL语句

SELECT 姓名，年龄，月工资 FROM 职工 WHERE 年龄>45 OR 月工资

可替换为：

SELECT 姓名，年龄，月工资 FROM 职工 WHERE 年龄>45

UNION

SELECT 姓名，年龄，月工资 FROM 职工 WHERE月工资

3.3 使用临时表优化查询

在涉及相关查询的某些情形中，构造临时关系可以提高查询效率。如：查询每个部门中月工资最高的“职工号”

SELECT 职工号 FROM 职工 AS e1 WHERE 月工资=(SELECT MAX(月工资)FROM 职工 AS e2 WHERE e1.部门号=e2.部门号)

以上的查询对于外层的职工关系e1中的每一个元组，都要对内层的整个职工关系e2进行检索，因此查询效率不高。可以构建临时关系提高查询效率。

SELECT MAX（月工资） AS 最高工资，部门号 INTO temp FROM职工 GROUP BY 部门号

SELECT 职工号 FROM 职工，temp WHERE 月工资=最高工资 AND 职工.部门号=temp.部门号

3.4 避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分。优化器不使用索引而使用全表扫描。如

SELECT * FROM职工 WHERE 月工资*12>20000

可改为：

SELECT * FROM 职工 WHERE 月工资>20000/12

3.5 谓词的等价变换

由于执行引擎对各种谓词的处理方法不同，把逻辑表达式重写成等价的且效率较高的表达式是提高效率的有效方法，同时也是切实可行的。针对执行引擎对各种谓词执行效率的不同，总结如下谓词转换规则：

1）将BETWEEN转化为AND 连接的谓词

把BETWEEN．．．AND．．．形式改写为用AND连接的两个谓词，效率往往

有一定的提高。

例如：月工资 BETWEEN 1000 AND 2000 改为： 月工资>=1000 AND 月工资

2）避免使用In语句

当查询语句中有In关键词时，优化器采用OR并列条件。

如：职工号IN (‘1001’,’2001’) 改为：职工号=’1001’ OR 职工号=’2001’

4 结束语

查询处理是数据管理系统的核心，而查询优化技术是查询处理的关键技术。查询优化就要抓住关键问题。在数据库的开发和维护过程中，查询优化设计可以提高系统的性能，尤其对于数据量大的数据库系统最为重要。本文提到一些优化方法是根据自己的经验，并查阅了大量的资料。在具体的使用时候要根据实际情况，才能合理制定出良好的优化方法，实现快速、高效的数据查询。

参考文献：

[1] 萨师煊，王珊．数据库系统概论[M].高等教育出版社, 2002.5.

[2] 周志易,江海．数据库系统理论与新技术[M]．北京理工大学出版社,2001.

[3] 陶宏才．数据库原理及设计[M]．清华大学出版社,2004.