简论关系数据库的查询优化

沈海峰

（安徽广播电视大学，安徽合肥 230022）

简论关系数据库的查询优化

沈海峰

（安徽广播电视大学，安徽合肥 230022）

数据库的应用日益广泛，随着数据量的增大，查询效率越来越受人们关注。为了提高查询的效率，在考虑处理器的速度、I／O速度、存储器的容量、操作系统、采取何种的数据库服务系统等方面的同时，对于特定服务器来说查询的效率主要取决于DBA（数据库管理员）所给定的查询语句的优化。

关系数据库；查询优化；分析

随着计算机应用技术的不断普及和发展，数据库系统正越来越多的走进人们的日常生活。在要求查询结果正确无误的同时，人们越来越关心查询的效率问题。影响查询效率的因素很多，诸如处理器的速度、I／O速度、存储器的容量、操作系统、采取何种的数据库服务系统等。但是对于特定服务器来说查询的效率主要取决于DBA（数据库管理员）所给定的查询语句。就目前使用最为广泛的关系数据库而言，查询优化主要是针对SELECT语句的优化。

1 数据库的分类

数据模型是数据库系统的核心和基础，不同的数据库具有不同的功能和数据模型。按照数据模型的特点可以将传统数据库系统分成网状数据库、层次数据库、关系数据库、面向对象数据库4类［1］。网状模型：在网状模型中，关系称为“集”。每个集包括至少两种记录类型，一个相当于层次模型中的父代的主记录，一个相当于层次模型中的子代的成员记录。网状模型的数据库，对于寻找附属于指定的对象的一组记录时，效率非常高。层次模型：IBM为其使用的D／L语言的IMS大型数据库系列产品开发了层次模型，开发层次模型是为了模拟现实生活中各种分层组织。对于描述一种简单的“树”型结构，层次模型非常合适，并且这种模型对于包含大量数据的数据库来说，效率很高。关系模型：关系模型的主要特点是表中的记录由属性之间的关系来进行连接，在保证数据集之间的逻辑关系表达的同时，保持数据集之间的独立性。在关系模型中，数据存储在由行和列组成的表中。使用关系数据库模型可以节省程序员的时间，以便将注意力尽量放在数据库的逻辑框架上，而不需要在物理框架方面花费太多精力。支持关系模型的数据库系统称为关系数据库，它是目前最为成熟、使用最为广泛的数据库类型。目前在互联网上使用的半结构或非结构化数据可以通过一定的转化过程映射到关系数据库。面向对象模型：面向对象的数据模型提供了一种类层次结构。在面向对象数据库模式中，一组类可以形成一个类层次。一个面向对象数据库可能有多个类层次。在一个类层次中，一个类继承其所有超类的全部属性、方法和消息。面向对象的数据库系统在逻辑上和物理上从面向记录上升为面向对象、面向可具有复杂结构的一个逻辑整体。允许用自然的方法，并结合数据抽象机制在结构和行为上对复杂对象建立模型，从而大幅度提高管理效率，降低用户使用复杂性。

2 关系数据库查询优化

2．1 查询优化的必要性

举例来说，如果一个数据库表信息积累到上百万甚至是上千万条记录，全表扫描一次需要数十分钟，甚至数小时；但如果采用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟，由此可见查询优化技术的重要性。

2．2 查询优化的原理

查询优化一般可分为代数优化和物理优化，代数优化是指关系代数表达式的优化；物理优化则是指存取路径和低层操作算法的选择。针对关系数据库，查询优化的总体目标是：选择有效的优化策略，计算给定关系表达式的值，使得查询的执行代价最小。在集中式数据库中，查询的执行代价＝I／O代价＋CPU代价＋内存代价，在粗略计算执行代价时，主要指标是I／O代价，CPU代价与内存代价可不考虑。因此，查询优化的目的主要就在于尽可能地减少I／O操作的次数［2］。

2．3 查询优化的方法

2．3．1 有效利用索引

在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。在频繁进行排序或分组的列上建立索引。在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。假如建立索引不但不会提高查询效率，反而会严重降低更新速度。假如待排序的列有多个，可以在这些列上建立复合索引。下面给出一些通用的规则［4］：1）在查询频率较高或经常用作过滤条件的字段上建立索引；2）在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；3）在外键上建立索引；4）在经常存取的多个列上建立复合索引；5）在不同值较少的字段上不必要建立索引，如性别字段；6）对于经常更新的列避免建立索引；7）不要对一些记录内容比较少的表建立索引。

2．3．2 SQL语句的优化

