毕业设计（论文）-数据库SQL查询处理及其优化方法的研究.doc

本科毕业论文 设计 I 数据库数据库 SQL 查询处理及其优化方法的研究查询处理及其优化方法的研究 摘要摘要 随着计算机技术的发展和各种应用软件的广泛使用 各种应用数据也随着日常工作而迅速增长 数据管理的重要性也日益显著 各种基于数据库的联机事务处理以及联机分析处理已经成为数据管理中 最重要的部分 大量的数据管理应用软件中 针对数据库的各种操作 查询操作所占的比重最大 如果 在查询过程中使用高效的查询策略 往往可以减少查询代价 缩短查询时间 提高查询效率 论文基于 SQL 语言和对数据各种操作分析的基础上 探讨了数据库 SQL 查询处理的内容和过程 详细的研究了基 于 SQL 查询优化的一些策略 其中主要包括索引查询 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 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 proportion 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 details 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 绪论绪论 1 2 关系数据库查询处理关系数据库查询处理 2 2 1 查询处理步骤 2 2 2 实现查询操作的算法示例 4 2 2 1 选择操作的实现 4 2 2 2 连接操作的实现 5 3 SQL 查询处理优化方法查询处理优化方法 6 3 1 基于索引的优化 6 3 2 SQL 语句优化 9 3 2 1 WHERE字句优化 9 3 2 2 避免相关子查询 11 3 2 3 优化表的连接条件 11 3 2 4 其他 SQL 语句优化 12 3 3 其他优化方法 12 3 3 1 避免或简化排序 13 3 3 2 使用临时表 13 3 3 3 优化表中数据类型 14 3 3 4 用排序来取代非顺序存取 14 3 3 5 数据服务器存储 14 4 实例分析实例分析 14 4 1 实验环境 14 4 2 案例数据库 15 4 3 具体实例 16 结束语结束语 20 致谢致谢 21 参考文献参考文献 22 本科毕业论文 设计 第 1 页 1 绪论绪论 到如今 几乎所有应用系统的开发都离不开数据库 通过查询数据库就可以有效的得到 想要的数据 但是 现实中许多数据库开发人员在利用一些前端数据库开发工具开发数据库 应用程序时只注重用户界面的华丽 并不注重查询效率 导致所开发出来的应用系统中查询 时间长 响应速度慢 甚至查询结果不够准确等 系统工作效率低下 资源浪费严重 究其 原因 一是硬件设备 如 CPU 磁盘 的存取速度跟不上 内存容量不够大 另一方面是 数据查询方法不适当 抑或是没有进行数据查询优化 许多数据库开发人员认为查询优化是 DBMS 数据库管理系统 的任务 与程序员所编 写的 SQL 语句关系不大 这是不对的 一个好的查询方法往往可以使程序性能提高数十倍 在实际的数据库产品 如 Oracle Sybase SQL Server 2000 等 的高版本中都是采用基于代 价的优化方法 这种优化能根据从系统字典表中所得到的信息来估计不同的查询方法代价 然后选择一个较优的规则 虽然现在的数据库产品在数据查询优化方面已经做得越来越好 但由于用户提交的 SQL 语句是查询优化的基础 因此用户所写语句的优劣至关重要 2 关系数据库查询处理关系数据库查询处理 要研究查询优化就必须知道数据库查询处理过程 本节阐述了关系数据库 RDBMS 的查询处理步骤 并介绍了查询处理的任务是把用户提交给 RDBMS 的查询语句转换为高效 的执行计划 2 1 查询处理步骤查询处理步骤 RDBMS 查询处理过程可以分为四个阶段 查询分析 查询检查 查询优化和查询执行 如图 2 1 所示 1 查询分析 查询分析是查询处理的第一个阶段 主要任务是对查询语句进行扫描 词法分析和语法 分析 从查询语句中识别出语言符号 SQL 关键字 属性名和关系名等 并且进行语法检查 和语法分析 即判断查询语句是否符合 SQL 语法规则 2 查询检查 查询检查是根据数据字典对合法的 SQL 查询语句进行语义检查 即检查语句中的数据 库对象 如属性名 关系名 是否存在和是否有效等 还要根据数据字典中的用户权限和完 本科毕业论文 设计 第 2 页 整性约束对用户的存取权限进行检查 如果该用户没有相应的访问权限或违反了完整性约束 就拒绝执行该查询操作 检查通过后便把 SQL 查询语句转换成等价的关系代数表达式 RDBMS 一般都用查询树 query tree 也称为语法分析树 来表示扩展的关系代数表达式 这个过程中要把数据库对象的外部名称转换为内部表示 词法分析 语法分析 语义分析 符号名转换 安全性检查 完整性检查 查询树 query tree 代数优化 物理优化等 执行策略描述 代码生成 查询计划的执行代码 数据库 数据字典 查询语句 查询分析 查询检查 查询优化 查询执行 图 2 1 查询处理步骤 3 查询优化 每个查询语句都会有很多可供选择的执行策略和操作算法 查询优化 query optimization 就是选择一个高效的查询处理策略 查询优化有许多种方法 按照优化的层次 一般可以分为代数优化和物理优化 代数优化是指关系代数表达式的优化 即按照一定的规 本科毕业论文 设计 第 3 页 则 改变代数表达式中操作的次序和组合 使查询执行更高效 物理优化则是指存取路径和 底层操作算法的选择 选择的依据可以是基于规则的 也可以基于代价的 还可以基于语义 的 实际 RDBMS 中的查询优化器都综合了运用了这些优化技术 以获得最好的查询优化效 果 4 查询执行 查询执行就是依据优化器得到的执行策略生成查询计划 由代码生成器 code generator 生成执行这个查询计划的代码 2 2 实现查询操作的算法示例实现查询操作的算法示例 选择操作和连接操作是查询操作的两个典型操作 每一种操作有多种执行这个操作的算 法 下面探讨实现这两种操作的几个主要算法 2 2 1 选择操作的实现选择操作的实现 众所周知 SELECT 语句功能十分强大 有许多选项 因此实现的算法和优化策略也很 复杂 下面以简单的选择操作为例讲述典型的实现方法 例 1 Select from student where 考虑的几种情况 C1 无条件 C2 Sno 200215121 C3 Sage 20 C4 Sdept CS AND Sage 20 1 简单的全表扫描方法 对查询的基本表顺序扫描 逐一检查每个元组是否满足选择条件 把满足条件的元组作 为结果输出 对于小表 这种方法简单有效 对于大表顺序扫描十分费时 效率很低 2 索引 或散列 扫描方法 如果选择条件中的属性上有索引 例如 B 树索引或 Hash 索引 可以用索引扫描方法 通过索引先找到满足条件的元组主码或元组指针 再通过元组指针直接在要查询的基本表中 本科毕业论文 设计 第 4 页 找到元组 例 1 C2 以 C2 为例 Sno 200215121 并且 Sno 上有索引 则可以通过使用索引得 到 Sno 为 200215121 元组的指针 然后通过元组指针在 student 表中检索等到该学生 例 1 C3 以 C3 为例 Sage 20 并且 Sage 上有 B 树索引 则可以使用 B 树索引找到 Sage 20 的索引项 以此为入口在 B 树的顺序集上得到 Sage 20 的所有元组指针 然后通过 这些元组指针到 student 表中检索所有年龄大于 20 的学生 例 1 C4 以 C4 为例 Sdept CS AND Sage 20 如果 Sdept 和 Sage 上都有索引 一种算 法是 分别用上面的两种方法分别找到 Sdept CS 的一组元组指针和 Sage 20 的另一组元组 指针 求这两组指针的交集 再到 student 表中检索 就得到计算机系年龄大于 20 的学生 另一种算法是 找到 Sdept CS 一组元组指针 通过这些元组指针到 student 表中检索 并对得到的元组检查另一些选择条件是否满足 把满足条件的元组作为结果输出 2 2 2 连接操作的实现连接操作的实现 连接操作是查询处理中最耗时的操作之一 不失一般性 本文只讨论等值连接最常用的 实现算法 例 2 SELECT FROM Student SC WHERE Student 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 个表原来无序 执行时间要加上 本科毕业论文 设计 第 5 页 对两个表的排序时间 即使这样 对于 2 个大表 先排序后使用 sort merge join 方法执行连 接 总的时间一般仍会大大减少 3 索引连接方法 用索引连接方法的步骤是 在 SC 表上建立属性 Sno 的索引 如果原来没有的话 对 Student 中的每一个元组 由 Sno 值通过 SC 的索引查找相应的 SC 元组 把这些 SC 元组和 Student 表中的元组连接起来 循环执行 直到 Student 表中的元组处理完为止 4 Hash Join 方法 属性作为 hash 码 用同一个 hash 函数把 R 和 S 中的元组散列到同一个 hash 文件中 第 一步 划分阶段 对包含较少元组的表进行一遍处理 把它的元组按 hash 函数分散到 hash 表的桶中 第二步 试探阶段 也称为连接阶段 对另一表 S 进行一遍处理 把 S 的元 组散列到适当的 hash 桶中 并把元组与桶中所有来自 R 并与之相匹配的元组连接起来 3 SQL 查询处理优化方法查询处理优化方法 查询优化在关系数据库系统中有着非常重要的地位 关系数据库系统和非过程化的 SQL 之所以能取得巨大的成功 关键得益于查询优化技术的发展 关系查询优化是影响 RDBMS 性能的关键因素 查询优化既是 RDBMS 实现的关键又是关系数据库的优点所在 它减轻了用户选择存取 路径的负担 用户只要提出 干什么 不必指出 怎么干 对比一下非关系系统中的情况 用户使用过程化的语言表达查询要求 执行何种记录级的操作 以及操作的序列是由用户而 不是由系统来决定的 因此用户必须了解存取路径 系统要提供用户选择存取路径的手段 查询效率由用户的存取策略决定 如果用户做了不当的选择 系统是无法对此加以改进的 这就要求用户有较高的数据库技术和程序设计水平 下面介绍几种常用的查询优化方法 3 13 1 基于索引的优化基于索引的优化 1 索引定义 索引是一个单独的 物理的数据库结构 它是根据表中一列或若干列 按照一定顺序建 立的列值与记录行之间的对应关系表 索引是依赖于表建立的 它包含索引键值及指向数据所在页面和行的指针 一个表的存 本科毕业论文 设计 第 6 页 储是由两部分组成的 一部分用来存放表的数据页面 另一部分存放索引页面 索引就存放 在索引页面上 通常 索引页面相对于数据页面来说要小得多 当进行数据检索时 系统先 搜索索引页面 从中找到所需数据的指针 然后再直接通过指针从数据页面中读取数据 索引可以提供对一个表中的数据的有效访问 它可以用于加速数据的检索和强制唯一性 限制 但是 不应该在每一个列上都建立索引 因为构造索引需要占用一定的系统资源 降 低更新的速度 而且 插入 删除或更新一个索引列中的数据比非索引列中的数据要花费更 长的时间 2 索引的作用 索引是加快数据检索的一种数据库结构 使得数据查询时不必扫描整个数据库就能迅速 查到想要的数据 具体如下 5 个方面 通过创建唯一性索引 可以保证数据库表中每一行数据的唯一性 可以大大加快数据的检索速度 这也是创建索引的最主要的原因 可以加速表和表之间的连接 特别是在实现数据的参考完整性方面特别有意义 在使用分组和排序子句进行数据检索时 同样可以减少查询中分组和排序的时间 通过使用索引 可以在查询的过程中 使用优化器隐藏 提高系统的性能 3 索引的类型 如果一个表没有创建索引 则数据行不按任何特定的顺序存储 这种结构称为堆集 在 SQL Server 2000 的数据库中 按存储结构的不同将索引分为两类 簇索引 Clustered Index 和非簇索引 Nonclustered Index 1 簇索引 簇索引对表的数据行的键值进行排序 然后再存储有用的数据记录 由于簇索引对表中 的数据一一进行了排序 因此用簇索引查找数据很快 但由于簇索引将表中的所有数据完全 重新排列了 它所需要的空间也就特别大 大约相当于表中数据所占空间的 120 表的数 据行只能以一种排序方式存储在磁盘上 所以一个表只能有一个簇索引 2 非簇索引 非簇索引具有完全独立于数据行的结构 使用非簇索引不用对表的数据行的键值进行排 序 非簇索引的 B 树叶节点存储了组成非簇索引的键值和行定位器 从索引行指向数据行 的指针称为行定位器 行定位器的结构和存储内容取决于数据的存储方式 如果数据是以 索引方式存储的则行定位器中存储的是簇索引的索引键 如果不是以索引方式存储的 这种 方式称为堆存储方式 Heap Structure 则行定位器中存储的是指向数据行的指针 非簇索 引将行定位器的键值用一定的方式排序 这个顺序与表的行在数据页中的排序是不匹配的 本科毕业论文 设计 第 7 页 由于非簇索引使用索引页存储 因此簇索引需要更多的空间 且检索效率较低 但一个 表只能建一个簇索引 当用户需要建立多个索引时 就需要使用非簇索引了 从理论上讲 一个表最多可以建 248 个非簇索引 对于何时使用簇索引 何时使用非簇索引如表 3 1 所示 表 3 1 使用簇索引或非簇索引的时机 动作描述使用簇索引使用非簇索引 列经常被分组排序应应 返回某范围内的数据应不应 一个或极少不同值不应不应 小数目的不同值应不应 大数目的不同值不应应 频繁更新的列不应应 外键列应应 主键列应应 频繁修改索引列不应应 4 索引的建立与删除 一般来说 建立与删除索引由数据库管理员 DBA 或表的属主 owner 即建表的人负 责完成 系统在存取数据时会自动选择合适的索引作为存取路径 用户不必也不能显式地选 择索引 1 建立索引 在 SQL 语言中 建立索引使用 CREATE INDEX 语句 其一般格式为 CREATE UNIQUE CLUSTER INDEX ON 其中 是要建索引的基本表的名字 索引可以建立在该表的一列或多列上 各列 名之间用逗号分隔 每个后面还可以用指定索引值的排列次序 可选 ASC 升序 或 DESC 降序 缺省值为 ASC UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录 CLUSTER 表示要建立的索引是聚簇索引 例 1 CREATE CLUSTER INDEX Stusname ON Student Sname 这条语句是在 Student 表的 Sname 姓名 列上建立一个聚簇索引 而且 Student 表中的 本科毕业论文 设计 第 8 页 记录将会按照 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 删除索引 索引一经建立 就由系统使用和维护它 不需用户干预 建立索引是为了减少查询操作 的时间 但如果数据增删改频繁 系统会花费很多时间来维护索引 从而降低了查询效率 这时可以删除一些不必要的索引 在 SQL 中 删除索引使用 DROP INDEX 语句 其一般格式为 DROP INDEX 例 3 删除 Student 表的 Stusname 索引 DROP INDEX Stusname 删除索引时 系统会同时从数据字典中删去有关该索引的描述 3 2 SQL 语句语句优化优化 使用索引可以有效的提高查询速度 但是 SQL 语句是对数据库操作的唯一途径 程序 的执行最终都归结为 SQL 语句的执行 所以 SQL 语句的执行效率对数据库系统的性能起了 决定性的作用 所以我们不但要会写 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 值 然后这样查询 本科毕业论文 设计 第 9 页 select id from t where num 0 2 应尽量避免在 where 子句中使用 或操作符 否则将引擎放弃使用索引而进行全 表扫描 优化器将无法通过索引来确定将要命中的行数 因此需要搜索该表的所有行 3 应尽量避免在 where 子句中使用 or 来连接条件 否则将导致引擎放弃使用索引而 进行全表扫描 如 select id from t where num 10 or num 20 可以这样查询 select id from t where num 10 union all select id from t where num 20 4 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 3 5 应尽量避免在 where 子句中对字段进行表达式操作 这将导致引擎放弃使用索引而 进行全表扫描 如 SELECT FROM T1 WHERE F1 2 100 应改为 SELECT FROM T1 WHERE F1 100 2 SELECT 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 dateofbirth 2005 11 30 and createdate 10 GROUP BY OrderID 可改为 SELECT DISTINCT OrderID FROM Details WHERE UnitPrice 10 2 能用 UNION ALL 就不要用 UNION UNION ALL 不执行 SELECT DISTINCT 函数 这样就会减少很多不必要的资源 3 尽量不要用 SELECT INTO 语句 SELECT INOT 语句会导致表锁定 阻止其他用户访问该表 4 IN OR 子句常会使用工作表 使索引失效 如果不产生大量重复值 可以考虑把子 句拆开 拆开的子句中应该包含索引 5 SET SHOW PLAN ALL ON 查看执行方案 DBCC 检查数据库数据完整性 DBCC DataBase Consistency Checker 是一组用于验证 SQL Server 数据库完整性的程序 本科毕业论文 设计 第 12 页 6 慎用游标 在某些必须使用游标的场合 可考虑将符合条件的数据行转入临时表中 再对临时表定 义游标并进行操作 这样可使性能得到明显提高 上面我们讲述的是一些基本的提高查询速度的方法 但是在更多的情况下 往往需要反复 试验比较不同的语句以得到最佳方案 最好的方法当然是测试 看实现相同功能的 SQL 语句 哪个执行时间最少 但是如果数据库中数据量很少 是比较不出来的 这时可以用查看执行 计划 即 把实现相同功能的多条 SQL 语句拷到查询分析器 按 CTRL L 查看所利用的索引 以及表扫描次数 这两个对性能影响最大 总体上看成本百分比即可 3 3 其他优化方法其他优化方法 数据库的查询优化方法除了索引和优化 SQL 语句还有其他的方法 其他方法的合理使用 同样也能很好的对数据库查询起到优化作用 我们就来列举几种简单实用的方法 3 3 1 避免或简化排序避免或简化排序 应当简化或避免对大型表进行重复的排序 当能够利用索引自动以适当的次序产生输出 时 优化器就避免了排序的步骤 以下是一些影响因素 索引中不包括一个或几个待排序的列 group by 或 order by 子句中列的次序与索引的次序不一样 排序的列来自不同的表 为了避免不必要的排序 就要正确地增减索引 合理地合并数据库表 尽管有时可能影 响表的规范化 但相对于效率的提高是值得的 如果排序不可避免 那么应当试图简化它 如缩小排序的列的范围等 3 3 2 使用临时表使用临时表 临时表中的行比主表中的行要少 而且物理顺序就是所要求的顺序 减少磁盘的 I O 操 作 查询工作量可以大幅减少 在表的一个子集进行排序并创建临时表 也能实现加速查询 在一些情况下这样可以避 免多重排序操作 但所创建的临时表的行要比主表的行少 其物理顺序就是所要求的顺序 这样就减少了输入和输出 降低了查询的工作量 提高了效率 而且临时表的创建并不会反 映主表的修改 本科毕业论文 设计 第 13 页 但是对临时表的使用也要有一些规则 主要有 1 尽量使用表变量来代替临时表 如果表变量包含大量数据 请注意索引非常有限 只 有主键索引 2 避免频繁创建和删除临时表 以减少系统表资源的消耗 3 临时表并不是不可使用 适当地使用它们可以使某些例程更有效 例如 当需要重复 引用大型表或常用表中的某个数据集时 但是 对于一次性事件 最好使用导出表 4 在新建临时表时 如果一次性插入数据量很大 那么可以使用 select into 代替 create table 避免造成大量 log 以提高速度 如果数据量不大 为了缓和系统表的资源 应先 create table 然后 insert 5 如果使用到了临时表 在存储过程的最后务必将所有的临时表显式删除 先 truncate table 然后 drop table 这样可以避免系统表的较长时间锁定 3 3 3 优化表中数据类型优化表中数据类型 对表中数据最好使用兼容的数据类型 因为数据类型的不兼容可能使优化器无法执行一 些本来可以进行的优化操作 例如 float 和 int char 和 varchar binary 和 varbinary 是不兼容 的 下面用例子来阐述 SELECT name FROM employee WHERE salary 60000 在这条语句中 如 salary 字段是 money 型的 则优化器很难对其进行优化 因为 60000 是 个整型数 我们应当在编程时将整型转化成为钱币型 而不要等到运行时转化 3 3 4 用排序来取代非顺序存取用排序来取代非顺序存取 磁盘存取臂的来回移动使得非顺序磁盘存取变成了最慢的操作 但是在 SQL 语句中这 个现象被隐藏了 这样就使得查询中进行了大量的非顺序查询 降低了查询速度 对于这个 现象还没有很好的解决方法 只能依赖于数据库的排序能力来替代非顺序的存取 有些时候 用数据库的排序能力来替代非顺序的存取能改进查询效率 3 3 5 数据服务器存储数据服务器存储 尽量将数据的处理工作放在服务器上 减少网络的开销 如使用存储过程 存储过程是 编译好 优化过并且被组织到一个执行规划里 且存储在数据库中的 SQL 语句 是控制流 语言的集合 速度当然快 本科毕业论文 设计 第 14 页 4 实例分析实例分析 第三章研究了数据库查询处理过程以及一些 SQL 优化方法 本章将在第三章研究的基础 上 利用案例数据库进行对上述三类 SQL 查询优化的有效性进行实验验证 并对实验结果分 析 4 1 实验环境实验环境 操作系统 Windows XP 数据库管理软件 SQL Server 2000 内存 2G CPU Intel R Pentium R Dual CPU E2160 1 80GHz 4 2 案例数据库案例数据库 为了进行实验分析 本文建立了案例数据库 包括 Company House Sale 三张表 如 表 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楼盘绿化面积 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楼盘销售时间编号 外键 Sale Areafloat8楼盘销售面积 Sale Pricefloat8楼盘销售价格 案例数据的数据该库如表 4 4 所示 表 4 4 数据库数据量概况 数据表名数据量 Company91 House300 Sale3590 4 3 具体实例具体实例 1 基于索引优化实验 索引优化是数据库 SQL 查询优化的重要方法 为了证明其有效性 本实验在 Company 和 House 表的 Company ID 列上建立了索引 以下面 SQL 语句为例 比较索引建立前后执 行时间的变化 SQL 语句 select getdate select from Company House where Company Company ID House Company ID select 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 两次时间相减就是 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 嵌套优化实验 本科毕业论文 设计 第 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 相等的元组 并把两表中这些元组的信息连接起来返回 最后再获取系统 当前时间 两次时间相减就是 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 ID select 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 实现嵌套查询时会使系统无法使用索引 只能对表中的数据全部搜索这将导致系统效率降低 3 表达式优化实验 由第三章可知在 where 子句中对字段进行表达式操作会降低查询性能 本实验分别在 where 字句中进行表达式操作和不进行表达式操作 比较两者的执行时间 在 where 字句中未进行表达式操作 SQL 语句 select getdate select from Sale House where Sale Sale Area 1500 And House 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 Sale Area 2