SQL书写规范整理

1 SQL编写风格

1.1SQL语句的大小写

• SQL语句的关键字统一使用全小写或全大写的方式。

        例如：select,update,from,where,order,by,group by declare,begin,end等。

• 标识符，变量和参数采用小写，如v_sal。

• 数据库对象、列以及别名用小写。

• 原则上只要整体保持一致即可，不要随意的书写语句。

1.2SQL语句缩进与换行

•  sql语句中的关键字右对齐。

• select/from/where/order by/group by等子句应独占一行。

• select子句内容如果只有一项，应与 select 同占一行。

• select子句内容如果多于一项，每一项都应独占一行，并在对应 select的基础上向右缩进8个字符。

• from子句内容如果只有一项，应与 from同占一行。

• from子句内容如果多于一项，每一项都应独占一行，并在对应from的基础上向右缩进4个字符。

• where子句内容如果只有一项，应与 where同占一行。

• where子句的条件如果有多项，每一个条件应独占一行，并以and开头，并在对应where的基础上向右缩进4个字符。

• update set子句内容每一项单独占一行，无缩进。

• insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进 。

1.3SQL语句编写应遵循以下空格规则

• SQL语句中不允许出现空行。

• SQL语句内的算术运算符、逻辑运算符（AND、OR、NOT）、 比较运算符（=、<=< span="">、>=、>、<< span="">、<>、BETWEEN AND）、IN、LIKE等运算符前后都应加一空格。

• 逗号之后必须接一个空格。

• 关键字、保留字和左括号之间必须有一个空格。

1.4表连接注意事项

• 关键SQL语句，尽量简化，不要包含太多的嵌套，避免执行计划错误的可能，原则上不能超过2层。

• SQL语句包含多表连接时，建议使用表别名，对每个字段的使用都要带上表别名，例        如：select a.col1, a.col2, b.col3 from table_name a, tableb b where a.col4=b.col5;

• 多表关联避免超过5个,可以通过临时表(表变量),简化复杂的关联

1.5SQL 语句的注释:

• 应遵循各语言编码规范的代码注释要求。

• 对较为复 杂的 SQL语句应注释，并说明算法和功能。

• 对重要的计算应说明其功能。

2 SQL语句编写规范

2.1 Select子句中避免使用'*'

    当你想在select子句中列出所有的column时，使用动态sql列引用'*'是一个方便的方法，不幸的是，这是一个非常低效的方法。

    实际上，oracle在解析的过程中，会将'*'依次转换成所有的列名。

    这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

2.2 INSERT时指定字段

使用INSERT语句时，禁止使用 insert into table_name values(?,?,?)，不应不指定字段名直接插入VALUES，应指定插入的字段名。

例如: insert into table_name (col1, col2,…) values(?,?,…)

2.3字符型字段必须加单引号

字符型字段必须加单引号,避免where查询条件做隐型转换时候出现混乱

select ename,sal,deptno
from emp
where empno='7782';
复制

2.4减少访问数据库的次数

当执行每条sql语句时，oracle在内部执行了许多工作：解析sql语句，估算索引的利用率，绑定变量，读数据块等等，减少访问数据库的次数，就能实际上减少oracle的工作量。

例如：

以下有三种方法可以检索出雇员号等于'7782'或'7788'的职员

方法1 (低效)
select ename,sal,deptno
from emp
where empno='7782';




select ename,sal,deptno
from emp
where empno='7788';
方法2 (高效)
select a.ename, a.sal, a.deptno, b.ename, b.sal, b.deptno
  from emp a, emp b
 where a.empno = '7782'
   and b.empno = '7788';




方法3：（绑定变量，高效）
select ename,sal,deptno
from emp
where empno=:A;
复制

2.5使用decode函数来减少处理时间

使用decode函数可以避免重复扫描相同记录或重复连接相同的表

例如：

select count(*), sum(sal)
from emp
where deptno = '20'
and ename like 'SMITH%';
　　
select count(*), sum(sal)
from emp
where deptno = '30'
and ename like 'SMITH%';
复制

你可以用decode函数高效地得到相同结果

select count(decode(deptno, '20', 'x', null)) d20_count,
       count(decode(deptno, '30', 'x', null)) d30_count,
       sum(decode(deptno, '20', sal, null)) d20_sal,
       sum(decode(deptno, 30, sal, null)) d30_sal
  from emp
 where ename like 'SMITH%';
复制

        'x'表示任何一个字段

类似的，decode函数也可以运用于group by和order by子句中。

2.6用where子句替换having子句

避免使用having子句，having只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作

如果能通过where子句限制记录的数目，那就能减少这方面的开销。

例如：

低效
　　select region, avg(log_size)
　　from location
　　group by region
　　having region != 'sydney'
　　and region != 'perth'
高效
　　select region, avg(log_size)
　　from location
　　where region != 'sydney'
　　and region != 'perth'
　　group by region
复制

2.7使用表的别名(alias)

当在sql语句中连接多个表时，请使用表的别名并把别名前缀于每个column上，这样可以减少解析的时间并减少那些由column歧义引起的语法错误。

2.8 用表连接替换exists

　　通常来说，采用表连接的方式比EXISTS更有效率。

SELECT ENAME
  FROM EMP E
 WHERE EXISTS (SELECT 'X'
          FROM DEPT
              WHERE DEPT_NO = E.DEPT_NO
                AND DEPT_CAT = 'A');
 (更高效)
