软件性能测试实施之数据仓库ETL及存储设计SQL效率和数据库设计性能注意事项

1、ETL过程建议 ETL过程的设计和实现是数据仓库解决方案中极其重要的一部分。ETL过程用于从多个数据源提取业务数据，清理数据，然后集成这些数据，并将它们加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。 ETL相对于关系数据库、数据仓库技术没有严格的数学理论基础，它更面向实际工程应用。所以从工程应用的角度来考虑，按着物理数据模型的要求加载数据并对数据进行一些系列处理，处理过程与经验直接相关，同时这部分的工作直接关系数据仓库中数据的质量，从而影响到联机分析处理和数据挖掘的结果的质量。通常将ETL分为准备（规范制定）、实现、测试、优化和迁移阶段，本文按照这五个阶段对ETL过程中的建议及

2、注意事项进行说明。1 准备阶段/规范制定阶段 准备阶段根据业务需求定义映射关系，分析数据源质量，针对数据源中有问题的数据制定数据抽取原则，确定数据抽取的技术架构及ETL的实现方式。建立高效、规范的管理机制，有利于ETL过程的规范，下面介绍元数据和数据质量管理两个重要方面。1.1 元数据的管理 对业务数据本身及其运行环境的描述与定义的数据，称之为元数据（metadata），元数据是描述数据的数据。从某种意义上说，业务数据主要用于支持业务系统应用的数据，而元数据则是企业信息门户、客户关系管理、数据仓库、决策支持和B2B等新型应用所不可或缺的内容。 元数据的典型表现为对象的描述，即对数据库、表、列、

3、列属性（类型、格式、约束等）以及主键/外键关联等等的描述。特别是现行应用的异构性与分布性越来越普遍的情况下，统一的元数据就愈发重要了。“信息孤岛”曾经是很多企业对其应用现状的一种抱怨和概括，而合理的元数据则会有效地描绘出信息的关联性。 元数据对于ETL的集中表现为：定义数据源的位置及数据源的属性、确定从源数据到目标数据的对应规则、确定相关的业务逻辑、在数据实际加载前的其他必要的准备工作，等等，它一般贯穿整个数据仓库项目，而ETL的所有过程必须最大化地参照元数据，这样才能快速实现ETL。现在有了很多元数据管理工具，但最重要的也是最有效的是从管理机制入手，建立一个有效的管理元数据的制度。1.1.1

4、 建立ETL映射文档 该文档应在准备阶段完成。ETL映射文档是元数据的主要依据，它记录数据从数据源到目标表的转换和对应关系。映射可以是一对一、一对多、多对一的。1.1.2 建立ETL变更流程 ETL变更流程其实是与需求变更紧密结合在一起的，项目中需求的变化是很常见的，需求的变化有可能会导致ETL映射关系、以及ETL业务逻辑的变化。修改ETL映射文件和业务逻辑文件必须从文档开始，并且应该有统一的入口。修改文档的主要内容应该包括：版本号、修改原因描述、修改过程、修改时间、修改的影响范围。1.2 数据质量管理 数据质量的好坏关系到项目的成败，前端的各种数据分析和展现的手段都是建立在良好的数据质量基础

5、上的。1.2.1 建立规范保证数据质量 通过制定统一的ETL规范，严格要求ETL过程中的每一步都要按照规范制定的步骤来执行。规范的制定可以根据各个项目组的实际情况作适当的增减，但是一些原则性的步骤一定要坚持执行。规范一旦制定下来，就必须严格执行。从源头来保证数据质量的可信度。应撰写下列文档来保障数据质量：ETL映射文档、数据源质量分析报告、全量数据抽取策略、增量数据抽取策略、问题数据处理规范、数据抽取中异常处理规范。1.2.2 建立数据核对和数据效验流程 这部分应该是根据数据仓库应用的实际情况来考虑制定的。数据效验的方式有多种，常见的是通过报表数据与已有系统中的数据进行比对，比对如果出现问题，

