数据库查询优化规划

摘要:数据库系统是管理信息系统的核心,是银行、企业、政府等众多部门最为重要的应用。而应用当中,查询操作是重中之重,并且查询操作往往需要花费时间和空间等资源。所以它的效率高低就很大程度上决定了数据库的在整体效率,同时也决定了系统的效率。本文重点围绕数据库的查询优化从需求分析、设计到使用等多方面提出了技术改进和利用。

关键词:数据库;优化;规范;查询

中图分类号:TP311.13 文献标识码:A文章编号:1007-9599 (2010) 05-0000-02

The Optimal Planning of Database Query

Long Zhuqian

(Hainan University College of Information Science and Technology,Haikou571400,China)

Abstract:The database system is the core of management information systems, and also the most important applications in many departments such as banks, enterprises, government and so on. Within the application, the query operation is the most important and it requires time, space and other resources.So its efficiency will largely determine the overall efficiency of the database and the efficiency of the system. This article focuses on the database query optimization and introduces some technological improvements and employment of its demand analysis, design and usage.

Keywords:Database;Optimization;Specification;Query

随着数据库技术的发展,越来越多的数据库应用系统和信息管理系统被开发、使用。而很多数据库应用系统和信息管理系统中,查询功能的要求是必不可少的,也是所占的比例最大的。数据库的查询功能优化规划好的话,整个系统的效率会有很大的提高,由此可见查询优化规划的重要性。要想整个数据库的查询得到很大程度的优化,就必须要从需求、设计到使用都要进行规划和技术改进。

一、详细的需求分析是优化的基础

数据库设计是从系统的需求出发,结合软硬件相关要求,设计合理能否符合系统需要功能的数据的集合。数据库设计中最基本的是数据库模式的设计。但是,设计一个完善的比较优秀的数据库系统往往是一个反复且周期性的过程。

二利用规范化是优化的关键

在数据库的设计周期中,需要进行逻辑结构设计,这个时候必须进行关系模式的规范化来达到优化数据库的目的。所谓关系规范化就是按统一标准对关系进行优化,从而最大程度上能消除关系数据库中的数据冗余、添加、删除和修改等操作异常以提高关系的质量,不好的关系操作时会有3个问题:1.冗余度大;2.插入异常;3.删除异常。

规范化理论认为,关系中的各属性是相互关联的,他们互相依赖、互相制约,构成一个结构严谨的整体。按照属性间相关的关系,可大概分类为函数依赖、多值依赖和连接依赖。其中按照函数依赖又可分为部分依赖、完全依赖和传递依赖,根据这三类函数依赖等可得到规范化等级主要有5种,即第1范式(1NF),第2范式(2NF),第3范式(3NF),BC范式(BCNF)和第4范式(4NF),满足这些范式条件的关系模式可在不同程度上避免冗余、插入和更新异常问题。

那么如何进行规范化呢?具体做法是:确定数据依赖,按照数据依赖的理论,逐一分析这组关系模式,确定他们属于第几范式,进行模式分解,逐步消除非主属性对码的部分依赖和传递依赖。在分解过程当中必须遵守以下2条原则:

1.无损分解原则无损分解就是在关系分解过程中,既不丢失数据也不增加数据,同时还能保持原有的函数依赖。

2.相互独立原则所谓独立是指分解后的新关系之间相互独立,对一个关系内容的修改不应该影响到另一关系。

三、规范化的收尾工作

关系分解必须从实际出发,并不是范式等级越高,分解得越细就越好。若把关系分解得过于琐碎,虽然对于消除数据冗余和更新异常等有好处,但在进行查询操作时往往又需要进行链接,降低查询效率。相反的若是范式等级太低,虽有利于查询,但会造成相关数据冗余、更新异常等情况。因此我们应该根据实际应用进行相关范式的设计。

(四)查询使用中的优化技术

应用项目的实施中,许多程序员在利用一些前端数据库开发工具开发数据库应用程序时,只注重用户界面的华丽,并不重视查询语句的效率问题,导致所开发出来的应用系统效率低下,资源浪费严重。因此,如何设计高效合理的查询语句就显得非常重要。

实际应用中许多程序员认为查询优化是DBMS的任务,与程序员所编写的SQL语句关系不大,这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。查询计划是用户所提交的SQL语句的集合,查询规划是经过优化处理之后所产生的语句集合。在实际的数据库产品的高版本中都是采用基于代价的优化方法,这种优化能根据从系统字典表所得到的信息来估计不同的查询规划的代价,然后选择一个较优的规划。虽然现在的数据库产品在查询优化方面已经做得越来越好,但由用户提交的SQL语句是系统优化的基础,很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效,因此用户所写语句的优劣至关重要。下面重点说明改善用户查询计划的常用解决方案。

1.合理使用索引

索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处,其使用原则如下:

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

在频繁进行排序或分组的列上建立索引。

在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。

2.避免或简化排序

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。

3.消除对大型表行数据的顺序存取

在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。还可以使用并集来避免顺序存取。

4.避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。

5.避免困难的正规表达式

LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”

即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode >“98000”,在执行查询时就会利用索引来查询,显然会大大提高速度。

6.使用临时表加速查询

把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。

7.尽量不要使用or

使用or会引起全表扫描,将大大降低查询效率。

8.字段提取要按照“需多少、提多少”的原则

避免“select *”,尽量使用“select 字段1,字段2,字段3........”。实践证明:每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断

五、结语

数据库查询优化是一项综合性工作,受到各种各样因素的制约,有些要求往往是彼此矛盾的。因此设计者必须根据实际情况,综合应用上述技术,在基本合理的总体设计的基础上,对数据库查询做一些优化调整,力求最大限度地提高操作效率,减少异常等,满足用户各种各样的要求,实现数据库的查询优化设计。

参考文献:

[1]萨师煊.数据库系统概论[M].北京:高等教育出版社,2000

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

[3]李芳.关系数据库设计的优化技术[J].现代电子技术,2003,9

[4]袁长河.Sybase SQLserver性能优化技术初探[J].计算机系统应用,2000,1