数据库查询优化研究

2025-01-17

数据库查询优化研究（共9篇）

数据库查询优化研究篇1

数据库系统作为管理信息系统的核心, 各种基于数据库的联机事务处理以及联机分析处理正慢慢地转变成为计算机应用的最为重要的部分, 根据以往大量的应用实例来看, 在数据库的各种操作中, 查询操作所占的比重最大, 而在查询操作中基于SELECT语句在SQL语句中又是代价最大的语句。

一、基于索引的优化

数据库的优化方法多种多样, 不同的方法对提高数据库查询效率也不相同。

索引作为数据库中的重要数据结构, 它的根本目的就是为了提高查询的效率。而优化查询的重要方法就是建立索引, 建立适合关系数据库系统的索引, 这样就可以避免表扫描, 并减少了因为查询而造成的输入输出开销, 有效提高数据的查询速度, 优化了数据库性能。

1.判断并建立必要的索引对所要创建的索引进行正确的判断, 使所创建的索引对数据库的工作效率提高有所帮助。为了实现这一点, 我们应做到以下要求:在熟记数据库程序中的相关SQL语句的前提下, 统计出常用且对性能有影响的语句;判断数据库系统中哪些表的哪些字段要建立索引。 2.对索引使用的一些规则索引的使用在一些大型数据库系统中会经常使用到, 这样可以有效的提高数据库性能, 使数据库的访问速度得到提高。但索引的使用要恰倒好处, 所以我们在使用索引时应遵守使用原则:建立索引可以提高数据库的查询速度, 但索引过多, 不但不能实现优化查询, 反而会影响到数据库的整体性能。索引作为数据库中实际存在的对象, 每个索引都要占用一定的物理空间。所以对于索引的建立要考虑到物理空间容量, 以及所建立索引的必要性和实用性。 3.合理的索引对SQL语句的意义索引建立之后, 还要确保其得到了真正的使用, 发挥了其应有的作用。首先, 可以通过SQL语句查询来确定所建立的索引是否得到了使用, 找出没有使用到的索引。其次, 索引得到使用以后, 是否得到了预期的效果, 对数据库的性能是否实现了真正意义上的提高, 只有合理的索引才能真正提高数据库的性能。

二、优化SQL语句

在使用索引时可以有效的提高查询速度, 但如果SQL语句使用不恰当的话, 所建立的索引就不能发挥其作用。所以我们应该做到不但会写SQL, 还要写出性能优良的SQL语句。下面, 就如何优化引用例子进行说明。首先, 在进行查询时, 返回的值应该是查询所需要的。在查询中应该尽量减少对数据库中的表的访问行数, 使查询的结果范围最小, 这就意味着在查询时, 不能过多的使用通配符, 如:select*from table1语句, 而应该做到最小化查询范围, 要查询几行几列就选择几行几列, 如:select col1 from table1;多数情况下, 用户并不需要查询到的所有数据, 而只是部分或靠前的数据时, 我们也可以通过SQL语句来进行限制查询的结果, 如:select top 50 col1 from table1。其次, 对于一些特殊的SQL语句, 在使用时应正确选择。我们用一组例子来说明, 如:EXISTS, NOT EXISTS。

语句一:select sum (t1.c1) from t1 where ( (select count (*) from t2 where t2.c2=t1.c2) >0)

语句二:select sum (t1.c1) from t1 where exists (select*from t2 where t2.c2=t1.c1)

两个语句所得到的结果相同, 但, 语句二的效率要远高于语句一, 因为语句一在查询中产生了大量的索引扫描。在对数据库查询时, 所使用的语句多种多样, 但选择恰当的语句能够有效的提高查询效率。

最后, WHERE子句在使用时应该注意的问题。

在WHERE子句中可以使用exist 和not exist代替in和not in。应该尽量避免使用in, not in, or 或者having。可以使用表链接代替 exist。Having可以用where代替, 如果无法代替可以分两步处理。

三、其他优化方法

数据库的查询优化方法不仅仅是索引和SQL语句的优化, 其他方法的合理使用同样也能很好的对数据库查询功能起到优化作用。如以下几种方法。

1.避免或简化排序应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时, 优化器就避免了排序的步骤。 2.避免相关子查询如果在主查询和WHERE子句中的查询中同时出现了一个列的标签, 这样就会使主查询的列值改变后, 子查询也必须重新进行一次查询。因为查询的嵌套层次越多, 查询的效率就会降低, 所以我们应当避免子查询。否则, 就要在查询的过程中过滤掉尽可能多的。 3.创建使用临时表在表的一个子集进行排序并创建临时表, 也能实现加速查询。在一些情况下这样可以避免多重排序操作。但所创建的临时表的行要比主表的行少, 其物理顺序就是所要求的顺序, 这样就减少了输入和输出, 降低工作量, 提高了效率, 而且临时表的创建并不会反映主表的修改。 4.用排序来取代非顺序存取磁盘存取臂的来回移动使得非顺序磁盘存取变成了最慢的操作。但是在SQL语句中这个现象被隐藏了, 这样就使得查询中进行了大量的非顺序页查询, 降低了查询速度, 对于这个现象还没有很好的解决方法, 只能依赖于数据库的排序能力来替代非顺序的存取。

四、结论

对于数据库的优化, 我们要抓住关键问题, 提出改善查询效率, 这样才能真正使数据库服务得到根本提高。本文在对数据库查询优化的方法上, 进行了分析, 提出了部分见解, 有效的提高数据库查询效率。

数据库查询优化研究篇2

一、利用EXPLAIN关键字来评估查询语句中的缺陷

如下图所示，现在笔者在数据库中执行了一条简单的Select查询语句，从一个表格中查询所有信息。现在数据库管理员想知道，数据库在执行这条语句时，做了哪些工作?或者说想知道，这条查询语句有没有进一步优化的可能。如果要了解这个信息的话，就可以在查询语句中加入一个Explain关键字。

通过Select查询语句可以从数据库中查询某个表中的数据。但是这条语句执行的效率如何?是否还有优化的余地?这些内容是无法从上面这个简单的查询语句中获得的。为了了解更加详细的信息，需要加入Explain关键字。如下图所示：加入Explain关键字之后，系统并没有查询出表格中的数据，而只是显示了查询过程中的一些信息。这些信息对于我们后续进行数据库查询优化非常有帮助。从上面这个信息中我们可以看出，用户只是进行来一个简单的查询。在这个查询中，没有用到任何索引、关键字等内容，也没有用到Where条件语句。为此这个查询语句并不是很合理。虽然其可以找到最后正确的结果，不过其查询效率可能并不是很明显。为此数据库专家可以根据上面显示的信息来进行优化。如果我们现在在查询语句中加入一条Where语句，那么又会有什么样的结果呢?如下图所示。此时在最后一个Extra字段中，系统就会显示已经使用了Where语句。在进行数据库优化中，我们需要抓住结果中的NULL字段或者空白内容的字段。这些地方往往是我们进行优化的重点。如上图所示，我们可以给这条Select语句进行如下的优化：在表中设置关键字或者索引，来提高查询的效率。

二、数据比较时采用相同类型的列以提高查询效率

在数据查询时，有时候会在条件语句中加入判断的条件。如现在有两张表：用户基本信息表和用户权限表，两者通过用户编号作为关联。现在需要查询出每个用户对应什么样的权限，此时就要通过用户编号作为查询条件来进行查询。现在假设用户基本信息表中的用户编号字段为CHAR类型的;而用户权限表中的用户编号是VARCHAR类型的。这两个数据类型虽然都是字符型，但是不是同一种类型。现在对这连个表执行关联查询，其查询的效率如何呢?首先需要确定的一点是，虽然他们两个是不同类型的字符型数据，不过是相互兼容的。最后仍然可以得到正确的结果。明确了这一点之后，我们再来考虑，能否对这个查询语句进行优化呢?

我们再假设一下。现在这两个表的用户编号的数据类型都是CHAR，

现在再对这两个表进行关联查询，得到的结果是否相同呢?我们测试的结果是，查询的结果是相同的，但是其所花费的时间是不同的。而且随着数据量的增加，两个查询所相差的时间会越来越长。从这里可以知道，虽然这两个查询语句是等价的，但是其查询的效率不同。

在MySQL数据库中，虽然相互兼容的数据类型可以进行相互比较。但是其查询的效率会有所影响。从提高数据库查询效率的角度出发，笔者建议在查询条件语句中最好比较具有相同类型的列。在同等条件下，相同的列类型比不同类型的列能够提供更好的性能。特别是在数据量比较多的数据库中，这尤其重要。

不过这个优化需要涉及到数据表的列类型。为此在数据表进行设计时，就需要考虑这一点。如针对上面这个案例，我们可以在两个表中专门设置一个用户ID列。可以使用整数类型的序列，让系统进行自动编号。然后在查询时通过这个用户ID列来进行比较，而不是通过原来的用户编号列进行比较。相对来说，这么操作查询的效率会更高。

三、在Like关键字的起始处通配符要谨慎使用

在实际工作中，笔者发现不少数据库管理员有一个不好的习惯。他们在使用Like等关键字时，通配符会乱用。如现在用户需要查找所有以“LOOK”为前缀的产品信息。用户在查询时，会习惯性的使用下面的语句进行查询：like “%LOOK%”。这个条件语句会查询出所有品名中有LOOK这个单词的纪录，而不是查询出以LOOK为前缀的产品信息。

虽然最终的结果可能是相同的。但是两者的查询效率不同。其实这很大一部分原因是客户端应用程序设计不当所造成的。如在客户端应用程序设计时，系统会默认显示一个%符号。如下图所示。

这么设计的本意是好的，让系统能够支持模糊查询。但是用户在实际操作起来，就可以有问题。如用户在查询时，不会在%号前面输入LOOK这个单词，而是在%后面输入LOOK这个单词。因为在查询时，光标会自动定位到%号后面。通常情况下，用户在输入时不会再去调整光标的位置。此时就出现了上面所说的这种情况。

为此笔者建议，在Like等关键字后面如果需要用到通配符的话，要非常的谨慎。特别是从大量数据中查找纪录时，这个通配符的位置一定要用对地方。在起始处能够不同通配符的话，尽量不要使用通配符。

四、尽量使用其它形式来代替Like关键字

