如何从优化SQL入手提高数据仓库的ETL效率

如何从优化SQL入手提高数据仓库的ETL效率

2006-12-28 16:17

作者：周四阳　蔡自兴 1        引言       数据仓库建设中的ETL(Extract, Transform, Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL语句的效率将直接影响到数据仓库后台的性能。 目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验，积累了大量可分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析，这也是近几年数据仓库系统建设成为IT领域热门话题的原因。 2        优化的思路分析     数据仓库ETL过程的主要特点是：面对海量的数据进行抽取；分时段对大批量数据进行删除、更新和插入操作；面对异常的数据进行规则化的清洗；大量的分析模型重算工作；有特定的过程处理时间规律性，一般整个ETL过程需要在每天的零点开始到6点之前完成。所以，针对ETL过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面，针对不同的情况从如何采用高效的SQL入手来进行。         优化的实例分析    目前数据仓库建设中的后台数据库大部分采用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; (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个左右的维表，在这种情况下，使用EXISTS而不用IN将提高查询的效率。  3.3.5 用NOT EXISTS替代NOT IN  子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历。用NOT EXISTS替代NOT IN将提高查询的效率。 3.3.6 优化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  3.3.7 有条件的使用UNION-ALL 替换UNION  ETL过程针对多表连接操作的情况很多，有条件的使用UNION-ALL 替换UNION的前提是：所连接的各个表中无主关键字相同的记录，因为UNION ALL 将重复输出两个结果集合中相同记录。 当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION， 这样排序就不是必要了，效率就会因此得到提高3-5倍 3.3.8 分离表和索引  总是将你的表和索引建立在不同的表空间内，决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。 同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上。 4        结论    随着数据仓库系统应用需求的急剧增长，参与和关注这方面的人员将越来越多。文章中主要依据本人4年多的数据仓库实施经验，同时结合ETL的过程原理和数据仓库建设的方法论，归纳总结了以上优化的方案，愿此文能在同行中起到抛砖引玉的作用。