Greenplum 数据库对象管理

最新推荐文章于 2021-11-02 23:24:57 发布

weixin_33832340

最新推荐文章于 2021-11-02 23:24:57 发布

阅读量418

点赞数

文章标签：数据库 python runtime

原文链接：https://my.oschina.net/crooner/blog/916779

版权

2019独角兽企业重金招聘Python工程师标准>>>

4. 数据库对象管理

数据库对象（database object）包括：数据库（database），文件空间（filespace），表空间（tablespace），模式（schema），数据表（table），序列（sequence），索引（index），视图（view）等。

4.1 创建与管理数据库（db）

一个GPDB系统可以有多个数据库(Database)。这与一些DBMS不同(比如Oracle)，它们的Instance就是Database。在GP系统中，虽然可以创建多个DB，但是客户端程序一次只能连接一个DB –不可以跨越DB执行查询语句。

数据库模板每个新的数据库都是基于一个模版创建的。缺省的数据库模版为template1。在初始化GPDB系统初期可以连接到该库。在没有明确指定模版的情况下创建新的数据库将缺省使用该DB作为模版。除非你希望之后创建的DB包含你所创建的对象，请不要在该DB中创建任何对象。

除了template1之外，每个新建的GP系统还包含另外两个模版template0和postgres，这两个DB是系统内部使用的，最好不要删除或者修改。 template0模版库可以用来创建仅仅包含标准对象的完全干净的数据库。如果想避免从template1中拷贝任何的对象，可以考虑使用该模版。

可以使用 DDL 语言创建数据库（要有 createdb 权限）

=>create database new_db;

也可以使用 createdb 工具来创建数据库

$ createdb -h mdw -p 5432 new_db

克隆一个数据库

=> create database new_db template old_db;

查看数据库列表在 psql 中可以直接使用 \l 来查看模板数据库在内的 db 列表。使用 DDL 语言时，可以通过查询 pg_database 系统日志表来获取。

=> select * from pg_database;

变更数据库修改 db 的名字

=> alter database old_db rename to new_db;

修改 db 的拥有者

=> alter database old_db_owner owner to new_owner;

切换数据库在 psql 中使用 \c 来切换 db。实例：

gpdb1=> \l
                  List of databases
   Name    |  Owner  | Encoding |  Access privileges  
-----------+---------+----------+---------------------
 gpdb1     | gpadmin | UTF8     | =Tc/gpadmin         
                                : gpadmin=CTc/gpadmin
 postgres  | gpadmin | UTF8     | 
 template0 | gpadmin | UTF8     | =c/gpadmin          
                                : gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | =c/gpadmin          
                                : gpadmin=CTc/gpadmin
 testdb1   | gpadmin | UTF8     | 
(5 rows)

gpdb1=> \c testdb1
You are now connected to database "testdb1" as user "gpdba".
testdb1=>

删除数据库此操作会删除 db，并删除该 db 包含的全部磁盘数据。

=> drop database my_db;

也可以使用 psql 中的 dropdb 工具来删除 db。

$ dropdb -h mdw -p 5432 my_db

4.2 表空间与文件空间

表空间（tablespace）允许 db 管理员使用多个文件系统来存储数据库对象，从而可以解决如何更好的利用他们的物理存储设备。

比如在访问程度不同的数据库对象上使用不同性能的磁盘。例如，将经常使用的表放在 SSD 上，而将其他表放在普通硬盘的文件系统上。

一个表空间是需呀一个文件系统来存储其数据库文件的。在 gpdb 中，master 节点和每个段实例（primary 和 mirror）都需要独立的存储位置。所有这些文件系统组件构成了 GP 系统中的文件空间（filespace）。文件空间定义之后，其可以被多个表空间使用。

总结： 在 GP 系统中，文件空间（filespace）建立在文件系统（filesystem）之上，表空间（tablespace）建立在文件空间（filespace）之上。

创建文件空间首先必须在所有相关的 GP 节点上准备好逻辑文件系统。文件系统位置对于主节点（mdw）和所有的主备节点都是必须的。在准备好文件系统之后，使用 gpfilespace 命令来定义文件空间。只有 superuser 才能进行该操作。

本人的部署为一共五个节点，mdw，sdw01，sdw02，sdw03，sdw04。其中每个节点为两个段（segment），sdw01 做 standby。

为了测试，预先在每个节点建立目录:

$mkdir -p /home/gpadmin/tol2/master
$mkdir -p /home/gpadmin/tol2/data
$mkdir -p /home/gpadmin/tol2/data/gp1
$mkdir -p /home/gpadmin/tol2/data/gp2
$mkdir -p /home/gpadmin/tol2/data/mi1
$mkdir -p /home/gpadmin/tol2/data/mi2

使用 gpfilespace 创建文件空间，举例：

使用 gpadmin 用户登录到 mdw 节点

$ ssh gpadmin@mdw

创建一个文件空间的配置文件：在输入 gpfilespace -o gpfilespace_config 后依次输入文件空间的名字 newdisk, 之后系统会根据你的集群情况进行提示，因为我的系统中只有两个段和两个镜像段，因此为提示两次主段和镜像段，分别是 /home/gpadmin/tol2/data/gp1, /home/gpadmin/tol2/data/gp2 和 /home/gpadmin/tol2/data/mi1, /home/gpadmin/tol2/data/gp1。

[gpadmin@mdw ~]$ gpfilespace -o gpfilespace_config
20170420:14:18:38:030754 gpfilespace:mdw:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.


20170420:14:18:38:030754 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Enter a name for this filespace
> newdisk

Checking your configuration:
Your system has 1 hosts with 0 primary and 0 mirror segments per host.
Your system has 4 hosts with 2 primary and 2 mirror segments per host.

Configuring hosts: [mdw]

Configuring hosts: [sdw04, sdw01, sdw03, sdw02]

Please specify 2 locations for the primary segments, one per line:
primary location 1> /home/gpadmin/tol2/data/gp1
primary location 2> /home/gpadmin/tol2/data/gp2

Please specify 2 locations for the mirror segments, one per line:
mirror location 1> /home/gpadmin/tol2/data/mi1
mirror location 2> /home/gpadmin/tol2/data/mi2

Enter a file system location for the master
master location> /home/gpadmin/tol2/master
20170420:14:21:22:030754 gpfilespace:mdw:gpadmin-[INFO]:-Creating configuration file...
20170420:14:21:22:030754 gpfilespace:mdw:gpadmin-[INFO]:-[created]
20170420:14:21:22:030754 gpfilespace:mdw:gpadmin-[INFO]:-
To add this filespace to the database please run the command:
   gpfilespace --config /home/gpadmin/gpfilespace_config