上面提到在使用Like关键字时需要注意通配符的位置。其实从查询效率来看，我们不仅需要注意通配符的位置，而且能够不用Like关键字最好就不用。其实在SQL语句中，可以利用其他方式来代替Like关键字。如现在有一个产品表，其编号为6位。现在需要查询以9开头的产品编号。这该怎么操作呢?

一是可以通过使用Like关键字，如LIKE “9%”。注意这个通配符的位置。这个条件语句可以查到所需要的结果。但是从性能优化的角度看，这条语句不是很好的处理方式。我们还可以通过一些折中的方式来实现。

数据库查询优化研究篇3

关键词：分布式数据库；查询策略；SDD-1算法

中图分类号：TP311文献标识码：A文章编号：1007-9599 (2010) 16-0000-01

The Distributed Database Query Optimization Strategy Research on SDD-1 Algorithm

Li Ertao

(Economics&Management College of Anhui,Hefei230059,China)

Abstract:Distributed database system has dealt with and increase a lot of new content and complexity because of distribution and redundancy for data distributed to inquire,so the query strategy seems particularly important.This text introduced the characteristics of query strategy which based on the SDD-1 algorithm and discuss the defeat and improving methods.

Keywords:Distributed database;Query strategy;SDD-1 algorithm

分布式数据库系统是数据库系统与计算机网络系统结合的产物，具有数据独立性、集中与自制相结合的控制机制、存在适当的数据冗余度、事务管理的分布性等特点。在分布式数据库系统中，数据独立性除了数据的逻辑独立性与物理独立性外，还有数据分布透明性。数据分布透明性指用户不必关心数据是如何被逻辑分片的（数据分片透明性），不必关心数据及其片段是否被复制及复制副本的个数（数据复制透明性），也不必关心数据及其片段的物理位置分布的细节（数据位置透明性），同时也不必关心局部场地上数据库支持哪种数据模型。有了分布透明性，用户的查询程序书写起来就如同数据没有分布一样，使系统使用起来更简单、有效。

一、分布式查询策略的基本特点

在分布式查询处理技术中，查询策略的基本类型通常包括两类：针对查询执行代价的策略和针对查询响应时间的策略。针对查询执行时间代价进行优化策略的目标是使查询执行所使用的系统资源尽量地少，从而降低整个系统开销。针对查询响应时间优化策略的目标是尽量减少查询的响应时间，而不计较系统资源的耗费。

查询优化有两种基本方法：第一是查询转化，即以不同的顺序执行关系操作，如连接和投影操作；第二是查询映射，即使用一系列高效的算法来存取各种设备和实现关系操作。即查询映射是针对关系的存取方法和操作的执行算法进行决策，而查询转化则是针对操作执行的顺序及不同站点之间数据流动的顺序进行决策。

二、SDD-1算法

SDD-1算法由两部分组成：基本算法和后优化。基本算法是根据评估所缩减程序的费用，效率，收益估算等几个因素，给出全部的半连接缩减程序集，决定一个最有益的执行策略。主要包括三个基本步骤：（1）初始化：已准备好从查询数转换的优化模型，且所有关系已完成局部缩減。（2）优化：根据初始条件，构造可能的半连接缩减程序；按半连接缩减程序的静态特性表，分别计算其代价和产生的益处，从其中选取一个半连接程序，设为S；以S完成缩减以后，又用重新产生的一组新的静态特性表再进行计算，再从其中选取一个合适的半连接程序，但每一个都只做一次；循环下去，直到没有半连接缩减程序为止。（3）结束：以最后一次缩减关系的静态特性表为基础，进行费用计算，选择场地。后优化是将基本算法得到的解进行修正，已得到更合理的执行策略。包括两种修正，一种是如果最后一次半连接程序缩减关系的所在场地恰好是被选中的执行场地，则最后一次半连接可以取消。另一种修正是在基本算法的流程图进行修正，因为某一个半连接缩减程序的代价可能很高，就必须修正半连接的操作序。

算法：SDD-l-QOA

input：QG：query graph with n relations；statistics for each relation

output：ES：execution strategy

begin

Es←local-operations（QG）

modify statistics to reflect the effect of local processing

Bs<-∮

For each semijion SJ in QG do

if cost（SJ）

BS<-BS∪SJ

end-if

end-for

while Bs≠∮ do

begin

SJ←most-beneficial（BS）{SJ：semijion with max（benefit—cost）}

BS←BS-SJ {remove SJ from BS}

ES←ES+SJ {append SJ to e ecutJon strategy}

但是，SDD-1算法存在一个严重问题，那就是它的算法的复杂性。当元组数目很大时，进行查询搜索的代价迅速增加，使系统无法承受。为此，我们在此基础上对它进行改进，降低它的时间复杂度。我们提出的改进算法描述如下：假设已经建立执行策略ES，有益半连接存储表BS表。

（一）置ES为空，读取并行参数值P；

（二）计算所有的有益半连接并加入BS表中；

（三）选择最有益半连接x和比x小P范围内的有益半连接，若这些有益半连接涉及到的关系有重复者，则去掉其中较小的有益半连接，将最终得到的有益半连接从BS表中删除并加入ES中；

（四）判断ES是否包含所有有益半连接，是则输出此执行策略，否则执行下一步；

（五）调整统计数据；

（六）转到第（二）步。

三、结束语

经过实验验证，采用改进的SDD-1算法对多关系查询进行优化后，不但减少了通信代价，而且提高了查询执行的并行能力。所以当查询涉及到的连接个数较多时，应用改进的SDD-1算法，通过在优化过程中添加并行参数，能很好的提高了SDD-1算法的并行执行能力。

参考文献：

[1]解飞,唐培丽,魏宁.基于数据立方体的关联规则挖掘方法研究[J].气象水文海洋仪器,2008,1

关系数据库查询优化策略研究篇4

目前, 随着信息化的不断发展, 数据库作为信息管理系统的后台, 广泛应用于各企事业单位[1], 主要负责信息的处理和存储, 有着举足轻重的地位。据调查, 大部分数据库在应用过程中常会出现延迟、等待处理时间长等问题, 这都可以归结为数据库的效率问题。在数据库对信息的处理中, 以查询所占的比例最大, 那么查询的效率将是数据库信息处理效率的关键所在。良好的查询优化策略, 将大幅提高数据库效率, 例如一些商业海量数据库, 某些原本需要执行数十分钟, 甚至1个小时的查询, 在适当的优化策略下, 执行时间可缩短为几分钟。如何根据实际情况制定适合的查询优化策略, 也正是本文的切入点。本文将通过分析数据库查询效率低的原因, 结合数据库理论知识、数据库应用的经验, 阐述数据库查询优化的策略。

2 影响数据库查询效率的因素分析

影响数据库查询效率的因素有很多, 可简单归纳为以下方面:

2.1 硬件性能方面

这里的硬件主要指数据库服务器, 由一台或多台主机和DBMS (数据库系统管理软件) 共同构成。“工欲善其事, 必先利其器”, 数据库系统服务器的硬件等级, 服务器的相关设置, 直接影响数据库的查询效率。

2.2 查询方案方面

现代的关系数据库的查询以关系代数为理论基础, SQL (Structured Query Language) 语言为手段完成和数据库的通信。用户编写SQL语句, DBMS (数据库系统管理软件) 编译并结合索引等数据库对象执行SQL语句, 返回查询结果实现用户对数据库的查询操作[2]。高效的查询方案可以将数据库的查询效率提高10倍以上, 海量数据更为明显。很多程序员、数据库操作员却并未认识到这点, 以为写出SQL语句能实现查询功能就可以了。

3 查询优化策略研究

数据库的查询优化有多种途径, 这里根据影响数据库查询效率的因素, 分别从硬件性能方面和查询方案两个角度来谈数据库查询优化策略。

3.1 基于硬件性能的查询优化

3.1.1 服务器硬件优化

提高服务器的工作效率, 升级硬件是最直接、有效的一个方法。针对数据库服务器的特点, 硬件升级时应考虑下列内容:

1) 加大内存

对于数据库服务器性能来说, 内存是最重要的因素。因为访问内存中的数据要比访问磁盘中的快, 所以可以通过加大内存, 把更多的数据保存在内存缓冲区, 从而减少磁盘的I/O, 提高数据库效率。

2) 配置多处理器

多处理器数据库系统中, 每个处理器可以运行一个事务, 实现多个事务真正意义上的并行运行, 也可以实现更加复杂的并发机制, 如果不考虑资金的问题是最理想的并发方式。

3) 配置高效硬盘

从磁盘读取数据的时候, 高转速的磁盘可以减少等待时间, 提高相应速度。另外, 可采用磁盘陈列, 优化磁盘的I/O分配使之均衡, 减少资源竞争, 达到提高数据库效率的目的。

3.1.2 服务器设置优化

1) 调整操作系统

服务器的运行离不开操作系统, 操作系统的性能直接影响着服务器的性能。

可通过操作系统规划使数据库服务器占有系统资源最大化, 设置合适虚拟内存的比例, 设置服务器进程优先级等手段提高数据库效率。

2) 调整服务器的缓冲区

数据在内存中的访问速度要高于硬盘中的访问速度, 所以可以通过相应参数设置服务器的缓冲区容量, 让数据和索引更长时间停留在内存中, 减少硬盘I/O, 提高数据库效率, 下面以Oracle为例, 介绍缓冲区的调整。

系统全局域SGA (System Global Area) 是Oracle数据库存放系统信息的内存区域, 是实例Oracle Instance的基本组成部分, 在实例启动时分配。它主要由数据高速缓冲区、日志缓冲区、共享池三部分组成。SGA存在的意义就是把数据放在内存中快速存取, 因此SGA要尽可能都进入内存但不要超过内存的容量, 否则会发生磁盘和内存的页面交换, 反而影响了效率。可以通过语句SHOW SGA

查看SGA内存分配的结构和大小。

数据高速缓冲区的大小应该根据该缓冲区的命中率来调整, 命中率高说明该缓冲区缓存够用, 性能良好。命中率是通过下面公式来计算的:高缓区合中率=1- (physical reads/ (db blockgets+consistentgets) )