6、关键点在于要明确不同的原因，通常原因包括：统计口径不同、已有系统数据有误、自己的数据有误，建议设计数据核对和数据效验流程对发现数据不一致后的验证操作进行规范。1.2.3 建立数据修改流程    有些错误是可以通过制定效验规则来自动进行修改的；而另一部分错误是必须有人工来判断错误原因，并且由人工或者由其他系统来修改，建议设计数据修改流程对发现错误后的数据修改流程进行规范。2 实现阶段 根据准备阶段的产出物进行ETL的实现。ETL主要包括了数据抽取、数据转换、数据清洗、数据装载四个步骤，实现过程如下图所示。图 ETL过程 在ETL实现过程中，通常利用索引提高数据查询的

7、效率。正确的索引可能极大的提高效率，而无效的索引可能不仅浪费了数据库空间，甚至大大降低查询性能。本节将介绍在实现过程各阶段中，使用索引的注意事项。2.1 数据抽取过程 并不是源数据库的所有数据对于数据仓库的主题域都是有用的，必须根据已确定主题的需要，从原有操作型数据库中提取期待的数据到数据仓库。在数据抽取过程中，应至少考虑下列因素：l 源数据库和目标数据库各自的格式是否一致？若不一致，不一致的程度如何？l 源数据库要访问的数据文件；l 源数据库中的数据表需要提取那些字段，按什么条件提取。 在数据抽取过程中，通常需要索引来提高执行效率，下面对数据抽取中索引使用时应遵循的一些原则进行介绍。 单表查

8、询时使用多个索引只有一个有效 通常存在一种错误的认为，对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤，但事实上并非如此。 假设查询语句如下： select * from t1 where c1=1 and c2=2 c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2，依据不同数据库管理系统的策略)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。2.1.2 避免在索引列上使用函数或计算 在WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例: 低效： SELECT * ROM DEPT WHER

9、E SAL * 12 > 25000； 高效： SELECT * FROM DEPT WHERE SAL > 25000/12；2.1.3 避免在索引列上使用NOT和“!=” 索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到NOT和“!=”类操作时，就会停止使用索引转而执行全表扫描。2.1.4 必要时创建基于函数的索引 如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。为了对使用函数的列启用索引，Oracle9i以上版本提供了新的功能：基于函数的索引(Function-Based Index)是一个较好的方案，但该类型索引的缺点是只能针对

10、某个函数来建立和使用该函数。涉及语法如下所示： CREATE INDEX EMP_I ON EMP (UPPER( ENAME)； SELECT * FROM EMP WHERE UPPER(ENAME) = 'BLACKSNAIL；2.2 数据转换 数据转换的注意事项 数据仓库中的数据往往来自一个或多个异构的数据库系统,这些数据源之间往往存在着不一致的问题,如不一致的字段长度、不一致的赋值等。数据不一致会严重影响数据仓库的数据质量。数据转换就是处理这些不一致性的过程。数据转换过程中，应至少考虑两个方面的内容：l 不同源数据库系统中的数据名称和数据格式存在不一致。l 数据仓库中存在的数

11、据可能不直接存在于源数据库中，需要根据数据库仓库的需要对源数据库中的数据进行分割、组合或运算等。2.2.2 控制游标操作的结果集 数据仓库的数据表往往具有海量数据，在这些数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式。但游标允许应用程序对查询语句select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作。游标还提供对基于游标位置而对表中数据进行删除或更新的能力，因此它在ETL过程中的使用必不可少，且有着极其重要的地位。 对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性，所以采用游标的是数据维护完整性的最好方式。由于它的效率低

12、，如果按照普通的方式将无法处理大数据量的维表数据维护(一般是指10万条记录以上的维表)，可以通过一下方式来限制游标操作的数据量。 (1) 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。 (2) 在INSERT和UPDATE维表时都加上一个条件来过滤维表中已经存在的记录，实例为： INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE) (3) 使用显式的游标(CURS

13、ORs)，因为使用隐式的游标将会执行两次操作，第一次检索记录，第二次检查TOO MANY ROWS这个EXCEPTION，而显式游标不执行第二次操作。2.3 数据清洗源数据库中的数据可能存在各种各样的问题，数据清洗应该处理许多可能存在的错误类型。这些类型包括数据源中丢失数据和有错误数据,还包括两个或多个数据源里的不一致数据和冲突数据,所有这些都必须处理。有时如果系统不能自动处理,还必须借助于手工操作来完成。2.4 数据装载 此过程将处理完成的数据装载到数据仓库，数据转载过程中需注意下列问题。 数据装载时出现乱码 通常数据库的迁移过程中出现乱码都是由字符集的错误导致的，可以简单把这种情况分为三类

