SQL技术规范
- 产品实施型项目，尽量采用通用的SQL写法；
- 大数据量下，用UNIOn ALL或者 UNIOn 代替OR，IN。
- 原因：OR不会用到索引，UNIOn 和UNIOn ALL 会用到索引，UNIOn会除去重复的数据，UNIOn ALL不会去除重复的数据，UNIOn ALL的效率是最高的；
- 语法
- 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;
- select * from t1 where (col1>val1 and col2
修改为
select * from t1 where (col1>val1 and col2
union all
select * from t1 where col3=val3;
- 其他：多表操作，对于不同的表，只要选择的内容一致，也可以用，且效率高。
- 多表操作时，用NOT EXISTS替代NOT IN，用EXISTS替代IN。
- 原因：NOT IN肯定不会用到索引，而NOT EXISTS 可以用到索引,当内表数据量大于外表时，用EXISTS，当外表数据大于内表数据时用IN（内表和外表：内表指嵌套在内做为条件的表，外表指需要最终选择数据的表）,经过测试，一般情况EXISTS效率优于IN；
- 语法
- EXISTS替代IN
select * from a where col1 in (select colx from b)；（a.col1 和b.colx 同一类型）可以替换为：
select * from a where exists(select colx from b where a.col1=b.colx);
更高效的写法为（需要验证）：
select a.* from a,b where a.col1=b.colx ;
- NOT EXISTS替代NOT IN
Select * from a where col1 not in (select colx from b)；（a.col1 和b.colx 同一类型）可以替换为：
Select * from a where not exists(select clox from b where a.col1=b.colx);
- UPDATe ,DELETE也可以替换，效率也非常高，特别是如果全表扫面将非常慢，会占用锁等很大的资源；
- 用表关联效率比exists高。（需要验证，写法见EXISTS替代IN的例子）
- 原因：NOT,<>不会用到索引;
- 解决办法：用多条件组合
select * from t where col1<>val;
可以修改为
select * from t where col1
union all
select * from t where col1>val;
- 其他
- 原因：不会用到索引
- 解决办法
- 条件后移
select * from t where col1+10=>20;
修改为
select * from t where col>(20-10);
- 右匹配使用索引，比如 like “abc%”；
- 左匹配不能使用索引，比如 like “%abc”;
- 前后匹配不能使用索引，比如 like “%abc%”；
- Oracle 对于左匹配可以用reverse，比如col like “%abc”修改为reverse(col) like reverse(“%abc”)（需要验证）；
- 避免在索引列上使用IS NULL和IS NOT NULL
- 原因：无法使用索引；
- 在索引尽量不要插入空值，可以使用默认值；
- 修改写法例如col为字符 col is not null 修改为 col>=0；
- 原因：无法用到索引
- 对值类型进行转换，例如col为int, 将where col=”123”修改为where col=TO_NUMBER（”123”）(oracle), where col=int（”123”）(db2);
- 原因：GROUP BY容易全表扫描
- order by 的顺序必须和索引顺序完全一致；
- 先将结果集选出，再order by 将极大提高效率，可以通过临时表，结果集（需要验证）先选出结果集，然后再order by；
- 原因：EXISTS子查询的条件一旦满足后,立刻返回结果；
- 优化语法
- SELECt DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
- WHERe D.DEPT_NO = E.DEPT_NO
- 修改为：
(高效):
SELECt DEPT_NO,DEPT_NAME FROM DEPT D WHERe EXISTS ( SELECt ‘X' FROM EMP E WHERe E.DEPT_NO = D.DEPT_NO);
- 尽量使用索引的一个字段，可以使用到索引；
- 可以进行索引前字段的全匹配，可以使用到索引（需要验证）；
- 原因：ROWID（oracle），ROWNUBER（db2）是物理位置；
- 最高效的删除重复记录方法 ( 因为使用了ROWID，ORACLE)
- 举例：
- delete from table1 t1
- where t1.rowid > (select min(t2.rowid) from table1 t2 where t1.id = t2.id);
- 尽量不要使用having子句，可以考虑用where替换。
- 原因：having只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序，总计等操作。
- 如果能通过where子句限制记录的数目，那就能减少这方面的开销
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
- 原因：大数据量的全表扫面是造成系统效率低下甚至系统崩溃的主要原因，特别是多表联合查询，产生笛卡尔积时，将极大的占用系统资源；
- 解决办法：尽可能用到索引，或者优化设计
- 原因：负责的SQL难以分析，读懂，很多时候效率很低；
- 解决办法：分拆SQL，优化设计，或者通过临时表，理顺过程。
- 原因：每个SQL都需要耗一定时间，如果在循环大量使用，将很低效；
- 解决办法：将循环内的SQL拿到循环外，带上条件一次执行，
- 原因：选择不需要的列会占用资源；
- 解决办法：只选择必须的列；
- 原因：>= 比>要精确，少扫描；
- 同理用”<=”代替”<”；
- 在大量数据插入或者删除后，索引会失效；
- 大量数据更新或者固定时间执行更新
- Oracle ：analyze table …
- db2 ：Runstats on table…
- Informix Update statistics…
- Oracle语句执行过程，会先将小写转换成大写，再进行数据库的相应操作。
select * from
t_base_chnl_menus where chnl_id=’001’;
SELECt * FROM
T_base_CHNL_MENUS WHERe CHNL_ID=’001’;
