关于数据库:MogDB与PostgreSQL分区策略语法测试

41次阅读

共计 10520 个字符，预计需要花费 27 分钟才能阅读完成。

1. 父子继承表

目前 MogDB 还不反对 inherits 继承个性。

omm=# CREATE TABLE tab_t2(age int) inherits(tab_t1);
ERROR:  CREATE TABLE ... INHERITS is not yet supported.

PostgreSQL 反对继承，版本 10 之前的分区表都是通过继承个性来实现，每个分区实际上都是一个独立的表。数据更新可通过触发器 trigger 或者规定 rule 来实现。

上面演示 PostgreSQL 中的继承个性：

CREATE TABLE tab_t1(id int primary key,name varchar(20) not null);
CREATE TABLE tab_t2(age int) inherits(tab_t1);

对父表减少字段：

alter table tab_t1 add create_date date;

查看表构造：

postgres=# \d tab_t1
                        Table "public.tab_t1"
   Column    |         Type          | Collation | Nullable | Default 
-------------+-----------------------+-----------+----------+---------
 id          | integer               |           | not null | 
 name        | character varying(20) |           | not null | 
 create_date | date                  |           |          | 
Indexes:
    "tab_t1_pkey" PRIMARY KEY, btree (id)
Number of child tables: 1 (Use \d+ to list them.)

postgres=# \d tab_t2
                        Table "public.tab_t2"
   Column    |         Type          | Collation | Nullable | Default 
-------------+-----------------------+-----------+----------+---------
 id          | integer               |           | not null | 
 name        | character varying(20) |           | not null | 
 age         | integer               |           |          | 
 create_date | date                  |           |          | 
Inherits: tab_t1

咱们不通过触发器或者规定路由数据，直接插入数据：

INSERT INTO tab_t1 VALUES (1,'data 1 in tab_t1',now());
INSERT INTO tab_t1 VALUES (2,'data 2 in tab_t1',now());
INSERT INTO tab_t2 VALUES (3,'data 3 in tab_t2',18,now());
INSERT INTO tab_t2 VALUES (4,'data 4 in tab_t2',20,now());

从父表中查问数据将显示父表及子表的所有数据：

postgres=# SELECT * from tab_t1;
id |       name       | create_date 
----+------------------+-------------
  1 | data 1 in tab_t1 | 2021-04-11
  2 | data 2 in tab_t1 | 2021-04-11
  3 | data 3 in tab_t2 | 2021-04-11
  4 | data 4 in tab_t2 | 2021-04-11
(4 rows)

通过 ONLY 关键字实现只对父表的查问：

postgres=#  SELECT * from ONLY tab_t1;
 id |       name       | create_date 
----+------------------+-------------
  1 | data 1 in tab_t1 | 2021-04-11
  2 | data 2 in tab_t1 | 2021-04-11
(2 rows)

从子表中查问只显示子表中的数据：

postgres=# select * from tab_t2;
 id |       name       | age | create_date 
----+------------------+-----+-------------
  3 | data 3 in tab_t2 |  18 | 2021-04-11
  4 | data 4 in tab_t2 |  20 | 2021-04-11
(2 rows)

继承个性应用留神点：

子表并不能齐全继承父表的所有属性，比方惟一束缚、主键、外键，查看束缚与非空束缚能够继承。

批改父表的构造，子表构造同时被批改。

父表不存数据时，不倡议在父表上创立索引和或惟一束缚，应该在每个子表上别离创立。

2. 申明式分区：范畴分区

将数据基于范畴映射到每一个分区，这个范畴是由创立分区表时指定的分区键决定的。这种分区形式较为罕用，并且分区键常常采纳日期。

PostgreSQL 从版本 10 开始反对，范畴分区申明式语法分两步：

1. 通过指定 PARTITION BY 子句把表创立为分区表，包含分区办法以及用作分区键的 column 列表。

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate)

2. 创立分区，每个分区的定义必须指定对应于父表的分区办法和分区键的边界。

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
...

MogDB 范畴分区申明式语法能够一步实现，范畴分区从句语法有两种格局：

VALUES LESS THAN 语法格局(范畴分区策略的分区键最多反对 4 列)

START END 语法格局(范畴分区策略的分区键仅反对 1 列)

