数据库知识讲解.docx
《数据库知识讲解.docx》由会员分享，可在线阅读，更多相关《数据库知识讲解.docx（18页珍藏版）》请在冰豆网上搜索。
数据库知识讲解
这篇论坛文章根据笔者多年的数据仓库实施经验，同时结合ETL的过程原理和数据仓库建设方法归纳总结了以下优化的方案，详细内容请读者参考下文：
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*ROMDEPTwhereSAL*12>25000;
高效：
select*FROMDEPTwhereSAL>25000/12;
（3）避免在索引列上使用NOT和”!
=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到NOT和”!
=”时，就会停止使用索引转而执行全表扫描。
（4）索引列上用>=替代>
高效：
select*FROMEMPwhereDEPTNO>=4
低效：
select*FROMEMPwhereDEPTNO>3
两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
（5）函数的列启用索引方法，如果一定要对使用函数的列启用索引，Oracle9i以上版本新的功能：
基于函数的索引（Function-BasedIndex）是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。
createINDEXEMP_IONEMP（UPPER（ENAME））;
select*FROMEMPwhereUPPER（ENAME）=‘BLACKSNAIL’;
3.2游标的正确使用
当在海量数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL过程中的使用又必不可少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。
对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性，所以采用游标的是数据维护完整性的最好方式。
由于它的效率低，如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指10万条记录以上的维表），以下是处理这种情况的有效方式：
（1）在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。
（2）在insert和update维表时都加上一个条件来过滤维表中已经存在的记录，实例为：
insertINTODIM_CUSTOMERselect*FROMODS_CUSTOMERwhereODS_CUSTOMER.CODENOTexists（DIM_CUSTOMER.CODE）
（3）使用显式的游标（CURSORs），因为使用隐式的游标将会执行两次操作，第一次检索记录，第二次检查TOOMANYROWS这个EXCEPTION，而显式游标不执行第二次操作。
3.3数据抽取和上载时的SQL优化
3.3.1where子句中的连接顺序
ORACLE采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其它where条件之前，那些可以过滤掉最大数量记录的条件必须写在where子句的末尾。
低效：
select*FROMEMPEwhereSAL>50000ANDJOB=‘MANAGER’AND25<（selectcount（*）FROMEMPwhereMGR=E.EMPNO）;
高效：
select*FROMEMPEwhere25<（selectcount（*）FROMEMPwhereMGR=E.EMPNO）ANDSAL>50000ANDJOB=‘MANAGER’;
3.3.2删除全表时用truncate替代delete
当delete删除表中的记录时，有回滚段（rollbacksegments）用来存放可以被恢复的信息，而当运用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用NOTexists替代NOTIN
子查询中，NOTIN子句将执行一个内部的排序和合并，无论在哪种情况下，NOTIN都是最低效的，因为它对子查询中的表执行了一个全表遍历。
用NOTexists替代NOTIN将提高查询的效率。
3.3.6优化GROUPBY
提高GROUPBY语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉。
低效：
selectJOB,AVG（SAL）FROMEMPGROUPBYJOBHAVINGJOB=‘PRESIDENT’ORJOB=‘MANAGER’
高效：
selectJOB,AVG（SAL）FROMEMPwhereJOB=‘PRESIDENT’ORJOB=‘MANAGER’GROUPBYJOB
3.3.7有条件的使用union-ALL替换union
ETL过程针对多表连接操作的情况很多，有条件的使用union-ALL替换union的前提是：
所连接的各个表中无主关键字相同的记录，因为unionALL将重复输出两个结果集合中相同记录。
当SQL语句需要union两个查询结果集合时，这两个结果集合会以union-ALL的方式被合并，然后在输出最终结果前进行排序。
如果用unionALL替代union，这样排序就不是必要了，效率就会因此得到提高3-5倍
3.3.8分离表和索引
总是将你的表和索引建立在不同的表空间内，决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。
同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上
事实表和维度表的设计
数据仓库的物理模型较常见的操作型数据库的物理模型有很大不同。
最明显的区别是：
操作型数据库主要是用来支撑即时操作，对数据库的性能和质量要求都比较高，为了防止“garbagein，garbageout”，通常设计操作型数据库的都要遵循几个范式的约束，除非少数情况下为了性能进行妥协，才可能出现冗余。
而数据仓库的建立并不上为了支撑即时操作，或者说，数据仓库的数据是来源于即时操作产生的数据，而不是直接来源于即时操作。
所以它的数据质量是由操作性系统来保证的，而不是由几个范式来保证的。
而且为了更好的跟踪历史信息，以及更快的产生报表，数据仓库的物理模型中存在着大量冗余字段。
数据仓库的物理模型分为星型和雪花型两种。
所谓星型，就是将模型中只有一个主题，其他的表中存储的都是主题的一些特征。
比如货物销量的主题仓库中，每次出售记录是事实表，而时间，售货员，商品是维度，都和事实表有联系，组织起来就是星型。
而如果更细化来看，商品是有种类，产地，价格等特征的，从这个角度来看，有两个主题，一个是商品出售，一个是商品本身。
组织起来就是雪花型。
实际项目中，由于操作型系统业务的复杂性导致本身产生了大量的数据，所以，组织起来也以雪花型居多。
那么围绕着主题，该如何设计事实表和维度表呢？
也是有规律可循的。
事实表和维度表的分界线
事实表是用来存储主题的主干内容的。
以日常的工作量为例，工作量可能具有如下属性：
工作日期，人员，上班时长，加班时长，工作性质，是否外勤，工作内容，审核人。
那么什么才是主干内容？
很容易看出上班时长，加班时长是主干，也就是工作量主题的基本内容，那么工作日期，人员，工作性质，是否外勤，工作内容是否为主干信息呢？
认真分析特征会发现，日期，人员，性质，是否外勤都是可以被分类的，例如日期有年-月-日的层次，人员也有上下级关系，外勤和正常上班也是两类上班考勤记录，而上班时长和加班时长则不具有此类意义。
所以一般把能够分类的属性单独列出来，成为维度表，在事实表中维护事实与维度的引用关系。
在上述例子中，事实表可以设计成如下
WorkDate EmployeeID，WorkTypeID，Islegwork，Content，
而时间，员工，工作类型，是否外勤则归为维度表。
总的来看，和其他建立主外键关系的表也都一样。
但是维度表的建立是需要有层次的（虽然不是必须，但是也是典型特征），而事实表的建立是针对已经发生的事实的，是历史数据的存档，也就是说是不应该修改的。
以测试部测试软件的Bug为例。
每个Bug都是一个事实。
这个Bug的状态在数据字典里可能设计成新建，转派，修复，拒绝等等。
那么在事实表中Bug表中有一个字段为Status。
当测试员或者开发人员改变了这个状态的值，事实表中该如何更新呢？
是直接更新Status还是什么其他的方式？
显然，为了能够追踪这个Bug的历史信息，应该是重新插入一条新的记录。
那么这和以往的数据库设计有什么区别呢？
可以看出对于原始记录和新插入的记录，其他字段全部是相同的，也就是全部冗余的。
如果以BugID作为主键，这时候会发现主键都是冗余的（当然，插入之前只能删除主键）。
所以可以看出，事实表一般是没有主键的。
数据的质量完全由业务系统来把握。
例如，在AdventureWorksDW数据库中，事实表是类似于如此
事实表中的外键是指向维度表的，那么维度表又有什么特征呢？
以时间维度为例
可以看出，维度表一般是有主键的。
代表该类物质的一个单一个体，其他的字段一般都是有层次关系的，例如2009年2月19日是主键，那么它会有年--月--日这样的层次，为了方便统计，年月日不会在做聚合的时候才计算出来，而是在维护记录时已经计算出来。
那么这些字段的冗余是否值得呢？
可以这样解释：
维度表的数据一般是比较少的，这个少是指相对事实表来讲的。
因为事实表是与日俱增，而维度表则增长缓慢，所以绝对数字也不会太大。
在事实表和维度表做连接查询的时候，会产生与事实表一样大的数据量，如果还需要groupbyYear（TimeKey）的话，其一是会增加计算，其二是由于引入了计算，索引会失效。
这个代价比引入冗余字段要大的多。
以AdventureWorks为例，它总共引入了日历年-月-日，财年-月-日，还有日历年-周-日，财年-周-日等等多个层次。
那么它在每个层次不同级别上做聚合都是不需要引入函数来做Year（TimeKey），Month（TimeKey）这样的运算。
总的说来，事实表的设计是以能够正确记录历史信息为准则，维度表的设计是以能够以合适的角度来聚合主题内容为准则。
数据仓库建模与ETL的实践技巧
一、Data仓库的架构
Data仓库（DataWarehouseDW）是为了便于多维分析和多角度展现而将Data按特定的模式进行存储所建立起来的关系型Datcbase，它的Data基于OLTP源Systam。
Data仓库中的Data是细节的、集成的、面向主题的，以OLAPSystam的分析需求为目的。
Data仓库的架构模型包括了星型架构（图二：
piC2.bmp）与雪花型架构（图三：
piC3.bmp）两种模式。
如图所示，星型架构的中间为事实表，四周为维度表，类似星星;而相比较而言，雪花型架构的中间为事实表，两边的维度表可以再有其关联子表，从而表达了清晰的维度层次关系。
从OLAPSystam的分析需求和ETL的处理效率两方面来考虑：
星型结构聚合快，分析效率高;而雪花型结构明确，便于与OLTPSystam交互。
因此，在实际项目中，我们将综合运用星型架构与雪花型架构来设计Data仓库。
那么，下面我们就来看一看，构建企业级Data仓库的流程。
二、构建企业级Data仓库五步法
（一）、确定主题
即确定Data分析或前端展现的主题。
例如：
我们希望分析某年某月某一地区的啤酒销售情况，这就是一个主题。
主题要体现出某一方面的各分析角度（维度）和统计数value型Data（量度）之间的关系，确定主题时要综合考虑。
我们可以形象的将一个主题想象为一颗星星：
统计数value型Data（量度）存在于星星中间的事实表;分析角度（维度）是星星的各个角;我们将通过维度的组合，来考察量度。
那么，“某年某月某一地区的啤酒销售情况”这样一个主题，就要求我们通过时间和地区两个维度的组合，来考察销售情况这个量度。
从而，不同的主题来源于Data仓库中的不同子集，我们可以称之为Data集市。
Data集市体现了Data仓库某一方面的信息，多个Data集市构成了Data仓库。
（二）、确定量度
在确定了主题以后，我们将考虑要分析的技术指标，诸如年销售额之类。
它们一般为数value型Data。
我们或者将该Data汇总，或者将该Data取次数、独立次数或取最大最小value等，这样的Data称为量度。
量度是要统计的指标，必须事先选择恰当，基于不同的量度可以进行复杂关键性能指标（KPI）等的设计和计算。
（三）、确定事实Data粒度
在确定了量度之后，我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况。
考虑到量度的聚合程度不同，我们将采用“最小粒度原则”，即将量度的粒度设置到最小。
例如：
假设目前的Data最小记录到秒，即Datcbase中记录了每一秒的交易额。
那么，如果我们可以确认，在将来的分析需求中，时间只需要精确到天就可以的话，我们就可以在ETL处理过程中，按天来汇总Data，此时，Data仓库中量度的粒度就是“天”;反过来，如果我们不能确认将来的分析需求在时间上是否需要精确到秒，那么，我们就需要遵循“最小粒度原则”，在Data仓库的事实表中保留每一秒的Data，以便日后对“秒”进行分析。
在采用“最小粒度原则”的同时，我们不必担心海量Data所带来的汇总分析效率问题，因为在后续建立多维分析模型（CUBE）的时候，我们会对Data提前进行汇总，从而保障产生分析结果的效率。
关于建立多维分析模型（CUBE）的相关问题，我们将在下期栏目中予以阐述。
（四）、确定维度
维度是指分析的各个角度。
例如我们希望按照时间，或者按照地区，或者按照产品进行分析，那么这里的时间、地区、产品就是相应的维度。
基于不同的维度，我们可以看到各量度的汇总情况，也可以基于所有的维度进行交叉分析。
这里我们首先要确定维度的层次（HierarChy）和级别（Level）（图四：
piC4.bmp）。
如图所示，我们在时间维度上，按照“年-季度-月”形成了一个层次，其中“年”、“季度”、“月”成为了这个层次的3个级别;同理，当我们建立产品维度时，我们可以将“产品大类-产品子类-产品”划为一个层次，其中包含“产品大类”、“产品子类”、“产品”三个级别。
那么，我们分析中所用到的这些维度，在Data仓库中的存在形式是怎样的呢?
我们可以将3个级别设置成一张Data表中的3个字段，比如时间维度;我们也可以使用三张表，分别保存产品大类、产品子类、产品三部分Data，比如产品维度。
（图五：
piC5.bmp）
另外，value得一提的是，我们在建立维度表时要充分使用代理键。
代理键是数value型的ID号码（例如图六中每张表的第一个字段），它唯一标识了每一维度成员。
更重要的是，在聚合时，数value型字段的匹配和比较，JOIN效率高，便于聚合。
同时，代理键对缓慢变化维度有着重要的意义，在原Data主键相同的情况下，它起到了对新Data与历史Data的标识作用。
在此，我们不妨谈一谈维度表随时间变化的问题，这是我们经常会遇到的情况，我们称其为缓慢变化维度。
比如我们增加了新的产品，或者产品的ID号码修改了，或者产品增加了一个新的属性，此时，维度表就会被修改或者增加新的记录行。
这样，我们在ETL的过程中，就要考虑到缓慢变化维度的处理。
对于缓慢变化维度，有三种情况：
1、缓慢变化维度第一种TYPE：
历史Data需要修改。
这种情况下，我们使用UPDATEmethod来修改维度表中的Data。
例如：
产品的ID号码为123，后来发现ID号码错了，需要改写成456，那么，我们就在ETL处理时，直接修改维度表中原来的ID号码为456。
2、缓慢变化维度第二种TYPE：
历史Data保留，新增Data也要保留。
这时，要将原Data更新，将新Data插入，我们使用UPDATE/INSERT。
比如：
某一员工2005年在A部门，2006年时他调到了B部门。
那么在统计2005年的Data时就应该将该员工定位到A部门;而在统计2006年Data时就应该定位到B部门，然后再有新的Data插入时，将按照新部门（B部门）进行处理，这样我们的做法是将该维度成员列表加入标识列，将历史的Data标识为“过期”，将目前的Data标识为“当前的”。
另一种method是将该维度打上时间戳，即将历史Data生效的时间段作为它的一个属性，在与原始表匹配生成事实表时将按照时间段进行关联，这种method的好处是该维度成员生效时间明确。
3、缓慢变化维度第三种TYPE：
新增Data维度成员改变了属性。
例如：
某一维度成员新加入了一列，该列在历史Data中不能基于它浏览，而在目前Data和将来Data中可以按照它浏览，那么此时我们需要改变维度表属性，即加入新的字段列。
那么，我们将使用存储过程或程式生成新的维度属性，在后续的Data中将基于新的属性进行查看。
（五）、创建事实表
在确定好事实Data和维度后，我们将考虑加载事实表。
在公司的大量Data堆积如山时，我们想看看里面究竟是什么，结果发现里面是一笔笔生产记录，一笔笔交易记录…那么这些记录是我们将要建立的事实表的原始Data，即关于某一主题的事实记录表。
我们的做法是将原始表与维度表进行关联，生成事实表（图六：
piC6.bmp）。
注意在关联时有为空的Data时（Data源脏），需要使用外连接，连接后我们将各维度的代理键取出放于事实表中，事实表除了各维度代理键外，还有各量度Data，这将来自原始表，事实表中将存在维度代理键和各量度，而不应该存在描述性信息，即符合“瘦高原则”，即要求事实表Data条数尽量多（粒度最小），而描述性信息尽量少。
如果考虑到扩展，可以将事实表加一唯一标识列，以为了以后扩展将该事实作为雪花型维度，不过不需要时一般建议不用这样做。
事实Data表是Data仓库的核心，需要精心维护，在JOIN后将得到事实Data表，一般记录条数都比较大，我们需要为其设置复合主键和索引，以呈现Data的完整性和基于Data仓库的查询性能优化。
事实Data表与维度表一起放于Data仓库中，如果前端需要连接Data仓库进行查询，我们还需要建立一些相关的中间汇总表或物化视图，以方便查询。
三、什么是ETL
在Data仓库的构建中，ETL贯穿于项目始终，它是整个Data仓库的生命线，包括了Data清洗、整合、convert、加载等各个过程。
如果说Data仓库是一座大厦，那么ETL就是大厦的根基。
ETL抽取整合Data的好坏直接影响到最终的结果展现。
所以ETL在整个Data仓库项目中起着十分关键的作用，必须摆到十分重要的位置。
ETL是Data抽取（ExtraCt）、convert（Transform）、加载（Load）的简写，它是指：
将OLTPSystam中的Data抽取出来，并将不同Data源的Data进行convert和整合，得出一致性的Data，然后加载到Data仓库中。
例如：
下图就向我们展示了ETL的Dataconvert效果。
（图七：
piC7.bmp）
那么，在这一convert过程中，我们就完成了对Data格式的更正、对Data字段的合并、以及新增指标的计算三项操作。
类似地，我们也可以根据其他需求，完善Data仓库中的Data。
简而言之，通过ETL，我们可以基于源Systam中的Data来生成Data仓库。
ETL为我们搭建了OLTPSystam和OLAPSystam之间的桥梁。
四、项目实践技巧
（一）、准备区的运用
在构建Data仓库时，如果Data源位于一台服务器上，Data仓库在另一台服务器端，考虑到Data源Server端访问频繁，并且Data量大，需要不断更新，所以可以建立准备区Datcbase（图八：
piC8.bmp）。
先将Data抽取到准备区中，然后基于准备区中的Data进行处理，这样处理的好处是防止了在原OLTPSystam中频繁访问，进行Data计算或排序等操作。
例如我们可以按照天将Data抽取到准备区中，基于Data准备区，我们将进行Data的convert、整合、将不同Data源的Data进行一致性处理。
Data准备区中将存在原始抽取表、convert中间表和临时表以及ETL日志表等。
（二）、时间戳的运用
时间维度对于某一事实主题来说十分重要，因为不同的时间有不同的统计Data信息，那么按照时间记录的信息将发挥很重要的作用。
在ETL中，时间戳有其特殊的作用，在上面提到的缓慢变化维度中，我们可以使用时间戳标识维度成员;在记录Datcbase和Data仓库
升级会员 