更新时间:2024-04-18 GMT+08:00
分享

表结构设计

在进行调优表实践之前,需要先了解下表结构设计相关的内容。因为进行数据库设计时,表设计上的一些关键项将严重影响后续整库的查询性能。表设计对数据存储也有影响:好的表设计能够减少I/O操作及最小化内存使用,进而提升查询性能。

本小节介绍如何设计GaussDB(DWS)表结构(包括选择表存储方式、压缩级别、分布方式、分布列以及使用分区表和局部聚簇等),从而实现表性能的优化。

选择存储方式

表的存储模型选择是表定义的第一步。业务属性是表的存储模型的决定性因素,根据下表选择适合当前业务的存储模型。

一般情况下,如果表的字段比较多(大宽表),查询中涉及到的列不多的情况下,适合列存储。如果表的字段个数比较少,查询大部分字段,那么选择行存储比较好。

存储模型

适用场景

行存

点查询(返回记录少,基于索引的简单查询)。

增删改比较多的场景。

列存

统计分析类查询 (group,join多的场景)。

表的行/列存储通过表定义的orientation属性定义。当指定orientation属性为row时,表为行存储;当指定orientation属性为column时,表为列存储;如果不指定,默认为行存储。

使用表压缩

表压缩可以在创建表时开启,压缩表能够使表中的数据以压缩格式存储,意味着占用相对少的内存。

对于I/O读写量大,CPU富足(计算相对小)的场景,选择高压缩比;反之选择低压缩比。建议依据此原则进行不同压缩下的测试和对比,以选择符合自身业务情况的最优压缩比。压缩比通过COMPRESSION参数指定,其支持的取值如下:

  • 列存表为:YES/NO/LOW/MIDDLE/HIGH,默认值为LOW。
  • 行存表为:YES/NO,默认值为NO。(行存表压缩功能暂未商用,如需使用请联系技术支持工程师)

各压缩级别所适用的业务场景说明如下:

压缩级别

所适用的业务场景

低级别压缩

系统CPU使用率高,存储磁盘空间充足。

中度压缩

系统CPU使用率适中,但存储磁盘空间不是特别充足。

高级别压缩

系统CPU使用率低,磁盘空间不充裕。

选择分布方式

GaussDB(DWS)支持的分布方式有复制表(Replication)、哈希表(Hash)和轮询表(Roundrobin)。

轮询表(Roundrobin)8.1.2及以上集群版支持。

策略

描述

适用场景

优势与劣势

复制表(Replication)

集群中每一个DN实例上都有一份全量表数据。

小表、维度表。

  • Replication优点是每个DN上都有此表的全量数据,在join操作中可以避免数据重分布操作,从而减小网络开销,同时减少了plan segment(每个plan segment都会起对应的线程)。
  • Replication缺点是每个DN都保留了表的完整数据,造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。

哈希表(Hash)

表数据通过hash方式散列到集群中的所有DN实例上。

数据量较大的事实表。

  • 在读/写数据时可以利用各个节点的IO资源,大大提升表的读/写速度。
  • 一般情况下大表(1000000条记录以上)定义为Hash表。

轮询表(Roundrobin)

表的每一行被轮番地发送给各个DN,数据会被均匀地分布在各个DN中。

数据量较大的事实表,且使用Hash分布时找不到合适的分布列。

  • Roundrobin优点是保证了数据不会发生倾斜,从而提高了集群的空间利用率。
  • Roundrobin缺点是无法像Hash表一样进行DN本地化优化,查询性能通常不如Hash表。
  • 一般在大表无法找到合适的分布列时,定义为Roundrobin表,若大表能够找到合适的分布列,优先选择性能更好的Hash分布。

选择分布列

采用Hash分布方式,需要为用户表指定一个分布列(distribute key)。当插入一条记录时,系统会根据分布列的值进行hash运算后,将数据存储在对应的DN中。

所以Hash分布列选取至关重要,需要满足以下原则:

  1. 列值应比较离散,以便数据能够均匀分布到各个DN。例如,考虑选择表的主键为分布列,如在人员信息表中选择身份证号码为分布列。
  2. 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如,表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’),那么就应当尽量不用zqdh做分布列。
  3. 在满足前两条原则的情况,考虑选择查询中的连接条件为分布列,以便Join任务能够下推到DN中执行,且减少DN之间的通信数据量。

    对于Hash分表策略,如果分布列选择不当,可能导致数据倾斜,查询时出现部分DN的I/O短板,从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查,以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性:

    1
    2
    3
    4
    5
    SELECT 
    xc_node_id, count(1) 
    FROM tablename 
    GROUP BY xc_node_id 
    ORDER BY xc_node_id desc;
    

    其中xc_node_id对应DN,一般来说,不同DN的数据量相差5%以上即可视为倾斜,如果相差10%以上就必须要调整分布列

  4. 一般不建议用户新增一列专门用作分布列,尤其不建议用户新增一列,然后用SEQUENCE的值来填充作为分布列。因为SEQUENCE可能会带来性能瓶颈和不必要的维护成本。

