SQL编写规范和优化讲稿

SQL编写规范和优化

SQL编写规范--1原则定义

1、要求代码行清晰、整齐，具有一定的可观赏性；

2、代码编写要充分考虑执行速度最优的原则；

3、代码行整体层次分明、结构化强；

4、代码中应有必要的注释以增强代码的可读性；

5、规范要求非强制性约束代码开发人员的代码编写行为，在实际应用中在不违反常规要求的前提下允许存在可理解的偏差。SQL编写规范--2大小写规则

1、所有的SQL语句中的保留字均采用全部大写，不要使用缩写；表别名也要大写；如ALLASCASECREATEDATABASEDELETEFROMININSERTJOINLEFTNONOTNULLOUTSELECTTABLETITLEUPDATEVIEWWHERE等。

2、表名、视图名、宏和存储过程名：全部小写；SQL编写规范--2大小写规则

3、字段名：每个单词的首字母大写，其余部分小写，如party_id,loc_id,prod_inst_id,Acct_Id,Type_Id等；使用如下语句获得字段列表:SQL编写规范--3缩进和换行

整个的SQL语句最好按照子句进行分行编写，SELECT/FROM/WHERE/UPDATE/INSERT等每个关键字都要另起一行。如：SQL编写规范--3缩进和换行SQL编写规范--3缩进和换行

1、同一级别的子句间要对齐

2、逗号放在每行的开头

3、分号放在SQL语句的最后，单独占一行

4、每行宽度不超过120字符(每个字符为8个点阵宽)，超过行宽的代码可折行与上行对齐编排；

5、每个字段后面使用字段标题作为注释

6、使用给出的语句获得字段列表和字段标题(借助UltraEdit列模式快速编辑)SQL编写规范--3缩进和换行下面的编写方式不是好的形式：在所有需要缩进的地方，每次缩进4格；在以下情况下需要缩进：

1、不同层次的SQL语句之间

2、SELECTINSERT等关键字之后的字段列表和关键字之间SQL编写规范--4别名

SQL语句别名的命名，分层命名，从第一层次至第四层次，分别用P、S、U、D（都是大写字母）表示，取意为Part,Segment,Unit,Detail。对于同一层次的多个子句，在字母后加1、2、3、4……区分。SQL编写规范--4别名

