[大数据]sql优化

SQL技术规范

• 产品实施型项目，尽量采用通用的SQL写法；
• 大数据量下，用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;

1. select * from t1 where (col1>val1 and col2<val2) ?or col3=val3;

修改为

select * ?from t1 where (col1>val1 and col2<val2)

union all

select * ?from t1 where??col3=val3;

1. 其他：多表操作，对于不同的表，只要选择的内容一致，也可以用，且效率高。

• 多表操作时，用NOT EXISTS替代NOT ?IN，用EXISTS替代IN。

1. 原因：NOT IN肯定不会用到索引，而NOT ?EXISTS?可以用到索引,当内表数据量大于外表时，用EXISTS，当外表数据大于内表数据时用IN（内表和外表：内表指嵌套在内做为条件的表，外表指需要最终选择数据的表）,经过测试，一般情况EXISTS效率优于IN；
2. 语法

1. 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 ;

1. 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);

1. UPDATE ,DELETE也可以替换，效率也非常高，特别是如果全表扫面将非常慢，会占用锁等很大的资源；
2. 用表关联效率比exists高。（需要验证，写法见EXISTS替代IN的例子）

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

1. 原因：NOT,<>不会用到索引;
2. 解决办法：用多条件组合

select * from ?t ?where col1<>val;

可以修改为

select?* from t ?where col1<val

union all

select * from t where col1>val;

1. 其他

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

1. 原因：不会用到索引
2. 解决办法

1. 条件后移

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

修改为

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

• 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

1. 原因：GROUP BY容易全表扫描
2. order by 的顺序必须和索引顺序完全一致；
3. 先将结果集选出，再order by 将极大提高效率，可以通过临时表，结果集（需要验证）先选出结果集，然后再order by；

• 用EXISTS替换DISTINCT

1. 原因：EXISTS子查询的条件一旦满足后,立刻返回结果；
2. 优化语法?
3. SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E?
4. WHERE D.DEPT_NO = E.DEPT_NO?
5. 修改为：?

(高效):?

SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X'? FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

• 按照索引字段顺序按前匹配可以使用到索引

1. 尽量使用索引的一个字段，可以使用到索引；
2. 可以进行索引前字段的全匹配，可以使用到索引（需要验证）；

• 尽量使用ROWID，ROWNUBER

1. 原因：ROWID（oracle），ROWNUBER（db2）是物理位置；
2. 最高效的删除重复记录方法 ( 因为使用了ROWID，ORACLE)
3. 举例：
4. delete from table1 t1
5. where t1.rowid > (select min(t2.rowid) from table1 t2 where t1.id = t2.id);

• 尽量不要使用having子句，可以考虑用where替换。

1. 原因：having只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序，总计等操作。
2. 如果能通过where子句限制记录的数目，那就能减少这方面的开销

• 尽量用表的别名：

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

• 禁止大数据量全表非索引扫面

1. 原因：大数据量的全表扫面是造成系统效率低下甚至系统崩溃的主要原因，特别是多表联合查询，产生笛卡尔积时，将极大的占用系统资源；
2. 解决办法：尽可能用到索引，或者优化设计

• 简化SQL复杂度

1. 原因：负责的SQL难以分析，读懂，很多时候效率很低；
2. 解决办法：分拆SQL，优化设计，或者通过临时表，理顺过程。

• 减少SQL交互

1. 原因：每个SQL都需要耗一定时间，如果在循环大量使用，将很低效；
2. 解决办法：将循环内的SQL拿到循环外，带上条件一次执行，

• 尽量少选择列

1. 原因：选择不需要的列会占用资源；
2. 解决办法：只选择必须的列；

• 用”>=” 代替”>”

1. 原因：>= 比>要精确，少扫描；
2. 同理用”<=”代替”<”；

• 更新索引统计

1. 在大量数据插入或者删除后，索引会失效；
2. 大量数据更新或者固定时间执行更新

1. Oracle??：analyze table …
2. db2?：Runstats on table…
3. Informix ?Update statistics…

• ?字段尽量使用大写

1. Oracle语句执行过程，会先将小写转换成大写，再进行数据库的相应操作。

select * from

t_base_chnl_menus where chnl_id=’001’;

SELECT * FROM

T_BASE_CHNL_MENUS WHERE CHNL_ID=’001’;