GreenPlum分布式数据库存储及查询处理

AiDBA宝典

发布于 2023-11-16 12:24:35

7550

发布于 2023-11-16 12:24:35

1.分布存储

Greenplum是一个分布式数据库系统，因此其所有的业务数据都是物理存放在集群的所有Segment实例数据库上；在Greenplum数据库中所有表都是分布式的，所以每一张表都会被切片，每个Segment实例数据库都会存放相应的数据片段。在下图中sale、customer、vendor、product四张表的数据都会切片存放在所有的Segment上，所有Segment实例同时工作，由于每个Segment只需要计算一部分数据，所以计算效率会大大提升。

1.1.表分布的策略-并行计算的基础

由于Greenplum是一个分布式数据库，所以建表时需要指定分布键，将数据平均分布到各个Segment上。Greenplum有两种数据分布策略：

Hash分布

当选择Hash分布策略时，可以指定表的一列或者多列组合。greenplum会根据指定的Hash key列计算每一行数据对应的Hash值，并映射到相应的segment实例。当选择的Hash key列的值唯一时，数据会均匀的分散至所有segment实例。对于分布键的选择，有以下方式及行为：

1.指定分布键，分布键可以是表的一列或者多列组合，但不建议组合分布键的列数超过两列。 2.若表中存在主键，不能指定其他单列作为唯一主键，且对于组合分布键，其中必须要包含主键，且主键必须要位于组合分布键的第一列，否则会报错。 3.若没有指定分布键，且表中没有主键及唯一键，则默认使用第一列作为分布键。 4.若没有指定分布键，且表中存在主键或唯一键（二者不能同时存在），则选择主键或唯一键作为分布键。

创建hash表：

CREATE TABLE … DISTRIBUTED BY (column [,…])

循环分布

当选择随机分布时，数据将会随机分布至segment，相同值的数据行不一定会分发至同一个segment。虽然随机分布可以确保数据平均分散至所有segment，但是在进行表关联分析时，仍然会按照关联键重分布数据，所以随机分布策略通常不是一个明智的选择（除非你的SQL只有对单表进行全局的聚合操作，即没有group by或者join等需要数据重分部的操作）。

CREATE TABLE … DISTRIBUTED RANDOMLY

在 create table 和 alter table 的时候使用 DISTRIBUTED BY(HASH 分布)或 DISTRIBUTED RANDOMLY(随机分布)来决定数据如何分布。考虑要点：

均匀的数据分布：尽量确保每个 segment 实例存储了等量的数据；尽可能使用具有唯一性的 DK，比如主键、唯一键等。
本地操作与分布式操作：确保查询的处理（关联、排序、聚合等）尽可能在每个实例的本地完成，避免数据重分布；不同表尽量使用相同DK，避免使用随机分布。
均衡的查询处理：尽可能确保每个 segment 实例能处理等量的工作负载。

声明分布键：

在创建或者修改表定义的时候指定；

如果没有指定，系统会依次考虑使用主键或第一个字段作为HASH分布的DK；

几何类型或自定义类型的列不适合作为GP的DK。

如果没有合适类型的列可以保证数据平均分布，则使用随机分布。

create table tb_dk_01(a int, b int) distributed by(b);
create table tb_dk_02(a int, b int);
create table tb_dk_03(a int primary key, b int);
create table tb_dk_04(a int, b int) distributed randomly;

1.2.Greenplum数据库表分区

分区并不会改变表数据在Segment之间的物理分布。

表分布是物理的：Greenplum数据库会在物理上把分区表和未分区表划分到多个Segment上来启用并行查询处理。
表分区是逻辑的：Greenplum数据库在逻辑上划分大表来提升查询性能并且有利于数据仓库维护任务，例如把旧数据滚出数据仓库。

一张大表逻辑性地分成多个部分，如按照分区条件进行查询，将减少数据的扫描范围，提高系统性能。提高对于特定类型数据的查询速度和性能，更方便数据库的维护和更新。