生成配置文件后，进行查看生成的配置文件会在您当前的工作目录，查看：

[gpadmin@mdw ~]$ ls -l | grep gpfilespace_config 
-rw-rw-r--. 1 gpadmin gpadmin   797 Apr 20 14:21 gpfilespace_config

基于生成的配置文件创建文件空间（filespace）使用 gpfilespace -c gpfilespace_config 进行操作：

[gpadmin@mdw ~]$ gpfilespace -c gpfilespace_config 
20170420:14:30:37:031094 gpfilespace:mdw:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.


20170420:14:30:37:031094 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Reading Configuration file: 'gpfilespace_config'
20170420:14:30:37:031094 gpfilespace:mdw:gpadmin-[INFO]:-Performing validation on paths
..............................................................................

20170420:14:30:39:031094 gpfilespace:mdw:gpadmin-[INFO]:-Connecting to database
20170420:14:30:39:031094 gpfilespace:mdw:gpadmin-[INFO]:-Filespace "newdisk" successfully created
[gpadmin@mdw ~]$

查看文件空间使用 psql 进行查询：

gpdb1=# select * from pg_filespace;
  fsname   | fsowner 
-----------+---------
 pg_system |      10
 newdisk   |      10
(2 rows)

表空间管理
创建表空间在上述文件空间的基础上创建表空间。

gpdb1=# create tablespace new_ts1 filespace newdisk;
CREATE TABLESPACE
gpdb1=# select * from pg_tablespace;
  spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid 
------------+----------+-------------+--------+-----------------+-----------------+----------
 pg_default |       10 |             |        |                 |                 |     3052
 pg_global  |       10 |             |        |                 |                 |     3052
 new_ts1    |       10 |             |        |                 |                 |    17354
(3 rows)

表空间必须有 superuser 才可以创建，在创建好之后，需要授权给其他 db 用户，其他 db 用户才能使用, 下面的例子中，授权给 gpdba。

=# grant create on tablespace new_ts1 to gpdba;

使用表空间存储 db 对象（db，表，索引）表，索引，甚至整个 db 都可以指定在特定的表空间中。若要如此，拥有给定表空间 create 权限的 role 必须通过表空间的名称作为相关命令的参数来实现。

在指定的表空间上创建表。

create table foo_info(lid int) tablespace new_ts1;

修改默认表空间通过修改表空间参数 default_tablespace 来设置：

set default_tablespace = new_ts1;
create table foo1_info(lid int);

由于默认表空间是相对于用户（或role）而言的，可以修改某个用户的默认表空间：

alter role gpdba set default_tablespace='new_ts1';

查看表空间每个 GP 系统都有两个默认的表空间： pg_global和pg_default。pg_global 用来存储系统日志信息，pg_default用来存储template1和template0这两个模板 db 的默认表空间。

gpdb1=# select * from pg_tablespace;
  spcname   | spcowner | spclocation |               spcacl                | spcprilocations | spcmirlocations | spcfsoid 
------------+----------+-------------+-------------------------------------+-----------------+-----------------+----------
 pg_default |       10 |             |                                     |                 |                 |     3052
 pg_global  |       10 |             |                                     |                 |                 |     3052
 new_ts1    |       10 |             | {gpadmin=C/gpadmin,gpdba=C/gpadmin} |                 |                 |    17354
(3 rows)

查看表空间与文件空间对应关系通过下面的命令可以查看表空间与文件空间，及文件系统间的关系：

=# 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;

gpdb1=# 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               
------------+-----------+----------+------------------------------------
 new_ts1    | newdisk   |        1 | /home/gpadmin/tol2/master/gpseg-1
 new_ts1    | newdisk   |        2 | /home/gpadmin/tol2/data/gp1/gpseg0
 new_ts1    | newdisk   |        3 | /home/gpadmin/tol2/data/gp2/gpseg1
 new_ts1    | newdisk   |        4 | /home/gpadmin/tol2/data/gp1/gpseg2
 new_ts1    | newdisk   |        5 | /home/gpadmin/tol2/data/gp2/gpseg3
 new_ts1    | newdisk   |        6 | /home/gpadmin/tol2/data/gp1/gpseg4
 new_ts1    | newdisk   |        7 | /home/gpadmin/tol2/data/gp2/gpseg5
 new_ts1    | newdisk   |        8 | /home/gpadmin/tol2/data/gp1/gpseg6
 new_ts1    | newdisk   |        9 | /home/gpadmin/tol2/data/gp2/gpseg7
 new_ts1    | newdisk   |       10 | /home/gpadmin/tol2/data/mi1/gpseg0
 new_ts1    | newdisk   |       11 | /home/gpadmin/tol2/data/mi1/gpseg1
 new_ts1    | newdisk   |       12 | /home/gpadmin/tol2/data/mi2/gpseg2
 new_ts1    | newdisk   |       13 | /home/gpadmin/tol2/data/mi1/gpseg3
 new_ts1    | newdisk   |       14 | /home/gpadmin/tol2/data/mi2/gpseg4
 new_ts1    | newdisk   |       15 | /home/gpadmin/tol2/data/mi1/gpseg5
 new_ts1    | newdisk   |       16 | /home/gpadmin/tol2/data/mi2/gpseg6
 new_ts1    | newdisk   |       17 | /home/gpadmin/tol2/data/mi2/gpseg7
 new_ts1    | newdisk   |       18 | /home/gpadmin/tol2/master/gpseg-1
 pg_default | pg_system |        1 | /tol/master/gpseg-1
 pg_default | pg_system |        2 | /tol/data/gp1/gpseg0
 pg_default | pg_system |        3 | /tol/data/gp2/gpseg1
 pg_default | pg_system |        4 | /tol/data/gp1/gpseg2
 pg_default | pg_system |        5 | /tol/data/gp2/gpseg3
 pg_default | pg_system |        6 | /tol/data/gp1/gpseg4
 pg_default | pg_system |        7 | /tol/data/gp2/gpseg5
 pg_default | pg_system |        8 | /tol/data/gp1/gpseg6
 pg_default | pg_system |        9 | /tol/data/gp2/gpseg7
 pg_default | pg_system |       10 | /tol/data/mi1/gpseg0
 pg_default | pg_system |       11 | /tol/data/mi2/gpseg1
 pg_default | pg_system |       12 | /tol/data/mi1/gpseg2
 pg_default | pg_system |       13 | /tol/data/mi2/gpseg3
 pg_default | pg_system |       14 | /tol/data/mi1/gpseg4
 pg_default | pg_system |       15 | /tol/data/mi2/gpseg5
 pg_default | pg_system |       16 | /tol/data/mi1/gpseg6
 pg_default | pg_system |       17 | /tol/data/mi2/gpseg7
 pg_default | pg_system |       18 | /tol/master/gpseg-1
 pg_global  | pg_system |        1 | /tol/master/gpseg-1
 pg_global  | pg_system |        2 | /tol/data/gp1/gpseg0
 pg_global  | pg_system |        3 | /tol/data/gp2/gpseg1
 pg_global  | pg_system |        4 | /tol/data/gp1/gpseg2
 pg_global  | pg_system |        5 | /tol/data/gp2/gpseg3
 pg_global  | pg_system |        6 | /tol/data/gp1/gpseg4
 pg_global  | pg_system |        7 | /tol/data/gp2/gpseg5
 pg_global  | pg_system |        8 | /tol/data/gp1/gpseg6
 pg_global  | pg_system |        9 | /tol/data/gp2/gpseg7
 pg_global  | pg_system |       10 | /tol/data/mi1/gpseg0
 pg_global  | pg_system |       11 | /tol/data/mi2/gpseg1
 pg_global  | pg_system |       12 | /tol/data/mi1/gpseg2
 pg_global  | pg_system |       13 | /tol/data/mi2/gpseg3
 pg_global  | pg_system |       14 | /tol/data/mi1/gpseg4
 pg_global  | pg_system |       15 | /tol/data/mi2/gpseg5
 pg_global  | pg_system |       16 | /tol/data/mi1/gpseg6
 pg_global  | pg_system |       17 | /tol/data/mi2/gpseg7
 pg_global  | pg_system |       18 | /tol/master/gpseg-1