公式中涉及的参数是Oracle运行时统计的数据存取情况, 放在V$SYSSTAT表中。physical reads是磁盘文件存取总数, db block gets是数据请求总数, consistent gets是内存缓冲区能够满足的请求总数。可以通过下面的语句查询这几个参数, 如图1。如果命中率低于85%, 将影响数据存取的效率, 可以通过增大初始化参数db_block_buffers的值 (它的最大值为65535) , 来增加高速缓冲区的容量。

共享池由库缓冲区和数据字典缓冲区组成, 它们的大小应根据缓冲区的不命中率来调整。库缓冲区的不命中率应该接近于0, 数据字典缓冲区的不命中率应该低于10%, 否则会影响数据存取效率。它们的不命中率公式如下:

可通过下面语句查询相应的参数, 如图2。如果不命中率过高, 可以通过字典区不合中率=错误请求数sum (getmisses) /相应项请求数sum (gets) 调整初始化参数shared_pool_size来重新调整分配给共享池的内存容量。

3.2 基于查询方案的优化

3.2.1 关系代数的等价变换优化

等价关系代数优化是查询优化的理论基础和前提。关系代数表达式等价主要是指用同样的关系实例代替两个表达式中相应关系时所得到的结果是一样的, 也就是指得到相同的属性集和相同的元组集[3]。等价关系代数优化就是找出等价的关系代数表达式, 从中选出执行效率最高的一个, 并以此为基础写出相应的SQL语句。

常用的等价关系代数规则如表1所示。

注释:E1、E2、E3是关系代数表达式, A1、A2...An, B1、B2...Bm是属性, F、F1、F2、F3是条件表达式

从查询优化的角度分析, 规则1、2的等价变换不会对查询效率产生影响, 而规则3-10的等价变换的查询效率则有所不同。经总结, 等价关系代数优化应遵循以下原则:

1) 根据情况尽量将选择、投影操作提前, 缩小关系的范围

2) 根据情况尽量将同一关系上的选择、投影操作一起进行, 避免关系的重复扫描。

3) 根据情况将选择或投影和连接结合起来进行, 以减少关系范围

3.2.2 合理的创建索引

索引是数据库中重要的数据结构, 它就像书的目录一样可以辅助快捷的访问数据库中的数据, 而不需要对数据库表进行遍历。索引的使用是由DBMS (数据库管理系统) 操作的, 作为数据库的设计者或操作人员, 关注的应该是如何创建合理的索引, 合理的索引设计要建立在对各种查询的分析和预测上。索引创建的不合理, 会有相反的效果。通常创建索引时, 要遵循以下原则:

1) 在频繁操作的列上建立索引。这样的列指的是多表查询时的表连接属性, 分组GROUP BY, 排序ORDER BY的依据属性。常用于分组、排序的列可以考虑建立聚集索引 (clustered index) 。

2) 在条件表达式中经常用到的取值范围大的列上建立检索, 取值范围低的列上不要建立索引。如属性“婚否”的取值范围只有“是”与“否”两个不同值, 就没必要建立索引。建立索引反而会降低更新速度。

3) 如果经常多列同时存取, 并且每一列都含有重复的值, 可以考虑建立组合索引 (compound index) ;

4) 对于不经常作为关键字查询的列则少创建或者不创建索引:对于频繁删改的表, 尽量少创建索引。

3.2.3 合理使用存储过程

存储过程 (Stored Procedure) 是一组具有特定功能的由流控制和SQL语句编写的程序块, 经编译和优化后存储在数据库服务器中, 具有灵活、性能高的特点。在数据库的应用中, 可以将数据库中常用的复杂查询操作写在存储过程中, 查询的时候调用相应过程就可以了。常规的SQL语句数据库系统是要编译后才可以执行的, 存储过程是经过编译和优化的, 用户在调用它进行查询的时候节省了编译时间;另外, 存储过程是存放在数据库中的, 这样查询的时候直接调用减少了系统的I/O, 提高了查询效率。可能存储过程每一次调用节省的时间很少, 但是如果对于多用户访问的海量数据库, 将是一个很大的性能提升。

3.2.4 合理编写SQL语句, 避免对表的顺序遍历

1) 尽量避免在SQL语句中对属性进行NULL值的判断, 防止DBMS将放弃索引进行全表遍历。如下面的查询SELECT NAME FROM EMP WHERE SAL IS NULL可进行如下优化, 将EMP表的SAL属性默认值设置为0, 让该属性没有NULL值, 查询语句变为SELECT NAME FROM EMP WHERE SAL=0

2) 尽量避免在SQL语句中使用OR作为查询条件的连接词, 防止DBMS跳过索引全表遍历。如下面的查询SELECT NAMEFROM EMP WHERE SAL=1000 OR

SAL=2000可以进行如下优化, 用UNION ALL代替OR, 查询语句变为SELECT NAME FROM EMP WHERE SAL=1000 UNIONALL SELECT NAME FROM EMP WHERE SAL=2000

3) 尽量避免在SQL语句中使用IN、<>或NOT关键字, 它们都会使DBMS放弃索引, 全表遍历。IN谓词可以用EXISTS谓词替代, 因为IN谓词引导的子查询返回是一个结果集, 而EXIXTS谓词引导的子查询返回的是’TRUE’或‘FALSE’, 然后子查询的结果再作为条件参与父查询, 显然EXISTS谓词引导子查询的效率会更高。如下面的查询SELECT SNAME FROM S WHERE SNO IN

(SELECT SNO FROM SC WHERE GRADE>80) 可优化为SELECT SNAME FROM S WHERE

EXISTS (SELECT 1 FROM SC WHERE SC.SNO=S.SNO AND GRADE>80) 。

4) 尽量避免在WHERE字句中对属性进行公式、函数操作, 如下面的查询SELECT NAME FROM EMP WHERE SAL*3=3000可以优化为SELECT NAME FROM EMP WHERE SAL=3000/3;SELECT JOB FROM EMP WHERE substring (name, 1, 3) ='smt'可以优化为SELECT JOB FROM EMP WHERE NAME LIKE‘smt%’

5) 尽量避免对表进行全属性SELECT*查询, 查询要精确写每个属性, 全属性也不例外。

3.2.5 合理运用查询优化器

查询优化器是数据库系统的一个组件, 简单的说, 它会根据已有的索引和查询信息的统计, 对SQL语句进行优化, 得到它认为性能最好的, 查询最高效的执行方案。通常, 查询优化器所作的工作用户看不到, 默认执行。但是, 有些时候我们也可以根据实际情况通过参数设置查询优化器, 让它更高效的运行。

以Oracle为例, 查询优化器认为性能最优的标准有两点, 一是Best throughput, 即从SQL开始执行到结束的时间最短, 二是Bes Response time, 即从SQL开始执行到返回第一条记录的时间最短。两个标准可以根据我们的需要设置参数Optimizer_mode来确定它们的优先级。设置参数为All_Rows表示希望以Best throughput的方式来优化, 设置参数为First_Rows_N表示希望以Best Response time的方式来优化。

4 结论

在大型数据库广泛应用的时代, 数据库查询优化已成为一门关键技术, 运用合理, 可以大幅提升数据库系统的效率[5]。提升硬件方面的优化是要衡量开销的, 使用优化器等软件优化仅是常规的优化, 在实际的应用中, 我们最需要关注和探索的是发掘现有服务器的潜能, 对数据查询方案作有效的分析并整理出高效率的数据查询方案。

参考文献

[1]杨小艳.Oracle数据库查询优化方法研究[J].计算机与现代化, 2008 (4) :4-7.

[2]李俊民.精通SQL-结构化查询语言详解[M].北京:人民邮电出版社.2008 (8) .

[3]梁志宏等.等价关系代数查询优化方法的研究[J].山西师范大学学报, 2004 (18) :34-38.

[4]盖国强等.Oracle数据库性能优化[M].北京:人民邮电出版社, 2005.

并行数据库系统的查询优化研究篇5

现在的主流并行数据库都是基于无共享架构, 在制定查询计划阶段, 是采用完全相同的模式, 不同之处是查询优化的策略和路径生成过程中所考虑的代价模型不相同。在制定好查询计划后, coordinator需要将查询计划发送给需要执行的节点进行查询的执行, 此时两种策略的执行方式不同, 但对结果的回收机制相同:各节点完成自身的查询计划后, 会将结果返回给coordinator进行最后的汇总。

通过分析, 发现根据查询计划的发送方式不同, 在制定查询计划时存在的问题:采用将SQL语句重写后发送的方式, 优点是各节点是一个独立的数据库在运行, 各节点接收到完整的SQL语句 (多条子语句) , 会遵循处理一条SQL语句的标准流程执行, 即对子SQL语句进行编译、优化和路径生成, 这样一来对单节点来说所制定出查询计划是最优的, 而coordinator上所制定的全局查询计划则会被改变, 即实际运行在各个节点上的查询相对于全局很可能不是最优的, 因为各节点无法知道全局节点的信息, 在制定本节点的查询计划时自然不会考虑与其他节点的通信代价, 即使考虑与其他节点的通信代价, 也无法将不与它通信的节点的通信代价考虑在内, 因此基于这个问题进行了调查。

2. 通信代价的分析

2.1 并行数据库的通信种类

在并行数据库系统下, 节点间通信包括以下三种情景:coordinator向各节点发送查询计划、各节点之间的join操作, 各节点向coordinator返回查询结果。

Coordinator向各节点发送查询计划和对结果的回收是在查询结果的执行前和查询结果的生成后, 这部分的通信对查询计划的优化没有任何影响, 因此通信的重点是如何优化join连接操作的通信代价。

在并行数据库系统下, 数据的存储策略主要包括Hash划分策略、Range划分策略以及round-bin (循环) 分区策略, 部分并行数据库系统支持局部划分策略。由于采用局部划分策略, 相当于在单节点的数据库上执行SQL命令, 没有并行因素, 与所研究的问题无关, 这里不做考虑。若采用Hash、Range和round-bin划分策略, 每个节点都存放数据表的一部分数据, 当并行执行时, 每个节点只对本节点上的数据进行操作, 当需要进行join操作时, 需要保证join操作的两个表中至少有一个是完整表, 而由于数据库本身的数据划分策略, 每个节点上的数据都不是完整的, 因此必须进行数据的迁移操作, 及各节点之间需建立通信并发送数据, 保证建立一个完整的数据表。

