鸿 网 互 联 www.68idc.cn

当前位置 : 主页 > 数据库 > mysql > >

Greenplum或DeepGreen数据库对象的使用和管理

来源:互联网 作者:佚名 时间:2016-06-21 11:35
1. 创建文件空间 [gpadmin@cdha ~]$ gpfilespace -o gpfilespace_config #当前目录下生成gpfilespace_config文件 Enter a name for this filespace zhangyun_fs #手工输入 primary location 1 /dbfast_zhangyun_tbs/primary #手工输入 primary location 2 /db
1. 创建文件空间
[gpadmin@cdha ~]$ gpfilespace -o gpfilespace_config   #当前目录下生成gpfilespace_config文件
Enter a name for this filespace> zhangyun_fs          #手工输入
primary location 1> /dbfast_zhangyun_tbs/primary      #手工输入
primary location 2> /dbfast_zhangyun_tbs/primary      #手工输入
mirror location 1> /dbfast_zhangyun_tbs/mirror        #手工输入
mirror location 2> /dbfast_zhangyun_tbs/mirror        #手工输入
master location> /dbfast_zhangyun_tbs/master          #手工输入


[gpadmin@cdha ~]$ gpfilespace -c gpfilespace_config   #基于gpfilespace_config配置文件创建文件空间




2. 创建表空间
CREATE TABLESPACE zhangyun_tbs FILESPACE zhangyun_fs;
允许普通的DB User来使用该表空间, 可以将CREATE权限授予相应的用户。
GRANT CREATE ON TABLESPACE zhangyun_tbs TO zhangyun;


或创建表空间时直接授权给用户
CREATE TABLESPACE zhangyun_tbs OWNER zhangyun FILESPACE zhangyun_fs;


3. 创建数据库
表空间创建好之后,我们就可以基于表空间来创建数据库
create database zhangyun_db with OWNER zhangyun template template0 encoding 'utf8' tablespace zhangyun_tbs ;


4. 使用表空间存储 DB 对象 
表、索引、甚至整个DB都可以指定在特定的表空间。


若要如此,拥有给定表空间CREATE权限的Role必须通过表空间的名称作为相关命令的参数来实现,下
面是创建一个zhangyun_tbs表空间上的表:
CREATE TABLE test(id int) TABLESPACE zhangyun_tbs;


或者使用缺省表空间参数default_tablespace来设定:
SET default_tablespace = zhangyun_tbs;
CREATE TABLE test(id int);


5. 查看现有的表空间和文件空间
每个GPDB系统都有两个缺省的表空间: pg_global(用以存储系统日志信息)和pg_default(用以存储template1和template0模版DB的缺省表空间)。 
这些表空间使用系统缺省的文件空间pg_system(系统初始化时使用的数据目录data directory)。


要获取文件空间的信息,可以查看系统日志表pg_filespace和pg_filespace_entry。
可通过与pg_tablespace关联查看表空间的完整定义:
SELECT spcname as tblspc, fsname as filespc, fsedbid as seg_dbid, fselocation as datadir
FROM pg_tablespace pgts, pg_filespace pgfs, pg_filespace_entry pgfse
WHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY tblspc, seg_dbid;


    tblspc    |   filespc   | seg_dbid |             datadir              
--------------+-------------+----------+----------------------------------
 pg_default   | pg_system   |        1 | /dbfast0/master/dg-1
 pg_default   | pg_system   |        2 | /dbfast1/primary/dg0
 pg_default   | pg_system   |        3 | /dbfast2/primary/dg1
 pg_default   | pg_system   |        4 | /dbfast1/primary/dg2
 pg_default   | pg_system   |        5 | /dbfast2/primary/dg3
 pg_default   | pg_system   |        6 | /dbfast1/mirror/dg0
 pg_default   | pg_system   |        7 | /dbfast2/mirror/dg1
 pg_default   | pg_system   |        8 | /dbfast1/mirror/dg2
 pg_default   | pg_system   |        9 | /dbfast2/mirror/dg3
 pg_global    | pg_system   |        1 | /dbfast0/master/dg-1
 pg_global    | pg_system   |        2 | /dbfast1/primary/dg0
 pg_global    | pg_system   |        3 | /dbfast2/primary/dg1
 pg_global    | pg_system   |        4 | /dbfast1/primary/dg2
 pg_global    | pg_system   |        5 | /dbfast2/primary/dg3
 pg_global    | pg_system   |        6 | /dbfast1/mirror/dg0
 pg_global    | pg_system   |        7 | /dbfast2/mirror/dg1
 pg_global    | pg_system   |        8 | /dbfast1/mirror/dg2
 pg_global    | pg_system   |        9 | /dbfast2/mirror/dg3
 zhangyun_tbs | zhangyun_fs |        1 | /dbfast_zhangyun_tbs/master/dg-1
 zhangyun_tbs | zhangyun_fs |        2 | /dbfast_zhangyun_tbs/primary/dg0
 zhangyun_tbs | zhangyun_fs |        3 | /dbfast_zhangyun_tbs/primary/dg1
 zhangyun_tbs | zhangyun_fs |        4 | /dbfast_zhangyun_tbs/primary/dg2
 zhangyun_tbs | zhangyun_fs |        5 | /dbfast_zhangyun_tbs/primary/dg3
 zhangyun_tbs | zhangyun_fs |        6 | /dbfast_zhangyun_tbs/mirror/dg0
 zhangyun_tbs | zhangyun_fs |        7 | /dbfast_zhangyun_tbs/mirror/dg1
 zhangyun_tbs | zhangyun_fs |        8 | /dbfast_zhangyun_tbs/mirror/dg2
 zhangyun_tbs | zhangyun_fs |        9 | /dbfast_zhangyun_tbs/mirror/dg3