(54 rows)

删除表空间和文件空间在相关表空间的所有对象被删除之前，此表空间不能被删除。同理，相关表空间被删除前，对应的文件空间不能被删除。
删除表空间

gpdb1=# \h drop tablespace
Command:     DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [ IF EXISTS ] tablespacename

删除文件空间

gpdb1=# \h drop filespace
Command:     DROP FILESPACE
Description: remove a filespace
Syntax:
DROP FILESPACE [IF EXISTS] filespacename

4.3 创建与管理模式（schema）

模式（schema）是 db 内组织对象的一种逻辑结构。模式允许用户在一个 db 内不同的模式之间使用相同的命名对象（比如: table 对象）。

缺省public模式每个新创建的 db 都有一个缺省的的模式 public。如果没有创建其他模式，在创建新的 db 对象时，将缺省使用模式 public。缺省情况下所有的 role（user）都有模式 public 下的create和usage权限。而在创建其他模式时，需要将该模式授权给相关的 role（user）。
创建模式创建一个模式 my_schema:

=> create schema my_schema;

创建模式时，将 owner 设置为其他 role（user） gpdba：

=> create schema my_schema authorization gpdba;

要访问某个模式中的对象，可以通过模式名.对象来指明对象所属的模式。比如：my_schema.foo_info。

默认模式顺序设置通过对 search_path 的设置，可以改变模式搜索的顺序，通过 alter database 来修改，例如：

=> alter database my_db set search_path to my_schema, public, pg_catalog;

还可以通过 alter role 来修改特定 role(user) 的模式搜索路径。例如：

=> alter role gpdba search_path to my_schema, public, pg_catalog;

设置了 search_path 之后，在未明确指明模式名称的情况下访问 db 对象，将会按照 search_path 列表的顺序依次在相应的模式中查找对应的对象，知道找到为止。若在不同的模式中存在相同名字的对象，db 优先匹配 search_path 中靠前的 search_path 的对象。

查看当前模式使用 current_schema() 函数进行查看：

=> select current_schema();
=> show search_path;

删除模式缺省情况下，只有空模式才允许被删除。

使用 drop schema 命令来删除模式。例如：

=> drop schema my_schema;

若想要直接删除模式及相关的所有对象（表，索引，函数等），可以使用如下命令：

=> drop schema my_schema cascade;

系统中的模式下面的这些系统级别的模式在所有的DB中都存在：
pg_catalog模式存储着系统日志表(System Catalog Table)、内置类型(Type)、函数(Function)和运算符(Operator)。该模式无论是否在search_path中指明，都存在search_path中。
information_schema模式由一个标准化视图构成，其包含DB中对象的信息。该视图用于以标准化的方法从系统日志表中查看系统信息。
pg_toast模式是一个储存大对象的地方(那些超过页面尺寸(page size)的记录)。该模式仅供 GPDB 系统内部使用，通常不建议管理员或者任何用户访问。
pg_bitmapindex视图是一个储存 bitmap index 对象的地方(值列表等)。该模式仅供 GPDB 系统内部使用，通常不建议管理员或者任何用户访问。
pg_aoseg视图是一个储存append-only表的地方。该模式仅供 GPDB 系统内部使用，通常不建议管理员或者任何用户访问。
gp_toolkit是一个管理用的模式，可以查看和检索系统日志文件和其他的系统信息。gp_toolkit视图包含一些外部表、视图、函数，可以通过 SQL 的方式访问它们。 gp_toolkit视图对于所有 DB User 都是可以访问的。更多信息查看 ”gp_toolkit管理视图” 。

4.4 创建和管理表（table）

GP 中的表除了数据是分布在不同 segment 节点外，和其他的关系型数据库的表是类似的。在创建表时，需要一个额外的语法来指明表的分布策略。

GP 中的数据类型

数值类型

对于数值型数据来说，应该尽量选择更小的类型。如果选择 bigint 类型来存储 smallint 就满足的数值，会造成空间的巨大浪费。

名字	存储空间	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围的整数	-9223372036854775808 到 9223372036854775807
decimal	变长	用户声明精度，精确	无限制
numeric	变长	用户声明精度，精确	无限制
real	4 字节	变精度，不精确	6 位十进制数字精度
double	8 字节	变精度，不精确	15 位十进制数字精度
serial	4 字节	自增整数	1 到 +2147483647
bigserial	8 字节	大范围的自增整数	1 到 9223372036854775807

字符类型

在 GP 系统中，char，varchar 和 text 之间不存在性能差异，有些 db 系统可能会在 char 中表现更好，但在 GP 系统中不存在这种情况。在多数情况下，应该使用 text 或者 varchar 而不是 char。

名字	描述
varchar(n)	变长，有长度限制
char(n)	定长,不足补空白
text	变长，无长度限制

