数据仓库中如何使用索引

数据仓库的索引是个棘手的问题。如果索引太多，数据插入很快但是查询响应就会很慢。如果太多索引，数据导入就很慢并且数据存储空间更大，但是查询响应更快。数据库中索引的作用就是加快查询速度，不论是传统数据库还是数据仓库。尤其是对于大数据量的表以及设计表连接的复杂查询。之前接触数据仓库比较少，这里只是介绍一点小经验。

当然，在创建数据仓库索引的时候需要考虑一些参数比如数据仓库类型、维度表和事实表大小、是否分区、是否AD hoc等等。这些参数决定了你的索引结构。本篇主要介绍如何对数据仓库中的关系表建立索引，注意是在关系数据库中的关系表，而不是SSAS中的数据表。

维度索引

如果打算在维度表的主键上建立索引，而该键是一个代理键，不是一个自然或者业务键（例如用户名称或者ID）。注意不要在维度表的代理键或者变现渐变的列上建立聚集索引。

维度表包含一个自然或者业务键（例如交易编码或者ID），我们称之为业务键是来自于业务系统的。尽管业务键可能不是唯一的，但是对于缓慢渐变的维度表而言，在标识列上建立索引是比较好的（如用户ID等），如下图：

用户和产品的维度表中聚集索引建立在业务键上，通过这样的索引，能强化查询速度尤其是where语句中使用了这些键的。通常where 表达式中经常会使用这个键值来查询维度数据。

通过业务键建立聚集索引可以避免锁升级（例如，行锁到表锁，意图排它到排它），因为在ETL过程中如果代理键上有非聚集索引并且所有的行都被添加到文件末尾就有可能发生锁升级，如果排它锁从行锁升级到表锁，那么就会引起其他读取或者ETL或者通用操作的阻塞甚至死锁，最终程序timeout。

在上图中，Date维度和Time维度有没外部的数据源或者业务键。考虑使用YYYYMMDD 和HHMMSSSSS 格式作为两个表的主键，并建立聚集索引。这个值保证了索引顺序，在事实表中也简化了范围查询，并且这个键值也包含了日期或者时间，不再需要具体时间。

 

对于大型的缓慢渐变维度表（例如这里需要键入新的数据），或许可以创建一个由四部分组成的非聚集索引包括业务键、记录开始时间、记录结束时间和代理键。为了效率并且阻止存储增大，使用Include来包含记录结束时间和代理键，如下所示:

                              CREATE NONCLUSTERED INDEX MyDim_CoveringIndex  ON (NaturalKEY, RecordStartDate)                             

                              INCLUDE ( RecordEndDate, SurrogateKEY);

 

 

这个索引在ETL的过程中对于历史数据的查询和操作是很有效的，通过非聚集索引减少列从而减少了没必要的存储空间。关系数据库引擎能直接从索引获取数据而不需要直接访问维度数据，减少了IO提高了查询速度。

如果在维度表中有其他用于查询、排序、分组的列，也可以创建非聚集索引，就如同你在事务性数据库中一样。如果在维度表中有一个嵌入层级，例如类-子类-产品ID的层级关系在产品维度表中，考虑在层次结构的键值上建立索引，会显著提高数据查询并且不会影响数据导入。

在事实表上建立索引

与在维度表建索引相似，当然需要考虑分区等条件。可以在日期列或者混合日期+时间的列上建立聚集索引。因为BI分析总是会使用日期/时间组件，事实表包含date或者datetime列，并且这里使用聚集索引会帮助构建cube。也因为这个原因，数据记录也是按照date或者datetime的顺序存储。对于历史的查询是有其优势的。如果事实表有多个这样的列，那就需要在查询或者构建cube最为频繁的列上建立索引。

如果在date列上分区，可以使用聚集索引在该列上。当发现用来创建分区和聚集索引在同一列上并且在保存分区事实表的文件组上创建了索引，那么SQLServer 将自动用事实表分区来分区索引（例如，索引会有和事实表相同的的分区函数和列）。当索引按照事实表分区后，这个表和他的索引自动对齐，尤其当你创建分区或者频繁切换分区开关时，这样就方便的多了。

下一步，创建非聚集索引在每个事实表的外键上，并且考虑混合外键和日期键，如图1所示可以见建立类似用CustomerKEY + DateKEY 的索引。使用相同的外键值查询将带有时间排序，这回提高查询速度。注意，处理外键时要考虑保持关系完整性。

改善索引架构

随着时间变化，数据仓库会发生改变来适应组织结构的变化，并且必须要改变索引结构。大多数数据仓库或者BI系统是直接连接关系表的，因此可以使用经过关系表调优的方法进行索引修改，例如评估查询和数据混合来相应地调整索引。如果关系数据仓库只用来表现SSAS结构，那么可能不需要我们之前讨论的索引。SSAS更倾向于反复使用相同的查询，因此可以使用索引优化向导或者对查询进行精确调优。开始单纯严谨彻底地评估以便在数据仓库中建立索引。

 

总结

本篇只是简单介绍了一般数据仓库的关系数据表如何建立索引，但是很多时候要根据实际请款来建立索引，甚至有时候不能使用索引。兼顾消耗和时间效率等多个方面，还是要不断通过生产环境的要求来变化的。