(27 rows)




6. 创建与管理模式
模式(Schema)是在DB内组织对象的一种逻辑结构。 模式可以允许用户在一个DB内不同的模式之间使用相同Name的对象(比如Table,View等)。


缺省"Public"模式
每个新创建的DB都有一个缺省的模式public。如果没有创建其他的模式,在创建DB对象时将缺省使用public模式。 缺省情况下所有的ROLE(User)都有public
模式下的CREATE和USAGE权限。而在创建其他模式时,需要将该模式授权给相关的ROLE(User)。


创建模式
CREATE SCHEMA zhangyun_schema;
CREATE SCHEMA zhangyun_schema AUTHORIZATION zhangyun;


模式搜索路径
要知道在DB的哪个模式下搜索需要的对象,可以通过明确指定模式名的方式来实现。
SELECT * FROM zhangyun_schema.mybigtable;
若不想通过指定模式名称的方式来实现,可以通过设置search_path参数来完成。
1) 设置模式搜索路径
该参数可以通过ALTER DATABAST命令修改DB的模式搜索路径
ALTER DATABASE zhangyun_db SET search_path TO zhangyun_schema, public, pg_catalog;


还可以通过ALTER ROLE命令修改特定ROLE(User)的模式搜索路径。例如:
ALTER ROLE zhangyun SET search_path TO zhangyun_schema, public, pg_catalog;


2) 查看当前的模式
SELECT current_schema();
SHOW search_path;


系统模式
下面的这些系统级别的模式在所有的DB中都存在:
1) pg_catalog模式
存储着系统日志表(System Catalog Table)、 内置类型(Type)、函数(Function)和运算符(Operator)。
该模式无论是否在search_path中指明,都存在search_path中。


2) information_schema模式
由一个标准化视图构成,其包含DB中对象的信息。该视图用于以标准化的方法从系统日志表中查看系统信息。


3) pg_toast模式
一个储存大对象的地方(那些超过页面尺寸(page size)的记录)。 该模式仅供GPDB系统内部使用,通常不建议管理员或者任何用户访问。


4) pg_bitmapindex模式
一个储存bitmap index对象的地方(值列表等)。该模式仅供GPDB系统内部使用,通常不建议管理员或者任何用户访问。


5) pg_aoseg模式
一个储存append-only表的地方。该模式仅供GPDB系统内部使用,通常不建议管理员或者任何用户访问。


6) gp_toolkit模式
一个管理用的模式,可以查看和检索系统日志文件和其他的系统信息。 gp_toolkit视图包含一些外部表、视图、函数, 
可以通过SQL的方式访问它们。 gp_toolkit视图对于所有DB User都是可以访问的。




7. 创建与管理表
创建表
1) 选择Column的数据类型
Column的数据类型决定了其可以储存什么类型的数据值。通常都希望用最小的空间储存数据。
对于字符串,在多数情况下,应该选择使用TEXT或者VARCHAR而不是CHAR。
对于Numeric类型来说,应该尽量选择更小的数据类型来适应数据。比如,选择BIGINT类型来存储SMALLINT类型范围内的数值,会造成空间的大量浪费。
对于打算用来做表关联的Column来说,应该考虑选择相同的数据类型。


2) 设置Table和Column的约束
检查约束
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );


非空约束
CREATE TABLE products (product_no integer NOT NULL, name text NOT NULL, price numeric );


唯一约束
在GPDB中使用唯一约束存在强制条件, Table必须是HASH分布的(而不是DISTRIBUTED RANDOMLY),并且唯一约束的Column集合必须完整包含所有的DK Column。
CREATE TABLE products (product_no integer UNIQUE, name text, price numeric)
DISTRIBUTED BY (product_no);


