金仓分析型数据库KADB使用建议

• 产品介绍

1. 架构

KADB的数据库服务器部分采用MPP+无共享式的分布式架构。集群组件包括主控（Master）节点以及计算（Segment）节点。

主控（Master）节点负责接收应用连接、为应用的请求生成执行计划并将计划分派给计算（Segment）节点执行并将执行结果汇总返回给应用。

计算（Segment）节点是独立的数据存储和查询执行引擎，用户数据以分片的方式存储在计算（Segment）节点上。计算（Segment）节点会执行主控节点分派的计算任务和将本节点的数据分发给需要数据的其他计算节点。

1. 功能

1. 数据存储模型

支持行存储和列存储，针对于不同的应用场景，用户可以自行选择行存表和列存表对数据进行存储，保证插入或查询性能。对于某些特定的应用场景，也可以使用行列混存的方式，将活跃的交互数据保存在行存分区表中（比如近1年的数据），而非活跃的仓库数据保存在列存分区表中（历史数据），空间的使用和性能的优化都能达到需求。

1. 数据分区

支持多种表分布方式和分区方式，分布包括hash分布、随机分布和复制分布，分区包括list、range及混合分区，减少因数据重分布和数据扫描带来的网络以及磁盘的开销。

范围分区

create table t_partition_range

(

    id int,

    name varchar(64),

    fdate varchar(64)

    ) distributed by (id)

    partition by range(fdate)

    (

        partition p1 start ('2022-01-01') inclusive end ('2022-02-01') exclusive,

        partition p2 start ('2022-02-01') inclusive end ('2022-03-01') exclusive,

        default partition default_p

    );

或者

create table t_partition_every_1

(

    id int,

    name varchar(64),

    fdate date

)

distributed by (id)

partition by range (fdate)

(

    partition pn_ start ('2022-01-01'::date) end ('2022-12-31'::date) every ('1 day'::interval),

    default partition default_p

);

列表分区：

create table t_partition_list

(

    id int,

    name varchar(64),

    fdate varchar(10)

)

distributed by (id)

partition by list (fdate)

(

    partition p1 values ('2022-01-01', '2017-01-02'),

    partition p2 values ('2022-01-03'),

    default partition default_p

);

1. 可靠性

KADB通过副本的冗余机制来保证高可用，每个节点上的数据都会有一份冗余，在解析主节点日志后通过基于日志的数据同步方式，保证主控节点和数据节点主备之间的数据一致。如果相关主节点出现故障，备节自动接替主节点继续提供服务，无需人工干预。同时，通过接口重试和重连的方式解决在主控节点切换过程中连接丢失的问题，实现业务对集群故障切换无感知的效果，当数据库集群整体不可用时，通过接口数据本地备份的方式保证数据不丢。

• 使用建议

1. 数据模型

MPP数据库是一种shared nothing的分析型MPP数据库。这种模型与高度规范化的/事务型 的SMP数据库有显著区别。正因为如此，推荐以下几项最佳实践。

MPP数据库使用非规范化的模式设计会工作得最好，非规范化的模式适合于MPP分析型处理，例如

1. 带有大型事实表和较小维度表的星形模式或者雪花模式。

事实表（Fact Table）主要记录具体业务过程中的数据或指标，例如销售订单、客户订购量、销售金额、利润等。事实表一般包含大量的数据，数据会随时间不断地增长，并支持各种不同的计算方式，如求和、平均值等。事实表中的数据可以与维度表进行关联，生成具有多维度决策分析的数据模型，并支持灵活的查询方式，如按照时间维度、地点维度、产品维度等进行数据切割和聚合。例如，在销售业务中，事实表可能包括订单表、交易表、发货表、客户反馈表等。【存储有事实记录的表】

维度表（Dimension Table）是描述业务事实的背景信息的表，主要记录业务事实中的各类维度属性，如时间、地点、产品、客户以及其他特定业务领域的信息。维度表一般具有相对稳定的数据内容，其中的数据不会经常改变。例如，在销售业务中，维度表可以包括时间维度表、地点维度表、产品维度表和客户维度表等。维度表能够为业务分析提供支持，有助于实现数据的多维分析和数据挖掘。