14、：（1）源数据库中保存的数据就是乱码 如果源数据就发生了错误，除非能够掌握数据错误发生的规律，否则难以纠正其错误。但是即使数据发生了错误，也可以对错误数据进行正确的迁移。（2）源数据库正确，导出的数据文件出现乱码 很多时候即使导出的数据文件中含有乱码（使用文本工具显示时），也并不意味着发生了错误，数据文件中的很多正确数据可能是文本显示工具无法展示的。 通常数据库厂商都提供了适配的数据导出工具或接口。如何不是显示问题，而是导出数据文件的内容发生了错误，可更换字符集后使用这些工具和接口尝试导出，如仍出现错误，只能联系数据库厂商了。（3）由数据文件导入目标数据库时发生了错误 此类错误通常包括两种情况

15、： A源数据库与目标数据库数据类型不一致 如SQL Server数据库中的text, char, varchar为单字节类型，保存一个英文字母用一个字节，一个汉字用两个字节，而 ntext,nchar,nvarchar双字节类型保存一个英文字母用两个字节，一个汉字也用两个字节。 对于导入、导出数据库均应当考虑源数据库和目标数据库的格式，在创建新的数据库（或数据仓库）时，应考虑源数据库的数据类型，是二者保持一致。 B统一源数据库导出与目标数据库导入字符集 从源数据库导出数据时，需要为设置导出数据文件的字符集格式，使用数据文件向目标数据库导入数据时也需要设置数据文件的字符集格式，两个字符

16、集的格式必须一致，否则就很有可能发生错误。2.4.2 大数据量插入前考虑删除索引 当插入的数据为数据表中的记录数量10%以上时，插入数据过程中维护索引的时间很可能大于数据插入后重新建立索引的时间。此时，需要考虑删除该表的索引来提高数据的插入效率，待数据全部插入后再建立索引。3 测试阶段 测试和优化两个阶段实际上是相辅相成的，测试阶段要测试ETL逻辑的准确性，通过测试过程就可以得出ETL过程的性能结果。建议产出物为ETL测试报告。4 优化阶段ETL优化阶段的主要内容包括正确的使用索引和游标、对数据抽取和载入时的SQL语句进行优化、数据库参数优化及ETL流程优化等方面。本文在ETL实现阶段提出了对

17、索引和游标的使用建议，下面主要对SQL语句的优化进行说明。SQL语句的设计是否合理，直接影响到ETL过程的执行效率，本文建议从下面几个方面进行检查。4.1 合理安排WHERE子句中的连接顺序 对于不同的数据库管理系统，可能会使用不同的机制解析where字句中的筛选顺序。鉴于吉林银行数据仓库使用ORACLE数据库，本文以该数据库为例。 在ORACLE数据库中，ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其它WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 低效：SELECT * FROM EMP E WHERE SAL &

18、gt; 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；4.2 使用TRUNCATE删除全表 当DELETE删除表中的记录时，有回滚段(rollback segments ) 用来存放可以被恢复的信息，而当运用TRUNCATE