2.2 Join代价评估

在不同的参考文献和书中, 对join方法都各自的描述, 下面将这些方面描述如下:在并行数据系统下, 两节点的join操作通常包括两部分:发送端的代价和接受端的代价。

在一次通信过程中, 对数据的发送者既需要计算传输的启动代价也要计算数据的传输代价, 而对数据的接收者只需计算传输启动的代价。传输代价包括两个参数mp和ml, 它们参数都基于page level。mp表示启动报文传输的时间代价, ml表示报文传输的时间代价。

计算发送者的总代价的方法为:

为描述并行数据库系统的信息, [1]通过建立一个Libraries存储环境变量, 主要包括四种环境变量:Architecture Library数据库系统的硬件环境、System Library模拟操作和事物系统、Opetator and Access Method Library模拟算法、Data Base Profile Libaray管理数据库调度策略。将代价分为三部分:CPU时间代价、内存时间代价和网络通信时间代价, 并将网络代价的计算包含:发送包的时间 (Smt) 、接收包的时间 (Rmt) 和传输包的时间 (Ptt) , 但是并没有提出如何评估这些时间代价。[2]提出一种并行数据库系统下的代价估计模型, 将查询优化划分为两部分:the metrics和the libraries部分。在计算反应时间上定义了四种类型:local response time, effective time, distribution (partition) time, communication time, 其目的是根据并行数据库的分布策略, 在计算join操作时可能只计算几类反应时间, 本文这样分类的目的应该是对计算的细化。通过阅读以上的代表性的书籍和论文, 发现在并行数据库系统下, 将通信代价考虑在查询优化中已被多处提出, 但现有的开源数据库系统Gridsql和Postgres-xc都没有实现 (此点已被确认) ;但是现有文章都没有提出一个具体的通信代价计算模型, 论文中对这一部分的计算都是提出要考虑的变量, 打算做的工作就是提出一种数学模型计算各节点之间的通信代价。

3. 解决思路

对于问题一将SQL语句分解而无法计算通信时间的问题, 需要在coordinator节点上建立全局信息表, 全局信息表除了要包含并行架构下的硬件资源外还要记录当前运行事物的状态, 即每个事物开始和结束后都要向coordinator提交一个请求用来修改全局表中当前事物的运行状态。

结合问题一和问题二提出一种并行数据库系统下的优化方法, 该方法满足不论是将SQL语句分开发送还是以执行树的形式发送都适用。具体工作如下:

(1) 建立一个全局信息表。该表主要包含3部分:全局硬件信息、全局网络构建、各节点状态信息。硬件信息用来估算各节点的处理延迟, 全局网络用来计算通信时的代价, 各节点状态信息用来描述当前系统中运行事物的状态。

(2) 选择概率统计模型。概率统计模型用来计算当处理多个写事物时, 写写之间发生冲突的概率。使用概率分布模型统计出在当前系统下, 发生n次写冲突的概率, 对于不同的冲突次数, 应该在coordinator节点上估计出处理这些冲突所需要的等待时间, 将这部等待的代价加入查询优化的代价估计中。

(3) 当发生冲突后, 根据步骤2中概率的大小, 和全局信息表中硬件信息估算出处理冲突的时间, 将这部时间加入到查询路径的生产阶段, 此部分的代价可能改变执行树的结构, 从而达到优化的效果。

4. 结论

本文通过分析并行数据库系统的查询优化的制订, 指出存在的弊端, 提出对应问题的解决方案, 为今后查询优化的制订提出技术支持。

参考文献

[1]F.Andrès et al.“A Multi-Environment Cost Evaluator for Parallel Database Systems”, 2nd Intl.Symp.on Database Systems for Advanced Applications, DASFAA’91, Tokyo, April 1991.

数据库查询优化研究篇6

1 引例

有员工数据表employee,和部门信息表department,现求销售部员工的姓名,用SQL语言表达为:

WHERE employee.d_id=department.d_id and department.d_name='销售部'

系统可以用多种等价的关系代数表达式来完成这一查询:

对于表达式1,首先要作连接运算。设一个内存块(存放连接运算的中间结果)能装10个employee元组或100个department元组,在内存中存放5块employee元组和1块department元组,则读取总快数为:

其中读employee表100块。读department表20遍,每遍100块。若每秒读写20块,则总计要花105s。

连接后的元组数为103×104 s设每块能装10个元组,则写出这些块要用106/20=5×104s。

然后作连接操作。假定内存处理时间忽略,这一步中间文件花费的时间需5×104 s。满足条件的元组假设仅50个,均可存放在内存。最后投影操作。

因此,表达式1执行查询的总时间≈105+2×5×104≈105s。这里所有的内存处理时间俊忽略不计。

对于表达式2,为了执行自然连接,读取employee和department表的策略不变,总的读取块数仍为2100块,花费时间为105s dan自然连接的结果比表达式1情况大大减少,为104个。因此,写出这些元组的时间为104,仅为表达式1的千分之一。读取中间文件块,执行运算时间为50s。投影时间忽略。表达式2执行总时间≈105+50+50≈205s。

对于表达式3,先对department表作选择运算,只需读一遍department表,存取100块耗时为5s,因为满足条件的元组金50个,不必使用中间文件。读employee表,把读入的元组和内存中的department元组作连接。也只需读一遍employee表共100块耗时5s。最后作投影时间忽略。表达式3的执行时间≈5+5≈10s。假如department表按连接字段有索引,总的存取时间将进一步减少到数秒。

上述例子充分说明了查询优化的必要性。本文将从内存优化,数据库设计方案、SQL语句书写等方面展开讨论。

2 SQL Server内存优化方法

内存是影响Microsoft SQL Server系统性能的一个重要因素,SQL Server数据库安装时将为具有32MB物理内存的机器缺省配置16MB可用内存,16MB物理内存的机器缺省配置4MB可用内存。应在Microsoft SQL Server数据库安装后进行内存选项(Memory设置,最大配置值为2GB。为了确定SQL Server系统最适宜的内存需求,可以从总的物理内存中减去Windows NT4.0需要的内存以及其它一些内存需求后综合确定,理想的情况是给SQL Server分配尽可能多的内存,而不产生页面调度。

SQL server默认是无限制占用内存的,SQL Server根据服务器的活动来增大或收缩缓冲区高速缓存,以使可用物理内存保持在4 MB到10 MB之间。如果仅仅是自己的机子上开发用,不是服务器的话,不可以让SQL无限制的占用内存的手动设置SQL Server内存选项有两种主要方法:第一种方法,将min server memory和max server memory设置为同一值。此值与达到该值后分配给SQL Server缓冲池的固定内存量相对应。第二种方法,将min server memory和max server memory设置成一个内存范围。这种方法在系统或数据库管理员希望配置SQL Server实例,同时又要考虑在同一台计算机上运行的其他应用程序的内存需求时很有用。

min server memory保证了SQL Server实例的缓冲池可用的最小内存量。SQL Server不会在启动时立即分配min server memory指定的内存量。不过,除非降低min server memory的值,否则当内存使用量由于客户端负荷而达到该值后,SQL Server不能从已分配的缓冲池中释放内存。

max server memory避免了SQL Server缓冲池使用的内存量多于指定的内存量,这样剩余的可用内存可以用来快速启动其他应用程序。SQL Server不会在启动时立即分配max server memory指定的内存量。内存使用量会随着SQL Server的需要增加,直到达到max server memory指定的值。除非提高max server memory的值,否则SQL Server不能超过此内存使用量。

3 数据库设计优化

要在良好的SQL Server方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案。在实际工作中,许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以,要实现良好的数据库设计就必须考虑这些问题。

一般来说,逻辑数据库设计会满足规范化的前3级标准(即范式)。第1范式要求关系模式:没有多值的;第2范式要求:每个非关键字段必须依赖于主关键字,不能依赖于1个组合式主关键字的某些组成部分;第3范式要求1个非关键字段不能依赖于另1个非关键字段。

遵守这些规则的设计会产生较少的列和更多的表,因而也就减少了数据冗余,也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理,这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能,非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行。

此外,生成物理数据库对书库的性能的影响也是十分巨大的。要想正确选择基本物理实现策略,必须懂得数据库访问格式和硬件资源的操作特点,主要是内存和磁盘子系统I/O。这里不赘述。

4 SQL语句优化策略

4.1 SQL语句优化步骤

如图1所示,首先SQL Server要对SQL语句进行语法分析,将其转换成某种内部视图,通常是语法树;然后根据一定的转换规则将语法树转换成标准(优化)表达式;再选择低层优化器,对于语法树中的每一个操作需要根据存取路径、数据的存储分布、存储数据的聚簇等信息来选择连接方式和连接顺序生成具体的执行算法。最后生成查询计划。通常查询计划的执行方案有多个,需要对每个计划计算代价,从中选取代价最小的一个。

目前的商品化RDBMS大都采用基于代价的优化算法。这种算法方法要求优化器充分考虑系统中的各种参数,如缓冲区大小,表的大小,数据的分布,存取路径等,通过某种代价模型计算机出各种查询执行方案的执行代价,然后选取代价最小的执行方案。多用户环境下,查询的执行开销可表示为:

4.2 SQL语句优化新策略

SQL优化的实质就是在结果正确的前提下,充分利用索引,减少表扫描的I/O次数,选择最有效的执行计划来执行SQL语句的过程。下面给出一些SQL优化的新策略。

1)Where子句中的条件由强到弱

在查询SELECT语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据,浪费了服务器的I/O资源,加重了网络的负担降低性能。如果表很大,在表扫描的期间将表锁住,禁止其他的联接访问表,后果严重。在书写时按照天剑先强后弱的原则。例如在学生表中查找通信工程专业的“钟江”。

2)统一SQL语句的写法

对于以下两句SQL语句,程序员认为是相同的,数据库查询优化器认为是不同的。

其实就是大小写不同,查询分析器就认为是两句不同的语句,必须进行两次解析。生成2个执行计划。所以作为程序员,应该保证相同的查询语句在任何地方都一致。

3)限制使用嵌套语句