日期类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp[无时区]	8字节	包括日期和时间	4713 BC	5874897AD	1毫秒/14位
timestamp[含时区]	8字节	日期和时间，带时区	4713 BC	5874897AD	1毫秒/14位
interval	12字节	时间间隔	-178000000年	178000000年	1毫秒/14位
date	4字节	只用于日期	4713 BC	32767AD	1天
time[无时区]	8字节	只用于一日内时间	00:00:00	24:00:00	1毫秒/14位

如果在后期会使用到表的 join，那么那些 join 的列应该考虑使用相同的数据类型。这样效率会更高，如果类型不同，数据库系统还需要使用其中一个类型做转换，再进行关联比较，这将浪费性能。

数据表的约束

数据类型用来限制在Table中可以存储的数据的性质。但对于很多应用来说，数据类型提供的限制粒度太大。 SQL标准允许在Table和Column上定义约束。约束将允许在Table的数据上使用更多的限制。果User试图在Table上储存违反约束的数据将会发生错误。在GPDB中使用约束是有一些限制的，最为显著的是外键(ForeignKey)、主键(PrimaryKey)和唯一约束(Unique Constraint)。其他约束的支持与PostgreSQL相同。

check约束

要求产品价格必须为正数：

=> CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );

非空约束

不可以存在（NULL）值：

=> CREATE TABLE products (product_no integer NOT NULL, name text NOT NULL, price
numeric );

唯一约束

唯一约束可以确保包含某些列的数据在整个数据表中是唯一的。在 GPDB 中使用唯一约束存在强制条件，数据表必须是HASH分布的(而不是 DISTRIBUTED RANDOMLY )，并且唯一约束的列集合必须完整包含所有的 DK 列。

=> CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric) DISTRIBUTED BY (product_no);

外键约束

在目前版本（到目前最新的 4.3.12.0）的 GPDB 中外键约束是没有被支持的。虽然可以定义外键约束，但是参照完整性是无效的，也就是说 DB 不会理会定义参照完整性限制。

外键约束要求一个数据表中某些Column的值必须在另外一个数据表中出现。这样可以保持两个相关表之间的数据完整性。在当前版本的 GPDB 中，在分布式的数据表之间的数据完整性检查是无效的。

表的分布策略

GPDB 的所有数据表都是分布式存储的。在CREATE TABLE和ALTER TABLE的时候有个DISTRIBUTED BY(HASH分布)或DISTRIBUTED RANDOMLY(随机分布)子句用以决定数据表的行数据如何分布。

重点考虑因素：

平坦的数据分布

为了尽可能达到最好的性能，所有的Instance应该尽量储存等量的数据。若数据的分布不平衡或倾斜，那些储存了较多数据的Instance在处理自己那部分数据时将需要耗费更多的工作量。为了实现数据的平坦分布，可以考虑选择具有唯一性的DK，如主键。往往很多Table是没有唯一键的只需尽量选择数据分布规律（例如：时间）且取值范围远远大于Instance数量的Column作为DK即可。

本地操作与分布式操作

在处理查询时，很多处理如关联、排序、聚合等若能够在Instance本地完成，其效率将远高于跨越系统级别(需在Instance之间交叉传输数据)的操作。当不同的Table使用相同的DK时，在DK上的关联或者排序操作将会以最高效的方式把绝大部分工作在Instance本地完成。本地操作大约比分布式操作快5倍。若Table使用随机分布策略，将大大限制本地操作的可能性，虽然这种方式可以确保数据分布的平坦性。

平坦的查询处理

在一个查询正被处理时，我们希望所有的Instance都能够处理等量的工作负载，从而尽可能达到最好的性能。有时候查询场景与数据分布策略很不吻合，这时很可能导致工作负载的倾斜。例如，有一张销售交易Table。该Table的DK为公司名称，那么数据分布的HASH算法将基于公司名称的值来计算，假如有一个查询以某个特定的公司名称作为查询条件，该查询任务将仅在一个Instance上执行。若查询时不是指定特定的单位名称，这仍然是个可行的分布策略。

声明分布键

在创建Table时有一个额外的子句用以指明分布策略。

如果在创建Table时没有指明 DISTRIBUTED BY 或者 DISTRIBUTED RANDOMLY 子句， GPDB将会采用如下策略：

使用主键 (假如该Table有的话) 或者第一个字段作为 HASH 分布的 DK。
一个Table如果没有一个合适类型的Column作为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;

数据表的存储模式
选择堆存储(Heap)或只追加(Append-Only/AO)存储

缺省情况下GPDB使用与PostgreSQL相同的存储模式 -- 堆存储。堆存储模式在OLTP类型工作负载的DB中很常用 -- 数据在初始装载后经常变化。 UPDATE和DELETE操作需要对ROW级别做版本控制从而确保DB事务处理的可靠性。

堆表更适合一些小表，比如维表，这种表可能会在初始化装载后经常更新数据。

GPDB还提供了一种称之为只追加存储模式的TABLE。AO表更适合数据仓库中非规范化事实表，这些表通常都是系统中最大的表。

事实表通常是批量装载数据且只进行只读式查询操作。 AO表在数据装载后是不支持更新的。将事实表数据存入AO表时不会保留更新相关ROW级别的信息(大约每ROW需要20字节)。AO表达到了更精简和优化的页面存储结构。

AO表不允许执行DELETE和UPDATE操作。该存储模式强化了批量数据装载的性能。不推荐一行一行的使用INSERT语句来装载数据。

创建堆表

例子：

=> CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);

创建只追加表

例子：

=> CREATE TABLE bar (a int, b text) WITH (appendonly=true)

选择行存储(Row-Orientation)或列存储(Column-Orientation)

GPDB提供存储导向的选择：行存或列存(或者混合)。本节提供一些关于正确选择行存或列存的常规指导。不过具体还需根据查询工作负载来确切评估。

大多数情况下，都应该使用行表，只有在列数很少的表中列表才会表现出更好的性能。

创建列存表

在 create table 时使用 with 子句来指明数据表的存储模式。如果没有指明，该表将会是缺省的行存堆表。使用列存的数据表必须是 AO 表。举例：

=> create table bar_info(a int, b text) with (appendonly=true, orientation=column) distributed by(a);

使用压缩(只可以是(Append-Only/AO)表)

在 GPDB 中，AO 表有两种库内压缩可选，一种是表级压缩，另一种是列级压缩，前者应用到整个数据表，后者应用到指定的列。在选择列级别压缩时，可以为不同的列选择不同的压缩算法。

压缩算法表

表导向	可用压缩类型	支持的压缩算法
行	表级别	zlib 和 quicklz
列	列级别和表级别	rle_type、zlib 和 quicklz

