logo资料库

毕业论文-数据库查询优化策略分析.doc

发布时间：2022-05-29 发布人：admin 分类：说明书 资料大小：0.12M 资料格式：doc 举报 版权申诉
第1页 / 共21页
第2页 / 共21页
第3页 / 共21页
第4页 / 共21页
第5页 / 共21页
第6页 / 共21页
第7页 / 共21页
第8页 / 共21页
资料共21页，剩余部分请下载后查看
    摘要
    第一章基于索引的优化
    1.1索引概念
    1.2判断并建立必要的索引
    1.3建立高效的索引
    1.3.1何时使用聚集索引或非聚集索引
    1.3.2结合实际，谈索引使用的误区
    1.4对索引使用的一些规则
    1.5 合理的索引对SQL语句的意义
    第二章 优化SQL语句
    2.1查询的优化
    2.1.1查询优化过程要注意事项
    2.2选择最有效率的表名顺序
    第三章 其他优化方法
    3.1 避免或简化排序
    3.2 避免相关子查询
    3.3 创建使用临时表
    3.4 用排序来取代非顺序存取
    3.5 数据服务器存储
    第四章 结论
    参考文献：
    济宁学院 毕 业 论 文 课题名称： 数据库查询优化策略分析 系 别： 专 班 学 业 ： 级 ： 号： 学生姓名： 指导教师 ： 完成时间 ：
    摘要 本文主要探讨了在数据库的应用中对数据库查询优化的一些策略，其中包括 基于索引、SQL 语句的优化的方法，并简要对其他一些常用方法做了介绍。 随着计算机应用的深入，计算机技术的成熟，各种应用软件的普及，应用数 据也随着日常工作而迅速增长，作为数据仓库的数据库的重要性也日益显著。 数据库系统作为管理信息系统的核心，各种基于数据库的联机事务处理以及 联机分析处理正慢慢的转变成为计算机应用的最为重要的部分，根据以往大量的 应用实例来看，在数据库的各种操作中，查询操作所占的比重最大，而在查询操 作中基于 SELECT 语句在 SQL 语句中又是代价最大的语句。如果在使用中采用了 优秀的查询策略，往往可以降低查询的时间，提高查询的效率，由此可见查询优 化在数据库中的重要性。本文就数据库查询优化中的策略进行介绍及探索。 关键词：数据库 查询优化 查询 优化 索引
    目 录 第一章基于索引的优化............................................................................................... 1 1.1 索引概念................................................................................................................. 1 1.2 判断并建立必要的索引......................................................................................... 1 1.3 建立高效的索引..................................................................................................... 1 1.3.1 何时使用聚集索引或非聚集索引............................................................... 3 1.3.2 结合实际，谈索引使用的误区................................................................... 3 1.4 对索引使用的一些规则......................................................................................... 5 1.5 合理的索引对 SQL 语句的意义...........................................................................6 第二章 优化 SQL 语句................................................................................................7 2.1 查询的优化............................................................................................................. 7 2.1.1 查询优化过程要注意事项........................................................................... 9 2.2 选择最有效率的表名顺序................................................................................... 14 第三章 其他优化方法............................................................................................... 16 3.1 避免或简化排序.................................................................................................. 16 3.2 避免相关子查询.................................................................................................. 16 3.3 创建使用临时表.................................................................................................. 16 3.4 用排序来取代非顺序存取.................................................................................. 16 3.5 数据服务器存储.................................................................................................. 16 第四章 结论............................................................................................................... 17 参考文献：................................................................................................................. 18
    2007 级计算机网络技术专业毕业论文 第一章基于索引的优化 数据库的优化方法多种多样，不同的方法对提高数据库查询效率也不相同。 索引作为数据库中的重要数据结构，它的根本目的就是为了提高查询的效率。 而优化查询的重要方法就是建立索引，建立适合关系数据库系统的索引，这样就 可以避免表扫描，并减少了因为查询而造成的输入输出开销，有效提高数据库数 据的查询速度，优化了数据库性能。然而在创建索引时也增加了系统时间和空间 的开销。所以创建索引时应该与实际查询需求相结合，这样才能实现真正的优化 查询。 1.1 索引概念 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列 值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定 的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索 引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的 行。 1.2 判断并建立必要的索引 对所要创建的索引进行正确的判断，使所创建的索引对数据库的工作效率 提高有所帮助。为了实现这一点，我们应做到以下要求：在熟记数据库程序中的 相关 SQL 语句的前提下，统计出常用且对性能有影响的语句；判断数据库系统 中哪些表的哪些字段要建立索引。其次，对数据库中操作频繁的表，数据流量较 大的表，经常需要与其他表进行连接的表等，要进行重点关注。这些表上的索引 将对 SQL 语句的性能产生重要的影响。 1.3 建立高效的索引 创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表 中数据的策略。大型数据库有两种索引即簇索引和非簇索引，一个没有簇索引的 表是按堆结构存储数据，所有的数据均添加在表的尾部，而建立了簇索引的表， 其数据在物理上会按照簇索引键的顺序存储，一个表只允许有一个簇索引，因此， 根据 B 树结构，可以理解添加任何一种索引均能提高按索引列查询的速度，但会 1
    2007 级计算机网络技术专业毕业论文 降低插入、更新、删除操作的性能，尤其是当填充因子（Fill Factor）较大时。 所以对索引较多的表进行频繁的插入、更新、删除操作，建表和索引时因设置较 小的填充因子，以便在各数据页中留下较多的自由空间，减少页分割及重新组织 的工作。 索引是从数据库中获取数据的最高效方式之一。95% 的数据库性能问题都 可以采用索引技术得到解决。作为一条规则，我通常对逻辑主键使用唯一的成组 索引，对系统键（作为存储过程）采用唯一的非成组索引，对任何外键列[字段] 采用非成组索引。不过，索引就象是盐，太多了菜就咸了。你得考虑数据库的空 间有多大，表如何进行访问，还有这些访问是否主要用作读写。 实际上，您可以把索引理解为一种特殊的目录。微软的 SQL SERVER 提供了 两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集 索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例 来说明一下聚集索引和非聚集索引的区别： 其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安” 字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音 排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然 地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字， 那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的 字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分 本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。 我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索 引” 。 如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇 到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要 查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码 直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的 字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查 部首之后的检字表中“张”的页码是 672 页，检字表中“张”的上面是“驰”字， 但页码却是 63 页，“张”的下面是“弩”字，页面是 390 页。很显然，这些字并 不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三 字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的 映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到 目录中的结果，然后再翻到您所需要的页码。 我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索 2
    2007 级计算机网络技术专业毕业论文 引” 。 进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引， 因为目录只能按照一种方法进行排序。 1.3.1 何时使用聚集索引或非聚集索引 下表总结了何时使用聚集索引或非聚集索引 动作描述 使用聚集索引 使用非聚集索引 列经常被分组排序 返回某范围内的数据 一个或极少不同值 小数目的不同值 大数目的不同值 频繁更新的列 外键列 主键列 频繁修改索引列 应 应 不应 应 不应 不应 应 应 不应 应 不应 不应 不应 应 应 应 应 应 事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上 表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚 合索引建立在了该列，这时您查询 2004 年 1 月 1 日至 2004 年 10 月 1 日之间的 全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序 的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非 聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到 具体内容。 1.3.2 结合实际，谈索引使用的误区 理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索 引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下 面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌 握索引建立的方法。 1、主键就是聚集索引 这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然 SQL 3
    2007 级计算机网络技术专业毕业论文 SERVER 默认是在主键上建立聚集索引的。 通常，我们会在每个表中都建立一个 ID 列，以区分每条数据，并且这个 ID 列是自动增大的，步长一般为 1。我们的这个办公自动化的实例中的列 Gid 就是 如此。此时，如果我们将这个列设为主键，SQL SERVER 会将此列默认为聚集索 引。这样做有好处，就是可以让您的数据在数据库中按照 ID 进行物理排序，但 笔者认为这样做意义不大。 显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引 的规则，这使得聚集索引变得更加珍贵。 从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好 处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中， 因为 ID 号是自动生成的，我们并不知道每条记录的 ID 号，所以我们很难在实践 中用 ID 号来进行查询。这就使让 ID 号这个主键作为聚集索引成为一种资源浪费。 其次，让每个 ID 号都不同的字段作为聚集索引也不符合“大数目的不同值情况 下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容， 特别是索引项的时候会负作用，但对于查询速度并没有影响。 在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议 还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还 有用户本身的“用户名”。 通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我 们的 where 语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立 了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次 全表扫描，这样做意义是不大的，绝大多数的用户 1 个月前的文件都已经浏览过 了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统 首页时，数据库仅仅查询这个用户近 3 个月来未阅览的文件，通过“日期”这个 字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的 2 年， 那么您的首页显示速度理论上将是原来速度 8 倍，甚至更快。 2、只要建立索引就能显著提高查询速度 事实上，我们可以发现上面的例子中，第 2、3 条语句完全相同，且建立索 引的字段也相同；不同的仅是前者在 fariqi 字段上建立的是非聚合索引，后者 在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任 何字段上简单地建立索引就能提高查询速度。 从建表的语句中，我们可以看到这个有着 1000 万数据的表中 fariqi 字段 有 5003 个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我 4
    2007 级计算机网络技术专业毕业论文 们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索 引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看 来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。 3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度 上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本 身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来， 建立一个复合索引（compound index）。 很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感 到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这 个问题，我们来看一下以下的查询速度（结果集都是 25 万条数据）：（日期列 fariqi 首先排在复合聚集索引的起始列，用户名 neibuyonghu 排在后列） 我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚 集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略 快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作 为查询条件的话，这个索引是不起任何作用的。当然，语句 1、2 的查询速度一 样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的 话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论 您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。 1.4 对索引使用的一些规则 1、表的主键、外键必须有索引； 2、数据量超过 300 的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在 Where 子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同时以 AND 方式出现在 Where 子句中？单 字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索 引； C、如果复合索引中包含的字段经常单独出现在 Where 子句中，则分解为多 个单字段索引； 5
    分享到：
    赞 收藏

    相关推荐

    资料库

    开发技术

    共收录19306份资料，累计14个分类，关注成员有19位，主要包括：Actionscript,Javascript,PHP,VB,Perl,Delphi,Python,C#,C,Java,C++,Web开发,硬件开发,其它

    热门标签

    Actionscript Javascript PHP VB Perl Delphi Python C# C Java C++ Web开发 硬件开发 其它

    最新资料