专栏首页文渊之博数据仓库中如何使用索引

数据仓库中如何使用索引

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

当然,在创建数据仓库索引的时候需要考虑一些参数比如数据仓库类型、维度表和事实表大小、是否分区、是否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更倾向于反复使用相同的查询,因此可以使用索引优化向导或者对查询进行精确调优。开始单纯严谨彻底地评估以便在数据仓库中建立索引。

总结

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 索引初探(二)

    在SqlServer中分为两种索引,一是聚集索引;一是费聚集索引。下面我就分别对两种索引进行介绍并分析其区别和各自的特点。 1.聚集索引      之前看过一个...

    用户1217611
  • 索引初探(三)

    由于前一篇写的有点匆忙很多地方不是很简单,这一片再描述一些概念和细节。 首先,我们都知道在数据库中的存储分为两种结构,一是堆;二是B树。堆的数据是没有排序也就没...

    用户1217611
  • 索引的初探(一)

    以前听做DBA的朋友说索引能解决数据库百分之八十的问题,我也开始简单的写几篇关于索引的随笔,顺便来总结一下我理解的索引以及相关的知识,毕竟进步在于总结。 简介:...

    用户1217611
  • 一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

    之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 -...

    bisal
  • SQL Server 索引和表体系结构(聚集索引+非聚集索引)

    聚集索引 概述 关于索引和表体系结构的概念一直都是讨论比较多的话题,其中表的各种存储形式是讨论的重点,在各个网站上面也有很多关于这方面写的不错的文章,我写这...

    逸鹏
  • 稀疏索引和稠密索引你了解吗?

    最近参加了一个面试,面试官先问了mysql的数据库的索引的底层数据接口,我回答了:平时都用的是innodb引擎,所以其底层的索引数据类型是B+树。面试官问我用没...

    居士
  • Mysql索引

    Mysql官方对索引的定义为:索引(index)是帮助Mysql高效获取数据的数据结构。

    彼岸舞
  • 索引入门:顺序索引

    之前我对索引的了解基本就是主索引和二级索引,此外还经常见到一些其他概念,如聚集索引和非聚集索引,稀疏索引和密集索引等,今天系统整理一下。

    Apache IoTDB
  • 数据库中的联合索引

    用户1637228
  • linux一些基本命令与mysql索引

    在Linux中大部分的操作都是通过终端来实现的,或者直接使用命令行界面,一般使用Linux架构服务器的话,安装的都是命令行界面,不安装图形化界面的原因是因为没有...

    端碗吹水

扫码关注云+社区

领取腾讯云代金券