GreenPlum教程FG006-GreenPlum分布键与分区键设计实战

本文档风哥主要介绍GreenPlum分布键与分区键设计，包括分布键概念、分区键概念、分布策略、分布键选择原则、分区键设计原则、数据倾斜避免、分布键创建、分区键创建、数据分布检查、订单表设计案例、日志表设计案例、数据倾斜修复案例等内容，风哥教程参考GreenPlum官方文档DDL Guide、Data Distribution等内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 GreenPlum分布键概念

分布键是GreenPlum数据库中用于决定数据如何在各个Segment节点之间分布的关键字段。通过合理选择分布键，可以确保数据均匀分布，充分利用MPP架构的并行处理能力。更多视频教程www.fgedu.net.cn

分布键的作用：

决定数据在Segment节点间的分布
影响查询性能和数据倾斜
影响Join操作的效率
影响数据加载和维护效率

1.1.1 分布键的工作原理

GreenPlum分布键工作原理：

1. 数据插入时
– 根据分布键的值计算哈希值
– 根据哈希值确定目标Segment
– 将数据分发到对应Segment

2. 查询时
– 根据查询条件中的分布键
– 快速定位到相关Segment
– 减少数据移动，提高查询效率

3. Join操作时
– 如果两个表使用相同的分布键
– Join操作在本地Segment完成
– 避免跨节点数据传输

分布键示例：
CREATE TABLE fgedu.fgedu_orders (
id INT,
order_date DATE,
customer_id INT,
amount NUMERIC(18,2)
) DISTRIBUTED BY (customer_id);

1.2 GreenPlum分区键概念

分区键是GreenPlum数据库中用于在单个表内部按逻辑分组的关键字段。通过分区，可以将大表分割成更小、更易管理的物理部分，提高查询性能和维护效率。学习交流加群风哥微信: itpux-com

1.2.1 分区的类型

GreenPlum支持的分区类型：

1. 范围分区（Range Partitioning）
– 按数值范围分区
– 常用于日期、时间、数值等
– 示例：按月份、季度、年份分区

2. 列表分区（List Partitioning）
– 按指定值列表分区
– 常用于枚举类型
– 示例：按地区、部门分区

3. 组合分区（Composite Partitioning）
– 先按范围分区，再按列表分区
– 或先按列表分区，再按范围分区
– 适用于复杂的业务场景

范围分区示例：
CREATE TABLE fgedu.fgedu_sales (
id INT,
sale_date DATE,
region VARCHAR(50),
amount NUMERIC(18,2)
) DISTRIBUTED BY (id)
PARTITION BY RANGE (sale_date)
(
START (date ‘2024-01-01’)
END (date ‘2025-01-01’)
EVERY (INTERVAL ‘1 month’)
);

1.3 GreenPlum分布策略

GreenPlum提供了多种数据分布策略，根据不同的业务场景选择合适的分布策略。学习交流加群风哥QQ113257174

1.3.1 哈希分布（Hash Distribution）

哈希分布特点：

1. 工作原理
– 根据分布键计算哈希值
– 根据哈希值将数据分配到Segment
– 数据分布相对均匀

2. 适用场景
– 数据量大的表
– 需要Join的表
– 需要均匀分布的表

3. 示例
CREATE TABLE fgedu.fgedu_orders (
id INT,
order_date DATE,
customer_id INT
) DISTRIBUTED BY (customer_id);

1.3.2 随机分布（Random Distribution）

随机分布特点：

1. 工作原理
– 不指定分布键
– 数据随机分配到各个Segment
– 数据分布完全均匀

2. 适用场景
– 小表
– 没有明显分布键的表
– Replicated表之外的选择

3. 示例
CREATE TABLE fgedu.fgedu_config (
id INT,
config_key VARCHAR(100),
config_value VARCHAR(500)
) DISTRIBUTED RANDOMLY;

1.3.3 复制分布（Replicated Distribution）

复制分布特点：

1. 工作原理
– 表的完整副本存储在所有Segment
– 查询时无需跨节点数据传输
– 占用较多存储空间