主键约束
在GPDB中使用主键约束存在强制条件, Table必须是HASH分布的(而不是DISTRIBUTED RANDOMLY),并且主键约束的Column集合必须完整包含所有的DK Column。
CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric)
DISTRIBUTED BY (product_no);


3) 声明分布键
在创建Table时有一个额外的子句用以指明分布策略。 如果在创建Table时没有指明DISTRIBUTED BY或者DISTRIBUTED RANDOMLY子句, 
GPDB将会依次考虑使用主键(假如该Table有的话)或者第一个字段作为HASH分布的DK。
CREATE TABLE products (name varchar(40), prod_id integer, supplier_id integer)
DISTRIBUTED BY (prod_id);


CREATE TABLE random_stuff (things text, doodads text, etc text)
DISTRIBUTED RANDOMLY;


4) 选择表的存储模式


######选择堆存储或者只追加存储(Heap Storage or Append-Optimized Storage)######
Heap Storage:
缺省情况下GPDB使用与PostgreSQL相同的存储模式为堆存储。堆存储模式在OLTP类型工作负载的DB中很常用,常用于数据在初始装载后经常变化。 
UPDATE和DELETE操作需要对ROW级别做版本控制从而确保DB事务处理的可靠性。堆表更适合一些小表,比如维表,这种表可能会在初始化装载后经常更新数据。
因此,对于经常update,delete,或单行insert操作,或者并行的update,delete和insert操作,都适合堆表存储。


创建堆表
行存堆表是缺省的存储模式:
CREATE TABLE test (id int, name text) DISTRIBUTED BY (id);




Append-Optimized Storage:
GPDB还提供了一种称之为只追加存储模式的表。AO表更适合数据仓库中非规范化事实表,这些表通常都是系统中最大的表。
AO表达到了更精简和优化的页面存储结构。该存储模式强化了批量数据装载的性能。不推荐一行一行的使用INSERT语句来装载数据。
目前版本AO表已经支持DELETE和UPDATE操作。
因此,对于初始数据导入,以及随后的批量insert,而且不频繁update的表适合使用AO表。不要在AO表上执行单条的insert,update或delete操作。
并行的批量insert操作也是可以的,但是不要执行并行批量的update或delte操作。不适合主要的原因是AO表的update或delete后的row占用的空间不能有效地
回收和重用。因此AO只适合数据一次导入的大表,update不频繁,并且多次查询的情况。


** 创建只追加表
CREATE TABLE test (id int, name text) WITH (appendonly=true);
演示delete和update对ao表的操作:
zhangyun_db=# insert into test values(1,'hello');
INSERT 0 1
zhangyun_db=# insert into test values(2,'spark');
INSERT 0 1
zhangyun_db=# update test set name = 'scala' where id = 1;
UPDATE 1
zhangyun_db=# select * from test;
 id | name  
----+-------
  1 | scala
  2 | spark
(2 rows)
zhangyun_db=# delete from test where id = 1;
DELETE 1
zhangyun_db=# select * from test;
 id | name  
----+-------
  2 | spark
(1 row)




######选择行存储或者列存储(Row or Column Orientation)######
考虑行存的情况:
(1) 表数据的更新
如果一张表在装载完之后一定有更新操作,那么就选择行存表。


(2) 经常做INSERT操作
如果经常有数据被INSERT,考虑选择行存表。列存表对于写操作不是最优的,
因为每条数据都需要被写到磁盘的多个位置(列存表的每列存储于不同的磁盘文件,而行存表是存储在同一个磁盘文件)。


(3) 查询涉及的COLUMN数量
若通常在SELECT或者WHERE中涉及TABLE的全部或大部COLUMN,考虑选择行存表。行存适合在WHERE或HAVING中对单列做聚合操作:
SELECT SUM(salary)...
SELECT AVG(salary)... WHERE salary > 10000


或者在WHERE条件中使用单个COLUMN条件且返回相对少量的ROW:
SELECT salary, dept ... WHERE state='CA'


创建行存表
在CREATE TABLE时使用WITH子句来指明TABLE的存储模式。如果没有指明,该表将会是缺省的行存堆表。




考虑列存的情况:
列式存储是对读操作进行优化的,而对写操作没有优化。对于row的列值被放在磁盘的不同位置。列存表在只访问宽表的很少COLUMN的查询中可以表现出更好的性能。
另外一个性能提升的地方是,列式存储的每列都是相同格式的数据值,压缩效率高,占用磁盘空间少,减少磁盘IO。


创建列存表
使用列存的TABLE必须是AO表。
CREATE TABLE test (id int, name text) WITH (appendonly=true, orientation=column)
DISTRIBUTED BY (id);