决定表的分区策略：

表是否足够大？大的事实表适合做表分区。
对目前的性能不满意？查询性能低于预期时再考虑分区。
查询条件是否能匹配分区条件？查询语句的WHERE条件是否与考虑分区的列一致
数据仓库是否需要滚动历史数据？历史数据的滚动需求也是分区设计的考虑因素
按照某个规则数据是否可以被均匀的分拆？尽量把数据均匀分拆的规则

Greenplum数据库支持：

范围分区：基于一个数字型范围划分数据，例如按照日期或价格划分。(日期范围或数字范围)/如日期、价格等
列表分区：基于一个值列表划分数据，例如按照销售范围或产品线划分。例如地区、产品等

官方分区文档：https://gp-docs-cn.github.io/docs/admin_guide/ddl/ddl-partition.html

创建分区

TABLE 只能在 CREATE TABLE 时被分区。第一步要选择分区类型(范围分区、列表分区)和分区字段

定义日期范围分区表(range分区) 使用单个 date 或者 timestamp 字段作为分区键。如果需要，还可以使用同样的字段做子分区。通过使用 START、END 和 EVERY 子句定义分区增量让 GP 自动产生分区。

CREATE TABLE tb_cp_01 (id int, date date, amt decimal(10, 2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
(
  -- (默认行为)INCLUSIVE：包含 2013-01-01；EXCLUSIVE：不包含 2014-01-01
  START (date '2013-01-01') INCLUSIVE
  END (date '2014-01-01') EXCLUSIVE 
  EVERY (INTERVAL '1 month') 
);

也可以为每个分区单独制定名称

CREATE TABLE tb_cp_02 (id int, date date, amt decimal(10, 2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( 
  PARTITION Jan13 START (date '2013-01-01') INCLUSIVE, 
  PARTITION Feb13 START (date '2013-02-01') INCLUSIVE, 
  PARTITION Mar13 START (date '2013-03-01') INCLUSIVE, 
  PARTITION Apr13 START (date '2013-04-01') INCLUSIVE, 
  PARTITION May13 START (date '2013-05-01') INCLUSIVE, 
  PARTITION Jun13 START (date '2013-06-01') INCLUSIVE, 
  PARTITION Jul13 START (date '2013-07-01') INCLUSIVE, 
  PARTITION Aug13 START (date '2013-08-01') INCLUSIVE, 
  PARTITION Sep13 START (date '2013-09-01') INCLUSIVE, 
  PARTITION Oct13 START (date '2013-10-01') INCLUSIVE, 
  PARTITION Nov13 START (date '2013-11-01') INCLUSIVE, 
  PARTITION Dec13 START (date '2013-12-01') INCLUSIVE 
  END (date '2014-01-01') EXCLUSIVE 
);

定义数字范围分区表

CREATE TABLE tb_cp_03 (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( 
  START (2010) END (2014) EVERY (1),
  DEFAULT PARTITION extra 
);

创建列表分区表(list分区) 可以使用任何数据类型的列作为分区键；可以使用多个列组合作为分区键。

CREATE TABLE tb_cp_04 (id int, rank int, year int, gender char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( 
  PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other 
);

定义多级分区表当需要子分区时，可以使用多级分区的设计。

CREATE TABLE tb_cp_05 (trans_id int, date date, amount decimal(9, 2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( 
  -- 子分区 
  SUBPARTITION usa VALUES ('usa'),
  SUBPARTITION europe VALUES ('europe'),
  DEFAULT SUBPARTITION other_regions
)
(
  -- 主分区 
  START (date '2013-09-01') INCLUSIVE
  END (date '2014-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month'),
  DEFAULT PARTITION outlying_dates 
);

创建3级子分区表，被分区为年、月、区域三层。

CREATE TABLE tb_cp_06 (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
SUBPARTITION TEMPLATE (
  -- 定义二级分区(2个 + default)  
  START (1) END (3) EVERY (1),
  DEFAULT SUBPARTITION other_months)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
  -- 定义三级分区(2个 + default) 
  SUBPARTITION usa VALUES ('usa'),
  SUBPARTITION europe VALUES ('europe'),
  DEFAULT SUBPARTITION other_regions)
( 
  -- 定义一级分区(2个 + default) 
  START (2012) END (2014) EVERY (1),
  DEFAULT PARTITION outlying_years 
);

查看分区设计

通过 pg_partitions 视图查看分区表设计情况。

SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank 
FROM pg_partitions WHERE tablename='tb_cp_05';

如下视图也可以查看分区表的信息：

查看创建 SUBPARTITION 的 pg_partition_templates
查看分区表的分区键 pg_partition_columns

维护分区表

必须使用 ALTER TABLE 命令从顶级表来维护分区。

(1) 添加新分区原分区表包含 subpartition template 设计：

ALTER TABLE tb_cp_05 DROP DEFAULT PARTITION;
ALTER TABLE tb_cp_05 ADD PARTITION START (date '2014-01-01') INCLUSIVE END (date '2014-02-01') EXCLUSIVE;

原分区不包含 subpartition template 设计：

ALTER TABLE tb_cp_05 ADD PARTITION START (date '2014-02-01') INCLUSIVE END (date '2014-03-01') EXCLUSIVE
(
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe')
);

注意：如果存在默认分区，只能从默认分区中拆分新的分区

(2) 重命名分区 GP 中的对象长度限制为 63 个字符，并且受唯一性约束。子表的名称格式：

<父表名称>_<分区层级>_prt_<分区名称>

修改父表名称，将会影响所有分区表

# 对应分区表将会改为：tbcp05_1_prt_5
ALTER TABLE tb_cp_05 rename to tbcp05;

只修改分区名称：

# 对应分区表将会改为：tbcp05_1_prt_jun13
ALTER TABLE tbcp05 RENAME PARTITION FOR('2013-06-01') TO Jun13;

(3) 删除分区

# 删除指定的分区
ALTER TABLE tb_cp_04 DROP PARTITION other;

# 删除默认分区：
ALTER TABLE tb_cp_04 DROP DEFAULT PARTITION; 

# 对于多级分区表，为同一层每一个分区删除默认分区：
ALTER TABLE tb_cp_06 ALTER PARTITION FOR (RANK(1)) DROP DEFAULT PARTITION;
ALTER TABLE tb_cp_06 ALTER PARTITION FOR (RANK(2)) DROP DEFAULT PARTITION;

(4) 添加默认分区

# 使用ALTER TABLE命令添加默认分区：
ALTER TABLE tbcp05 ADD DEFAULT PARTITION other;

# 如果是多级分区表，同一层每个分区都需要默认分区：
ALTER TABLE tb_cp_06 ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other;
ALTER TABLE tb_cp_06 ALTER PARTITION FOR (RANK(2)) ADD DEFAULT PARTITION other;

(5) 清空分区数据

# 使用ALTER TABLE命令来清空分区。
ALTER TABLE tbcp05 TRUNCATE PARTITION FOR (RANK(1));

(6) 交换分区交换分区是用一个普通的 TABLE 与现有的分区交换身份。使用 ALTER TABLE 命令来交换分区。只能交换最低层次的分区表。

CREATE TABLE jan13(LIKE tb_cp_02) WITH(appendonly=true);
INSERT INTO jan13 VALUES(1,'2013-01-15',123.45);
ALTER TABLE tb_cp_02 EXCHANGE PARTITION for(date '2013-01-01') WITH TABLE jan13;

(7) 拆分分区使用 ALTER TABLE 命令将现有的一个分区拆分成两个。例如：将一个月分区数据拆分到一个1-15日的分区和另一个16-31日的分区

ALTER TABLE tb_cp_02 SPLIT PARTITION FOR('2013-01-01') AT ('2013-01-16')
    INTO (PARTITION jan131to15, PARTITION jan0816to31);

如果分区表有默认分区，要添加新分区只能从默认分区拆分：

ALTER TABLE tb_cp_03 SPLIT DEFAULT PARTITION
START (2014) INCLUSIVE END (2015) EXCLUSIVE
INTO (PARTITION y2014, DEFAULT PARTITION);

(8) 修改子分区模板使用 ALTER TABLE SET SUBPARTITION TEMPLATE 命令来修改现在分区表的子分区模板。例如：

ALTER TABLE tb_cp_05 SET SUBPARTITION TEMPLATE
(
    SUBPARTITION usa VALUES('usa'),
    SUBPARTITION africa VALUES('africa'),
    DEFAULT SUBPARTITION other
);

使用新模板后为表 tb_cp_05 添加一个分区，

ALTER TABLE tb_cp_05 ADD PARTITION Feb14 START ('2014-02-01') INCLUSIVE END('2014-03-01') EXCLUSIVE;

装载分区表

分区表中顶级表是空的，数据存储在最底层的表中。
为避免数据装载失败，可选择定义默认分区。
查询分区表时，默认分区总是会被扫描，如果默认分区包含数据，会影响查询效率。
在使用 COPY 或者 INSERT 向父级表装载数据时，数据会自动路由到正确的分区。
可考虑交换分区的方法直接转载数据到子表，提高性能。

验证分区策略

# EXPLAIN查看查询计划是否扫描了相关分区
EXPLAIN SELECT * FROM tb_cp_05 WHERE date=‘2013-12-01’ AND region=‘usa’;

分区选择性扫描的限制

如果查询计划显示分区表没有被选择性的扫描，可能和以下的限制有关：

查询计划仅可以对稳定的比较运算符，如：=, <, <=, >, >=, <>
查询计划不识别非稳定函数来执行选择性扫描。比如，WHERE 子句中使用如 date>CURRENT_DATE 会使查询计划执行分区扫描，而 time>TIMEOFDAY 不会。

1.3.数据存储方式

Greenplum有两种数据存储方式，比如是行存储，还是列存储，是普通的heap表，还是append optimized表。

行存储是行为单位存储数据，一行中越是靠后的列，那么查询需要的cost相对越大，这个以前oracle做过相应比较，都是一样的道理，行存储更适合OLTP的系统。
列存储是以列为单位存储数据，物理上一列会对应一个或者多个数据文件，而且列存储的压缩比比较高，但是如果查询的时候，如果返回的列很多，那么效率不如行存储，列存储更适合对某一列做相关统计，列存储更适合OLAP的系统。
堆表，我们普通的创建的表默认都是堆表，适合频繁的更新删除操作的小表，适合OLTP系统。
AO表，适合批量数据写入，不适合单行的insert,适合大表使用，所以一般用在数据仓库系统，适合OLAP系统。

# 创建一个heap表
CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);

# 不带压缩的ao表
CREATE TABLE bar (a int, b text) 
    WITH (appendonly=true)
    DISTRIBUTED BY (a);

# 创建列存储表
CREATE TABLE bar (a int, b text) 
    WITH (appendonly=true, orientation=column)
    DISTRIBUTED BY (a);

1.3.1.行存储与列存储

选择行存储(Row-Orientation)或列存储(Column-Orientation)，考虑因素：

表数据的更新：只能选择行存储。
如果经常有数据被 INSERT：考虑选择行存储。
查询设计的列数量：如果在 SELECT 或 WHERE 中涉及表的全部或大部分列时，考虑行存储。列存储适用于在 WHERE 或 HAVING 中队单列作聚合操作：

SELECT SUM(salary)
SELECT AVG(salary)…WHERE salary>10000

或在 WHERE 条件中使用单个列条件且返回少量的行使用压缩存储

SELECT salary, dept…WHERE state=‘CA’

表的列数量：行存储对于列多或行尺寸相对小的表更高效；列存储在只访问宽表的少量列的查询中性能更高。
压缩：列存储表具有压缩优势。

# 创建列存储时，只能为追加(Append-Only)存储
create table tb_col_01(a int, b text) with (appendonly=true, orientation=column) distributed by (a);

1.3.2.压缩存储(只支持Append-only表)

两种压缩方式：表级压缩和列级压缩。

(1) 选择压缩方式和级别的考虑因素：

CPU性能
压缩比
压缩速度
解压速度或查询效率

应保证不会显著提高压缩时间和查询效率的前提下最有效的压缩减少数据尺寸。ZLIB 压缩率高于 QUICKLZ，但速度较低。

QUICKLZ 只有一种压缩级别，即没有 compresslevel 参数，而 ZLIB 有 1-9 可选。

create table tb_zlib_01(a int, b text) with (appendonly=true, compresstype=zlib, compresslevel=5);

(2) 检查 AO 表的压缩和分布情况：

# 查看表的分布情况，只能用于Append-Only表
select get_ao_distribution('表名');

# 查看表的压缩率，只能用于Append-Only表
select get_ao_compression_ratio('表名');
select gp_segment_id, count(1) from 表名 group by 1;

(3) 参数

COMPRESSTYPE：ZLIB(更高压缩率)、QUICKLZ(更快压缩)、RLE_TYPE(运行长度编码)、none(无压缩、缺省)
COMPRESSLEVEL：ZLIB为1-9级可选，1级最低，9级最高；QUICKLZ仅1级压缩可选；RLE_TYPE为1-4级可选，1级快但压缩率低，4级较慢但压缩率高
BLOCKSIZE：8K~2M

(4) 压缩设置的优先级在越低级别的设置具有越高的优先级：

子分区的列压缩设置将覆盖分区、列和表级的设置
分区的列压缩设置将覆盖列和表级的设置
列的压缩设置将覆盖整个表级的设置

注意：存储设置不可以被继承

create table tb_t3 (
    c1 int encoding(compresstype=zlib),
    c2 text,
    c3 text encoding(compresstype=rle_type),
    c4 smallint encoding(compresstype=none),
    defalut column encoding(compresstype=quicklz, blocksize=65536)
)
with(appendonly=true, orientation=column)
partition by range(c3)
(
    start('2010-01-01'::date) end('2010-12-31'::date), 
    column c3 encoding(compresstype=zlib)
);

1.4.数据类型

对于字符类型，多数选择 TEXT 或者 VARCHAR
对于数值类型，尽量选择更小的数据类型
对于打算用作连接的列，选择相同的数据类型

create table tb_products(
    -- 1. 主键约束，唯一约束和非空约束的综合体。默认成为 DK(Distributed Key)
    id integer integer PRIMARY KEY,
    -- 2. 唯一约束，确保字段的数据在表中唯一
    product_no integer UNIQUE, 
    -- 3. 非空约束，不可以存在空值
    name text  NOT NULL, 
    -- 4. 检查约束，过制定数据必须满足一个布尔表达式来约束
    price numeric CHECK(price>0)
    -- 5. 外键约束，GPDB 目前不支持。
);

注意：主键约束与唯一约束只有出现一个。

2.查询规划和分发

用户像对任何数据库管理系统那样将查询发送到Greenplum数据库。它们使用psql之类的客户端应用连接到Greenplum的Master主机上的数据库实例并且提交SQL语句。

Master接收、解析并且优化查询。作为结果的查询计划可能是并行的或者定向的。如图1所示，Master会把并行查询计划分发到所有的Segment。 Master会把定向查询计划分发到单一的一个Segment。每个Segment负责在其自己的数据集上执行本地数据库操作。大部分的数据库操作（例如表扫描、连接、聚集和排序）都会以并行的方式在所有Segment上执行。在一个Segment的数据库上执行的每个操作都独立于存储在其他Segment数据库中的数据。

某些查询可能只访问单个Segment上的数据，例如单行的INSERT、UPDATE、DELETE或者SELECT操作或者以表分布键列过滤的查询。在这些查询中，查询计划不会被分发到所有的Segment，而是定向给到包含受影响或者相关行的Segment。

3.查询计划

查询计划是Greenplum数据库将要执行以产生查询答案的操作集合。计划中的每个节点或者步骤表示一个数据库操作，例如表扫描、连接、聚集或者排序。计划的读取和执行按照从底向上的顺序进行。

除通常的数据库操作（例如表扫描、连接等等）之外，Greenplum数据库还有一种额外的被称为移动的操作类型。移动操作涉及到在查询处理期间在Segment之间移动元组。注意并非每一个查询都需要移动操作。例如，定向查询计划就不需要通过Interconnect移动数据。

为了在查询执行期间达到最大并行度，Greenplum将查询计划的工作划分成切片。切片是Segment能够在其上独立工作的计划片段。只要有一个移动操作出现在计划中，该查询计划就会被切片，在移动的两端分别有一个切片。

SELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE date=04302008;

每个Segment接收一份查询计划的拷贝并且并行地根据计划工作。

这个例子的查询计划有一个重分布移动，它在Segment之间移动元组以完成连接。重分布移动是必要的，因为customer表在Segment上按照cust_id分布，而sales表是按照sale_id分布。为了执行该连接，sales元组必须按照cust_id重新分布。该计划在重分布移动操作的两边被切换，形成了slice 1和slice 2。

这个查询计划由另一种称为收集移动的移动操作。收集操作表示Segment何时将结果发回给Master，Master再将结果呈现给客户端。由于只要有移动产生查询计划就会被切片，这个计划在其最顶层也有一个隐式的切片（slice 3）。不是所有的查询计划都涉及收集移动。例如，一个CREATE TABLE x AS SELECT…语句不会有收集移动，因为元组都被发送到新创建的表而不是发给Master。

4.并行查询及执行

Greenplum会创建若干数据库进程来处理查询的工作。在Master上，查询工作者进程被称作查询分发器（QD）。QD负责创建并且分发查询计划。它也收集并且表达最终的结果。在Segment上，查询工作者进程被称为查询执行器（QE）。QE负责完成它那一部分的工作并且与其他工作者进程交流它的中间结果。

对查询计划的每一个切片至少要分配一个工作者进程。工作者进程独立地工作在分配给它的那部分查询计划上。在查询执行期间，每个Segment将有若干进程并行地为该查询工作。

为查询计划的同一个切片工作但位于不同Segment上的相关进程被称作团伙。随着部分工作的完成，元组会从一个进程团伙流向查询计划中的下一个团伙。这种Segment之间的进程间通信被称作Greenplum数据库的Interconnect组件。

下图为一个简单SQL语句，从两张表中找到2008年的销售数据。图中右边是这个SQL的查询计划。从生成的查询计划树中看到有三种不同的颜色，颜色相同表示做同一件事情，我们称之为分片/切片（Slice）。最下层的橙色切片中有一个重分发节点，这个节点将本节点的数据重新分发到其他节点上。中间绿色切片表示分布式数据关联（HashJoin）。最上面切片负责将各个数据节点收到的数据进行汇总。

上图和下图所示查询计划在Master和两个Segment实例上的查询工作者进行。

然后看看这个查询计划的执行。主节点（Master）上的调度器（QD）会下发查询任务到每个数据节点，数据节点收到任务后（查询计划树），创建工作进程（QE）执行任务。如果需要跨节点数据交换（例如上面的HashJoin），则数据节点上会创建多个工作进程协调执行任务。不同节点上执行同一任务（查询计划中的切片）的进程组成一个团伙（Gang）。数据从下往上流动，最终Master返回给客户端。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-11-14，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库