2. 适用场景
– 小表（几MB以内）
– 维度表
– 经常Join的小表
– 配置表

3. 示例
CREATE TABLE fgedu.fgedu_dim_region (
region_id INT,
region_name VARCHAR(50)
) DISTRIBUTED REPLICATED;

Part02-生产环境规划与建议

2.1 GreenPlum分布键选择原则

2.1.1 分布键选择标准

风哥提示：分布键选择应遵循以下标准：

选择基数高的字段（不同值多）
选择查询中常用的过滤条件
选择Join操作中常用的字段
避免选择NULL值多的字段
避免选择更新频繁的字段

2.1.2 好的分布键示例

好的分布键示例：

1. 用户ID（customer_id）
– 基数高，每个用户唯一
– 查询和Join常用
– 数据分布均匀

2. 订单ID（order_id）
– 唯一标识符
– 基数高
– 适合大表分布

3. 日期+用户ID复合键
– 适合时间序列数据
– 提高查询效率
– 示例：DISTRIBUTED BY (sale_date, customer_id)

CREATE TABLE fgedu.fgedu_good_dist (
id INT,
customer_id INT,
order_date DATE,
amount NUMERIC(18,2)
) DISTRIBUTED BY (customer_id);

2.1.3 坏的分布键示例

坏的分布键示例：

1. 性别（gender）
– 基数低（只有男/女）
– 数据严重倾斜
– 不适合作为分布键

2. 状态（status）
– 基数低（只有几个状态）
– 数据分布不均匀
– 不适合作为分布键

3. 经常更新的字段
– 导致数据重新分布
– 影响性能
– 增加维护成本

4. NULL值多的字段
– 所有NULL值集中在一个Segment
– 严重数据倾斜
– 不适合作为分布键

CREATE TABLE fgedu.fgedu_bad_dist (
id INT,
gender VARCHAR(10),
status VARCHAR(20),
amount NUMERIC(18,2)
) DISTRIBUTED BY (gender);

2.2 GreenPlum分区键设计原则

2.2.1 分区键选择标准

分区键选择标准：

1. 选择查询中常用的过滤条件
– 提高分区裁剪效率
– 减少扫描的数据量

2. 选择有规律的数据
– 日期、时间
– 地区、部门
– 数值范围

3. 分区大小适中
– 每个分区10GB-100GB
– 避免分区过多或过少
– 便于管理和维护

4. 考虑业务需求
– 数据保留策略
– 历史数据归档
– 查询模式分析

分区键示例：
CREATE TABLE fgedu.fgedu_sales_part (
id INT,
sale_date DATE,
region VARCHAR(50),
amount NUMERIC(18,2)
) DISTRIBUTED BY (id)
PARTITION BY RANGE (sale_date)
(
START (date ‘2024-01-01’)
END (date ‘2025-01-01’)
EVERY (INTERVAL ‘1 month’)
);

2.2.2 分区策略建议

分区策略建议：

1. 按时间分区
– 月度分区：适合1-2年数据
– 季度分区：适合2-5年数据
– 年度分区：适合5年以上数据

2. 按业务周期分区
– 工作日/周末分区
– 业务高峰/低峰分区
– 促销活动期间分区

3. 分区数量控制
– 建议分区数：12-100个
– 避免分区过多（管理复杂）
– 避免分区过少（效果不明显）

4. 分区维护
– 定期添加新分区
– 定期删除旧分区
– 定期归档历史分区

2.3 GreenPlum数据倾斜避免

2.3.1 数据倾斜的原因

数据倾斜的常见原因：

1. 分布键选择不当
– 基数低的字段
– NULL值多的字段
– 更新频繁的字段

2. 业务数据特性
– 某些用户数据特别多
– 某些地区数据特别多
– 某些时间段数据特别多

3. 数据加载问题
– 批量加载时数据集中
– 导入过程中的数据偏差
– ETL过程中的数据倾斜

