前往顾页
以后地位: 主页 > 收集编程 > Jsp实例教程 >

Oracle SQL的优化技能详解

时候:2011-04-26 18:48来源:知行网www.zhixing123.cn 编辑:麦田守望者

SQL的优化应当从5个方面进行调剂:
1.去失落不需求的年夜型表的全表扫描
2.缓存小型表的全表扫描
3.查验优化索引的利用
4.查验优化的连接办艺
5.尽可能减少履行打算的Cost

SQL语句:
是对数据库(数据)进行操纵的唯一路子;
耗损了70%~90%的数据库资本;自力于法度设想逻辑,相对对法度源代码的优化,对SQL语句的优化在时候本钱微风险上的代价都很低;
可以有不合的写法;易学,难精通。

SQL优化:
牢固的SQL誊写习惯,不异的查询尽可能保持不异,存储过程的效力较高。
应当编写与其格局分歧的语句,包含字母的年夜小写、标点标记、换行的地位等都要分歧
ORACLE优化器:
在任何可能的时候都会对表达式进行评价,并且把特定的语法布局转换成等价的布局,这么做的启事是
要么成果表达式可以或许比源表达式具有更快的速率
要么源表达式只是成果表达式的一个等价语义布局
不合的SQL布局偶然具有一样的操纵(比方:= ANY (subquery) and IN (subquery)),ORACLE会把他们映照到一个单一的语义布局。
1 常量优化:
常量的计较是在语句被优化时一次性完成,而不是在每次履行时。下面是检索月薪年夜于2000的的表达式:
sal > 24000/12
sal > 2000
sal*12 > 24000
如果SQL语句包含第一种环境,优化器会简朴地把它转变成第二种。
优化器不会简化超越比较符的表达式,比方第三条语句,鉴于此,应尽可能写用常量跟字段比较检索的表达式,而不要将字段置于表达式当中。不然没有体例优化,比如如果sal上有索引,第一和第二便可利用,第三就难以利用。

2 操纵符优化:
优化器把利用LIKE操纵符和一个没有通配符的表达式构成的检索表达式转换为一个“=”操纵符表达式。
比方:优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH'
优化器只能转换触及到可变长数据范例的表达式,前一个例子中,如果ENAME字段的范例是CHAR(10), 那么优化器将不做任何转换。

一般来讲LIKE比较难以优化。
此中:
~~IN 操纵符优化:
优化器把利用IN比较符的检索表达式替代为等价的利用“=”和“OR”操纵符的检索表达式。
比方,优化器会把表达式ename IN ('SMITH','KING','JONES')替代为
ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘
oracle 会将 in 前面的东西天生一张内存中的临时表。然掉队行查询。

若何编写高效的SQL:
当然要考虑sql常量的优化和操纵符的优化啦,别的,还需求:
1 公道的索引设想:
例:表record有620000行,试看在不合的索引下,下面几个SQL的运行环境:
语句A
SELECT count(*) FROM record
WHERE date >'19991201' and date <'19991214‘ and amount >2000
语句B
SELECT count(*) FROM record
WHERE date >'19990901' and place IN ('BJ','SH')
语句C
SELECT date,sum(amount) FROM record
group by date
1 在date上建有一个非聚集索引
A:(25秒)
B:(27秒)
C:(55秒)
阐发:
date上丰年夜量的反复值,在非聚集索引下,数据在物理上随机存放在数据页上,在范围查找时,必须履行一次表扫描才气找到这一范围内的全数行。
2 在date上的一个聚集索引
A:(14秒)
B:(14秒)
C:(28秒)
阐发:
在聚集索引下,数据在物理上按依次在数据页上,反复值也摆列在一路,因此在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了年夜范围扫描,进步了查询速率。
3 在place,date,amount上的组合索引
A:(26秒)
C:(27秒)
B:(<1秒)
阐发:
这是一个不很公道的组合索引,因为它的前导列是place,第一和第二条SQL没有援引place,是以也没有操纵上索引;第三个SQL利用了place,且援引的所有列都包含在组合索引中,构成了索引覆盖,所以它的速率是非常快的。
4 在date,place,amount上的组合索引
A: (<1秒)
B:(<1秒)
C:(11秒)
阐发:
这是一个公道的组合索引。它将date作为前导列,使每个SQL都可以操纵索引,并且在第一和第三个SQL中构成了索引覆盖,因此机能到达了最优。

