《SQL 书写规范.docx》由会员分享，可在线阅读，更多相关《SQL 书写规范.docx（15页珍藏版）》请在课桌文档上搜索。
1、SQ1.书写规范SQ1.书写规范1SQ1.书写规范21.1选择最有效率的表名依次（只在基于规则的优化器中有效）21.2where子句中的连接依次31.3Select子句中避开运用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
2、.14.2建立和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。依据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来只占数据库系统性能提升的40%左右，其余的60%系统性能提升来自对应用程序的优化。很多优化专家认为，对应用程序的优化可以得到80$的系统性能的提升。因此，在此整理一些SQ1.书写规范，以期通过优化SQ1.达到提升系统性能的目的。1SQ1.书写规范1.1选择最有效率的表名依次（只在基于规则的优化器中有效）ORAC1.E的解析器依据从右到左的依次处理EROM子句中的表名，因此FROM子句中写在最终的表（基础表drivingtable）将被最先处理.
3、在FROM子句中包含多个表的状况下，你必需选择记录条数最少的表作为基础表.当ORAC1.E处理多个表时，会运用排序及合并的方式连接它们.首先，扫描第一个表（FROM子句中最终的那个表）并对记录进行派序，然后扫描其次个表（FRoM子句中最终其次个表），最终将全部从其次个表中检索出的记录与第一个表中合适记录进行合并.例如：表TABl16,384条记录表TAB21条记录选择TB2作为基础表（最好的方法）selectcount（*）fromtabi,tab2执行时间0.96秒选择TABl作为基础表（不佳的方法）selectcount（*）fromtab2,tabi执行时间26.09秒假如有3个以上的表
4、连接查询，那就须要选择交叉表(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_NOBET
5、WEEN1000AND20001.2where子句中的连接依次OraCle采纳自下而上的依次解析where子句依据这个原理，表之间的连接必需写在其他WhCrC条件之前，那些可以过滤掉最大数量记录的条件必需写在where子句的末尾例如：(低效，执行时间156.3秒)select*fromCmPewheresal50000andjob=manager,and25(selectcount(*)fromempwheremgr=e.empno);(高效，执行时间10.6秒)select*fromempewhere25(selectcount(*)fromempwheremgr=e.empno)andsa
6、l50000andjob=manager,;1.3QSelect子句中避开运用*当你想在select子句中列出全部的column时，运用动态Sql歹IJ弓I用*是一个便利的方法，不幸的是，这是一个特别低效的方法事实上，OraCle在解析的过程中，会将*依次转换成全部的列名这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间1.4削减访问数据库的次数当执行每条SqI语句时，OraCIe在内部执行了很多工作：解析SqI语句，估算索引的利用率，绑定变量，读数据块等等由此可见，削减访问数据库的次数，就能事实上削减oracle的工作量例如：以下有两种方法可以检索出雇员号等于0342或0291的职员
7、方法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)fr
8、omempwheredept_no=0020,andenamelikesmith%*;selectcount(*),sum(sal)fromempwheredept_no=0030andename1ikesmith%;你可以用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
9、)d0030_salfromempwhereenamelikesmith%;x表示任何一个字段类似的，decode函数也可以运用于groupby和Orderby子句中1.6用where子句替换having子句避开运用having子句，having只会在检索出全部记录之后才对结果集进行过滤，这个处理须要排序、统计等操作假如能通过where子句限制记录的数目，那就能削减这方面的开销例如：低效selectregion,avg(log_size)fromlocationgroupbyregionhavingregion!=Sydney,andregion!=*perth,高效selectregion,
10、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(T
11、ABNAME,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)FROM
12、EMP工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
13、*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
14、);为了提高效率.改写为：(方法一：高效)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=
15、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)WHEREEX
16、ISTS(SE1.ECTXFRoMEMPEWHEREE.DEPT_NO=D.DEPT_NO);EXISTS使杳询更为快速，因为RDBMS核心模块将在子查询的条件一旦满意后，立即返回结果.1.13运用union-all和union当SQ1.语句须要UNION两个查询结果集合时，这两个结果集合会以UNION-A1.1.的方式被合并,然后在输出最终结果前进行排序.假如用UNIONA1.1.替代UNION,这样排序就不是必要了.效率就会因此得到提高.举例：低效：SE1.ECTACCT_NUM,B1.NCE-MTFROMDebitjransactionswheretran_date=31-dec-95U
17、NIONSE1.ECTACCTJiUM,B1.NCE-MTFROMdebit_transactionswhereTRA1.DATE=31-dec-95高效：SE1.ECTACCT_NUM,B1.NCE-MTFROMDebitjransactionswheretranjjate=31-dec-95UNIONA1.1.SE1.ECT/CCT_NUM,B1.NCE-AMTFROMDebitjransactionswheretranjjate=31-dec-95须要留意的此，UniOnaIl将重复输出两个结果集合中相同记录，因此还是要从业务需求分析运用UnionaU的可行性关于索引下列阅历请参考：1)
18、.假如检索数据量超过30%的表中记录数，运用索引将没有显著的效率提高2).在特定状况下，运用索引或许会比全表扫描慢，但这是同一个数量级上的差距；而通常状况下，运用索引比全表扫描要快几倍乃至几千倍！1.14用索引提高效率索引是表的一个概念部分，用来提高检索数据的效率.事实上，ORAC1.E运用了一个困难的自平衡B-tree结构.通常，通过索引查询数据比全表扫描要快.当ORAC1.E找出执行查询和Update语句的最佳路径时，ORAC1.E优化器将运用索引.同样在联结多个表时运用索引也可以提高效率.另一个运用索引的好处是,它供应了主键(Primarykey)的唯一性验证.除了那些1.ONG或1.O
19、NGRAW数据类型，你可以索引几乎全部的列.通常，在大型表中运用索引特殊布效.当然，你也会发觉，在扫描小表时，运用索引同样能提高效率.虽然运用索引能得到查询效率的提高，但是我们也必需留意到它的代价.索引须要空间来存储，也须要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改.这意味着每条记录的INSERT,DE1.ETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引须要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢.1.14.1索引的操作ORAC1.E对索引有两种访问模式.索引唯一扫描(INDEXUNIQUESCAN)大多数状况下，优化器通过WHERE
20、子句访问INDEX.例如：表1.ODGING有两个索引：建立在1.ODGING列上的啡一性索引1.ODGING_PK和建立在MANAGER列上的非唯一性索弓I1.ODGlXGSMANAGER.SE1.ECT*FROM1.ODGINGWHERE1.ODGING=ROSEHI1.1.;在内部，上述SQ1.将被分成两步执行，首先，1.ODGING_PK索引将通过索引唯一扫描的方式被访问，获得相对应的ROWID,通过ROWID访问表的方式执行下一步检索.假如被检索返回的列包括在INDEX列中，ORAC1.E将不执行其次步的处理(通过ROWIf)访问表).因为检索数据保存在索引中，单单访问索引就可以完全
21、满意查询结果.下面SQ1.SE1.ECT只须要INDEXUNIQUESCAN操作.1.ODGINGFROM1.ODGINGWHERE1.ODGING=ROSEHI1.1.;索引范围查询（IDEXRANGESCAN）适用于两种状况：1.基于一个范围的检索2.基于非唯一性索引的检索例1:SE1.ECTI.OFXiINGFROM1.ODGINGWHERE1.ODGING1.IKEM%;WHERE子句条件包括一系列值，ORAC1.E将通过索引范围查询的方式查询1.ODGING_PK.由于索引范围查询将返回一组值，它的效率就要比索引唯一扫描低一些.例2:SE1.ECT1.ODGINGFROM1.ODGI
22、NGWHEREMANAGER=BI1.1.GATES;这个SQ1.的执行分两步，1.ODGlNG$MANAGER的索引范围查询（得到全部符合条件记录的RoIYID）和下一步同过ROWID访问表得到lodging列的值.由于1.odgingsmanager是一个非唯一性的索引，数据库不能对它执行索引唯一扫描.由于SQ1.返回1.ODGING列，而它并不存在于1.odgingjmanager索引中，所以在索引范围查询后会执行一个通过ROWID访问表的操作.WHERE子句中,假如索引列所对应的值的第一个字符由通配符（WI1.DCARD）起先，索引将不被采纳.SE1.ECT1.OrXJINGFROM1
23、.ODGINGWHEREMANAGER1.IKE%HNMN;在这种状况下，ORAC1.E将运用全表扫描.1.14.2建立索引表的主键、外键必需有索引；常常与其他表进行连接的表，在连接字段上应当建立索引：索引应当建立在杳询条件中进行比较的字段上，而不是建立在我们要找出来并且显示的字段上：在条件表达式中常常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引；比如在雇员表的性别列上只有男与女两个不同值，因此就无必要建立索引O假如建立索引不但不会提高查询效率，反而会严峻降低更新速度。组合索引要尽量使关键杳询形成索引覆盖，其前导列肯定是运用最常见的列。1.14.3索引失效：1.14.3.1IN
24、.OR子句常会运用工作表，使索引失效。假如不产生大量重曳值，可以考虑把子句拆开。拆开的子句中应当包含索引。例子如下：假如在Heldsl和fields2上同时建立了索引,fieldsl为主索引以下Sql会用到索引select*fromtabIenamelwherefieldsl=valuelandfields2=valuc2以下sql不会用到索弓Iselect*fromtabIenamelwherefieldsl=valuelorfields2=value21.14.3.2运用ISNU1.1.或ISNOTNU1.1.运用ISNU1.1.或ISNOTNU1.1.同样会限制索引的运用。因为NU1.1
25、.值并没有被定义。在SQ1.语句中运用NU1.1.会有很多的麻烦。因此建议开发人员在建表时，把须要索引的列设成NOTNU1.1.o假如被索引的列在某些行中存在NU1.1.值，就不会运用这个索引。避开在索引中运用任何可以为空的列，oracle将无法运用该索引对于单列索引，假如列包含空值，索引中将不存在此记录：对于复合索引，假如每个列都为空，索引中同样不存在此记录。假如至少有一个列不为空，则记录存在于索引中假如唯一性索引建立在表的a列和b列上，并且表中存在一条记录的a,b值为(123,null),oracle将不接受下一条具有相同a,b值(123,null)的记录插入假如全部的索引列都为空，Ora
26、CIe将认为整个键值为空，而空不行能等于空，因此你可以插入1000条具有相同键值的记录，当然它们都是空！因为空值不存在于索引列中，所以where子句中对索引列进行空值比较将使oracle停用该索引低效(索引失效)selectfromdepartmentwheredept_codeisnotnull1.14.3.30条件字段运用函数和表达式假如不运用基于函数的索引，那么在SQ1.语句的WHERE子句中对存在索引的列运用函数时，会使优化器忽视掉这些索引。下面的查询不会运用索引(只要它不是基于函数的索引)selectcmpno,ename,deptnofromempwheretrunc(hiieda
27、te)=Ol-MY-81;把上面的语句改成下面的语句，这样就可以通过索引进行查找。selectempno,ename,deptnofromempwherehiredate(to.date(4Ol-MAY-8)+0.9999);请务必留意，检索中不要对索引列进行处理，如:trim,to_date,类型转换等操作，破坏索引，运用全表扫描，影响Sql执行效率1.M.3.4同比较不匹配的数据类型比较不匹配的数据类型也是比较难于发觉的性能问题之一。留意下面查询的例子，account_number是一个VAReHAR2类型，在account-number字段上有索弓I。下面的语句将执行全表扫描。selec
28、tbank_name,address,city,state,zipfrombankswhereaccount_number=990354;Oracle可以自动把where子句变成to-number(account-number)=990354,这样就限制了索引的运用，改成下面的查询就可以运用索引：selectbank_name,address,city,state,zipfrombankswhereaccount_numbcr=990354;特殊留意：不匹配的数据类型之间比较会让OraCIe自动限制索引的运用，即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次全表扫描。1.1
29、4.3.5带通配符(%)的Iike语句同样以上面的例子来看这种状况。目前的需求是这样的，要求在职工表中查询名字中包含CIitOn的人。可以采纳如下的查询SQ1.语句:select*fromemployeewherelast_name1ike%cliton%;这里由于通配符（$）在搜寻词首出现，所以OraCle系统不运用Iastjamc的索引。在很多状况下可能无法避开这种状况，但是肯定要心中有底，通配符如此运用会降低杳询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了运用：select*fromemployeewherelast_name1ikec%,;1.
30、 14.3.6Orderby语句ORDERBY语句确定了Oracle如何将返回的查询结果排序。Orderby语句对要排序的列没有什么特殊的限制，也可以将函数加入列中（象联接或者附加等）0任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。细致检查Orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的方法就是重写Orderby语句以运用索引，也可以为所运用的列建立另外一个索引，同时应肯定避开在OrdCrby子句中运用表达式。1.14. 3.7及操作符（大于或小于操作符）大于或小于操作符一般状况下是不用调整的，因为它有索引就会采纳索引查找，但有的状况下可以对它进行优化，如一个表有100万记录，一个数值型字段A,30万记录的A=O,30万记录的=l,39万记录的A=2,1万记录的A=3。那么执行A2与A=3的效果就有很大的区分了，因为2时ORAC1.E会先找出为2的记录索引再进行比较，而A=3时ORAC1.E则干脆找到=3的记录索引。