数据倾斜示例：
– 1个Segment存储了80%的数据
– 其他Segment只存储了20%的数据
– 查询时负载严重不均
– 性能大幅下降

2.3.2 数据倾斜的影响

风哥提示：数据倾斜会带来以下影响：

查询性能严重下降
某些Segment负载过高
存储空间浪费
备份恢复时间长
集群扩容困难

Part03-生产环境项目实施方案

3.1 GreenPlum分布键创建实战

3.1.1 创建哈希分布表

# 连接数据库
$ psql -d fgedudb -U fgedu
psql (9.4.26)
Type “help” for help.

fgedudb=>

# 创建哈希分布表
fgedudb=> CREATE TABLE fgedu.fgedu_orders_hash (
id INT,
order_date DATE,
customer_id INT,
product_id INT,
amount NUMERIC(18,2)
) DISTRIBUTED BY (customer_id);
CREATE TABLE

# 创建复合分布键表
fgedudb=> CREATE TABLE fgedu.fgedu_sales_compound (
id INT,
sale_date DATE,
region VARCHAR(50),
customer_id INT,
amount NUMERIC(18,2)
) DISTRIBUTED BY (sale_date, customer_id);
CREATE TABLE

3.1.2 创建随机分布表

# 创建随机分布表
fgedudb=> CREATE TABLE fgedu.fgedu_config_random (
id INT,
config_key VARCHAR(100),
config_value VARCHAR(500),
create_time TIMESTAMP
) DISTRIBUTED RANDOMLY;
CREATE TABLE

3.1.3 创建复制分布表

# 创建复制分布表（维度表）
fgedudb=> CREATE TABLE fgedu.fgedu_dim_region (
region_id INT,
region_name VARCHAR(50),
province VARCHAR(50),
city VARCHAR(50)
) DISTRIBUTED REPLICATED;
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_dim_region VALUES
fgedudb-> (1, ‘华东’, ‘上海’, ‘上海’),
fgedudb-> (2, ‘华北’, ‘北京’, ‘北京’),
fgedudb-> (3, ‘华南’, ‘广东’, ‘广州’),
fgedudb-> (4, ‘西南’, ‘四川’, ‘成都’);
INSERT 0 4

3.2 GreenPlum分区键创建实战

3.2.1 创建范围分区表

# 创建范围分区表（按月分区）
fgedudb=> CREATE TABLE fgedu.fgedu_sales_range (
id INT,
sale_date DATE,
region VARCHAR(50),
amount NUMERIC(18,2)
) DISTRIBUTED BY (id)
PARTITION BY RANGE (sale_date)
(
START (date ‘2024-01-01’)
END (date ‘2025-01-01’)
EVERY (INTERVAL ‘1 month’)
);
CREATE TABLE

# 查看所有分区
fgedudb=> SELECT
fgedudb-> partitiontablename,
fgedudb-> partitionrange,
fgedudb-> partitionstart,
fgedudb-> partitionend
fgedudb-> FROM pg_partitions
fgedudb-> WHERE tablename = ‘fgedu_sales_range’;

partitiontablename | partitionrange | partitionstart | partitionend
——————–+————————+—————-+—————
fgedu_sales_range_1_prt_1 | [2024-01-01,2024-02-01) | 2024-01-01 | 2024-02-01
fgedu_sales_range_1_prt_2 | [2024-02-01,2024-03-01) | 2024-02-01 | 2024-03-01
fgedu_sales_range_1_prt_3 | [2024-03-01,2024-04-01) | 2024-03-01 | 2024-04-01
fgedu_sales_range_1_prt_4 | [2024-04-01,2024-05-01) | 2024-04-01 | 2024-05-01
fgedu_sales_range_1_prt_5 | [2024-05-01,2024-06-01) | 2024-05-01 | 2024-06-01
fgedu_sales_range_1_prt_6 | [2024-06-01,2024-07-01) | 2024-06-01 | 2024-07-01
fgedu_sales_range_1_prt_7 | [2024-07-01,2024-08-01) | 2024-07-01 | 2024-08-01
fgedu_sales_range_1_prt_8 | [2024-08-01,2024-09-01) | 2024-08-01 | 2024-09-01
fgedu_sales_range_1_prt_9 | [2024-09-01,2024-10-01) | 2024-09-01 | 2024-10-01
fgedu_sales_range_1_prt_10 | [2024-10-01,2024-11-01) | 2024-10-01 | 2024-11-01
fgedu_sales_range_1_prt_11 | [2024-11-01,2024-12-01) | 2024-11-01 | 2024-12-01
fgedu_sales_range_1_prt_12 | [2024-12-01,2025-01-01) | 2024-12-01 | 2025-01-01
(12 rows)

