【云小课】EI第31课 不可不知的调优技巧-GaussDB(DWS)表结构优化

合理的表结构可以带来稳定和高效的性能，但是反过来说，如果表结构不合理，就可能会引起比较大的性能问题或者额外的工作成本。

如何设计和优化一张表呢？GaussDB(DWS)带你一起来学习调优表的关键技巧。

了解GaussDB(DWS)表设计的六个关键要素，就能很快发现调优表的技巧。

选择存储方式

表的存储方式分为行存储和列存储。

左图直观的展示了存储方式的差异，但是建表时怎么决定用行存储还是列存储呢？

用户业务类型是决定表的存储类型的主要因素，可以根据以下业务场景选择：

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

增、删、改操作较多的场景

统计分析类查询 (关联、分组操作较多的场景)

即席查询（查询条件不确定，行存表扫描难以使用索引）

创建列存表

示例：创建表warehouse_t1，存储方式为列存通过ORIENTATION参数指定。

CREATE TABLE warehouse_t1 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) WITH (ORIENTATION = COLUMN);

选择压缩比

对于数据库，IO 相对于CPU通常都是系统的性能瓶颈，合理的压缩手段不仅能节省空间，也能减少IO，提高读取性能。

针对行存和列存，在数据入库的时候有不同的压缩比，进而入库的性能也会不同。对于I/O读写量大，CPU富足（计算相对小）的场景，选择高压缩比；反之选择低压缩比。依据此原则进行不同压缩下的测试和对比，以选择符合自身业务情况的最优压缩比。

压缩比通过COMPRESSION参数指定，其中列存表取值为：YES/NO/LOW/MIDDLE/HIGH，默认值为LOW。

选择压缩比

压缩级别

所适用的业务场景

LOW

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

MIDDLE

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

HIGH

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

创建使用压缩比的表

示例：创建列存表warehouse_t2，压缩比通过COMPRESSION参数指定。

CREATE TABLE warehouse_t2 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE);

选择分布方式和分布列

---复制表（Replication）将表中的全量数据在集群的每一个DN实例上保留一份，即每个数据节点都有完整的表数据。

---哈希（Hash）表将表中某一个或几个字段进行hash运算后，生成对应的hash值，通过映射，把数据分布到指定DN。对于Hash分布表，在读/写数据时可以利用各个节点的IO资源，大大提升表的读/写速度。

选择分布方式

可根据以下业务场景选择：

小表、维度表。

Hash分布表的分布列选取至关重要，需要满足以下原则：

列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。

在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’)，那么就应当尽量不用zqdh做分布列。

在满足前两条原则的情况，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。

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

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%以上就必须要调整分布列。

创建Replication/Hash分布表

示例：创建行存表warehouse_t3，Replication分布方式通过DISTRIBUTE BY参数指定。

CREATE TABLE warehouse_t3 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) DISTRIBUTE BY REPLICATION;

示例：创建列存表warehouse_t4，Hash分布方式通过DISTRIBUTE BY参数指定，分区键为W_WAREHOUSE_SK。

CREATE TABLE warehouse_t4 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH (W_WAREHOUSE_SK);

分区的设计

分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。

分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点：

---改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。

---增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。

---方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。

GaussDB(DWS)支持的分区表为范围分区表：将数据基于范围映射到每一个分区。这个范围是由创建分区表时指定的分区键决定的。分区键经常采用日期，例如将销售数据按照月份进行分区。

分区使用示例：

---创建分区表 CREATE TABLE demo_partition( id varchar(50), start_time timestamp without time zone ) with (orientation=column, compression=low) distribute by hash(id)Partition by range (start_time) ( partition p1 values less than ('2019-04-11 00:00:00'), partition p2 values less than ('2019-04-12 00:00:00'), partition p3 values less than ('2019-04-19 00:00:00'), partition p4 values less than (MAXVALUE) ); ---删除最大分区pmx ALTER TABLE demo_partition DROP partition pmx; ---增加分区 ALTER TABLE demo_partition add partition p4 values less than('2019-04-20 00:00:00'); ---删除某个分区中的数据 ALTER TABLE demo_partition truncate partition p1; ---查询某个分区的行数 SELECT count(1) FROM demo_partition partition(p1); ---分割某个分区（目前只有行存支持，列存不支持此功能） ALTER TABLE demo_partition split partition p3 at('2019-04-15 00:00:00') into( partition p31, partition p32 ); ---合并分区 ALTER TABLE demo_partition merge partitions p1,p2 into partition p12;

选择索引

索引可以用来提高数据查询性能，但是不恰当的使用将导致数据性能下降。建议仅在匹配如下某条原则时创建索引：

经常执行查询的字段

在连接条件上创建索引

Where子句的过滤条件上

经常出现在order by，group by和distinct字段

DWS索引支持：

Btree索引：使用一种类似B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。Btree适合支持比较查询和查询范围。但因为其本身的数据结构，当数据量非常大的时候，每条数据的入库速度会变得越来越慢，占用的内存也会越来越大，严重的影响了入库性能。

GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。

Gist适用于几何和地理等多维数据类型和集合数据类型。

Psort:针对列存表进行局部排序索引。

行存表支持的索引类型：btree（行存表缺省值）、gin、gist；列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。

索引使用示例：

---在表上创建一个索引 CREATE INDEX test-index on test using btree(vid); ---在分区表上创建分区索引 CREATE INDEX test-index on test using btree(vid) local; ---重建索引 ALTER INDEX test-index rebuild; ---设置索引不可用 ALTER INDEX test-index unusable; ---删除索引 DROP INDEX test-index;

选择局部聚簇

局部聚簇（Partial Cluster Key）是列存下的一种技术。该技术可以通过min/max稀疏索引较快的事先基表扫描的filter过滤。局部聚簇可以指定多列，但是不建议超过2列，选择原则如下：

---受基表的简单表达式约束。这种约束一般形如col op const,其中col为列名，op为操作符=，<，>，<=，>=，const为常量值。

---尽量采用选择度比较高（过滤更多数据）的简单表达式的列。

---尽量把选择度比较低的约束列放在局部聚簇中的前面。

---尽量把枚举类型的列放在局部聚簇的前面。

局部聚簇使用示例：

---创建一个带局部聚簇列的表 CREATE TABLE test(id int,volume text,partial cluster key (volume)) with (orientation=column, compression=low) distribute by hash (id); ---为普通表创建一个PCK，并使其生效 ALTER TABLE table_name add partial cluster key (column_name); VACUUM FULL table_name; ---删除表中的局部聚簇列： ALTER TABLE table_name drop constraint partial_cluster_key_name;

基于这些关键要素的掌握，您可以改进表的分配，以达到您所期望的数据加载、存储和查询方面的效果。

了解更多华为云数据仓库GaussDB(DWS)，请点击这里。

EI企业智能 云小课 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。