sql 在数据仓库中的优化

1 引言

数据仓库建设中的ETL(Extract, Transform, Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL语句的效率将直接影响到数据仓库后台的性能。

 

目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验，积累了大量可分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析，这也是近几年数据仓库系统建设成为IT领域热门话题的原因。

 

2 优化的思路分析

数据仓库ETL过程的主要特点是：面对海量的数据进行抽取；分时段对大批量数据进行删除、更新和插入操作；面对异常的数据进行规则化的清洗；大量的分析模型重算工作；有特定的过程处理时间规律性，一般整个ETL过程需要在每天的零点开始到6点之前完成。所以，针对ETL过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面，针对不同的情况从如何采用高效的SQL入手来进行。

 

3 优化的实例分析

   目前数据仓库建设中的后台数据库大部分采用Oracle，以下的SQL采用Oracle的语法来说明，所有的测试在Oracle9i环境中通过，但其优化的方法和原理同样适合除Oracle之外的其他数据库。

 

3.1索引的正确使用

在海量数据表中，基本每个表都有一个或多个的索引来保证高效的查询，在ETL过程中的索引需要遵循以下使用原则：

(1) 当插入的数据为数据表中的记录数量10%以上时, 首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。

(2) 避免在索引列上使用函数或计算，在WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例:

低效： SELECT  *  ROM  DEPT  WHERE  SAL * 12  >  25000;

高效： SELECT  *  FROM  DEPT  WHERE  SAL  >  25000/12;

低效： select * from table1 where name='zhangsan' and tID > 10000

高效： select * from table1 where tID > 10000 and name='zhangsan'

如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。

(3) 避免在索引列上使用NOT和”!=”或<> ， 索引只能告诉什么存在于表中， 而不能告诉什么不存在于表中，当数据库遇到NOT和”!=”时，就会停止使用索引转而执行全表扫描。

(4) 索引列上用>=替代>

高效：   SELECT  *  FROM EMP  WHERE  DEPTNO >=4

低效：   SELECT  *  FROM EMP  WHERE  DEPTNO >3

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

(5) 函数的列启用索引方法，如果一定要对使用函数的列启用索引，Oracle9i以上版本新的功能：基于函数的索引(Function-Based Index)是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。

CREATE  INDEX  EMP_I  ON  EMP (UPPER( ENAME)); /*建立基于函数的索引*/

SELECT  *  FROM EMP  WHERE  UPPER(ENAME) = ‘BLACKSNAIL’; /*将使用索引*/

 

 

3.2 游标的正确使用

当在海量数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL过程中的使用又必不可少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。

 

对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性，所以采用游标的是数据维护完整性的最好方式。由于它的效率低，如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指10万条记录以上的维表），以下是处理这种情况的有效方式：

(1) 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。

(2) 在INSERT和UPDATE维表时都加上一个条件来过滤维表中已经存在的记录，实例为：

INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE  NOT EXISTS  (DIM_CUSTOMER.CODE)

/* ODS_CUSTOMER为数据源表；DIM_CUSTOMER为维表*/

(3) 使用显式的游标(CURSORs) ，因为使用隐式的游标将会执行两次操作， 第一次检索记录，第二次检查TOO MANY ROWS 这个EXCEPTION，而显式游标不执行第二次操作。

 

3.3数据抽取和上载时的SQL性能优化

3.3.1 WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其它WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

 

低效：SELECT * FROM EMP E WHERE SAL > 50000  AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP   WHERE MGR=E.EMPNO);

高效：SELECT * FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP              WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

 

3.3.2 删除全表时用TRUNCATE替代DELETE

当DELETE删除表中的记录时，有回滚段(rollback segments ) 用来存放可以被恢复的信息，而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息，所以执行时间也会很短。 同时需要注意TRUNCATE只在删除全表时适用，因为TRUNCATE是DDL而不是DML。

 

3.3.3 尽量多使用COMMIT

ETL中同一个过程的数据操作步骤很多，数据仓库采用的是数据抽取后分析模型重算的原理，所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行，只要有可能就在程序中对每个DELETE、INSERT和UPDATE操作尽量多使用COMMIT, 这样系统性能会因为COMMIT所释放的资源而大大提高。

 

3.3.4 用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件往往需要对另一个表进行联接，例如在ETL过程写数据到模型时经常需要关联10个左右的维表，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程, 在这种情况下，使用EXISTS而不用IN将提高查询的效率。

 

3.3.5 用NOT EXISTS替代NOT IN

子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历。用NOT EXISTS替代NOT IN将提高查询的效率。

 

3.3.6  !=或<> 操作符（不等于）

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。推荐方案：用其它相同功能的操作运算代替，如a<>0 改为 a>0 or a<0 a><>’’ 改为 a>’’

 

3.3.7  IS NULL 或IS NOT NULL操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

推荐方案：用其它相同功能的操作运算代替，如a is not null 改为 a>0 或a>’’等。

不允许字段为空，而用一个缺省值代替空值。

 

【注：】

not in 或not exits都不能应用表的索引,另外!=或<>也都不能应用表的索引,因为对它们的处理只会产生全表扫描。如：a<>0 改为 a>0 or a<0  a><>’’ 改为 a>’’

 

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的记录索引。

 

3.3.9 优化GROUP BY

提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。

低效： SELECT  JOB , AVG(SAL)  FROM  EMP  GROUP  BY  JOB  HAVING  JOB = ‘PRESIDENT’ OR  JOB  = ‘MANAGER’

高效： SELECT  JOB , AVG(SAL)  FROM  EMP  WHERE  JOB  = ‘PRESIDENT’ OR JOB  = ‘MANAGER’ GROUP  BY  JOB

【注：】

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。

select topicid,userid,sum(counter) from cis_topic_stat group by rollup(topicid,userid);

也就是说，如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

select topicid,userid,sum(counter) from cis_topic_stat group by cube(topicid,userid);

和ROLLUP相比，CUBE又增加了对STATUS列的GROUP BY统计。

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

 

3.4.0 有条件的使用UNION-ALL 替换UNION

ETL过程针对多表连接操作的情况很多，有条件的使用UNION-ALL 替换UNION

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7600171/viewspace-466193/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7600171/viewspace-466193/