3.2.2 创建列表分区表

# 创建列表分区表（按地区分区）
fgedudb=> CREATE TABLE fgedu.fgedu_sales_list (
id INT,
sale_date DATE,
region VARCHAR(50),
amount NUMERIC(18,2)
) DISTRIBUTED BY (id)
PARTITION BY LIST (region)
(
PARTITION east VALUES (‘上海’, ‘江苏’, ‘浙江’),
PARTITION north VALUES (‘北京’, ‘天津’, ‘河北’),
PARTITION south VALUES (‘广东’, ‘广西’, ‘海南’),
PARTITION west VALUES (‘四川’, ‘重庆’, ‘贵州’),
DEFAULT PARTITION other
);
CREATE TABLE

# 查看列表分区
fgedudb=> SELECT
fgedudb-> partitiontablename,
fgedudb-> partitionlist
fgedudb-> FROM pg_partitions
fgedudb-> WHERE tablename = ‘fgedu_sales_list’;

partitiontablename | partitionlist
——————–+———————
fgedu_sales_list_1_prt_east | {上海,江苏,浙江}
fgedu_sales_list_1_prt_north| {北京,天津,河北}
fgedu_sales_list_1_prt_south| {广东,广西,海南}
fgedu_sales_list_1_prt_west | {四川,重庆,贵州}
fgedu_sales_list_1_prt_other|
(5 rows)

3.3 GreenPlum数据分布检查实战

3.3.1 检查数据分布情况

# 先插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_orders_hash
fgedudb-> SELECT
fgedudb-> generate_series(1, 10000) AS id,
fgedudb-> ‘2024-01-01’::DATE + (random() * 365)::INT AS order_date,
fgedudb-> (random() * 1000)::INT AS customer_id,
fgedudb-> (random() * 100)::INT AS product_id,
fgedudb-> (random() * 10000)::NUMERIC(18,2) AS amount;
INSERT 0 10000

# 检查数据分布情况
fgedudb=> SELECT
fgedudb-> gp_segment_id,
fgedudb-> count(*) AS row_count,
fgedudb-> pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_orders_hash’)
fgedudb-> / (SELECT count(*) FROM gp_segment_configuration WHERE role = ‘p’)) AS estimated_size
fgedudb-> FROM fgedu.fgedu_orders_hash
fgedudb-> GROUP BY gp_segment_id
fgedudb-> ORDER BY gp_segment_id;

gp_segment_id | row_count | estimated_size
—————+———–+—————-
0 | 1667 | 128 kB
1 | 1667 | 128 kB
2 | 1666 | 128 kB
3 | 1667 | 128 kB
4 | 1666 | 128 kB
5 | 1667 | 128 kB
(6 rows)

