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

1、本科毕业论文（设计）i数据库数据库 sql 查询处理及其优化方法的研究查询处理及其优化方法的研究摘要摘要：随着计算机技术的发展和各种应用软件的广泛使用，各种应用数据也随着日常工作而迅速增长，数据管理的重要性也日益显著，各种基于数据库的联机事务处理以及联机分析处理已经成为数据管理中最重要的部分。大量的数据管理应用软件中，针对数据库的各种操作，查询操作所占的比重最大，如果在查询过程中使用高效的查询策略，往往可以减少查询代价，缩短查询时间，提高查询效率。论文基于sql 语言和对数据各种操作分析的基础上，探讨了数据库 sql 查询处理的内容和过程，详细的研究了基于 sql 查询优化的一些策略，其中主要

2、包括索引查询、sql 语句优化其他一些常用优化方法。 关键词：关键词：数据库 查询优化 索引 sql 语句本科毕业论文（设计）ii the research of database sql query processing and optimized method abstract: with the development of computer technology and various application software widely used, various application data also grow rapidly, the importance of data

3、management increases markedly too, various processes based on database online transaction processing and on-line analytical processing have become the most important parts of data management. among a lot of data management application software, pointing at various operations in the database, the pro

4、portion of the query operation is the biggest, if using efficient query execution, it can reduce cost, decrease query time and improve the query efficiency. based on the sql language and the analysis of various operations in database, this paper probes into the database sql query processing and deta

5、ils some strategies which based on the content and process of sql query optimization , these strategies mainly include index inquiry, sql optimization and other common optimization methods. key words：database；query optimization； index ； sql statements 本科毕业论文（设计）iii目目 录录1 绪论绪论.12 关系数据库查询处理关系数据库查询处理.2

6、2.1 查询处理步骤.22.2 实现查询操作的算法示例.42.2.1 选择操作的实现.42.2.2 连接操作的实现.53 sql 查询处理优化方法查询处理优化方法.63.1 基于索引的优化.63.2 sql 语句优化.93.2.1 where字句优化.93.2.2 避免相关子查询 .113.2.3 优化表的连接条件.113.2.4 其他 sql 语句优化 .123.3 其他优化方法.123.3.1 避免或简化排序 .133.3.2 使用临时表.133.3.3 优化表中数据类型.143.3.4 用排序来取代非顺序存取 .143.3.5 数据服务器存储 .144 实例分析实例分析.144.1 实验

7、环境.144.2 案例数据库.154.3 具体实例.16结束语结束语.20致谢致谢.21参考文献参考文献.22本科毕业论文（设计）第 1 页1 绪论绪论 到如今，几乎所有应用系统的开发都离不开数据库，通过查询数据库就可以有效的得到想要的数据。但是，现实中许多数据库开发人员在利用一些前端数据库开发工具开发数据库应用程序时只注重用户界面的华丽，并不注重查询效率，导致所开发出来的应用系统中查询时间长，响应速度慢，甚至查询结果不够准确等，系统工作效率低下，资源浪费严重。究其原因，一是硬件设备（如 cpu、磁盘）的存取速度跟不上，内存容量不够大；另一方面是数据查询方法不适当，抑或是没有进行数据查询优化。

8、许多数据库开发人员认为查询优化是 dbms（数据库管理系统）的任务，与程序员所编写的 sql 语句关系不大，这是不对的，一个好的查询方法往往可以使程序性能提高数十倍。在实际的数据库产品（如 oracle、sybase、sql server 2000 等）的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表中所得到的信息来估计不同的查询方法代价，然后选择一个较优的规则。虽然现在的数据库产品在数据查询优化方面已经做得越来越好，但由于用户提交的 sql 语句是查询优化的基础，因此用户所写语句的优劣至关重要。2 关系数据库查询处理关系数据库查询处理要研究查询优化就必须知道数据库查询处理过程