19、时，回滚段不再存放任何可被恢复的信息，所以执行时间也会很短。同时需要注意TRUNCATE只在删除全表时适用，因为TRUNCATE是DDL而不是DML。4.3 多使用COMMIT ETL中同一个过程的数据操作步骤很多，数据仓库采用的是数据抽取后分析模型重算的原理，所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行，只要有可能就在程序中对每个DELETE、INSERT和UPDATE操作尽量多使用COMMIT, 这样系统性能会因为COMMIT所释放的资源而大大提高。4.4 用EXISTS替代IN（1）查询条件为存在的情况在许多基于基础表的查询中，为了满足一

20、个条件往往需要对另一个表进行联接，例如在ETL过程写数据到模型时经常需要关联10个左右的维表，在这种情况下，使用EXISTS而不用IN将提高查询的效率。 举例： (高效) SELECT  *  FROM  EMP (基础表) WHERE EMPNO>0  AND EXISTS ( SELECT X'  FROM DEPT  WHERE DEPT.DEPTNO = EMP.DEPTNO AND  LOC = MEL

21、B')  (低效) SELECT  * FROM  EMP(基础表)  WHERE  EMPNO>0 AND DEPTNO IN (SELECT DEPTNO  FROM  DEPT  WHERE  LOC = MELB')（2）查询条件为不存在的情况 子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN都是最低效的，因为它

22、对子查询中的表执行了一个全表遍历。用NOT EXISTS替代NOT IN将提高查询的效率。4.5 优化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

23、 JOB4.6 尽量使用UNION-ALL替换UNION ETL过程针对多表连接操作的情况很多，有条件的使用UNION-ALL 替换UNION的前提是：所连接的各个表中无主关键字相同的记录，因为UNION ALL 将两个结果集合中的相同记录合并为一条。 当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高3-5倍4.7 谨慎使用 SELECT * 在使用SELECT语句时，应只选择所需要的列。当希望在SELECT子句中列出所有的C

24、OLUMN时，使用动态SQL列引用*是一个方便的方法。但这是一个非常低效的方法。 以ORACLE数据库为例，ORACLE会在解析的过程中, 将*依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。4.8 减少对表的查询 在含有子查询的SQL语句中，要特别注意减少对表的查询，减少数据库压力，举例如下。 低效语句：               SELECT TAB_NAME FROM TABLES 

25、;              WHERE TAB_NAME = ( SELECT TAB_NAME                             &#

26、160; FROM TAB_COLUMNS WHERE VERSION = 604)               ANDDB_VER= ( SELECT DB_VER                      

27、         FROM TAB_COLUMNS WHERE VERSION = 604)          高效语句：               SELECT TAB_NAME FROM TABLES    

28、60;          WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER)                 FROM TAB_COLUMNS WHERE VERSION = 604)5 迁移阶段 我们经常碰到从一个环境迁移到另一个环境的情况，ETL迁移的过程应该放在

29、数据仓库模型迁移后，也可以根据项目的实际情况一起进行迁移。建议为本阶段建立ETL迁移规范。存储设计建议 数据仓库的存储设计就是数据仓库逻辑模型在物理系统中的实现模式。其中包括了逻辑模型中各种实体表的具体化，例如表的数据结构类型、索引策略、数据存放位置和数据存储分配等。在进行物理模型的设计实现时，所考虑的因素有：I/O存取时间、空间利用率及维护的代价。 为确定数据仓库的物理模型，设计人员必须做这样几方面工作：首先要全面了解所选用的数据库管理系统，特别是存储结构和存取方法；其次了解数据环境、数据的使用频率、使用方式、数据规模及响应时间要求等，这些都是对时间和空间效率进行平衡和优化的重要依据；最后还

30、需要了解外部存储设备的特征。只有这样才能在数据的存储需求与外部存储设备条件两者之间获得平衡。1  物理存储结构 在数据仓库存储的物理设计中，需要按数据的重要性、使用频率及对反应时间的要求进行分类，并将不同类型的数据分别存储在不同的存储设备中。重要性高、经常存取并对反应时间要求高的数据存放在高速存储设备上；存取频率低或对存取响应时间要求低的数据则可以存放在低速存储设备上。另外，在设计时还要考虑数据在特定存储介质上的布局。在设计数据的布局时要注意遵循以下原则。1.1 设计数据库分区 分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对

