oushu学习心得-3.2.4 表分区

表分区的好处:
1.查询优化器可以针对分区表进行优化,如果查询只涉及到某些分区,
则查询计划只需要扫描这些分区,从而加速查询;

2.如果我们按照日期进行分区的话,我们可以简单的加入分区和删除过期的分区。

分区表的目的是提高查询性能,但并非所有的表都适合做分区,只有大型事实表,
经常使用特定条件,日期,地区等,查询数据、维护历史数据、数据分布均匀的情况,
可通过分区策略大大提升使用性能,且分区数不易过多,否则会影响维护和管理工作速度

OushuDB 支持基于 Range 和 List 的两种分区方式:

Range 分区:
表被一个或者多个关键列分区成“范围”,这些范围在不同的分区里没有重叠,比如依据日期、
价格数值范围进行分区;

List 分区:
依据一个值的列表进行分区,比如依据地区列表进行分区;

创建新的分区表:CREATE TABLE ... PARTITION BY
1.确定分区类型
2.选定分区字段
3.确定分区级别

OushuDB 支持子分区 SUBPARTITION,可以按照不同维度实现多级分区

--创建分区从 2008 年到 2009 年每月创建一个分区
CREATE TABLE sales(id int,date date,amt decimal(10,2))
PARTITION BY RANGE(date)
(START(date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month'));

查看表信息:
\d+ sales

testdb=# \d+ sales
Orc Table "public.sales"
Column | Type | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
id | integer | | plain |
date | date | | plain |
amt | numeric(10,2) | | main |
Child tables: sales_1_prt_1,
sales_1_prt_10,
sales_1_prt_11,
sales_1_prt_12,
sales_1_prt_2,
sales_1_prt_3,
sales_1_prt_4,
sales_1_prt_5,
sales_1_prt_6,
sales_1_prt_7,
sales_1_prt_8,
sales_1_prt_9
Has OIDs: no
Options: appendonly=true, orientation=orc, compresstype=lz4
Table Bucket Number: 8
Distributed randomly
Partition by: (date)

我们也可以用显式声明子分区并指定子表名字
CREATE TABLE sales_exp(id int,date date,amt decimal(10,2))
PARTITION BY RANGE(date)
(PARTITION Jan08 START (date '2008-01-01') INCLUSIVE,
PARTITION Feb08 START (date '2008-02-01') INCLUSIVE,
PARTITION Mar08 START (date '2008-03-01') INCLUSIVE,
PARTITION Apr08 START (date '2008-04-01') INCLUSIVE,
PARTITION May08 START (date '2008-05-01') INCLUSIVE,
PARTITION Jun08 START (date '2008-06-01') INCLUSIVE,
PARTITION Jul08 START (date '2008-07-01') INCLUSIVE,
PARTITION Aug08 START (date '2008-08-01') INCLUSIVE,
PARTITION Sep08 START (date '2008-09-01') INCLUSIVE,
PARTITION Oct08 START (date '2008-10-01') INCLUSIVE,
PARTITION Nov08 START (date '2008-11-01') INCLUSIVE,
PARTITION Dec08 START (date '2008-12-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE );

另外一个根据 Range 分区的例子,这次使用的是整型列进行分区:
CREATE TABLE rank(id int,rank int,year int,gender char(1),count int)
PARTITION BY RANGE(year)
(START (2001) END (2008) EVERY (1)
DEFAULT PARTITION extra);

DEFAULT PARTITION :在不满足其他分区的条件下,数据会被插入 DEFAULT PARTITION;

定义 List 分区:
列表分区的分区字段可以是任意可进行等于比较的数据类型,且允许多列组合进行分区,
而范围分区只允许选定一列作分区字段;
列表分区时必须显式为每个分区申明分区值;

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

定义多级分区:
多级分区使用 PARTITION BY 和 SUBPARTITION BY 实现,使用 SUBPARTITION TEMPLATE 可以保证
所有的子分区都一样,包括后续新增的子分区;

演示将范围分区和列表分区进行组合,并使用多级分区实现:
CREATE TABLE mult_mersurement(
city text,
logdate date,
peaktemp int,
unitsales int
) WITH (APPENDONLY = true, OIDS = FALSE, ORIENTATION = orc)
PARTITION BY RANGE (logdate)
SUBPARTITION BY LIST (city)
SUBPARTITION TEMPLATE
(SUBPARTITION beijing VALUES('BJ'),
SUBPARTITION shanghai VALUES('SH'),
SUBPARTITION guangzhou VALUES('GZ'),
SUBPARTITION shenzhen VALUES('SZ'),
DEFAULT SUBPARTITION other)
(START (date '2020-01-01') INCLUSIVE END (date '2020-02-01')
EXCLUSIVE EVERY (INTERVAL '1 month'));

或者可以按照年、月进行多级分区组合:
CREATE TABLE ym_measurement(
city_id int not null,
r_year int,
r_month int,
peaktemp int,
unitsales int
) format 'magmaap'
PARTITION BY RANGE (r_year)
SUBPARTITION BY RANGE (r_month)
SUBPARTITION TEMPLATE
(START (1) END (13) EVERY (1),DEFAULT SUBPARTITION orther_months)
(START (2017) END (2020) EVERY (1),DEFAULT PARTITION orther_years);

我们也可以对上述三个条件进行组合可创建三级分区:
CREATE TABLE mult_measurement_3(
city text,
r_year int,
r_month int,
peaktemp int,
unitsales int
) with (appendonly = true, orientation = parquet)
PARTITION BY RANGE (r_year)
SUBPARTITION BY RANGE (r_month)
SUBPARTITION TEMPLATE
(START (1) END (13) EVERY (1) , DEFAULT SUBPARTITION other_months)
SUBPARTITION BY LIST (city)
SUBPARTITION TEMPLATE
(SUBPARTITION beijing VALUES ('BJ'),
SUBPARTITION shanghai VALUES ('SH'),
SUBPARTITION guangzhou VALUES ('GZ'),
SUBPARTITION shenzhen VALUES ('SZ'),
DEFAULT SUBPARTITION other)
(START (2017) END (2020) EVERY (1) , DEFAULT PARTITION other_years);

注意:当我们在使用多级分区的时候系统会产生大量的小表,有些表可能没有数据或包含很少数据,
这样会对系统元数据管理产生过多压力,建议不要创建具有过多分区的表,
一般限制分区数在 100 或以内比较合理;

分区现有表:
如果我们需要对一张已有大表数据进行分区,操作步骤如下:
1.按照现有大表结构创建一个新的分区表
2.将现有大表中的数据 load 到新创建的分区表中
3.删除原来的大表
4.按照原来大表名称重命名分区表名称

假设已有表 measurement
CREATE TABLE measurement(
id int, logdate date
);

--创建分区表
CREATE TABLE measurement_cp (like measurement)
PARTITION BY RANGE (logdate)
(START (date '2020-01-01') INCLUSIVE END (date '2020-04-01')
EXCLUSIVE EVERY (INTERVAL '1 month'));

--插入数据
INSERT INTO measurement_cp SELECT * FROM measurement;

--删除原表
DROP TABLE measurement;

--重命名分区表
ALTER TABLE measurement_cp RENAME TO measurement;

--

评论
    test