9、，本节阐述了关系数据库（rdbms）的查询处理步骤，并介绍了查询处理的任务是把用户提交给 rdbms 的查询语句转换为高效的执行计划。2.1 查询处理步骤查询处理步骤 rdbms 查询处理过程可以分为四个阶段：查询分析、查询检查、查询优化和查询执行，如图 2-1 所示。(1) 查询分析查询分析是查询处理的第一个阶段，主要任务是对查询语句进行扫描、词法分析和语法分析。从查询语句中识别出语言符号，sql 关键字、属性名和关系名等，并且进行语法检查和语法分析，即判断查询语句是否符合 sql 语法规则。(2) 查询检查查询检查是根据数据字典对合法的 sql 查询语句进行语义检查，即检查语句中的数据库对

10、象，如属性名、关系名，是否存在和是否有效等。还要根据数据字典中的用户权限和完本科毕业论文（设计）第 2 页整性约束对用户的存取权限进行检查。如果该用户没有相应的访问权限或违反了完整性约束，就拒绝执行该查询操作。检查通过后便把 sql 查询语句转换成等价的关系代数表达式。rdbms 一般都用查询树（query tree）,也称为语法分析树，来表示扩展的关系代数表达式。这个过程中要把数据库对象的外部名称转换为内部表示。词法分析语法分析语义分析符号名转换安全性检查完整性检查查询树（query tree）代数优化物理优化等执行策略描述代码生成查询计划的执行代码数据库数据字典查询语句查询分析查询检查查询

11、优化查询执行图 2-1 查询处理步骤(3) 查询优化每个查询语句都会有很多可供选择的执行策略和操作算法，查询优化（query optimization）就是选择一个高效的查询处理策略。查询优化有许多种方法。按照优化的层次一般可以分为代数优化和物理优化。代数优化是指关系代数表达式的优化，即按照一定的规本科毕业论文（设计）第 3 页则，改变代数表达式中操作的次序和组合，使查询执行更高效；物理优化则是指存取路径和底层操作算法的选择。选择的依据可以是基于规则的，也可以基于代价的，还可以基于语义的。实际 rdbms 中的查询优化器都综合了运用了这些优化技术，以获得最好的查询优化效果。(4) 查询执行 查

12、询执行就是依据优化器得到的执行策略生成查询计划，由代码生成器（code generator）生成执行这个查询计划的代码。2.2 实现查询操作的算法示例实现查询操作的算法示例选择操作和连接操作是查询操作的两个典型操作，每一种操作有多种执行这个操作的算法，下面探讨实现这两种操作的几个主要算法。2.2.1 选择操作的实现选择操作的实现众所周知 select 语句功能十分强大，有许多选项，因此实现的算法和优化策略也很复杂。下面以简单的选择操作为例讲述典型的实现方法。例 1 select * from student where；考虑的几种情况：c1：无条件；c2：sno=200215121；c3：sa

13、ge20；c4: sdept=cs and sage20；（1）简单的全表扫描方法对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。对于小表，这种方法简单有效。对于大表顺序扫描十分费时，效率很低。（2）索引（或散列）扫描方法如果选择条件中的属性上有索引（例如 b+树索引或 hash 索引） ，可以用索引扫描方法。通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在要查询的基本表中本科毕业论文（设计）第 4 页找到元组。例 1-c2 以 c2 为例，sno=200215121，并且 sno 上有索引，则可以通过使用索引得到 sno 为200215

14、121元组的指针，然后通过元组指针在 student 表中检索等到该学生。例 1-c3 以 c3 为例，sage20，并且 sage 上有 b+树索引，则可以使用 b+树索引找到sage=20 的索引项，以此为入口在 b+树的顺序集上得到 sage20 的所有元组指针，然后通过这些元组指针到 student 表中检索所有年龄大于 20 的学生。例 1-c4 以 c4 为例，sdept=csand sage20,如果 sdept 和 sage 上都有索引，一种算法是：分别用上面的两种方法分别找到 sdept=cs的一组元组指针和 sage20 的另一组元组指针，求这两组指针的交集，再到 stud

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

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

