数据库SQL查询处理及其优化方法的研究 精品.docx

1、数据库SQL查询处理及其优化方法的研究 精品数据库SQL查询处理及其优化方法的研究1 绪论 到如今，几乎所有应用系统的开发都离不开数据库，通过查询数据库就可以有效的得到想要的数据。但是，现实中许多数据库开发人员在利用一些前端数据库开发工具开发数据库应用程序时只注重用户界面的华丽，并不注重查询效率，导致所开发出来的应用系统中查询时间长，响应速度慢，甚至查询结果不够准确等，系统工作效率低下，资源浪费严重。究其原因，一是硬件设备（如CPU、磁盘）的存取速度跟不上，内存容量不够大；另一方面是数据查询方法不适当，抑或是没有进行数据查询优化。许多数据库开发人员认为查询优化是DBMS（数据库管理系统）的任务

2、，与程序员所编写的SQL语句关系不大，这是不对的，一个好的查询方法往往可以使程序性能提高数十倍。在实际的数据库产品（如Oracle、Sybase、SQL Server 2000等）的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表中所得到的信息来估计不同的查询方法代价，然后选择一个较优的规则。虽然现在的数据库产品在数据查询优化方面已经做得越来越好，但由于用户提交的SQL语句是查询优化的基础，因此用户所写语句的优劣至关重要。2 关系数据库查询处理要研究查询优化就必须知道数据库查询处理过程，本节阐述了关系数据库（RDBMS）的查询处理步骤，并介绍了查询处理的任务是把用户提交给RDBM

3、S的查询语句转换为高效的执行计划。2.1 查询处理步骤 RDBMS查询处理过程可以分为四个阶段：查询分析、查询检查、查询优化和查询执行，如图2-1所示。(1) 查询分析查询分析是查询处理的第一个阶段，主要任务是对查询语句进行扫描、词法分析和语法分析。从查询语句中识别出语言符号，SQL关键字、属性名和关系名等，并且进行语法检查和语法分析，即判断查询语句是否符合SQL语法规则。(2) 查询检查查询检查是根据数据字典对合法的SQL查询语句进行语义检查，即检查语句中的数据库对象，如属性名、关系名，是否存在和是否有效等。还要根据数据字典中的用户权限和完整性约束对用户的存取权限进行检查。如果该用户没有相应

4、的访问权限或违反了完整性约束，就拒绝执行该查询操作。检查通过后便把SQL查询语句转换成等价的关系代数表达式。RDBMS一般都用查询树（query tree）,也称为语法分析树，来表示扩展的关系代数表达式。这个过程中要把数据库对象的外部名称转换为内部表示。图2-1 查询处理步骤(3) 查询优化每个查询语句都会有很多可供选择的执行策略和操作算法，查询优化（query optimization）就是选择一个高效的查询处理策略。查询优化有许多种方法。按照优化的层次一般可以分为代数优化和物理优化。代数优化是指关系代数表达式的优化，即按照一定的规则，改变代数表达式中操作的次序和组合，使查询执行更高效；物理

5、优化则是指存取路径和底层操作算法的选择。选择的依据可以是基于规则的，也可以基于代价的，还可以基于语义的。实际RDBMS中的查询优化器都综合了运用了这些优化技术，以获得最好的查询优化效果。(4) 查询执行 查询执行就是依据优化器得到的执行策略生成查询计划，由代码生成器（code generator）生成执行这个查询计划的代码。2.2 实现查询操作的算法示例选择操作和连接操作是查询操作的两个典型操作，每一种操作有多种执行这个操作的算法，下面探讨实现这两种操作的几个主要算法。2.2.1 选择操作的实现众所周知SELECT语句功能十分强大，有许多选项，因此实现的算法和优化策略也很复杂。下面以简单的选择

6、操作为例讲述典型的实现方法。例1 Select * from student where；考虑的几种情况：C1：无条件；C2：Sno=20XX15121；C3：Sage20；C4: Sdept=CS AND Sage20；（1）简单的全表扫描方法对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。对于小表，这种方法简单有效。对于大表顺序扫描十分费时，效率很低。（2）索引（或散列）扫描方法如果选择条件中的属性上有索引（例如B+树索引或Hash索引），可以用索引扫描方法。通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在要查询的基本表中找到元组。例

7、1-C2 以C2为例，Sno=20XX15121，并且Sno上有索引，则可以通过使用索引得到Sno为20XX15121元组的指针，然后通过元组指针在student表中检索等到该学生。例1-C3 以C3为例，Sage20，并且Sage上有B+树索引，则可以使用B+树索引找到Sage=20的索引项，以此为入口在B+树的顺序集上得到Sage20的所有元组指针，然后通过这些元组指针到student表中检索所有年龄大于20的学生。例1-C4 以C4为例，Sdept=CSAND Sage20,如果Sdept和Sage上都有索引，一种算法是：分别用上面的两种方法分别找到Sdept=CS的一组元组指针和Sag