通常,将一个SELECT语句的结果作为子集,然后从该子集中再进行查询,这种一层嵌套语句还是比较常见的,但是根据经验,超过3层嵌套,查询优化器就很容易给出错误的执行计划。另外,执行计划是可以被重用的,越简单的SQL语句被重用的可能性越高

4)使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb数据库中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。

5)OLTP系统SQL语句必须采用绑定变量

以上两句语句,查询优化器认为是不同的SQL语句,需要解析两次。如果采用绑定变量

@chgtime变量可以传入任何值,这样大量的类似查询可以重用该执行计划了,这可以大大降低数据库解析SQL语句的负担。一次解析,多次重用,是提高数据库效率的原则。

事物都存在两面性,绑定变量对大多数OLTP处理是适用的,但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。“倾斜字段”指该列中的绝大多数的值都是相同的,比如一张人口调查表,其中“民族”这列,90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少,那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。

试想如果@nation传入的第一个值是“汉族”,那整个执行计划必然会选择表扫描。然后,第二个值传入的是“布依族”,按理说“布依族”占的比例可能只有万分之一,应该采用索引查找。但是,由于重用了第一次解析的“汉族”的那个执行计划,那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”,建议对于“倾斜字段”不要采用绑定变量。

6)只在必要的情况下才使用BEGIN TRAN

SQL Server中一句SQL语句默认就是一个事务,在该语句执行完成后也是默认COMMIT的。其实,这就是BEGIN TRAN的一个最小化的形式,好比在每句语句开头隐含了一个BEGIN TRAN,结束时隐含了一个COMMIT。

有些情况下,我们需要显式声明BEGIN TRAN,比如做“插、删、改”操作需要同时修改几个表,要求要么几个表都修改成功,要么都不成功。BEGIN TRAN可以起到这样的作用,它可以把若干SQL语句套在一起执行,最后再一起COMMIT。好处是保证了数据的一致性,但任何事情都不是完美无缺的。BEGIN TRAN付出的代价是在提交之前,所有SQL语句锁住的资源都不能释放,直到COMMIT掉。

可见,如果BEGIN TRAN套住的SQL语句太多,那数据库的性能就糟糕了。在该大事务提交之前,必然会阻塞别的语句,造成block很多。BEGIN TRAN使用的原则是,在保证数据一致性的前提下,BEGIN TRAN套住的SQL语句越少越好!有些情况下可以采用触发器同步数据,不一定要用BEGIN TRAN。

7)使用like查询时应注意

有的时候会需要进行一些模糊查询比如

SELECT*from contact where username like'%金%'

关键词%金%,由于“金”前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%。

8)连接查询时先按连接字段建立索引

SQL Server表连接的三种方式

SQL Server 2000只有一种Join方式———Nested Loop Join,如果A结果集较小,那就默认作为外表,A中每条记录都要去B中扫描一遍,实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大,那Join的结果很糟糕。

SQL Server 2005新增了Merge Join,如果A表和B表的连接字段正好是聚集索引所在字段,那么表的顺序已经排好,只要两边拼上去就行了,这种Join的开销相当于A表的结果集行数加上B表的结果集行数,一个是加,一个是乘,可见merge Join的效果要比Nested Loop Join好很多。如果连接的字段上没有索引,那SQL2000的效率是相当低的,而SQL2005提供了Hash Join,相当于临时给A,B表的结果集加上索引,因此SQL2005的效率比SQL2000有很大提高,这是一个重要的原因。

5 结束语

查询优化可以极大地改善数据库(尤其是大型数据库)的性能。在优化过程中需要根据具体情况具体分析选择最佳的优化方案,在合理设计数据库的基础上,提高SQL查询语句的执行效率,善于使用临时表和绑定变量等策略,减少对内存的需求及变异优化的时间,从而达到提高查询速度,优化数据库性能的目的。

参考文献

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

[2]Grant Fritchey,Sajal Dam.SQL Server 2008 Query Performance Tuning Distilled[M].北京:人民邮电出版社,2010.

数据库查询优化研究篇7

查询优化一直是提升并行数据库性能的研究重点,作为数据库的一个常用且重要的操作,多表连接查询是查询优化的一个难点。由于多表连接查询优化寻求最优解问题是一个NP完全问题[1,2],在参与连接的表数目过多的情况下,充分考虑所有的表连接顺序将会造成查询响应时间过长。研究表明,基于启发式的遗传优化算法可用于解决多连接查询的优化问题。

遗传算法是模拟达尔文的遗传选择和自然淘汰的生物进化过程的计算模型[3]。1991年,Kristin Bennett等人首次提出将遗传算法的思想应用于数据库的查询优化中。1996年,Michael Stillger等人提出使用遗传算法来进行并行查询优化[4,5]。该论文虽然考虑了算子间的并行和流水并行,但是,并没有充分考虑遗传算子内的并行和代价估计模型。

本文提出的Improved Genetic Optimization(以下简称IGO算法)是基于无共享(SN)的并行数据库架构,参考遗传算法的思想并充分考虑影响遗传收敛性的各个算子,形成的一种改进的优化算法,且鉴于并行数据库与传统数据库的差异,提出了针对并行数据库的代价估计模型。在此模型中,将多表连接造成的各个数据节点之间的数据迁移代价考虑在内,提高了代价估计的准确性。

1 代价估计

1.1 单表的代价估计

定义1 表的读取代价计算公式[4,5,6]可描述为:

读取代价 = P+W*T (1)

其中P表示需要访问的页面数;T表示需要访问的元组数;W表示磁盘I/O代价和CPU消耗的权重因子。

定义2 对无索引的情况下,表读取代价的计算公式如下:

estCost = nPages+FACTOR*nRows (2)

其中FACTOR定义为常数0.0010;nPages,nRows,FACTOR分别对应于式(1)的P,T, W。

定义3 对于含有类似于“分布表.分布列 =常量”的查询条件,其代价估计的计算公式可表示为:

estCost=I.nPages+nRows*Col.Selectivity+FACTOR*nRow (3)

其中I.nPages为索引的页数; Col.Selectivity为列的选择度。列的选择度反应了此列中的重复数据对代价的影响,重复数据越多,参与连接的概率就越大,代价估计也就越大。对于列的选择度的详细定义如定义4:

定义4 对于列的选择度的计算公式可表示为:

Selectivity=

${\begin{cases} \frac{1.0}{元组总数} 没有重复数据 \\ \frac{1.0}{\frac{元组总数}{数据节点个数 (n)} * \sum_{i = 1}^{n} s t a d i s i n c t_{i}} 有重复数据 \end{cases} (4)$

其中数据节点i上列的stadistinct= -(列的投影总数/表的元组总数)。

1.2 多表连接的代价估计

对于没有数据迁移的连接代价估计可利用单点数据库的代价估计模型,有关单点数据库的代价估计技术已经非常成熟,本文着重分析含有数据迁移的连接代价估计模型。含有数据迁移的普通连接基本模型如图 1所示。计算连接代价可按照是否有索引分为两个处理流程。

1) 首先,确定node1和node2的数据页数。

node1.nPages =

node1. RowsReturned * node1. rowSize / PAGE_SIZE

node2.nPages =

node2. baseNumRows * node2. rowSize / PAGE_SIZE

计算node1和node2的数据页数时用到了不同的参数,node1进行了选择操作,故用估计返回元组数 RowsReturned,node2上没有进行选择操作,故用表中元组的总数目baseNumRows。

其次,分布列减少因子partReduction的确定。其初始值为1.0,若Column2是Table2的分布列,则partReduction = 1.0 / Table2的分布节点个数。

2) 从数据迁移方向可知,只需要考虑Column2是否有索引。若Column2有索引,则node2的索引页数node2.IndexNumPages = 右节点的估计返回元组数 / 每个索引页的元组数。连接操作要处理的元组数目resultCardinality = 左节点的估计返回元组数 * 右节点的估计返回元组数 * Column2的选择度。

此时的连接代价可表示为:

estJoinCost = node1.nPages+ node1. RowsReturned * node2.nPages * partReduction+ FACTOR * resultCardinality * partReduction (5)

(连接代价 = 左节点I /O代价 + 右节点索引页的I /O代价+右节点数据页I /O代价+ CPU代价)

3) 若Column2上没有索引,连接代价可表示为:

estJoinCost = node1.nPages+ node1. RowsReturned * node2.nPages+ FACTOR * (joinNode. RowsReturned) (6)

(连接代价= 左节点I /O代价 + 右节点 I /O代价 + CPU处理代价)

由于在没有索引的情况,对于node1中的每一条记录,必须在node2上进行一次完整的扫描。最坏的情况下,假设缓存只能容纳每个node的一个数据页,此时共需(node1.nPages + node1.RowsReturned * node2.nPages)次数据页传输。

2 IGO查询优化算法

2.1 查询的预优化

在进行查询优化之前,是对整个查询的一个预优化过程。预优化的目的是实现对部分特定表的合并,从而减少IGO查询优化中表的数量。预优化的思想是:如果进行连接的两表采用相同的数据分布策略(如散列划分、轮询划分等)且分布列相同或者涉及复制表的连接,则说明两表的连接不需要数据迁移,只需在相应的数据节点上即可完成连接操作,对于这样的两表可进行合并操作。把合并后的两表从连接表序列中删除,把合并后的生成的新表添加到连接表序列中。

预优化的例子如图 2所示。预优化之前是一个浓密度树,经过合并表后形成了一棵含有四个叶子结点的简单左线性树。不需要数据迁移即可完成连接操作的表D和表E进行合并操作,生成新表DE添加到连接表序列中,并将表D,表E从连接表序列中删除。

2.2 IGO查询优化算法

遗传算法是模拟生物进化过程的计算模型,是现代启发式算法的一种。其具有全局优化性能、通用性强、且适合于并行处理 [7,8]。本文充分考虑遗传算法的相关因素,提出了一种IGO查询优化算法,其处理的主要处理流程如图 3所示。

1) 确定种群规模大小和编码方式。种群规模的大小直接影响到算法的性能。种群太小则不能提供基因丰富的个体;种群规模太大则过多的个体会增加优化的时间和空间消耗,造成收敛时间过长。种群规模应与参与连接的关系数和系统的处理能力有关。

表的可能的连接先后顺序可用整数组成的字符串编码。例如:图 2所示的表连接顺序的可表示为“1234”,这里的1、2、3、4分别为表A、表B、表C、表DE的ID号。

