数据库SQL编写规范

 

第一章 数据库SQL编写规范
1.1 语法规范
1.1.1 关键字及变量命名
SQL中的关键字均使用大写字母，字段名、表名均使用小写字母。
数据表最好起别名，以便于的优化器快速分析。
1.1.2 INSERT语句
尽量不要使用 INSERT INTO table VALUE(?,?,?,?,?)格式，要指出具体要赋值的字段，INSERT．．．SELECT的效率会有提高。
1.1.3 查询语句 SELECT
SELECT与FROM语句之间只定义返回的字段名，除非返回所有的字段，尽量不要使用 * ，字段名应按照表的字段物理顺序编写，字段提取要按照“需多少、提多少”的原则（因为大批量数据的抽取会影响SQL缓存的效率）
同时使用COUNT(*)也是要避免的，因为COUNT(*)会对全字段做聚集，建议使用COUNT (0)或COUNT(1)这样的查询语句。
另外条件中使用OR 会引起全表扫描，比较影响查询效率，尽可能少用或不用，实在不行可以用UNION代替。NULL的检索要回避一下。
1.子查询
在子查询中，如果有2层嵌套，假如每层都查询1000行，那么这个查询就要查询100万行数据，查询嵌套层次越多,效率越低,因此应当尽量避免子查询。假如子查询不可避免,那么要在子查询中过滤掉尽可能多的行。能用直连接代替的就用直连接。
2．通配符
在SQL语句中，LIKE关键字支持通配符匹配，但这种匹配非常耗费时间。例如：cust_nm like '%$cust_nm$%'。即使在cust_nm字段上建立了索引，在这种情况下也还是采用顺序扫描的方式，查询表中有1000条记录，就需要比较1000次。执行查询时最好利用索引来查询，会大大提高速度。
3． DISTINCT　　
使用DISTINCT是为了保证在结果集中不出现重复值，但是DISTINCT会产生一张工作表，并进行排序来删除重复记录，这会大大增加查询和I/O的操作次数。因此应当避免使用DISTINCT关键字。
4. 负逻辑
负逻辑如!=、<>、not in等，都会导致ORACLE用表扫描来完成查询。当表较大时，会严重影响系统性能，可以用别的操作来代替。
5. 应当尽可能提供一些冗余的搜索参数，使优化器有更多的选择余地。请看以下2种方法：
第一种方法：
select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code='01') and (employee.dep_code='01');
第二种方法：
Select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (employee.dep_code='01');
6. ORDER BY和GROPU BY
     使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，优化器将无法优化。
1.1.4 连接（ 最多5个）
join 与on 必须严格匹配，不允许出现没有on的join。
(LEFT JOIN 和全关联，特别是多个表进行关联，因为每个关联表查询的时候，进行扫描的时候都是一个笛卡尔乘积的数量级，扫描数量很大，如果确实是需要进行关联操作，请给where或者on的条件进行索引。)
join…on 后面慎用 or ，如果用到，请把or的范围用( )括起来。
SQL语句包含多表连接时，必须对每个表命名别名，对每个字段的使用都要带上别名。
Where 子句中的连接顺序：oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。如：
低效：select * from emp e where sal>5000 and job = ‘manager’ and 25< (select count (*) from emp where mgr=e.empno);
高效：select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;
尽量不使用右连接。
参与左连接的列不能为常量。例如，不允许如下语句： select * from t1 left outer join t2 on t1.f1='A'。
左连接的写法必须带“outer”关键字。例如： select t1.f1 from t1 left outer t2 on t1.f1 = t2.f1；而不是： select f1 from t1 left t2 on t1.f1 = t2.f1。
判断是外联接中的主表还是从表主要看from从句中各个表在LEFT JOIN或RIGHT JOIN两边的位置：LEFT JOIN左边的表是主表，RIGHT JOIN右边的表是主表；ON表达了两个表连接的条件，一般外联接是等值联接，不等值联接意义不大。
1.1.5 联合
在使用UNION或UNION  ALL 的前后的两个SQL需要加 ( )。
union扫描的是全索引，可以适当用集合差MINUS 和 集合交INTERSECT 来代替。