留神下面两种从句语法不能混用，START END 语法格局应用 gs_dump 时会转变为 VALUES LESS THAN 语法格局。

MogDB 范畴分区例子：

CREATE TABLE tab_part (
    id int not null,
    create_date date not null
) PARTITION BY RANGE(create_date) 
(PARTITION p_20210401 VALUES LESS THAN(to_date('2021-04-01','yyyy-mm-dd')),
PARTITION p_20210402 VALUES LESS THAN(to_date('2021-04-02','yyyy-mm-dd')),
PARTITION p_max VALUES LESS THAN(MAXVALUE)
);

查看零碎表可看到分区策略为“r”，range 分区。

omm=# select relname,partstrategy from pg_partition where relname='tab_part';
 relname  | partstrategy 
----------+--------------
 tab_part | r
(1 row)

查看分区及边界：

select relname,parttype,parentid,boundaries 
from pg_partition 
where parentid in(select oid from pg_class where relname='tab_part');
  relname   | parttype | parentid |       boundaries        
------------+----------+----------+-------------------------
 tab_part   | r        |    16412 | 
 p_20210401 | p        |    16412 | {"2021-04-01 00:00:00"}
 p_20210402 | p        |    16412 | {"2021-04-02 00:00:00"}
 p_max      | p        |    16412 | {NULL}
(4 rows)

接下来插入三条数据：

insert into tab_part values(1,'2021-03-31');
insert into tab_part values(2,'2021-04-01');
insert into tab_part values(3,'9999-12-31');

查问分区，按分区名 p_20210402，也能够按分区边界值（PARTITION FOR）。

omm=# select * from tab_part PARTITION (p_20210402);
 id |     create_date     
----+---------------------
  2 | 2021-04-01 00:00:00
(1 row)

3. 申明式分区：列表分区

通过显式地列出每一个分区中呈现的键值来划分表。

与后面范畴分区一样，PostgreSQL 列表分区申明式语法也是两步，从版本 10 开始反对，MogDB 只需一步实现。

MogDB 列表分区例子：

CREATE TABLE tab_list(
    dept_no number,
    part_no varchar2(20),
    country varchar2(20),
    dtime date,
    amount number
)
PARTITION BY LIST(country)(PARTITION europe VALUES('FRANCE', 'ITALY'),
    PARTITION asia VALUES('INDIA', 'PAKISTAN'),
    PARTITION americas VALUES('US', 'CANADA')
);

查看零碎表可看到分区策略为“l”，list 分区。

omm=# select relname,partstrategy from pg_partition where relname='tab_list';
 relname  | partstrategy 
----------+--------------
 tab_list | l
(1 row)

查看分区及边界：

select relname,parttype,parentid,boundaries 
from pg_partition 
where parentid in(select oid from pg_class where relname='tab_list');
 relname  | parttype | parentid |    boundaries    
----------+----------+----------+------------------
 tab_list | r        |    16389 | 
 americas | p        |    16389 | {US,CANADA}
 asia     | p        |    16389 | {INDIA,PAKISTAN}
 europe   | p        |    16389 | {FRANCE,ITALY}
(4 rows)

4. 申明式分区：哈希分区

将数据通过哈希映射到每一个分区，每一个分区中存储了具备雷同哈希值的记录。

PostgreSQL 哈希分区申明式语法也是两步，从版本 11 开始反对，MogDB 只需一步实现。

MogDB 哈希分区例子：

CREATE TABLE tab_hash(
    dept_no number,
    part_no varchar2(20),
    country varchar2(20),
    dtime date,
    amount number
)PARTITION BY HASH(part_no)(
    PARTITION p1,
    PARTITION p2,
    PARTITION p3
);

查看零碎表可看到分区策略为“h”，hash 分区。

omm=# select relname,partstrategy from pg_partition where relname='tab_hash';
 relname  | partstrategy 
----------+--------------
 tab_hash | h
(1 row)

查看分区及边界：

select relname,parttype,parentid,boundaries 
from pg_partition 
where parentid in(select oid from pg_class where relname='tab_hash');
 relname  | parttype | parentid | boundaries 
----------+----------+----------+------------
 tab_hash | r        |    16405 | 
 p3       | p        |    16405 | {2}
 p2       | p        |    16405 | {1}
 p1       | p        |    16405 | {0}