# 计算数据倾斜率
fgedudb=> WITH dist AS (
fgedudb(> SELECT
fgedudb(> gp_segment_id,
fgedudb(> count(*) AS cnt
fgedudb(> FROM fgedu.fgedu_orders_hash
fgedudb(> GROUP BY gp_segment_id
fgedudb(> )
fgedudb-> SELECT
fgedudb-> max(cnt) AS max_rows,
fgedudb-> min(cnt) AS min_rows,
fgedudb-> avg(cnt) AS avg_rows,
fgedudb-> (max(cnt) – avg(cnt)) / avg(cnt) * 100 AS skew_percent
fgedudb-> FROM dist;

max_rows | min_rows | avg_rows | skew_percent
———-+———-+———————–+———————-
1667 | 1666 | 1666.666666666666667 | 0.01999600079984002
(1 row)

3.3.2 使用gp_toolkit检查数据分布

# 使用gp_toolkit查看表的大小分布
fgedudb=> SELECT
fgedudb-> sodddatname AS database_name,
fgedudb-> soddrelname AS table_name,
fgedudb-> sodddsize AS total_size,
fgedudb-> sodddskewpercent AS skew_percent
fgedudb-> FROM gp_toolkit.gp_size_of_database_distribution
fgedudb-> WHERE soddrelname = ‘fgedu_orders_hash’;

# 查看所有表的分布情况
fgedudb=> SELECT
fgedudb-> schemaname,
fgedudb-> tablename,
fgedudb-> size,
fgedudb-> skewpercent
fgedudb-> FROM gp_toolkit.gp_skew_coefficients
fgedudb-> WHERE schemaname = ‘fgedu’
fgedudb-> ORDER BY skewpercent DESC
fgedudb-> LIMIT 10;

Part04-生产案例与实战讲解

4.1 GreenPlum订单表设计案例

4.1.1 业务需求分析

订单表业务需求：

1. 数据量
– 每日订单量：100万条
– 年订单量：3.6亿条
– 保留时间：3年

2. 查询模式
– 按客户ID查询订单
– 按日期范围查询订单
– 按地区统计订单
– Join客户表、产品表

3. 维护需求
– 定期归档历史数据
– 定期删除过期数据
– 快速查询性能

更多学习教程公众号风哥教程itpux_com

4.1.2 表设计方案

# 订单表设计方案
fgedudb=> CREATE TABLE fgedu.fgedu_orders_prod (
id BIGINT,
order_no VARCHAR(50),
order_date DATE,
order_time TIMESTAMP,
customer_id INT,
product_id INT,
region_id INT,
quantity INT,
unit_price NUMERIC(18,2),
total_amount NUMERIC(18,2),
status VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
WITH (
appendonly = true,
orientation = column,
compresstype = zlib,
compresslevel = 5
)
DISTRIBUTED BY (customer_id)
PARTITION BY RANGE (order_date)
(
START (date ‘2024-01-01’)
END (date ‘2027-01-01’)
EVERY (INTERVAL ‘1 month’)
);
CREATE TABLE

# 创建索引
fgedudb=> CREATE INDEX idx_orders_order_date ON fgedu.fgedu_orders_prod (order_date);
CREATE INDEX
fgedudb=> CREATE INDEX idx_orders_region_id ON fgedu.fgedu_orders_prod (region_id);
CREATE INDEX

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_orders_prod (
fgedudb-> id, order_no, order_date, order_time,
fgedudb-> customer_id, product_id, region_id,
fgedudb-> quantity, unit_price, total_amount, status
fgedudb-> )
fgedudb-> SELECT
fgedudb-> generate_series(1, 100000) AS id,
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 100000)::TEXT, 10, ‘0’) AS order_no,
fgedudb-> ‘2024-01-01’::DATE + (random() * 365)::INT AS order_date,
fgedudb-> ‘2024-01-01’::TIMESTAMP + (random() * 365 * 24 * 3600) * INTERVAL ‘1 second’ AS order_time,
fgedudb-> (random() * 10000)::INT AS customer_id,
fgedudb-> (random() * 1000)::INT AS product_id,
fgedudb-> (random() * 4 + 1)::INT AS region_id,
fgedudb-> (random() * 10 + 1)::INT AS quantity,
fgedudb-> (random() * 1000)::NUMERIC(18,2) AS unit_price,
fgedudb-> (random() * 10000)::NUMERIC(18,2) AS total_amount,
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘NEW’
fgedudb-> WHEN 1 THEN ‘PAID’
fgedudb-> WHEN 2 THEN ‘SHIPPED’
fgedudb-> ELSE ‘COMPLETED’
fgedudb-> END AS status;
INSERT 0 100000

# 测试分区裁剪
fgedudb=> EXPLAIN SELECT * FROM fgedu.fgedu_orders_prod
fgedudb-> WHERE order_date BETWEEN ‘2024-06-01’ AND ‘2024-06-30’;
QUERY PLAN
————————————————————————————
Gather Motion 6:1 (slice1; segments: 6) (cost=0.00..431.00 rows=1 width=244)
-> Append (cost=0.00..431.00 rows=1 width=244)
-> Seq Scan on fgedu_orders_prod_1_prt_6 fgedu_orders_prod (cost=0.00..431.00 rows=1 width=244)
Filter: ((order_date >= ‘2024-06-01’::date) AND (order_date <= '2024-06-30'::date)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows)

4.2 GreenPlum日志表设计案例

4.2.1 业务需求分析

日志表业务需求：

1. 数据量
– 每日日志量：1000万条
– 年日志量：36亿条
– 保留时间：6个月

2. 查询模式
– 按时间范围查询
– 按日志级别查询
– 按模块查询
– 快速检索能力

3. 特点
– 写入频繁
– 查询相对较少
– 需要快速归档
– 需要压缩存储

from GreenPlum视频:www.itpux.com

4.2.2 表设计方案

# 日志表设计方案
fgedudb=> CREATE TABLE fgedu.fgedu_access_log (
id BIGINT,
log_time TIMESTAMP,
log_date DATE,
log_level VARCHAR(10),
module VARCHAR(50),
message TEXT,
user_id INT,
ip_address VARCHAR(50),
request_url VARCHAR(500),
response_time INT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
WITH (
appendonly = true,
orientation = column,
compresstype = zstd,
compresslevel = 6
)
DISTRIBUTED BY (log_date, user_id)
PARTITION BY RANGE (log_date)
(
START (date ‘2024-01-01’)
END (date ‘2024-07-01’)
EVERY (INTERVAL ‘1 day’)
);
CREATE TABLE

# 查看分区数量
fgedudb=> SELECT count(*) FROM pg_partitions
fgedudb-> WHERE tablename = ‘fgedu_access_log’;
count
——-
181
(1 row)

# 添加新分区（提前一个月添加）
fgedudb=> ALTER TABLE fgedu.fgedu_access_log
fgedudb-> ADD PARTITION
fgedudb-> START (date ‘2024-07-01’)
fgedudb-> END (date ‘2024-08-01’)
fgedudb-> EVERY (INTERVAL ‘1 day’);
ALTER TABLE

# 删除旧分区
fgedudb=> ALTER TABLE fgedu.fgedu_access_log
fgedudb-> DROP PARTITION FOR (date ‘2024-01-01’);
ALTER TABLE

4.3 GreenPlum数据倾斜修复案例

4.3.1 发现数据倾斜

# 创建一个有数据倾斜的表
fgedudb=> CREATE TABLE fgedu.fgedu_skew_example (
id INT,
region VARCHAR(50),
amount NUMERIC(18,2)
) DISTRIBUTED BY (region);
CREATE TABLE

# 插入倾斜数据（80%数据在上海）
fgedudb=> INSERT INTO fgedu.fgedu_skew_example
fgedudb-> SELECT
fgedudb-> generate_series(1, 8000) AS id,
fgedudb-> ‘上海’ AS region,
fgedudb-> (random() * 10000)::NUMERIC(18,2) AS amount;
INSERT 0 8000

fgedudb=> INSERT INTO fgedu.fgedu_skew_example
fgedudb-> SELECT
fgedudb-> generate_series(8001, 10000) AS id,
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘北京’
fgedudb-> WHEN 1 THEN ‘广州’
fgedudb-> ELSE ‘深圳’
fgedudb-> END AS region,
fgedudb-> (random() * 10000)::NUMERIC(18,2) AS amount;
INSERT 0 2000

# 检查数据分布
fgedudb=> SELECT
fgedudb-> gp_segment_id,
fgedudb-> count(*) AS row_count,
fgedudb-> count(*) * 100.0 / (SELECT count(*) FROM fgedu.fgedu_skew_example) AS percent
fgedudb-> FROM fgedu.fgedu_skew_example
fgedudb-> GROUP BY gp_segment_id
fgedudb-> ORDER BY gp_segment_id;

gp_segment_id | row_count | percent
—————+———–+——————–
0 | 100 | 1.0000000000000000
1 | 100 | 1.0000000000000000
2 | 100 | 1.0000000000000000
3 | 9400 | 94.0000000000000000
4 | 100 | 1.0000000000000000
5 | 200 | 2.0000000000000000
(6 rows)

4.3.2 修复数据倾斜

# 方案1：更换分布键
fgedudb=> CREATE TABLE fgedu.fgedu_skew_fixed AS
fgedudb-> SELECT * FROM fgedu.fgedu_skew_example
fgedudb-> DISTRIBUTED BY (id);
SELECT 10000

# 检查新表的数据分布
fgedudb=> SELECT
fgedudb-> gp_segment_id,
fgedudb-> count(*) AS row_count,
fgedudb-> count(*) * 100.0 / (SELECT count(*) FROM fgedu.fgedu_skew_fixed) AS percent
fgedudb-> FROM fgedu.fgedu_skew_fixed
fgedudb-> GROUP BY gp_segment_id
fgedudb-> ORDER BY gp_segment_id;

gp_segment_id | row_count | percent
—————+———–+——————–
0 | 1667 | 16.6700000000000000
1 | 1667 | 16.6700000000000000
2 | 1666 | 16.6600000000000000
3 | 1667 | 16.6700000000000000
4 | 1666 | 16.6600000000000000
5 | 1667 | 16.6700000000000000
(6 rows)

# 方案2：使用复合分布键
fgedudb=> CREATE TABLE fgedu.fgedu_skew_compound AS
fgedudb-> SELECT * FROM fgedu.fgedu_skew_example
fgedudb-> DISTRIBUTED BY (region, id);
SELECT 10000

# 重命名表
fgedudb=> ALTER TABLE fgedu.fgedu_skew_example RENAME TO fgedu_skew_example_old;
ALTER TABLE
fgedudb=> ALTER TABLE fgedu.fgedu_skew_fixed RENAME TO fgedu_skew_example;
ALTER TABLE

# 删除旧表
fgedudb=> DROP TABLE fgedu.fgedu_skew_example_old;
DROP TABLE

Part05-风哥经验总结与分享

5.1 GreenPlum分布键与分区键最佳实践

5.1.1 分布键最佳实践

分布键最佳实践：

1. 选择分布键原则
– 优先选择唯一键或高基数字段
– 选择查询和Join常用的字段
– 避免选择NULL值多的字段
– 避免选择更新频繁的字段

2. 不同场景的分布键选择
– 大事实表：选择业务主键或常用Join字段
– 维度表：使用Replicated或高基数字段
– 小表：使用Replicated或Random分布
– 日志表：使用复合分布键（时间+ID）

3. 复合分布键使用
– 当单个字段基数不够时使用
– 顺序：先常用过滤条件，再唯一标识
– 示例：(region, customer_id)或(sale_date, id)

4. 定期检查数据分布
– 使用gp_toolkit.gp_skew_coefficients
– 使用gp_toolkit.gp_size_of_database_distribution
– 倾斜率超过10%需要重视
– 倾斜率超过20%必须优化

5.1.2 分区键最佳实践

风哥提示：分区键最佳实践：

选择查询中常用的过滤条件作为分区键
时间类型字段是最常见的分区键
每个分区大小建议10GB-100GB
分区数量建议12-100个，不宜过多
定期添加新分区，删除旧分区
分区表与分布键配合使用效果最佳

5.2 GreenPlum表设计检查清单

GreenPlum表设计检查清单：

[ ] 1. 分布键设计
[ ] 选择了合适的分布键
[ ] 分布键基数足够高
[ ] 分布键不是更新频繁的字段
[ ] 分布键NULL值少
[ ] 检查了数据分布情况

[ ] 2. 分区键设计
[ ] 确定了是否需要分区
[ ] 选择了合适的分区键
[ ] 确定了分区类型（范围/列表）
[ ] 确定了分区粒度
[ ] 制定了分区维护计划

[ ] 3. 存储模型选择
[ ] 选择了合适的存储模型（Heap/AO/AOCO）
[ ] 确定了压缩类型和级别
[ ] 考虑了读写比例
[ ] 考虑了数据更新频率

[ ] 4. 索引设计
[ ] 确定了是否需要索引
[ ] 选择了合适的索引类型
[ ] 避免了过多的索引
[ ] 考虑了索引维护成本

[ ] 5. 性能验证
[ ] 测试了数据分布均匀性
[ ] 测试了查询性能
[ ] 测试了数据加载性能
[ ] 测试了Join操作性能

5.3 GreenPlum常见设计误区

5.3.1 常见误区1：所有表都用相同的分布键

误区：所有表都用相同的分布键

错误示例：
CREATE TABLE fgedu.fgedu_orders DISTRIBUTED BY (id);
CREATE TABLE fgedu.fgedu_customers DISTRIBUTED BY (id);
CREATE TABLE fgedu.fgedu_products DISTRIBUTED BY (id);
— 三张表都用id作为分布键，但Join时可能需要跨节点

正确做法：
CREATE TABLE fgedu.fgedu_orders DISTRIBUTED BY (customer_id);
CREATE TABLE fgedu.fgedu_customers DISTRIBUTED BY (id);
CREATE TABLE fgedu.fgedu_products DISTRIBUTED REPLICATED;
— 订单表用customer_id分布，客户表用id分布，产品表复制分布

原因分析：
– 不同表有不同的访问模式
– Join操作时需要考虑数据局部性
– 小表适合用Replicated分布

5.3.2 常见误区2：过度使用分区

误区：过度使用分区

错误示例：
— 按天分区，但只有1000条数据
CREATE TABLE fgedu.fgedu_small_table (…)
PARTITION BY RANGE (log_date)
(
START (date ‘2024-01-01’)
END (date ‘2025-01-01’)
EVERY (INTERVAL ‘1 day’)
);
— 365个分区，每个分区只有几条数据

正确做法：
— 小表不需要分区
CREATE TABLE fgedu.fgedu_small_table (…)
DISTRIBUTED BY (id);

原因分析：
– 小表分区反而增加管理开销
– 分区裁剪效果不明显
– 建议数据量至少100GB以上再考虑分区

5.3.3 常见误区3：忽略数据倾斜检查

误区：创建表后不检查数据倾斜

错误做法：
CREATE TABLE fgedu.fgedu_table (…) DISTRIBUTED BY (region);
INSERT INTO fgedu.fgedu_table …;
— 直接使用，不检查数据分布

正确做法：
CREATE TABLE fgedu.fgedu_table (…) DISTRIBUTED BY (region);
INSERT INTO fgedu.fgedu_table …;

— 检查数据分布
SELECT gp_segment_id, count(*)
FROM fgedu.fgedu_table
GROUP BY gp_segment_id;

— 如果倾斜严重，更换分布键
CREATE TABLE fgedu.fgedu_table_new AS
SELECT * FROM fgedu.fgedu_table
DISTRIBUTED BY (id);

原因分析：
– 数据倾斜是GreenPlum性能第一杀手
– 早期发现容易修复
– 后期修复成本很高

通过本文档的学习，相信您已经掌握了GreenPlum分布键与分区键的设计方法和最佳实践。在生产环境中，合理的分布键和分区键设计是GreenPlum高性能的基础，需要根据业务场景认真规划和设计。更多视频教程www.fgedu.net.cn，学习交流加群风哥微信: itpux-com，学习交流加群风哥QQ113257174，更多学习教程公众号风哥教程itpux_com，from GreenPlum视频:www.itpux.com。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html