31、象进行管理，为大量数据提供了可伸缩的性能。图 数据库分区 如上图所示，分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。 分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分，从而可以更精细地对这些数据库对象进行管理和访问。各种数据库管理系统提供了种类繁多的分区方案以满足所有的业务需要。另外，由于在 SQL语句中是完全透明的，所以分区可以用于几乎所有的应用程序。 （1）分区的优点 分区可以提高可管理性、性能与可用性，从而给各种各样的应用程序带来极大的好处。通常，分区可以

32、使某些查询以及维护操作的性能大大提高。此外，分区还能够在很大程度上简化日常管理任务。 分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。分区是建立上亿万字节数据系统或需要极高可用性系统的关键工具。（2）表和索引分区 分区能够使表、索引或索引编排表进一步细分。这些数据库对象的片段叫做分区。每个分区有自己的名称，还可以选择自己的存储特征。例如，表可以在不同的表空间以压缩表的形式存储旧的分区。从数据库管理员的角度看，分区的对象有多个段，可以进行统一管理或单独管理。这使数据库管理员在管理分区的对象时有相当大的灵活性。但是，从应用程序的角度看，分区的表与未分区的表是一样的，所以在使用

33、SQL DML命令访问分区的表时无须进行修改。1.2 使用裸设备存储（1）裸设备介绍 裸设备也叫裸分区（原始分区），是一种没有经过格式化，不经过操作系统（文件系统）而应用程序可以直接读写的特殊字符设备。它由应用程序负责对它进行读写操作，不经过文件系统的缓冲。 使用裸设备可避免经过操作系统这一层，数据直接从磁盘到数据库进行传输，所以使用裸设备对于读写频繁的数据库应用来说，可以极大地提高数据库系统的性能。当然，这是以磁盘的 I/O非常大，磁盘I/O已经称为系统瓶颈的情况下才成立。如果磁盘读写确实非常频繁，以至于磁盘读写成为系统瓶颈的情况成立，那么采用裸设备确实可以大大提高性能。（2）使用裸设备的场

34、合在仅考虑数据库写入或更新操作的情况下，裸设备完全可以大大提供操作效率。但类似数据仓库这样数据读操作为主的场合，当从数据库中读取的数据量级别与内存大小级别接近时，使用文件系统存储的数据库可以从文件系统缓存中获得大量数据，加快数据库读取操作的速度。而裸设备没有经过文件系统缓存，此时对裸设备操作的速度反而可能低于通过文件系统的操作速度。 但读取数据量大于内存大小级别时，文件系统往往是开始快（利用缓存），后来就慢下来（正常速度）。而且，文件系统缓存使用的过大，是要占用大量内存资源或者是swap空间，更会影响数据库操作的性能。在这种情况下，使用裸设备存储可以大大提高数据库的操作性能。1.3 分离连接操

35、作频率高的大表 不要把经常需要连接的几张表放在同一存储设备上，这样可以利用存储设备的并行操作功能加快数据查询的速度。1.4 考虑网络流量的影响 如果几台服务器之间的连接会造成严重的网络业务量的问题，则要考虑服务器复制表格，因为不同服务器之间的数据连接会给网络带来沉重的数据传输负担。1.5 共享数据存放在高速设备上 考虑把整个企业共享的细节数据放在主机或其他集中式服务器上，提高这些共享数据的使用速度。1.6 分离大数据表和索引 不要把表格和它们的索引放在同一设备上。一般可以将索引存放在高速存储设备上，而表格则存放在一般存储设备上，以加快数据的查询速度。1.7 合理使用RAID技术 在对服务器进行

