数据库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 文本常量和数字处理
从代码中删除所有文本常量和数字,使用常量代替他们。可以确保系统的可维护性。
? 确保该常量的值仅被设置一次。
? 如果发现程序中某些变量值没有变化,需要首先检查逻辑是否正确;如果正确,则更改为常量。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值