星型模型：是一种多维的数据关系，它由一个事实表（Fact Table）和一组维表（Dimension Table）组成。每个维表都有一个维作为主键，所有这些维的主键组合成事实表的主键。事实表的非主键属性称为事实（Fact），它们一般都是数值或其他可以进行计算的数据。星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，所以数据有一定的冗余

雪花型模型：当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的事实表，形成一些局部的 "层次 " 区域，这些被分解的表都连接到主维度表而不是事实表。通过最大限度地减少数据存储量以及联合较小的维表来改善查询性能。雪花型结构去除了数据冗余。

根据经验一般建议使用星型模型。因为在实际项目中，往往最关注的是查询性能问题，至于磁盘空间一般都不是问题。

1. 表之间用于连接(join)的列采用相同的数据类型。[沐浴阳光1] 避免产生查询效率问题

例如：表good和orange分别在字段id和good_type上进行关联查询：

表结构如下：

1. 存储模型

1. 堆表VS追加优化表

堆存储（行存）是默认存储模型，并且是KADB为所有数据库表使用的模型。行存储是行为单位存储数据，一行中越是靠后的列，那么查询需要的开销相对越大，行存储更适合OLTP的系统。如果表上的操作是频繁的UPDATE、DELETE以及单个INSERT操作，建议使用堆表作为表的存储方式。列存储是以列为单位存储数据，物理上一列会对应一个或者多个数据文件，而且列存储的压缩比比较高，但是如果查询的时候，如果返回的列很多，那么效率不如行存储，列存储更适合对某一列做相关统计，列存储更适合OLAP的系统。如果表上的操作是并发 UPDATE、DELETE以及INSERT操作，建议表和分区使用列存储。[沐浴阳光2] 

为初始装载后就很少被更新并且后续只会以批操作执行插入的表和分区使用追加优化存储。不要在追加优化表上执行单个INSERT、UPDATE或者DELETE操作。在追加优化表上建议使用并发的批量INSERT操作，但是不要执行并发的批量UPDATE 或者DELETE操作。[沐浴阳光3] 

追加优化表中被更新和删除的行所占用的空间不会像堆表那样被有效地回收及重用，因此追加优化存储模型不适合于频繁更新的表。它的设计目标是用于一次装载、很少更新且频繁进行分析查询处理的大型表。

1. 经常进行反复的批量或单一UPDATE，DELETE和INSERT 操作的表或分区使用堆存储。
2. 经常进行并发UPDATE，DELETE和INSERT的表或分区使用堆存储。
3. 对于在初始装载后很少更新并且只会在大型批处理操作中进行后续插入的表和分区，使用追加优化存储。
4. 不在追加优化表上执行单个INSERT，UPDATE或 DELETE操作。
5. 不在追加优化表上执行并发的批量UPDATE或DELETE操作。可以执行并发的批量INSERT操作。

1. 行存还是列存

按行存储数据是传统的存储数据库元组的方式。组成一行的列被连续地存储在磁盘上，因此整个行可以被以单次I/O的形式从磁盘上读出。

面向列的方式把列值在磁盘上存在一起。对每一列都会创建一个单独的文件。如果表被分区，则会对每个列和分区的组合创建一个单独的文件。当一个查询在一个有很多列的列存表中访问少量列时，I/O代价会比行存表要减少很多，因为不必从磁盘上检索没有被引用的列。例如：在分析场景下，事实表一般记录对象的基本信息，有几十个属性，是个宽表（车牌号码、车型、发动机号码、底盘号码、使用单位、责任人、加油卡号、保险公司、路桥费缴纳日期、二保时间、车类型、上传照片），我们每次对车辆信息进行分析时，会考虑车辆信息事实表和省份这个维度进行关联分析，查询在某一省份中的车牌号码情况。在这种查询下，我们只关系车辆事实表的‘车牌号码’这一列的数据，而不需要把车辆的所有信息都从数据库中提取出来。通过列存的方式就可以大大减少从磁盘读取数据的I/O，从而提高查询效率。