36、处理时往往要进行大量的等待磁盘数据的工作，此时，可以在系统中使用RAID(Redundant Array of Inexpensive Disk，廉价冗余磁盘阵列)。2  大表的存储策略2.1 大表及其索引分区 可以将大表和索引进行分区，变成可以管理的小块，从而避免了对每个表作为一个大的，单独的对象进行管理。分区(partitoning)是一种”分而置之”的技术，它为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过包含故障的影响还增加了可用性。 管理员可以指定每个分区的存储属性，分区在宿主文件系统中的放

37、置情况，这样便增加了对超大型数据库的控制粒度(granularity)。分区可以被单独地卸出或装入(taken off-line or brought on-line)、备份、恢复、转出和转入(exported and imported)以及加载；因此减少了需要进行管理操作的时间。 对表分区还可以创建单独的索引分区，从而限制了需要进行索引维护操作的时间。此外，还提供了种类繁多的局部和全局的索引技术。分区操作也可以被并行执行。 分区技术还提高了数据的可用性。当部分数据由于故障或其它原因不可用时，其它分区内的数据可用不收影响继续使用。 分区对应用是透明的，可以通过标准的SQL语句对分区表进行操作。

38、2.2 宽表存储 通过分析银行业务的数据特点，同时根据前台数据分析查看的需要，对于某些大表可采用宽表(列比较多，结构上较宽的表)这种数据存储方式。实现中，模型采用宽表形式的事实表组织业务指标数据，来达到存储空间小、访问速度快的目的。其实质是根据银行业数据的特点，在数据仓库基本模型(星型模型)设计的基础上做出的一种改进。 以银行帐户的余额指标为例。通常银行分户帐数据的存储方式，是每日每帐户记录余额，既使帐户某日的余额没有发生变动，也需要复制前日余额作为当日余额。但事实上，帐户余额每日变动的频率非常小，大部分帐户的变动集中在几次或一次。此变化频率可以使用宽表来记录帐户某个指标的变化。将每日余额值记

39、录到列上，即一天的余额值用一列记录，如下面的贷款帐户指标表(ACCT_TARGET_DK)所示，它把时间维拆分为年和月、日，分别放在行和列上，而不是传统地把时间(年，月，日)作为一个维(数据要求到日粒度的)，利用此方法可达到性能优化的目的。ACCT_TARGET_DK (ACCT_SEQ，YEAR，TARGET_TYPE，COL0101，COL 0102，COL 0103，COLl230，COLL1231) 其中ACCT_SEQ:为帐户序列号、外键、贷款帐户维表的主键；YEAR:年份；TARGET_TYPE:指标类型；COLXXYY: XX月YY号某帐户某个类型的指标值。 假设原表中具有168

40、0000条记录，测试的具体结论比较值： 传统结构：需要空间大小为500M日×365日=182500M年 宽表结构：需要空间大小3372M年 比较：宽表结构的存储方式大小是传统结构的1.8。 另外使用宽表进行存储，还具有提高处理速度、增强查询性能的作用。3  设计索引策略 数据仓库的数据量很大，因而需要对数据的存取路径进行仔细地设计和选择。由于数据仓库的数据一般很少更新，所以可以设计索引结构来提高数据存取效率。在数据仓库中，设计人员可以考虑对各个数据存储建立专用的索引和复杂的索引，以获取较高的存取效率，虽然建立它们需要付出一定的代价，但建立后一般不需要过多的维护。 数据仓库中

41、的表通常要比联机事务处理系统(OLTP)中的表建立更多的索引，表中应用的最大索引数应与表格的规模成正比。数据仓库是个只读的环境，建立索引可以取得灵活性，对性能极为有利。但是表若有很多索引，那么数据加载时间就会延长，因此索引的建立需要进行综合的考虑。在建立索引时，可以按照索引使用的频率由高到低逐步添加，直到某一索引加入后，使数据加载或重组表的时间过长时，就结束索引的添加。 最初，一般都是按主关键字和大多数外部关键字建立索引，通常不要添加很多的其他索引。在表建立大量的索引后，对表进行分析等具体使用时，可能需要许多索引，这会导致表的维护时间也随之增加。如果从主关键字和外部关键字着手建立索引，并按照需