1.1.6 group by
提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。如：
低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’; 
高效： select job, avg(sal) from emp having  job=’president’ or job=’manager’ group by job;
1.2 注意事项
1.2.1 避免使用IN和NOT IN
避免使用IN和NOT IN ，使用EXIST和NOT EXIST代替。
1.2.2 将计算从等号左边移到右边
将计算从等号左边移到右边,例如：把a*2>4　改为a>4/2；把TO_CHAR(zip) = ‘94002’ 改为zip = TO_NUMBER('94002')。
1.2.3 不要使用NOT
不要使用NOT，如Goods_no != 2，要改为:  where Goods_no>2 or Goods_no<2
1.2.4 不要使用is null
不要使用is null , 如WHERE DEPT_CODE IS NOT NULL 要改为:  WHERE DEPT_CODE >=0;
1.2.5 删除全表数据
删除全表数据时使用truncate 替代 delete,同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml语句。
1.2.6 尽量多使用commit
尽量多使用commit只要有可能就在程序中对每个delete、insert、 update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。
1.2.7 分页技术和锁
对于返回大记录的操作使用分页技术。注意及时释放相关资源和锁。
1.2.8 循环封装
尽量把循环的操作封装到PL/SQL写的存储过程里，因为存储过程都在服务端执行，所以没有数据往返的消耗。 有机会，将一些查询封装到函数里，而在普通SQL里使用这些函数，同样是很有效的优化。
1.2.9 绑定变量
开发人员编写sql语句时，尽量使用绑定变量，增加sql在sharepool中重用命中的几率。
1.2.10 大数据量的操作
大表中删除大记录：分段循环删除，批量绑定删除 ；大表中更新大记录：分段更新。
1.3 索引规范
对于开销比较大的SQL查询，要建立数据库索引。数据库索引可以大大加快数据库的查询速度，索引是把表中的逻辑值映射到ROWID，因此索引能进行快速定位数据的物理地址，快速返回想要查询的结果集。
1.3.1 索引不被使用
索引不被使用的几种情况：null，比较，not函数
1.3.2 强制SQL使用索引
可以通过index hints来强制SQL使用索引。Index Hints的格式如下:/*+ INDEX ( table [index [index]...] ) */

1.3.3 索引列上不要计算
索引列上不要进行计算，如where trunc(order_date)=trunc(sysdate) ,  i+2>4。索引失效的原因也简单，索引是针对原值建的二叉树，你将列值*3/4+2折腾一番后，原来的二叉树当然就用不上了。解决的方法:
(1)换成等价语法，比如trunc(order_date) 换成where order_date>trunc(sysdate)-1 and order_date<trunc(sysdate)+1
(2)特别为计算建立函数索引：create index I_XXXX on shop_order(trunc(order_date))
1.3.4 索引列上>= 代替 >
高效：select * from emp where deptno >=4
低效：select * from emp where deptno > 3
两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
1.3.5 索引重建
索引会定时重建的情况：层数超过4层，索引中删除的数据超过索引总数据的20%，加载大记录时删除索引，加载后会重建索引；当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。
1.3.6 索引数量
索引数量应不超过列总数的40%。
1.3.7 索引创建的个数以及在where条件中出现的顺序
索引创建的规则如下：
通常如果一个表有5个字段经常作为查询条件，那么创建5个单独字段的索引，然后再创建一个包含这5个字段的组合索引。索引的列尽量编写在where条件语句的最后，以便执行计划命中索引。
第二章 PL/SQL规范
2.1 规范
2.1.1 存储过程编写方式
按模块建立PACKAGE，并在其下建立FUNCTION、PROCEDURE。
具体命名规则如下：WMS_模块信息
如下：
CREATE OR REPLACE PACKAGE WMS_SYS_MANAGEMENT AS
/********************************************************************
* SYSTEM     : Wealth Management System of BOC
* SUBSYSTEM :System Management
* DESCRIPTION:DELETE OVERDUE DATA.
* AUTHOR:XXX/DC
*MODIFY:
*    PERSONNAME        DATE        DETAIL
*    XXX               2008-7-30   ADD PROCEDURE WMS_DELETE_HISDATA
********************************************************************/