对于包含要求更新并且频繁执行插入的事务的交易型负载，推荐使用面向行的存储。当对表的选择很宽（即查询中需要单个行的很多列）时，应该使用面向行的存储。如果大部分列出现在查询的SELECT 列表或者WHERE子句中，请使用行存储。对一般目的或者混合负载使用面向行的存储，因为它能提供灵活性和性能的最佳组合。以银行储户的交易操作流水表为例，每天有大量交易记录需要记录，并且储户的账户余额频繁会发生改变，这种情况下推荐使用行存表。每次对储户交易信息的查询需要查询的表的列很多，比如：储户id、交易时间、交易金额、交易对象等等，这些信息都需要一同查询出来，在这种情况下建议储户信息采用行存的方式。

面向列的存储是为了读操作而优化，但它并未对写操作优化，一行的列值必须被写入到磁盘上的不同位置。对于有很多列的大型表，当查询中只访问列的一个小集合时，列存表可以提供最优查询性能。列存表的每一列对于操作系统的一个存储文件，每一个存储文件可以认为是顺序在磁盘上保存的，顺序数据的读取比磁盘的随机读取效率要高。如果一个列存表有几十个列，那么表在磁盘上就会对应几十个数据文件，如果我们查询时只选择部分的表的列，那么不涉及查询列的那些数据文件就不必从磁盘上访问了，从减少查询数据量这个层面上看，可以大大提高查询效率。

列存的另一个好处是，同一种数据类型的值集合可以用比混合类型值集合更少的空间存储在一起，因此列存表比行存表使用的磁盘空间更少（进而导致需要更少的磁盘I/O）。列存表的压缩效果也比行存表更好。因为相同数据类型的数据，其在数据格式，数据存储方式等方面的特性是一致的，那么就可以对应选择相应的压缩算法，对表的每一列可以选择不同的压缩算法达到最优的压缩比。因为不同的压缩算法对不同的数据类型其压缩效率是不一致的，有的压缩算法适合压缩数值类型的数据，有的压缩算法适合压缩字符类型的数据

对于数据仓库的分析型负载，其中的选择很窄或者在少量列上计算数据聚集，请使用面向列的存储。对于定期更新单个列但不修改行中其他列的表，使用面向列的存储。在一个很宽的列存表中读取一个完整的行比在行存表中读取同样一行需要更多时间。有必要理解每个列都是MPP数据库中每个segment上一个单独的物理文件。

1. 如果负载中有要求更新并且频繁执行插入的迭代事务，则对这种负载使用行存。
2. 在对宽表选择时使用行存。
3. 为一般目的或混合负载使用行存。
4. 选择面很窄（很少的列）和在少量列上计算数据聚集时使用列存。
5. 如果表中有单个列定期被更新而不修改行中的其他列，则对这种表使用列存。

创建普通heap表

create table student(

    id character varing(30),

    xm character varing(20),

    xh character varing(20),

    bj character varing(20)

)

distributed by (id);

创建AO表

create table tmp_001(

                month_id numeric(8),

                serv_id numeric(30),

                cust_id numeric(30)

                )

with (

appendonly=true,compresslevel=5,orientation=column,     compresstype=zlib,oids=false

                 )

distributed by (month_id,serv_id)

或者使用全局参数：

gpconfig -c ‘gp_default_storage_options’ -v ‘appendonly=true, orientation=column, BLOCKSIZE=32768, COMPRESSTYPE= ZLIB, COMPRESSLEVEL= 5'

1. 数据分布

能让数据被均匀分布的最优分布方式是MPP数据库使用过程中的一个重要因素。在一个MPP无共享环境中，一个查询的总体响应时间由所有segment的完成时间度量。整个系统的响应速度和最慢的segment正相关。如果数据发生倾斜，拥有更多数据的segment将需要更多时间完成，因此每一个segment必须有大约相同数据量的行并且执行大概相同量级的处理。如果一个Segment比其他segment有明显更多的数据要处理，将会导致糟糕的性能和内存不足的情况。