8、e20的另一组元组指针，求这两组指针的交集，再到student表中检索，就得到计算机系年龄大于20的学生。另一种算法是：找到Sdept=CS一组元组指针，通过这些元组指针到student表中检索，并对得到的元组检查另一些选择条件是否满足，把满足条件的元组作为结果输出。2.2.2 连接操作的实现连接操作是查询处理中最耗时的操作之一。不失一般性，本文只讨论等值连接最常用的实现算法。例2 SELECT * FROM Student，SC WHERE Student.Sno=SC.Sno;（1）嵌套循环方法这是最简单可行的算法。对外层循环（student）的每一个元组（s）,检索内层循环（SC）中的每

9、一个元组（sc）,并检查这两个元组在连接属性（sno）上是否相等。如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止。（2）排序-合并方法这也是最常用的算法，尤其适合连接的诸表已经排好序的情况。用排序-合并连接方法的步骤是：如果连接的表没有排好序，首先对Student表和SC表按连接属性Sno排序；取student中的第一个Sno，依次扫描SC表中具有相同的Sno的元组；把它们连接起来；当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组；再扫描SC表中具有相同的Sno的元组，把它们连接起来。重复上述步骤直到Student表扫描完。这样Stude

10、nt表和SC表都只要扫描一遍。当然，如果2个表原来无序，执行时间要加上对两个表的排序时间。即使这样，对于2个大表，先排序后使用sort-merge join方法执行连接，总的时间一般仍会大大减少。（3）索引连接方法用索引连接方法的步骤是：在SC表上建立属性Sno的索引，如果原来没有的话；对Student中的每一个元组，由Sno值通过SC的索引查找相应的SC元组；把这些SC元组和Student表中的元组连接起来。循环执行，直到Student表中的元组处理完为止。（4）Hash Join方法属性作为hash 码，用同一个hash函数把R和S中的元组散列到同一个hash文件中。第一步，划分阶段，对包

11、含较少元组的表进行一遍处理，把它的元组按hash函数分散到hash表的桶中；第二步，试探阶段，也称为连接阶段，对另一表（S）进行一遍处理，把S的元组散列到适当的hash桶中，并把元组与桶中所有来自R并与之相匹配的元组连接起来。3 SQL查询处理优化方法查询优化在关系数据库系统中有着非常重要的地位，关系数据库系统和非过程化的SQL之所以能取得巨大的成功，关键得益于查询优化技术的发展。关系查询优化是影响RDBMS性能的关键因素。查询优化既是RDBMS实现的关键又是关系数据库的优点所在。它减轻了用户选择存取路径的负担。用户只要提出“干什么”，不必指出“怎么干”。对比一下非关系系统中的情况：用户使用过

12、程化的语言表达查询要求，执行何种记录级的操作，以及操作的序列是由用户而不是由系统来决定的。因此用户必须了解存取路径，系统要提供用户选择存取路径的手段，查询效率由用户的存取策略决定。如果用户做了不当的选择，系统是无法对此加以改进的。这就要求用户有较高的数据库技术和程序设计水平。下面介绍几种常用的查询优化方法。3.1 基于索引的优化（1）索引定义索引是一个单独的、物理的数据库结构。它是根据表中一列或若干列，按照一定顺序建立的列值与记录行之间的对应关系表。索引是依赖于表建立的，它包含索引键值及指向数据所在页面和行的指针。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面，

13、索引就存放在索引页面上。通常，索引页面相对于数据页面来说要小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，然后再直接通过指针从数据页面中读取数据。索引可以提供对一个表中的数据的有效访问，它可以用于加速数据的检索和强制唯一性限制。但是，不应该在每一个列上都建立索引，因为构造索引需要占用一定的系统资源，降低更新的速度。而且，插入、删除或更新一个索引列中的数据比非索引列中的数据要花费更长的时间。（2）索引的作用索引是加快数据检索的一种数据库结构，使得数据查询时不必扫描整个数据库就能迅速查到想要的数据。具体如下5个方面：通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

14、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。 通过使用索引，可以在查询的过程中，使用优化器隐藏，提高系统的性能。（3）索引的类型如果一个表没有创建索引，则数据行不按任何特定的顺序存储，这种结构称为堆集。在SQL Server 2000的数据库中，按存储结构的不同将索引分为两类：簇索引（Clustered Index）和非簇索引(Nonclustered Index)。1. 簇索引簇索引对表的数据行的键值进行排序，然后再存储有用的数据记录。

