基本使用

muzimu217

2025-06-19 发布247 浏览 · 0 点赞 · 0 收藏

在快速入门文章中我们介绍了 OpenTenBase 的架构、源码编译安装、集群运行状态、启动停止等内容。

在应用接入中我们介绍了应用程序连接 OpenTenBase 数据库进行建库、建表、数据导入、查询等操作。

本篇将介绍 OpenTenBase 中特有的 shard 表、冷热分区表、复制表的创建，和基本的 DML 操作。

1、创建数据表

1.1、创建 shard 普通表

OpenTenBase_shard普通表

OpenTenBase_shard普通表续

OpenTenBase_shard普通表说明

说明：

distribute by shard(x) 用于指定分布键，数据分布于那个节点就是根据这个字段值来计算分片。

to group xxx 用于指定存储组（每个存储组可以有多个节点）。

分布键字段值不能修改，字段长度不能修改，字段类型不能修改。

1.2、创建 shard 普通分区表

OpenTenBase_shard分区表

OpenTenBase_shard分区表续

[opentenbase@VM_0_37_centos shell]$ psql -h 172.16.0.42 -p 11387 -d postgres -U opentenbase
psql (PostgreSQL 10.0 opentenbase V2)
Type "help" for help.

postgres=# create table public.t1_pt
(
f1 int not null,
f2 timestamp not null,
f3 varchar(20),
primary key(f1)
)
partition by range (f2)
begin (timestamp without time zone '2019-01-01 0:0:0')
step (interval '1 month') partitions (3)
distribute by shard(f1)
to group default_group;

CREATE TABLE
postgres=#

postgres=# \d+ public.t1_pt
                                             Table "public.t1_pt"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 f1     | integer                     |           | not null |         | plain    |              |
 f2     | timestamp without time zone |           | not null |         | plain    |              |
 f3     | character varying(20)       |           |          |         | extended |              |
Indexes:
    "t1_pt_pkey" PRIMARY KEY, btree (f1)
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES
Partition By: RANGE(f2)
         # Of Partitions: 3
         Start With: 2019-01-01
         Interval Of Partition: 1 MONTH

postgres=#

说明：

partition by range (x) 用于指定分区键，支持 timesamp,int 类型，数据分布于那个子表就是根据这个字段值来计算分区。

begin( xxx )指定开始分区的时间点。

step(xxx)指定分区有周期

partions(xx)初始化时建立分区子表个数。

增加分区子表的方法 ALTER TABLE public.t1_pt ADD PARTITIONS 2;

1.3、创建 shard 冷热分区表

OpenTenBase_shard冷热分区表

OpenTenBase_shard冷热分区表续

[opentenbase@VM_0_37_centos shell]$ psql -h 172.16.0.42 -p 11387 -d postgres -U opentenbase
psql (PostgreSQL 10.0 opentenbase V2)
Type "help" for help.

postgres=# create table public.t1_cold_hot
(
f1 int not null,
f2 timestamp not null,
f3 varchar(20),
primary key(f1)
)
partition by range (f2)
begin (timestamp without time zone '2017-01-01 0:0:0')
step (interval '12 month') partitions (4)
distribute by shard(f1,f2)
to group default_group cold_group;
CREATE TABLE
postgres=# \d+ public.t1_cold_hot
Table "public.t1_cold_hot"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
f1 | integer | | not null | | plain | |
f2 | timestamp without time zone | | not null | | plain | |
f3 | character varying(20) | | | | extended | |
Indexes:
"t1_cold_hot_pkey" PRIMARY KEY, btree (f1)
Distribute By SHARD(f1,f2)
Hotnodes:dn001 Coldnodes:dn002
Partition By: RANGE(f2) # Of Partitions: 4
Start With: 2017-01-01
Interval Of Partition: 12 MONTH

postgres=#

说明：

Distribute By SHARD(f1,f2)，冷热分区表需要指定两个字段来做路由，分别是分布键和分区键。

to group default_group cold_group，需要指定两个存储组，第一个是热数据存储组，第二个是冷存储组。

创建时间范围冷热分区表需要有两个group，冷数据的cold_group对应的节点需要标识为冷节点，如下所示:

[opentenbase@VM_0_37_centos shell]$ psql -h 172.16.0.42 -p 11000 -d postgres -U opentenbase
psql (PostgreSQL 10.0 opentenbase V2)
Type "help" for help.

postgres=# select pg_set_node_cold_access();
pg_set_node_cold_access

---

success
(1 row)

冷热分区表需要在postgresql.conf中配置冷热分区时间参数和分区级别，如下所示:

cold_hot_sepration_mode = 'year'
enable_cold_seperation = true
manual_hot_date = '2019-01-01'

1.4、创建复制表

OpenTenBase_shard冷热分区表

[opentenbase@VM_0_37_centos shell]$ psql -h 172.16.0.42 -p 11387 -d postgres -U opentenbase
psql (PostgreSQL 10.0 opentenbase V2)
Type "help" for help.

postgres=# create table public.t1_rep
(
f1 int not null,
f2 varchar(20),
primary key(f1)
)
distribute by replication ;
to group default_group;
CREATE TABLE

说明：

经常要跨库JOIN的小数据量表可以考虑使用复制表。

复制表是所有节点都有全量数据，对于大数据量的数据表不适合。

复制表更新性能较低。

2、DML相关操作

2.1 INSERT

插入多条记录

