udw优化指南

1数据类型

通过选择最合适的数据类型可以节省磁盘空间,提高查询性能。字符类型的数据建议使用TEXT或者VARCHAR类型,不要使用CHAR类型。TEXT或者VARCHAR类型能够减少存储空间。选取合适的数字类型,如INT或者SMALLINT能满足业务需求时,使用BIGINT会带来磁盘空间的浪费。

2存储方式

udw在创建表格的时候提供了堆表和追加表两种存储模式,提供了行存储、列存储、外部表的存储方式。

2.1堆表和追加表

堆表适合于较小、经常更新的数据存储方式。

追加表适合不经常更新,批量INSERT的大表。不要在追加表上执行单个的INSERT、UPDATE或者DELETE操作。并发的INSERT操作是可以,但是不要执行并发的UPDATE或者DELETE操作。

2.2行存储和列存储

行存储的应用场景

表数据在载入后经常update;

表数据经常insert;

查询中选择大部分的列;

列存储的应用场景

列存储一般适用于宽表(即字段非常多的表)。在使用列存储时,同一个字段的数据连续保存在一个物理文件中,所以列存储的压缩率比普通压缩表的压缩率要高很多,另外在多数字段中筛选其中几个字段中,需要扫描的数据量很小,扫描速度比较快。因此,列存储尤其适合在宽表中对部分字段进行筛选的场景。注意:列存储的表必须是追加表(Appendonly table)。

3压缩

压缩能够节约存储空间并减少从存储读取的数据大小,这种方法还可以减少磁盘 I/O 量,因此可提高查询性能。

4数据分布

UDW表的记录有两种分布策略,分别是哈希分布(DISTRIBUTED BY(key))和随机分布(DISTRIBUTED RANDOMLY)。如果不指定分布策略默认使用哈希分布,并且选择primary key或者第一个column。

在MPP环境下,查询的执行时间是由所有节点决定的。当数据出现倾斜时,会导致较低的性能以及内存溢出的情况。

当选择分布键时,考虑以下几个方面:

为所有的表显示地指定哈希或随机分布,不要使用默认的。

理想的情况下,使用一个可以将数据均匀分布在各个节点上的字段作为分布键。

不要选择可能出现在WHERE子句的字段作为分布键。

不要选择时间或时间戳类型的字段作为分布键。

作为分布键的字段的值应具有唯一性或非常高的散列程度。

如果单个字段无法达到平均分布,使用多个字段作为分布键,但不要超过两个。

如果两个字段作为分布键仍无法达到平均分布,考虑使用随机分布。

常做join操作的大表之间,选择相同的字段作为分布键(字段的顺序也要一样)。作为分布键的字段数据类型也要一致(虽然不同数据类型的某些值是一样的,但是它们的存储方式不同,哈希之后的值会不一样,因而会分布到不同的节点上)。

5导入数据

可以参考上面的加载数据、我们提供丰富的数据导入方法,我们不建议使用insert一条条的导入数据、这个效率是比较低的。强烈建议用copy或者其他更搞高效的数据导入方式。

6分区

分区表主要用来存放大表,把大数据切片,便于查询,也便于数据库维护。分区创建时,每个分区会自带一个Check约束,来限定数据的范围。

关于分区的一些建议:  只对大表进行进行分区。

不要使用默认分区。  对行存储的表不要使用过多的分区。

7索引


在绝大部分传统数据中,索引都能够极大地提高数据访问速速。然而,在像 UDW数据仓库这样的分布式数据库系统中,索引的使用需要更加谨慎。

索引会增加数据库系统的运行开销,它们占用存储空间并且在数据更新时,需要额外的维护工作。请确保查询集合在使用您创建的索引后,性能得到了改善(和全表顺序扫描相比)。可以使用 EXPLAIN 命令来确认索引是否被使用。

创建索引时,需要注意下面的问题点:  查询特点:索引对于查询只返回单条记录或者较少的数据集时,性能提升明显。

压缩表:对于压缩的 append 表来说,对于返回一部分数据的查询来说性能也能得到提高。对于压缩的数据,使用索引访问方法时,只有需要的数据才会被解压缩。  避免在经常改变的列上创建索引:在经常更新的列上创建索引会导致每次更新数据时写操作大量增加。

创建选择率高的 B-树索引  对于选择率较低的列,使用 Bitmap 索引。

对参与连接操作的列创建索引。对经常用于连接的列(例如:外键列)创建索引,可以让查询优化器使用更多的连接算法,进而提高连接效率。  对经常出现在 WHERE 条件中的列创建索引。

避免创建冗余的索引。如果索引开头几列重复出现在多个索引中,这些索引就是冗余的。  在大量数据加载时,删除索引。如果要向表中加载大量数据,考虑加载数据前删除索引,加载后重新建立索引的方法。这样的操作通常比带着索引加载要快。

考虑聚簇索引。聚簇索引是指数据在物理上,按照索引顺序存储。如果您访问的数据在磁盘是随机存储,那么数据库就需要在磁盘上不断变更位置读取您需要的数据。如果数据更佳紧密的存储起来,读取数据的操作效率就会更高。例如:在日期列上创建聚簇索引,数据也是按照日期列顺序存储。一个查询如果读取一个日期范围的数据,那么就可以利用磁盘顺序扫描的快速特性。

8ANALYZE/VACUUM

定期的执行ANALYZE或者在表格数据变化比较大的时候执行、可以提高查询效率,定期的执行VACUUM可以清理一些磁盘空间。但是执行ANALYZE和VACUUM都会占用一些系统资源。