如下图所示：SQL编写规范--5运算符前后间间隔要求算术运算符、、逻辑运算符符的前后至少少要保留一个个空格，如下下图所示：SQL编写规范--6变量引用1、在SQL语句中引用变变量时，要在在变量名两端端加花括号2、对日期变量量的引用要在在单引号内，，如'${MYDATE}'SQL编写规范--7注释针对复杂的SQL语句，请尽量量增加相应的的注释说明，，以便自己和和其它同事事事后可以比较较容易的读懂懂和修改。注释中应包含含以下内容：：1、编写人/编写日期2、修改人/修改日期3、该脚本的编编写目的与主主要内容4、如果有特殊殊处理、特别别的技巧等内内容，一定要要在注释中详详细说明SQL编写规范--7注释5、每一段SQL的前面必须要要有注释，重重点说明该SQL的技术含义和和应用含义、、选择理由。。技术含义指指这段SQL在技术上这么么写的原因、、好处，应用用含义指这段段SQL从应用的角度度将，是为了了达到一个什什么样的目的的。如果有可可能，还需要要说明为什么么这么选择，，而不选择别别的方式的理理由。如果发发现了不足，，还可记录不不足的原因和和建议的解决决办法等。SQL编写规范--8连接的使用对于内连接和和外连接的使使用，要求该该使用外连接接的地方都已已经使用了外外连接，不需需要外连接的的地方一定不不使用外连接接。表中的字段若若是从其它表表引用的，要要确保该字段段在被引用的的表中存在。。SQL编写规范--8连接的使用要求所有的连连接都写成JOIN的形式，如：：SQL编写规范--8连接的使用而不要写成：：SQL编写规范--8连接的使用另外，为了保保证多表连接接的连接条件件穿透性，要要求在多表连连接的SQL书写时将连接接条件有机的的闭环起来，，尤其是多表表PI相同，并用PI连接时。SQL编写规范--8连接的使用例如：SQL编写规范--8连接的使用即连接条件如如下所示：SQL优化(teradata)SQL脚本优化原则则要优化脚本，，在深刻理解解业务逻辑的的基础上，一一个重要的方方式是一段一一段的查看SQL的执行计划，，然后针对执执行计划中不不合理、不优优化的地方对对SQL进行优化编写写，这项工作作需要实践经经验，经常看看执行计划会会有所帮助。。SQL优化(teradata)SQL脚本优化原则则表级优化可以以参照以下以以下优化原则则：(1)如果过滤性不不很强，又不不需要重分布布，对大表尽尽可能不要只只做一下过滤滤就进一次SPOOL，最好是直接接与别的表JOIN，边JOIN边过滤了；(2)如果过滤性非非常强，可以以只做一下过过滤就进一次次SPOOL；SQL优化(teradata)SQL脚本优化原则则(3)SPOOL空间尽量小原原则，即尽可可能使中间过过程中的SPOOL空间小一些，，这样可以减减小I/O以及继续关联联的代价；在在此原则下可可以引申出下下面几个具体体原则：(3.1)在几个表大小小差不多时，，过滤性条件件较强的先JOIN；(3.2)在大/大/小三个表内联联时，避免先先把两个大表表JOIN，除非过滤条条件非常强；；(3.3)在大/小/小三个表内联联时，尽量先先把两个小表表JOIN；(3.4)有时将看似没没必要的过滤滤条件加上，，在关联表较较多时可能有有效的减小SPOOL；如表A、B、C、D的关联字段均均为k，即A.k=B.kANDA.k=C.kANDA.k=D.k，而同时还有有条件substr(A.k,1,2)='01'，有时加上substr(B.k,1,2)='01'、substr(C.k,1,2)='01'、substr(D.k,1,2)='01‘会有好处；SQL优化(teradata)SQL脚本优化原则则(4)尽量避免大表表重分布；(5)当大表与很小小的表(记录数量级在在5位数以内)JOIN时，尽量让小小表Duplicate；(6)如果必须有重重分布时，尽尽量使之靠后后；(7)尽量减少较大大的中间过程程中的SPOOL空间重分布的的次数；SQL优化(teradata)SQL脚本优化原则则(8)遇到productjoin时要小心一些些；(9)尽量减少对大大表的扫描次次数；(10)在拆SQL时也应注意，，合起来虽然然可能大些，，但只扫描一一次大表，而而拆成多句后后就要多次扫扫描大表，可可能效率反降降；SQL优化(oracle)基于索引的SQL语句优化数据库的优化化方法有很多多种，在应用用层来说，主主要是基于索索引的优化。。难就难在如如何判断哪些些索引是必要要的，哪些又又是不必要的的。判断的最最终标准是看看这些索引是是否对我们的的数据库性能能有所帮助。。具体到方法上上，就必须熟熟悉数据库应应用程序中的的所有SQL语句，从中统统计出常用的的可能对性能能有影响的部部分SQL，分析、归纳纳出作为Where条件子句的字字段及其组合合方式；在这这一基础上可可以初步判断断出哪些表的的哪些字段应应该建立索引引。SQL优化(oracle)基于索引的SQL语句优化其次，必须熟熟悉应用程序序。必须了解解哪些表是数数据操作频繁繁的表；哪些些表经常与其其他表进行连连接；哪些表表中的数据量量可能很大；；对于数据量量大的表，其其中各个字段段的数据分布布情况如何；；等等。对于于满足以上条条件的这些表表，必须重点点关注，因为为在这些表上上的索引，将将对SQL语句的性能产产生举足轻重重的影响SQL优化(oracle)基于索引的SQL语句优化建立索引常用用的规则如下下：1、表的主键、、外键必须有有索引；2、数据量超过过300的表应该有索索引；3、经常与其他他表进行连接接的表，在连连接字段上应应该建立索引引；4、经常出现在在Where子句中的字段段，特别是大大表的字段，，应该建立索索引；5、索引应该建建在选择性高高的字段上；；SQL优化(oracle)基于索引的SQL语句优化6、索引应该建建在小字段上上，对于大的的文本字段甚甚至超长字段段，不要建索索引；7、复合索引的的建立需要进进行仔细分析析；尽量考虑虑用单字段索索引代替：A、正确选择复复合索引中的的主列字段，，一般是选择择性较好的字字段；B、复合索引的的几个字段是是否经常同时时以AND方式出现在Where子句中？单字字段查询是否否极少甚至没没有？如果是是，则可以建建立复合索引引；否则考虑虑单字段索引引；SQL优化(oracle)基于索引的SQL语句优化C、如果复合索索引中包含的的字段经常单单独出现在Where子句中，则分分解为多个单单字段索引；；D、如果复合索索引所包含的的字段超过3个，那么仔细细考虑其必要要性，考虑减减少复合的字字段；E、如果既有单单字段索引，，又有这几个个字段上的复复合索引，一一般可以删除除复合索引；；SQL优化(oracle)基于索引的SQL语句优化8、频繁进行数数据操作的表表，不要建立立太多的索引引；9、删除无用的的索引，避免免对执行计划划造成负面影影响；以上是一些普普遍的建立索索引时的判断断依据。一言言以蔽之，索索引的建立必必须慎重，对对每个索引的的必要性都应应该经过仔细细分析，要有有建立的依据据。SQL优化(oracle)基于索引的SQL语句优化太多的索引与与不充分、不不正确的索引引对性能都毫毫无益处：在在表上建立的的每个索引都都会增加存储储开销，索引引对于插入、、删除、更新新操作也会增增加处理上的的开销。另另外，过多的的复合索引，，在有单字段段索引的情况况下，一般都都是没有存在在价值的；相相反，还会降降低数据增加加删除时的性性能，特别是是对频繁更新新的表来说，，负面影响更更大。SQL优化(oracle)避免对列的的操作任何对列的的操作都可可能导致全全表扫描，，这里所谓谓的操作包包括数据库库函数、计计算表达式式等等，查查询时要尽尽可能将操操作移至等等式的右边边，甚至去去掉函数。。例1：下列SQL条件语句中中的列都建建有恰当的的索引，但但30万行数据情情况下执行行速度却非非常慢：select*fromrecordwheresubstrb(CardNo,1,4)='5378'(13秒)select*fromrecordwhereamount/30<1000（11秒）select*fromrecordwhereto_char(ActionTime,'yyyymmdd')='19991201'（10秒）SQL优化(oracle)避免对列的的操作由于where子句中对列列的任何操操作结果都都是在SQL运行时逐行行计算得到到的，因此此它不得不不进行表扫扫描，而没没有使用该该列上面的的索引；如如果这些结结果在查询询编译时就就能得到，，那么就可可以被SQL优化器优化化，使用索索引，避免免表扫描，，因此将SQL重写如下：：select*fromrecordwhereCardNolike'5378%'（<1秒）select*fromrecordwhereamount<1000*30（<1秒）select*fromrecordwhereActionTime=to_date('1999120差别是很明显的！SQL优化(oracle)避免不必要要的类型转转换需要注意的的是，尽量量避免潜在在的数据类类型转换。。如将字符符型数据与与数值型数数据比较，，ORACLE会自动将字字符型用to_number()函数进行转转换，从而而导致全表表扫描。例2：表tab1中的列col1是字符型（（char)，则以下语语句存在类类型转换：：selectcol1,col2fromtab1wherecol1>10，应该写为：：selectcol1,col2fromtab1wherecol1>'10'。SQL优化(oracle)增加查询的的范围限制制增加查询的的范围限制制，避免全全范围的搜搜索。例3：以下查询询表record中时间ActionTime小于2001年3月1日的数据：：select*fromrecordwhereActionTime<to_date('20010301','yyyymm')查询计划表表明，上面面的查询对对表进行全全表扫描，，如果我们们知道表中中的最早的的数据为2001年1月1日，那么，，可以增加加一个最小小时间，使使查询在一一个完整的的范围之内内。修改如如下：SQL优化(oracle)增加查询的的范围限制制select*fromrecordwhereActionTime<to_date('20010301','yyyymm')andActionTime>to_date('20010101','yyyymm')后一种SQL语句将利用用上ActionTime字段上的索索引，从而而提高查询询效率。把把'20010301'换成一个变变量，根据据取值的机机率，可以以有一半以以上的机会会提高效率率。同理，，对于大于于某个值的的查询，如如果知道当当前可能的的最大值，，也可以在在Where子句中加上上“AND列名<MAX(最大值)”。SQL优化(oracle)尽量去掉"IN"、"OR“含有"IN"、"OR"的Where子句常会使使用工作表表，使索引引失效；如如果不产生生大量重复复值，可以以考虑把子子句拆开；；拆开的子子句中应该该包含索引引。例4：selectcount(*)fromstuffwhereid_noin('0','1')（23秒）可以考虑将将or子句分开：：selectcount(*)fromstuffwhereid_no='0'selectcount(*)fromstuffwhereid_no='1'然后再做一一个简单的的加法，与与原来的SQL语句相比，，查询速度度更快。SQL优化(oracle)尽量去掉"<>“尽量去掉"<>"，避免全表表扫描，如如果数据是是枚举值，，且取值范范围固定，，则修改为为"OR"方式。例5：UPDATESERVICEINFOSETSTATE=0WHERESTATE<>0;以上语句由由于其中包包含了"<>"，执行计划划中用了全全表扫描（（TABLEACCESSFULL），没有用用到state字段上的索索引。实际际应用中，，由于业务务逻辑的限限制，字段段state为枚举值，，只能等于于0，1或2，而且，值值等于=1，2的很少，因因此可以去去掉"<>"，利用索引引来提高效效率。修改为：UPDATESERVICEINFOSETSTATE=0WHERESTATE=1ORSTATE=2。进一步的的修改可以以参考第4种方法。SQL优化(oracle)去掉Where子句中的ISNULL和ISNOTNULLWhere字句中的ISNULL和ISNOTNULL将不会使用用索引而是是进行全表表搜索，因因此需要通通过改变查查询方式，，分情况讨讨论等方法法，去掉Where子句中的ISNULL和ISNOTNULL。SQL优化(oracle)索引提高数数据分布不不均匀时查查询效率索引的选择择性低，但但数据的值值分布差异异很大时，，仍然可以以利用索引引提高效率率。A、数据分布布不均匀的的特殊情况况下，选择择性不高的的索引也要要创建。表ServiceInfo中数据量很很大，假设设有一百万万行，其中中有一个字字段DisposalCourseFlag，取值范围围为枚举值值：[0，1，2，3，4，5，6，7]。按照前面面说的索引引建立的规规则，“选选择性不高高的字段不不应该建立立索引，该该字段只有有8种取值，索索引值的重重复率很高高，索引选选择性明显显很低，因因此不建索索引。然而而，由于该该字段上数数据值的分分布情况非非常特殊，，具体如下下表：SQL优化(oracle)索引提高数数据分布不不均匀时查查询效率而且，常用用的查询中中，查询DisposalCourseFlag<6的情况既多多又频繁，，毫无疑问问，如果能能够建立索索引，并且且被应用，，那么将大大大提高这这种情况的的查询效率率。因此，，我们需要要在该字段段上建立索索引。SQL优化(oracle)利用HINT强制指定索索引在ORACLE优化器无法法用上合理理索引的情情况下，利利用HINT强制指定索索引。继续上面7的例子，ORACLE缺省认定，，表中列的的值是在所所有数据行行中均匀分分布的，也也就是说，，在一百万万数据量下下，每种DisposalCourseFlag值各有12.5万数据行与与之对应。。假设SQL搜索条件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引引进行数据据搜索效率率，往往不不比全表扫扫描的高，，ORACLE因此对索引引“视而不不见”，从从而在查询询路径的选选择中，用用其他字段段上的索引引甚至全表表扫描。根根据我们上上面的分析析，数据值值的分布很很特殊，严严重的不均均匀。SQL优化(oracle)利用HINT强制指定索索引为了利用索索引提高效效率，此时时，一方面面可以单独独对该字段段或该表用用analyze语句进行分分析，对该该列搜集足足够的统计计数据，使使ORACLE在查询选择择性较高的的值时能用用上索引；；另一方面，，可以利用用HINT提示，在SELECT关键字后面面，加上““/*+INDEX(表名称，索索引名称））*/”的方式，强强制ORACLE优化器用上上该索引。。比如：select*fromserviceinfowhereDisposalCourseFlag=1;SQL优化(oracle)利用HINT强制指定索索引上面的语句句，实际执执行中ORACLE用了全表扫扫描，加上上蓝色提示示部分后，，用到索引引查询。如如下：select/*+INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG)*/*fromserviceinfowhereDisposalCourseFlag=1;请注意，这这种方法会会加大代码码维护的难难度，而且且该字段上上索引的名名称被改变变之后，必必须要同步步所有指定定索引的HINT代码，否则则HINT提示将被ORACLE忽略掉。SQL优化(oracle)屏蔽无用索索引继续上面8的例子，由由于实际查查询中，还还有涉及到到DisposalCourseFlag=6的查询，而而此时如果果用上该字字段上的索索引，将是是非常不明明智的，效效率也极低低。因此这这种情况下下，我们需需要用特殊殊的方法屏屏蔽该索引引，以便ORACLE选择其他字字段上的索索引。比如如，如果字字段为数值值型的就在在表达式的的字段名后后，添加““+0”，为字符型型的就并上上空串：““||""””如：select*fromserviceinfowhereDisposalCourseFlag+0=6andworkNo='36'。不过，不要要把该用的的索引屏蔽蔽掉了，否否则同样会会产生低效效率的全表表扫描。SQL优化(oracle)分解复杂查查询，用常常量代替变变量对于复杂的的Where条件组合，，Where中含有多个个带索引的的字段，考考虑用IF语句分情况况进行讨论论；同时，，去掉不必必要的外来来参数条件件，减低复复杂度，以以便在不同同情况下用用不同字段段上的索引引。SQL优化(oracle)分解复杂查查询，用常常量代替变变量继续上面9的例子，对对于包含Where(DisposalCourseFlag<v_DisPosalCourseFlag)or(v_DisPosalCourseFlagisnull)and....的查询，(这里v_DisPosalCourseFlag为一个输入入变量，取取值范围可可能为[NULL，0，1，2，3，4，5，6，7])，可以考虑虑分情况用用IF语句进行讨讨论，类似似：IFv_DisPosalCourseFlag=1THENWhereDisposalCourseFlag=1and....ELSIFv_DisPosalCourseFlag=2THENWhereDisposalCourseFlag=2and....。。。。。。。SQL优化(oracle)like子句尽量前前端匹配因为like参数使用的的非常频繁繁，因此如如果能够对对like子句使用索索引，将很很高的提高高查询的效效率。例6：select*fromcitywherenamelike‘‘%S%’’以上上查查询询的的执执行行计计划划用用了了全全表表扫扫描描（（TABLEACCESSFULL），如果能够够修改为：select*fromcitywherenamelike‘‘S%’那么查询的执执行计划将会会变成（INDEXRANGESCAN），成功的利利用了name字段的索引。。这意味着OracleSQL优化器会识别别出用于索引引的like子句，只要该该查询的匹配配端是具体值值。因此我们们在做like查询时，应该该尽量使查询询的匹配端是是具体值，即即使用like‘‘S%’。SQL优化(oracle)用Case语句合并多重重扫描我们常常必须须基于多组数数据表计算不不同的聚集。。例如下例通通过三个独立立查询：例8：1）selectcount(*)fromempwheresal<1000;2）selectcount(*)fromempwheresalbetween1000and5000;3）selectcount(*)fromempwheresal>5000;这样我们需要要进行三次全全表查询，但但是如果我们们使用case语句：SQL优化(oracle)用Case语句合并多重重扫描selectcount(salewhensal<1000then1elsenullend)count_poor,count(salewhenbetween1000and5000then1elsenullend)count_blue_collar,count(salewhensal>5000then1elsenullend)count_poorfromemp;这样查询的结结果一样，但但是执行计划划只进行了一一次全表查询询SQL优化(oracle)使用nls_date_format例9：select*fromrecordwhereto_char(ActionTime,'mm')='12'这个查询的执执行计划将是是全表查询，，如果我们改改变nls_date_format，SQL>alertsessionsetnls_date_formate=’MM’;现在重新修改改上面的查询询：select*fromrecordwhereActionTime='12'这样就能使用用actiontime上的索引了，，它的执行计计划将是（INDEXRANGESCAN）SQL优化(oracle)使用基于函数数的索引前面谈到任何何对列的操作作都可能导致致全表扫描，，例如：select*fromempwheresubstr(ename,1,2)=’SM’’;但是这种查询询在客服系统统又经常使用用，我们可以以创建一个带带有substr函数的基于函函数的索引，，createindexemp_ename_substroneemp(substr(ename,1,2));这样在执行上上面的查询语语句时，这个个基于函数的的索引将排上上用场，执行行计划将是（（INDEXRANGESCAN）。SQL优化(oracle)基于函数的索索引要求等式式匹配上面的例子中中，我们创建建了基于函数数的索引，但但是如果执行行下面的查询询：select*fromempwheresubstr(ename,1,1)=’S’得到的执行计计划将还是（（TABLEACCESSFULL），因为只有有当数据列能能够等式匹配配时，基于函函数的索引才才能生效，这这样对于这种种索引的计划划和维护的要要求都很高。。请注意，向向表中添加索索引是非常危危险的操作，，因为这将导导致许多查询询执行计划的的变更。然而而，如果我们们使用基于函函数的索引就就不会产生这这样的问题，，因为Oracle只有在查询使使用了匹配的的内置函数时时才会使用这这种类型的索索引。SQL优化(oracle)使用分区索引引在用分析命令令对分区索引引进行分析时时，每一个分分区的数据值值的范围信息息会放入Oracle的数据字典中中。Oracle可以利用这个个信息来提取取出那些只与与SQL查询相关的数数据分区。例如，假设你你已经定义了了一个分区索索引，并且某某个SQL语句需要在一一个索引分区区中进行一次次索引扫描。。Oracle会仅仅访问这这个索引分区区，而且会在在这个分区上上调用一个此此索引范围的的快速全扫描描。因为不需需要访问整个个索引，所以以提高了查询询的速度。SQL优化(oracle)使用位图索引引位图索引可以以从本质上提提高使用了小小于1000个唯一数据值值的数据列的的查询速度，，因为在位图图索引中进行行的检索是在在RAM中完成的，而而且也总是比比传统的B树索引的速度度要快。对于于那些少于1000个唯一数据值值的数据列建建立位图索引引，可以使执执行效率更快快。SQL优化(oracle)决定使用全表表扫描还是使使用索引和所有的秘笈笈一样，最后后一招都会又又回到起点，，最后我们来来讨论一下是是否需要建立立索引，也许许进行全表扫扫描更快。在在大多数情况况下，全表扫扫描可能会导导致更多的物物理磁盘输入入输出，但是是全表扫描有有时又可能会会