数据仓库优化方案.docx

1、数据仓库优化方案数据仓库构建及优化数据仓库构建及优化 1引言 2一、数据仓库构建 21.1数据仓库建设 21.2数据仓库在构建之初应明确其主题。 31.3数据维护与管理 5二、数据仓库的优化 62.1优化的思路分析 62.2.优化的方法 62.2.1索引的正确使用 62.2.1.1什么是索引 62.2.1.2为什么要创建索引 72.2.1.3增加索引的缺点 82.2.1.4在哪建索引 82.2.1.5不应该创建索引 92.2.2游标的正确使用 102.2.3分离表和索引 112.2.4提高磁盘 I/O 并行度改善性能 11三、优化实例 123.1在ETL过程中的索引需要遵循以下使用原则 123

2、.2数据抽取和上载时的SQL优化 133.2.1where子句中的连接顺序 133.2.2删除全表时用truncate替代delete 143.2.3 尽量多使用COMMIT 143.2.4用exists替代IN 143.2.5 用NOT exists替代NOT IN 153.2.6优化GROUP BY 153.2.7 有条件的使用union-ALL 替换union 15备注： 16引言数据仓库建设中的ETL(Extract, Transform, Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL语句的效率

3、将直接影响到数据仓库后台的性能。目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验，积累了大量可分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析，这也是近几年数据仓库系统建设成为IT领域热门话题的原因。一、数据仓库构建对企业自身来说，数据仓库的建设是一个系统工程，是一个不断建立、发展、完善的过程，通常需要较长的时间数据仓库的实现主要以关系数据库（RDB）技术为基础，通过使用一些技术，如动态分区、位图索引、优化查询等，使关系数据库管理系统在数据仓库应用环境中的性能得到大幅度的提高。1.1数据仓库建设 建立一个数据仓库需要经过以下几个处理过程： 数据仓库设

4、计 数据抽取 数据管理数据仓库的实现主要以关系数据库（RDB）技术为基础，因为关系数据库的数据存储和管理技术发展得较为成熟，其成本和复杂性较低，已开发成功的大型事务数据库多为关系数据库，但关系数据库系统并不能满足数据仓库的数据存储要求，需要通过使用一些技术，如动态分区、位图索引、优化查询等，使关系数据库管理系统在数据仓库应用环境中的性能得到大幅度的提高。1.2数据仓库在构建之初应明确其主题。主题是一个在较高层次将数据归类的标准，每一个主题对应一个宏观的分析领域，针对具体决策需求可细化为多个主题表，具体来说就是确定决策涉及的范围和所要解决的问题。但是主题的确定必须建立在现有联机事务处理（OLTP

5、）系统基础上，否则按此主题设计的数据仓库存储结构将成为一个空壳，缺少可存储的数据。但一味注重OLTP数据信息，也将导致迷失数据提取方向，偏离主题。需要在OLTP数据和主题之间找到一个“平衡点”，根据主题的需要完整地收集数据，这样构建的数据仓库才能满足决策和分析的需要。根据决策主题设计数据仓库结构，一般采用星型模型和雪花模型设计其数据模型，在设计过程中应保证数据仓库的规范化和体系各元素的必要联系。主要有以下3个步骤： 1. 定义该主题所需各数据源的详细情况，包括所在计算机平台、拥有者、数据结构、使用该数据源的处理过程、仓库更新计划等。2. 定义数据抽取原则，以便从每个数据源中抽取所需数据；定义数

6、据如何转换、装载到主题的哪个数据表中。3. 将一个主题细化为多个业务主题，形成主题表，据此从数据仓库中选出多个数据子集，即数据集市（Data Mart）。数据集市通常针对部门级的决策或某个特定业务需求，它开发周期短，费用低，能在较短时间内满足用户决策的需要。因此，在实际开发过程中可以选择在成功建立几个数据集市后再构建数据仓库这种策略。这些数据定义直接输入系统中，作为元数据（metadata）存储，供数据管理模块和分析使用。元数据存储在元数据库中，它不仅是数据仓库的文档资料，供管理、维护人员使用，而且亦可供用户查询，以便更好地了解数据仓库结构，提高自己的使用水平。根据元数据库中的主题表定义、数据

7、源定义、数据抽取规则定义对异地异构数据源（包括各平台的数据库、文本文件、HTML文件、知识库等）进行清理、转换，对数据进行重新组织和加工，装载到数据仓库的目标库中，这就是数据抽取。加工数据是保证目标数据库中数据的完整性、一致性。早期数据抽取是依靠手工编程和程序生成器实现，现在则通过高效的工具来实现，如Ardent公司的Infomoter产品、SAS的数据仓库产品SAS/WA（WarehouseAdministrator）及各大数据仓库厂商推出的、完整的数据仓库解决方案。1.3数据维护与管理数据维护与管理分为目标数据维护和元数据维护两方面。目标数据维护是根据元数据库所定义的更新频率、更新数据项等

8、更新计划任务来刷新数据仓库，以反映数据源的变化，且对时间相关性进行处理。更新操作有二种情况，即在仓库的原有数据表中进行某些数据的更新和产生一个新的时间区间的数据，因为汇总数据与数据仓库中的许多信息元素有关系，必需完整地汇总，这样才能保证全体信息的一致性。数据仓库规模一般都很大，从建立之初就要保证它的可管理性，一个企业可能建立几个数据仓库或数据集市，但他们可共用一个元数据库对其进行管理。首先从元数据库查询所需元数据，然后进行数据仓库更新作业，更新结束后，将更新情况记录于元数据库中。当数据源的运行环境、结构及目标数据的维护计划发生变化时，需要修改元数据。元数据是数据仓库的重要组成部分，元数据的质量

9、决定整个数据仓库的质量。对企业自身来说，数据仓库的建设是一个系统工程，是一个不断建立、发展、完善的过程，通常需要较长的时间。这就要求各企业对整个系统的建设提出一个全面、清晰的远景规划及技术实施蓝图，将整个项目的实施分成若干个阶段，以“总体规划、分步实施、步步见效”为原则，不仅可迅速从当前投资中获得收益，而且可以在已有的基础上，结合其他已有的业务系统，逐步构建起完整、健壮的数据仓库系统。二、数据仓库的优化2.1优化的思路分析数据仓库ETL过程的主要特点是：面对海量的数据进行抽取；分时段对大批量数据进行删除、更新和插入操作；面对异常的数据进行规则化的清洗；大量的分析模型重算工作；有特定的过程处理时

10、间规律性，一般整个ETL过程需要在每天的零点开始到6点之前完成。所以，针对ETL过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面，针对不同的情况从如何采用高效的SQL入手来进行。2.2.优化的方法目前数据仓库建设中的后台数据库大部分采用Oracle，以下的SQL采用Oracle的语法来说明，所有的测试在Oracle10g环境中通过，但其优化的方法和原理同样适合除Oracle之外的其他数据库。2.2.1索引的正确使用2.2.1.1什么是索引索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录，能加快数据库的查询

11、速度。例如这样一个查询：select * from table1 where id=44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，直接在索引里面找44（也就是在ID这一列找），就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。索引分为聚簇索引和非聚簇索引两种，聚簇索引 是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。2.2.1.2为什么要创建索引建立索引的目的是加快对表中记录的查找或排序。创建索引可以大大提高系统的性能。第一，通过创

12、建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。2.2.1.3增加索引的缺点也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空

13、间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。2.2.1.4在哪建索引索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引： 在经常需要搜索的列上，可以加快搜索的速度； 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续

14、的； 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。2.2.1.5不应该创建索引同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了

15、表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。2.2.2游标的正确使用当在海量数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL过程中的使用又必

16、不可少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性，所以采用游标的是数据维护完整性的最好方式。由于它的效率低，如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指10万条记录以上的维表），以下是处理这种情况的有效方式：(1) 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。(2) 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录，实例为：insert INTO DIM_CUSTOMER select * FROM O

17、DS_CUSTOMER where ODS_CUSTOMER.CODE NOT exists (DIM_CUSTOMER.CODE)(3) 使用显式的游标(CURSORs) ，因为使用隐式的游标将会执行两次操作，第一次检索记录，第二次检查TOO MANY ROWS 这个EXCEPTION，而显式游标不执行第二次操作。2.2.3分离表和索引总是将你的表和索引建立在不同的表空间内，决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上2.2.4提高磁盘 I/O 并行度改善性能 为了改善存储在多个磁盘驱动器上的大型数据库的性

18、能，一个有效的方法是创建磁盘 I/O 并行机制，该机制同时对多个磁盘驱动器进行读写操作。RAID 通过硬件和软件实现磁盘 I/O 并行度。三、优化实例3.1在ETL过程中的索引需要遵循以下使用原则在海量数据表中，基本每个表都有一个或多个的索引来保证高效的查询，在ETL过程中的索引需要遵循以下使用原则：(1) 当插入的数据为数据表中的记录数量10%以上时, 首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。(2) 避免在索引列上使用函数或计算，在where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例:低效： select * FROM DEPT

19、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大于

20、3的记录。(5)函数的列启用索引方法，如果一定要对使用函数的列启用索引，Oracle9i以上版本新的功能：基于函数的索引(Function-Based Index)是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。create INDEX EMP_I ON EMP (UPPER( ENAME); select * FROM EMP where UPPER(ENAME) = BLACKSNAIL;3.2数据抽取和上载时的SQL优化3.2.1where子句中的连接顺序ORACLE采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其它where条件之前，

21、那些可以过滤掉最大数量记录的条件必须写在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 50000 AND JOB = MANAGER;3.2.2删除全表时用truncate替代delete当delete删除表中的记录时，有回滚段(rollback segments ) 用来存放可以被恢复的信息，而当运用truncate时，回滚段不再存放任

22、何可被恢复的信息，所以执行时间也会很短。同时需要注意truncate只在删除全表时适用，因为truncate是DDL而不是DML。3.2.3 尽量多使用COMMITETL中同一个过程的数据操作步骤很多，数据仓库采用的是数据抽取后分析模型重算的原理，所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行，只要有可能就在程序中对每个delete、insert和update操作尽量多使用COMMIT, 这样系统性能会因为COMMIT所释放的资源而大大提高。3.2.4用exists替代IN在许多基于基础表的查询中，为了满足一个条件往往需要对另一个表进行联接，例如

23、在ETL过程写数据到模型时经常需要关联10个左右的维表，在这种情况下，使用exists而不用IN将提高查询的效率。3.2.5 用NOT exists替代NOT IN子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历。用NOT exists替代NOT IN将提高查询的效率。3.2.6优化GROUP BY提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉。低效： select JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = PR

24、ESIDENT OR JOB = MANAGER高效： select JOB , AVG(SAL) FROM EMP where JOB = PRESIDENT OR JOB = MANAGER GROUP BY JOB3.2.7 有条件的使用union-ALL 替换unionETL过程针对多表连接操作的情况很多，有条件的使用union-ALL 替换union的前提是：所连接的各个表中无主关键字相同的记录，因为union ALL 将重复输出两个结果集合中相同记录。当SQL语句需要union两个查询结果集合时，这两个结果集合会以union-ALL的方式被合并，然后在输出最终结果前进行排序。如果用union ALL替代union，这样排序就不是必要了，效率就会因此得到提高3-5倍。备注：本文内容出自互联网仅供学习参考之用，如有引用请注明出处。