SQL 书写规范.docx

SQ1.书写规范SQ1.书写规范1SQ1.书写规范21.1选择最有效率的表名依次（只在基于规则的优化器中有效）21.2where子句中的连接依次31.3©Select子句中避开运用'31.4削减访问数据库的次数41.5运用decode函数来削减处理时间41.6用where子句替换having子句51.7削减对表的查询61.8运用表的别名(alias)71.9用exists替代in71.10用notexists替代notin71.11用表连接替换exists81.12用exists替换distinct91.13运用union-all和union91.14用索引提高效率101.14.1索引的操作101.14.2建立和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。依据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来只占数据库系统性能提升的40%左右，其余的60%系统性能提升来自对应用程序的优化。很多优化专家认为，对应用程序的优化可以得到80$的系统性能的提升。因此，在此整理一些SQ1.书写规范，以期通过优化SQ1.达到提升系统性能的目的。1SQ1.书写规范1.1选择最有效率的表名依次（只在基于规则的优化器中有效）ORAC1.E的解析器依据从右到左的依次处理EROM子句中的表名，因此FROM子句中写在最终的表（基础表drivingtable）将被最先处理.在FROM子句中包含多个表的状况下，你必需选择记录条数最少的表作为基础表.当ORAC1.E处理多个表时，会运用排序及合并的方式连接它们.首先，扫描第一个表（FROM子句中最终的那个表）并对记录进行派序，然后扫描其次个表（FRoM子句中最终其次个表），最终将全部从其次个表中检索出的记录与第一个表中合适记录进行合并.例如：表TABl16,384条记录表TAB21条记录选择TB2作为基础表（最好的方法）selectcount（*）fromtabi,tab2执行时间0.96秒选择TABl作为基础表（不佳的方法）selectcount（*）fromtab2,tabi执行时间26.09秒假如有3个以上的表连接查询，那就须要选择交叉表(intersectiontable)作为基础表，交叉表是指那个被其他表所引用的表.例如：EMP表描述了1.OCATION表和CATEGORY表的交集.SE1.ECT*FRoM1.OCATION1.,CATEGORYC,EMPEWHEREE.EMP一NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.1.OCN=1.1.OCN将比下列SQ1.更有效率SE1.ECT*FROMEMPE,1.OCATION1.,CATEGORYCWHEREE.CAT_NO=C.CT-NOANDE.1.OCN=1.1.OCNANDE.EMP_NOBETWEEN1000AND20001.2where子句中的连接依次OraCle采纳自下而上的依次解析where子句依据这个原理，表之间的连接必需写在其他WhCrC条件之前，那些可以过滤掉最大数量记录的条件必需写在where子句的末尾例如：(低效，执行时间156.3秒)select*fromCmPewheresal50000andjob='manager,and25(selectcount(*)fromempwheremgr=e.empno);(高效，执行时间10.6秒)select*fromempewhere25(selectcount(*)fromempwheremgr=e.empno)andsal50000andjob='manager,;1.3QSelect子句中避开运用'*'当你想在select子句中列出全部的column时，运用动态Sql歹IJ弓I用*'是一个便利的方法，不幸的是，这是一个特别低效的方法事实上，OraCle在解析的过程中，会将'*依次转换成全部的列名这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间1.4削减访问数据库的次数当执行每条SqI语句时，OraCIe在内部执行了很多工作：解析SqI语句，估算索引的利用率，绑定变量，读数据块等等由此可见，削减访问数据库的次数，就能事实上削减oracle的工作量例如：以下有两种方法可以检索出雇员号等于0342或0291的职员方法1(低效)selectemp-name1salary,gradefromempwhereemp_no=342：selectemp_name,salary,gradefromempwherecmp_no=291;方法2(高效)selecta.emp_name,a.salary,a.grade,b.emp_name,b.salary,b.gradefromempa,empbwherea.emp_no=342andb.emp_no=291;1.5运用decode函数来削减处理时间运用decode函数可以避开重熨扫描相同记录或重熨连接相同的表例如：selectcount(*),sum(sal)fromempwheredept_no='0020,andenamelike'smith%*;selectcount(*),sum(sal)fromempwheredept_no='0030'andename1ike'smith%'你可以用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,salnull)d0030_salfromempwhereenamelike'smith<%''x'表示任何一个字段类似的，decode函数也可以运用于groupby和Orderby子句中1.6用where子句替换having子句避开运用having子句，having只会在检索出全部记录之后才对结果集进行过滤，这个处理须要排序、统计等操作假如能通过where子句限制记录的数目，那就能削减这方面的开销例如：低效selectregion,avg(log_size)fromlocationgroupbyregionhavingregion!='Sydney,andregion!=*perth,高效selectregion,avg(log_size)fromlocationwhereregion!='Sydney,andregion!=*perth,groupbyregion1.7削减对表的查询在含有子查询的sql语句中，要特殊留意削减对表的查询例如：例如：低效SE1.ECTTAB_NAMEFROMTAB1.ESWHERETAB_NAME=(SE1.ECTTAB.NAMEFROMTAB工O1.UMNSVERSION=604)FROMTAB工O1.UMNS604)高效TAB1.ESWHEREANDDB_VER=(SE1.ECTDl1.VERWHEREVERSION=SE1.ECTTAB_NAMEFROMWHERE(TABNAME,DB_VER)=(SE1.ECTTAB_NAME,DB一VERFROMTAB_CO1.UMNSWHEREVERSION=604)Update多个Column例子：低效:UPDATEEMPSETEMP_CAT=(SE1.ECTMAX(CATEGORY)FROMEMP/ATEGORIES),SA1._RANGE=(SE1.ECTMAX(SAI._RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;高效：UPDATEEMPSET(EMP_CAT,SA1._RANGE)=(SE1.ECTMAX(CATEGORY),MAX(SA1._RANGE)FROMEMP工ATEGORIES)WHEREEMP_DEPT=0020;1.8运用表的别名(alias)当在Sql语句中连接多个表时，请运用表的别名并把别名前缀于每个column上这样可以削减解析的时间并削减那些由COIUmn歧义引起的语法错误1.9用exists替代in在很多基于基础表的查询中，为了满意一个条件，往往须要对另一个表进行联接.在这种状况下，运用EXISTS(或NOTEXISTS)通常将提高查询的效率.低效：SE1.ECT*FROMEMP(基础表)WHEREEMPNO0ANDDEPTNOIN(SE1.ECTDEPTNOFROMDEPTWHERE1.OC=ME1.B)高效：SE1.ECT*FROMEMP(基础表)WHEREEMPNO0ANDEXISTS(SE1.ECTXFROMDEPTWHEREDEPT.DEIyTNO=EMP.DEPTNOAND1.OC=ME1.B)1.10用notexists替代notin在子查询中，NOTIN子句将执行一个内部的排序和合并.无论在哪种状况下，NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避开运用NOTIN,我们可以把它改写成外连接(OUterJoinS)或NOTEXISTS.例如：SE1.ECTFROMEMPWHEREDEPT_NONOTIN(SE1.ECTDEPT_NOFROMDEPTWHEREDEPT-CT=A);为了提高效率.改写为：(方法一：高效)SE1.ECT.FROMEMPA,DEPTBWHERE.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNU1.1.ANDB.DEPT,CAT(+)=A(方法二：最高效)SE1.ECT.FROMEMPEWHERENOTEXISTS(SE1.ECTXFROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT.CT=)：1.11同用表连接替换exists通常来说，采纳表连接的方式比EXISTS更有效率SE1.ECTENAMEFROMEMPEWHEREEXISTS(SE1.ECTXFROMDEPTWHEREDEPT_N0=E.DEPT_N0ANDDEPT_CAT=);(更高效)SE1.ECTENMEFROMDEPTD,EMPEWHEREE.DEpT_N0=D.DEPT_N0AKDDEIr1.CAT=A：1.12用exists替换distinct当提交一个包含一对多表信息仕匕如部门表和雇员表)的查询时，避开在SE1.ECT子句中运用DISTINCT.一般可以考虑用EXIST替换例如：低效：SE1.ECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效：SE1.ECTDEPT_NO,DEPT_NAMEFROMDEPTf)WHEREEXISTS(SE1.ECTXFRoMEMPEWHEREE.DEPT_NO=D.DEPT_NO);EXISTS使杳询更为快速，因为RDBMS核心模块将在子查询的条件一旦满意后，立即返回结果.1.13运用union-all和