使用库内压缩要求 segment 系统具备强劲的 CPU 来压缩和解压缩数据。不要在压缩文件系统使用压缩 AO 表。

在选择 AO 表的压缩方式和级别时，需要考虑几点因素：

CPU性能
压缩比或占用磁盘尺寸
压缩速度
解压缩速度或扫表效率

压缩算法选择：

quicklz 使用于 cpu 能力一般的情况，压缩速度比 zlib 快，但压缩率不如 zlib。
在压缩级别为1时，quicklz 与 zlib 可能获得差不多的压缩率，zlib 压缩速度差些。
在压缩级别为6及其以上时，zlib 压缩率显著高于 quicklz ，但压缩速度更慢。

创建压缩表：

=> create table foo_info(a int, b text) with(appendonly=true, compresstype=zlib,compresslevel=5);

quicklz 压缩模式只有一种压缩级别，没有级别选项可以选择。而 zlib 压缩模式有 1-9 个压缩级别可选。

检查只追加(AO)表的压缩和分布情况

GPDB 内置了一些函数来检查 AO表的压缩率和分布情况。这两个函数可以使用对象ID或者表名作为参数。表名可能需要带上其对应的 schema。

函数 get_ao_distribution(name), get_ao_distribution(oid) 返回类型 Set of (dbid, tuplecount) rows：

展示 AO 表的分布情况，每行对应 Segment Instance 的 dbid 与储存的数据行数

函数 get_ao_compression_ratio(name), get_ao_compression_ratio(oid) 返回类型为 float8:

计算出 AO 表的压缩率。如果该信息未得到，将返回-1 值。

例子：

=# SELECT get_ao_distribution('lineitem_comp');
get_ao_distribution

(0,7500721)
(1,7501365)
(2,7499978)
(3,7497731)
(4 rows)

其他压缩配置请参考文档

支持运行长度编码，使用列级压缩，缺省压缩属性（根据系统block_size设置），压缩设置的优先级，列压缩设置的最佳定位，存储参数的例子，通过TYPE命令的方式设置压缩配置

变更表

ALTER TABLE命令用于改变现有表的定义。通过ALTER TABLE命令可以改变TABLE的各种属性，如：列定义、分布策略、存储模式和分区结构(可参见”维护分区表”章节)等。例如，将TABLE的一个COLUMN添加一个非空限制：

=> ALTER TABLE address ALTER COLUMN street SET NOT NULL;

改变表的分布

ALTER TABLE命令提供了改变分布策略的选项。在修改TABLE的分布策略时，表中的数据要在磁盘上做重分布，该操作可能需要密集的资源消耗。还有一个按照现有策略重新分布数据的选项。

修改分布策略

ALTER TABLE命令可用于改变表的分布策略。对于分区表来说，修改分布策略会递归的应用于所有的子分区。该操作不会改变表的OWNER以及其他TABLE属性。例如，下面的命令在所有Segment之间按照customer_id作为DK重分布sales表：

ALTER TABLE sales SET DISTRIBUTED BY (customer_id);

在修改TABLE的HASH分布时，表数据会自动重新分布。然而，将分布策略改为随机分布时不会重新分布数据。例如：

ALTER TABLE sales SET DISTRIBUTED RANDOMLY;

重分布数据

对于随机分布策略或者不改变分布策略的表，要重分布TABLE的数据，使用REORGANIZW=TRUE。这在处理数据倾斜问题时可能是很必要的，在添加新的Segment节点资源时也是必要的。比如：

ALTER TABLE sales SET WITH (REORGANIZE=TRUE);

案例：

gpdb1=# \timing on
Timing is on.
gpdb1=# select count(*) from students_info;
 count 
--
 10000
(1 row)

Time: 19.924 ms
gpdb1=# select * from students_info offset 0 limit 10;
 sid | lid | name 
-----+-----+------
   8 |  28 | E
  16 |  62 | M
  24 |  37 | P
  32 |   8 | X
  40 |  36 | Z
  48 |  70 | Z
  56 |  68 | V
  64 |  62 | M
  72 |  89 | M
  80 |  32 | J
(10 rows)

Time: 99.425 ms
gpdb1=# \d students_info;
 Table "public.students_info"
 Column |  Type   | Modifiers 
--------+---------+-----------
 sid    | integer | 
 lid    | integer | 
 name   | text    | 
Distributed by: (sid)

gpdb1=# select gp_segment_id, count(*) from students_info group by gp_segment_id;
 gp_segment_id | count 
---------------+-------
             7 |  1300
             4 |  1300
             1 |  1300
             6 |  1200
             3 |  1200
             0 |  1200
             5 |  1200
             2 |  1300
(8 rows)

Time: 143.842 ms

gpdb1=# alter table students_info set distributed by(lid);
ALTER TABLE
Time: 410.603 ms

gpdb1=# alter table students_info set with(reorganize=true);
ALTER TABLE
Time: 512.887 ms

gpdb1=# select gp_segment_id, count(*) from students_info group by gp_segment_id;
 gp_segment_id | count 
---------------+-------
             7 |  1288
             4 |  1361
             1 |  1306
             6 |  1188
             0 |  1212
             5 |  1210
             2 |  1261
             3 |  1174
(8 rows)

Time: 121.831 ms

删除表

使用 drop table 命令删除表。此命令会删除所有与该表相关的索引，规则、触发器、约束等。如果要一起删除与该表相关的视图（view），必须使用 cascade。例子：

drop table my_table;
drop table my_table cascade;

只想删除表中的记录，使用 delete 或者 truncate, 本人建议使用 truncate。

例子：

delete from my_table;
truncate table my_table;

4.5 分区大表

表分区用以解决特别大的表的问题，比如事实表，解决办法就是将表分成很多小且更容易管理的部分。分区表在执行给定的查询语句时扫描相关的部分数据而不是全表的数据从而提高查询性能。分区表对于数据库的管理也有帮助，比如在数据仓库中滚动旧的数据。

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

决定表的分区策略

并不是任何TABLE都适合做分区。如果下列问题的全部或者大部分的答案是Yes，这样的表可以通过分区策略来提高查询性能。如果大部分的答案是No，分区不是好的方案。

表是否足够大？

大的事实表适合做表分区。若在一张表中有百万级甚至数亿条记录，从逻辑上把表分成较小的分区将可以改善性能。而对于只有数千条或者更少记录的表，对分区预先进行的管理开销将远大于可以获得的性能改善。

对目前的性能不满意？

作为一种调优方案，应该在查询性能低于预期时再考虑表分区