CREATE TABLE public.t1_insert_mul (
    f1 int NOT NULL,
    f2 varchar(20),
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

INSERT INTO t1_insert_mul VALUES (1, 'opentenbase'), (2, 'pg');
-- 输出: INSERT 0 2

插入更新（ON CONFLICT）

CREATE TABLE public.t1_conflict (
    f1 int NOT NULL,
    f2 varchar(20),
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

INSERT INTO t1_conflict VALUES (1, 'opentenbase') 
ON CONFLICT (f1) DO UPDATE SET f2 = 'opentenbase';

支持复合主键场景：

CREATE TABLE public.t1_conflict (
    f1 int NOT NULL,
    f2 varchar(20) NOT NULL,
    f3 int,
    PRIMARY KEY(f1, f2)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

INSERT INTO t1_conflict VALUES (1, 'opentenbase', 2) 
ON CONFLICT (f1, f2) DO UPDATE SET f3 = 2;

插入返回（RETURNING）

CREATE TABLE public.t1_insert_return (
    f1 int NOT NULL,
    f2 varchar(20) NOT NULL DEFAULT 'opentenbase',
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

INSERT INTO t1_insert_return VALUES (1) RETURNING *;
-- 输出:
-- f1 | f2  
-- ----+-------
-- 1 | opentenbase
-- (1 row)
-- INSERT 0 1

更多 INSERT 使用方法请参考 PostgreSQL 官方文档：http://www.postgres.cn/docs/10/sql-insert.html

2.2 UPDATE

基于分布键条件更新

CREATE TABLE public.t1_update_pkey (
    f1 int NOT NULL,
    f2 varchar(20) NOT NULL DEFAULT 'opentenbase',
    f3 varchar(32),
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

EXPLAIN UPDATE t1_update_pkey SET f2='opentenbase' WHERE f1=1;
-- 查询计划显示仅在单个节点执行，性能最优

非分布键更新

EXPLAIN UPDATE t1_update_pkey SET f2='opentenbase' WHERE f3='pg';
-- 查询计划显示需要扫描所有节点

分区表带分区条件更新

CREATE TABLE public.t1_pt_update (
    f1 int NOT NULL,
    f2 timestamp NOT NULL,
    f3 varchar(20),
    PRIMARY KEY(f1)
) PARTITION BY RANGE (f2)
BEGIN ('2019-01-01') STEP (INTERVAL '1 month') PARTITIONS (2)
DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

EXPLAIN UPDATE t1_pt_update SET f3='opentenbase'
WHERE f1=1 AND f2>'2019-01-01' AND f2<'2019-02-01';
-- 仅更新符合条件的分区，性能最优

关联表更新

UPDATE t1_update_join1
SET f2='pg'
FROM t1_update_join2
WHERE t1_update_join1.f1 = t1_update_join2.f1;
-- 要求关联字段必须是分布键

分布键、分区键不可更新

UPDATE t1_update_pkey SET f1=2 WHERE f1=1;
-- 错误提示: ERROR: Distributed column or partition column "f1" can't be updated in current version

当前解决办法：删除旧记录，再新增记录
更多 UPDATE 使用方法请参考 PostgreSQL 官方文档：http://www.postgres.cn/docs/10/sql-update.html

2.3 DELETE

删除并返回记录

CREATE TABLE public.t1_delete_return (
    f1 int NOT NULL,
    f2 varchar(20) NOT NULL DEFAULT 'opentenbase',
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

DELETE FROM t1_delete_return WHERE f1=1 RETURNING *;
-- 输出:
-- f1 | f2  
-- ----+-------
-- 1 | opentenbase
-- (1 row)

DELETE 的最佳实践与 UPDATE 类似，建议基于分布键进行操作。
更多 DELETE 使用方法请参考 PostgreSQL 官方文档：http://www.postgres.cn/docs/10/sql-delete.html

2.4 SELECT

基于分布键查询

CREATE TABLE public.t1_select (
    f1 int NOT NULL,
    f2 varchar(20) NOT NULL DEFAULT 'opentenbase',
    f3 varchar(32),
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

EXPLAIN SELECT * FROM t1_select WHERE f1=1;
-- 查询计划显示仅在单个节点执行，性能最优

非分布键查询

EXPLAIN SELECT * FROM t1_select WHERE f1 < 3;
-- 查询计划显示需扫描所有节点并在 CN 汇总结果

分布键 JOIN 查询

EXPLAIN SELECT t1_select_join1.* 
FROM t1_select_join1, t1_select_join2 
WHERE t1_select_join1.f1 = t1_select_join2.f1 AND t1_select_join1.f1 = 1;
-- 查询计划显示在单个节点完成 JOIN，性能最优

非分布键 JOIN 查询

EXPLAIN SELECT * 
FROM t1_select_join1, t1_select_join2 
WHERE t1_select_join1.f1 = t1_select_join2.f2 AND t1_select_join1.f2 = 1;
-- 查询计划显示需要数据重分布，性能较低

2.5 TRUNCATE

普通表 TRUNCATE

CREATE TABLE public.t1_delete_truncate (
    f1 int NOT NULL,
    f2 varchar(20) NOT NULL DEFAULT 'opentenbase',
    PRIMARY KEY(f1)
) DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

TRUNCATE TABLE t1_delete_truncate;

分区表 TRUNCATE

CREATE TABLE public.t1_pt (
    f1 int NOT NULL,
    f2 timestamp NOT NULL,
    f3 varchar(20),
    PRIMARY KEY(f1)
) PARTITION BY RANGE (f2)
BEGIN ('2019-01-01') STEP (INTERVAL '1 month') PARTITIONS (3)
DISTRIBUTE BY SHARD(f1) TO GROUP default_group;

TRUNCATE public.t1_pt PARTITION FOR ('2019-01-01'::timestamp without time zone);

文章转载于OpenTenBase官网，原链接为：[基本使用]

#OpenTenBase官网 #教程

请前往登录/注册即可发表您的看法…