2) 适应度函数的选择。个体的适应值是遗传优化过程中优胜劣汰的准则。数据库中适应度函数的选取应与个体通过代价估计函数得出的相应代价成反比。可表示为:

$f i t n e s s (Ι n d i_{i}) = \frac{1 / e s t c o s t (Ι n d i_{i})}{\sum_{j = 1}^{p o p L e n g t h} 1 / e s t c o s t (Ι n d i_{j})} 0 < i < p o p L e n g t h$

其中popLength表示种群规模;estcost(Indii)表示种群中第i个个体的代价估计值。在代价估计的过程中根据个体中参与连接的表的不同情况调用不同的公式。

● 存在索引的情况下,如果参与连接的表存在“分布表.分布列 =常量”的查询条件,则首先调用式(3),进行单表的代价估计,其次调用式(5)进行连接代价估计,两表的执行总代价为上述两个代价估计之和;如果不满足上述查询条件,则首先调用式(2)进行单表的代价估计,其次调用式(5)进行连接代价估计。两表的执行总代价为式(2)与式(5)的代价估计之和。

● 无索引的情况下,两表的执行总代价为式(2)与式(6)的代价估计之和。

● 按照个体表示的连接顺序计算出个体中所有相邻表的执行总代价,取其和作为个体的代价估计值。

3) 挑选个体进行配对和变异,产生新个体。本文的选择算子采用的是排序选择法。在排序选择法中挑选个体遗传到下一代采用的是基于偏置因子的线性函数[9]。

f(geqo

$_r a n d) = {\begin{cases} \frac{p o p L e n g t h * (b i a s - \sqrt{s q r t v a l})}{2.0 * (b i a s - 1.0)} (s q r t v a l > 0) \\ \frac{p o p L e n g t h * (b i a s - s q r t v a l)}{2.0 * (b i a s - 1.0)} (s q r t v a l \leq 0) \end{cases}$

其中:sqrtval = (bias * bias)-4.0 * (bias-1.0) * geqo_rand;计算结果f(geqo_rand)表示选中个体的编号,geqo_rand是0.00-1.00之间的一个随机数,偏置因子bias设为2.0。排名靠前的个体被选中的概率大,在bias=2的情况下,排名靠前的个体选中次数是排名靠后的个体的三倍多。

其次,配对算子的选择。虽然影响遗传算法收敛性的因素比较多,但是,配对算子因具有全局搜索能力是影响遗传算法收敛性的最主要算子。传统的配对算子有部分配对(PMX),循环配对(CX),基于位置的配对和顺序配对等。由于传统的配对算子不能很好地将父代优秀的基因传递给下一代[10]。所以,结合数据库多表连接的特征和贪婪算法的思想,本文提出了一种新的配对算子。

输入:Inid1= (x1, x2,…,xn),Inid2= (y1, y2,…,yn)

//costxiyj基因xi,yj所代表的两个表的总执行代价估计值;

最后,变异算子的确定。变异算子具有局部搜索的能力,对交叉算子具有辅助作用。本文采用的变异算子大致流程可总结为:随机的从新个体中选出两个基因位,交换两个基因位的值,重复执行n次。

4) 添加新个体到种群中。按照原先的排序关系将新个体添加到种群中,并且在添加的过程中,如果出现个体重复的情况,就需删除其中一个进行单一化操作。在添加新个体完毕后,为了体现优胜劣汰的思想,并且防止种群中个体数量增多,需淘汰排名靠后的个体。

3 实验分析

实验数据库的环境采用的是开源的并行数据库gridsql,其中包括1个控制节点和8个硬件环境相同的数据节点。所有的处理结点通过交换机实现完全互连,且硬件环境均采用:CPU P4 2.0GHz;内存512MB;硬盘40GB;网卡100M bit/s。节点主机之间采用的是100M bit/s的以太局域网,操作系统为Red Hat Enterprise Linux 5.0。

实验中分别采用了6表连接、11表连接和14表连接。各个表中均含有10 000个元组。且在14表连接中存在三个表的连接不含有数据迁移。以响应时间(单位为毫秒)作为查询系统性能好坏的一个标准,并模拟了贪心算法和基于左线性树算法。设定各个处理机的可用内存为每次实验各个查询运行3次,取平均值作为该查询的查询响应时间。

实验结果如图 4所示。实验表明:IGA优化算法在处理多连接查询优化的过程中,优于其他两种算法,且通过分析11表连接和14表连接可知影响查询响应时间的主要因素为表之间的数据迁移。

4 结语

在并行数据库中,多连接查询优化是影响查询的一个重要课题。本文提出的一种新的代价估计模型,充分结合了并行数据库优化的特殊性和遗传算法的生物学特性,并对传统遗传算法进行了改进。实验证明IGO算法减少了多表连接查询的响应时间。

摘要：多表连接查询是并行数据库中的一种常用且重要的操作,然而基于传统遗传算法所制定的多表连接查询计划,往往存在查询响应时间长的缺陷。根据无共享并行数据库的特点,将一种新的代价估计模型引入到传统遗传算法中,并对传统遗传算法进行了改进。实验证明改进后的遗传算法能制定出更优的查询计划,从而减少多表连接时的查询响应时间。

关键词：并行数据库,无共享架构,多表连接优化,代价估计,遗传算法

参考文献

[1]李建中,孙文隽.并行关系数据库管理系统引论[M].北京:科学出版社,1998.

[2]杨利,昌月楼.并行数据库技术[M].北京:国防科技大学出版社,2000.

[3]陈国良,王煦法,庄镇泉,等.遗传算法及其应用[M].人民邮电出版社,1996.

[4]Bennett K,Ferrism C,Ioannidisy E.A genetic algorithm for databasequery optimization[C]//Proceedings of the fourth International Confer-ence on Genetic Algorithms,1991:400-407.

[5]Stillger M,Spiliopqulou M,Freytagj C.Parallel query optimization:exploiting bushy and pipeline parallelism with genetic programs[R].Technical report,Humboldt-University at Berlin,1996.

[6]文继荣,陈红,王珊.Shared-nothing并行数据库系统查询优化技术[J].计算机学报,2000,23(1):28-37.

[7]彭智勇,彭煜玮.postgresql数据库内核分析[M].北京:机械工业出版社,2012.

[8]杨冬青,等译.数据库系统概念[M].北京:机械工业出版社,2008.

数据库查询优化研究篇8

随着信息化社会的到来,数据库存储的数据量越来越大,如何提高海量数据的查询效率一直是人们关注的焦点。据统计,信息系统80%的查询性能问题可以通过SQL优化来解决[3],通过对查询语句的优化,不仅可以提高查询效率,而且还有助于提高数据库和应用系统的整体性能。

关于海量数据SQL查询的优化方法有多种,随着数据库优化器技术和应用领域的发展,一般的SQL语句优化技术已不能满足新业务发展的需求。为了提高海量数据的查询效率,本文以“上海市实有人口信息管理系统”性能优化为实践基础,依据数据库技术相关理论,提出并总结了有效利用SQL提示来提高查询效率的方法,现供有关读者参考。

“上海市实有人口信息管理系统”是根据国家各部委联合编写的《全国人口信息资源数据库项目可行研究报告》而专门研制开发的大型数据库系统。目前,该系统有2TB以上的数据量,400多个并发用户,选用ORACLE数据库系统,属于实时要求极高的海量级数据库系统。本文提出的方法在实践中得到验证和应用,取得了较好的效果。

1优化器与SQL提示

1.1优化器与SQL提示关系

在ORACLE数据库系统中,SQL提示是添加在SQL语句中用以影响执行计划的一种注释性指令。最初,SQL提示是作为一种调试工具出现的,主要用来弥补未成熟的基于代价优化器(CBO)的缺陷。随着优化器引擎技术的完善,在目前的数据库应用中,基于优化器引擎技术的SQL提示不仅可以作为补救优化器缺陷的有用工具,而且对于期望优化执行计划的用户来说也是一种有效且方便的提高查询效率的手段。

最佳的数据库查询计划需要先进的优化器,同时需要加以人工辅助的SQL提示以达到应用最优目标。在实际的数据库优化过程中通过添加正确的SQL提示注释指令不仅可以使查询工作趋于简单化,而且能确保在数据库表和索引的相关资料更改时,SQL语句仍然可以完成预先指定的执行计划而不影响系统的性能,尤其是在海量数据的综合查询时,合理地应用SQL提示方法既能使一些复杂的查询变得简单可行,又可以成倍的提高查询效率。

1.2优化器的提示原则

随着数据库技术的不断发展,优化器引擎技术的日趋改进,相应的SQL提示种类也越来越多,使用起来也显得倍加复杂。因此,有必要了解SQL提示并掌握SQL提示之间的兼容性。在实际应用过程中,使用SQL提示时需遵循以下原则:

(1) 语法准确性原则为了防止数据库系统把优化器引擎的提示注释指令与普通的注释文本同等对待而使提示失效,需注意提示指令的语法准确性,即必须使用完整的提示注释语法。在实际应用中,本文推荐使用/*+HINTS*/形式的注释格式,否则ORACLE系统将视其为普通注释而失效,当使用全套提示即同时使用多个不同SQL提示时,各提示之间用应用空格隔开。

(2) 指向一致性原则若在数据库查询功能设计中为表指定了别名,则在SQL提示中必须使用表别名而不能使用表名称,以保持提示注释指令中的表名与所访问的表名一致,不然数据库操作系统将会忽略提示注释指令内容而影响执行效率。同时需注意的是,不能在提示中带有模式名称,如果在提示中指定了模式所有者,提示将不起作用。

(3) 提示兼容性原则在实际使用SQL提示时,由于各种原因,经常会临时添加各种提示而忽略了各提示之间的兼容性,导致提示无效,有时甚至会降低系统的原有性能。这是因为数据库操作系统发现如果存在多个提示,且它们之间存在冲突时将会忽略此查询中所有的提示。如在没有索引的列上指定索引提示,或在索引扫描时指定了并行查询等。

2优化器的SQL提示方法

根据SQL查询语句的特点和数据库优化器种类,ORACLE系统提供了不同的SQL提示。当应用系统进行SQL查询优化时,不仅要遵循SQL提示的使用原则,还要考虑海量数据查询的特点来选用合适的SQL提示,使之更好地为查询服务。

