SQL查询性能的优化研究

第27卷第1期 2 0 0 9年1月 西安航空技术高等专科学校学报 Journal of Xi’an Aerotechnical College Vol127 No11 Jan . 2 0 0 9 收稿日期:2008206222 作者简介:张晓丽(1980 - ) ,女,重庆市人,硕士研究生,研究方向:Internet技术及Web应用。 SQL查询性能的优化研究 张晓丽 (西安航空技术高等专科学校 计算机工程系,陕西 西安710077) 摘 要:数据库是应用程序的核心,为提高基于SQL Server数据库应用程序的执行效率,本文分析了SQL Server数 据库的查询优化处理技术,分别讨论了建立索引,避免SQL排序,优化多表查询等技术,从而优化SQL查询的性能。 关键词:性能优化;索引;排序;内嵌视图 中图分类号:TP393. 07 文献标识码:A 文章编号:100829233(2009)0120047203 1 引言 数据库系统是管理信息系统的核心,它涉及到 系统的效率,影响整个系统的性能。随着数据库技 术的发展,数据库的规模越来越大,对数据进行的处 理操作也越来越复杂。如果设计者在开发时没有考 虑数据库的优化,在数据库的使用过程中,随着数据 量的增大,在数据库中执行一条访问数据库的SQL 语句可能要花费相当长的时间,这就降低了应用系 统的速度,使得应用系统运行效率非常低下。如何 提高应用程序访问数据库的速度和正确性,已经成 了系统开发中的一个重要问题。 针对这个问题,本文分析了查询优化的处理过 程,讨论了使用索引、 优化多表连接查询等方法改善 SQL Server查询效率,提高基于SQL Server应用程 序的性能并改善其可伸缩性。 2 SQL Server优化器 Microsoft SQL Server数据库内核用1个基于 费用的查询优化器自动优化向SQL提交的数据查 询操作[1]。SQL Server的优化通过3个阶段完成: 查询分析、 索引选择、 合并选择。 2. 1 查询分析 在查询分析阶段,SQL Server优化器查看每一 个由正规查询树代表的子句,并判断它是否能被优 化。SQL Server尽量优化那些限制扫描的子句。 通过限制查找,查询优化器减少处理行、 索引和读入 数据页的数量[1]。 2. 2 索引选择 对于每个可优化的子句,优化器都查看数据库 系统表,以确定是否有相关的索引能用于访问数据。 只有当索引中列的一个前缀与查询子句中的列完全 匹配时,这个索引才被认为是有用的。这一信息的 收集,来自于索引统计值和列统计值。 2. 3 合并选择 当索引选择结束,并且所有的子句都有了一个 基于它们的访问计划的处理费用时,优化器开始执 行合并选择。合并选择被用来找出一个用于合并子 句访问计划的有效顺序。为了做到这一点,优化器 比较子句的不同排序,然后选出在物理磁盘I/ O的 角度看,是处理费用最低的合并计划。 SQL Server的查询优化器包括上述三个阶段。 在检查完语句的词法、 语法之后,DBMS将语句提交 给查询优化器,优化器做完代数优化和存取路径的 优化之后,由预编译模块对语句进行处理并生成查 询规划,然后在合适的时间提交给系统处理执行,最 后将执行结果返回给用户。SQL Server采用基于 代价的优化方法,这种优化能根据从系统字典表所 得到的信息来估计不同的查询规划的代价,然后选 择一个较优的规划。 SQL Server系统优化的基础是SQL语句[2],如 果SQL语句性能不高,那么优化的作用也不大。为 了提高SQL的查询性能,本文主要从索引、 排序以 及多表连接查询等方面来考虑。 3 SQL语句优化 3. 1 建立合适的索引 优化SQL Server性能的方法很多,但提高速度 最快的方法就是索引[3]。索引是数据库中重要的 数据结构,它的根本目的就是为了提高查询效 率[4]。在关系数据库系统的表上建立合适的索引, 可以避免表扫描并减少因查询而造成的I/ O开销, 极大的提高了查询的执行速度。索引指定表中记录 的逻辑顺序,为查询提供优化的工具。 索引的有用性取决于数据查询的选取性,选取 性基于对查询将返回的结果集占全部记录集的百分 比。SQL Server查询优化器索引选择阶段会根据 数据页和索引页的分布统计情况给索引打分的方式 来选取可用的索引。此外,查询返回结果集的大小 对索引的使用也有不同影响。一般来说,表中数据 量越大,查询返回的结果越少,索引的使用效果越明 显。因而,应该分析对表的操作,建立合适的索引。 在建立索引时,需要考虑以下几个因素: (1)在经常链接,但是没有指定为外键的列上建 立索引,而不经常链接的字段则由优化器自动生成 索引。 (2)在频繁进行排序或分组(即进行group by 或order by操作)的列上建立索引。 (3)对SELECT操作来说,索引总是有利的[2]。 但对于UPDATE和DELETE操作,索引既有正面 影响,又具有负面效应,UPDATE和DELETE操作 的第一步是找到该记录,索引可以加快检索,但为维 护索引的顺序,第二步必须修改索引,这又不可避免 的影响UPDATE和DELETE操作完成时间。因 此,在建立索引时,对UPDATE和DELETE操作频 繁的表,尽量少建或不建索引,避免同一个表上的索 引之间有相同的列存在。 3. 2 避免或简化排序 排序的成本十分高昂,它消耗大量的CPU时间 和内存,触发磁盘分页和交换操作,SQL性能必然 降低。因此应当简化或避免在SQL语句中进行排 序操作。 SQL查询语句中引起排序的操作有:ORDER BY和GROUP BY从句: DISTINCT修饰符; U2 NION、INTERSECT、MINUS集合操作符;多表连 接时的排序合并连接等。 当能够利用索引自动以适当的次序产生输出 时,优化器就避免了排序的步骤。但是当索引中不 包括一个或几个待排序的列,或者group by或order by子句中列的次序与索引的次序不一样时,排序就 不可避免。如果排序不可避免,那么就可以采用以 下方法优化排序。 (1)建立恰当的索引。对经常进行排序和连接 操作的字段建立索引。在建立索引后,当服务器向 这些字段发出排序请求时,将直接引用索引而不进 行排序操作;当进行等值连接查询操作时,若建立连 接的字段未建立索引,服务器进行的是排序合并连 接(SORT MERGE JOIN) ,如果建立连接的字段已 建立索引,服务器进行嵌套循环连接(NESTED LOOP JOINS) ,该连接方式不需要任何排序。因 此,建立索引可避免多数排序操作。 (2)使用UNIION ALL代替UNION。在数据 库中,UNION和UNION ALL关键字都是将两个 结果合并为一个。UNION在进行链接后会筛选掉 重复的记录,所以在表链接后会对所产生的结果集 进行排序运算,删除重复的记录再返回结果。 大部分应用中是不会产生重复记录的,最常见 的是过程表与历史表UNION。 select3from prosc union select3from hissc 以上SQL在运行时先取出两个表的结果,再用 排序空间进行排序删除重复