虽然特定的数据库服务器都会对输入的查询语句进行一定的优化操作，但是查询效率主要取决于DBA所书写的SQL语句的好坏。为确保编写的SQL语句有较好的性能，应考虑以下的优化方法：1）尽量减少使用NOT、＜＞、！＝等操作符因为它们会导致全表扫描。可以把含有NOT、＜＞、！＝等负逻辑的条件表达式转化为意思相当的正逻辑。2）只查询需要的字段，尽量少用“select＊”格式，以减少物理I／O操作。3）用EXISTS替代IN、用NOT EXISTS替代NOT IN：在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。4）IS NULL与IS NOT NULL：不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在WHERE子句中使用is null或is not null的语句优化器是不允许使用索引的。5）避免使用不兼容的数据类型：例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如：SELECT name FROM employee WHERE salary＞60000在这条语句中，如salary字段是money型的，则优化器很难对其进行优化，因为60000是个整型数。我们应当在编程时将整型转化成为货币型，而不要等到运行时转化。6）尽量减少使用联接字段而把所有的条件分列出来用and来进行连接，可以充分的利用在某些字段上已经存在的索引。select work－id from salary where work－salary｜｜work－dept＝’＄2000teacher’：如果把条件分开来写成下面的格式，系统的查询性能可以得到一定的提高。

select work－no from salary where work－salary＝＄2000and work－dept＝’teacher’；

7）避免相关子查询：一个列的标签同时在主查询和WHERE子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。8）避免困难的正规表达式：MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT＊FROM customer WHERE zipcode LIKE“98＿＿＿”即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT＊FROM customer WHERE zipcode＞“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。

2．3．3 视图的使用

利用视图不仅可以提高数据的保密性，方便的设置用户的权限，而且也可以提高数据的精炼性［3］。在DBMS中有着许多不同的角色，他们对数据的要求是不同的，针对不同类别的用户分别建立合适的视图，可以在有效的条件下提高数据的有用性，提高系统对不同用户的查询响应时间。此外用户访问数据库一般要求得到的是最近的数据，比如查询话费，最常用的数据是最近三个月的。因此在许多情况下，可以按照时间对数据库中的数据进行水平分片，把最近一段时间的数据呈现给用户。当用户需要查找“过期”数据时再把相应的块调进来。由于这种情况极少发生，在一定的情况下，可以有效的减少数据量，缩小数据查找范围。使用这种方法要注意分区数据的维护，因此一定要在权衡维护和查询代价的基础上确定是否要使用分片。如果经常要访问全库数据进行综合对比的话，这种方法就不适用。

2．3．4 合理使用存储过程

存储过程由SQL语句和SPL语言的语句组成，创建后转换为可执行代码，作为数据库的一个对象存储在数据库中，存储过程的代码驻留在服务器端，因而执行时不需要将应用程序代码向服务器端传送，可以大大减轻网络负载，加快系统响应时间。同时，由于存储过程已编译为可执行代码，不需要每次执行时进行分析和优化工作，从而减少了预处理所花费的时间，提高了系统的效率。在工程中，我们可以把经常用到的查询动作编写成一个存储过程，并利用参数实现动态查询过程来响应客户的要求；可以实现在服务器端进行批量数据处理等操作；可以使用存储过程作为强制安全性工具；还可以利用系统为用户定义的管理级别存储过程实现数据的管理、配置和监控等。合理使用存储过程可以有效的提高系统效率。

2．4 查询优化须注意的问题

合理以上数据库查询优化方法会从不同方面，不同程度地提高查询效率。但使用优化方法要考虑具体环境和数据状态，以避免不适当地使用带来的负面影响。例如，索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每添加一个索引，数据库就要做更多的工作，过多的索引甚至会导致索引碎片。另外，对于具体的数据结构，优化方法和重心也不尽相同。我们要合理使用优化策略，综合考虑优化方法带来的效率和影响，使数据库的性能得到更好的发挥。

3 结束语

数据库优化是在数据量增大的情况下必不可少的工作，本文是针对关系数据库的优化方法进行的一些分析和总结，其中部分方法也使用于非关系数据库领域，例如，对于面向对象数据库，合理使用索引对于数据库优化也起到很重要的作用，接下来的工作会具体研究关系数据库的优化方法以及对于不同类型的数据库，不同的服务器，对优化工作的具体要求，接着还要研究非关系数据库的优化方法。

［1］ 萨师煊，王珊．数据库系统概论：第3版［M］．北京：高等教育出版社，2000．

［2］ （美）HectorGarcia－Molina，JeffreyD．Ullman，and－JenniferWidomDatabaseSystemImplementation［M］．杨冬青，唐世渭，徐其钧，等，译．北京：机械工业出版社，2001．

［3］ 贾素玲，王强．Oracle数据库基础［M］．北京：清华大学出版社，2007．

［4］ 李建中，等．数据库系统原理：第2版［M］．北京：电子工业出版社，2005．

责任编辑：孟云玲

TP311．132．3

A

1671－8275（2011）01－0007－02

2010－11－12

沈海峰（1977－），男，安徽合肥人，安徽广播电视大学工程师。