数据库的查询优化

摘要：数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）以及联机分析处理(OLAP)是银行、企业、商业、政府等部门最为重要的计算机应用之一。而用户提交的SQL语句是系统优化的基础，如何设计高效合理的查询语句非常重要。根据多年的数据库应用程序开发实际经验，就查询优化问题,谈点实践体会。

关键词：数据库系统；OLTP；查询优化

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

Database Query Optimization

LIPeng

（Liaodong university, Dandong 118001,China）

Abstract:Database system is the key of the management information system ,Based on the database of online transaction processing (OLTP) and online analytical processing (OLAP) are the importantcomputer application of banks, enterprises, business, and government departments. While the SQL statement Submitted by the users is the basis for system optimization. How to design effective and reasonable query is very important. This paper supposes some query optimization experiences based on years of database application development.

Key words:Database system;OLAP;query optimization

1 引言

数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）以及联机分析处理(OLAP)是银行、企业、商业、政府等部门最为重要的计算机应用之一。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大，而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句。如果数据的量积累到一定的程度，比如积累到上百万甚至上千万条记录，全表扫描一次需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，可以使查询时间降为几分钟，由此可见查询优化技术的重要性。

在应用项目的实施中，许多程序员在利用一些前端数据库开发工具（如PowerBuilder、Delphi等）开发数据库应用程序时，只注重用户界面的华丽，并不重视查询语句的效率问题，大量的使用通配符，隐式转换，过分依赖算子IN、BETWEEN等。导致所开发出来的应用系统效率低下，资源浪费严重。许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的SQL语句关系不大，这是错误的。查询计划是用户所提交的SQL语句的集合，查询规划是经过优化处理之后所产生的语句集合。DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。在实际的数据库产品(如Oracle、Sybase等)的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表所得到的信息来估计不同的查询规划的代价，然后选择一个较优的规划。虽然现在的数据库产品在查询优化方面已经做得越来越好，但由用户提交的SQL语句是系统优化的基础，很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效。因此，如何设计高效合理的查询语句就显得非常重要。

2 优化方法

针对以上的问题，结合在数据库应用程序开发实际经验，本文就查询优化问题,谈点实践体会。

2.1 查询优化一

在SELECT/INSERT语句中，必须记述选择表（项目名）。选择表中禁止使用通配符（*）。假如处理A改变表T，在表增加了项目X（or变更or删除）。对同一表T进行访问，与项目X无关的处理B的程序就发生错误。也就是说维护性不好。

差例：

SELECT * FROM epc_tbl1；

INSERT INTO epc_tbl1 VALUES(1，2，3)；

良例：

SELECT cd_col1，cd_col2，cd_col3 FROM tbl1；

INSERT INTO epc_tbl1(cd_col1，cd_col2，cd_col3) VALUES(1，2，3)；

用SELECT语句指定的列，限定使用在这种处理中。

2.2 查询优化二

给SELECTDE的项目名起一个表别名（Alias）。为了减轻ORACLE的解析负荷，SELECT的项目名必须做到以下两点。但在单一表中使用SELECT时除外。用table、cloumn的形式指定项目名。table名中尽量使用短的别名（Alias）。

差例：

SELECT nm_ename，nm_dname FROM epc_emp，epc_dept WHERE ～；

良例：

SELECT e.nm_ename，d.nm_dname FROM epc_emp e，epc_dept d WHERE ～；

2.3 查询优化三

在WHERE语句中不要让函数和算子纠缠在INDEX项目中WHERE语句中的INDEX项目成为运算对象，放在函数中或NULL值进行比较时都不能作为索引检索的对象，而成为发生全表扫描的原因。这样性能会变得很糟，因此要十分注意。如在可能成为影响性能的地方，这些问题不可避免的话，就要重新研究处理要件与表设计的合理性。

差例：

～ WHERE nu_sal_nn * 1.1 > 950；（nu_sal_nn是INDEX项目）

～ WHERE TO_CHAR(dt_hire_nn，'YYYY/MM/DD') = :i_dt ；（dt_hire_nn是INDEX项目）良例：

～ WHERE nu_sal_nn > 950 / 1.1；

～ WHERE dt_hire_nn = TO_DATE(：dt,'YYYY/MM/DD')；

不得已必须使用时要与开发责任人商议。

2.4 查询优化四

不依赖算子IN、BETWEEN。为了减轻ORACLE的解析负荷，尽量禁止使用算子IN、BETWEEN。

差例：～ WHERE nm_ename IN ('SMITH'，'KING'，'JONES')；

～ WHERE nu_sal BETWEEN 2000 AND 3000；

良例：～ WHERE nm_ename = 'SMITH' OR nm_ename = 'KING' OR nm_ename = 'JONES'；