5) 使用压缩(只可以是AO表)
在GPDB中, AO表有两种库内压缩可选,一种是表级的压缩,另外一种是COLUMN级别的压缩,前者应用到整个TABLE,后者应用到指定的COLUMN。
在选择COLUMN级别压缩时,可以为不同的COLUMN选择不同的压缩算法。下表是可用的压缩算法:


表导向  |  可用压缩类型    |    支持压缩算法
--------|------------------|-------------------------------
行      |  表级别          |    ZLIB 和 QUICKLZ
--------|------------------|---------------------------------
列      | 列级别 和 表级别 |    RLE_TYPE、 ZLIB 和 QUICKLZ


使用库内压缩要求Segment系统具备强劲的CPU来压缩和解压缩数据。不要在压缩文件系统使用压缩AO表。如果Instance数据目录是压缩文件系统,不要压缩使用AO表。


QUICKLZ压缩通常适用于CPU能力一般的情况,其压缩速度比ZLIB快,但压缩率不如ZLIB。 
相反的, ZLIB提供更高的压缩率,但压缩速度较低。 在压缩级别为1时, QUICKLZ与ZLIB可能获得差不多的压缩率(但压缩速度ZLIB可能差一些)。
但在6级以上的ZLIB在压缩率方面的优势显著高于QUICKLZ(但压缩速度也因此显著的低于QUICKLZ)。


QUICKLZ压缩模式只有一种压缩级别,没有级别选项可以选择。而ZLIB压缩模式有1 – 9个压缩级别可选。




创建压缩表
CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5);


检查AO表的压缩与分布情况
------------------------------------------------------------------------------------------------------------------------
select get_ao_distribution(name);      |   Set of (dbid, tuplecount) rows | 展示 AO 表的分布情况,每ROW 对应 
select get_ao_distribution(oid);       |                                  | Segment Instance 的dbid 与储存的数据行数。
------------------------------------------------------------------------------------------------------------------------
select get_ao_compression_ratio(name); |   float8                         | 计算出 AO 表的压缩率。
select get_ao_compression_ratio(oid);  |                                  | 如果该信息未得到,将返回-1 值
------------------------------------------------------------------------------------------------------------------------


示例:
select get_ao_distribution('foo'); #每个Instance存储foo表的ROW数量
 get_ao_distribution 
---------------------
 (3,2500037)
 (0,2499970)
 (2,2499974)
 (1,2500033)


select get_ao_compression_ratio('foo');
 get_ao_compression_ratio 
--------------------------
                     4.76 #意味着foo表未压缩状态下的储存尺寸是压缩下的储存尺寸的4倍多
(1 row)


下面演示通过oid查看:
select oid from pg_class where relname = 'foo';
  oid  
-------
 25626


select get_ao_distribution(25626);
 get_ao_distribution 
---------------------
 (0,2499970)
 (1,2500033)
 (3,2500037)
 (2,2499974)


select get_ao_compression_ratio(25626);
 get_ao_compression_ratio 
--------------------------
                     4.76




支持运行长度编码
GPDB已支持COLUMN级别的运行长度编码(Run-length Encoding /RLE)压缩算法。 RLE是一种将连续重复的数据作为一种计数方式存储的压缩算法。
RLE对于重复元素是很有效的。比如,在一个表中有两个COLUMN,一个日期COLUMN和一个描述COLUMN,其中包含200000个date1和400000个data2,
RLE压缩处理这种数据为类似data1 200000 data2 400000这样的效果。对于那些没有很多重复值的数据RLE是不适合的,而且还可能会显著的增加存储文件的尺寸。
RLE压缩有4种级别。级别越高,压缩效率越高,但压缩速度也会越低。
               
使用列级压缩
在CREATE TABLE、 ALTER TABLE和CREATE TYPE命令中包含对COLUMN设置压缩类型、压缩级别和块尺寸(Block Size)的选项。 这些参数统称为存储参数。
存储参数可用于行导向和列导向的AO表。 下面列举这3种存储参数及每种参数的可选值。


名称                |          解释            |      可选值
COMPRESSTYPE        |        使用的压缩类型    |      ZLIB(更高压缩)
                    |                          |      QUICKLZ(更快压缩)
                    |                          |      RLE_TYPE(运行长度编码)
                    |                          |      none(无压缩、缺省值)
----------------------------------------------------------------------------------------------------------
COMPRESSLEVEL       |        压缩级别          |      ZLIB 为 1-9 级可选
                    |                          |      1 级压缩较快但压缩率较低,9 级压缩较慢但压缩率较高
                    |                          |      QUICKLZ 仅 1 个级别可选(缺省不需指定)
                    |                          |      RLE_TYPE 为 1-4 级可选
                    |                          |      1 级压缩较快但压缩率较低,4 级压缩较慢但压缩率较高