1. 为所有的表明确定义一个分布列或者随机分布。不要使用默认分布。
2. 理想情况下，使用单个将数据在所有Segment之间均匀分布的列作为分布列。
3. 不要将查询的WHERE子句中将要使用的列作为分布列。
4. 不要在日期或者时间戳上分布。
5. 分布键列数据应该含有唯一值或者非常高的可辨别性。
6. 如果单个列无法实现均匀分布，则使用多列分布键，但不要超过两列。额外的列值通常不会得到更[沐浴阳光4] 均匀的分布，而且它们要求额外的哈希处理时间。
7. 如果两个列的分布键无法实现数据的均匀分布，则使用随机分布。[沐浴阳光5] 

1. 索引

在MPP数据库中通常不需要索引。大部分分析型查询会在大体量数据上操作，而索引是用于从多行数据中定位某一行或某几行。在MPP数据库中，顺序扫描是一种读取数据的有效方法，因为每个segment都含有数据同等大小的一部分并且所有的segment都并行工作以读取数据。

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

对于具有高选择性的查询，索引可能会提升查询性能。对于选择性查询所要求的高基数表，在一个列式表的单列上 创建用于钻透目的的索引。[沐浴阳光6] 

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

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

带有谓词的索引会创建一个部分索引，它可以被用来从大型表中选择少量行[沐浴阳光7] 。

CREATE INDEX idx_customer_inactive

ON customer(active)

WHERE active = 0;

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

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

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

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

位图索引适合于查询但不适合于更新。当列具有较低的基数（100到100,000个唯一值）时位图索引表现得最好。

不要为唯一列、基数非常高或者非常低的数据使用位图索引。不要为事务性负载使用位图索引。

1. 装载数据

带列值的INSERT语句

带有值的单个INSERT语句会向表中加入一行。这个行会流过master并且被分布到一个segment上。 这是最慢的方法并且不适合装载大量数据。

COPY语句

KADB的COPY语句从外部文件拷贝数据到数据表中。它比INSERT 语句插入多行的效率更高，但是行仍需流过master。所有数据都在一个命令中被拷贝，它并不是一种并行处理。

COPY命令的数据输入来自于一个文件或者标准输入。例如：

COPY table FROM '/data/mydata.csv' WITH CSV HEADER;

使用COPY适合于增加相对较小的数据集合（例如多达上万行的维度表）或者一次性数据装载。

在编写脚本处理装载少于1万行的少量数据时使用COPY。

因为COPY是一个单一命令，在使用这种方法填充表时没有必要禁用自动提交。使用者可以运行多个并发的COPY命令以提高性能。

外部表

外部表提供了对MPP数据库之外的数据来源的访问。可以用SELECT语句访问它们，外部表 通常被用于抽取、装载、转换（ELT）模式，这是一种抽取、转换、装载（ETL）模式的变种，这种模式可以利用 MPP数据库的快速并行数据装载能力。

通过ETL，数据被从其来源抽取，在数据库外部使用外部转换工具（Informatica或者Datastage）转换，然后被装载到数据库中。

通过ELT，MPP外部表提供对外部来源中数据的访问，外部来源可以是只读文件（例如文本、CSV或者XML文件）、 Web服务器、Hadoop文件系统、可执行的OS程序或者MPP gpfdist文件服务器。外部表支持选择、排序和连接这样的SQL操作，这样数据可以被同时装载和转换，或者被装载到一个 装载表并且在数据库内被转换成目标表。

外部表使用CREATE EXTERNAL TABLE语句定义，该语句有一个LOCATION 子句定义数据的位置以及一个FORMAT子句定义源数据的格式，这样系统才能够解析输入数据。 文件使用file://协议，并且文件必须位于一台segment主机上由MPP超级用户可访问 的位置。数据可以被分散在segment主机上，并且每台主机上的每个主segment有不超过一个文件。LOCATION 子句中列出的文件的数量是将并行读取该外部表的segment的数量。

---