～ WHERE nu_sal >= 2000 AND nu_sal

2.5 查询优化五

避免隐式转换。在WHERE语句中对字符列项目不做单独引证就进行字符列比较的话就是隐式转换，这样有时不能进行索引检索。

差例：SELECT e.nm_ename FROM epc_emp e WHERE nm_job = sales；

良例：SELECT e.nm_ename FROM epc_emp e WHERE nm_job = 'sales'；

但在程序中应不使用有单独引证包围的字符列，而应该提前进行常数定义，再进行与该常数的比较。

2.6 查询优化六

不要无谓地反复选择同一条件的行。一旦把SELECT 的行作为UPDATE/DELETE 对象时，性能发挥作用后可利用ROWID 使ORACLE不做不必要的检索动作。

差例：SELECT nm_ename，nu_sal FROM ～ WHERE no_empno = ：i_empno AND ～；

UPDATE ～ WHERE no_empno = ：i_empno；（但如从代码的易见性、维护性出发设定了有效的索引，也可以采用这种写法）。

良例：SELECT nm_ename, nu_sal，ROWID FROM ～ WHERE nm_empno = ：i_empno AND ～；

UPDATE ～ WHERE ROWID = ：i_rowid；

2.7 查询优化七

不要根据INDEX项的NOT EQUAL进行评判

差例： ～ WHERE no_deptno 30；

良例：～ WHERE no_deptno > 30 OR no_deptno < 30；

不要根据INDEX项的IS NULL进行评判，不要根据INDEX项的 %LIKE% 进行评判。

2.8 查询优化八

不要使用HAVING语句。在下述的差例中可以看出处理选定的全行内容，与此前使用WHERE语句作出的良例相比，处理负荷大了。

差例：～ GROUP BY nm_regionHAVING nm_region 'SYDNEY' AND nm_region 'PERTH'；

良例：～ WHERE nm_region 'SYDNEY' ND nm_region 'PERTH'GROUP BY nm_region；

2.9 查询优化九

从NOT IN 开始也要使用NOT EXISTS。不要进行使用NOT IN 的内部分类合并。

差例：SELECT e.no_empno FROM epc_emp e WHERE e.no_deptno NOT IN(SELECT d.no_deptno FROM epc_dept d WHERE d.nm_loc = 'OSAKA')；

良例：SELECT e.no_empno FROM epc_emp e WHERE NOT EXISTS(SELECT 'X' FROM epc_dept d WHERE d.no_deptno = e.no_deptno AND d.nm_loc = 'OSAKA')；

2.10 查询优化十

从EXISTS开始也要使用JOIN。从副查询开始也要使用JOIN。

差例：SELECT e.nm_ename FROM epc_emp e WHERE EXISTS(SELECT 'X' FROM epc_dept d WHERE d.no_deptno = e.no_deptno AND d.nm_loc = 'OSAKA')；

良例：SELECT e.nm_ename FROM epc_dept d, epc_emp e WHERE e.no_deptno = d.no_deptno AND d.nm_loc = 'OSAKA'；

2.11 查询优化十一

INDEX的顺序与汇总。要对某个表，在不同的检索条件下进行高效的SELECT就必须对各个检索条件设定适当的INDEX。但是如果对一个表设定太多的INDEX，就会出现类似更新的总开销增大等的弊病（标准要设为三种）。按A，B，C项顺序排列的INDEX对下述检索要求有效。对这些检索要求没必要另外设定对应的INDEX。

只以项目A为关键字的检索。

以项目A和B为关键字的检索。

以项目A、B、C为关键字的检索。

但是，INDEX的项目排列顺序不同的话，有效的检索条件也会不同，这一点要注意。（在上述例子中INDEX的项目排列如果是B，A，C的顺序，那么在项目A的单独检索中INDEX就无效）。项目A，B使用的INDEX在以下条件时可以代用项目A，B，C使用的INDEX。

在A，B项目中的选择性高（缩小到5%以下）在A，B项目中缩小的数据绝对量少（50行以下）。

3 结论

以上着重从实现的角度讨论了查询优化,实际上要想根本解决查询优化问题,还需从设计上进行优化,如尽量使用大的内存,数据可适度冗余,库结构优化,对于频繁使用的表建立索引,面向对象的数据库设计方法等等。

参考文献：

[1]萨师煊，王珊.数据库系统概论[M]. 北京. 高等教育出版社，2004:190-215.

[2]王能斌.数据库系统原理[M]. 北京. 电子工业出版社，2000:157-190.

[3]吴胜利，王能斌.面向对象数据库的查询优化[J]. 软件学报，1997(8-2):27-29.

[4]董亚凤，王庆君.数据库的查询优化策略[J]. 计算机系统应用，2004(4):39-41.