-----------------------------------------------------------------------------------------------------------
BLOCKSIZE           |        表的存储块大小    |      8192 – 209715(8K – 2M)该值必须是 8192 的倍数
-----------------------------------------------------------------------------------------------------------
使用存储参数的格式如下:
[ ENCODING ( storage_directive [,…] ) ]
这里ENCODING关键字是必须的, 存储参数包含3个部分:参数名称、等于号、参数值。
如下面的CREATE TABLE语句所示:
一般用法:
column_name data_type ENCODING ( storage_directive [, … ] ), …
COLUMN column_name ENCODING ( storage_directive [, … ] ), …
DEFAULT COLUMN ENCODING ( storage_directive [, … ] )
例如:
C1 char ENCODING (compresstype=quicklz, blocksize=65536)
COLUMN C1 ENCODING (compresstype=quicklz, blocksize=65536)
DEFAULT COLUMN ENCODING (compresstype=quicklz)


示例:
CREATE TABLE T1 (
    c1 int ENCODING (compresstype=zlib),
    c2 char ENCODING (compresstype=quicklz, blocksize=65536),
    c3 char)
WITH (appendonly=true, orientation=column);


CREATE TABLE T2 (
    c1 int ENCODING (compresstype=zlib),
    c2 char ENCODING (compresstype=quicklz, blocksize=65536),
    c3 char, COLUMN c3 ENCODING (RLE_TYPE) )
WITH (appendonly=true, orientation=column);


CREATE TABLE T3 (
    c1 int ENCODING (compresstype=zlib),
    c2 char ENCODING (compresstype=quicklz, blocksize=65536),
    c3 char,
    COLUMN c3 ENCODING (compresstype=RLE_TYPE))
WITH (appendonly=true, orientation=column)
PARTITION BY RANGE (c3) (
    START ('1900-01-01'::DATE) END ('2100-12-31'::DATE),
    COLUMN c3 ENCODING (zlib) #实际上c3使用的是ZLIB压缩而非RLE_TYPE压缩
);


CREATE TABLE T4 (
    c1 int ENCODING (compresstype=zlib),
    c2 char,  #将从DEFAULT COLUMN ENCODING子句继承压缩方式(QUICKLZ)和块尺寸(65536)
    c3 char,  #压缩方式(RLE_TYPE), 块尺寸(65536)从DEFAULT COLUMN ENCODING子句继承而来
    c4 smallint ENCODING (compresstype=none), #而块尺寸没有显式的复写设置,因此,其块尺寸为65536.
    DEFAULT COLUMN ENCODING (compresstype=quicklz, blocksize=65536),
    COLUMN c3 ENCODING (compresstype=RLE_TYPE) )
WITH (appendonly=true, orientation=column);




CREATE TABLE T5 (
    i int,
    j int,
    k date,
    DEFAULT COLUMN ENCODING (blocksize=1048576) --1MB
) WITH (appendonly = true, orientation=column)
PARTITION BY RANGE(k)
  SUBPARTITION BY LIST(j)
    SUBPARTITION TEMPLATE
    (
      SUBPARTITION one_two VALUES(1, 2) COLUMN j ENCODING (compresstype=RLE_TYPE),
      SUBPARTITION rest VALUES(3, 4, 5, 6, 7, 8, 9) COLUMN j ENCODING (compresstype=zlib, compresslevel=9),
      DEFAULT COLUMN ENCODING (compresstype=quicklz)
    )
(
    START (date '2011-01-01') END (date '2011-12-31')
    EVERY (interval '1 day')
);




通过TYPE命令的方式设置压缩配置
使用精简的方式创建压缩表:
CREATE TABLE t2 (c1 comptype) WITH (APPENDONLY=true, ORIENTATION=column);
这里的comptype的定义为:
CREATE TYPE comptype (
    internallength = 4,
    input = comptype_in,
    output = comptype_out,
    alignment = int4,
    default = 123,
    passedbyvalue,
    compresstype="quicklz",
    blocksize=65536,
    compresslevel=1
);


不建议使用这种不明显的方式,虽然在定义TABLE时看起来精简了不少,但对于别人来说,阅读和理解可能都存在障碍。另外替代原生TYPE的定义未必适应所有情况。建议慎用。




选择块尺寸
在一个TABLE中,每个块尺寸意味着相应数量byte的存储。块尺寸必须在8192到2097152之间,并且必须是8192的整数倍。缺省值为32768。 
需要注意的是,指定大的块大小会消耗大量的内存资源。块尺寸决定着存储层的尺寸, 在GP中,每个块作为一部分数据来维护,因此多分区表和列存储表都会消耗更多的内存。




6) 改变表的分布
下面的命令在所有Segment之间按照customer_id作为DK重分布sales表:
ALTER TABLE sales SET DISTRIBUTED BY (customer_id);