SELECT ENAME
  FROM DEPT D, EMP E
 WHERE E.DEPT_NO = D.DEPT_NO
   AND DEPT_CAT = 'A';
复制

@用exists替换distinct

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换

低效:
SELECT DISTINCT d.DEPTNO, d.DNAME
 FROM DEPT D, EMP E 
WHERE D.DEPTNO = E.DEPTNO
高效:
SELECT DEPTNO, DNAME
  FROM DEPT D
 WHERE EXISTS (SELECT 'X'  FROM EMP E WHERE E.DEPTNO = D.DEPTNO);
复制

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

2.9 使用union-all和union

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。

如果用UNION ALL替代UNION，这样排序就不是必要了。 效率就会因此得到提高.

 需要注意的是，union all将重复输出两个结果集合中相同记录，因此还是要从业务需求分析使用union all的可行性。

2.10 尽量多使用COMMIT：

只要有可能，在程序中尽量多使用COMMIT, 以便及时释放资源，提高性能。

COMMIT所释放的资源：如下

                    第一，回滚段上用于恢复数据的信息。

                    第二，被程序语句获得的锁.

                    第三，redo log buffer 中的空间

                    第四，ORACLE为治理上述3种资源中的内部花费

2.11 用>=替代> 

高效:SELECT * FROM EMP WHERE DEPTNO >=4
低效:SELECT * FROM EMP WHERE DEPTNO >3
复制

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

2.12相同语句书写

同一功能同一性能不同写法SQL的影响。

如：

开发人员A写的为：
select * from emp
开发人员B写的为：
select * from scott.emp （带表的所有者前缀）
开发人员C写的为：
select * from SCOTT.EMP （大写表名）
开发人员D写的为：
select *  from SCOTT.EMP （中间多了个空格）
复制

四个SQL在ORACLE分析整理后产生的结果及执行的时间是一样的，但从ORACLE共享内存的原理，可以看出ORACLE对每一个SQL进行了一次解析，并且独立的占用共享内存，如果将SQL完全相同的格式，ORACLE只解析一次，共享内存只保留一次分析结果，这样不仅可以减少解析SQL的时间，而且可以减少共享内存里的重复信息。

2.13绑定变量

SQL语句要绑定变量实现SQL语句的共享，禁止使用常量，绑定变量使用也是减少ORACLE的硬解析和共享内存的使用。

3 关于索引使用经验

1)、如果检索数据量超过30%的表中记录数，使用索引将没有显著的效率提高

2)、在特定情况下，使用索引也许会比全表扫描慢，但这是同一个数量级上的差距；而通常情况下，使用索引比全表扫描要快几倍乃至几千倍！

3.1 用索引提高效率

索引是表的一个概念部分，用来提高检索数据的效率。实际上，ORACLE使用了一个复杂的自平衡B-tree结构。 通常，通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引。同样在链接多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。

除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列。通常, 在大型表中使用索引特别有效。当然，在扫描小表时，使用索引同样能提高效率。

虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时， 索引本身也会被修改。 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢，所以索引不能盲目的建立。

3.2建立索引

· 表的主键、外键必须有索引；

· 经常与其他表进行连接的表，在连接字段上应该建立索引，多表进行多字段连接，建议适当建立复合索引；

· 索引应该建立在查询条件中进行比较的字段上，而不是建立在我们要找出来并且显示的字段上；

· 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引；

比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

· 组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

3.3索引失效

3.3.1IN、OR子句常会使用工作表，使索引失效。

如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

例子如下:

如果在fields1和fields2上同时建立了索引，fields1为主索引

以下sql会用到索引 
select * from tablename1 where fields1=’value1’ and fields2=’value2’ 
以下sql不会用到索引 
select * from tablename1 where fields1=’value1’ or fields2=’value2’
复制

3.3.2使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被索引定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引。也可以使用特定的值来代替NULL。

请避免在索引中使用任何可以为空的列，oracle将无法使用该索引。

对于单列索引，如果列包含空值，索引中将不存在此记录；

对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中

因为空值不存在于索引列中，所以where子句中对索引列进行空值比较将使oracle停用该索引

（索引失效）

select …
 from department
where dept_code is not null
复制

3.3.3条件字段使用函数和表达式

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）

select empno,ename,deptno
from   emp
where  trunc(hiredate)='01-MAY-81';
复制

把上面的语句改成下面的语句，这样就可以通过索引进行查找。

  select empno, ename, deptno
       from emp
 where hiredate << span=""> (to_date('01-MAY-81') + 0.9999);
复制

请务必注意，检索中不要对索引列进行处理，如：trim，to_date，类型转换等操作，破坏索引，使用全表扫描，影响sql执行效率

3.3.4避免在索引列上使用计算

假如索引列是函数的一部分，优化器将不使用索引而使用全表扫描。

低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效: 
SELECT … FROM DEPT WHERE SAL > 25000/12;
复制

3.3.5比较不匹配的数据类型

比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。

select bank_name, address, city, state, zip
  from banks
 where account_number = 990354;
复制

Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：

select bank_name, address, city, state, zip
  from banks
 where account_number = '990354';
复制

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

3.3.6带通配符（%）的like语句

要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句：

select * from employee where last_name like '%cliton%';
复制

这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。

在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%';
复制

3.3.7Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

3.3.8大于或小于操作符

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化。

如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。