数据库编码规范

例会制度第页数据库编码规范技术研发中心2020年12月12日文档修订序号版本日期更改人描述（注明修改的条款或页）1V1.0初稿

目录1. 概述 41.1 编写目的 41.2 使用范围 41.3 参考文献 42. 数据库开发 52.1 概述 52.2 注释编写规范 52.3 SQL在JAVA代码中的编写规范 52.4 SQL编写原则 63. 附录 103.1 建议 103.2 SQL优化 133.2.1 利用NavicatPremium工具分析SQL执行效率 13

概述编写目的本文描述了使用SQL技术进行编程的一些规范。为保证在开发过程中产出高效、格式统一、易阅读、易维护的SQL代码，利于不同开发人员维护，所有的数据库代码和相关文档都应遵循本文档描述的规则和约定。使用范围开发人员、工程实施人员、系统维护人员参考文献《数据库开发规范》

数据库开发概述数据库开发工具使用NavicatPremium，便于应用统一的美化器对所有数据库程序代码实现统一的格式化；便于使用数据库程序模版创建统一格式的数据程序对象。数据库中存储的程序代码不可具备事务提交功能，所有事务的提交在应用层完成。数据库中的程序代码统一使用存储包的形式，不再出现单独的存储过程或者函数。存储包超过6000行需另建存储包。动态SQL执行必须使用BIND变量。程序代码内中不可出现DDL语句。注释编写规范每个数据库程序对象（包、包内过程函数、触发器、自定义类等）、变量及常量必须使用注释。注释方式：/**/SQL在JAVA代码中的编写规范SQL语句在程序中应尽量少的出现(写在数据库存储过程中，后台执行效率较高)，必须出现则一般以字符串的形式出现，现对程序中的SQL书写做以下约定。java代码中出现的SQL语句包括（字段名，表名，SQL关键字、保留字）均应采用大写形式；避免在java代码中，使用循环语句多次执行数据库查询；用于查询/更新/删除的SQL，WHERE条件固定的，必须使用预编译方式；对SQL语句加上适当的注释，特别是对语句上出现的枚举值要标明其含义；合理使用空格和缩进，保证SQL语句结构清晰；在SQL内置运算符前后加上空格；在SQL关键字、保留字前后都要有空格；拼接SQL时，新一行加上空格；SQL语句较长时，应把SQL语句放在一个方法内；在每一个子句及逻辑判断占一行；对子查询使用缩进，一般为4个字符；SQL语句应该保持在80行以内；代码中严禁使用SELECT*代替具体的字段名。Oracle在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。在做插入SQL时，必须列出要插入表的字段名。不允许出现类似如下SQL：INSERTINTOTABLE_NAME1SELECT*FROMTABLE_NAME2WHERE……;代码中定义的SQL，如果为定长SQL，必须定义为finalstatic类型。如果SQL中有特殊业务含义的值的引用，要加上注释说明如：举例：下面是修改一个用户状态的SQL语句//STATUS=’11’其中‘11’代表用户正常状态finalstaticStringupdateUserStatusSql=”UPDATEPUB_USERSSETSTATUS=’11’WHEREUSER_ID=’superadmin’SQL编写原则多表查询，要使用别名；当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。多表关联效率优化1)大数量的表和数据量非常小的表不宜直接做关联，可以考虑通过程序或子查询的方式先处理小表，根据获取的数据，再检索大表。2)WHERE条件顺序SQL解析器采用自下而上的顺序解析WHERE子句，建议将表之间的连接放在其他条件之前，将可以过滤掉最大数量记录的条件放在WHERE子句的末尾。低效SQL,执行时间156.3秒SELECT...

FROMEMPE

WHERESAL>50000

ANDJOB='MANAGER'

AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);高效SQL,执行时间10.6秒SELECT...

FROMEMPE

WHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)

ANDSAL>50000

