优化SQL语句.docx
- 文档编号:17609173
- 上传时间:2023-07-27
- 格式:DOCX
- 页数:27
- 大小:56.47KB
优化SQL语句.docx
《优化SQL语句.docx》由会员分享,可在线阅读,更多相关《优化SQL语句.docx(27页珍藏版)》请在冰点文库上搜索。
优化SQL语句
优化SQL语句
系统的运行性能如何,很大一部分是与编程人员有关。
本章介绍基于开销的SQL语句程序设计,包括:
●SQL语句优化方法
●优化目标
●最佳实例
●SQL语句优化技巧
●使用EXISTS和IN
§25.1SQL语句的优化方法
在提高SQL语句效率上,可以考虑下面几个方面:
●重新构造索引;
●重新构造语句;
●修改或禁止触发器;
●重新构造数据;
●及时统计CBO所用信息。
§25.1.1重新构造索引*
对于数据量的应用来说,经常性地进行重新构造索引和数据是一个好的方法。
可以进行下面的整理工作:
●清除那些不具有可选择性的索引,以提高DML处理速度;
●索引性能-即访问路径;
●考虑一般的哈希索引(不一定唯一)。
§25.1.2重新构造语句
在重构索引之后,你可以试着重构语句。
即重写那些效率差的语句。
如果你对语句的意图了解的话,就很容易对语句进行修改。
1.考虑可选择性的SQL语法:
由于SQL语言的灵活性,应用中多个语句更适合应用的需要。
虽然两个SQL语句可以产生同样的结果,但Oracle处理一个语句比处理多个组合而成的语句更快。
你可以使用EXPLAINPLAN语句的结果进行比较,从而确定哪个语句更有效。
看一下的特殊语句:
SELECTdname,deptno
FROMdept
WHEREdeptnoNOTIN
(SELECTdeptnoFROMemp);
实际是有两个语句组成,两个语句都是从dept表中取数,执行的是同一个功能。
两个语句返回在emp表中没有的所有dept行,每个语句都搜索emp子查询。
假定在emp表中建立了索引为deptno_index的话,执行计划是:
步骤3预示着Oracle要对emp表进行全表扫描。
这些全表扫描可能是时间的操作。
这里Oracle根本不使用索引,因为没有where子句。
然而,下面的语句使用索引也能查询到同样的行:
SELECTdname,deptno
FROMdept
WHERENOTEXISTS
(SELECTdeptno
FROMemp
WHEREdept.deptno=emp.deptno);
这里的执行计划是全表扫描和索引扫描。
由于在查询emp表时使用了where子句,所以它就使用了索引。
它的索引使用在时间上要比全表扫描要省许多。
而且,当第一次查到结果后,第二个SQL语句就比第一个语句快多了。
有此看出,如果你的应用中使用了NOTIN运算的话,请你将其换成NOTEXISTS运算。
2.使用AND和=谓词
随时都可以用等同连接(equijoin),正常情况下,等同连接的执行在非转换列上是很容易进行调整的。
3.选择有利的连接顺序
连接顺序对执行很有意义。
SQL语句调整的主要目标是避免执行不必要的工作,这样就考虑三个规则:
●如果通过索引可以得到行的话,就要避免全表扫描;
●如果你能使用的索引取到100行的话,就避免使用取到10,000行的索引;
●选择连接要注意选择连接较少行的表。
下面的例子说明连接顺序的效率:
SELECTinfo
FROMtabaa,tabbb,tabcc
WHEREa.acolBETWEEN:
alowAND:
ahigh
ANDb.bcolBETWEEN:
blowAND:
bhigh
ANDc.ccolBETWEEN:
clowAND:
chigh
ANDa.key1=b.key1
ANDa.key2=c.key2;
1).选择驱动表和驱动索引。
前三个条件是对单个表进行筛选,后两个条件是连接条件。
筛选条件主要是表和索引的选择。
2).选择正确的索引
当了解所要驱动的表后,就要选择有效的索引。
否则就可能出现全表扫描。
3).选择最好的连接顺序
4.使用非转换的列值
可以在where子句中使用非转换的列值,如:
WHEREa.order_no=b.order_no
这样的使用比下面的用法更好:
WHERETO_NUMBER(SUBSTR(a.order_no,instr(b.order_no,’.’)-1))
=TO_NUMBER(SUBSTR(a.order_no,instr(b.order_no,’.’)-1))
建议不要在where后使用SQL函数。
5.避免混合类型表达式
避免使用混合的类型表达式和警惕不明确的类型转换。
当你在VARCHAR2类型的索引列charcol列上使用索引时,可能将语句写成:
ANDcharcol=
这里的numxpr是数字类型表达式。
Oracle会将该表达式转换成:
ANDTO_NUMBER(charcol)=numexpr
这样就会有下面的结果:
●任何在列上使用表达式,比如函数,都会引起优化被忽略。
甚至是唯一索引也一样。
●如果系统在处理字符串时不作转换,则要返回错误。
要避免这样的情况发生,接要进行转换,如:
ANDcharcol=TO_CHAR(
作为选择,可以使所有类型直接转换,语句为:
numcol=charexpr
在numcol上允许使用索引,因为缺省的转换总是字符到数字。
6.特殊的值写单独的语句
SQL语句不是过程语言,用一条语句来完成多个不同的事情不是一个好的主意。
所以要完成两件工作,写两条语句比写一条要好。
例如:
SELECTinfoFROMtables
WHERE...
ANDsomecolumnBETWEENDECODE(:
loval,'ALL',somecolumn,:
loval)
ANDDECODE(:
hival,'ALL',somecolumn,:
hival);
这样写,数据库不会使用索引,因为这里用了BETWEEN子句。
如果要使用索引,只要给出:
loval和:
hival即可。
然后重写逻辑语句:
SELECT/*changethishalfofunionallifotherhalfchanges*/info
FROMtables
WHERE...
ANDsomecolumnBETWEEN:
lovalAND:
hival
AND(:
hival!
='ALL'AND:
loval!
='ALL')
UNIONALL
SELECT/*Changethishalfofunionallifotherhalfchanges.*/info
FROMtables
WHERE...
AND(:
hival='ALL'OR:
loval='ALL');
7.使用提示控制访问路径
使用优化器提示,即使用/*+ORDERED*/来控制访问路径。
这样的方法不传统的的技术要好。
例如:
SELECT/*+FULL(emp)*/e.ename
FROMempe
WHEREe.job=’CLERK';
ratherthan
SELECTe.enameFROMempe
WHEREe.job||''=’CLERK';
8.使用IN和NOTIN要注意
使用(NOT)EXISTS是很有用。
注意(NOT)EXISTS不等于NOTIN。
9.在应用中嵌如数据列表要注意
数据列表值总是以单引号引起,如:
WHEREtransportIN(’BMW’,’CITROEN’,’FORD’,HONDA’)
10.减少数据库的调用次数
使用适当地以单条语句INSERT、UPDATE或DELETE…RETURNING来查询和修改数据时,可以减少数据库的调用次数而提高性能。
见“Oracle8iSQLReferenceManual”。
11.谨慎使用管理视图
当对视图进行连接视图、执行视图的外连接、重复利用视图,要谨慎地使用。
1)连接视图
在访问视图时可以通过SGA区的SQL区来达到减少解释的开销。
但是Oracle建议不要某种类似联合或连接的视图操作。
看下面的操作:
CREATEVIEWdx(deptno,dname,totsal)
ASSELECTd.deptno,d.dname,e.sum(sal)
FROMempe,deptd
WHEREe.deptno=d.deptno
GROUPBYdeptno,dname;
SELECT*
FROMdx
WHEREdeptno=10;
在上例中,不好的就是用了GROUPBY,而且有在查询中加条件(连接操作)。
2)子查询嵌套
可以通过设置参数UNNEST_SUBQUERY=TRUE来启用子查询嵌套操作。
但是此参数不是基于开销的。
在缺省下是不需要设置的。
可以用NO_UNNEST来禁止子查询嵌套操作。
详细见“Oracle8iSQL”中UNNEST和NO_UNNEST参数。
3)执行视图的外连接
对于对个表视图的外连接,最好不要构造过于复杂的外连接。
要尽量简单。
比如可以建立下面视图:
CREATEVIEWempdept(empno,deptno,ename,dname)
ASSELECTe.empno,e.deptno,e.ename,d.dname
FROMdeptd,empe
WHEREe.deptno=d.deptno(+);
用简单的外连接查询来实现操作:
SELECTe.ename,d.loc
FROMdeptd,empdepte
WHEREd.deptno=e.deptno(+)
ANDd.deptno=20;
4)不能重复利用的视图
要了解编写视图就是为了让另外的程序可以直接对其进行操作后得到需要的结果。
而不是别的目的。
如:
SELECTdnamefromdxwheredeptno=10;
这里dx是一个视图,这样的用法不如直接从原始表中进行查询有效。
要通过dx视图反而效率低下。
§25.1.3调整或使触发器无效
使用触发器会消耗系统资源,如果系统使用了过多的触发器,则需要将部分触发器置为无效。
§25.1.4重组数据
在重建索引和重建语句后,可以再考虑重组数据:
●引入起源值,避免GROUPBY从句;
●实现丢失实体(missingentity)和交叉(intersection)表
●减少网络负载、迁移、复制及分区数据
作为数据分布策略的主要目的是定位每个数据的属性。
比如使网络行程最小化。
如果当前行程数过大,则移动(迁移)该数据是一个自然的解决方案。
然而,数据的非单一位置(数据放在多个地方)可以减少网络加载到接收层的时间或减少信息传输延迟。
在这种情况下,可考虑支持多个拷贝(复制数据)或在不同的地点保持有数据的不同部分(对于本地就相当于丢失实体)。
然而,要进行分布式查询,需要有效的代码与PL/SQL存储过程或用户接口代码结合一起使用。
当考虑交叉连接时,你可以:
1)从远程节点拿来数据并与本地执行连接;2)或将数据送到远程节点,再执行远程连接。
这样的选择,需要你考虑不同节点数据卷的关系。
§25.2优化目标
数据库开发人员都知道,结构查询语言(SQL)是用于执行数据库的操作。
但要很好的进行使用,还需要注意许多地方:
●调整序列SQL语句
●调整并行执行
●调整OLTP应用
一般来说,这样考虑应用的类型:
●数据仓库操作处理很高的数据量,具有很高的相关性。
●OLTP应用有很大的用户数,并且它们都与一系列的操作有关。
§25.2.1优化序列SQL语句
在孤立的环境下调整一个SQL语句的目的是:
在执行中使资源的使用减少到最小。
你可以选择不用修改应用而使用不同的SQL语法来得出语句的执行代价。
使用EXPLAINPLAN语句,比较各个语句的执行计划及代价。
另外要提示的是,在调整完SQL语句后要实际的运行一下应用才能了解到语句的效果。
§25.2.2优化并行执行
调整并行执行的目的是:
最大地发挥硬件的能力。
如果你有一个高性能的系统,有高优先的SQL语句在运行,则并行语句就可以使用所有有效的资源。
Oracle可以执行的下面的并行:
●并行查询;
●并行DML(包括INSERT,UPDATE,DELETE;APPEND提示,并行索引扫描);
●并行DDL;
●并行恢复;
●并行加载;
●并行传播(复制);
●高数据量的行处理。
如果处理的行数较多,可以考虑将其进行分割(split),使得单个进程只处理部分的行。
关于这里的相关信息,请参考“Oracle8I数据仓库指南”中的下面内容:
1)设置并行度和使能多用户
2)调整并行执行参数
3)建立并行索引
4)分区索引扫描
5)使用大块插入、更新和删除
如果具备并行环境,可考虑下面的内容:
●对称多处理器(SMP),集群或强大的并行系统;
●有效的I/O带宽;
●低利用的或闲置的CPU(如CPU使用小于30%);
●对附加的内存无效,如分类、哈西索引及I/O缓冲区等。
如果你的系统缺少以上这些特点,则并行可能不会有多大改善。
§25.2.3调整OLTP应用
调整OLTP应用大多涉及到调整SQL语句。
建议考虑两种设计方案:
使用SQL与共享PL/SQL
将分析减少到最小,就要在SQL语句中使用绑定变量的方法老实现。
这样,所有的用户都可以使用相同的SQL语句。
事务模式
如果性能已经达到极限并且这些用户愿意设计后应用的话,有经验的用户可以采用分散事务的方法来实现性能的改善。
触发器
如果过度的使用触发器的话就会影响系统的性能。
所以要用ALTERTRIGGER语句来设置触发器为脱机的状态。
§25.3实际优化例子
§25.3.1避免基于规则优化器技术
传统的基于规则的技术包括:
●不用索引:
–col+0或col||‘‘
–在列外套函数,如NVL(col,-999)或TO_NUMBER等。
●在FROM子句中工作表的次序
基于CBO选择最讲究连接顺序。
所以要主要工作表的次序。
§25.3.2索引代价
如果选择的索引的代价过高,即可选择性太少=满足的行太多,可考虑采用全表扫描方法。
例。
下面例子中有employee_num索引,但由于代价高而直接采用全表扫描:
SELECTemployee_num,full_nameNAME,employee_id
FROMmtl_employees_current_view
WHERE(employee_numLIKE'20%')AND
(organization_id=:
1)
ORDERBYemployee_num;
§25.3.3分析统计数据
要分析的统计数据包括:
●列统计
●数据偏移
●表统计
●索引统计
●分区统计
下面是基于CBO的例子:
SELECTitem.expenditure_item_id
FROMpa_taskst,
pa_expendituresexp,
pa_expenditure_typesetype,
pa_expenditure_itemsitem
WHERE
TRUNC(exp.expenditure_ending_date)<=TRUNC(NVL(TO_DATE(:
b0),
exp.expenditure_ending_date))
ANDexp.expenditure_status_code||''='APPROVED'
ANDexp.expenditure_group=NVL(:
b1,exp.expenditure_group)
ANDexp.expenditure_id=item.expenditure_id
AND(NVL(item.request_id,(:
b2+1))<>:
b2ORitem.cost_dist_rejection_codeIS
NULL)
ANDitem.cost_distributed_flag='N'andt.task_id=item.task_id
ANDt.project_id=DECODE(:
b4,0,T.project_id,:
b4)
ANDitem.expenditure_type=etype.expenditure_type
ANDetype.system_linkage_function||''=:
b6
ORDERBYitem.expenditure_item_date;
COSTDISTRIBUTEDFLAG
C7
N80,251
Y16,534,822
基于规则的规划(RulePlan)的情况:
Cost=SELECTSTATEMENT
COUNT(*)
Cost=SORTORDERBY
===================================
Cost=NESTEDLOOPS
Cost=NESTEDLOOPS
Cost=NESTEDLOOPS
Cost=TABLEACCESSBYINDEXROWIDPA_EXPENDITURE_ITEMS_ALL
Cost=INDEXRANGESCANPA_EXPENDITURE_ITEMS_N3:
COST_DISTRIBUTED_
FLAG
Cost=TABLEACCESSBYINDEXROWIDPA_EXPENDITURE_TYPES
Cost=INDEXUNIQUESCANPA_EXPENDITURE_TYPES_U1:
EXPENDITURE_TYPE
Cost=TABLEACCESSBYINDEXROWIDPA_EXPENDITURES_ALL
Cost=INDEXUNIQUESCANPA_EXPENDITURES_U1:
EXPENDITURE_ID
Cost=TABLEACCESSBYINDEXROWIDPA_TASKS
Cost=INDEXUNIQUESCANPA_TASKS_U1:
TASK_ID
基于开销的规划(CBOPlan-缺省)的情况:
Cost=6503SELECTSTATEMENT
Cost=6503SORTORDERBY
Cost=6489NESTEDLOOPS
Cost=6487NESTEDLOOPS
Cost=6478MERGEJOINCARTESIAN
Cost=6477TABLEACCESSFULLPA_EXPENDITURES_ALL
Cost=1SORTJOIN
Cost=1TABLEACCESSFULLPA_EXPENDITURE_TYPES
Cost=9TABLEACCESSBYINDEXROWIDPA_EXPENDITURE_ITEMS_ALL
Cost=4INDEXRANGESCANPA_EXPENDITURE_ITEMS_N1:
EXPENDITURE_ID
Cost=2TABLEACCESSBYINDEXROWIDPA_TASKS
Cost=1INDEXUNIQUESCANPA_TASKS_U1:
TASK_ID
使用提示强制使用规则的情况:
对于同样的要求,利用强制规则要比CBO要高出许多:
ThisillustratesthatthecostoftheRBOplanissignificantlyhigherthanthatofthe
thedefaultCBOgeneratedplan.
Cost=592532SELECTSTATEMENT
Cost=592532SORTORDERBY
Cost=592518NESTEDLOOPS
Cost=592516NESTEDLOOPS
Cost=587506NESTEDLOOPS
Cost=504831TABLEACCESSBYINDEXROWIDPA_EXPENDITURE_ITEMS_ALL
Cost=32573INDEXRANGESCANPA_EXPENDITURE_ITEMS_N3:
Cost=1TABLEACCESSBYINDEXROWIDPA_EXPENDITURE_TYPES
Cost=INDEXUNIQUESCANPA_EXPENDITURE_TYPES_U1:
Cost=2TABLEACCESSBYINDEXROWIDPA_EXPENDITURES_ALL
Cost=1INDEXUNIQUESCANPA_EXPENDITURES_U1:
Cost=2TABLEACCESSBYINDEXROWIDPA_TASKS
Cost=1INDEXUNIQUESCANPA_TASKS_U1:
重写SQL语句:
为了避免全表扫描,可以用更有效的可选择性过滤器来实现重写SQL语句。
下面的NVL函数是避免索引的使用:
SELECTitem.expenditure_item_id
FROMpa_taskst,
pa_expendituresexp,
pa_expenditure_typesetype,
pa_expenditure_itemsitem
WHERE
TRUNC(exp.expenditure_ending_date)<=TRUNC(NVL(TO_DATE(:
b0),
exp.expenditure_ending_date))
ANDexp.expenditure_status_code||''='APPROVED'
ANDexp.expenditure_group=:
b1
ANDexp.expenditure_id=item.expenditure_id
AND(NVL(item.request_id,(:
b2+1))<>:
b2ORitem.cost_dist_rejection_codeIS
NULL)
ANDitem.cost_distributed_flag='N'andt.task_id=item.task_id
ANDt.project_id=DECODE(:
b4,0,t.project_id,:
b4)
ANDitem.expenditure_type=etype.expenditure_type
ANDetype.system_linkage_function||''=:
b6
ORDERBYitem.expenditure_item_date
新的CBO规划:
Cost=32SELECTSTATEMENT
Cost=32SORTORDERBY
Cost=18NESTEDLOOPS
Cost=16NESTEDLOOPS
Co
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 优化 SQL 语句
![提示](https://static.bingdoc.com/images/bang_tan.gif)