影响系统查询效率的主要因素有以下几个方面,为不同查询目的而建立的库表关键索引之间的冲突问题;多表关联统计查询中的操作访问、连接和顺序等执行计划配置问题;多重嵌套、挖掘和钻取查询过程中的子查询语句编程技巧等问题。针对上述问题,本文提出了基于优化器改进的解决方法。

2.1选择优化器模式的提示

作为SQL处理引擎的核心,ORACLE优化器的主要职能是尽可能地为所有SQL语句创建最快、最有效的执行计划来为查询服务。在目前ORACLE数据库优化器中,系统自动默认的主要有基于规则的优化器(RBO)和基于代价的优化器(CBO)二大类,而实际可供选择的优化模式有四种:1)系统默认的CHOOSE模式;2)基于规则的RULE模式;3)基于响应时间最短的FIRST_ROWS模式;4)基于整体资源消耗最小的ALL_ROWS模式。

在通常应用中,用户可以采用系统提供的优化器默认模式,但在复杂使用中,由于应用系统的查询类型及数据库结构设计的不同等原因,默认模式的优化器未能处在最佳状态下,因此需要根据不同的目标任务进行选择和更改以使优化器能生成最佳的查询计划。

例如,当ORACLE数据库优化器探测到某个表需统计数据而其它表没有或相关统计资料不足时,系统将自动采用基于代价的CHOOSE默认模式,由此将导致在查询的过程中对资料不足的表进行统计和分析处理,这样将大大降低SQL的执行速度。再如,对于比较单一的数据查询任务,虽然可以通过设置OPTIMIZER-MODE等系统参数以得到期望的优化器模式,但对于海量数据库上千种排列组合需求的查询任务,为完成其中的某几项查询任务而频繁更改系统参数是不可行的方案,且有可能造成整个系统的不稳定。在这种情况下,可以通过添加SQL提示来干预优化器从而达到直接、高效的查询目的。

在实际应用中除了默认的CHOOSE模式外,本文对于优化器的其余三种应用模式和适用范围作一归纳:

(1) RULE提示,是指定ORACLE系统为查询任务提供基于规则的优化模式。在实用中,当用户不确定使用哪种优化器模式为最佳时,通常首先尝试使用RULE提示。

(2) FIRST_ROWS提示,是基于时间成本的优化方法,目的是提供最快的响应时间。使用FIRST_ROWS提示应该保障查询中涉及的表和索引拥有统计资料,而该统计资料是由执行ANALYZE指令后得到的。

(3) ALL_ROWS提示,是基于资源成本的优化模式,目的是提供整体最佳的吞吐量和最小的系统资源消耗。一般ALL_ROWS提示采用全表扫描方法,其主要用于批量查询任务。

在具体的数据查询中,可以根据不同的需要来选用合理的优化模式。例如,在一事务处理的查询中,系统参数已经设置为OPTIMIZER_MODE=RULE,当用户需要尽快地看到部分查询结果时,可以不更改系统参数,只需在SQL语句中添加FIRST_ROWS提示即可,如下所示:

SQL>SELECT/*+FIRST_ROWS*/A.A1 FROM A WHERE ……

由于海量数据查询语句的复杂多变,单纯依靠更改优化器模式很难得到理想的查询效果。一般情况下,优化器提示需要和其它相关提示结合使用。

2.2选择索引的提示方法

在大多数系统查询情况下,一般都选择索引扫描的方式以提高查询效率,但在查询表有多个索引的情况,若优化器首选了非关键索引同样会发生查询效率低下而难以承受。在此情况下,若在查询语句中显式地指定表名和索引名,添加合理的关键索引提示,优化器不仅会使用指定的关键索引,还可以同时使用多个索引为查询服务。

例如,表A中数据项A1和A2都有索引。在没有指定索引的时候,优化器选择默认的索引为IND_A1,而根据表的特点和查询需要,应首选的索引为IND_A2。虽然都采用索引扫描的计划,但添加索引提示后,系统的执行效率明显提高。实例语句如下:

SQL>SELECT/*+INDEX(A,A2_IND)*/A.A1,A.A2,A.A3 FROM A WHERE ……

在上例查询中,优化器选择默认索引时执行时间为121.2秒,添加提示指令后查询时间为42.23秒,效率为默认选择的2.87倍。

在实际应用中由于上述两索引都是非唯一索引,还可以通过添加AND_EQUAL提示,将两个索引连锁成单一索引,这样不仅避免了不必要的输入输出,而且可以进一步提高查询效率。实例语句如下:

SQL>SELECT/*+AND_EQUAL(A,IND_A1,IND_A2)*/A.A1,A.A2,A.A3 FROM A WHERE ……

经上述优化提示后,查询时间为29.03秒,效率为默认选择的4.17倍,达到了系统TB级数据量条件下查询时间小于30秒的设计指标。

2.3全表扫描和并行查询的提示方法

在数据查询时,使用索引扫描可以快速直接有序的存取元组,但同时也带来了访问包含索引数据块的额外代价,并且在一些情况下索引扫描的效率比全表扫描方式时还要低下。据有关资料介绍:在对有序表的访问,返回的记录数大于全表记录数的40%时,或对无序表的访问,查询的记录大于全表记录数的7%时,采用全表扫描的效率远远高于索引扫描[8]。一旦检验了全表扫描的合法性之后,可以考虑在指示全表扫描的基础上调用ORACLE的并行查询来提高查询效率。并行查询的调用,可以通过ALTER TABLE命令来实现,但在部分查询时使用此命令是非常危险的。因为此举不仅大大削弱了数据库的性能,而且还会给成百上千的SQL语句带来负面影响。而更有效的方法是在查询语句中添加并行查询的SQL提示,使优化器在不影响其它语句的情况下轻松实现并行查询的目的。

如在实有人口信息管理系统中,表A有8302万条记录,需查询的记录超过有序表A总记录数的40%。适合做全表扫描,并符合并行查询要求。在查询中添加相应提示后,查询效果十分明显,查询时间由222.57秒减少到34.21秒,语句如下:

SQL>SELECT/*+FULL(A) PARALLEL(A,DEFAULT,DEFAULT)*/A.A1,A.A2,A.A3 FROM A WHERE ……

在多表海量查询中可以考虑在添加并行查询提示的同时再添加表连接类型的提示。例如在实有人口信息管理系统中,A表有8302万记录,B表有6730万条记录,根据两个表都采用全表扫描方式,结果集都比较大,并且两个表的总记录数相差不多的特点,适合采用在调用并行查询时选择HASH连接的方式来获得最佳的查询计划,具体如下:

SQL>SELECT/*+USE_HASH(A,B) PARALLEL(A,15) PARALLEL(B,15)*/A.A1,A.A2,A.A3 FROM A,B WHERE ……

在同等条件下测试,添加提示前后的执行时间分别是630.45秒和64.3秒,查询效率提高9.8倍。

2.4表操作的执行计划提示

在实际海量数据查询应用中,常遇到一次查询要访问多个表的情况,并且各表结构和数据量相差很大。实现索引提示的同时还需制定对多表操作的执行计划。对多表的操作涉及到表的访问、连接和访问顺序等类型。在决定表的访问顺序时要考虑优化器模式:在RBO模式时,驱动表是指FROM语句中最后面的一个表;而在CBO模式下,驱动表默认为最前面一个表。当一条查询语句提交后,优化器主要根据表的操作类型和相应的操作方法来生成查询计划去查询记录,如图1所示。

从图1中可看出数据查询中对表操作的多样性和复杂性。对表操作的查询优化中不仅需要选用最佳的优化模式,且需要手工调整表的访问顺序。最常用且有效的方法是根据表的结构和大小选用不同的提示来指示表的访问顺序和连接类型。

举例:在实验系统中涉及到对四表(A,B,C,D)的访问,最大的表有8千万条记录,最小的表只有5千条记录。根据查询需要,用ORDERED提示对两个大表进行散列(HASH)连接,两个小表进行嵌套循环(NL)连接。执行语句如下:

SQL>SELECT/*+ORDERED USE_HASH(A,B) USE_NL(C,D)*/COUNT(*) FROM A,B,C,D WHERE ……

2.5提高子查询效率的提示方法

由于SQL语句重写功能可使大部分查询语句有多种写法,所以,在SQL语句中使用子查询也是开发人员需避免的事情。然而,在某些情况下会不可避免地要用到ORACLE子查询。因此,当我们必须使用子查询的时候,应考虑使用SQL反连接提示这一选项。

反连接是SQL中含有NOT IN,NOT EXISTS子句时执行的操作。当子查询非空时,我们可以添加的SQL连接提示主要有MERGE_AJ提示和HASH_AJ提示。MERGE_AJ提示可用来强制执行一个合并反连接操作,适用于含有NOT IN的子查询且全表扫描优于索引访问的情况下。HASH_AJ提示在NOT IN子查询进行散列连接时,可用来执行一个散列反连接操作。

如在实有人口信息管理系统中,表A有4 201万条记录,表B有125万条记录,结合此查询执行全表扫描的特点,添加MERGE_AJ提示及其具体语句如下:

SQL>SELECT A.A1,A.A2,A.A3 FROM A WHERE A.A1 NOT IN(SELECT/*MERGE_AJ*/B.B1 FROM B WHERE ……)

添加此提示前后的执行时间分别为158.34秒和36.12秒,查询效率提高了4.38倍。

3实践与小结

在提高海量数据查询效率时,SQL语句的优化是个繁琐而又重要的主题。特别是用以查询的SQL语句相对复杂时,优化器并不总能选择最佳的查询计划。这时,我们可以首先考虑根据不同的用途和需要来添加合理的SQL提示,直至获得到最佳的查询计划。在“上海市实有人口信息管理系统”性能优化实践中,我们充分地利用了上述方法,下面列出实践中所常用到的具体优化方法,如表1所示。

结合表1的优化方案,在SET TIMING ON命令下执行上述实例语句并对其优化前后的用时结果作一对比图,具体如图2所示。

在图2中:

1代表添加/*+FIRST_ROWS*/提示前后结果对比;

2代表添加/*+INDEX(A,JIGUAN_IND)*/提示前后结果对比;