15、由于簇索引对表中的数据一一进行了排序，因此用簇索引查找数据很快。但由于簇索引将表中的所有数据完全重新排列了，它所需要的空间也就特别大，大约相当于表中数据所占空间的120%。表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。2. 非簇索引非簇索引具有完全独立于数据行的结构，使用非簇索引不用对表的数据行的键值进行排序。非簇索引的B-树叶节点存储了组成非簇索引的键值和行定位器（从索引行指向数据行的指针称为行定位器），行定位器的结构和存储内容取决于数据的存储方式，如果数据是以索引方式存储的则行定位器中存储的是簇索引的索引键；如果不是以索引方式存储的，这种方式称为堆存储方式（Heap

16、 Structure）,则行定位器中存储的是指向数据行的指针。非簇索引将行定位器的键值用一定的方式排序，这个顺序与表的行在数据页中的排序是不匹配的。由于非簇索引使用索引页存储，因此簇索引需要更多的空间，且检索效率较低。但一个表只能建一个簇索引，当用户需要建立多个索引时，就需要使用非簇索引了。从理论上讲，一个表最多可以建248个非簇索引。对于何时使用簇索引、何时使用非簇索引如表3-1所示表3-1使用簇索引或非簇索引的时机动作描述使用簇索引使用非簇索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频

17、繁修改索引列不应应（4）索引的建立与删除一般来说，建立与删除索引由数据库管理员DBA或表的属主（owner），即建表的人负责完成。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。1.建立索引在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为： CREATE UNIQUE CLUSTER INDEX ON （，）其中，是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，可选ASC（升序）或DESC（降序），缺省值为ASC。UNIQUE表明此索引的每一个索引值只对应唯一的数据记录

18、。CLUSTER表示要建立的索引是聚簇索引。例1 CREATE CLUSTER INDEX Stusname ON Student(Sname);这条语句是在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将会按照Sname值的升序存放。例2 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Couo ON Student(o); CREATE UNIQUE INDEX So ON Student(Sno ASC,o DESC);这三条语句是为学生-课程数据库中的Student，

19、Course，SC 3个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。2.删除索引索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费很多时间来维护索引，从而降低了查询效率。这时可以删除一些不必要的索引。在SQL中，删除索引使用DROP INDEX 语句，其一般格式为 DROP INDEX ；例3 删除Student表的Stusname索引。 DROP INDEX Stusname;删除索引时，系统会同时从数据字典中删去有关该索引的描述。3.2

20、SQL语句优化使用索引可以有效的提高查询速度，但是SQL语句是对数据库操作的唯一途径，程序的执行最终都归结为SQL语句的执行，所以SQL语句的执行效率对数据库系统的性能起了决定性的作用。所以我们不但要会写SQL语句，还要写出性能优良的SQL语句。对于优化SQL语句，本主要就避免相关子查询、where字句的优化以及几个表的连接条件这几个方面进行阐述。3.2.1 where字句优化在where子句中优化SQL语句是SQL语句优化的重要部分，它包括很多内容，这里只介绍几种常用的优化原则。1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：s

21、elect id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=02.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where

22、num=10union allselect id from t where num=204.in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 35.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FRO

23、M T1 WHERE F1=100*2SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=5378 应改为: SELECT * FROM RECORD WHERE CARD_NO LIKE 5378%SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE() 21 应改为: SELECT member_number, first_name, last_name FROM members WHERE dateofbir

24、th =20XX-11-30 and createdate 10 GROUP BY OrderID 可改为： SELECT DISTINCT OrderID FROM Details WHERE UnitPrice 102.能用UNION ALL就不要用UNION UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源 3.尽量不要用SELECT INTO语句。 SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。4.IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。5.SET SHOW

25、PLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。DBCC(DataBase Consistency Checker)是一组用于验证 SQL Server 数据库完整性的程序。6.慎用游标在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标并进行操作，这样可使性能得到明显提高。上面我们讲述的是一些基本的提高查询速度的方法,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试，看实现相同功能的SQL语句哪个执行时间最少，但是如果数据库中数据量很少，是比较不出来的，这时可以用查看执行计划，即：把实现相同功能的多条SQ

26、L语句拷到查询分析器，按CTRL+L查看所利用的索引以及表扫描次数（这两个对性能影响最大），总体上看成本百分比即可。3.3 其他优化方法 数据库的查询优化方法除了索引和优化SQL语句还有其他的方法，其他方法的合理使用同样也能很好的对数据库查询起到优化作用。我们就来列举几种简单实用的方法。 3.3.1 避免或简化排序 应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素:索引中不包括一个或几个待排序的列;group by或order by子句中列的次序与索引的次序不一样;排序的列来自不同的表。为了避免不必要的排序，就要正确地增减索引，合理地合并数据库表(尽管有时可能影响表的规范化，但相对于效率的提高是值得的)。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。3.3.2 使用临时表临时表中的行比主表中的行要少，而且物理顺序就是所要求的顺序，减少磁盘的I/O操作，查询工作量可以大幅减少。在表的一个子集进行排序并创建临时表，也能实现加速查询。在一些情况下这样可以避免多重排序操作。但所创建的临时表的行要比主表的行少，其物理顺