在修改TABLE的HASH分布时,表数据会自动重新分布。如果将分布策略改为随机分布时也会重新分布数据。例如:


测试:
template1=# \d+ foo
            Append-Only Table "public.foo"
 Column |  Type   | Modifiers | Storage  | Description 
--------+---------+-----------+----------+-------------
 a      | integer |           | plain    | 
 b      | text    |           | extended | 
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Has OIDs: no
Options: appendonly=true, compresstype=zlib, compresslevel=5
Distributed by: (a)


template1=# select get_ao_distribution('foo');
 get_ao_distribution 
---------------------
 (1,2500033)
 (0,2499970)
 (2,2499974)
 (3,2500037)
(4 rows)


template1=# ALTER TABLE foo SET DISTRIBUTED RANDOMLY; #重新设置分布策略
 
template1=# select get_ao_distribution('foo');
 get_ao_distribution 
---------------------
 (1,2500003)
 (3,2500004)
 (0,2500002)
 (2,2500005)
(4 rows)


template1=# ALTER TABLE foo SET WITH (REORGANIZE=TRUE); #重分布数据
template1=# select get_ao_distribution('foo');
 get_ao_distribution 
---------------------
 (3,2500004)
 (0,2500003)
 (1,2500004)
 (2,2500003)
(4 rows)




template1=# ALTER TABLE foo SET DISTRIBUTED BY (a);  #重新设置分布策略
ALTER TABLE
template1=# select get_ao_distribution('foo');
 get_ao_distribution 
---------------------
 (1,2500033)
 (2,2499974)
 (3,2500037)
 (0,2499970)
(4 rows)


重分布表数据
对于随机分布策略或者不改变分布策略的表,要重分布TABLE的数据,使用REORGANIZW=TRUE。
这在处理数据倾斜问题时可能是很必要的,在添加新的Segment节点资源时也是必要的。
ALTER TABLE sales SET WITH (REORGANIZE=TRUE);
该命令会在Instance之间按照现有的分布策略(包括随机分布策略)重新平衡表中数据。


7) 修改表的存储模式
在TABLE被创建之后,修改表的存储模式是不可能的。存储模式只能在CREATE TABLE时被指定。
如果要修改现有表的存储模式,必须使用正确的存储选项重建该表,重新加载数据到新的表,删除旧的表,修改新表为旧的表名。另外还必须重新授权表的权限。


CREATE TABLE sales2 (LIKE sales)
WITH (appendonly=true, compresstype=quicklz, compresslevel=1, orientation=column);
INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;




8) 在现有表上添加压缩列
可以使用ALTER TABLE命令来添加一个压缩列。
ALTER TABLE T1 ADD COLUMN c4 int DEFAULT 0 ENCODING (COMPRESSTYPE=zlib);


9) 继承压缩设置
创建一个带子分区设置的表,然后增加一个分区:
CREATE TABLE ccddl (i int, j int, k int, l int)
WITH (APPENDONLY = TRUE, ORIENTATION=COLUMN)
PARTITION BY range(j)
  SUBPARTITION BY list (k)
    SUBPARTITION template(
      SUBPARTITION sp1 values(1, 2, 3, 4, 5),
      COLUMN i ENCODING(COMPRESSTYPE=ZLIB),
      COLUMN j ENCODING(COMPRESSTYPE=lz4), #Vitesse DeepGreen does not support quicklz.  Please use lz4 or set vitesse.lz4_replace_quicklz. 
      COLUMN k ENCODING(COMPRESSTYPE=ZLIB),
      COLUMN l ENCODING(COMPRESSTYPE=ZLIB))
( PARTITION p1 START(1) END(10), 
  PARTITION p2 START(10) END(20)
);


ALTER TABLE ccddl ADD PARTITION p3 START(20) END(30);






10) 分区大表
表分区用以解决特别大的表的问题,比如事实表,解决办法就是将表分成很多小且更容易管理的部分。
在CREATE TABLE时使用PARTITION BY(以及可选的SUBPARTITION BY)子句来做分区。在GPDB中对一张表做分区,实际上是创建了一张顶层(父级)表和多个低层
(子级)表。 在内部, GPDB在顶级表与低级表之间创建了继承关系(类似于PostgreSQL中的继承/INHERIT功能)。


GPDB支持范围(根据数值型的范围分割数据,比如日期或价格)分区和列表(根据值列表分区,比如区域或生产线)分区,或者两种类型的结合。


表分区是一种大表逻辑切分和数据仓库任务的工具。分区本身不会改变Instance间物理上的数据分布规律。


决定表分区的策略:
表是否足够大? 
对目前的性能不满意?
查询条件是否能匹配分区条件? 
数据仓库是否需要滚动历史数据? 
按照某个规则数据是否可以被均匀的分拆? 