17、扫描完。这样 student 表和 sc 表都只要扫描一遍。当然，如果 2 个表原来无序，执行时间要加上本科毕业论文（设计）第 5 页对两个表的排序时间。即使这样，对于 2 个大表，先排序后使用 sort-merge join 方法执行连接，总的时间一般仍会大大减少。（3）索引连接方法用索引连接方法的步骤是：在 sc 表上建立属性 sno 的索引，如果原来没有的话；对 student 中的每一个元组，由 sno 值通过 sc 的索引查找相应的 sc 元组；把这些 sc 元组和 student 表中的元组连接起来。循环执行，直到 student 表中的元组处理完为止。（4）hash join 方

18、法属性作为 hash 码，用同一个 hash 函数把 r 和 s 中的元组散列到同一个 hash 文件中。第一步，划分阶段，对包含较少元组的表进行一遍处理，把它的元组按 hash 函数分散到 hash表的桶中；第二步，试探阶段，也称为连接阶段，对另一表（s）进行一遍处理，把 s 的元组散列到适当的 hash 桶中，并把元组与桶中所有来自 r 并与之相匹配的元组连接起来。3 sql 查询处理优化方法查询处理优化方法查询优化在关系数据库系统中有着非常重要的地位，关系数据库系统和非过程化的 sql之所以能取得巨大的成功，关键得益于查询优化技术的发展。关系查询优化是影响 rdbms性能的关键因素。查询

19、优化既是 rdbms 实现的关键又是关系数据库的优点所在。它减轻了用户选择存取路径的负担。用户只要提出“干什么” ，不必指出“怎么干” 。对比一下非关系系统中的情况：用户使用过程化的语言表达查询要求，执行何种记录级的操作，以及操作的序列是由用户而不是由系统来决定的。因此用户必须了解存取路径，系统要提供用户选择存取路径的手段，查询效率由用户的存取策略决定。如果用户做了不当的选择，系统是无法对此加以改进的。这就要求用户有较高的数据库技术和程序设计水平。下面介绍几种常用的查询优化方法。3.13.1 基于索引的优化基于索引的优化（1）索引定义索引是一个单独的、物理的数据库结构。它是根据表中一列或若干列

20、，按照一定顺序建立的列值与记录行之间的对应关系表。索引是依赖于表建立的，它包含索引键值及指向数据所在页面和行的指针。一个表的存本科毕业论文（设计）第 6 页储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面，索引就存放在索引页面上。通常，索引页面相对于数据页面来说要小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，然后再直接通过指针从数据页面中读取数据。索引可以提供对一个表中的数据的有效访问，它可以用于加速数据的检索和强制唯一性限制。但是，不应该在每一个列上都建立索引，因为构造索引需要占用一定的系统资源，降低更新的速度。而且，插入、删除或更新一个索引列中的

21、数据比非索引列中的数据要花费更长的时间。（2）索引的作用索引是加快数据检索的一种数据库结构，使得数据查询时不必扫描整个数据库就能迅速查到想要的数据。具体如下 5 个方面： 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。 通过使用索引，可以在查询的过程中，使用优化器隐藏，提高系统的性能。（3）索引的类型如果一个表没有创建索引，则数据行不按任何特定的顺序存储，这种结构称为堆集。在sq

22、l server 2000 的数据库中，按存储结构的不同将索引分为两类：簇索引（clustered index）和非簇索引(nonclustered index)。1. 簇索引簇索引对表的数据行的键值进行排序，然后再存储有用的数据记录。由于簇索引对表中的数据一一进行了排序，因此用簇索引查找数据很快。但由于簇索引将表中的所有数据完全重新排列了，它所需要的空间也就特别大，大约相当于表中数据所占空间的 120%。表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。2. 非簇索引非簇索引具有完全独立于数据行的结构，使用非簇索引不用对表的数据行的键值进行排序。非簇索引的 b-树叶节点存

23、储了组成非簇索引的键值和行定位器（从索引行指向数据行的指针称为行定位器） ，行定位器的结构和存储内容取决于数据的存储方式，如果数据是以索引方式存储的则行定位器中存储的是簇索引的索引键；如果不是以索引方式存储的，这种方式称为堆存储方式（heap structure）,则行定位器中存储的是指向数据行的指针。非簇索引将行定位器的键值用一定的方式排序，这个顺序与表的行在数据页中的排序是不匹配的。本科毕业论文（设计）第 7 页由于非簇索引使用索引页存储，因此簇索引需要更多的空间，且检索效率较低。但一个表只能建一个簇索引，当用户需要建立多个索引时，就需要使用非簇索引了。从理论上讲，一个表最多可以建 248