42、要添加其他索引，就会避免首先建立大量的索引带来的后果。如果表格过大，而且需要另外增加索引，那么可以将表进行分割处理。如果一个表中所有用到的列都在索引文件中，就不必访问事实表，只要访问索引就可以达到访问数据的目的，以此来减少I/O操作。3.1 选择正确的索引类型 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引与表一样，需要占用磁盘空间，但在索引里的数据存放形式与表里的数据存放形式不一样。在理解索引时，可以想象一本书，其中书的内容就相当于表里的数据，而书前面的目录就相当于该表的索引。通常情况下，索引所占用的

43、磁盘空间要比表要小的多，其主要作用是为了加快对数据的搜索速度，也可以用来保证数据的唯一性。 索引作为一种可选的数据结构，你可以选择为某个表创建索引，也可以不创建。一旦创建了索引，就意味着数据库系统对表进行DML（包括INSERT、UPDATE、DELETE）时，必须处理额外的工作量（也就是对索引结构的维护）以及存储方面的开销。在数据库设计过程中，需要根据实际业务需求和数据特点，考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。故数据库表中的索引绝对不是多多益善。具体来说，在索引建立上有如下建议。 B树索引 缺省的和最常用的表列索引类型是B树(或标准)索引，这是执行标准的

44、索引创建语句时需要使用的索引类型。它是有索引节点的有序树，每一个节点包括一个或多个索引项，每个索引项对应于表中的一行，它包含两个元素：行的索引列值(或数值集)与行的RowID(或物理磁盘位置)。B树索引为表中的每行包含一项除非行的索引项是空值。当使用B树索引时，数据库管理系统顺着索引节点树向下查找有查询选择条件相匹配的索引值。当它找到匹配时数据库管理系统使用相应的ROWlD来定位和从磁盘中读取相关的表行数据。 B树索引并不是对所有类型的应用程序和所有类型的表列都适合的。通常B树索引最适合用于不断插入、更新和删除数据的OLTP应用程序。在这种环境中，B树索引最适合于包含许多相对于列中键值的总数来

45、说的不同值的关键列。表中的主键和次键是有B树索引的列的最好的例子。 谨慎使用反向键索引（1）为什么要使用反向键索引 反向键索引是一种特殊类型的B树索引，在索引基于含有序数的列时使非常有用的，如果一个传统的B树索引基于一个含有这种数据的列，往往会产生许多级。图 理想B树索引与非正常B树索引 如上左图所示，B树的所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么B树的搜索性能逼近二分查找。但如果插入索引列的数据是严格的序列时，普通B树索引就可能形成如上右图所示的结构，此时它的搜索性能已经是线性的了。 但B树索引有4级以上的深度就会降低性能，因此反向键索引更适合这种类型，反向键索引通过简单

46、的反象被索引的列中的数据来解决问题，他首先反向每个列键值的字节，然后在反向后的新数据上进行索引，而新数据在值的范围上的分布通常比原来的有序数更均匀。  如1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。显然经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能。（2）数据仓库中并适用 但反向键索引有它局限性，由于键的真实值发生了方向，虽然在上述情况下，反向键索引可以大大提高INSERT、UPDATE等操作的性能，但对于需要进行范围搜索的场合不一定使用，而数据仓库大多数情况下正是此类应用

47、。 建立位图索引（1）位图索引原理 在基数小的栏位上要善于使用位图索引，基数是位图索引中的一个基本的定义，它是指数据库表中某个字段内容中不重复的数值。如在员工信息表中的性别字段，一般就只有男、女两个值，因此其基数为2；婚姻状况字段的话，则其只有已婚、未婚、离婚三种状态，其基数就为3；民族一览内也只存在有限的取值。 对于要查询基数小的字段，如现在用户想查找所有婚姻状况为”已婚”的”女性”时，利用位图索引可以提高查询的效率。这主要是因为标准索引是通过在索引中保存排序过的索引列以及对应的ROWID来实现的。若我们在基数小的列上建立标准索引的话，则其会返回大量的记录。 而当我们在创建位图索引的时候，通

