数据库技术规范

SQL规范

一、尽量采用通用的SQL写法，简单就是高效；

二、对于交易系统尽量的采用索引，禁止大数据量表的条

件不用索引；

1、当处理的行数小于等于总表的5%时，使用索引效果非常显著，也是最常见最有效的优化策略；

2、WHERE条件，GROUP BY，ORDER BY，JION，DISTINCT使用到索引；

3、在表关联的连接字段使用到索引；

4、并非索引总是能加快效率，对索引列值差异性大并且所要查询的

数据只占了少部分肯定高效；

5、避免索引泛滥，一般一个表的索引不要超过5个；

6、对于索引数据频繁更新数据，需要定期重建；

三、用UNION ALL或者UNION 代替OR，IN。

1、原因：OR不会用到索引，UNION 和UNION ALL 会用到索引，UNION

会除去重复的数据，UNION ALL不会去除重复的数据，UNION ALL 的效率是最高的；

2、语法

(1)、selelct col1,col2… from t1 where col1 =val1 or

col1=val2;

修改为：

selelct col1,col2… from t1 where col1 =val1

union all

selelct col1,col2… from t1 where col1=val2;

(2)、select * from t1 where (col1>val1 and col2

col3=val3;

修改为

select * from t1 where (col1>val1 and col2

union all

select * from t1 where col3=val3;

3、其他：多表操作，对于不同的表，只要选择的内容一致，也可以

用，且效率高。

四、多表操作时，用NOT EXISTS替代NOT IN，用EXISTS替代IN。

1、原因：NOT IN肯定不会用到索引，而NOT EXISTS 可以用到索

引,当内表数据量大于外表时，用EXISTS，当外表数据大于内表数据时用IN（内表和外表：内表指嵌套在内作为条件的表，外表指需要最终选择数据的表）,经过测试，一般情况EXISTS效率优于IN；

2、语法

(1)、EXISTS替代IN

select * from a where coln in (select colx from b)；

（a.coln 和b.colx 同一类型）可以替换为：

select * from a where exists(select colx from b where

a.coln=

b.colx);

更高效的写法为：

select a.* from a,b where a.coln=b.colx ;

(2)、NOT EXISTS替代NOT IN

Select * from a where coln not in (select colx from b)；

（a.coln 和b.colx 同一类型）可以替换为：

Select * from a where not exists(select clox from b where

a.coln=

b.colx);

(3)、UPDATE ,DELETE也可以替换，效率也非常高，特别

是如果全表扫面将非常慢，会占用锁等很大的资源；

(4)、用表关联效率比exists高。

五、在索引上尽量不用NOT，<>操作

1、原因：NOT,<>不会用到索引;

2、解决办法：用多条件组合,或者换一种表达方式

(1)、select * from t where coln<>val;

可以修改为:

select * from t where coln

union all

select * from t where coln>val;

(2)、select * from t where coln is not null;

可以修改为：

select * from t where coln>0

3、not in用not exists 替换

六、索引上的列尽量不使用计算

1、原因：不会用到索引

2、解决办法

(1)、条件后移

select * from t where coln+10=>20;

修改为

select * from t where coln>(20-10);

(2)、select * from t where substr(col,1,3)=”abc”;

修改为

select * from t where col like ”abc%”;

七、like的使用及其优化

1、右匹配使用索引，比如like “abc%”；

2、左匹配不能使用索引，比如like “%abc”;

3、前后匹配不能使用索引，比如like “%abc%”；

4、Oracle 对于左匹配可以用reverse，比如col like “%abc”修改为

reverse(col) like reverse(“%abc”)；

八、避免在索引列上使用IS NULL和IS NOT NULL

1、原因：无法使用索引；

2、在索引尽量不要插入空值，可以使用默认值；

3、修改写法例如col为字符col is not null 修改为col>=0；

九、避免改变索引列的类型

1、原因：无法用到索引

2、对值类型进行转换，例如col为int, 将where col=”123”修改为

where col=TO_NUMBER（”123”）(oracle), where col=int（”123”）(db2);

十、优化GROUP BY ，ORDER BY

1、原因：GROUP BY，ORDER BY容易全表扫描

2、GROUP BY，ORDER BY 的顺序必须和索引顺序完全一致；

3、先将结果集选出，再分组排序将极大提高效率，可以通过临时

表，结果集（需要验证）先选出结果集，然后再操作；

十一、用EXISTS替换DISTINCT

1、原因：EXISTS子查询的条件一旦满足后,立刻返回结果；

2、优化语法

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E

WHERE D.DEPT_NO = E.DEPT_NO