SQL语句优化原则

福建电脑2004年第8期SQL语句优化原则盘莉莉(桂林航天工业高等专科学校计算机系广 西桂林541004)[摘要]在数据库中,对于大量的数据, SQL语句消耗了70%至90%的数据库资源,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就行,而是要写出高质量的SQL语句,提高系统的可用性。SQL优化的实质就是在结果正确的前提下,优化器根据定义的索引来提高查找效率,尽量避免全表扫描的发生。[关键词] SQL;DBMS;优化1问题的提出要求在学生表中查询名字中包含jeumy的人。可以采用如下的在应用系统开发初期,由于开发数据库数据比较少,对于查询 sQL语句:查询SQL语句,复杂视图的编写等体会不出SQL语句各种写select # from student where last-name like '% jeuny % '法的性能优劣,但是如果将应用系统提交实际应用后,随著数这里由于通配符( % )在搜寻词首出现,将不使用last-name据库中数据的增加,系统的响应速度就成为目前系统需要解决的索引。 在很多情况下可能无法避免这种情况,但是一定要心的最主要的问题之-。DBMS优化一个很重要的方面就是中有底 ,通配符如此使用会降低查询速度。然而当通配符出现SQL语句的优化。所谓优化即where子句利用了索引,不可优在字 符串其他位置时,优化器就能利用索引。在下面的查询中化即发生了表扫描或额外开销。如果在SQL语句的where子索引得到了 使用:句中写的代码不合理,就会造成优化器删去索引而使用全表扫select # from student where last name like 'j% '描,一般把这种SQL语句称为劣质SQL语句。在编写SQL语.2.4 Order by语句句时我们应清楚优化器根据何种原则来删除索引,这有助于写ORDER BY语句决定了查询结果顺序。Order by语句对出高性能的sQL语句。要排序的列没有什么特别的限制，也可以将函数加入列中。任2 saL语句优化原则何在Orderby语句的非索引项或者有计算表达式都将降低查下面就某些SQL语句的where子句编写中需要注意的问询速度。 仔细检查order by语句以找出非索引项或者表达式, .题作详细介绍。在这些where子句中,即使某些列存在索引,但它们会降低性能。 解决这个问题的办法就是重写order by语句是由于编写了劣质的SQL,DBMS在运行该SQL语句时也不能以使用索引, 也可以为所使用的列建立另外-个索引,同时应使用该索引,而同样使用全表扫描,这就造成了响应速度的极绝对避免在 order by子句中使用表达式。大降低。2.5 NOT2.1 ISNULL与IS NOT NUL我们在查询时经常在where子句使用一些逻辑表达式，如不能用null作索引,任何包含null值的列都将不会被包含大于(>)、小于(<= 等于(=)以及不等于(<>)等等，也可在索引中。即使索引有多列这样的情况下,只要这些列中有一以使用 and(与)、or(或)以及nor(非)。NOT 可用来对任何逻辑列含有null,该列就会从索引中排除。也就是说如果某列存在运算符号取反。 下面是- -个NOT子句的例子:空值,即使对该列建索引也不会提高性能。where not (age = 18)任何在where子句中使用is null或is not null的语句优化如果要使用NOT,则应在取反的短语前面加上括号,并在器是不允许使用索引的。短语前面加上NOT运算符。NOT运算符包含在另外-个逻辑2.2任何对列的操作都将导致表扫描运算符中,这就是不等于(<> )运算符。换句话说，即使不在假定有一个学生表(student) ,对于一个学生的姓和名分成查询where子句中显式地加人NOT词,NOT仍在运算符中,见两列存放(FIRST-.NAME和LAST-NAME) ,现在要查询- -个叫下例:"Bill jeumy"的学生。select # from student whereage <> 18应当采用下面这种SQL语句的编写,DBMS系统就可以采对这个查询,可以改写为不使用NOT:用基于last name创建的索引。select # from student where age<18 σr age>18Select # from student where first-name = 'ill' and last-name =虽然这两种查询的结果- -样 ,但是第二种查询方案会比第'jeuny '-种查询方案更快些。第二种查询允许对age列使用索引,而遇到下面这种情况又如何处理呢?如果-一个变量(name) 第-种查询则不能使用索引。中存放着Bill jeuny这个学生的姓名,对于这种情况我们又如何2.6 IN和EXISTS避免全程遍历,使用索引呢?可以使用一个函数INSTR,将变有时候会将- -列和一 系列值相比较。最简单的办法就是量name中的姓和名分开就可以了,但是有-点需要注意,这个在 where子句中使用子查询。在where子句中可以使用两种格函数是不能作用在索引列上。任何对列的操作都将导致表扫式的子查询。描,它包括数据库函数、计算表达式等等,查询时要尽可能将操第一种格式是使用IN操作符:作移至等号右边。下面是SQL查询语句:select # from student where第中国煤化工first-name = SUBSTR (‘ &&name '，1， INSTR(‘&name',' ')-1)我桶YHCN M H G,因为它比较容易编SUBSTR (' &&name'，INSTR 写,而实际上第二种格式要远比第-一种格式的效率高。在SQL(‘&&name',' ')+1)中几乎可以将所有的IN操作符子查询改写为使用EXISTS的2.3带通配符( % )的like语句子查询。同样以上面的例子来看这种情况。目前的需求是这样的,第二种格式中,子查询以select # '开始。(下转第 12頁12福建电脑2004年第8期.们将纵横两个方面提到为区域划分和行政等级。区域代表横门用一台计算机连 上-根电话线，上网帮助农民下载最新的农向;中央-地方两级部门代表纵向。业技术知识,或者是寻找到城市打工的就业信息，可能是最有首先,从横向上来看,实效的,这远要比购买- -大批机器设备建立-一个没有 任何业务一东部地区。 人均GDP较高,资金有保障,内在和外在的机关局域网好得多。动力充足,有条件大力推进电子政务,并且在利用电子政务手地级市(以及直辖市)应该是地方电子政务建设的重点层.段打破行政体制阻力,重组政府业务流程,整合政府资源方面面,同样分析地级市 的EG - Readines:1、人均GDP较高,政府创造出好的经验;财政能力较好,能够为电子政务建设提供资金保障;2、管辖地一中部地区。 在资金相对有保障、具备外部和内部动力域广 阔,人口数量巨大,政府行政事务特别是公共服务事务的的情况下,可以积极借鉴东部发达地区的经验,稳妥扎实地推规模巨大,具有通过电子政务 手段来提高工作效率、改进工作进电子政务;质量的内在动力;3、直接面向社会公众,面对强大的要求行政一西部地区。 在资金有限,外部和内部动力不足的情况透明、政务公开的社会舆论监督 和压力。因此,从资金保降、内下,应该切实根据本地区的实际需要,有针对性地利用信息技外部动力等各方 面来看,地级市政府具有建设电子政务的良好术解决实际困难和问题,不宜在电子政务建设上全面铺开,搞条件和动机。 实贱也证明,地级市政府在外部、内部压力的共大推进。同推动下,根据建立服务型政府的要求,在建设社保、医保系其次,从纵向.上来看，借助EG- Readiness的概念,也可以统,推进网 上审批提供在线服务等方面,取得了很好的效果。对不同层次的电子政务进行分析。在进行具体分析之前,要注比较起来, 省级政府的职能主要集中在宏观管理和调控意到一个事实:在不同层次的政府部门(中央政府、省级政府、上,公共服务事务较少 ,而且不直接面对社会公众,缺乏外部动市级政府等)中,政府职能的侧重点也略有不同，中央政府和省力 ,因此,EG- Readiness较差。因此,人均GDP较高的省份,在级政府的职能主要集中在宏观管理和调控,即管理职能上;而能够提供资 金保障的情况下,可以从本省的国民经济和社会发市级政府直接面对社会公众,为民服务的压力与日俱增。展的实际需求出发,把电子政务建设的重点放在促进政府部门一中央政府。 我个人认为，中央政府的EG - Readines间的互联互通 ,共享政府信息资源,形成协同运作的宏观管理处于中等偏上水平介于中部和东部地区之间。从人均GDP来和调控体系上。 人均GDP不高,经济发展水平较差的省份,在看,中央政府的人均GDP即全国的人均GDP,是平均水平。因内外部动力不足 ,需求不充分的情况下.如果花费大笔资金全此,建设资金相对有保障;由于我国地域辽阔,人口众多,经济面推进电子政务 ,效益有限。和社会发展情况复杂,中央政府部门承担着异常繁重的行政管再来看县级市,人均GDP可能有高有低,社会公众的参与理职能。因此,虽然大部分中央政府部门不直接面对社会公众度也可 能有差别,但是政府行政事务规模却已经由城市规模确服务,但是管理职能的繁重,加上政府机构改革的不断推进,人定,因此,我个人认为,一般来看 ,县级市普遍缺乏电子政务建员精简所以行政事务规模显得非常庞大,提高工作效率、改进设的内在 动力,而且由于政府行政事务规模不大，采用信息技工作流程的内在动力不断加大;此外,社会公众对中央政府的术 、全面推行电子政务也不太容易收到实际的效益。当然, 也行政事务参与度虽然不大,但关注的程度却超出任何其他级别不排除有 的县级市在具备-定条件情况下,通过比较务实的方的政府,因此，中央政府也有很强的舆论监督的外在压力。因式能够较好地发展电子政务。此,中央政府具有较高水平的EG- Rediness,有推进电子政务纵横交叉之后,可以得出一个结论，中央政府的重点领域的强烈意图。但是制约的因素是,中央财政的资金即使是相对和地级市(特 别是东部发达地区的地级市),可能是中国目前推有保障的,但是由于目前财政状况8益紧张,也不可能无限制进电子政务 的优先领域或者说是可能率先取得突破,创造先进地全面投资。因此,中央政府要面向未来,参照政府行政体制经验的领域。 我们应该根据需求导向、务求实效的原则,有重改革,根据政府职能转变的要求,针对本届政府面临的挑战,在点有步骤地扎实推进。宏观管理、政府收支和社会稳定等重点职能领域,加快电子政此外,一个值得注意的问题是,由于EG - Readines的存务建设的进度,在其他领域则应量力而行。在,决定了各地区、各层次政府部门电子政务建设起步水平的一地方政府。 情况千差万别,必须认真分析EG- Readi-.差距, 而由于电子政务对政府行政能力提高的加速作用,可能ness的各项指标,确定推进电子政务的时机和重点。在EG-会造 成不同地区政府部门行政能力差距的进一步加大,我们可Readiness 并不充分的条件下,是花大力气全面推行电子政务，以称之为“EG- DIVIDE"--电子政务鸿沟。 这个问题需要引还是根据自己的实际情况在局部有针对性地利用信息技术解起我们的重视,并在实践中进一步研究加以解决。决现实问题,这是需要认真考虑的。例如在贫困山区,政府部(上接第42页)运用EXISTS子句不管子查询从表中抽取什么数据它只查看时表中以后再执行主查询。 这也就是使用EXISTS比使用INwhere子句。这样优化器就不必遍历整个表而仅根据索引就可通 常查询速度快的原因。完成工作(这里假定在where语句中使用的列存在索引)。相对同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子者都使里了 NOT1不能使田虔引而降低速度) ,NOT EXISTS要查询困难一些。比NO中国煤化工通过使用EXISTS,DBMS系统会首先检查主查询,然后运MH| CN M H G过程,涉及数据库层的行子查询直到它找到第一个匹配项,这就节省了时间。而资源配直 、网冶层的重化稱以区保TF承统层的总体设计。我DBMS系统在执行IN子查询时,首先执行子查询，并将获得的只是在开发 应用软件过程有一些体会和心得,在编写高效的结果列表存放在一-个加了索引的临时表中。在执行子查询之sQL 语句作-些探索。前,DBMS系统先将主查询挂起,待子查询执行完毕,存放在临