48、常地数据库管理系统（如Oracle）会对整个表进行扫描，并且会为索引列的每个取值建立一个位图。若内容相同，则在位图上会以一个相同的数字表示。此时，若这个字段的基数比较小的话，则若需要实现对整个字段的查询的话，效率就会非常的高。因为此时，数据库只要位图中数字相同的内容找出来即可。 除了在数据表某列基数比较小的情况下，采用位图索引外，我们往往在一些特殊的情况下，也会建议采用位图索引。最常见的情况是，在Where限制条件中，若我们多次采用AND或者OR条件时，也建议采用位图索引。因为当一个查询引用了一些部署了位图索引的列的时候，这些位图可以很方便的与AND或者Or 运算符操作结合以快速的找出用户所需

49、要的记录。（2）使用位图索引的场合 在数据库设置中，一般只有在三种情况下才采用位图索引： 列的基数比较小，而有可能需要根据这些字段的内容查找相关的记录。 在条件语句中，用到了AND或者OR运算符的时候（除了这两种情况外，最好能够采用其他适合的索引）； 需要用到NULL作为查询的限制条件，位图索引会记录相关的NULL值列信息。因为标准查询一般情况下，会忽略所有的NULL值列。也就是说，若需要查询“所有没有身份证号码”的员工的信息的时候，标准索引并不能够起到加速查询速度的作用。此时，需要采用位图索引。（3）与B树索引的比较 假设银行某理财产品存在5种类型的业务：A、B、C、D、E，在数据库中为该类

50、型建立位图索引：如果某行包含一个特定值那么位图在此位置上包含一个1位，否则它包含一个0位，如下图所示。图 位图索引 假设产品表中有一千万行记录，并假设B树索引中的每个索引项有10个字节，那么一个B树索引将会需要： 一千万索引项×每项10字节=100MB； 一个位图索引大约需要： (一千万行×每行1位×5个位图)每字节8位=625MB； 在这种情况下，位图索引不仅节省存储，而且读取速度必然要比B树索引快得多。（4）注意事项 不是在条件语句中包含运算符的时候，采用位图索引都能够提供比较高的效率。一般来说，只有AND 或者OR运算符的时候，位图索引才会比较具有优势。若此

51、时用户采用大于号或者不等于号作为条件语句中的限制条件的时候，则往往采用标准索引具有更大的优势。 使用组合索引 组合索引又叫复合索引，是同时包含两个或两个以上列的索引。如果一列数据的信息不能单独提供较高的选择性，组合索引将会非常有用，可以有效的缩小搜索范围。 吉林银行使用Oracle数据库作为数据仓库，在组合索引的使用方面，Oracle有以下特点： 当使用基于规则的优化器(RBO)时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引； 在使用Oracle9i之前的基于成本的优化器(CBO)时， 只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到

52、该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径(请见下面的测试1和测试2)； 从Oracle9i起，Oracle引入了一种新的索引扫描方式索引跳跃扫描(index skip scan)，这种扫描方式只有基于成本的优化器(CBO)才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引； Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示(hint)，我们可

53、以帮助Oracle优化器作出更好的选择。 如上所述，虽然Oracle 9i以后引入的跳跃式扫描索引访问方法增强了优化器在使用组合索引时的选择，但是您应该谨慎地选择索引中的列顺序。一般来说，索引的第一列应该是最有可能在WHERE子句中使用的列，并且也是索引中最具选择性的列。 聚集索引与非聚集索引 聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引)，就像电话簿按姓氏和名字进行组织一样。 表 聚集索引的使用场合动作描述使用聚集索引使用非聚集索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引列不应应 如上表所示，为聚集索引的使用场合，聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引