查询条件是否能匹配分区条件？

检查查询语句的WHERE条件是否与考虑分区的COLUMN一致。例如，如果大部分的查询使用日期条件，那么按照月或者周的日期分区设计也许很有用，而如果查询条件更多的是使用地区条件，可以考虑使用地区将表做列表类型的分区。

数据仓库是否需要滚动历史数据？

历史数据的滚动需求也是分区设计的考虑因素。比如，数据仓库中仅需要保留过去两个月的数据。如果数据按照月进行分区，将可以很容易的删除掉两个月之前的数据，而最近的数据存入最近月份的分区即可。

按照某个规则数据是否可以被均匀的分拆？

应该选择尽量把数据均匀分拆的规则。若每个分区储存的数据量相当，那么查询性能的改善将与分区的数量相关。例如，把一张表分为10个分区，命中单个分区条件的查询扫表性能将比未分区的情况下高10倍。译者认为，我们不应该简单的说查询性能是10倍，因为多数的查询不是count(*)这样简单的计数，若是其他耗时的运算，除了扫表过滤数据这部分可以提升外，后续的处理部分是不会有性能提升的。

创建分区表

TABLE只能在执行CREATE TABLE命令时被分区。

表做分区的第一步是选择分区类型(范围分区、列表分区等)和分区字段。决定分区的层数。例如，先按照日期范围划分一级月分区，再将月分区按照区域做二级列表分区。本节通过示例演示如何创多种分区表。

定义日期范围分区表

日期范围分区表使用单个date或者timestamp字段作为分区键。如果需要，还可以使用同样的字段做子分区(比如按照月分区后再按照日做子分区)。使用日期分区时优先考虑直接使用最细粒度的分区。比如，设置365个日分区，而不是先设置年分区再设置月分区再设置日分区。 多级分区会降低查询计划的时间，但水平的分区设计可以提高执行的速度。

可以通过使用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 );

定义数字范围分区表

数字范围分区表使用单个数字列作为分区键。例如：

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 );

定义列表分区表

列表分区表可以使用任何数据类型的列作为分区键，分区规则使用等值比较

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 );

多级分区

不建议使用多级分区

将现有表分区

对已经创建的表是不能分区的。只能在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;

维护分区表

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

本部分将以 sales 表为例进行：

创建分区表

create table sales (id int, date date, amt decimal(10,2)) 
distributed by (id) 
partition by range (date) (
start (date '2016-01-01') inclusive
end (date '2016-12-31') exclusive
every (interval '1 month') );

添加分区表

alter table sales add partition
start (date '2017-01-01') inclusive
end (date '2017-12-31') exclusive;

gpdb1=# \d
                      List of relations
 Schema |          Name           | Type  |  Owner  | Storage 
--------+-------------------------+-------+---------+---------
 public | sales                   | table | gpadmin | heap
 public | sales_1_prt_1           | table | gpadmin | heap
 public | sales_1_prt_10          | table | gpadmin | heap
 public | sales_1_prt_11          | table | gpadmin | heap
 public | sales_1_prt_12          | table | gpadmin | heap
 public | sales_1_prt_2           | table | gpadmin | heap
 public | sales_1_prt_3           | table | gpadmin | heap
 public | sales_1_prt_4           | table | gpadmin | heap
 public | sales_1_prt_5           | table | gpadmin | heap
 public | sales_1_prt_6           | table | gpadmin | heap
 public | sales_1_prt_7           | table | gpadmin | heap
 public | sales_1_prt_8           | table | gpadmin | heap
 public | sales_1_prt_9           | table | gpadmin | heap
 public | sales_1_prt_r304846230  | table | gpadmin | heap

修改分区表的名字

规则: <父表名称>_<分区层级>ptr<分区名称>

alter table sales rename partition for (date '2017-01-01') to r2017;

gpdb1=# \d
                     List of relations
 Schema |         Name         | Type  |  Owner  | Storage 
--------+----------------------+-------+---------+---------
 public | sales                | table | gpadmin | heap
 public | sales_1_prt_1        | table | gpadmin | heap
 public | sales_1_prt_10       | table | gpadmin | heap
 public | sales_1_prt_11       | table | gpadmin | heap
 public | sales_1_prt_12       | table | gpadmin | heap
 public | sales_1_prt_2        | table | gpadmin | heap
 public | sales_1_prt_3        | table | gpadmin | heap
 public | sales_1_prt_4        | table | gpadmin | heap
 public | sales_1_prt_5        | table | gpadmin | heap
 public | sales_1_prt_6        | table | gpadmin | heap
 public | sales_1_prt_7        | table | gpadmin | heap
 public | sales_1_prt_8        | table | gpadmin | heap
 public | sales_1_prt_9        | table | gpadmin | heap
 public | sales_1_prt_r2017    | table | gpadmin | heap

删除分区

alter table sales drop partition for (date '2017-01-01');

多次添加分区，并对分区命名

添加 2017-01-01 到 2017-04-31 的月分区

alter table sales add partition r201701 
start (date '2017-01-01') inclusive
end (date '2017-02-01') exclusive;

alter table sales add partition r201702 
start (date '2017-02-01') inclusive
end (date '2017-03-01') exclusive;

alter table sales add partition r201703 
start (date '2017-03-01') inclusive
end (date '2017-04-01') exclusive;

alter table sales add partition r201704 
start (date '2017-04-01') inclusive
end (date '2017-05-01') exclusive;

gpdb1=# \d
                     List of relations
 Schema |         Name         | Type  |  Owner  | Storage 
--------+----------------------+-------+---------+---------
 public | sales                | table | gpadmin | heap
 public | sales_1_prt_1        | table | gpadmin | heap
 public | sales_1_prt_10       | table | gpadmin | heap
 public | sales_1_prt_11       | table | gpadmin | heap
 public | sales_1_prt_12       | table | gpadmin | heap
 public | sales_1_prt_2        | table | gpadmin | heap
 public | sales_1_prt_3        | table | gpadmin | heap
 public | sales_1_prt_4        | table | gpadmin | heap
 public | sales_1_prt_5        | table | gpadmin | heap
 public | sales_1_prt_6        | table | gpadmin | heap
 public | sales_1_prt_7        | table | gpadmin | heap
 public | sales_1_prt_8        | table | gpadmin | heap
 public | sales_1_prt_9        | table | gpadmin | heap
 public | sales_1_prt_r201701  | table | gpadmin | heap
 public | sales_1_prt_r201702  | table | gpadmin | heap
 public | sales_1_prt_r201703  | table | gpadmin | heap
 public | sales_1_prt_r201704  | table | gpadmin | heap

