GreenPlum中的索引

0    89    2

Tags:

👉 本文共约8442个字,系统预计阅读时间或需32分钟。

简介

在PG中,我们会经常使用索引来加速查询,但是在Greenplum中对于索引的依赖却比较低,应该合理的使用。因为在gp中顺序扫描会很快,而索引扫描则是一种随即搜索的模式。Greenplum的数据分布在Segment上,因此每个Segment会扫描全体数据的一小部分来得到结果。通过表分区,要扫描的数据量可能会更少,因此索引使用的场景可能会更少。并且索引通常会增加一些数据库负担,它们使用存储空间并且在表被更新时需要被维护。

在Greenplum数据库中,小表通常不需要索引。大部分分析型查询会在大体量数据上操作,而索引是用于从多行数据中定位 某一行或某几行

在Greenplum数据库中,顺序扫描是一种读取数据的有效方法,因为每个segment都含有数据同等 大小的一部分并且所有的segment都并行工作以读取数据。

如果增加索引不能获得性能提升,马上删掉它。验证您创建的每个索引都被优化器使用到。

对于具有高选择性的查询,索引会提升查询性能。对于选择性查询所要求的高基数表,在一个列式表的单列上 创建用于钻透目的的索引。

  • 不要在频繁更新的列上创建索引。在频繁被更新的列上创建索引会增加更新时所需的写次数。

  • 只有当表达式被频繁地使用在查询中时,才应该在表达式上建立索引。

  • 带有谓词的索引会创建一个部分索引,它可以被用来从大型表中选择少量行。

  • 避免重叠的索引。具有相同前导列的索引是冗余的。

对于返回一个定向行集合的查询来说,索引能够提高在压缩追加优化表上的性能。对于压缩数据,采用索引访问 方法意味着只有必要的页面会被解压缩。

创建有选择性的B-树索引。索引选择度是一列中的唯一值数量除以表中的行数。例如,如果一个表有1000行并且 有一列中有800个唯一值,那么该索引的选择度就是0.8,这被认为是中不错的索引使用情形。

总是在向表中装载数据前删除索引。这样装载的运行速度将会比在带有索引的表中装载数据快一个数量级。 在装载之后,重新创建索引。

位图索引适合于查询但不适合于更新。当列具有较低的基数(100到100,000个唯一值)时位图索引表现得最好。 不要为唯一列、基数非常高或者非常低的数据使用位图索引。不要为事务性负载使用位图索引。

通常,不要索引分区表。如果需要索引,索引列必须不同于分区列。索引分区表的一个好处是因为当B-树尺寸增长时 其性能呈指数下降,在分区表上创建索引可以得到很多较小的B-树,其性能比未分区表上的B-树更好。

在Greenplum数据库中使用索引

在大部分传统数据库中,索引能够极大地改善数据访问时间。 不过,在一个Greenplum之类的分布式数据库中,索引应该被更保守地使用。 Greenplum数据库会执行非常快的顺序扫描,索引则使用一种随机搜索的模式在磁盘上定位记录。 Greenplum的数据分布在Segment上,因此每个Segment会扫描全体数据的一小部分来得到结果。 通过表分区,要扫描的数据量可能会更少。 因为商业智能(BI)查询负载通常会返回非常大的数据集,使用索引并不是很有效。

首先在不加索引时尝试用户的查询负载。 索引更有可能为OLTP负载改进性能,在那种场景中查询会返回一个单一记录或者数据的一个小的子集。 在被压缩过的追加优化表上,索引也可以提高返回一个目标行集合的查询的性能,因为优化器在适当的时候可以使用一种索引访问方法而不是全表扫描。 对于压缩过的数据,使用一种索引访问方法意味着只有必要的行会被解压。

Greenplum数据库会自动为带有主键的表创建PRIMARY KEY约束。 要在一个被分区的表上创建索引,就在用户创建的分区表上创建一个索引。 该索引会被传播到Greenplum数据库所创建的所有子表上。不支持在Greenplum数据库为分区表创建的子表上创建索引。

注意一个UNIQUE CONSTRAINT(例如PRIMARY KEY CONSTRAINT)会隐式地创建一个UNIQUE INDEX,它必须包括分布键中所有的列以及任何分区键。 UNIQUE CONSTRAINT会在整个表上被强制要求,包括所有的表分区(如果有)。

索引会增加一些数据库负担,它们使用存储空间并且在表被更新时需要被维护。 要确保查询负载会用到用户创建的索引,并且检查用户增加的索引是否改进了查询性能(与表的顺序扫描相比)。 要确定是否使用了索引,检查查询的EXPLAIN计划。参见查询分析

在创建索引时请考虑以下几点。

  • 用户的查询负载。 索引能改进查询返回单一记录或者非常小的数据集的性能,例如OLTP负载。
  • 压缩表。在被压缩过的追加优化表上,索引也可以提高返回一个目标行集合的查询的性能。对于压缩过的数据,一种索引访问方法意味着只有必要的行会被解压。
  • 避免在频繁更新的列上建立索引。在一个被频繁更新的列上建立索引会增加该列被更新时所要求的写操作数据量。
  • 创建选择性的B-树索引。 索引选择度是一个列中具有的可区分值的数量除以表中行数得到的比例。 例如,如果一个表有1000行并且一个列中有800个可区分的值,则该索引的选择度为0.8,这还不错。 唯一索引的选择度总是1.0,这是最好的选择度。Greenplum数据库只允许在分布键列上的唯一索引。
  • 为低选择度的列使用位图索引。 Greenplum数据库的位图索引类型在常规的PostgreSQL中不可用。参见关于位图索引
  • 索引在连接中用到的列。 在被用于频繁连接的一个列(例如一个外键列)上的索引能够提升连接性能,因为这让查询优化器有更多的连接方法可以使用。
  • 索引在谓词中频繁使用的列。 频繁地在WHERE子句中被引用的列是索引的首选。
  • 避免重叠的索引。具有相同前导列的索引是冗余的。
  • 批量载入前删掉索引。 对于载入大量数据到一个表中,请考虑先删掉索引并且在数据装载完成后重建它们。这常常比更新索引更快。
  • 考虑一个聚簇索引。 聚簇一个索引意味着记录会根据索引被物理排序后存储在磁盘上。 如果用户需要的数据被随机分布在磁盘上,数据库必须在磁盘上来回寻找以取得所需的记录。 如果这些记录被存储得彼此临近,那么取得它们的操作就会更高效。 例如,一个在日期列上的聚簇索引中数据会按照日期顺序存放。 针对一个指定日期范围的查询将会导致对磁盘的一次有序地读取,这会利用快速的顺序访问。

在Greenplum数据库中聚簇一个索引

使用CLUSTER命令根据一个索引从物理上重新排序一个非常大的表可能会花费很长的时间。 为了更快达到同样的结果,用户可以通过创建一个中间表并且按照想要的顺序重载数据来手工在磁盘上重排数据。例如:

索引类型

Greenplum数据库数据库支持Postgres索引类型B-树、GiST和GIN,不支持Hash索引。 每一种索引类型都使用一种不同的算法,它们最适合的查询类型也不同。 B-树索引适合于最常见的情况并且是默认的索引类型。 对于这些类型的描述请见PostgreSQL文档中的索引类型

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
GreenPlum中的索引后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部