使用分区表

分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表,物理块称之为分区。分区表是一张逻辑表,不存储数据,数据实际是存储在分区上的。分区表和普通表相比具有以下优点:

  1. 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索效率。
  2. 增强可用性:如果分区表的某个分区出现故障,表在其他分区的数据仍然可用。
  3. 方便维护:如果分区表的某个分区出现故障,需要修复数据,只修复该分区即可。

GaussDB(DWS)支持的分区表为范围分区表和列表分区(列表分区8.1.3集群版本支持)。

使用局部聚簇

局部聚簇(Partial Cluster Key)是列存下的一种技术。这种技术可以通过min/max稀疏索引较快的实现基表扫描的filter过滤。Partial Cluster Key可以指定多列,但是一般不建议超过2列。Partial Cluster Key的选取原则:

  1. 受基表中的简单表达式约束。这种约束一般形如col op const,其中col为列名,op为操作符 =、>、>=、<=、<,const为常量值。
  2. 尽量采用选择度比较高(过滤掉更多数据)的简单表达式中的列。
  3. 尽量把选择度比较低的约束col放在Partial Cluster Key中的前面。
  4. 尽量把枚举类型的列放在Partial Cluster Key中的前面。

选择数据类型

高效数据类型,主要包括以下三方面:

  1. 尽量使用执行效率比较高的数据类型

    一般来说整型数据运算(包括=、>、<、≧、≦、≠等常规的比较运算,以及group by)的效率比字符串、浮点数要高。比如某客户场景中对列存表进行点查询,filter条件在一个numeric列上,执行时间为10+s;修改numeric为int类型之后,执行时间缩短为1.8s左右。

  2. 尽量使用短字段的数据类型

    长度较短的数据类型不仅可以减小数据文件的大小,提升IO性能;同时也可以减小相关计算时的内存消耗,提升计算性能。比如对于整型数据,如果可以用smallint就尽量不用int,如果可以用int就尽量不用bigint。

  3. 使用一致的数据类型

    表关联列尽量使用相同的数据类型。如果表关联列数据类型不同,数据库必须动态地转化为相同的数据类型进行比较,这种转换会带来一定的性能开销。

使用索引

  • 建立索引的目的是为了加速查询,所以请确保索引能在一些查询中被使用。如果一个索引不会被任何查询语句用到,那这个索引是没有意义的,请删除这个索引。
  • 避免创建不需要的二级索引,有用的二级索引能加速查询,但是要注意索引占用空间也会随这索引数量的增加而增加。每增加一个索引,在插入一条数据的时候,就要额外新增一个 Key-Value,所以索引越多,写入越慢,并且空间占用越大。另外过多的索引也会影响优化器运行时间,并且不合适的索引会误导优化器。所以索引并不是越多越好。
  • 根据具体的业务特点创建合适的索引。原则上需要对查询中需要用到的列创建索引,目的是提高性能。下面几种情况适合创建索引:
    • 区分度比较大的列,通过索引能显著地减少过滤后的行数。例如推荐在人的身份证号码这一列上创建索引,但不推荐在人的性别这一列上创建索引。
    • 有多个查询条件时,可以选择组合索引,注意需要把等值条件的列放在组合索引的前面。这里举一个例子,假设常用的查询是 SELECT * FROM t where c1 = 10 and c2 = 100 and c3 > 10,那么可以考虑建立组合索引 Index cidx (c1, c2, c3),这样可以用查询条件构造出一个索引前缀进行 Scan。
  • 在查询条件中使用索引列作为条件时,不要在索引列上做计算、函数或者类型转换的操作,会导致优化器无法使用该索引。
  • 尽量使索引列包含查询列,避免总是 SELECT * 查询所有列的语句。
  • 查询条件使用 !=,NOT IN 时,无法使用索引。
  • 使用LIKE时如果条件是以通配符 % 开头,也无法使用索引。
  • 当查询条件有多个索引可供使用,但用户知道用哪一个索引是最优的时,推荐使用优化器Hint来强制优化器使用这个索引,这样可以避免优化器因为统计信息不准或其他问题时,选错索引。
  • 查询条件使用IN表达式时,后面匹配的条件数量不宜过多,否则执行效率会较差。
分享:

    相关文档

    相关产品