添加缺省分区

查看表会多出一个 sales_1_prt_other 表。

有了缺省分区之后，要添加新分区只能从缺省分区中拆分。

alter table sales add default partition other;

清空分区数据

(r201701为分区表名字)

alter table sales truncate partition r201701;

交换分区

用一个普通数据表与现有的分区交换身份

注意这里 sales_test1 交换的是 r201701

所以其中日期必须都是2017-01-xx的，或者 sales_test1 是空表，否则将无法交换

create table sales_test1(id int, date date, amt decimal(10,2));
insert into sales_test1 values(1, '2017-01-03', 65.11);
insert into sales_test1 values(2, '2017-01-03', 32.11);
alter table sales exchange partition r201701 with table sales_test1;

拆分分区

将现有的一个分区分成两个分区。

r201701被分为sales_1_prt_r201701b01t15和sales_1_prt_r201701b15t31。

如果原有分区表中有数据，则会将数据按日期逻辑分给新分后的表。

alter table sales split partition r201701 at ('2017-01-16') 
into (partition r201701b01t15, partition r201701b15t31);

对缺省分区进行拆分

如果分区表有缺省分区，要添加新的分区只能从缺省分区拆分。

先直接添加分区进行尝试，会提示错误：

gpdb1=# alter table sales add partition r201705 
gpdb1-# start (date '2017-05-01') inclusive
gpdb1-# end (date '2017-06-01') exclusive;

ERROR:  cannot add RANGE partition "r201705" to relation "sales" with DEFAULT partition "other"
HINT:  need to SPLIT partition "other"

拆分缺省分区：

alter table sales split default partition 
start (date '2017-05-01') inclusive
end (date '2017-06-01') exclusive
into (partition r201705, default partition);

附加：哈希分区

由于原生的 Greenplum 的分区表方式只有列表分区和范围分区，不支持哈希分区。因为哈希分区对于业务的支撑是极其重要的，它不会对字符串类型的字段进行限制，而列表分区需要手动创建一个个列表，范围分区只能限定字段是数值类型。

然而在实际业务中，分区多是根据身份证号码这样的ID号进行的（目的是尽可能加快对某ID号的查询速度），而这种ID多是字符串类型，所以必须寻找一种策略来对这种情况进行处理。

那么我们可以假设sid字段是身份证号ID，则新建一个bp_sid字段，用于存储sid的哈希值，目前的哈希算法为将sid中的每个字符ascii码值的总和除以希望分区的数量(p)，即：

hash=(∑sid)∕p

在具体操作上，当每次有数据插入时，将计算出sid的hash值(数值型)，相应存入bp_sid字段。分区时，是对bp_sid字段进行的分区。相应地，在查询时，需要先计算出sid的hash值，再通过复合条件进行查询，例如，假设分区数为128，sid为320100199001013312，计算出其hash值是4，进行查询：

select * from test_info where bp_sid=4 and sid=’ 320100199001013312’;

另外还可以在bp_sid字段上建立位图索引，这样在进行sid查询的时候速度是非常快的。经本人测试，单表10亿条记录级别的点查sid基本保持在了秒级。

4.6 序列（sequence）

序列常用于在新增记录时自动生成唯一标识符。

创建序列

create sequence my_seq1 start 1;

使用序列

使用 create sequence 创建序列之后，就可以使用 nextval 函数在序列上操作。

函数nextval是不回滚的。其只要获得值就被认为已经使用该值，即便是事务在nextval之后被中断。这就意味着中断事务会使得有空缺的序列没有被真正的使用。同样的setval函数也是不回滚的。

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

例，获取序列的下一个值插入表中：

insert into sales values(nextval('my_seq1'), '2017-05-23', 11.12);

使用 setval 函数重置一个序列(值可以用户设定)，例如：

select setval('my_seq1', 100);

查询序列的当前值

select * from my_seq1;

修改序列

此功能类似于 setval 函数：

alter sequence my_seq1 restart with 105;

删除序列

drop sequence my_seq1;

4.7 索引（index）

在大多数的OLTP数据库中，索引可以显著的改善数据访问性能。然而在分布式数据库如GP中，应保守使用索引。 GPDB在顺序扫描(索引通过随机寻址在磁盘上定位数据记录)方面很快。与传统的OLTP数据库不同的是，数据是分布在多个Instance上的。这意味着每个Instance都扫描全部数据的一小部分来查找结果。如果使用了表分区，扫描的数据可能更少。通常，商业智能(BI)的查询工作负载需要返回大量的数据，这种情况下使用索引未必有效。

GP建议在没有添加索引的情况下先测试一下查询工作负载。索引更易于改善OLTP类型的工作负载，其返回很少量的数据。在返回一定量结果的情况下，索引同样可以改善压缩AO表上查询的性能，当情况合适时查询优化器会把索引作为获取数据的选择，而不是一味的全表扫描。对于压缩数据来说，索引访问数据的方法是解压需要的记录而不是全部解压。

在创建索引时需要综合考虑的问题:
查询工作负载。

索引更易于改善OLTP类型的工作负载，其返回很少量的数据。通常，商业智能(BI)的查询工作负载需要返回大量的数据，这种情况下使用索引未必有效。对于这种工作负载，使用顺序扫描来定位大部分数据比使用索引扫描的随机寻址定位数据更有效。

压缩表。

在返回一定量结果的情况下，索引同样可以改善压缩AO表上查询的性能，当情况合适时查询优化器会把索引作为获取数据的选择，而不是一味的全表扫描。对于压缩数据来说，索引访问数据的方法是解压需要的记录而不是全部解压。

避免在频繁更新的列上使用索引。

在频繁更新的列上创建索引，当该列被更新时，需要消耗大量的写磁盘资源和CPU计算资源。

创建选择性B-tree索引。

选择性指的是列中DISTINCT值的数量除以表中的记录数。例如，如果一张表中有1000行记录且有800个DISTINCT值，选择性指数为 0.8，这被认为是良好的。唯一索引总是具备1.0的选择比，这是最好的情况。值得注意的是在GPDB中唯一索引必须包含所有的DK键。

低选择性列上使用位图索引。

GPDB还有一种索引叫位图(Bitmap)索引，其在标准PostgreSQL中是没有的。

索引列用于关联。

经常关联(JOIN)的COLUMN(比如外键)上建立索引或许可以改善JOIN的性能，因为其可以帮助查询规划器使用其他的关联方法。

索引列经常用在查询条件中。

