《SQL编写规范与优化(适用于Oracle)》由会员分享,可在线阅读,更多相关《SQL编写规范与优化(适用于Oracle)(23页珍藏版)》请在金锄头文库上搜索。
1、SQL编写规范与优化,皮皮网运维部 2012.10,一、概述,本规范侧重于代码编写过程中SQL语句的编写规范问题,内容涉及编写风格、编写规范、SQL语句编写过程中的优化建议、不良SQL语句的优化等方面。,二、SQL编写风格,2.1、SQL语句的大小写SQL语句的关键字统一使用全小写或全大写的方式,例如:select,update,from,where,order,by,group by declare,begin,end等。标识符,变量和参数采用小写,如v_sal。 数据库对象、列以及别名用小写。,2.2、SQL语句缩进与换行sql语句中的关键字右对齐。select/from/where/or
2、der by/group by等子句应独占一行。select子句内容如果只有一项,应与 select 同占一行。select子句内容如果多于一项,每一项都应独占一行,并在对应 select的基础上向右缩进8个字符。from子句内容如果只有一项,应与 from同占一行。from子句内容如果多于一项,每一项都应独占一行,并在对应from的基础上向右缩进4个字符。where子句内容如果只有一项,应与 where同占一行。where子句的条件如果有多项,每一个条件应独占一行,并以and开头,并在对应where的基础上向右缩进4个字符。update set子句内容每一项单独占一行,无缩进。insert子
3、句内容每个表字段单独占一行,无缩进;values每一项单独占一行,无缩进 。,SQL语句缩进与换行示例:-select语句书写的正确示例1、select column_namefrom table_namewhere column_name =xxxxxxxxx;2、select a.column1, a.column2, b.column3from table_name_1 a , table_name_2 bwhere a.column1 = b.column2 and b.stat= 2;,-update语句书写的正确示例update table_namesetlist_stat = x
4、xx, parent = xxx,name=xxxwhere list_no = xxxand city=xxx;,-Insert语句书写的正确示例insert into table_name( list_no,list_stat,parent, manifest_no, div_flag)values( bill020,1,0,000000000000007807, 0);,2.3、SQL语句编写应遵循以下空格规则 1. SQL语句中不允许出现空行。 2. SQL语句内的算术运算符、逻辑运算符(AND、OR、NOT)、 比较运算符(=、=、BETWEEN AND)、IN、LIKE等运算符前后
5、都应加一空格。3. 逗号之后必须接一个空格。 4. 关键字、保留字和左括号之间必须有一个空格。,三、SQL语句编写规范,1、表结构设计的时候尽量选择合适的数据类型、合适的长度、避免行链接、行迁移的出现,外键所在的字段一定要创建索引,否则,对主表进行操作,外键所在的子表有可能被完全封锁。2、SQL语句的语法应与所使用的数据库相适应。3、关键SQL语句,尽量简化,不要包含太多的嵌套,避免执行计划错误的可能,原则上不能超过2层。4、SQL语句包含多表连接时,建议使用表别名,对每个字段的使用都要带上表别名,例如: select a.col1, a.col2, b.col3 from table_nam
6、e a, tableb b where a.col4=b.col5;5、多表关联避免超过5个,可以通过临时表(表变量),简化复杂的关联。,6、使用SELECT语句时,禁止使用select * ,应当指出具体查询的字段名,例如:select col1,col2,col3, from table_name;。7、使用INSERT语句时,禁止使用 insert into table_name values(?,?,?),不应不指定字段名直接插入VALUES,应指定插入的字段名,例如: insert into table_name (col1, col2,) values(?,?,)8、避免在wher
7、e使用1=1,1=2这种表达式作为部分条件,例如: select col1, col2 from table_name where 1=1 and col1 0。9、字符型字段必须加单引号,避免where查询条件做隐型转换时后出现混乱。10、SQL 语句的注释: 应遵循各语言编码规范的代码注释要求。 对较为复 杂的 SQL语句应注释,并说明算法和功能。 对重要的计算应说明其功能。,四、SQL语句编写过程中的优化建议,应用逻辑复杂时,使用SQL实现困难,尽量使用程序去实现。建议使用hint固定关键SQL语句执行计划,原则上批量作业要用hint指定索引;在进行多条记录的增加、修改、删除时,建议使用
8、批量提交,降低事务的提交频度。SQL语句要绑定变量实现SQL语句的共享,禁止使用常量。对于分区表的查询,原则上使用分区键做条件;,尽量避免多表关联查询、特别是表之间的嵌套连接。尽量使用exists、not exists 替代 in、not in(大部份情况下exists 、not exists的性能都比in 、not in 好)。使用union 的时候如果没有去除重复数据的要求,建议尽量用union all替代。尽量避免使用order by和group by排序操作,因为大量的排序操作影响系统性能。如必须使用排序操作,尽量建立在有索引的列上。索引的建立应慎重考虑,不是越多越好。索引可以提高相应
9、的select的效率,但同时也降低了INSERT、UPDATE 的效率。Where 条件中的索引列应避免使用,not、 is null,is not null、 likke %xxxx%,%xxx,oracle系统函数。,开发人员对自己编写的SQL语句,要能够简单的评估自己所写语句的执行计划,避免显而易见的问题:比如说数据类型的隐式转换,大表上的全表扫描,对大表进行多次扫描等等。怎样生成执行计划,在SQL*PLUS里面可以通过set autotrace on 或set autotrace traceonly explain生成,在PL/SQL Developer工具里面直接按键盘上面的F5。,
10、五、不良sql语句优化,例1:不合理的执行计划,导致cpu使用率过高.select count(*) as rCount from ( select * from PPFILM_COFIG where SID not in (select MOV_ID from pp_code_movs where CODE_ID=3) order by idx desc) temp; RCOUNT - 646Elapsed: 00:00:21.08,执行计划,问题说明:ppfilm_config表中的记录为1192,pp_code_movs记录为76844存在不必要的嵌套和排序操作(造成不合理执行计划的出现
11、),书写不规范。语句在执行的时候逻辑读过高,占用大量的CPU资源(不合理的执行计划造成)。语句在解析的时候会将not in转换成Not exists,解决方法去除嵌套和排序、使用not exists 替代not in 优化后的语句如下:select count(*) from ppfilm_cofig pg where not exists ( select mov_id from pp_code_movs ps where ps.code_id=3 and pg.sid=ps.mov_id ); COUNT(*)- 646Elapsed: 00:00:00.01,6、优化之后的执行计划和统计
12、信息,例2:select * 导致不必要的输入输出,例2问题说明:pmt这张表有45个字段,实际查询需求只需要4个字段,使用”select *”导致大量的不必输入输出。,select pmt.*, to_char(pmt.MOV_LASTMODIFY, yyyy-mm-dd hh24:mi:ss) MOV_LASTMODIFY, to_char(pmt.MOV_DATE, yyyymmddhh24miss) UPDATE_TIME, pmi.MOV_AVG_LEVEL, pmi.MOV_MARKCONUT, pmi.MOV_REVIEWCOUNT from pp_movies_info_tab
13、 pmi inner join pp_movies_tab pmt on pmi.MOV_ID=pmt.MOV_ID where pmt.issearch=1 and pmt.MOV_DATE=sysdate and pmt.mov_id not in(select mov_id from pp_code_movs where code_id=1) and instr(pmt.TP_NAMELIST, 电视剧)=0 and instr(pmt.MOV_NAME, (预告片)=0 and instr(pmt.TP_NAMELIST, 综艺)=0 and instr(pmt.TP_NAMELIST
14、, MTV)=0 and instr(pmt.TP_NAMELIST, 时事)=0 and instr(pmt.TP_NAMELIST, 游戏)=0 and instr(pmt.TP_NAMELIST, 动物)=0 and instr(pmt.TP_NAMELIST, 军事)=0 and instr(pmt.TP_NAMELIST, 文化)=0 and instr(pmt.TP_NAMELIST, 旅行)=0 and instr(pmt.TP_NAMELIST, 体育)=0 and instr(pmt.TP_NAMELIST, 动漫)=0 and instr(pmt.TP_NAMELIST, 纪实)=0 and instr(pmt.TP_NAMELIST, 科教)=0 and instr(pmt.TP_NAMELIST, 广告)=0 order by pmt.MOV_DATE desc;,执行计划,解决方法将pmt.*替换成实际查询所需要的字段,结果如下:,Q&A,谢谢,