ANDJOB='MANAGER';FROM子句顺序(选择基础表)：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表(放置在from子句的最后面)作为基础表。例如：表TAB1共16,384条记录表TAB2共1条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1，tab2执行时间0.96秒选择TAB2作为基础表(不佳的方法)selectcount(*)fromtab2，tab1执行时间26.09秒不要对索引列使用函数，不要在索引列上使用NOT，不要在索引列上使用计算，不要在索引列上使用ISNULL和ISNOTNULL，不要改变索引列的类型。对索引列使用函数会使索引失效，执行全表扫描，导致查询性能降低。对索引列使用NOT，会产生与上述操作相同的影响。对索引列使用计算，优化器将不再使用索引而使用全表扫描。例如:低效:SELECT…FROMDEPTWHERESAL*12>25000;高效:SELECT…FROMDEPTWHERESAL>25000/12;索引列上使用ISNULL和ISNOTNULL,该索引失效。例如：低效:(索引失效)SELECT…FROMDEPARTMENTWHEREDEPT_CODEISNOTNULL;高效:(索引有效)SELECT…FROMDEPARTMENTWHEREDEPT_CODE>=0;改变索引列类型,该索引失效。故查询时值应该与索引列类型保持一致。当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换。假设EMPNO是一个数值类型的索引列：SELECT…FROMEMPWHEREEMPNO=‘123'。实际上,经过Oracle类型转换,语句转化为:SELECT…FROMEMPWHEREEMPNO=TO_NUMBER(‘123')。幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变。现在,假设EMP_TYPE是一个字符类型的索引列：SELECT…FROMEMPWHEREEMP_TYPE=123。这个语句被Oracle转换为:SELECT…FROMEMPWHERETO_NUMBER(EMP_TYPE)=123。因为内部发生的类型转换,这个索引将不会被用到!为了避免Oracle对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来。例如：低效:SELECT…FROMDEPTWHERESUBSTR(STARTDATE,0,4)>’2012’;高效:SELECT…FROMDEPTWHERESTARTDATE>TO_DATE(‘2012-01-01’,’yyyy-MM-dd’);若索引建立在多个列上，则只有第一列作为查询条件时，优化器才会使用它，否则，忽略该索引使用全表扫描。这也是一条简单而重要的规则。其他索引失效的情况有：(1)‘!=’将不使用索引。记住,索引只能告诉你什么存在于表中,而不能告诉你什么不存在于表中。(2)‘||’是字符连接函数。就象其他函数那样,停用索引。(3)‘+’、‘-’、‘*’、‘/’、‘%’是数学函数。就象其他数学函数那样,停用索引。使用IN语句时，要确保IN中的数据不能超过1000。用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换,EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。例子：(低效):SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO(高效):SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X'FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);不准使用CROSSJOIN，使用OUTER，INNERJOIN用EXISTS替代IN、用NOTEXISTS替代NOTIN语句在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率，而在子查询中,NOTIN子句将执行一个内部的排序和合并。无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS。(低效)SELECT*FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A');

(高效)SELECT*FROMEMPEWHERENOTEXISTS(SELECT'X'FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT='A');若业务允许，用UNION-ALL替换UNIONUNIONALL是将多个查询语句，查询列意义相同并且一一对应，在一起执行。UNIONALL与UNION的区别是UNION会对查询出的多个结果集做排序和DISTINCT操作。而UNIONALL不会。因此在查询性能上,UNIONALL会比UNION快很多。因此在使用时要根据情况，看是否多个sql查询出的结果是否会有重复行，如果没有或业务上不关注重复，建议使用UNIONALL语法。DATA_DATE的使用对时间条件可使用“=”、“>=”、“<=”、“BETWENN...AND”、to_Date,不可使用任何函数。对较长的SQL要进行执行计划分析，对于在系统使用比较复杂的sql，需要与有经验的同事或技术经理讨论。

附录建议减少访问数据库的次数Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。优化GROUPBY提高GROUPBY语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。低效：SELECTJOB，AVG(SAL)FROMEMPGROUPbyJOBHAVINGJOB=“PRESIDENT‟ORJOB=“MANAGER‟高效：SELECTJOB，AVG(SAL)FROMEMPWHEREJOB=“PRESIDENT‟ORJOB=“MANAGER‟GROUPbyJOB用>=替代>如果DEPTNO上有一个索引，高效：SELECT*FROMEMPWHEREDEPTNO>=4低效：SELECT*FROMEMPWHEREDEPTNO>3两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。删除重复记录的方法：最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询。例子：SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VERFROMTAB_COLUMNSWHEREVERSION=604)比较：SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)用索引提高效率索引是用来提高检索数据的效率，Oracle使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时,Oracle优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价：索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的：ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>用UNION替换OR(适用于索引列)通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低。避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。如有可能,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写。任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。注意rownum的使用：1.rownum的使用：如下两条语句：selectrownum,id,namefromstudentwhererownum>2;selectrownum,id,namefromstudentwhererownum<=10;第一条语句的执行结果为空，第二条语句的执行结果为前10条记录；为什么会这样呢，我们知道rownum是伪列，是oracle为查询结果自动添加的伪列，第一行是1，如果whererownum>2，这时候查找第一