24、 个非簇索引。对于何时使用簇索引、何时使用非簇索引如表 3-1 所示表 3-1 使用簇索引或非簇索引的时机动作描述使用簇索引使用非簇索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引列不应应（4）索引的建立与删除一般来说，建立与删除索引由数据库管理员 dba 或表的属主（owner） ，即建表的人负责完成。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。1.建立索引在 sql 语言中，建立索引使用 create index 语句，其一般格式为： cre

25、ate unique cluster index on （，）其中，是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，可选asc（升序）或 desc（降序） ，缺省值为 asc。unique 表明此索引的每一个索引值只对应唯一的数据记录。cluster 表示要建立的索引是聚簇索引。例 1 create cluster index stusname on student(sname);这条语句是在 student 表的 sname（姓名）列上建立一个聚簇索引，而且 student 表中的本科毕业论文（设计）第 8 页记录将会按

26、照 sname 值的升序存放。例 2 create unique index stusno on student(sno); create unique index coucno on student(cno); create unique index scno on student(sno asc,cno desc);这三条语句是为学生-课程数据库中的 student，course，sc 3 个表建立索引。其中student 表按学号升序建唯一索引，course 表按课程号升序建唯一索引，sc 表按学号升序和课程号降序建唯一索引。2.删除索引索引一经建立，就由系统使用和维护它，不需用户干预。

27、建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费很多时间来维护索引，从而降低了查询效率。这时可以删除一些不必要的索引。在 sql 中，删除索引使用 drop index 语句，其一般格式为 drop index ；例 3 删除 student 表的 stusname 索引。 drop index stusname;删除索引时，系统会同时从数据字典中删去有关该索引的描述。3.2 sql 语句语句优化优化使用索引可以有效的提高查询速度，但是 sql 语句是对数据库操作的唯一途径，程序的执行最终都归结为 sql 语句的执行，所以 sql 语句的执行效率对数据库系统的性能起了决定性的

28、作用。所以我们不但要会写 sql 语句，还要写出性能优良的 sql 语句。对于优化 sql 语句，本论文主要就避免相关子查询、where 字句的优化以及几个表的连接条件这几个方面进行阐述。3.2.1 where 字句优化字句优化在 where 子句中优化 sql 语句是 sql 语句优化的重要部分，它包括很多内容，这里只介绍几种常用的优化原则。1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，

29、然后这样查询：本科毕业论文（设计）第 9 页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 num=10union allselect id from t where num=204.in

30、 和 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 * from t1 where f1=100*2select * from record where

31、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 dateofbirth =2005-11-30 and createdate 10 group by orde

32、rid 可改为： 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 plan_all on 查看执行方案。dbcc 检查数据库数据完整性。db

33、cc(database consistency checker)是一组用于验证 sql server 数据库完整性的程序。本科毕业论文（设计）第 12 页6.慎用游标在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标并进行操作，这样可使性能得到明显提高。上面我们讲述的是一些基本的提高查询速度的方法,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试，看实现相同功能的 sql 语句哪个执行时间最少，但是如果数据库中数据量很少，是比较不出来的，这时可以用查看执行计划，即：把实现相同功能的多条 sql 语句拷到查询分析器，按 ctr

34、l+l 查看所利用的索引以及表扫描次数（这两个对性能影响最大） ，总体上看成本百分比即可。3.3 其他优化方法其他优化方法 数据库的查询优化方法除了索引和优化 sql 语句还有其他的方法，其他方法的合理使用同样也能很好的对数据库查询起到优化作用。我们就来列举几种简单实用的方法。 3.3.1 避免或简化排序避免或简化排序 应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素:索引中不包括一个或几个待排序的列;group by 或 order by 子句中列的次序与索引的次序不一样;排序的列来自不同的表。为了避免不必要的排序，