总结1
缺省环境下建立的索引是非聚集索引,但偶然它其实不是最好的;公道的索引设想要建立在对各种查询的阐发和瞻望上。一般来讲:
丰年夜量反复值、且常常有范围查询(between, >,<,>=,<=)和order by、group by产生的列,考虑建立聚集索引;
经 常同时存取多列,且每列都含有反复值可考虑建立组合索引;在前提表达式中经常常利用到的不合值较多的列上建立检索,在不合值少的列上不要建立索引。比如在雇员 表的“性别”列上只需“男”与“女”两个不合值,是以就无需求建立索引。如果建立索引不单不会进步查询效力,反而会严峻降落更新速率。
组合索引要尽可能使关头查询构成索引覆盖,其前导列必然是利用最频繁的列。
2 避免利用不兼容的数据范例:
比方float和INt、char和varchar、bINary和varbINary是不兼容的。数据范例的不兼容可能使优化器无法履行一些原本可以进行的优化操纵。比方:
SELECT name FROM employee WHERE salary > 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们该当在编程时将整型转化成为货币型,而不要比及运行时转化。
3 IS NULL 与IS NOT NULL:
不 能用null作索引,任何包含null值的列都将不会被包含在索引中。即便索引有多列如许的环境下,只需这些列中有一列含有null,该列就会从索引中排 除。也就是说如果某列存在空值,即便对该列建索引也不会进步机能。任安在WHERE子句中利用is null或is not null的语句优化器是不允 许利用索引的。
5 IN、OR子句常会利用事情表,使索引见效:
如果不产生年夜量反复值,可以考虑把子句拆开。拆开的子句中应当包含索引。
6 避免或简化排序:
该当简化或避免对年夜型表进行反复的排序。当可以或许操纵索引主动以恰当的依次产生输入时,优化器就避免了排序的步调。以下是一些影响身分:
索引中不包含一个或几个待排序的列;
group by或order by子句中列的依次与索引的依次不一样;
排序的列来自不合的表。
为了不不需求的排序,就要精确地增建索引,公道地归并数据库表(固然偶然可能影响表的标准化,但相对效力的进步是值得的)。如果排序不成避免,那么该当试图简化它,如减少排序的列的范围等。

7 消弭对年夜型表行数据的依次存取:
在 嵌套查询中,对表的依次存取对查询效力可能产生致命的影响。比如采取依次存取战略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询 10亿行数据。避免这类环境的首要体例就是对连接的列进行索引。比方,两个表:门生表(学号、姓名、春秋??)和选课表(学号、课程号、成绩)。如果两个 表要做连接,就要在“学号”这个连接字段上建立索引。
还可利用并集来避免依次存取。固然在所有的查抄列上都有索引,但某些情势的WHERE子句逼迫优化器利用依次存取。下面的查询将逼迫对orders表履行依次操纵:
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
固然在customer_num和order_num上建有索引,但是在下面的语句中优化器还是利用依次存取途径扫描全部表。因为这个语句要检索的是分离的行的调集,所以应当改成以下语句:
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008
如许就可以操纵索带途径措置查询。

8 避免相关子查询:
一个列的标签同时在主查询和WHERE子句中的查询中呈现,那么很可能当主查询中的列值改变以后,子查询必须从头查询一次。查询嵌套层次越多,效力越低,是以该当尽可能避免子查询。如果子查询不成避免,那么要在子查询中过滤失落尽可能多的行。
9 避免坚苦的正轨表达式:
MATCHES和LIKE关头字支撑通配符婚配,技术上叫正轨表达式。但这类婚配特别破钞时候。
比方:SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
即便在zipcode字段上建立了索引,在这类环境下也还是采取依次扫描的体例。如果把语句改成SELECT * FROM customer WHERE zipcode >“98000”,在履行查询时就会操纵索引来查询,明显会年夜年夜进步速率。
别的,还要避免非开端的子串。比方语句:SELECT * FROM customer WHERE zipcode[2,3] >“80”,在WHERE子句中采取了非开端子串,因此这个语句也不会利用索引。

10 不充份的连接前提:
例:表card有7896行,在card_no上有一个非聚集索引,表account有191122行,在account_no上有一个非聚集索引,试看在不合的表连接前提下,两个SQL的履行环境:
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no
(20秒)
将SQL改成:
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no and a.account_no=b.account_no
(<1秒)
阐发:
在第一个连接前提下,最好查询计划是将account作外层表,card作内层表,操纵card上的索引,其I/O次数可由以下公式预算为:
外层表account上的22541页+(外层表account的191122行*内层表card上对应外层表第一行所要查找的3页)=595907次I/O
在第二个连接前提下,最好查询计划是将card作外层表,account作内层表,操纵account上的索引,其I/O次数可由以下公式预算为:

顶一下
(0)
0%
踩一下
(0)
0%
------分开线----------------------------
标签(Tag):数据库 oralce
------分开线----------------------------
颁发评论
请自发遵循互联网相关的政策法规,严禁公布色情、暴力、革命的谈吐。
评价:
神色:
考证码:点击我更换图片
猜你感兴趣