ORACLE基于SQL查询优化培训文档

ORACLE基于SQL查询优化培训文档2013年11月查询优化基本准则详解2ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。例如：表TAB116384条记录表TAB21条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1，tab2执行时间0.96秒选择TAB2作为基础表(不佳的方法)selectcount(*)fromtab2，tab1执行时间26.09秒查询优化基本准则详解3ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如：(低效，执行时间156.3秒)SELECT…FROMEMPEWHERESAL50000ANDJOB=‘MANAGER'AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.6秒)SELECT…FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL50000ANDJOB=‘MANAGER';在进行多表关联时，多用Where语句把单个表的结果集最小化，多用聚合函数汇总结果集后再与其它表做关联，以使结果集数据量最小化查询优化基本准则详解4减少对表的查询在含有子查询的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)查询优化基本准则详解5用EXISTS替代IN在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOTEXISTS)通常将提高查询的效率。使用exists而不用IN因为Exists只检查行的存在，而in检查实际值例如：低效SELECT*FROMEMP(基础表)WHEREEMPNO0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB')高效SELECT*FROMEMP(基础表)WHEREEMPNO0ANDEXISTS(SELECT‘X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB')用IN的SQL性能总是比较低，原因是：对于用IN的SQL语句ORACLE总是试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录如果转换成功就转换成多个表的连接。因此不管理怎么，用IN的SQL语句总是多了一个转换的过程。因此在业务密集的SQL当中尽量不采用IN操作符。查询优化基本准则详解6用NOTEXISTS替代NOTIN在子查询中，NOTIN子句将执行一个内部的排序和合并。无论在哪种情况下，NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOTIN，我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.例如：SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A');为了提高效率。改写为：SELECT….FROMEMPEWHERENOTEXISTS(SELECT‘X'FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A');NOTIN操作符此操作强烈推荐不使用，因为其不能应用表的索引。如遇这种情况，应该用EXISTS，NOTEXISTS方案代替。查询优化基本准则详解7用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换例如：低效：SELECTDISTINCTDEPT_NO,DEPT_NFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效：SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X'FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);查询优化基本准则详解8用表连接替换EXISTS通常来说，采用表连接的方式比EXISTS更有效率例如：SELECTENAMEFROMEMPEWHEREEXISTS(SELECT‘X'FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A');为了提高效率。改写为：SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT=‘A';查询优化基本准则详解9避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。这是一个非常实用的规则，请务必牢记举例：低效：SELECT…FROMDEPTWHERESAL*1225000;高效：SELECT…FROMDEPTWHERESAL25000/12;查询优化基本准则详解10避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。当ORACLE“遇到”NOT，他就会停止使用索引转而执行全表扫描。举例：低效：(这里，不使用索引)SELECT…FROMDEPTWHEREDEPT_CODENOT=0;高效：(这里，使用了索引)SELECT…FROMDEPTWHEREDEPT_CODE0;查询优化基本准则详解11用=替代举例：如果DEPTNO上有一个索引，高效：SELECT*FROMEMPWHEREDEPTNO=4低效：SELECT*FROMEMPWHEREDEPTNO3两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。查询优化基本准则详解12操作符不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。举例：A0-A0ORA0A''-A''查询优化基本准则详解13like操作符遇到需要用到LIKE过滤的SQL语句，完全可以用instr代替。处理速度将显著提高Instr用法已经分享过，详情见以前分享邮件！查询优化基本准则详解14用(UNION)UNIONALL替换OR(适用于索引列)通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择OR而降低。在下面的例子中，LOC_ID和REGION上都建有索引。在下面的例子中,LOC_ID和REGION上都建有索引低效：SELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHERELOC_ID=10ORREGION=“MELBOURNE”高效：SELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHERELOC_ID=10UNIONSELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHEREREGION=“MELBOURNE”如果你坚持要用OR，那就需要返回记录最少的索引列写在最前面。注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.查询优化基本准则详解15优化GROUPBY提高GROUPBY语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。举例：低效：SELECTJOB，AVG(SAL)FROMEMPGROUPbyJOBHAVINGJOB=‘PRESIDENT'ORJOB=‘MANAGER'高效：SELECTJOB，AVG(SAL)FROMEMPWHEREJOB=‘PRESIDENT'ORJOB=‘MANAGER'GROUPbyJOB使用where而不是having，where是用于过滤行的，而having是用来过滤组的，因为行被分组后，having才能过滤组，所以尽量用WHERE过滤查询优化基本准则详解16优化GROUPBY提高GROUPBY语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。举例：低效：SELECTJOB，AVG(SAL)FROMEMPGROUPbyJOBHAVINGJOB=‘PRESIDENT'ORJOB=‘MANAGER'高效：SELECTJOB，AVG(SAL)FROMEMPWHEREJOB=‘PRESIDENT'ORJOB=‘MANAGER'GROUPbyJOB使用where而不是having，where是用于过滤行的，而having是用来过滤组的，因为行被分组后，having才能过滤组，所以尽量用WHERE过滤查询优化基本准则详解17避免改变索引列的类型.:当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换.假设EMPNO是一个数值类型的索引列.SELECT…FROMEMPWHEREEMPNO=‘123'实际上,经过ORACLE类型转换,语句转化为:SELECT…FROMEMPWHEREEMPNO=TO_NUMBER(‘123')幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.假设EMP_TYPE是一个字符类型的索引列.SELECT…FROMEMPWHEREEMP_TYPE=123这个语句被ORACLE转换为:SELECT…FROMEMPWHERETO_NUMBER(EMP_TYPE)=123因为内部发生的类型转换,这个索引将不会被用到!查询优化基本准则详解181).应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描2).应尽量避免在where子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。3).应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描4).in和notin也要慎用，否则会导致全表扫描5).在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致6).任何地方都不要使用select*fromt，用具体的字段列表代替“*”，不要返回用不到的任何字段。查询优化基本准则详解19SQL书写的影响同一功能同一性能不同写法SQL的影响如一个SQL在A程序员写的为select*fromzl_yhjbqkB程序员写的为select*fromdlyx.zl_yhjbqk（带表所有者的前缀）C程序员写的为select*fromDLYX.ZLYHJBQK（大写表名）D程序员写的为select*fromDLYX.ZLYHJBQK（中间多了空格）四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL都会对其进行一次分析，并且占用共享内存，如果将SQL的字