定义日期范围分区表
日期范围分区表使用单个date或者timestamp字段作为分区键。 
可以通过使用START值、 END值和EVERY子句定义分区增量让GPDB自动产生分区。
缺省情况下, START值总是被包含而END值总是被排除。
CREATE TABLE sales (id int, date date, amt decimal(10,2)) 
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 day') );




不过也可以为每个分区单独指定名称。比如:
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
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 
);


上面的分区的范围都是连续的,如果不连续需要指定end值。


定义数字范围分区表
数字范围分区表使用单个数字列作为分区键。例如:
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2001) END (2008) EVERY (1),
  DEFAULT PARTITION extra 
);




定义列表分区表
列表分区表可以使用任何数据类型的列作为分区键,分区规则使用等值比较。
列表分区可以使用多个COLUMN(组合起来)作为分区键,而范围分区只允许使用单独COLUMN作为分区键。对于列表分区,必须为每个分区指定相应的值。


CREATE TABLE rank (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 );




定义多级分区表
使用subpartition template来确保每个分区具有相同的子分区结构,尤其是对那些后增加的分区来说。
CREATE TABLE sales (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 asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe'),
    DEFAULT SUBPARTITION other_regions
  )
( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month'), 
  DEFAULT PARTITION outlying_dates 
);




下面是一个3级分区表的例子,这里表sales被分区为年、月、区域。 SUBPARTITION TEMPLATE子句确保每个年分区有相同的子分区结构。
另外,每个级别的分区都有一个默认分区:
CREATE TABLE sales (id int, year int, month int, day int, region text) DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
  SUBPARTITION TEMPLATE 
  (
    START (1) END (13) EVERY (1),
    DEFAULT SUBPARTITION other_months 
  )
SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE 
  (
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION asia VALUES ('asia'),
    DEFAULT SUBPARTITION other_regions 
  )
( START (2002) END (2010) EVERY (1), 
  DEFAULT PARTITION outlying_years 
);




将现有表分区
对已经创建的表是不能分区的。只能在CREATE TABLE的时候做分区。要想对现有的表做分区,只能重新创建一个分区表、重新装载数据到新的分区表中、删
掉旧表然后把新的分区表改为旧表的名称。还必须重新对TABLE做授权。
CREATE TABLE sales2 (LIKE sales)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month') 
);
INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;


分区表的限制
主键或者唯一约束必须包含表上的所有分区键。而唯一索引可以不包含分区键,
但是,其只对一个分区强制有效,而不是对整个分区表有效。




装载分区表
一旦创建了分区表,顶级表总是空的。数据值储存在最低层的表中。在多级分区表中,仅仅在层级最低的子分区中有数据。
在运行期间,查询规划器会扫描整个TABLE的层级结构并使用CHECK约束适配查询条件来决定哪些子表需要被扫描。
默认分区(只要该层级中存在)总是会被扫描。如果默认分区中包含数据,其会拖慢整体的扫表时间。


如果有必要,还可以直接把数据装载到子表中。还可以先创建一个中间表、装载数据、然后与分区表进行分区交换。这种分区交换的性能高于直接的COPY和INSERT。




查看分区设计
要查看分区表的设计情况,通过pg_partitions视图查看。
SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank
FROM pg_partitions WHERE tablename='sales2';


pg_partition_templates - 用以创建SUBPARTITION的SUBPARTITION template
pg_partition_columns – 用于分区的分区键




维护分区表
必须使用ALTER TABLE命令从顶级表来维护分区。 最常见的场景是根据日期范围的设计来维护数据时,删除旧分区并添加一个新的分区。
还有一种可能就是把旧的分区交换为压缩AO表以节省空间。 若在父表中存在默认分区,添加分区的操作只能是从默认分区拆分出一个新的分区。


由于分区不要求有名称,若分区没有名称,下面的表达式仍可以指定一个分区:
PARTITION FOR (value) or PARTITION FOR(RANK(number))
(1) 添加新分区
如果原有的分区表包含了subpartition template设计,新增的分区将根据该模版创建子分区。
CREATE TABLE sales (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 asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe'),
    DEFAULT SUBPARTITION other_regions
  )
( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month'), 
  DEFAULT PARTITION outlying_dates 
);


ALTER TABLE sales ADD PARTITION
START (date '2009-02-01') INCLUSIVE
END (date '2009-03-01') EXCLUSIVE;




如果在创建TABLE时没有subpartition template,在新增分区时需要定义子分区:
ALTER TABLE sales ADD PARTITION
START (date '2009-02-01') INCLUSIVE
END (date '2009-03-01') EXCLUSIVE
( SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'),
SUBPARTITION europe VALUES ('europe') );


