《SQL优化培训》PPT课件.ppt

ORACLESQL培训,概述,SQL共享原则SQL优化的一般性原则不要让Oracle做得太多给优化器更明确的命令减少访问次数细节上的影响表的链接,什么是好的SQL语句？,尽量简单，模块化易读、易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁,ORACLE优化器介绍,Oracle的优化器共有两种模式：RBO(基于规则)和CBO(基于成本)。CBO在ORACLE7引入，但在ORACLE8i中才成熟。ORACLE已经明确声明在ORACLE9i之后的版本中(ORACLE10G)，RBO将不再支持。因此选择CBO是必然的趋势。RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是通过对表进行分析后才出现的，很多时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息CBO和RBO作为不同的SQL优化器，对SQL语句的执行计划产生重大影响。以下提及的优化原则均基于CRO的优化模式。,SQL共享原理,ORACLE将执行过的SQL语句存放在内存的共享池(sharedbufferpool)中，可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.这个功能大大地提高了SQL的执行性能并节省了内存的使用。,SQL共享的三个条件,当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）两个语句所指的对象必须完全相同（同义词与表是不同的对象）两个SQL语句中必须使用相同的名字的绑定变量(bindvariables),共享SQL语句,注意：Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个条件：字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。例如：SELECT*FROMATABLE；和下面每一个SQL语句都不同：SELECT*fromATABLESelect*FromAtable；语句所指对象必须完全相同即两条SQL语句操作的数据库对象必须同一。语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量以相同的值：第一组selectpin，namefrompeoplewherepin=：blk1.pin；selectpin，namefrompeoplewherepin=：blk1.pin；第二组selectpin，namefrompeoplewherepin=：blk1.ot_jnd；selectpin，namefrompeoplewherepin=：blk1.ov_jnd；,什么叫做重编译问题,什么叫做重编译？下面这个语句每执行一次就需要在SHAREPOOL硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库如果绑定变量，则只需要硬解析一次，重复调用即可select*fromdConMsgwherecontract_no=32013484095139,绑定变量解决重编译问题,未使用绑定变量的语句beginforiin1.100loopexecuteimmediateinsertintoscott.testvalues(|i|);endloop;end;使用绑定变量的语句beginforiin100.200loopexecuteimmediateinsertintoscott.testvalues(:x)usingi;endloop;end;,访问数据表的方式,全表扫描全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(databaseblock)的方式优化全表扫描。通过ROWID访问表ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。,SQLTunning的重点,SQL:insert,update,delete,select;主要关注的是select关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置,SQL优化的一般性原则,目标：减少服务器资源消耗（主要是磁盘IO）；设计方面：尽量依赖oracle的优化器，并为其提供条件；合适的索引，索引的双重效应，列的选择性；编码方面：利用索引，避免大表FULLTABLESCAN；合理使用临时表；避免写过于复杂的sql，不一定非要一个sql解决问题；在不影响业务的前提下减小事务的粒度；,TunningTip的各个方面,1.不要让Oracle做得太多；2.给优化器更明确的命令；3.减少访问次数；4.细节上的影响；,1.不要让Oracle做得太多,避免复杂的多表关联,selectfromuser_filesuf,df_money_filesdm,cw_charge_recordccwhereuf.user_no=dm.user_noanddm.user_no=cc.user_noandandnotexists(select)?很难优化，随着数据量的增加性能的风险很大。,避免使用*,当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用*是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间；只提取你所要使用的列；使用别名能够加快解析速度；,避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUPBY子句,GROUPBY会触发嵌入排序(NESTEDSORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写.,例如:低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECTXFROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);,用EXISTS替换DISTINCT,用UNION-ALL替换UNION(ifpossible),当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序.举例:低效：SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95高效:SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONALLSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95,2.给优化器更明确的命令,自动选择索引,如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引举例:SELECTENAMEFROMEMPWHEREEMPNO=2326ANDDEPTNO=20;这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP_NO_IDX,至少要包含组合索引的第一列,如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引.SQLcreatetablemultiindexusage(indanumber,indbnumber,descrvarchar2(10);Tablecreated.SQLcreateindexmultindexonmultiindexusage(inda,indb);Indexcreated.SQLsetautotracetraceonlySQLselect*frommultiindexusagewhereinda=1;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OFMULTIINDEXUSAGE21INDEX(RANGESCAN)OFMULTINDEX(NON-UNIQUE)SQLselect*frommultiindexusagewhereindb=1;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OFMULTIINDEXUSAGE很明显,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引,避免在索引列上使用函数,WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描举例:低效：SELECTFROMDEPTWHERESAL*1225000;高效:SELECTFROMDEPTWHERESAL25000/12;,避免使用前置通配符,WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引将不被采用.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NOLIKE%109204421;在这种情况下，ORACLE将使用全表扫描.,避免在索引列上使用NOT,通常，我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.举例:低效:(这里,不使用索引)SELECTFROMDEPTWHEREDEPT_CODENOT=0;高效:(这里,使用了索引)SELECTFROMDEPTWHEREDEPT_CODE0;,避免在索引列上使用ISNULL和ISNOTNULL,避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空.因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。,避免出现索引列自动转换,当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换.假设EMP_TYPE是一个字符类型的索引列.SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NO=109204421这个语句被ORACLE转换为:SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHERETO_NUMBER(USER_NO)=109204421因为内部发生的类型转换,这个索引将不会被用到!,在查询时尽量少用格式转换,如用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),3.减少访问次数,减少访问数据库的次数,当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.类比，工程实施,使用DECODE来减少处理时间,例如:SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKESMITH%;SELECTCOUNT(*)，SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKESMITH%;你可以用DECODE函数高效地得到相同结果SELECTCOUNT(DECODE(DEPT_NO,0020,X,NULL)D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL)D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL)D0030_SALFROMEMPWHEREENAMELIKESMITH%;,减少对表的查询,在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604),4.细节上的影响,WHERE子句函数、表达式使用,最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。,Orderby语句,ORDERBY语句决定了Oracle如何将返回的查询结果排序。Orderby语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在orderby子句中使用表达式。,联接列,对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。select*fromemploysswherefirst_name|last_name=BeillCliton;系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。select*fromemployeewherefirst_name=Beillandlast_name=Cliton;,带通配符（%）的like语句,通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。select*fromemployeewherelast_namelike%cliton%;在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select*fromemployeewherelast_namelikec%;,用Where子句替换HAVING子句,避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:低效:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=SYDNEYANDREGION!=PERTH高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=SYDNEYANDREGION!=PERTHGROUPBYREGION顺序WHEREGROUPHAVING,用NOTEXISTS替代NOTIN,在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).使用NOTEXISTS子句可以有效地利用索引。尽可能使用NOTEXISTS来代替NOTIN，尽管二者都使用了NOT（不能使用索引而降低速度），NOTEXISTS要比NOTIN查询效率更高。例如:语句1SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMemp);语句2SELECTdname,deptnoFROMdeptWHERENOTEXISTS(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);2要比1的执行性能好很多。因为1中对emp进行了fulltablescan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。,用索引提高效率,索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证。通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。,避免在索引列上使用计算,WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描低效：SELECTFROMDEPTWHERESAL*1225000;高效:SELECTFROMDEPTWHERESAL25000/12;,用=替代,如果DEPTNO上有一个索引。高效:SELECT*FROMEMPWHEREDEPTNO=4低效:SELECT*FROMEMPWHEREDEPTNO3,通过使用=、B.COL4)、外连接(WHEREA.COL3=B.COL4(+)。下面以等值连接为例进行介绍。在后面的介绍中，都已：SELECTA.COL1,B.COL2FROMA,BWHEREA.COL3=B.COL4;为例进行说明，假设A表为RowSoruce1，则其对应的连接操作关联列为COL3；B表为RowSoruce2，则其对应的连接操作关联列为COL4；,表间连接,连接类型：排序-合并连接(SortMergeJoin(SMJ)嵌套循环(NestedLoops(NL)哈希连接(HashJoin)笛卡儿乘积(CartesianProduct),表间连接,内部连接过程：首先生成rowsource1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。随后生成rowsource2需要的数据，然后对这些数据按照与sortsource1对应的连接操作关联列(如B.col4)进行排序。最后两边已排序的行被放在一起执行合并操作，即将2个rowsource按照连接条件连接起来。,排序-合并连接(SortMergeJoin(SMJ),下面是连接步骤的图形表示：,如果rowsource已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操作，特别是对于较大的表。预先排序的rowsource包括已经被索引的列(如a.col3或b.col4上有索引)或rowsource已经在前面的步骤中被排序了。尽管合并两个rowsource的过程是串行的，但是可以并行访问这两个rowsource(如并行读入数据，并行排序).排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个rowsource都已经预先排序，则这种连接方法的效率较高。,排序-合并连接(SortMergeJoin(SMJ),SMJ连接的例子：SQLexplainplanforselect/*+ordered*/e.deptno,d.deptnofromempe,deptdwheree.deptno=d.deptnoorderbye.deptno,d.deptno;QueryPlan-SELECTSTATEMENTCHOOSECost=17MERGEJOINSORTJOINTABLEACCESSFULLEMPANALYZEDSORTJOINTABLEACCESSFULLDEPTANALYZED,描述该连接过程是一个2层嵌套循环，则外层循环的次数越少越好，这也就是我们为什么将小表或返回较小rowsource的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时不遵守这个理论依据，反而会获得更好的效率。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。,嵌套循环(NestedLoops(NL),内部连接过程：Rowsource1的Row1-Probe-Rowsource2Rowsource1的Row2-Probe-Rowsource2Rowsource1的Row3-Probe-Rowsource2.Rowsource1的Rown-Probe-Rowsource2从内部连接过程来看，需要用rowsource1中的每一行，去匹配rowsource2中的所有行，所以此时保持rowsource1尽可能的小与高效的访问rowsource2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则，目的是使整个连接操作产生最少的物理I/O次数，而且如果遵守这个原则，一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则，反而能用更少的物理I/O实现连接操作，那尽管违反指导原则吧！因为最少的物理I/O次数才是我们应该遵从的真正的指导原则，在后面的具体案例分析中就给出这样的例子。,嵌套循环(NestedLoops(NL),在上面的连接过程中，我们称RowSource1为驱动表或外部表。RowSource2被称为被探查表或内部表。在NESTEDLOOPS连接中，Oracle读取rowsource1中的每一行，然后在rowsourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理rowsource1中的下一行。这个过程一直继续，直到rowsource1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。如果drivingrowsource(外部表)比较小，并且在innerrowsource(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTEDLOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。,嵌套循环(NestedLoops(NL),如果不使用并行操作，最好的驱动表是那些应用了where限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU，多个硬盘控制器)，所以要具体问题具体对待。,嵌套循环(NestedLoops(NL),NL连接的例子：SQLexplainplanforselecta.dname,b.sqlfromdepta,empbwherea.deptno=b.deptno;QueryPlan-SELECTSTATEMENTCHOOSECost=5NESTEDLOOPSTABLEACCESSFULLDEPTANALYZEDTABLEACCESSFULLEMPANALYZED,理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。较小的rowsource被用来构建hashtable与bitmap，第2个rowsource被用来被hansed，并与第一个rowsource生成的hashtable进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hashtable中是否有匹配的行。特别的，当hashtable比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hashtable与bitmap的表为驱动表，当被构建的hashtable与bitmap能被容纳在内存中时，这种连接方式的效率极高。要使哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置hash_area_siz