(4 rows)

5. 基于范畴分区的主动扩大距离分区

距离分区（Interval-Partition）是针对 Range 类型分区的一种性能拓展。对间断数据类型的 Range 分区，如果插入的新数据值与以后分区均不匹配，Interval-Partition 个性能够实现主动的分区创立。分区字段必须是工夫类型(date 或 timestamp)。

PostgreSQL 目前还不反对该语法，MogDB 从版本曾经反对。

MogDB 距离分区例子：

CREATE TABLE tab_range_interval (
    id int not null,
    create_date date not null
) PARTITION BY RANGE(create_date) INTERVAL('1 month')
(PARTITION p1 VALUES LESS THAN(to_date('2021-01-29','yyyy-mm-dd'))
);

查看零碎表可看到分区策略为“i”，interval 分区。

omm=# select relname,partstrategy,interval from pg_partition where relname='tab_range_interval';
 relname  | partstrategy | interval  
----------+--------------+-----------
 tab_part | i            | {"1 month"}
(1 row)

接下来插入三条数据：


insert into tab_range_interval values(1,'2021-01-29');
insert into tab_range_interval values(2,'2021-02-28');
insert into tab_range_interval values(3,'2022-03-29');

插入数据后查看是否主动创立了相应的分区：

omm=# select relname,parttype,parentid,boundaries 
from pg_partition 
where parentid in(select oid from pg_class where relname='tab_range_interval');
      relname       | parttype | parentid |  boundaries  
--------------------+----------+----------+--------------
 tab_range_interval | r        |    16572 | 
 p1                 | p        |    16572 | {2021-01-29}
 sys_p1             | p        |    16572 | {2021-02-28}
 sys_p2             | p        |    16572 | {2021-03-28}
 sys_p3             | p        |    16572 | {2022-04-28}
(5 rows)

能够看到 sys_p1，sys_p2，sys_p3 为零碎主动生成的分区，并且主动解决了月末问题。

留神：

1. 从 2.0.0 版本开始，模板库默认字符集由 SQL_ASCII 改为了 UTF8, 同时数据库兼容性由 Oracle 改为 PostgreSQL，对本测试的影响是 date 数据类型。

2. 目前只反对 INTERVAL-RANGE，其它形式不反对。

3. 距离分区字段必须是工夫类型(date 或 timestamp)。

6. 子分区

MogDB 目前还不反对子分区，PostgreSQL 申明式分区是反对的。

PostgreSQL 子分区例子:

先创立分区表：

CREATE TABLE sales(
        id serial,
        sales_count int,
        sales_date date not null
 ) PARTITION BY RANGE(sales_date);

再按年创立分区：sales_2018、sales_2019、sales_2020：

CREATE TABLE sales_2018 PARTITION OF sales
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
 
CREATE TABLE sales_2019 PARTITION OF sales
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
 
CREATE TABLE sales_2020 PARTITION OF sales
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

2021 年新建分区时能够创立子分区，按月分区：

create table sales_2021 partition of sales
for values from ('2021-01-01') to ('2022-01-01') partition by range(sales_date);

create table sales_2021_01 partition of sales_2021
for values from ('2021-01-01') to ('2021-02-01');
create table sales_2021_02 partition of sales_2021
for values from ('2021-02-01') to ('2021-03-01');
create table sales_2021_03 partition of sales_2021
for values from ('2021-03-01') to ('2021-04-01');
create table sales_2021_04 partition of sales_2021
for values from ('2021-04-01') to ('2021-05-01');
create table sales_2021_05 partition of sales_2021
for values from ('2021-05-01') to ('2021-06-01');
create table sales_2021_06 partition of sales_2021
for values from ('2021-06-01') to ('2021-07-01');
create table sales_2021_07 partition of sales_2021
for values from ('2021-07-01') to ('2021-08-01');
create table sales_2021_08 partition of sales_2021
for values from ('2021-08-01') to ('2021-09-01');
create table sales_2021_09 partition of sales_2021
for values from ('2021-09-01') to ('2021-10-01');
create table sales_2021_10 partition of sales_2021
for values from ('2021-10-01') to ('2021-11-01');
create table sales_2021_11 partition of sales_2021
for values from ('2021-11-01') to ('2021-12-01');
create table sales_2021_12 partition of sales_2021
for values from ('2021-12-01') to ('2022-01-01');