35、就要正确地增减索引，合理地合并数据库表(尽管有时可能影响表的规范化，但相对于效率的提高是值得的)。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。3.3.2 使用临时表使用临时表临时表中的行比主表中的行要少，而且物理顺序就是所要求的顺序，减少磁盘的 i/o 操作，查询工作量可以大幅减少。在表的一个子集进行排序并创建临时表，也能实现加速查询。在一些情况下这样可以避免多重排序操作。但所创建的临时表的行要比主表的行少，其物理顺序就是所要求的顺序，这样就减少了输入和输出，降低了查询的工作量，提高了效率，而且临时表的创建并不会反映主表的修改。 本科毕业论文（设计）第 13 页但是对临时表的

36、使用也要有一些规则，主要有：1.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引） 。2.避免频繁创建和删除临时表，以减少系统表资源的消耗。3.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。4.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后 insert。5.如果使用到了临时表，在存储过

37、程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 3.3.3 优化表中数据类型优化表中数据类型对表中数据最好使用兼容的数据类型，因为数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如 float 和 int、char 和 varchar、binary 和 varbinary 是不兼容的。下面用例子来阐述select name from employee where salary 60000 在这条语句中,如 salary 字段是 money 型的,则优化器很难对其进行优化,因为 60000

38、 是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。3.3.4 用排序来取代非顺序存取用排序来取代非顺序存取 磁盘存取臂的来回移动使得非顺序磁盘存取变成了最慢的操作。但是在 sql 语句中这个现象被隐藏了，这样就使得查询中进行了大量的非顺序查询，降低了查询速度，对于这个现象还没有很好的解决方法，只能依赖于数据库的排序能力来替代非顺序的存取。有些时候，用数据库的排序能力来替代非顺序的存取能改进查询效率。3.3.5 数据服务器存储数据服务器存储尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过并且被组织到一个执行规划里、且存储在数据库中

39、的 sql 语句，是控制流语言的集合，速度当然快。本科毕业论文（设计）第 14 页4 实例分析实例分析第三章研究了数据库查询处理过程以及一些 sql 优化方法，本章将在第三章研究的基础上，利用案例数据库进行对上述三类 sql 查询优化的有效性进行实验验证，并对实验结果分析。4.1 实验环境实验环境操作系统：windows xp数据库管理软件：sql server 2000内存：2gcpu：intel(r) pentium(r) dual cpu e2160 1.80ghz4.2 案例数据库案例数据库为了进行实验分析，本文建立了案例数据库，包括 company，house、sale 三张表，如表

40、 4-1、4-2、4-3 所示，案例数据库的数据概况如表 4-4 所示。表 4-1 company 数据表定义列名数据结构长度说明company_idint4编号，主键company_namenvarchar255公司名称company_distrisct_idint4公司所在行政区域编号，外键company_attribute_idint4公司所在性质类别编号，外键表 4-2 house 数据表定义列名数据结构长度说明house _idint4编号，主键house_ namenvarchar255楼盘名称occupy_areafloat8楼盘占地面积afforest_areafloat8楼盘

41、绿化面积build_costmoney8楼盘建筑成本sum_areafloat8楼盘建筑面积sale_able_areafloat8楼盘可售面积company_idint4楼盘公司编号，外键section_idint4楼盘所在地理区域编号，外键type_idint4楼盘类型编号，外键distrisct_idint4楼盘所在行政区域编号，外键本科毕业论文（设计）第 15 页sum_ build_costmoney8楼盘总建筑成本表 4-3 sale 数据表定义列名数据结构长度说明sale _idint4编号，主键house_ idint4楼盘编号，外键time_idint4楼盘销售时间编号，外键

42、sale_areafloat8楼盘销售面积sale_pricefloat8楼盘销售价格案例数据的数据该库如表 4-4 所示表 4-4 数据库数据量概况数据表名数据量company91house300sale35904.3 具体实例具体实例（1）基于索引优化实验索引优化是数据库 sql 查询优化的重要方法，为了证明其有效性，本实验在 company和 house 表的 company_id 列上建立了索引，以下面 sql 语句为例，比较索引建立前后执行时间的变化。 sql 语句：select getdate()select * from company,house where company.c