3代表添加/*+ORDERED USE_NL(T_PCS,T_JBXX) USE_HASH(T_JWRY,T_JBXX)*/提示前后结果对比;

4代表添加/*+MERGE_AJ*/提示前后结果对比;

5代表添加/*+ORDERED USE_NL(T_PCS,T_JBXX) USE_HASH(T_JWRY,T_JBXX)*/提示前后结果对比;

6代表添加/*+FULL(T_PCS_MJDA) PARALLEL(T_PCS_MJDA,DEFAULT,DEFAULT)*/提示前后结果对比;

7代表添加/*+USE_HASH(A,B) PARALLEL(A,15) PARALLEL(B,15)*/提示前后结果对比。

综上所述,通过添加合理的SQL提示不仅可以解决查询语句的瓶颈问题,而且能在付出相对较低的系统代价时轻松地提高查询效率。

参考文献

[1]盖国强,冯春培.ORACLE数据库性能优化[M].人民邮电出版社,2005.

[2]Robert G Freeman.ORACLE DBA必备技能详解[M].清华大学出版社,2006.

[3]Lawson C.Oracle性能优化科学与艺术[M].清华大学出版社,2006.

[4]LoneyK.Oracle8i数据库管理员手册[M].机械工业出版社,2000.

[5]Richard JNiemiec.ORACLE9i性能调整[M].清华大学出版社,2004.

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

[7]蒋秀凤,何凤英.Oracle9i数据库管理教程[M].清华大学出版社,2005

浅谈数据库查询优化技术篇9

1 查询优化的必要性

查询优化对系统性能的提高起到非常重要的作用。有些程序程序设计员觉得查询优化只是DBMS的任务, 而与所编写的SQL查询语句没太大关系, 这种想法是不对的。由于SQL语句是操作数据库唯一的途径, 所有应用程序的执行最后都是要归结到SQL语句的执行, SQL语句的运行效率将直接决定数据库系统的性能。所以查询语句的优化是查询效率提高的根本。下面通过一个例子说明进行查询优化的必要性。

例:查询到选修了课程号为3的课程的学生姓名。用SQL语句来表达如下:

假设在该数据库中拥有1000条学生的记录, 10000条选课的记录., 而其中选修了3号课程选课的记录是50条。

系统能够运用多种相互等价的关系表达式来进行查询:

除了这三种表达式之外还有几种, 但本文就仅引用这三种来进行对比分析。我们将会看出因为查询所执行策略的不同, 其查询的效率也将相差的很大。

1) 对于第一种情况

(1) 首先计算广义的迪卡尔积

将Student与KC的每个元组进行连接。通常采用的连接方法是:在内存里尽量多的装进某一个表 (如KC表) 的若干块元组, 而留下一块用来存放另外一个表 (如Student表) 的元组。紧接着将Student中的每一个元组与KC中每一个元组进行连接, 在连接之后的元组将一块装满后就将其写到中间文件里, 然后再从Student里读取一块与内存中的KC元组进行连接, 直至Student表全部处理完。这个时候再次读取若干块KC的元组, 读取一块Student的元组, 然后重复以上的处理过程, 直至将KC表中内容全部处理。假设每个块能够装10个Student元组或者100个KC的元组, 那么在内存里放入5块KC元组以及1块Student元组, 则读取总块数为:

其中读了Student表的100块。KC表读了20次, 而每一次是100块。如果每秒读写量为20块, 那么总共要花105秒的时间。假设每块能够装入10个元组, 那么将这些块写出所要花的时间为103×l04=107秒。

(2) 进行选择操作

依次将连接后的元组进行读取, 根据选择的条件将达到要求的记录选取出来。如果忽略内存的处理时间。那么这一步中间文件的读取所花费的时间为5×l04秒。

(3) 作投影处理

将第二步的结果在Name进行投影输出, 得出最后的结果。所以该情况下所执行的查询总时间大约为105秒。

2) 对于第二种情况

(1) 首先计算自然连接

为了能够执行自然连接, 对KC与Student表的读取策略保持不变, 所需总的读取时间任为105秒。但是自然连接的结果却比第一种的情况很大程度上减少了, 大约104条。所以将这些元组写出的时间大约为50秒。所化时间仅是第一种情况所用时间的千分之一。

(2) 然后读取中间的文件块, 进行选择的运算, 所花费的时间也50秒。

(3) 将第二步的结果进行投影输出。那么第二种情况所用的总时间大约为205秒。

3) 对于第三种情况

(1) 首先对KC表进行选择的运算, 只对其进行一次读操作, 而存取100块所花费的时间是5秒, 这是因为达到条件的元组只有50个, 没有必要用到中间文件。

(2) 读取Student表, 将读取的Student表的元组以及内存里的KC表的元组进行连接。这里只需要读取一次Student表, 总共花费的时间是5秒。

(3) 最后将连接的结果进行投影输出。那么第三种情况所花费的总时间大约为10秒。

通过以上这个比较简单的例子能够充分的说明进行查询优化的必要性, 也让我们对于查询优化的一些方法有了比较初步的了解。例如当同时存在选择与连接操作的时候, 应该先进行选择的操作, 这样进行连接的元组就能够在很大程度上减少了。

2 常用的查询优化方法

首先简单介绍一些比较常见的查询优化方法, 其次重点介绍通过合理安排索引来进行查询优化的方法。

1) 比较常用的查询优化的方法有:

(1) 将选择运算算与投影运同时进行。

倘若在同一关系中存在多个选择与投影的运算, 那么可将选择的运算与投影的运算进行结合, 此举目的在于选取出满足条件的元组之后就对其进行投影操作。

(2) 选择的运算应量先进行。

由于符合选择的条件的元组通常情况下都是原来关系的子集, 这就让计算的中间结果变小了。这种做法是最为基本也是非常有效的查询优化方法。

(3) 将一些选择与其之前执行的笛卡尔积进行结合形成一个连接的运算。

连接尤其是等连接的运算与同样关系上的笛卡尔所积产生的结果相比要小很多。所执行的代价也小很多。

(4) 将投影与其前后的双目运算进行结合。

双目运算主要包括JOIN运算与笛卡尔积, 同以上几条方法原理类似, 当进行JOIN运算或笛卡尔积的时候, 都要将关系的元组选出, 没必要为了投影操作而将关系单独的扫描一次。

(5) 恰当的选择连接的算法。

连接的操作在关系操作中是最为费时的操作, 如今也有了许多进行连接优化的算法。例如排序合并算法、索引连接算法以及HASH连接算法等。恰当的选择连接算法从一定意义上说是存取路径的选择, 属于物理优化的范畴。一些RDBMS已经提供了多种的连接算法来为优化子系统服务。

2) 合理的安排索引的方法

索引是由用户来定义的, 存储与物理介质中的数据结构。当按照索引来进行数据查询的时候, 索引就提供了对数据快速的访问。倘若不依靠索引, 数据库也可以依靠SELECT语句查询到相应结果, 但是随着记录数量的不断增加, 运用适当的索引能够让查询效率在很大程度上提高。然而, 倘若在对索引的使用过程中, 不能够认真的考虑索引实现的过程, 那么将会造成数据库性能的破坏。

索引的创建通常有两个目的:一是对被索引列的唯一性进行维护;二是提供对表中数据进行快速访问的策略。一般大型的数据库拥有两种索引方式:聚集索引与非聚集索引。其中, 聚集索引是数据行的键值为基础在表内进行排序以及数据行的存储, 这就是根据索引列对表进行物理的排序, 所以在一个表里只可以存在一个聚集索引。如果表中没有聚集索引, 那么数据则按堆集的方式进行存储。采用聚集索引进行的数据查询的效率最高, 尤其对于那些需要频繁搜索的列非常的有效。而非聚集索引的结构是完全的独立于行的, 它不对表中的数据进行排序, 所以在一个表内可创建几个非聚集的索引。由于非聚集索引的键值项是通过指针指向包含此键值的数据行的, 因此其查询的效率要比聚簇索引慢些。对于索引文件的安排可从如下几点考虑:

(1) 当表中对主键的查询较少并且很少按范围检索的时候, 此时不要将聚集索引建立于主键上。因为每一张表只有一个聚集索引, 应该根据实际的应用情况确定将其分配给经常使用范围检索的属性列, 这种做法可以最大限度地提高系统的运行效率。

(2) 对于索引较多的表, 如果进行频繁的插入、更新、删除等的操作, 在建表和设置索引时应当尽量设置较小的填充因子, 以在各个数据页中留下比较多的自由空间, 以此来减少页分割和重新组织的工作。

(3) 当用户检索的数据量较大时, 则必须在相应的数据列上建立索引、同时建立复合索引时涉及的属性列应尽量控制个数, 不要太多, 否则将会增加索引的开销。当执行更新操作时, 数据库的更新速度会降低。

(4) 当检索的属性列中其记录值重复非常多, 如逻辑型的数据、标志位, 那么在这样的属性列上不需建立索引。

(5) 如果要频繁修改索引列的值、则避免在该属性列上建立聚簇索引, 这种操作会降低系统整体的运行效率。

(6) 越窄的索引具有越高的效率。对于比较窄的索引, 每页可以存放较多的索引行, 且索引的深度较少。因此, 缓存中可以存放更多的索引页, 这样也就减少了输入输出操作。

(7) 索引不是建立得越多越好。当对表执行更新时, 系统此时会自动更新该表的所有索引文件, 而索引文件的更新是需要耗费时间的, 这样也就降低了系统的运行效率。

(8) 设置合理的组合索引。查询时, 在查询条件中必须使用组合索引前导列, 否则该组合索引失效。如果在SQL中形成了索引覆盖的情况, 性能将达到最优。

3 结束语

合理的选择查询优化的方法能够很大程度上增加系统的查询效率, 从而提高了整个系统的运行效率。现如今, 在查询优化方面的研究也越来越多, 其研究的价值也是有目共睹的。因此, 无论从哪方面来说, 进行查询的优化方法的研究都是有意义的。

摘要：运用数据库查询优化技术能够让一般的查询效率在一定程度上得到提高。该文首先通过举例说明优化查询的必要性, 并在此基础上提出查询优化的方法。

关键词：数据库,查询优化,查询效率