数据库SQL查询处理及其优化方法的研究 精品Word格式.docx
《数据库SQL查询处理及其优化方法的研究 精品Word格式.docx》由会员分享，可在线阅读，更多相关《数据库SQL查询处理及其优化方法的研究 精品Word格式.docx（25页珍藏版）》请在冰点文库上搜索。
选择的依据可以是基于规则的，也可以基于代价的，还可以基于语义的。
实际RDBMS中的查询优化器都综合了运用了这些优化技术，以获得最好的查询优化效果。
（4）查询执行
查询执行就是依据优化器得到的执行策略生成查询计划，由代码生成器（codegenerator）生成执行这个查询计划的代码。
2.2实现查询操作的算法示例
选择操作和连接操作是查询操作的两个典型操作，每一种操作有多种执行这个操作的算法，下面探讨实现这两种操作的几个主要算法。
2.2.1选择操作的实现
众所周知SELECT语句功能十分强大，有许多选项，因此实现的算法和优化策略也很复杂。
下面以简单的选择操作为例讲述典型的实现方法。
例1Select*fromstudentwhere<
条件表达式>
；
考虑<
的几种情况：
C1：
无条件；
C2：
Sno=‘20XX15121’；
C3：
Sage>
20；
C4:
Sdept=‘CS’ANDSage>
（1）简单的全表扫描方法
对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。
对于小表，这种方法简单有效。
对于大表顺序扫描十分费时，效率很低。
（2）索引（或散列）扫描方法
如果选择条件中的属性上有索引（例如B+树索引或Hash索引），可以用索引扫描方法。
通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在要查询的基本表中找到元组。
例1-C2以C2为例，Sno=‘20XX15121’，并且Sno上有索引，则可以通过使用索引得到Sno为‘20XX15121’元组的指针，然后通过元组指针在student表中检索等到该学生。
例1-C3以C3为例，Sage>
20，并且Sage上有B+树索引，则可以使用B+树索引找到Sage=20的索引项，以此为入口在B+树的顺序集上得到Sage>
20的所有元组指针，然后通过这些元组指针到student表中检索所有年龄大于20的学生。
例1-C4以C4为例，Sdept=‘CS’ANDSage>
20,如果Sdept和Sage上都有索引，一种算法是：
分别用上面的两种方法分别找到Sdept=‘CS’的一组元组指针和Sage>
20的另一组元组指针，求这两组指针的交集，再到student表中检索，就得到计算机系年龄大于20的学生。
另一种算法是：
找到Sdept=‘CS’一组元组指针，通过这些元组指针到student表中检索，并对得到的元组检查另一些选择条件是否满足，把满足条件的元组作为结果输出。
2.2.2连接操作的实现
连接操作是查询处理中最耗时的操作之一。
不失一般性，本文只讨论等值连接最常用的实现算法。
例2SELECT*FROMStudent，SCWHEREStudent.Sno=SC.Sno;
（1）嵌套循环方法
这是最简单可行的算法。
对外层循环（student）的每一个元组（s）,检索内层循环（SC）中的每一个元组（sc）,并检查这两个元组在连接属性（sno）上是否相等。
如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止。
（2）排序-合并方法
这也是最常用的算法，尤其适合连接的诸表已经排好序的情况。
用排序-合并连接方法的步骤是：
如果连接的表没有排好序，首先对Student表和SC表按连接属性Sno排序；
取student中的第一个Sno，依次扫描SC表中具有相同的Sno的元组；
把它们连接起来；
当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组；
再扫描SC表中具有相同的Sno的元组，把它们连接起来。
重复上述步骤直到Student表扫描完。
这样Student表和SC表都只要扫描一遍。
当然，如果2个表原来无序，执行时间要加上对两个表的排序时间。
即使这样，对于2个大表，先排序后使用sort-mergejoin方法执行连接，总的时间一般仍会大大减少。
（3）索引连接方法
用索引连接方法的步骤是：
在SC表上建立属性Sno的索引，如果原来没有的话；
对Student中的每一个元组，由Sno值通过SC的索引查找相应的SC元组；
把这些SC元组和Student表中的元组连接起来。
循环执行
，直到Student表中的元组处理完为止。
（4）HashJoin方法
属性作为hash码，用同一个hash函数把R和S中的元组散列到同一个hash文件中。
第一步，划分阶段，对包含较少元组的表进行一遍处理，把它的元组按hash函数分散到hash表的桶中；
第二步，试探阶段，也称为连接阶段，对另一表（S）进行一遍处理，把S的元组散列到适当的hash桶中，并把元组与桶中所有来自R并与之相匹配的元组连接起来。
3SQL查询处理优化方法
查询优化在关系数据库系统中有着非常重要的地位，关系数据库系统和非过程化的SQL之所以能取得巨大的成功，关键得益于查询优化技术的发展。
关系查询优化是影响RDBMS性能的关键因素。
查询优化既是RDBMS实现的关键又是关系数据库的优点所在。
它减轻了用户选择存取路径的负担。
用户只要提出“干什么”，不必指出“怎么干”。
对比一下非关系系统中的情况：
用户使用过程化的语言表达查询要求，执行何种记录级的操作，以及操作的序列是由用户而不是由系统来决定的。
因此用户必须了解存取路径，系统要提供用户选择存取路径的手段，查询效率由用户的存取策略决定。
如果用户做了不当的选择，系统是无法对此加以改进的。
这就要求用户有较高的数据库技术和程序设计水平。
下面介绍几种常用的查询优化方法。
3.1基于索引的优化
（1）索引定义
索引是一个单独的、物理的数据库结构。
它是根据表中一列或若干列，按照一定顺序建立的列值与记录行之间的对应关系表。
索引是依赖于表建立的，它包含索引键值及指向数据所在页面和行的指针。
一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面，索引就存放在索引页面上。
通常，索引页面相对于数据页面来说要小得多。
当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，然后再直接通过指针从数据页面中读取数据。
索引可以提供对一个表中的数据的有效访问，它可以用于加速数据的检索和强制唯一性限制。
但是，不应该在每一个列上都建立索引，因为构造索引需要占用一定的系统资源，降低更新的速度。
而且，插入、删除或更新一个索引列中的数据比非索引列中的数据要花费更长的时间。
（2）索引的作用
索引是加快数据检索的一种数据库结构，使得数据查询时不必扫描整个数据库就能迅速查到想要的数据。
具体如下5个方面：
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化器隐藏，提高系统的性能。
（3）索引的类型
如果一个表没有创建索引，则数据行不按任何特定的顺序存储，这种结构称为堆集。
在SQLServer2000的数据库中，按存储结构的不同将索引分为两类：
簇索引（ClusteredIndex）和非簇索引（NonclusteredIndex）。
1.簇索引
簇索引对表的数据行的键值进行排序，然后再存储有用的数据记录。
由于簇索引对表中的数据一一进行了排序，因此用簇索引查找数据很快。
但由于簇索引将表中的所有数据完全重新排列了，它所需要的空间也就特别大，大约相当于表中数据所占空间的120%。
表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。
2.非簇索引
非簇索引具有完全独立于数据行的结构，使用非簇索引不用对表的数据行的键值进行排序。
非簇索引的B-树叶节点存储了组成非簇索引的键值和行定位器（从索引行指向数据行的指针称为行定位器），行定位器的结构和存储内容取决于数据的存储方式，如果数据是以索引方式存储的则行定位器中存储的是簇索引的索引键；
如果不是以索引方式存储的，这种方式称为堆存储方式（HeapStructure）,则行定位器中存储的是指向数据行的指针。
非簇索引将行定位器的键值用一定的方式排序，这个顺序与表的行在数据页中的排序是不匹配的。
由于非簇索引使用索引页存储，因此簇索引需要更多的空间，且检索效率较低。
但一个表只能建一个簇索引，当用户需要建立多个索引时，就需要使用非簇索引了。
从理论上讲，一个表最多可以建248个非簇索引。
对于何时使用簇索引、何时使用非簇索引如表3-1所示
表3-1使用簇索引或非簇索引的时机
动作描述
使用簇索引
使用非簇索引
列经常被分组排序
应
返回某范围内的数据
不应
一个或极少不同值
小数目的不同值
大数目的不同值
频繁更新的列
外键列
主键列
频繁修改索引列
（4）索引的建立与删除
一般来说，建立与删除索引由数据库管理员DBA或表的属主（owner），即建表的人负责完成。
系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。
1.建立索引
在SQL语言中，建立索引使用CREATEINDEX语句，其一般格式为：
CREATE[UNIQUE][CLUSTER]INDEX<
索引名>
ON<
表名>
（<
列名>
[<
次序>
][，<
]]…）
其中，<
是要建索引的基本表的名字。
索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。
每个<
后面还可以用<
指定索引值的排列次序，可选ASC（升序）或DESC（降序），缺省值为ASC。
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER表示要建立的索引是聚簇索引。
例1CREATECLUSTERINDEXStusnameONStudent（Sname）;
这条语句是在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将会按照Sname值的升序存放。
例2CREATEUNIQUEINDEXStusnoONStudent（Sno）;
CREATEUNIQUEINDEXCouoONStudent（o）;
CREATEUNIQUEINDEXSoONStudent（SnoASC,oDESC）;
这三条语句是为学生-课程数据库中的Student，Course，SC3个表建立索引。
其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。
2.删除索引
索引一经建立，就由系统使用和维护它，不需用户干预。
建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费很多时间来维护索引，从而降低了查询效率。
这时可以删除一些不必要的索引。
在SQL中，删除索引使用DROPINDEX语句，其一般格式为
DROPINDEX<
例3删除Student表的Stusname索引。
DROPINDEXStusname;
删除索引时，系统会同时从数据字典中删去有关该索引的描述。
3.2SQL语句优化
使用索引可以有效的提高查询速度，但是SQL语句是对数据库操作的唯一途径，程序的执行最终都归结为SQL语句的执行，所以SQL语句的执行效率对数据库系统的性能起了决定性的作用。
所以我们不但要会写SQL语句，还要写出性能优良的SQL语句。
对于优化SQL语句，本主要就避免相关子查询、where字句的优化以及几个表的连接条件这几个方面进行阐述。
3.2.1where字句优化
在where子句中优化SQL语句是SQL语句优化的重要部分，它包括很多内容，这里只介绍几种常用的优化原则。
1.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
selectidfromtwherenumisnull
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
selectidfromtwherenum=0
2.应尽量避免在where子句中使用!
=或<
>
操作符，否则将引擎放弃使用索引而进行全表扫描。
优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
3.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
selectidfromtwherenum=10ornum=20
可以这样查询：
selectidfromtwherenum=10
unionall
selectidfromtwherenum=20
4.in和notin也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。
如：
selectidfromtwherenumin（1,2,3）
对于连续的数值，能用between就不要用in了：
selectidfromtwherenumbetween1and3
5.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
SELECT*FROMT1WHEREF1/2=100
应改为:
SELECT*FROMT1WHEREF1=100*2
SELECT*FROMRECORDWHERESUBSTRING（CARD_NO,1,4）=’5378’
SELECT*FROMRECORDWHERECARD_NOLIKE‘5378%’
SELECTmember_number,first_name,last_nameFROMmembers
WHEREDATEDIFF（yy,datofbirth,GETDATE（））>
21
WHEREdateofbirth<
DATEADD（yy,-21,GETDATE（））
即：
任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
6.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
selectidfromtwheresubstring（name,1,3）='
abc'
--name以abc开头的id
selectidfromtwheredatediff（day,createdate,'
20XX-11-30'
）=0--‘20XX-11-30’生成的id
selectidfromtwherenamelike'
abc%'
selectidfromtwherecreatedate>
='
andcreatedate<
'
20XX-12-1'
7.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
8.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
9.很多时候用exists是一个好的选择：
electnumfromawherenumin（selectnumfromb）
用下面的语句替换：
selectnumfromawhereexists（select1frombwherenum=a.num）
3.2.2避免相关子查询
如果在主查询和WHERE子句中的查询中同时出现了一个列的标签，这样就会使主查询的列值改变后，子查询也必须重新进行一次查询。
因为查询的嵌套层次越多，查询的效率就越低，所以我们应当避免子查询。
如果无法避免，就要在查询的过程中过滤掉尽可能多的子查询。
例如首先使用子查询实现查询要求（查询开销为87%）
SELECTSname
FROMSTUDENT
WHEREEXISTS（SELECT*FROMTABLEC
WHERECARDID=C.CARDIDANDBALANCE=200）
然后不使用子查询实现查询要求（查询开销为13%）
SELECTSname
FROMTABLES
JOINTABLECONS.CARDID=C.CARDID
WHEREBALANCE=200
3.2.3优化表的连接条件
在某种情况下，两个表之间可能有不只一个连接条件，这时在WHERE子句中充分使用连接条件，有可能大大提高查询速度。
例：
SELECTSUM（A.AMOUNT）FROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NO
SELECTSUM（A.AMOUNT）FROMACCOUNTA,CARDBWHEREA.CARD_NO=B.CARD_NOANDA.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句执行快得多。
3.2.4其他SQL语句优化
除了上面几种比较典型的几种方法，还有几种优化方法也能发挥提高查询效率的作用。
1.能用DISTINCT的就不用GROUPBY。
SELECTOrderIDFROMDetailsWHEREUnitPrice>
10GROUPBYOrderID
可改为：
SELECTDISTINCTOrderIDFROMDetailsWHEREUnitPrice>
10
2.能用UNIONALL就不要用UNION
UNIONALL不执行SELECTDISTINCT函数，这样就会减少很多不必要的资源
3.尽量不要用SELECTINTO语句。
SELECTINOT语句会导致表锁定，阻止其他用户访问该表。
4.IN、OR子句常会使用工作表，使索引失效。
如果不产生大量重复值，可以考虑把子句拆开。
拆开的子句中应该包含索引。
5.SETSHOWPLAN_ALLON查看执行方案。
DBCC检查数据库数据完整性。
DBCC（DataBaseConsistencyChecker）是一组用于验证SQLServer数据库完整性的程序。
6.慎用游标
在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标并进行操作，这样可使性能得到明显提高。
上面我们讲述的是一些基本的提高查询速度的方法,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。
最好的方法当然是测试，看实现相同功能的SQL语句哪个执行时间最少，但是如果数据库中数据量很少，是比较不出来的，这时可以用查看执行计划，即：
把实现相同功能的多条SQL语句拷到查询分析器，按CTRL+L查看所利用的索引以及表扫描次数（这两个对性能影响最大），总体上看成本百分比即可。
3.3其他优化方法
数据库的查询优化方法除了索引和优化SQL语句还有其他的方法，其他方法的合理使用同样也能很好的对数据库查询起到优化作用。
我们就来列举几种简单实用的方法。
3.3.1避免或简化排序
应当简化或避免对大型表进行重复的排序。
当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。
以下是一些影响因素:
●索引中不包括一个或几个待排序的列;
●groupby或orderby子句中列的次序与索引的次序不一样;
●排序的列来自不同的表。
为了避免不必要的排序，就要正确地增减索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。
如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。
3.3.2使用临时表
临时表中的行比主表中的行要少，而且物理顺序就是所要求的顺序，减少磁盘的I/O操作，查询工作量可以大幅减少。
在表的一个子集进行排序并创建临时表，也能实现加速查询。
在一些情况下这样可以避免多重排序操作。
但所创建的临时表的行要比主表的行少，其物理顺
升级会员 