数据仓库中索引的使用方法

数据仓库中索引的使用方法数据仓库的索引是一个棘手的问题。如果索引太多，数据插入会很快，但查询响应会很慢。如果索引过多，数据导入速度较慢，数据存储空间较大，但查询响应较快。索引在数据库中的作用是加快查询速度，无论是传统数据库还是数据仓库。特别是对于数据量大、查询复杂的表设计表连接。之前接触数据仓库很少，这里只是一点点体会。当然，在创建数仓索引时需要考虑一些参数，比如数仓类型、维表和事实表大小、是否分区、是否ADhoc等等。这些参数决定了你的索引结构。本文主要介绍如何对数据仓库中的关系表进行索引。注意是关系数据库中的关系表，不是SSAS中的数据表。维度索引如果你打算在维度表的主键上创建索引，并且该键是代理键，而不是自然键或业务键（例如用户名或ID）。注意不要在维表的代理键或者实现渐变的列上建聚簇索引。维表包含一个自然键或业务键（如交易代码或ID），我们称之为来自业务系统的业务键。虽然业务key可能不完美，但是对于缓慢变化的维度表还是在标识列（比如用户id等）上建索引比较好，如下图：维度表中的聚簇索引创建users和products在业务键上，通过这样的索引，可以提高查询速度，尤其是在where语句中使用这些键。通常，该键值常用于查询维度数据的where表达式中。在业务键上构建聚簇索引可以避免锁升级（例如，行锁到表锁，意向独占到独占），因为在ETL过程中如果代理键上有非聚簇索引并且所有行都添加到可能会发生文件锁升级。如果排他锁从行锁升级为表锁，会造成其他读、ETL或一般操作的阻塞甚至死锁，最终导致程序超时。在上图中，日期和时间维度是否有任何外部数据源或业务键。考虑使用YYYYMMDD和HHMMSSSSS格式作为两张表的主键，建立聚簇索引。该值保证了索引顺序，简化了事实表中的范围查询，并且该键值还包含日期或时间，不需要具体时间。对于缓慢变化的大型维度表（例如需要键入新数据的地方），可以创建一个由业务键、记录开始时间、记录结束时间和代理键四部分组成的非聚集索引。为了提高效率并防止存储增长，请使用Include来包含记录结束时间和代理键，如下所示：3CREATENONCLUSTEREDINDEXMyDim_CoveringIndexON(NaturalKEY,RecordStartDate)INCLUDE(RecordEndDate,SurrogateKEY);该索引用于在ETL过程中查询和操作历史数据非常有效，通过非聚集索引减少列来减少不必要的存储空间。关系型数据库引擎可以直接从索引中获取数据，无需直接访问维度数据，减少了IO，提高了查询速度。如果您在维度表中有其他列用于查询、排序、分组，您也可以创建非聚集索引，就像在事务数据库中一样。如果维度表中存在嵌套层级，比如产品维度表中class-subcategory-productID的层级关系，可以考虑在层级的key值上建立索引，这样会显着提高数据查询，不会影响数据导入。在事实表上建立索引类似于在维度表上建立索引。当然，还需要考虑分区等条件。聚簇索引可以建立在日期列或日期+时间混合列上。因为BI分析总是使用日期/时间成分，所以事实表包含日期或日期时间列，这里使用聚簇索引将有助于构建立方体。同样出于这个原因，数据记录也按日期或日期时间的顺序存储。历史查询有很多优势。如果事实表有多个这样的列，则需要在最常查询或建立方的列上建立索引。如果在日期列上进行分区，则可以在该列上使用聚集索引。当发现用于创建同一列的分区和聚集索引，并且索引是在保存分区事实表的文件组上创建的，那么SQLServer会自动使用事实表分区对索引进行分区（例如，索引将具有与事实表相同的分区函数和列）。当索引按照事实表分区时，表和它的索引会自动对齐，尤其是当你频繁创建分区或切换分区时，方便很多。接下来，在每个事实表的外键上创建一个非聚集索引，并考虑混合使用外键和日期键，如图1所示。可以看到创建了一个类似CustomerKEY+DateKEY的索引。使用相同外键值的查询会有时间排序，提高查询速度。请注意，在处理外键时，维护关系完整性是一个问题。改进索引架构随着时间的推移，数据仓库会发生变化以适应组织结构的变化，而索引结构也必须发生变化。大多数数据仓库或BI系统都直接连接到关系表，因此可以使用关系表调整方法来执行索引修改，例如评估查询和数据混合以相应地调整索引。如果关系数据仓库只是用来表示SSAS结构，那么我们前面讨论的索引可能就不需要了。SSAS更喜欢反复使用相同的查询，因此您可以使用索引优化向导或微调查询。从对数据仓库中的索引进行简单而全面的评估开始。总结本文只是简单介绍了一般数据仓库中如何为关系型数据表建立索引，但是很多时候需要根据实际的需求来建立索引，甚至有的时候甚至无法使用索引。考虑到消耗和时间效率等多个方面，还是要根据生产环境的要求不断变化。