SQL Server的查询优化

山西科技SHANXI SCIENCE AND TECHNOLOCY2008年第5期9月 20日出版(信息技术SQLServer的查询优化张兴”李海芳2)(太原理工大学计算机与软件学院)摘要:文章首先介绍了数据库系统的性能问题,查询处理和优化,并在此基础上对SQL Server200的查询优化实现进行了分析,比较了不同查询语句的执行速度和执行计划的差异。关键词:系统性能;查询处理及优化;SQL Server 2000中图分类号:TP309文献标识码:A文章编号:1004- 6429(2008)05 - 0053-03在关系数据库管理系统中,性能是一个关键性的问题,待查询优化是为关系代数表达式的计算选择最有效的查询别是怎样通过优化使性能达到最佳状态。关系数据库系统对计划的过程。一般步骤为:①把查询转化成某种内部表示。②用户的查询要求隐藏查询路径和存取方法,用户只需指出需要代数优化。③物理优化。④生成查询计划,选择代价最小者。查询的内容,而由DBMS负责选择具体的查询路径和存取方1)代数优化。代数优化是对查询表达式进行等效变换,以法。为了提高和达到用户接受的查询性能,关系系统中查询优减少执行开销。等效变换的主要依据是一组等价变换规则。化就显得十分必要。其一般准则有:1性能评估及 监枧选择运算尽可能先做,目的是为了减小中间关系;通过有效的网络流量,磁盘IV0和CPU时间,可获得最短在执行连接操作前对文件适当进行预处理,如排序或在连的响应时间和最大的吞吐率,从而获得最佳性能。其中响应时接属性上建立索引;间是处理查询并将结果集中的首行返回给用户所需的时间长投影运算和选择运算同时做，目的是避免重复扫描关系;度,而吞吐率是服务器在给定时间内处理的查询总数的度量将投影运算与其前面或后面的双目运算结合起来,以碱少值。因此,响应时间的长短和吞吐率的大小直接影响到系统性扫描关系的次数;能的高低。影响SQL Sever系统性能的主要因素有;①系统硬合并乘积和其后的选择为连接运算;件资源:主要有CPU大小,硬件容量,内存大小等。②操作系去掉多余的属性。可能时对每个叶结点或中间结点加上统环境:是Windows NT还是Windows 200③数据库应用程必要的投影操作,以除去对后续操作没有影响的属性,臧少中序:数据库逻辑设计与物理设计的质量。④客户端应用程序: .间结果;程序中事务控制、锁机制的应用和游标的应用等。⑤网络环找出公共子表达式。境:网络带寬网络连接及数据传输速度等。2)物理优化。物理优化是查询语句处理的详细策略的选若要对上述几个方面进行监督,就必须了解正常工作条件择,例如选择执行运算所采用的具体算法,选择将使用的特定下的合理性能级别。可以通过监视下列3个方面来解决问题:索引等。在关系代数表达式的基础上,执行计划进-步说明每sQL Sever存储过程或用户应用程序提交的批处理SQL语句，个运算的实现算法,各运算的执行顺序及是否采用流水线技术。用户活动和硬件活动。基本运算有选择运算、连接运算、排序运算分组运算集函数运2查询处理及优化算,其中连接运算的执行效率是影响查询效率的重要部分。2.1查询处理连接运算的实现方法有:嵌套循环连接、块嵌套循环连接、数据库的主要功能是存储管理好资料供用户查询使用。索引嵌套循环连接排序-归并连接及散列连接。其中散列连查询是数据库中最基本最常用的操作。关系数据库的查询操接只适用于等值连接;当要进行连接的两表都较大且都已按照作一般用非过程性语言描述,如SQL语言。查询语句本身主要连接属性排了序,则选择排序-归并算法将很有效;当要进行注重表达查询条件和结果关系的组成,而具体查询的实施过程连接的两表,一个较大另一个较小,则可以选择嵌套循环连接，和查找策略的选择由DBMS负责完成。DBMS 在接到查询后，并选择较小的表作为外表。首先进行词法分析和语法分析,产生查询的内部表示(- -般为2.3查询优化器查询树或查询图),然后由DBMS制定-一个执行策略 ,包括如何进行SQL语句优化的数据库管理系统部分称为查询优化访向内部文件,如何存储中间结果,如何获得查询结果。器。查询优化器将查询分解成它的组成部分并重新安排运行，概括起来查询处理的基本步骤为:①语法分析与翻译。②通过计算-些搜索条件或访问路径来确定要使用的最有效索优化。③执行。引,即查询优化器生成一个计划来完成指定的任务。2.2 查询优化中国煤化工器有两种优化方法:基于规则基于成本的优化,数据作者简介:1)张兴,男 ,1973年出生,太原理工大学计算.库查询HC N M H G,找出最低估算成本的机与软件学院在读硕士研究生,30009,山西省太原市执行计划，自动将向sQL提交的数据查询操作进行优化。收稿日期:2008-05- 16.3 SQL Server查询优化山西科技SHANXI SCIENCE AND TECHNOLOGY2008年第5期9月 20日出版SQL Server优化可以通过3个阶段完成:查询分析,索引选相关嵌套查询:seletx1.* from SP] x1撣和合并选择。where exists (select # from SPJ x23.1查询分析goup by x2. PNO在查询分析阶段,查询优化器查看每-一个由正规查询语句having x1 .QTY = MAX(x2.QTY))。代表的子句,并判断是否能被优化。SQL Sever一般会尽量优其执行计划如图1。化那些限制扫描的子句。并不是所有合法的SQL语法都可以非相关嵌套查询:selectx1.* from SPJ x1分成可优化的子句,如含有SQL不等关系运算符的子句。当-where x1.QTY = some (select MAX(QTY)个关系型查询中含有不可优化的子句时,执行计划用表扫描来from SPJ x2访问查询的这个部分,对于查询树中可优化的子句,则由优化goup by x2. PNO)。器执行索引选择。.它们的执行效率和执行计划也完全相同,说明在很多情况3.2 索引选择下,系统对相关嵌套查询和非相关嵌套查询的实际内部实现是对于每个可优化的子句,查询优化器都会查看数据库系统一样的。表,以确定是否有相关的索引能用于访问数据。只有当索引中的列的一个前缀与查询子句中的列完全匹配时,这个索引才被认为是有用的。3.3 合并选择在索引选择结束并且所有子句都有一个基于其访向计划的处理成本时,查询优化器开始执行合并选择。它根据子句的-圈二不同排序,选出从物理磁盘U0的角度看处理成本最低的合并计划。在合并选择结束时,查询优化器已经生成-一个基于成本的查询执行计划,而且该计划充分利用可用的索引，以最小的F日便+镇用护电国o系统开支和良好的执行性能访问数据。4实例下面将通过几个例子来分析SQL Server 2000对不同的查團1相关嵌套查询执行计划圜询语句的处理及其查询执行计划。所使用的4张表定义如下:4.3复合条件查询供应商(S)属性有SNO.SNAME.STATUS、CITY.select distinctS. * from S,P where s. CITY= P. CITY and P.项目(J)属性有JNOJNAME.CTTYPNAME= '螺丝刀'零件(P)属性有PNO、PNAME、COLOR、 WEICHTCITY可以看到,系统并没有按照语句中条件的顺序来执行,而以上三者的关系(SP)属性有:SNO.PNO.JNO.QTY是先做选择,后做连接,这正是系统对查询进行优化的结果。4.1嵌套查询与非 嵌套查询4.4引入视團1)非嵌套查询: elect distinct J. * from J,SPJ where J. JNO=为查询electx1. * from SPJ x1SPJ.JNO and J.CITY in(“北京','天津")。where exists (select # from SPJ 2嵌套查询:electJ. * fom J where J.CITY in(“北京',‘天津') and J.JNO in( select JNO from SPI)。having x1.QTY= MAX(x2.QTY))从执行情况看,系统在做优化操作时将两个查询语句作为建立视图:create view MAX(PNO, MAXQTY)同一种查询进行。因此对于同一个查询的不同查询语句有时as selet PNO,MAX(QTY) from SPJ group by PNO; .系统内部实现时可能会完全一样。则可以在MAX上进行如下查询:2)非嵌套查询:ele x1. # from S xI,S x where x1.STATUSselect distinct SP]. * from SPJ, MAX where SPJ.Q1Y = MAX.= x2.STATUS and x2.CIY=‘北京'。MAXQTY。嵌套查询:select s. # from s where s. STATUS in( selet其执行计划如图2所示。STATUS from s where CTTY=‘北京')。可以看出它们的执行效率和执行计划不同,前者用的是哈希匹配的方式,后者采用嵌套循环的方式。4.2相关嵌套查询与 非相关嵌套查询非相关嵌套查询:electS. # frorm swhere s. STATUS in( selet STATUSfrom s where CTTY=‘北京')。.相关嵌套查询:elctx1.* from Sx1where exits(select # from S x2中国煤化工.5.-where x2. CTTY=‘北京'and x1. STATUS= x2. STATUS)。YHCNMH G+%w结果是此相关嵌套查询与非相关嵌套查询执行效率和执(下转第32页)行计划完全相同,再换-个查询看结果如何:54●山西科技SHANXI SCIENCE AND TECHNOLOCY2008年第5期9月 20日出版设计职业生涯转换,就是员工在不同岗位序列的纵向通道竞 争性,保证让合适的人到合适的岗位上,提高晋升的有效性,转换的横向通道,以此来培养企业的中高层管理人员。从而降低企业领导人在员工提升问题上的主观性,避免一个员2.2 建立与职业生涯规划相配套的管理制度工的提升打击-大片员工积极性,甚至导致有潜力的员工的离建立配套的管理制度并不是要把企业目前的管理制度全职。 通过建立有效的晋升制度,企业大部分中高级管理人员可部推翻,而是对现有的制度进- -步健全完善,有效支持职业生以通过内部培养成长起来。涯规划。应包括以下配套管理制度:2.3协调好组织规划与员 工个人意愿之间的关系培训制度。根据企业发展战略及人力资源规划,培训制度企业发展对人员需求是职业生涯的落脚点,企业对此应有将职业生涯规划与企业竞争力连接起来,支持员工长期持续发清醒的认识,企业指导 员工进行职业生涯规划,其目的在于保展,实现培训的个性化,让员工主动培训,通过员工能力发展椎证员工发展与企业发展需求相-致.使企业的规划行为和各种动企业核心竞争力提升。投人更好的支持企业的发展,通过培训,将员工打造成企业需考核制度。根据企业发展战略与岗位工作需要,制定各岗要的人才。同时摆在员工面前的是预定的发展空间,员工在企位序列不同等级的能力标准,为考核能力评价提供基础。同时业的发展需求指导下来调整个人发展方向,通过提升员工个人将职业生涯中的能力评价、沟通与制定发展计划与绩效管理中能 力促进企业的发展。的绩效反馈.绩效改善计划有机地结合起来,既提高了管理效2.4 建 立合理有效的用人机制率,减少了重复劳动,又使员I能力发展与实际工作绩效相互职业生涯规划“双赢‘效果的实现,根本上依赖于用人机制印证,提高了制定工作计划的针对性、有效性。的调整。企业用人机制调整的关键是要在企业内形成以能力薪酬制度。通过考核,员工的能力评价应体现到薪酬中,为基础的人力资源管理氛围,建立竞争机制.选拔机制、激励机给予相应的激励。但应改善薪酬结构,增加薪酬宽带,使同一制,打破“ 官本位”和“论资排辈”,让员工建立个人发展与企业薪酬等级内容纳更多的员工能力差异,使员工的能力发展在薪发展的正相关连接,愿意与企业共同发展。酬制度中得以体现。参考文献晋升制度。企业实行职业生涯规划是与员工达成一个心[1] 赵西萍,等. 组织与人力资源管理[M].西安:西安交通大理契约,企业愿意为员工提供更大的职业发展空间,通过完善学出版社,999晋升制度,建立合理的甄选程序,提高晋升的公开性、公正性、[2] 黄雏德.人力资源管理[M].北京:高等教育出版社,00.Career Management of the Staff in Small and Medium - sized enterprisesLi Jinpoees measures to build a conplete career planning sytem, build a management system supporting career planning, coordinate the relations be-trween the organization panning and he will of the sa and build a reasonable and efective employment syatem.KEYWORDS: smal and medium - sized enterprises; saf; career management(上接第54页)有时会生成完全相同的执行计划,有时又会选取不同的执行计可以看出图2与图1完全相同，即它与不使用视图的执行划,这受到诸多因素的影响,其中优化的代价也是需要考虑的。计划一样,表明视图只是一组sQL语句,当执行时便去调用这组查询语句,把在视图中的查询语句转化为相应基本表上的查[1] 王珊,萨师煊.数据库系统概论[M].北京:高等教育出版社,2006.5结束语[2]王俊伟,史创明,等. SQL Server 2000中文版数据库管理与由于-一个查询往往有多种执行策略,策略好坏的差别也很应用[M].北京:清华大学出版社,2006.大,因此系统需要从中选择- -个较好的执行策略 ,即查询优化。[3] 姚春龙, 丁春欣,姜翠霞.数据库系统基础教程[M].北在SQL Sever 2000中,系统会根据实际情况对查询进行优化，京:北京航空航天大学出版社,2003.生成较优的执行计划;而对于同一查询的不同表示形式,系统Query Optimization of SQL ServerZhang Xing,Li Haifang中国煤化工ABSTRACT: After 8n itoduction of the performance, query and opinindYHC N M H G' analys the query opi-mizaion of SQL Server 2000 and compares the diferences of the running speed and. quco.KEYWORDS: systrm perdomance; query and optimization; SQL Server 2000