上面咱们通过 \d+ 查看 sale 表，能够看到分区 sales_2021 比其它分区多一个 PARTITIONED 属性，阐明它是一个子分区。


                                          Partitioned table "postgres.sales"
   Column    |  Type   | Collation | Nullable |              Default              | Storage | Stats target | Description 
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('sales_id_seq'::regclass) | plain   |              | 
 sales_count | integer |           |          |                                   | plain   |              | 
 sales_date  | date    |           | not null |                                   | plain   |              | 
Partition key: RANGE (sales_date)
Partitions: sales_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            sales_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            sales_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            sales_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONED

咱们能够通过 \d+ 查看 sales_2021：


                                         Partitioned table "postgres.sales_2021"
   Column    |  Type   | Collation | Nullable |              Default              | Storage | Stats target | Description 
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('sales_id_seq'::regclass) | plain   |              | 
 sales_count | integer |           |          |                                   | plain   |              | 
 sales_date  | date    |           | not null |                                   | plain   |              | 
Partition of: sales FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((sales_date IS NOT NULL) AND (sales_date >= '2021-01-01'::date) AND (sales_date < '2022-01-01'::date))
Partition key: RANGE (sales_date)
Partitions: sales_2021_01 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01'),
            sales_2021_02 FOR VALUES FROM ('2021-02-01') TO ('2021-03-01'),
            sales_2021_03 FOR VALUES FROM ('2021-03-01') TO ('2021-04-01'),
            sales_2021_04 FOR VALUES FROM ('2021-04-01') TO ('2021-05-01'),
            sales_2021_05 FOR VALUES FROM ('2021-05-01') TO ('2021-06-01'),
            sales_2021_06 FOR VALUES FROM ('2021-06-01') TO ('2021-07-01'),
            sales_2021_07 FOR VALUES FROM ('2021-07-01') TO ('2021-08-01'),
            sales_2021_08 FOR VALUES FROM ('2021-08-01') TO ('2021-09-01'),
            sales_2021_09 FOR VALUES FROM ('2021-09-01') TO ('2021-10-01'),
            sales_2021_10 FOR VALUES FROM ('2021-10-01') TO ('2021-11-01'),
            sales_2021_11 FOR VALUES FROM ('2021-11-01') TO ('2021-12-01'),
            sales_2021_12 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01')

总结
1.MogDB 目前只反对申明式分区，反对范畴分区、列表分区、哈希分区以及 INTERVAL-RANGE 的主动扩大距离分区。PostgreSQL 反对继承及申明式分区，不反对主动扩大距离分区。

2. 主动扩大距离分区的分区字段目前只反对工夫类型(date 或 timestamp)。

3.MogDB 目前不反对子分区，PostgreSQL 反对申明式子分区。

4. 对于申明式分区的分区来说，分区必须具备和分区表正好雷同的列汇合，表构造必须严格统一，而在表继承中，子表能够有父表中没有呈现过的额定列，同时表继承容许多继承。

墨天轮原文链接：https://www.modb.pro/db/49865…（复制到浏览器或者点击“浏览原文”立刻查看）

对于作者
彭冲，云和恩墨 PG 技术顾问，网名“多米爸比”，PG 社区认证专家，中国首期 PostgreSQL ACE Partner，多年从事基于 PostgreSQL 数据库的软件研发，擅长于 PL/PGSQL 业务迁徙及优化，Oracle 到 PostgreSQL 的迁徙降级，异构数据库整合；作为墨天轮 PostgreSQL 实际专栏作者，热衷于 PostgreSQL 实际技术分享，在本人的岗位踊跃推广 PostgreSQL，致力为 PG 社区多做贡献。

正文完
数据库
发表至： 数据库
2022-01-19
 0
关于数据库:Oracle案例ORA00600-internal-error-code-arguments-4187
关于数据库:BTM-382-数据库管理
关于数据库:开源大数据热力报告StarRocks摘得数据查询与分析方向增速第一
关于数据库:DBA100人白鳝一直往上走从程序员到数据库专家