SQL语句的性能优化ORACLE.docx
- 文档编号:23797732
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:11
- 大小:22.42KB
SQL语句的性能优化ORACLE.docx
《SQL语句的性能优化ORACLE.docx》由会员分享,可在线阅读,更多相关《SQL语句的性能优化ORACLE.docx(11页珍藏版)》请在冰豆网上搜索。
SQL语句的性能优化ORACLE
ORACLESQL语句的性能优化
浪潮软件技术架构部
梁圣奇
2005/11/03
版本
日期
修改人
描述(注明修改的条款或页)
1.0
2005-11-3
梁圣奇(创建)
ORACLESQL语句的性能优化1
1.前言4
1.1.问题提出4
1.2.解决思路4
1.2.1.索引优化4
1.2.2.连接手段4
1.2.3.其他手段4
2.解决思路5
2.1.ISNULL与ISNOTNULL5
2.2.联接列||5
2.3.带通配符(%)的like语句6
2.4.Orderby语句6
2.5.NOT和<>操作符(不等于)7
2.6.IN和EXISTS8
2.7.使用UNIONALL代替UNION9
2.8.采用函数处理的字段不能利用索引9
2.9.条件内包括了多个本表的字段运算时不能进行索引9
2.10.WHERE后面的条件顺序影响10
2.11.查询表顺序的影响10
2.12.sql书写的影响10
2.13.应用ORACLE的HINT(提示)处理10
1.前言
1.1.问题提出
许多程序员认为查询优化是DBMS(数据库管理系统)的任务,与程序员所编写的SQL语句关系不大,这是错误的。
虽然现在的数据库产品在查询优化方面已经做得越来越好,但由用户提交的SQL语句是系统优化的基础,很难设想一个原本糟糕的查询语句经过系统的优化之后会变得高效,因此用户所写语句的优劣至关重要。
1.2.解决思路
优化器的优化基于用户对所查询表的内容和其他一些与服务器有关的因素,如Cache大小、Cache策略、I/O大小等。
SQL查询语句都可以有很多种查询计划,优化器将估计出全部的执行方法中所需时间最少的也就是所谓成本最低的一种方法。
一般来说硬盘访问是成本最高的操作,因此对用户来讲,使用什么索引和采用何种表的连接手段是优化查询的关键,而所有优化的进行都是基于用户所使用的查询语句中的where子句。
1.2.1.索引优化
要尽可能的使用索引,减少磁盘的io操作.
1.2.2.连接手段
在进行查询连接时优化器将所有连接的方法全部列举出来,计算每一种连接的成本,选择成本最低的一种。
如连接时用到的数据无法获得,一般系统会使用平均密度作为依据,估算可能的命中率。
如,一个存储过程或触发器中,有表达式的值在编译时无法得到,优化器就只能使用它的平均密度来估计命中的记录数。
例如:
DECLARE@valuemoney
SELECTnameFROMemployeeWHEREsalary=@value
只是由于@value的值在执行前不知道,它只能使用其平均密度来估计这条命令将要命中的记录数。
1.2.3.其他手段
如,数据表空间和索引表空间的分离,关系密切的表之间的表空间的分离,表空间的物理分布,都可以提高应用的性能。
2.解决思路
2.1.ISNULL与ISNOTNULL
不能用null作索引,任何包含null值的列都将不会被包含在索引中。
即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。
也就是说如果某列存在空值,即使对该列建索引也不会提高性能。
所以判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。
推荐方案:
用其它相同功能的操作运算代替,如
aisnotnull改为a>0或a>’’等。
不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。
建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)。
2.2.联接列||
对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。
我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个叫比尔.克林顿(BillCliton)的职工。
下面是一个采用联接查询的SQL语句,
select*fromemployss
where
first_name||''||last_name='BeillCliton';
上面这条语句完全可以查询出是否有BillCliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。
Select*fromemployee
where
first_name='Beill'andlast_name='Cliton';
遇到下面这种情况又如何处理呢?
如果一个变量(name)中存放着BillCliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?
可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。
下面是SQL查询脚本:
select*fromemployee
where
first_name=SUBSTR('&&name',1,INSTR('&&name','')-1)
and
last_name=SUBSTR('&&name',INSTR('&&name’,'')+1)
2.3.带通配符(%)的like语句
LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE‘%5400%’这种查询不会引用索引,而LIKE‘X5400%’则会引用范围索引。
一个实际例子:
用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BHLIKE‘%5400%’这个条件会产生全表扫描,如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。
如,要求在职工表中查询名字中包含cliton的人。
可以采用如下的查询SQL语句:
select*fromemployeewherelast_namelike'%cliton%';
这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。
在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。
然而当通配符出现在字符串其他位置时,优化器就能利用索引。
在下面的查询中索引得到了使用:
select*fromemployeewherelast_namelike'c%';
2.4.Orderby语句
ORDERBY语句决定了Oracle如何将返回的查询结果排序。
Orderby语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。
任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查orderby语句以找出非索引项或者表达式,它们会降低性能。
解决这个问题的办法就是重写orderby语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在orderby子句中使用表达式。
2.5.NOT和<>操作符(不等于)
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。
那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。
推荐方案:
用其它相同功能的操作运算代替,如
a<>0改为a>0ora<0
a<>’’改为a>’’
我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。
NOT可用来对任何逻辑运算符号取反。
下面是一个NOT子句的例子:
...wherenot(status='VALID')
如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。
NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。
换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:
...wherestatus<>'INVALID';
再看下面这个例子:
select*fromemployeewheresalary<>3000;
对这个查询,可以改写为不使用NOT:
select*fromemployeewheresalary<3000orsalary>3000;
虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。
第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。
2.6.IN和EXISTS
用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。
但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:
ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。
由此可见用IN的SQL至少多了一个转换的过程。
一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
如,
第一种格式是使用IN操作符:
wherecolumnin(select*from...where...);
第二种格式是使用EXIST操作符:
whereexists(select'X'from...where...);
我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。
在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
第二种格式中,子查询以‘select'X'开始。
运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。
这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。
相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。
通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。
Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。
在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。
这也就是使用EXISTS比使用IN通常查询速度快的原因。
同时应尽可能使用NOTEXISTS来代替NOTIN,尽管二者都使用了NOT(不能使用索引而降低速度),NOTEXISTS要比NOTIN查询效率更高。
推荐方案:
在业务密集的SQL当中尽量不采用IN操作符。
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:
用NOTEXISTS或(外连接+判断为空)方案代替
2.7.使用UNIONALL代替UNION
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。
如:
select*fromgc_dfysunionselect*fromls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案:
采用UNIONALL操作符替代UNION,因为UNIONALL操作只是简单的将两个结果合并后就返回。
2.8.采用函数处理的字段不能利用索引
如:
substr(hbs_bh,1,4)=’5400’,优化处理:
hbs_bhlike‘5400%’
trunc(sk_rq)=trunc(sysdate),优化处理:
sk_rq>=trunc(sysdate)andsk_rq 进行了显式或隐式的运算的字段不能进行索引,如: ss_df+20>50,优化处理: ss_df>30 ‘X’||hbs_bh>’X5400021452’,优化处理: hbs_bh>’5400021542’ sk_rq+5=sysdate,优化处理: sk_rq=sysdate-5 hbs_bh=5401002554,优化处理: hbs_bh=’5401002554’,注: 此条件对hbs_bh进行隐式的to_number转换,因为hbs_bh字段是字符型。 2.9.条件内包括了多个本表的字段运算时不能进行索引 如: ys_df>cx_df,无法进行优化 qc_bh||kh_bh=’5400250000’,优化处理: qc_bh=’5400’andkh_bh=’250000’ 2.10.WHERE后面的条件顺序影响 WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响, 如: Select*fromzl_yhjbqkwheredy_dj='1KV以下'andxh_bz=1 Select*fromzl_yhjbqkwherexh_bz=1anddy_dj='1KV以下' 以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。 oracle对条件顺序进行分析。 2.11.查询表顺序的影响 在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。 (注: 如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接) 2.12.sql书写的影响 同一功能同一性能不同写法SQL的影响 如一个SQL在 A程序员写的为Select*fromzl_yhjbqk B程序员写的为Select*fromdlyx.zl_yhjbqk(带表所有者的前缀) C程序员写的为Select*fromDLYX.ZLYHJBQK(大写表名) D程序员写的为Select*fromDLYX.ZLYHJBQK(中间多了空格) 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。 2.13.应用ORACLE的HINT(提示)处理 提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。 它可以对SQL进行以下方面的提示 目标方面的提示: COST(按成本优化) RULE(按规则优化) CHOOSE(缺省)(ORACLE自动选择成本或规则进行优化) ALL_ROWS(所有的行尽快返回) FIRST_ROWS(第一行数据尽快返回) 执行方法的提示: USE_NL(使用NESTEDLOOPS方式联合) USE_MERGE(使用MERGEJOIN方式联合) USE_HASH(使用HASHJOIN方式联合) 索引提示: INDEX(TABLEINDEX)(使用提示的表索引进行查询) 其它高级提示(如并行处理等等) ORACLE的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。 根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 语句 性能 优化 ORACLE