子表的名称格式如下:
<父表名称>_<分区层级>_prt_<分区名称>


子表的名称不能通过直接执行ALTER表名来实现。但修改顶级表的名称,该改变将会影响所有相关的分区表。


添加缺省分区
ALTER TABLE sales ADD DEFAULT PARTITION other;
如果是多级分区表, 同一层次中的每个分区都需要一个默认分区。
ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other;
ALTER TABLE sales ALTER PARTITION FOR (RANK(2)) ADD DEFAULT PARTITION other;
ALTER TABLE sales ALTER PARTITION FOR (RANK(3)) ADD DEFAULT PARTITION other;
RANK(partitionrank)指的是范围分区同一层级中的顺序。partitionrank可参见pg_partition表。


(2)删除分区
ALTER TABLE sales DROP PARTITION FOR (RANK(1));


注意: 在将RANK(1)的分区删除后,其余分区的partitionrank值仍然是从1开始的连续编号。 编号的顺序按照分区字段的值由小到大从1开始排序。
不管分区是否连续(中间有值不匹配分区),或者随意的修改分区定义。


(3)清空分区数据
在清空一个包含子分区的分区时,其所有相关子分区的数据都自动被清空。
ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));


(4)交换分区:
CREATE TABLE jan08 (LIKE sales) WITH (appendonly=true);
INSERT INTO jan08 SELECT * FROM sales_1_prt_1 ;
ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2008-01-01') WITH TABLE jan08




(5)拆分分区
拆分分区是将现有的一个分区分成两个分区。 使用ALTER TABLE命令来拆分分区。只能拆分最低层级的分区表(只有包含数据的分区可以拆分)。
指定的分割值对应的数据将进入后面一个分区(就是STAER为INCLUSIVE)。
ALTER TABLE sales SPLIT PARTITION FOR ('2008-01-01')
AT ('2008-01-16') INTO (PARTITION jan081to15, PARTITION jan0816to31);


如果分区表有默认分区,要添加新的分区只能从默认分区拆分。而且只能从最低层级分区的默认分区拆分(只有包含数据的分区可以拆分)。 
在使用INTO子句时,第2个分区名称必须是已经存在的默认分区。
ALTER TABLE sales SPLIT DEFAULT PARTITION
START ('2009-01-01') INCLUSIVE
END ('2009-02-01') EXCLUSIVE
INTO (PARTITION jan09, default partition);




(6)修改子分区模版
使用ALTER TABLE SET SUBPARTITION TEMPLATE命令来修改现有分区表的子分区模版。 在修改了子分区模版之后添加的分区,
其子分区将按照新的模版产生。已经存在的分区不会被修改。
ALTER TABLE sales SET SUBPARTITION TEMPLATE
(   SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION africa VALUES ('africa')
    DEFAULT SUBPARTITION other 
);
ALTER TABLE sales ADD PARTITION sales_prt_3
START ('2009-03-01') INCLUSIVE END ('2009-04-01') EXCLUSIVE;


这个例子在一级分区有默认分区时是不能执行的,要查看效果,先删除默认分区。
要删除子分区模版,使用SET SUBPart TEMPLATE并使用空的参数来完成。
ALTER TABLE sales SET SUBPARTITION TEMPLATE ();




11) 创建与使用序列


(1)创建序列
CREATE SEQUENCE myserial START 101;


(2)使用序列
获取序列的下一个值并插入表中:
INSERT INTO vendors VALUES (nextval('myserial'), 'acme');


可以使用setval函数重置一个序列计数器的值。例如:
SELECT setval('myserial', 201);


注意,如果启用了镜像功能, nextval函数不允许在UPDATE和DELETE语句中被使用,另外currval和lastval函数目前未被GPDB支持。


检查序列当前的计数设置,可以直接查询该序列表:
SELECT * FROM myserial;


(3)修改序列
ALTER SEQUENCE myserial RESTART WITH 105;




12) 索引


在创建索引时需要综合考虑的问题:
查询工作负载
压缩表
避免在频繁更新的列上使用索引
创建选择性B-tree索引
低选择性列上使用位图索引
索引列用于关联
索引列经常用在查询条件中


(1)创建索引
CREATE INDEX title_idx ON films (title);
CREATE INDEX gender_bmp_idx ON employee USING bitmap (gender);


使用EXPLAIN ANALYZE命令对使用索引前后进行计时比较会很有用。


(2)管理索引
更新和删除操作不更新位图索引。因此在删除或者更新了位图索引列之后,可能需要使用REINDEX命令重建索引。
重建表上的全部索引
REINDEX my_table;
重建特定的索引
REINDEX my_index;


(3)删除索引
在装载数据时,通常先删除索引、再装载数据、然后在重新创建索引,这样比直接装载数据要快很多。

网友评论