43、ompany_id=house.company_idselect getdate()此语句的功能是先获取系统的当前时间，然后查询 company 和 house 表中公司编号（company_id）相等的元组，并把两表中这些元组的信息连接起来返回，最后再获取系统当前时间，两次时间相减就是 sql 语句执行时间。在 company_id 列上未建立索引时系统执行情况如图 4-1 所示本科毕业论文（设计）第 16 页图 4-1 未建立索引系统执行情况图由图 4-1 可知，sql 语句执行前的系统时间为 19:29:36.013，sql 语句执行后的系统时间为 19:29:36.030，两次时间相减

44、就是 0.017s，故 sql 语句执行时间为 0.017s。在 company_id 列上建立索引时系统执行情况如图 4-4 所示图 4-2 建立索引系统执行情况图由图 4-2 可知，sql 语句执行前的系统时间为 19:45:01.060，sql 语句执行后的系统时间为 19:45:01.060，两次时间相减就是 0.000s.由上实验可以知道，建立索引时，sql 语句执行时间约为 0.000s，而未建立索引时系统的查询时间为 0.017s,很明显建立索引可以显著的提高系统执行效率，但是要是建立了不适当的索引，不但会浪费系统资源，还会降低查询性能。（2）嵌套优化实验本科毕业论文（设计）第

45、17 页由第三章的理论研究可知，当查询语句中包括嵌套查询时，会降低查询效率。本实验分别用嵌套的 sql 语句和没有嵌套的 sql 语句来实现相同的功能，比较这两者的执行时间。用 in 的嵌套 sql 语句查询sql 语句为：select getdate()select * from company,house where company.company_id in （select house.company_id from house）select getdate() 此语句的功能是先获取系统的当前时间，然后查询 company 和 house 表中公司编号（company_id）相等的元组，

46、并把两表中这些元组的信息连接起来返回，最后再获取系统当前时间，两次时间相减就是 sql 语句执行时间。系统执行情况如图 4-3 所示图 4-3 嵌套 sql 查询系统执行情况图由图 4-3 可知，sql 语句执行前的系统时间为 19:54:40.513，sql 语句执行后的系统时间为 19:54:41.230，两次时间相减就是 0.717s，故 sql 语句执行时间为 0.717s。没有嵌套的 sql 语句查询sql 语句：select getdate()select * from company,house where company.company_id=house.company_ids

47、elect getdate()此语句的功能如，系统执行情况如图 4-4 所示本科毕业论文（设计）第 18 页图 4-4 嵌套 sql 查询系统执行情况图由图 4-4 可知，sql 语句执行前的系统时间为 19:55:56.043，sql 语句执行后的系统时间为 19:55:56.060，两次时间相减就是 0.017s，故 sql 语句执行时间为 0.017s。由上实验可以知道，用 in 实现嵌套查询时，sql 语句执行时间约为 0.717s，而不采用嵌套查询时，系统的执行时间为 0.017s, 由其可见，不使用 in 可以提高查询性能，因为用 in实现嵌套查询时会使系统无法使用索引,只能对表中

48、的数据全部搜索这将导致系统效率降低。（3）表达式优化实验由第三章可知在 where 子句中对字段进行表达式操作会降低查询性能，本实验分别在where 字句中进行表达式操作和不进行表达式操作，比较两者的执行时间。在 where 字句中未进行表达式操作 sql 语句：select getdate()select * from sale，house where sale.sale_area1500 andhouse.house_id=sale. house_id select getdate()该语句的功能是在 house 和 sale 两个表中找到 house_id 相等的元组，然后再在这些元组中找出楼盘销售面积大于 1500 的元组。系统执行情况如图 4-5 所示本科毕业论文（设计）第 19 页图 4-5 未进行表达式操作的系统执行情况图由图 4-5 可知，sql 语句执行前的系统时间为 20:40:08.640，sql 语句执行后的系统时间为 20:40:08.780，两次时间相减就是 0.140s，故 sql 语句执行时间为 0.140s在 where 字句中进行表达式操作sql 语句：select getdate()select * from sale，house where sale.