数据仓库中如何使用索引

数据仓库中如何使用索引

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

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

维度索引

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

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

1

用户和产品的维度表中聚集索引建立在业务键上,通过这样的索引,能强化查询速度尤其是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更倾向于反复使用相同的查询,因此可以使用索引优化向导或者对查询进行精确调优。开始单纯严谨彻底地评估以便在数据仓库中建立索引。

 

总结

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119716.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • unity3D 编辑器扩展,MenuItem 和 ContextMenu 的使用方法[通俗易懂]

    unity3D 编辑器扩展,MenuItem 和 ContextMenu 的使用方法[通俗易懂]官方也有一个文章,举了MenuItem类的一些使用方法。我是传送门,点我首先是unity顶部菜单栏的一些用法,如图:注意:MenuItem是编辑器类,所以技能导入usingUnityEditor;命名空间,且一般我们的类也不是集成自MonoBehaviour的,而是集成ScriptableObject的。最普通的MenuItem调用:[MenuItem(“MenuItem/普通…

    2022年10月29日
  • SQL 模糊查询LIKE concat用法[通俗易懂]

    SQL 模糊查询LIKE concat用法[通俗易懂]concat用来拼接查询的字符串,如下代码所示SELECT*FROMdeploymentWHEREnameLIKEconcat(concat(‘%’,#{queryMessage}),’%’) 

  • Android开机动画bootanimation

    Android开机动画bootanimationandroid开机动画详细分析可以参见http://blog.csdn.net/luoshengyang/article/details/7691321引用老罗的文章,写的太好了。以下介绍一些相关的点。这里的开机动画指的是以bootanimation.zip方式存在,不包括BootVideo这种方式。1、开机动画路径:bootanimation.zip路径有两个:a、”/data/lo

  • 善待自己:改变命运的N个人生哲理

    善待自己:改变命运的N个人生哲理心灵的栅栏  人与月亮的距离并不遥远,因为人与人心灵间的距离更为遥远。  ——王尔德    当玛格丽特的丈夫杰瑞因脑瘤去世后,她变得异常愤怒,生活太不公平,她憎恨孤独。孀居3年,她的脸变得紧绷绷的。  一天,玛格丽特在小镇拥挤的路上开车,忽然发现一幢她喜欢的房子周围竖起一道新的栅栏。那房子已有一百多年的历史,颜色变白,有很大的门廊,过去一直隐藏在路后面。如今马路扩展,街口竖起了红绿灯,小镇已颇有些

  • Java 实现水仙花数

    Java 实现水仙花数Java实现水仙花数,并进行代码优化。

  • 机器学习之数据预处理

    在sklearn之数据分析中总结了数据分析常用方法,接下来对数据预处理进行总结当我们拿到数据集后一般需要进行以下步骤:(1)明确有数据集有多少特征,哪些是连续的,哪些是类别的(2)检查有没有缺失

    2021年12月30日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号