对于大表来说，查询语句WHERE条件中经常用到的列，可以考虑使用索引。

索引类型

GPDB提供了PostgreSQL的索引类型： B-tree和GiST( Hash索引与 GIN索引在GPDB中不可用 )。每种索引使用不同的算法以最优化适应不同类型的查询。缺省状态下CREATE INDEX命令将创建B-tree索引，其使用于大多数场合。

B-tree索引

在一张大表上建立全表的B-tree索引可能需要庞大的空间，其可能是数据空间的好几倍。

位图索引

普通的索引每个键值对应一组数据表中相同值行的ID记录。而在Bitmap索引，每个位图对应一组数据表中相同值行的ID记录。Bitmap索引通常只是数据空间零点几。位图的每一位对应源数据的标识符，被设置的位对应的记录包含该位图相同的值。数据的实际位置可以通过映射函数得到，因此位图索引提供了普通索引相同的功能。位图索引以压缩的方式存储位图。 当DISTINCT值很小时，位图索引在压缩空间上面比B-tree表现的更(Bitmap索引在DISTINCT值数量介于100到100000之间时可以有较好的表现)。

比如，性别列仅有两种DISTINCT值：男、女，不适合使用索引。一个DISTINCT值数量超过100000的列， Bitmap索引的空间与性能效率都将下降。 Bitmap索引的尺寸与表中记录数和DISTINCT值数量成正比。

在WHERE子句中包含多个条件的查询可能更适合位图索引。一部分而不是全部行被匹配，条件在访问表本身之前就被过滤了。这将经常不可思议的有效改善响应时间。如果查询结果的数据量很小，将可以快速的响应。

创建索引

使用CREATE TABLE命令在表上定义新的索引。缺省状态下，不明确指定索引类型时创建B-tree索引。例如，在表 sales 的 id 列上创建 B-tree 索引：

create index sales_id_bt_idx on sales(id);

创建位图索引：

create index sales_id_bmp_idx on sales using bitmap(id);

检查索引的使用

主要命令 'explain', 使用 students_info 来测试：

先创建索引：

create index students_info_sid_bmp_idx on students_info using bitmap(sid);

执行分析：
分析实验

使用语句进行分析

gpdb1=# explain select * from students_info;
                                     QUERY PLAN                                     
--
 Gather Motion 8:1  (slice1; segments: 8)  (cost=0.00..4800.73 rows=13573 width=10)
   ->  Seq Scan on students_info  (cost=0.00..4800.73 rows=1697 width=10)
 Optimizer status: legacy query optimizer
(3 rows)

使用到条件查询：

gpdb1=# explain select * from students_info where sid=1;
                                    QUERY PLAN                                    
--
 Gather Motion 8:1  (slice1; segments: 8)  (cost=0.00..4834.66 rows=101 width=10)
   ->  Seq Scan on students_info  (cost=0.00..4834.66 rows=13 width=10)
         Filter: sid = 1
 Optimizer status: legacy query optimizer
(4 rows)

使用 explain analyze:

gpdb1=# explain analyze select sid from students_info where sid=12;
                                                               QUERY PLAN                    
                                           
--
 Gather Motion 8:1  (slice1; segments: 8)  (cost=0.00..4834.66 rows=101 width=4)
   Rows out:  100 rows at destination with 5.653 ms to first row, 9.066 ms to end, start offs
et by 92 ms.
   ->  Seq Scan on students_info  (cost=0.00..4834.66 rows=13 width=4)
         Filter: sid = 12
         Rows out:  Avg 12.5 rows x 8 workers.  Max 18 rows (seg2) with 0.246 ms to first row
, 0.402 ms to end, start offset by 113 ms.
 Slice statistics:
   (slice0)    Executor memory: 344K bytes.
   (slice1)    Executor memory: 163K bytes avg x 8 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 101.820 ms
(12 rows)

Time: 102.819 ms

没有使用到索引的小结

不管使用 explain 还是 explain analyze，都没有在分析中出现 Index Scan,Bitmap Heap Scan, Bitmap Index Scan, BitmapAnd or BitmapOr 等关键字, 此处需要注意。

双字段建索引

那么将 students_info 的两个字段建为位图索引，进行试验：

create index s_bmp_idx on students_info using bitmap(sid,lid);

再次分析

此次出现了使用 s_bmp_idx 索引的情况：

gpdb1=# explain select sid,lid from students_info where sid > 12 and lid < 30;
                                    QUERY PLAN                                     
--
 Gather Motion 8:1  (slice1; segments: 8)  (cost=101.04..150.90 rows=2397 width=8)
   ->  Bitmap Heap Scan on students_info  (cost=101.04..150.90 rows=300 width=8)
         Recheck Cond: sid > 12 AND lid < 30
         ->  Bitmap Index Scan on s_bmp_idx  (cost=0.00..100.44 rows=283 width=0)
               Index Cond: sid > 12 AND lid < 30
 Optimizer status: legacy query optimizer
(6 rows)

小结

要注意 GP 是否真正使用了索引，否则建立索引反而成为浪费。

在建立索引时，应该进行充分的测试。

管理索引

在某些情况下，性能变差可能需要通过REINDEX命令来重建索引。重建索引将使用存储在索引表中的数据建立一个新的索引取代旧的索引。也就是说，其比删除重新建立索引的效率是要高很多的。

在已有数据表上新建索引，最好重建一下索引：

重建索引语法：

gpdb1=# \h reindex
Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

举例：

对数据表的所有索引进行重建：

reindex table students_info;

对某个索引进行重建：

reindex index s_bmp_idx;

删除索引

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

需要注意的是，删除分区表的顶级分区的索引时不会自动删除相关子表的索引，要删除子分区的索引，需要逐个手动删除，使用时需慎重。

drop index s_bmp_idx;

4.8 视图（view）

对于那些使用频繁或者比较复杂的查询，通过创建视图(VIEW)可以把其当作访问一张表一样使用SELECT语句来访问。视图不会像表一样存在于物理介质上。每当视图被访问时，创建视图的查询语句作为子查询被执行。

创建视图

把 lid 为 10 的学生建为一个视图：

create view students_info_l10 as select * from students_info where lid = 10;

注意，目前视图忽略ORDER BY或者SORT操作，虽然在定义视图的语句中可以显式的使用ORDER BY子句，但该子句不会得到执行，除非有LIMIT子句同时出现。

删除视图

drop view students_info_l10;

转载于:https://my.oschina.net/crooner/blog/916779

weixin_33832340

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Greenplum 数据库对象管理

2019独角兽企业重金招聘Python工程师标准>>> ...
复制链接

扫一扫