PROCEDURE WMS_DELETE_HISDATA; 
--DELETE OVERDUE DATA
 
END WMS_SYS_MANAGEMENT;

CREATE OR REPLACE PACKAGE BODY MST_PRD.WMS_SYS_MANAGEMENT AS

PROCEDURE WMS_DELETE_HISDATA IS
/********************************************************************
* SYSTEM     : Wealth Management System of BOC
* SUBSYSTEM :System Management
* DESCRIPTION:DELETE OVERDUE DATA.
* AUTHOR:XXX/DC
*MODIFY:
*    PERSONNAME        DATE        DETAIL
*    XXX               2008-7-30   ADD PROCEDURE WMS_DELETE_HISDATA
********************************************************************/
BEGIN
......
   EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;
      RETURN; 
END WMS_DELETE_HISDATA;

END WMS_SYS_MANAGEMENT;
2.1.2 变量命名
变量使用英文字符和下划线进行命名，变量名尽量具有意义，可以分析出该变量的用途。对于相应的数据列的变量使“v_数据列”形式进行命名
2.1.3 类型声明
对于类型的声明，尽量使用%TYPE及%ROWTYPE方式进行，预防因数据库的变动对程序的影响。
2.1.4 代码段的划分
根据处理逻辑，程序中可能要做几个动作或者运算，每个动作或者运算作为一个逻辑区块处理，应使用BEGIN <<sec1>>及END sec1进行相应的标识。
2.1.5 注释
分为两种情况，即单行注释和多行区块注释。格式分别如下：
-- 单行注释示例
/**
 * 多行区块注释
 */
对于每个PL/SQL程序要在开始的多行区块注释中写明该段代码的书写人，创建日期，功能描述，修改日志内容。
2.1.6 空行
? 代码之间空行不要超过一行；
? 子程序之间空两行；
2.1.7 数据缩进
使用制表符(TAB)控制文本缩进。
2.1.8 SQL布局
每行不多于80个字符，且按照如下格式书写：
SELECT tr03.rsm_seqno, tr03.rsm_dsrb
  FROM tr03_tab_daemon tr03, item_info item
 WHERE tr03.cst_id = item.cst_id
   AND tr03.reg_cod = item.reg_cod
   AND tr03.rln_tpcode = item.rln_tpcode
   AND tr03.rel_cstid = item.rel_cstid
 ORDER BY 1;
2.1.9 循环
? 对于FOR和WHILE循环不要EXIT和RETURN；
? 仅在执行指定次数时使用FOR循环；
? 仅在条件边界为FALSE时退出循环；
? Cursor FOR loop通常效率均比OPEN-FETCH-EXIT-CLOSE好，并且后者更复杂；作为一个规则，仅在以下情况下使用OPEN-FETCH-EXIT-CLOSE：
? 要与游标变量一起工作，编译器不能自动声明ref cursors的cursor%ROWTYPE；
? 希望在循环后保留值。
? 使用匿名游标循环，FOR r IN (SELECT cols FROM sometable) LOOP；但是不能重用，也不能使用cursor's %ROWTYPE。
2.1.10 条件表达式
? 布局
? 不要增加冗余的括号；
? THEN关键字可以在IF的一行，也可以在下一行；
? 恰当布局IF中的条件；
? 如果混合or和and，除非很简单，否则一般使用缩进；
? 确保ELSIF中的条件是排斥的
2.2 注意事项
2.2.1 异常处理
对于可能遇到的异常要提早作出预定，并在其可能发生的位置上将其捕获处理掉。
2.2.2 删除不实用变量
删除不使用的变量：在程序编译之后，会提示那些变量没有使用到，对于这些没有用到的变量需要把它删除掉。
2.2.3 避免变量循环使用
避免变量循环使用：一个变量在一个区块中使用即可，不要循环使用，以避免因此产生的问题。
2.2.4 文本常量和数字处理
从代码中删除所有文本常量和数字，使用常量代替他们。可以确保系统的可维护性。
? 确保该常量的值仅被设置一次。
? 如果发现程序中某些变量值没有变化，需要首先检查逻辑是否正确；如果正确，则更改为常量。