GreenPlum中的数据库对象

小麦苗DBA宝典

发布于 2023-11-01 20:19:36

4960

发布于 2023-11-01 20:19:36

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

1.管理数据库

greenplum Schema 是 Database中逻辑组织object和data。在同一Database中，不同schema的对象可以使用相同的名称。

系统模式简介：

pg_catalog模式存储系统日志表、内置类型、函数和运算符。
Information_schema模式由一个标准化视图构成。其中包含DB中对象的信息。
pg_toast模式是存储大对象（系统内部使用）。
pg_bitmapindex模式存储bitmap index对象（系统内部使用）。
pg_aoseg存储append-only表（系统内部使用）。
gp_toolkit是管理用的模式，可以查看和检查系统日志文件和其他系统信息。

2.创建与管理数据库

Greenplum如何从无到有？ Greenplum使用PostgreSQL的初始化机制来初始化集群，PostgreSQL使用initdb创建一个PostgreSQL实例。initdb从无到有创建单个空的可用的PostgreSQL数据库，也就是在一个空的目录中创建PostgreSQL运行所需要的所有文件，包括全局数据字典、控制文件和三个数据库：template0、template1、postgres。

由于PostgreSQL自身历史的原因，先创建template1数据库，然后拷贝整个目录成为另外两个数据库。为了创建template1，首先initdb以bootstrap模式运行postgres进程，并使用postgres.bki文件的内容进行初始化；也就是创建PostgreSQL运行需要的重要的元数据，然后通过运行SQL以单节点模式初始化更多元数据。

Initdb Initdb 有大量参数，main()函数入口一开始对各种各样的参数进行检查，然后分为5个阶段进行数据库初始化。

第一阶段，设置数据库安装路径、二进制路径、版本、各种数据模板和locale等，主要函数有：get_restricted_token()，Windows平台相关处理，目的是放弃掉管理员权限，以允许Windows的管理员角色运行initdb初始化数据库；setup_pgdata()，设置PGDATA环境变量，为了运行postgres进程做准备，目的是避免命令行参数引起奇怪的特殊字符转义问题，比如路径中包含空格、# 等字符；setup_bin_paths()，设置bin路径，否则找不到postgres二进制文件；effective_user()设置owner信息，如果一起 ok，会显示一条输出信息”The files belong to this database system will be owned by user ‘yydzero’. This user must also own the server process.”；set_info_version()，从PG_VERSION提取版本信息，后面设置Information schema时需要；setup_data_file_paths()，设置初始化过程中使用的主要数据文件的完整路径，所有数据文件都来自于INSTALLPATH，这些文件包括postgres.bki，postgres.description，postgres.shdescription，pg_hba.conf.sample，pg_indent.conf.sample，postgresql.conf.sample，conversioin_create.sql，information_schema.sql，sql_features.txt，system_views.sql（Greenplum还有一个特定的目录cdb_init.d）；setup_locale_encoding()，设置locale编码；setup_text_search()，设置full text search配置。

第二阶段，创建PGDATA目录、其子目录以及三个主要的配置文件和version文件。这个阶段为使用bootstrap模式创建 template1 做好准备。主要函数有：setup_signals()，设置信号处理函数；create_data_directory()，创建PGDATA目录；create_xlog_or_symlink()，创建WAL日志目录pg_wal；创建PGDATA的子目录，包括global、pg_commit_ts、pg_dynshmem、pg_notify、pg_snapshots、pg_twophase、pg_multixact、base、base/1、pg_replslot、pg_tblspc、pg_xact、pg_stat、pg_logical、pg_distributedlog、log等，其中有的目录为greeplum独有，比如pg_distributedlog；write_version_file()，创建PG_VERSION文件，bootstrapper需要这个文件；setup_config()，设置配置文件，主要有三个配置文件postgresql.conf、pg_hba.conf、pg_ident.conf。

第三阶段，也叫bootstrap，这个阶段使用BKI进行最核心的初始化，主要函数为bootstrap_template1()，该函数以bootstrap模式运行postgres进程，并以BKI文件供养postgres进程，首先对BKI文件中的变量进行替换处理，包括NAMEDATALEN、SIZEOF_POINTER、FLOAT4PASSBYVAL、POSTGRES、ENCODING、LC_COLLATE等，然后使用管道执行命令，输入为BKI数据文件的内容，进程启动命令postgres -boot -x1 -k ‘boot_options’ args，逐行处理BKI文件内容foreach lines: PG_CMD_PUTS(*line)，这个函数会输出很多内容，如Creating template1 database in %s/base/1 … OK。

第四阶段，也叫post-bootstrap，主要使用SQL完成剩余的元数据的初始化，write_version_file(“base/1”)创建文件base/1/PG_VERSION，可以用这个文件来判断Initdb的进展；Setup_auth()初始化pg_authid表，主要是回收所有权限，使得任何人都不能读这张表REVOKE ALL on pg_authid FROM public；get_set_pwd()处理passwd；Setup_depend()插入元数据到pg_depend和pg_shdepend；setup_sysviews()从system_views文件读取指令，并逐条执行SQL，完成system views的初始化；setup_description()加载系统对象的description信息，主要信息来自于desc_file文件，也是前面提到的那些安装目录下的元数据文件postgres.description；setup_conversion()加载conversion_create.sql文件；setup_dictionary()加载词典文件，比如snowball_create.sql；setup_privileges()为内建的数据库对象设置权限；setup_schema()加载information_schema.sql文件；load_plpgsql()加载plpgsql并CREATE EXTENSION plpgsql；setup_cdb_schema()创建Greenplem特定的schema，源数据位于cdb_init.d目录下；vacuum_db()运行Analyze和Vacuum freeze。

第五阶段，主要是数据的copy&paste，直接拷贝template1，创建template0和postgres数据库：make_tempalte0()、make_postgres()。

PostgreSQL/Greenplum的三种模式

Bootstrap模式：从无到有创建数据库的模式，postgres –boot -x1 -k -F
Single模式：单用户模式，只允许单个用户执行SQL命令。Bootstrap创建了最核心的元数据之后使用single模式创建其他数据，对应着postbootstrap阶段
Normal模式：多用户的正常模式

管道技术 BKI数据的处理通过管道技术实现：PG_CMD_OPEN、PG_CMD_CLOSE、PG_CMD_PUTS，底层使用标准C库函数popen、pclose。

故障分析 initdb的noclean选型和debug选项对分析数据库初始化过程中发生的错误很有帮助。

2.1.数据库模版

一个Greenplum数据库是Greenplum数据库的一个单一实例。可能会安装有多个单独的Greenplum数据库系统，但是通常只会用环境变量设置选择其中一个。

一个Greenplum数据库系统中可以有多个数据库。这与某些数据库管理系统（如Oracle）不同，那些系统中数据库实例就是数据库。尽管用户可以在一个Greenplum系统中创建很多数据库，但是客户端程序一次只能连接上并且访问一个数据库，因此用户无法跨数据库进行查询。

用户创建的每一个新数据库都是基于一个模板的。Greenplum提供了一个默认数据库template1。第一次可以使用 postgres连接到Greenplum数据库。除非指定另一个模板，Greenplum会使用template1来创建数据库。不要在template1中创建任何对象，除非用户想要在每一个用户创建的数据库中都有那些对象。

在内部，Greenplum数据库还是用另一个数据库模板template0。不要删除或者修改 template0。用户可以使用template0来创建一个只包含Greenplum数据库在初始化时预定义的标准对象且完全干净的数据库，尤其是在用户修改过 template1的情况下。

2.2.创建一个数据库

CREATE DATABASE 命令会创建一个新的数据库。例如：

=> CREATE DATABASE new_dbname;

要创建一个数据库，用户必须具有创建一个数据库的特权或者是一个Greenplum数据库超级用户。如果用户没有正确的特权，用户就不能创建数据库。可以联系用户的Greenplum数据库管理员为用户授予必要的特权或者替用户创建一个数据库。

用户还可以使用客户端程序createdb来创建一个数据库。例如，在一个使用提供的主机名和端口连接到Greenplum数据库命令行终端中运行下列命令，它将会创建一个名为 mydatabase的数据库：

$ createdb -h masterhost -p 5432 mydatabase

上述主机名和端口必须匹配所安装的Greenplum数据库系统的主机名和端口。

一些对象（如角色）会在一个Greenplum数据库系统的所有数据库之间共享。其他对象（例如用户创建的表）则只出现在创建它们的数据库中。警告： CREATE DATABASE命令不是事务性的。

2.3.克隆一个数据库

默认情况下，一个新数据库通过克隆标准系统数据库模板template1而创建。其实在创建新数据库时，任何一个数据库都可以被用作模板，这样就提供了“克隆”或者复制一个现有数据库及其所包含的所有对象和数据的能力。例如：

=> CREATE DATABASE new_dbname TEMPLATE old_dbname;

2.4.查看数据库的列表

如果用户在使用psql客户端程序，用户可以使用 \l元命令来显示用户的Greenplum数据库系统中的数据库和模板的列表。如果使用的是另一种客户端程序并且用户是超级用户，用户可以从 pg_database系统目录表查询数据库的列表。例如：

=> SELECT datname from pg_database;

2.5.修改一个数据库

ALTER DATABASE命令可以修改数据库的属性，例如拥有者、名称或者默认配置属性。例如，下面的命令会修改一个数据库的默认方案搜索路径（ search_path配置参数）：

=> ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;

要修改一个数据库，用户必须是该数据库的拥有者或者超级用户。

2.6.删除一个数据库

DROP DATABASE命令删除一个数据库。它会移除该数据库的系统目录项并且删除该数据库在磁盘上的目录及其中包含的数据。要删除一个数据库，用户必须是该数据库的拥有者或者超级用户，并且当用户或者其他人正连接到该数据库时不能删除它。在删除一个数据库时，可以连接到postgres（或者另一个数据库）。例如：

=> \c postgres
=> DROP DATABASE mydatabase;

用户也可以使用客户端程序dropdb来删除一个数据库。例如，下面的命令会用给出的主机名和端口连接到Greenplum数据库并且删除数据库mydatabase：

$ dropdb -h masterhost -p 5432 mydatabase

警告：删除数据库不能被撤销。 DROP DATABASE命令不是事务性的。

3.文件空间和表空间

Greenplum中创建表空间的方式和pg中有较大不同，因为gp中有表空间(tablespace)和文件空间(filespace)两个概念。因为在gp中master和segment节点中数据文件肯定得存放在不同的位置，所以我们得想办法将所有的文件系统位置的文件全部都表示起来，因此就引入了文件空间这样一个概念——Greenplum数据库中所有部件所需的文件系统位置集合被称作一个文件空间。文件空间可以被一个或多个表空间使用。

表空间允许数据库管理员在每台机器上拥有多个文件系统并且决定如何最好地使用物理存储来存放数据库对象。表空间允许用户为频繁使用和不频繁使用的数据库对象分配不同的存储，或者在特定的数据库对象上控制I/O性能。例如，把频繁使用的表放在使用高性能固态驱动器（SSD）的文件系统上，而把其他表放在标准的磁盘驱动器上。

表空间需要一个主机文件系统位置来存储其数据库文件。在Greenplum数据库中，文件系统位置必须存在于包括运行master,standby master和每个primary和mirror的所有主机上。

默认系统的文件系统filespace：pg_system在初始化时候建立的
所有的system对象都存放的此文件系统
所有用户的数据也是存放在此文件系统

表空间位于与底层文件系统交互的文件空间之上
一个文件系统可以创建多个表空间
两个默认的表空间： pg_default and pg_global

3.1.创建与管理文件空间

一个文件空间会为用户的Greenplum系统留出存储位置。一个文件空间是一个符号存储标识符，它映射到用户的Greenplum主机文件系统中的一组位置。要创建一个文件空间，应在用户所有的Greenplum主机上准备好逻辑文件系统，然后使用gpfilespace工具来定义文件空间。用户必须是一个数据库超级用户才能创建文件空间。

Greenplum数据库并不会直接意识到在用户底层系统上的文件系统边界。它只会在用户告诉它要使用的目录中存放文件。用户无法在一个逻辑文件系统中控制单个文件在磁盘上的位置。

step 1.在所有GP HOST主机上准备好逻辑文件系统

# gpssh –f seg_hosts –e ‘mkdir –p /gpfs/seg’
# gpssh -f seg_hosts -e 'chown gpadmin /gpfs/seg‘
# mkdir -p /gpfs/master
# chown gpadmin /gpfs/master/

step 2.使用gpfilespace创建一个文件空间作为gpadmin用户登入到Greenplum数据库的Master。

$ su - gpadmin

创建一个文件空间配置文件：

$ gpfilespace -o gpfilespace_config

在提示符处，输入该文件空间的名称、主要Segment文件系统的位置、镜像Segment文件系统的位置以及Master文件系统的位置。主要和镜像位置引用Segment主机上的目录，Master位置引用Master主机和后备Master（如果配置）上的一个目录。例如，如果用户的配置在每个主机上有2个主要Segment和2个镜像Segment：

Enter a name for this filespace> fastdisk 
primary location 1> /gpfs1/seg1 
primary location 2> /gpfs1/seg2 
mirror location 1> /gpfs2/mir1 
mirror location 2> /gpfs2/mir2 
master location> /gpfs1/master

gpfilespace会创建一个配置文件。检查该文件来验证gpfilespace配置是正确的。

再次运行gpfilespace来基于该配置文件创建该文件空间：

$ gpfilespace -c gpfilespace_config

临时表空间和事务文件

临时表空间

PostgreSQL的临时表空间，通过参数temp_tablespaces 进行配置，PostgreSQL允许用户配置多个临时表空间。配置多个临时表空间时，使用逗号隔开。如果没有配置temp_tablespaces 参数，临时表空间对应的是默认的表空间。 PostgreSQL的临时表空间用来存储临时表或临时表的索引，以及执行SQL时可能产生的临时文件例如排序，聚合，哈希等。为了提高性能，一般建议将临时表空间放在SSD或者IOPS，以及吞吐量较高的分区中。

分布式事务日志（Distributed Log）

我们以 CREATE TABLE t1(i int); 为例介绍关键事务日志的时间线。随后讲解事务在各个阶段发生故障时，Greenplum怎样保证数据的一致性。

QD/QE进程更新数据库对象，插入表格。
QD进程通知segments执行PREPARE TRANSACTION，并等待所有primary节点上的QE返回成功。
primary上的QE在本地执行完PREPARE TRANSACTION后，将WAL刷盘，并唤醒walsender进程，并将WAL同步到mirror本地。primary上的QE成功地将WAL同步后，返回结果给QD，至此该primary准备就绪。
所有的segments准备就绪后，QD在本地写入DISTRIBUTED_COMMIT日志并刷盘，然后唤醒walsender进程，并等待将日志同步至standby。
QD进程发起两阶段提交的COMMIT PREPARED。
primary上的QE进程执行COMMIT PREPARED。QE在本地写入COMMIT_PREPARED日志并刷盘，唤醒walsender进程后，等待将日志同步至mirror。
primary上的QE进程等待日志成功同步至mirror后，将结果返回给QD。
QD等待所有的segments都成功提交后，写入DISTRIBUTED_FORGET。此记录并不需要刷盘，没有它数据库也能工作。它的作用仅仅是优化了数据库恢复过程，告诉恢复进程“这个分布式事务已经成功提交(包括segments），忽略掉它，不要再尝试提交了”

GreenPlumSQL执行流程及事务处理：

SQL事务处理架构图：

Master SQL处理过程图：

SQL处理流程图：

临时表空间和事务文件的作用：改善DB的查询性能、备份性能、连续存储数据的性能

只有SUPERUSER可以移动位置，只有gpfilespace工具可以写该文件
只能为临时文件或事务文件指定一个文件空间
如果文件空间被临时文件使用，不能删除

除非另外指定，临时和事务文件和所有的用户数据存储在一起。当用户第一次使用 gpfilespace –movetempfiles时，默认的临时文件位置 <filespace_directory>/<tablespace_oid>/<database_oid>/pgsql_tmp会被改变。

以下关于临时或者事务文件的信息：

用户只能把一个文件空间专用于临时或者事务文件，不过用户可以使用同一个文件空间来存放其他类型的文件。
如果一个文件空间被临时文件使用，用户就不能删除它。
用户必须先创建文件空间。

移动临时或者事务文件的位置

用户可以把临时或者事务文件移动到一个特定的文件空间，以便在运行查询、创建备份时改进性能，并且更加连续地存储数据。

临时和事务文件专用的文件空间由两个独立的平面文件 gp_temporary_files_filespace和gp_transaction_files_filespace 跟踪。它们位于每一个主要和镜像Segment、Master和后备Master上的pg_system目录中。要移动临时或者事务文件，用户必须是一个超级用户。只有gpfilespace工具能写入到这个文件。

使用gpfilespace移动临时文件

检查文件空间存在并且和用于存放所有其他用户数据的文件空间不同。

发出smart关闭让Greenplum数据库下线。如果还有连接在进行中，gpfilespace –movetempfiles工具将会失败。

将Greenplum数据库上线但没有活动会话，并且运行下面的命令：

gpfilespace --movetempfilespace filespace_name

事务文件的位置存储在Segment的配置共享内存（PMModuleState）中并且在创建、打开或者删除事务文件时使用。

转移临时文件或事务文件的位置使用gpfilespace移动事务文件

确保文件空间存在，且与存储其他用户数据的文件空间不同

停止GPDB

将GPDB启动为限制模式

$ gpfilespace –movetransfilespace test_fs

事务文件位置在Instance中配合共享内存使用，在创建、打开、删除事务文件时用到。

3.2.创建与管理表空间

表空间即PostgreSQL存储数据文件的位置，其中包括数据库对象。如，索引、表等。 PostgreSQL使用表空间映射逻辑名称和磁盘物理位置。默认提供了两个表空间：

pg_default 表空间存储用户数据.
pg_global 表空间存储全局数据.

利用表空间可以控制PostgreSQL的磁盘布局，它有两方面的优势：首先，如果集群中的某个分区超出初始空间，可以在另一个分区上创建新的表空间并使用。后期可以重新配置系统。其次，可以使用统计优化数据库性能。举例，可以把频繁访问的索引或表放在高性能的磁盘上，如固态硬盘；把归档数据放在较慢的设备上。使用CREATE TABLESPACE语句创建表空间，语法如下：

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;

可以使用\db+查看更详细的表空间信息。

postgres=# \db+
                                  表空间列表
    名称    |  拥有者  |       所在地       | 存取权限 | 选项 |  大小   | 描述
------------+----------+--------------------+----------+------+---------+------
 pg_default | postgres |                    |          |      | 6558 MB |
 pg_global  | postgres |                    |          |      | 575 kB  |
 ts_primary | postgres | E:\pg-data\primary |          |      | 7513 kB |
(3 行记录)

创建表空间

在用户创建了一个文件空间后，可使用CREATE TABLESPACE命令定义一个使用该文件空间的表空间。例如：

=# CREATE TABLESPACE fastspace FILESPACE fastdisk;

数据库超级用户可以用GRANTCREATE command定义表空间并且为数据库用户授予访问权限。例如：

=# GRANT CREATE ON TABLESPACE fastspace TO admin;

使用表空间存储DB对象

在一个表空间上拥有CREATE特权的用户可以在其中创建数据库对象，例如表、索引和数据库。命令是：

CREATE TABLE tablename(options) TABLESPACE spacename

例如，下列命令在表空间space1中创建一个表：

CREATE TABLE foo(i int) TABLESPACE space1;

用户也可以使用default_tablespace参数为没有指定表空间的CREATE TABLE and CREATE INDEX命令指定默认表空间：

SET default_tablespace = space1;
CREATE TABLE foo(i int);

与一个数据库相关的表空间存放着该数据库的系统目录、使用该数据库的服务器进程创建的临时文件，并且在创建对象（表和索引）且没有指定TABLESPACE时充当该数据库中的默认表空间。如果用户在创建一个数据库时没有为它指定表空间，该数据库将使用与其模板数据库相同的表空间。

如果有适当的特权，用户可以从任何数据库使用一个表空间。

如果一个表空间与DB关联，那么其将存储所有该DB的系统日志、临时文件等

3.3.查看现有的表空间和文件空间

每一个Greenplum数据库系统都有下列默认的表空间。

pg_global用于共享的系统目录。
pg_default是默认表空间。由template1和template0数据库使用。

gp中默认的两个表空间pg_default和pg_global是存放在pg_system文件空间中的。

文件空间的信息可以在目录表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;

3.4.删除表空间和文件空间

要删除一个表空间，用户必须是该表空间的拥有者或者超级用户。如果要删除一个表空间，只有在所有数据库中所有使用该表空间的对象都被删除之后才能做到。

只有超级用户才能删除一个文件空间。只有所有使用一个文件空间的表空间都被移除后，该文件空间才能被删除。

对象被删除前，表空间不能被删除; 表空间被删除前，文件空间不能被删除。

通过DROP TABLESPACE命令删除表空间

=# DROP TABLESPACE testspace;

通过DROP FILESPACE 命令删除文件空间

=# DROP FILESPACE test_fs;

如果一个文件空间存储着临时或者事务文件，用户不能删除它。

4.创建与管理模式

SCHEMA 从逻辑上组织一个数据库中的对象和数据。 SCHEMA 允许用户在同一个数据库中拥有多于一个对象（例如表）具有相同的名称而不发生冲突，只要把它们放在不同的方案中就好。

默认的“Public” SCHEMA

每个数据库都一个名为public的默认 SCHEMA 。如果用户没有创建任何 SCHEMA ，对象会被创建在这个public SCHEMA 中。所有的数据库角色（用户）都在public SCHEMA 中拥有 CREATE和USAGE特权。在用户创建一个 SCHEMA 时，用户应该为允许访问该 SCHEMA 的用户授予特权。创建一个方案

可以使用CREATE SCHEMA命令来创建一个新的 SCHEMA 。例如：

=> CREATE SCHEMA myschema;

要在一个 SCHEMA 中创建或者访问对象，需要写一个由 SCHEMA 名和表名构成的限定名，两者之间用点号隔开。例如：

myschema.table

用户可以创建一个由他人拥有的 SCHEMA ，例如用来把用户的活动限定在定义良好的名字空间内。语法是：

=> CREATE SCHEMA schemaname AUTHORIZATION username;

SCHEMA 搜索路径

要在数据库中指定一个对象的位置，请使用 SCHEMA 限定的名称。例如：

=> SELECT * FROM myschema.mytable;

用户可以设置search_path配置参数来指定在其中搜索对象的可用 SCHEMA 的顺序。在该搜索路径中第一个列出的 SCHEMA 会成为所谓的默认 SCHEMA 。如果没有指定 SCHEMA ，对象会被创建在默认 SCHEMA 中。

设置方案搜索路径

search_path配置参数设置 SCHEMA 搜索顺序。ALTER DATABASE命令可以设置搜索路径。例如：

=> ALTER DATABASE mydatabase SET search_path TO myschema,
public, pg_catalog;

用户也可以使用ALTER ROLE命令为特定的角色（用户）设置search_path。例如：

=> ALTER ROLE sally SET search_path TO myschema, public, 
pg_catalog;

查看当前方案

使用current_schema()函数可以查看当前的 SCHEMA 。例如：

=> SELECT current_schema();

使用SHOW命令可以查看当前的搜索路径。例如：

=> SHOW search_path;

删除一个方案

使用DROP SCHEMA命令可以删除一个 SCHEMA 。例如：

=> DROP SCHEMA myschema;

默认情况下，在能够删除一个 SCHEMA 前，它必须为空。要删除一个 SCHEMA 连同其中的所有对象（表、数据、函数等等），可以使用：

=> DROP SCHEMA myschema CASCADE;

系统方案

下列系统级 SCHEMA 存在于每一个数据库中：

pg_catalog包含着系统目录表、内建数据类型、函数和操作符。即便在方案搜索路径中没有显式地提到它，它也总是方案搜索路径的一部分。
information_schema有一个包含数据库中对象信息的视图集合组成。这些视图以一种标准化的方式从系统目录表中得到系统信息。
pg_toast存储大型对象，如超过页面尺寸的记录。这个方案由Greenplum数据库系统内部使用。
pg_bitmapindex存储位图索引对象，例如值的列表。这个方案由Greenplum数据库系统内部使用。
pg_aoseg存储追加优化表对象。这个方案由Greenplum数据库系统内部使用。
gp_toolkit是一个管理用途的方案，它包含用户可以用SQL命令访问的外部表、视图和函数。所有的数据库用户都能访问gp_toolkit来查看和查询系统日志文件以及其他系统指标。

5.创建与管理表

Greenplum数据库的表与任何一种关系型数据库中的表类似，不过其表中的行被分布在系统中的不同Segment上。当用户创建一个表时，用户会指定该表的分布策略。

CREATE TABLE命令创建一个表并且定义它的结果。当用户创建一个表示，用户需要定义：

该表的列以及它们的数据类型
任何用于限制列或者表中能包含的数据的表或者列约束
表的分布策略，这决定了Greenplum数据库如何在Segment之间划分数据，指定DISTRIBUTED BY（哈希分布）或者 DISTRIBUTED RANDOMLY（循环分布）来决定该表的行分布
表存储在磁盘上的方式,比如压缩、列存储，压缩存储等
大型表的表分区策略

5.1.选择列的数据类型

一个列的数据类型决定了该列能包含的数据的类型。选择的数据类型应使用最少的空间，但仍能容纳用户的数据并且能最好地约束数据。例如，对字符串使用character数据类型，对于日期使用date或者timestamp数据类型，而对数字使用numeric数据类型。

对于包含文本数据的表列，应指定数据类型为 VARCHAR或者TEXT。不推荐指定数据类型为 CHAR。在Greenplum数据库中数据类型 VARCHAR或者TEXT会把增加到数据的填充（在最后一个非空白字符后面增加的空白字符）处理为有效字符，而数据类型CHAR不会这样做。

使用能容纳用户的数字型数据的且允许未来扩张的最小数字数据类型。例如，为适合INT or SMALLINT的数据使用BIGINT会浪费存储空间。如果用户预期用户的数据值将会随着时间扩张，应该考虑到在装载大量数据后从较小的数据类型更改成较大的数据类型需要很大的代价。例如，如果用户当期的数据值适合SMALLINT，但是很可能值会扩张，这样INT就是更好的长期选择。

对用户计划要用在交叉表连接中的列使用相同的数据类型。交叉表连接通常使用一个表中的主键和其他表中的外键。当数据类型不同时，数据库必须转换其中之一以便数据值能被正确地比较，这会增加不必要的开销。

Greenplum数据库为用户提供了丰富的本地数据类型集合。

5.2.设置表和列约束

用户可以在列和表上定义约束来限制表中的数据。Greenplum数据库支持和PostgreSQL相同的约束，但是有一些限制，包括：

CHECK约束只能引用它所在的表。
UNIQUE和PRIMARY KEY约束必须和它们所在表的分布键和分区键（如果有）兼容。
允许FOREIGN KEY约束，但不会被强制。
用户在分区表上定义的约束将作为整体应用到分区表上。用户不能在该表的单独的部分上定义约束。

检查约束检查约束允许用户指定一个特定列中的值必须满足一个布尔（真值）表达式。例如，要求正的产品价格：

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

唯一约束唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。该表必须是哈希分布（非DISTRIBUTED RANDOMLY）的，并且约束列必须是该表的分布键列（或者是一个超集）。例如：

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

主键主键约束是一个UNIQUE约束和一个 NOT NULL约束的组合。该表必须是哈希分布（非DISTRIBUTED RANDOMLY）的，并且约束列必须是该表的分布键列（或者是一个超集）。如果一个表具有主键，这个列（或者这一组列）会被默认选中为该表的分布键。例如：

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

外键不支持外键。用户可以声明它们，但是参照完整性不会被实施。外键约束指定一列或者一组列中的值必须匹配出现在另一个表的某行中的值，以此来维护两个相关表之间的参照完整性。参照完整性检查不能在一个Greenplum数据库的分布表段之间实施。

5.3.选择表分布策略

所有的Greenplum数据库表都会被分布。当用户创建或者修改一个表时，用户可以有选择地指定DISTRIBUTED BY（哈希分布）或者 DISTRIBUTED RANDOMLY（循环分布）来决定该表的行分布。

如果创建表时没有指定DISTRIBUTED BY，Greenplum数据库服务器配置参数 gp_create_table_random_default_distribution控制表的分布策略。

在决定表分布策略时，请考虑以下几点。

均匀数据分布 — 为了最好的性能，所有的Segment应该包含等量的数据。如果数据不平衡或者倾斜，具有更多数据的Segment就必须做更多工作来执行它那一部分的查询处理。请选择对于每一个记录都唯一的分布键，例如主键。
本地和分布式操作 — 本地操作比分布式操作更快。在Segment层面上，如果与连接、排序或者聚集操作相关的工作在本地完成，查询处理是最快的。在系统层面完成的工作要求在Segment之间分布元组，其效率会低些。当表共享一个共同的分布键时，在它们共享的分布键列上的连接或者排序工作会在本地完成。对于随机分布策略来说，本地连接操作就行不通了。
均匀查询处理 — 为了最好的性能，所有的Segment应该处理等量的查询负载。如果一个表的数据分布策略与查询谓词匹配不好，查询负载可能会倾斜。例如，假定一个销售事务表按照客户ID列（分布键）分布。如果查询中的谓词引用了一个单一的客户ID，该查询处理工作会被集中在一个Segment上。

声明分布键 CREATE TABLE的可选子句DISTRIBUTED BY和DISTRIBUTED RANDOMLY指定一个表的分布策略。默认是使用PRIMARY KEY（如果表有主键）或者表的第一个列作为分布键的哈希分布策略。几何或者用户定义数据类型的列不能作为Greenplum分布键列。如果一个表没有符合要求的列，Greenplum会以随机或者循环方式分布行。

为了确保数据的均匀分布，应该选择对每个记录都唯一的分布键。如果做不到，可选择DISTRIBUTED RANDOMLY。例如：

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

主键总是表的分布键。如果不存在主键，但是存在唯一键，那么唯一键就是该表的分布键。

5.4.选择表的存储模式

Greenplum（以下简称 GP）有2种存储格式，Heap 表和 AO 表（AORO 表，AOCO 表）。

Heap 表：这种存储格式是从 PostgreSQL 继承而来的，目前是 GP 默认的表存储格式，只支持行存储。 AO 表： AO 表最初设计是只支持 append 的（就是只能 insert ），因此全称是Append-Only，在4.3之后进行了优化，目前已经可以 update 和 delete 了，全称也改为 Append-Optimized。AO 支持行存储（AORO）和列存储（AOCO）。

Heap表

Heap 表是从 PostgreSQL 继承而来，使用 MVCC 来实现一致性。如果你在创建表的时候没有指定任何存储格式，那么 GP 就会使用 Heap 表。

Heap 表支持分区表，只支持行存，不支持列存和压缩。需要注意的是在处理 update 和 delete 的时候，Heap 表并没有真正删除数据，而只是依靠 version 信息屏蔽老的数据，因此如果你的表有大量的 update 或者 delete，表占用的物理空间会不断增大，这个时候需要依靠 vacuum 来清理老数据。

Heap 表不支持逻辑增量备份，因此如果要对 Heap 表做快照，每次都需要导出全量数据。

如果该表是一张小表，比如数仓中的维度表，或者数据量在百万以下，推荐使用 Heap 表。

如果该表的使用场景是 OLTP 的，比如有较多的 update 和 delete，查询多是带索引的点查询等，推荐使用 Heap 表。

缺省的存储模式

=> CREATE TABLE tb_heap_01(id int) DISTRIBUTED BY (id)

AO表

AO 表是 GP 特有的，设计的目的就是为了数仓中大型的事实表。AO 表支持行存和列存，并且也支持对数据进行压缩。

AO 表无论是在表的逻辑结构还是物理结构上，都与 Heap 表有很大的不同。比如上文所述 Heap 表使用 MVCC 控制 update 和 delete 之后数据的可见性，而 AO 表则使用一个附加的 bitmap 表来实现，这个表的的内容就是表示 AO 表中哪些数据是可见的。

对于有大量 update 和 delete 的 AO 表，同样需要 vacuum 进行维护，不过在 AO 表中， vacuum 需要对 bitmap 进行重置并压缩物理文件，因此通常比 Heap 的 vacuum 要慢。

只追加存储适合仓库中事实大表，通常是批量装载数据并只进行只读查询操作，不支持UPADTE和DELETE操作。

AORO表

AORO 就是行存的 AO 表，同时行存也是 AO 表的默认存储方式。 AORO 支持表级别的压缩，不支持列级别的压缩。

AO 表主要是针对大表，比如数仓中的事实表。
AO 表支持逻辑增量备份，对于比较大的表，如果需要定期做快照，建议使用 AO 表，否则每次都要导出全量数据。
如果该表是大表，使用场景偏 OLTP 并且 update 和 delete 频率不高，可以考虑使用 AORO 表。
如果该表是大表，并且查询通常都需要扫描大多数列比如查询明细（最典型的就是 SELECT * FROM ），可以考虑使用 AORO 表。
在设置压缩级别的时候，通常对于数据仓库用户，设置到 4 或者 5 是比较折中的一个选择。

建表语句如下，重点是 with 后的 appendonly=true，由于 AO 表默认是行存，因此 orientation=row 也可以不要，后面的 compresstype=zlib, compresslevel=4 都是压缩相关选项。

CREATE TABLE aoro(
    a int,
    b int,
    c varchar(32),
    d varchar(32)
)
WITH (appendonly=true, orientation=row, compresstype=zlib, compresslevel=4)
DISTRIBUTED BY (a)

压缩选项：

compresstype ：压缩格式，开源版本的 AORO 表只支持 zlib。
compresslevel ：压缩级别，从1-9，简单说来，级别越低（1最低），压缩比越低，但是压缩与解压消耗的 cpu 资源就越少。默认压缩级别是1。

AOCO表

AOCO 表就是列存的 AO 表。 AOCO 不仅支持表级别的压缩，同时也支持列级别的压缩。

CREATE TABLE aoco(
    a int  ENCODING (compresstype=zlib, compresslevel=5),
    b int  ENCODING (compresstype=none),
    c varchar(32) ENCODING (compresstype=RLE_TYPE, blocksize=32768),
    d varchar(32),
    fdate date
)
WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=6, blocksize=65536)
DISTRIBUTED BY (a)
PARTITION BY RANGE(fdate)
(
    PARTITION pn START ('2018-11-01'::date) END ('2018-11-10'::date) EVERY ('1 day'::interval),
    DEFAULT PARTITION pdefault
);

压缩选项：

compresstype：支持2种压缩格式，zlib 和 RLE_TYPE，其中 RLE_TYPE（Run-length Encoding）对于有较多重复值的列压缩比很高，因为它会将多个重复值存储为一个值，从而大大降低存储量，比如日期，性别，年龄等字段。
compresslevel：compresstype 如果是 zlib，compresslevel 在1-9，compresstype 如果是 RLE_TYPE，compresslevel 在1-4。
列压缩与表压缩：AOCO 表除了支持表级别的压缩外，还支持列级别的压缩，列级别的压缩配置会覆盖表级别的压缩配置，比如上述语法中4个字段，每个字段都采用了不用的压缩方式，d 列没有定义，则会默认使用表级别的压缩方式。
分区压缩：在使用分区表的时候，每个分区表也可以设置不同的压缩配置，这个常用于对数据进行冷热分离，比如对于非常老的数据，由于访问频率较低，可以考虑采用较大的压缩比，减少存储量。

BLOCKSIZE：

表的存储块大小，通常表数据对应的物理文件就是按 blocksize 的粒度增加，也就是初始就是 blocksize 大，并且保持 blocksize 的倍数。
blocksize 大小在8192和2097152之间，必须是 8192 的倍数，默认是 32768。
在 AOCO 表中，每一列也可以设置自己的 blocksize，列的配置会覆盖表的配置。

物理文件：

AOCO 表之所以能够按照列来设置压缩等参数，本质原因在于 AOCO 表中每一列的数据都会单独存储在一个文件中。因此不同文件之间可以按不同的参数进行存储，互不影响。
对于 AOCO 表，如果使用了分区，那么对于每一个分区的每一列都会有一个文件，如果一个表的分区很多，又是一张大宽表，那么产生的文件就会很多，也会对性能有一些影响。

AOCO 表通常用于数仓中的核心事实表，这种表字段多，数据量大，主要是用于 OLAP 场景，也就是查询的过程不会 SELECT * FROM，而是对其中部分字段进行读取和聚合。

由于 AOCO 表一般用于大表，因此经常搭配压缩和分区，以减少表的实际存储量来提升性能。

一般情况下，压缩格式选择 zlib，压缩级别可以采用折中的 4 或者 5，但是对于有大量重复值的字段，记得要采用 RLE_TYPE 压缩格式。

blocksize 不要设置过大，特别是对于分区表，GP 对于每个分区的每个字段都会维护一个 buffer，blocksize 过大，会导致消耗的内存过大，通常就采用默认值 32768 即可。

选择行存储(Row-Orientation)或列存储(Column-Orientation) 考虑因素：

表数据的更新数据需要更新，只能选择行存储。

经常做INSERT操作如果经常有数据被INSERT，考虑选择行存储。

查询设计的列数量如果在SELECT或WHERE中涉及表的全部或大部分列时，考虑行存储。列存储适用于在WHERE或HAVING中队单列作聚合操作：

SELECT SUM(salary)…
SELECT AVG(salary)…WHERE salary>10000
或在WHERE条件中使用单个列条件且返回少量的行
SELECT salary, dept…WHERE state=‘CA’

表的列数量行存储对于列多或行尺寸相对小的表更高效；列存储在只访问宽表的少量列的查询中性能更高。

使用压缩存储列存储表具有压缩优势。

在CREATE TABLE时使用WITH子句指定表的存储模式。

=> CREATE TABLE tb_col_01(a int, b text) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (a);

5.5.对大型表分区

表分区让我们能通过把表划分成较小的、更容易管理的小块来支持非常大的表，例如事实表。通过让Greenplum数据库查询优化器只扫描满足给定查询所需的数据而避免扫描大表的全部内容，分区表能够提升查询性能。

表分区并不会改变表数据在Segment之间的物理分布。表分布是物理的：Greenplum数据库会在物理上把分区表和未分区表划分到多个Segment上来启用并行查询处理。表分区是逻辑的：Greenplum数据库在逻辑上划分大表来提升查询性能并且有利于数据仓库维护任务，例如把旧数据滚出数据仓库。

Greenplum数据库支持：

范围分区：基于一个数字型范围划分数据，例如按照日期或价格划分。
列表分区：基于一个值列表划分数据，例如按照销售范围或产品线划分。
两种类型的组合。

Greenplum数据库把表划分成部分（也称为分区）来启用大规模并行处理。表分区在使用PARTITION BY（以及可选的SUBPARTITION BY）子句的CREATE TABLE执行期间进行。分区操作会创建一个顶层（父）表以及一层或者多层子表。在内部，Greenplum数据库会在顶层表和它的底层分区之间创建继承关系，类似于PostgreSQL的INHERITS子句的功能。

Greenplum使用表创建时定义的分区标准来创建每一个分区及其上一个可区分的CHECK约束，这个约束限制了该表能含有的数据。查询优化器使用CHECK约束来决定要扫描哪些表分区来满足一个给定的查询谓词。

Greenplum系统目录存储了分区层次信息，这样插入到顶层父表的行会被正确地传播到子表分区。要更改分区设计或者表结构，可使用带有PARTITION子句的ALTER TABLE修改父表。

要把数据插入到一个分过区的表中，用户需要指定根分区表，也就是用CREATE TABLE命令创建的那个表。用户也可以在INSERT命令中指定分区表的一个叶子子表。如果该数据对于指定的叶子子表不合法，则会返回一个错误。不支持在DML命令中指定一个非叶子或者非根分区表。

决定一个表的分区策略

不是所有的表都适合于分区。如果下列问题的答案全部或者大部分都是yes，表分区就是一种可行的改进查询性能的数据库设计策略。如果下列问题的答案大部分都是no，表分区对于该表就不是正确的方案。请测试用户的设计策略来确保查询性能能得到预期的改进。

表是否足够大？大型的事实表是进行表划分很好的候选。如果在一个表中有几百万或者几十亿个记录，从逻辑上将数据分成较小的块会让用户在性能方面受益。对于只有几千行或者更少数据的小表来说，维护分区的管理开销将会超过用户可能得到的性能收益。
用户是否体验到不满意的性能？正如任何性能调节的动机一样，只有针对一个表的查询产生比预期还要慢的响应时间时才应该对该表分区。
用户的查询谓词有没有可识别的访问模式？检查用户的查询负载的WHERE子句并且查找一直被用来访问数据的表列。例如，如果大部分查询都倾向于用日期查找记录，那么按月或者按周的日期分区设计可能会对用户有益。或者如果用户倾向于根据地区访问记录，可考虑一种列表分区设计来根据地区划分表。
用户的数据仓库是否维护了一个历史数据的窗口？另一个分区设计的考虑是用户的组织对维护历史数据的业务需求。例如，用户的数据仓库可能要求用户保留过去十二个月的数据。如果数据按月分区，用户可以轻易地从仓库中删除最旧的月份分区并且把当前数据载入到最近的月份分区中。
数据能否基于某种定义的原则被划分成差不多相等的部分？尽可能选择将把用户的数据均匀划分的分区原则。如果分区包含基本同等数量的记录，查询性能会基于创建的分区数量而提升。例如，通过将一个大型表划分成10个分区，一个查询的执行速度将比在未分区表上快10倍，前提是这些分区就是为支持该查询的条件而设计。

不要创建超过所需数量的分区。创建过多的分区可能会拖慢管理和维护工作，例如清理、恢复Segment、扩展集群、检查磁盘用量等等。

除非查询优化器能基于查询谓词排除一些分区，分区技术就不能改进查询性能。每个分区都扫描的查询运行起来会比表没有分区时还慢，因此如果用户的查询中很少能实现分区排除，请避免进行分区。请检查查询的解释计划来确认分区被排除。

警告：请对多级分区格外谨慎，因为分区文件的数量可能会增长得非常快。例如，如果一个表被按照日和城市划分并且有1,000个日以及1,000个城市，那么分区的总数就是一百万。列存表会把每一列存在一个物理表中，因此如果这个表有100个列，系统就需要为该表管理一亿个文件。

在选定一种多级分区策略之前，可以考虑一种带有位图索引的单级分区。索引会降低数据装载的速度，因此推荐用用户的数据和模式进行性能测试以决定最佳的策略。

创建分区的表

在使用CREATE TABLE创建表时就可以对它们分区。这个主题提供了用于创建带有数个分区的表的SQL语法的例子。

要对一个表分区：

决定分区设计：日期范围、数字范围或者值的列表。
选择要按哪个（哪些）列对表分区。
决定用户需要多少个分区级别。例如，用户可以按月创建一个日期范围分区表，然后对每个月的分区按照销售地区划分子分区。

定义日期范围表分区
定义数字范围表分区
定义列表表分区
定义多级分区
对一个现有的表进行分区

定义日期范围表分区

一个按日期范围分区的表使用单个date或者timestamp列作为分区键列。如果需要，用户可以使用同一个分区键列来创建子分区，例如按月分区然后按日建子分区。请考虑使用最细的粒度分区。例如，对于一个用日期分区的表，用户可以按日分区并且得到365个每日的分区，而不是先按年分区然后按月建子分区再然后按日建子分区。一种多级设计可能会减少查询规划时间，但是一种平面的分区设计运行得更快。

用户可以通过给出一个START值、一个END值以及一个定义分区增量值的子句让Greenplum数据库自动产生分区。默认情况下，START值总是被包括在内而END值总是被排除在外。例如：

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 '2017-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 Jan16 START (date '2016-01-01') INCLUSIVE , 
  PARTITION Feb16 START (date '2016-02-01') INCLUSIVE ,
  PARTITION Mar16 START (date '2016-03-01') INCLUSIVE ,
  PARTITION Apr16 START (date '2016-04-01') INCLUSIVE ,
  PARTITION May16 START (date '2016-05-01') INCLUSIVE ,
  PARTITION Jun16 START (date '2016-06-01') INCLUSIVE ,
  PARTITION Jul16 START (date '2016-07-01') INCLUSIVE ,
  PARTITION Aug16 START (date '2016-08-01') INCLUSIVE ,
  PARTITION Sep16 START (date '2016-09-01') INCLUSIVE ,
  PARTITION Oct16 START (date '2016-10-01') INCLUSIVE ,
  PARTITION Nov16 START (date '2016-11-01') INCLUSIVE ,
  PARTITION Dec16 START (date '2016-12-01') INCLUSIVE 
                  END (date '2017-01-01') EXCLUSIVE );

用户不需要为每一个分区声明一个END子句，只需要为最后一个分区写上就好。在这个例子中，Jan16会在Feb16开始处结束。

定义数字范围表分区

一个按数字范围分区的表使用单个数字数据类型列作为分区键列。例如：

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) 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 );

当前的Greenplum数据库传统优化器允许列表分区带有多列（组合）分区键。一个范围分区只允许单一列作为分区键。Greenplum查询优化器不支持组合键，因此用户不能使用组合分区键。

定义多级分区

用户可以用分区的子分区创建一种多级分区设计。使用一个子分区模板可以确保每一个分区都有相同的子分区设计，包括用户后来增加的分区。例如，下面的SQL创建图 1中所示的两级分区设计：

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 '2011-01-01') INCLUSIVE
   END (date '2012-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month'), 
   DEFAULT PARTITION outlying_dates );

下面的例子展示了一个三级分区设计，其中 sales表被按照year分区，然后按照 month分区，再然后按照region分区。SUBPARTITION TEMPLATE子句保证每一个年度的分区都有相同的子分区结构。这个例子在该层次的每一个级别上都声明了一个DEFAULT分区。

CREATE TABLE p3_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 (2012) EVERY (1), 
  DEFAULT PARTITION outlying_years );

当用户创建基于范围的多级分区时，很容易会创建大量的子分区，有一些包含很少的甚至不包含数据。这可能会在系统表中增加很多项，这些项增加了优化和执行查询所需的时间和内存。增加范围区间或者选择一种不同的分区策略可减少创建的子分区数量。

对一个现有的表进行分区

表只能在创建时被分区。如果用户有一个表想要分区，用户必须创建一个分过区的表，把原始表的数据载入到新表，再删除原始表并且把分过区的表重命名为原始表的名称。用户还必须重新授权表上的权限。例如：

CREATE TABLE sales2 (LIKE sales) 
PARTITION BY RANGE (date)
( START (date 2016-01-01') INCLUSIVE
   END (date '2017-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;

分区表的限制

对于每个分区级别，一个已分区的表最多能有32,767个分区。

一个已分区表上的主键或者唯一约束必须包含所有的分区列。一个唯一索引可以忽略分区列，但是它只能在已分区表的每个部分而不是整个已分区的表上被强制。

Greenplum的下一代查询优化器GPORCA支持统一的多级分区表。如果启用了GPORCA（默认情况）并且多级分区表不统一，Greenplum数据库会用传统查询优化器对该表执行查询。

如果一个分区表由SUBPARTITION子句创建或者一个分区有一个子分区，则不支持把一个叶子子分区与一个外部表交换。

当一个叶子子分区是外部表时，对分区表有一些限制：

针对包含外部表分区的分区表运行的查询将用传统查询优化器执行。
外部表分区是一个只读外部表。尝试在该外部表分区中访问或者修改数据的命令会返回一个错误。例如：
- 尝试在外部表分区中改变数据的INSERT、DELETE以及UPDATE命令会返回一个错误。
- TRUNCATE命令返回一个错误。
- COPY命令无法复制数据到一个会更新外部表分区的分区表中。
- 尝试从一个外部表分区中复制出数据的COPY命令会返回一个错误，除非用户为COPY命令指定IGNORE EXTERNAL PARTITIONS子句。如果用户指定该子句，数据不会被从外部表分区复制出来。要对一个有外部表作为叶子子表的分区表使用COPY命令，可以使用一个SQL查询来拷贝数据。例如，如果表my_sales包含一个外部表作为叶子子表，这个命令可以把其数据发送到 stdout：COPY (SELECT * from my_sales ) TO stdout
- VACUUM命令会跳过外部表分区。
如果在外部表分区上没有数据改变，则支持下列操作。否则，返回一个错误。
- 增加或者删除一列。
- 更改列的数据类型。
如果分区表包含一个外部表分区，则不支持这些ALTER PARTITION操作：
- 设置一个子分区模板。
- 更改分区性质。
- 创建一个默认分区。
- 设置一种分布策略。
- 设置或者删除列的一个NOT NULL约束。
- 增加或者删除约束。
- 分裂一个外部分区。
如果分区表的一个叶子子分区是一个可读的外部表，Greenplum数据库工具gpcrondump不会从该叶子子分区中备份数据。

载入分区表

在用户创建了分区表结构之后，顶层父表为空。数据会被路由到底层的子表分区中。在一个多级分区设计中，只有层次底部的子分区能够包含数据。

不能被映射到一个子表分区的行会被拒绝并且载入会失败。为了避免无法映射的行在载入时被拒绝，可以为用户的分区层次定义一个 DEFAULT分区。任何不匹配一个分区的CHECK约束的行会被载入到DEFAULT分区。

在运行时，查询优化器扫描整个表继承层次并使用CHECK表约束来决定要扫描哪个子表分区来满足查询的条件。DEFAULT分区（如果用户的层次中有一个）总是会被扫描。包含数据的DEFAULT分区会拖慢总体扫描时间。

当用户使用COPY或者INSERT来载入数据到父表时，数据会被自动路由到正确的分区，这就像是向一个常规表中载入数据一样。

向分区表中载入数据的最佳方法是创建一个中间状态表，把数据载入其中，然后把它交换到用户的分区设计中。

验证分区策略

当一个表基于查询谓词被分区时，用户可以使用 EXPLAIN来验证查询优化器只扫描相关的数据来检查查询计划。

例如，假设一个sales表被按日期范围分区，先用月份分区然后用地区建立子分区，如图 1所示。对于下列查询：

EXPLAIN SELECT * FROM sales WHERE date='01-07-12' AND 
region='usa';

这个查询的查询计划应该展示只涉及到下列表的表扫描：

返回0-1行的默认分区（如果用户的分区设计有一个默认分区）
返回0-1行的January 2012分区（sales_1_prt_1）
返回若干行的USA地区子分区（sales_1_2_prt_usa）。

下面的例子展示了相关的查询计划片段。

->  Seq Scan onsales_1_prt_1 sales (cost=0.00..0.00 rows=0 
     width=0)
Filter: "date"=01-07-12::date AND region='USA'::text
->  Seq Scan onsales_1_2_prt_usa sales (cost=0.00..9.87 
rows=20 
      width=40)

确保查询优化器不会扫描不必要的分区或者子分区（例如，扫描没有在查询谓词中指定的月份或者地区），以及顶层表的扫描返回0-1行。

选择性分区扫描排查

下列限制可能导致一个对用户的分区层次进行非选择性扫描的查询计划。

只有当查询包含表的使用不可变操作符，例如=、<、<=、>、>=和<>的直接或者简单限制时，查询优化器才能有选择地扫描分区表。
选择性扫描识别查询中的STABLE以及 IMMUTABLE函数，但是不识别VOLATILE函数。例如， date > CURRENT_DATE这样的WHERE子句导致查询优化器选择性扫描分区表，但是time > TIMEOFDAY不会。

查看用户的分区设计

用户可以使用pg_partitions视图查看有关分区设计的信息。例如，要查看sales表的分区设计：

SELECT partitionboundary, partitiontablename, partitionname, 
partitionlevel, partitionrank 
FROM pg_partitions 
WHERE tablename='sales';

下列表和视图展示了关于分区表的信息

pg_partition – 跟踪分区表以及它们的继承层次关系。
pg_partition_templates – 展示使用一个子分区模板创建的子分区。
pg_partition_columns – 显示在一个分区设计中用到的分区键列。

维护分区表

要维护一个分区表，对顶层父表使用ALTER TABLE命令。最常用的情景是删除旧的分区以及增加新的分区，以此在一种范围分区设计中维护数据的一个滚动窗口。用户可以把旧的分区转换（交换）成追加优化的压缩存储格式来节省空间。如果在用户的分区设计中有一个默认分区，用户可以通过分裂默认分区来增加一个分区。

增加一个分区
重命名一个分区
增加一个默认分区
删除一个分区
截断一个分区
交换一个分区
分裂一个分区
修改一个子分区模板
用一个外部表交换一个叶子子分区

在定义和改变分区设计时，要使用给定的分区名而不是表对象名。尽管用户可以直接使用SQL命令来查询和装载任何表（包括分区表），用户只能使用ALTER TABLE…PARTITION子句修改一个分区表的结构。

分区并不要求有名称。如果一个分区没有名称，可使用下列表达式之一来指定它：PARTITION FOR (value)或者PARTITION FOR(RANK(number))。

增加一个分区用户可以用ALTER TABLE命令为一个分区设计增加一个分区。如果原始分区设计包括由一个子分区模板定义的子分区，新增加的分区也会根据该模板划分子分区。例如：

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

如果在创建表时没有使用一个子分区模板，用户可以在增加分区时定义子分区：

ALTER TABLE sales ADD PARTITION 
         START (date '2017-02-01') INCLUSIVE 
         END (date '2017-03-01') EXCLUSIVE
   ( SUBPARTITION usa VALUES ('usa'), 
     SUBPARTITION asia VALUES ('asia'), 
     SUBPARTITION europe VALUES ('europe') );

当用户为一个现有分区增加一个子分区时，用户可以指定要更改的分区。例如：

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
   ADD PARTITION africa VALUES ('africa');

用户不能向一个具有默认分区的分区设计中增加分区。用户必须分裂默认分区来增加分区。

重命名一个分区分区表使用下列命名习惯。分区子表的名称服从唯一性要求和长度限制。

<parentname>_<level>_prt_<partition_name>

对于自动生成的范围分区，在没有给出名称时会分配一个数字：

sales_1_prt_1

要重命名一个已分区的子表，应重命名顶层父表。在所有相关的子表分区的表名中，<parentname>都会改变。

ALTER TABLE sales RENAME TO globalsales;

用户可以更改一个分区的名称让它更容易标识。例如：

ALTER TABLE sales RENAME PARTITION FOR ('2016-01-01') TO jan16;

在使用ALTER TABLE命令修改分区表时，总是用它们的分区名（jan16）而不是它们的完整表名（sales_1_prt_jan16）引用表。注意：表名不能是一个ALTER TABLE语句中的分区名。例如，ALTER TABLE sales…是正确的。 ALTER TABLE sales_1_part_jan16…则不被允许。

增加一个默认分区用户可以用ALTER TABLE命令为一个分区设计增加一个默认分区。

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;

如果到来的数据不匹配一个分区的CHECK约束并且没有默认分区，该数据就会被拒绝。默认分区确保到来的不匹配一个分区的数据能被插入到默认分区中。

删除一个分区用户可以使用ALTER TABLE命令从用户的分区设计中删除一个分区。当用户删除一个具有子分区的分区时，子分区（以及其中的所有数据）也会被自动删除。对于范围分区，从范围中删除较老的分区很常见，因为旧的数据会被滚出数据仓库。例如：

ALTER TABLE sales DROP PARTITION FOR (RANK(1));

截断一个分区用户可以使用ALTER TABLE命令截断一个分区。当用户截断一个具有子分区的分区时，子分区也会被自动截断。

ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

交换一个分区用户可以使用ALTER TABLE命令交换一个分区。交换一个分区用一个表换掉一个现有的分区。用户只能在分区层次的最底层交换分区（只有包含数据的分区才可以被交换）。分区交换对数据装载有用。例如，装载一个分段表并且把装载好的表换入到用户的分区设计中去。用户可以使用分区交换来把较老分区的存储类型改为追加优化表。例如：

CREATE TABLE jan12 (LIKE sales) WITH (appendonly=true);
INSERT INTO jan12 SELECT * FROM sales_1_prt_1 ;
ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2012-01-01') 
WITH TABLE jan12;

这个例子指的是表sales的单级定义，就是在前面那些例子对它增加或者修改分区之前。警告：如果用户指定WITHOUT VALIDATION子句，用户必须确保用户用于交换现有分区的表中的数据对于该分区上的约束是合法的。否则，针对分区表的查询可能会返回不正确的结果。 Greenplum数据库服务器配置参数 gp_enable_exchange_default_partition控制 EXCHANGE DEFAULT PARTITION子句的可用性。该参数的默认值是 off，表示该子句不可用，如果在ALTER TABLE命令中指定了该子句，Greenplum数据库会返回一个错误。关于该参数的信息，请见 Greenplum数据库参考指南中的“服务器配置参数”。在用户交换默认分区前，用户必须确保要被交换的表中的数据（即新的默认分区）对于默认分区是合法的。例如，新默认分区中的数据不能含有对分区表其他叶子子分区有效的数据。否则，交换过默认分区的分区表上由GPORCA执行的查询可能会返回不正确的结果。

分裂一个分区分裂一个分区会把一个分区划分成两个分区。用户可以使用ALTER TABLE命令分裂分区。用户只能在用户的分区层次的最底层分裂分区：只有包含数据的分区能被分裂。用户指定的分裂值会分在后一个分区中。例如，把一个月度分区分裂成两个，第一个分区包含日期January 1-15而第二个分区包含日期January 16-31：

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

如果用户的分区设计有一个默认分区，用户必须分裂该默认分区来增加分区。在使用INTO子句时，指定当前的默认分区为第二个分区名。例如，要分裂一个默认的范围分区来为January 2017增加一个新的月度分区：

ALTER TABLE sales SPLIT DEFAULT PARTITION 
START ('2017-01-01') INCLUSIVE 
END ('2017-02-01') EXCLUSIVE 
INTO (PARTITION jan17, default partition);

修改一个子分区模板使用ALTER TABLE SET 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 '2014-01-01') INCLUSIVE
 END (date '2014-04-01') EXCLUSIVE
 EVERY (INTERVAL '1 month') );

这个ALTER TABLE命令修改子分区模板。

ALTER TABLE sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), 
SUBPARTITION asia VALUES ('asia'), 
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION africa VALUES ('africa'), 
DEFAULT SUBPARTITION regions );

当用户为表sales增加一个日期范围分区时，它包括非洲的新地区列表子分区。例如，下面的命令创建子分区 usa、asia、europe、 africa以及一个名为other的默认分区：

ALTER TABLE sales ADD PARTITION "4"
START ('2014-04-01') INCLUSIVE 
END ('2014-05-01') EXCLUSIVE ;

要查看为分区表sales创建的表，用户可以从psql命令行使用\dt sales*。要移除一个子分区模板，使用带有空圆括号的SET SUBPARTITION TEMPLATE。例如，要清除sales表的子分区模板：

ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

用一个外部表交换一个叶子子分区用户可以用一个可读的外部表交换一个分区表中的一个叶子子分区。外部表数据可以位于一个主机文件系统、一个NFS挂载或者一个Hadoop文件系统（HDFS）。例如，如果用户有一个分区表，它按月被分成月度分全局并且对该表的大部分查询值访问较新的数据，用户可以把较旧的、较少访问的数据拷贝到外部表并且把较旧的分区与这些外部表交换。对于之访问较新数据的查询，用户可以创建使用分区排除的查询来防止扫描较旧的、不需要的分区。用一个外部表交换一个叶子子分区在这些情况下不被支持：

分区表用SUBPARTITION子句创建或者如果一个分区有一个子分区。
分区表含有一个带检查约束或者NOT NULL约束的列。

关于交换和修改一个叶子子分区的信息，请见 Greenplum数据库命令参考中的ALTER TABLE命令。用一个外部表交换一个分区的例子

这是一个简单的例子，它把这个分区表的一个叶子子分区交换为一个外部表。分区表包含2010至2013年份的数据。

CREATE TABLE sales (id int, year int, qtr int, day int, region text)
  DISTRIBUTED BY (id) 
  PARTITION BY RANGE (year) 
  ( PARTITION yr START (2010) END (2014) EVERY (1) ) ;

该分区表有四个叶子子分区。每一个叶子子分区含有单一年份的数据。叶子子分区表 sales_1_prt_yr_1包含2010年的数据。这些步骤把表sales_1_prt_yr_1交换为一个使用gpfdist协议的外部表：

step 1.确保Greenplum数据库系统启用了该外部表协议。这个例子使用了gpfdist协议。这个命令开始gpfdist协议。

$ gpfdist

step 2.创建一个可写的外部表。This CREATE WRITABLE EXTENAL TABLE命令用和分区表相同的列创建一个可写的外部表。

CREATE WRITABLE EXTERNAL TABLE my_sales_ext ( LIKE sales_1_prt_yr_1 )
  LOCATION ( 'gpfdist://gpdb_test/sales_2010' )
  FORMAT 'csv' 
  DISTRIBUTED BY (id) ;

step 3.创建一个可读的外部表，它从前一步创建的可写外部表的目的地读取数据。这个CREATE EXTENAL TABLE创建一个可读外部表，它使用和可写外部数据相同的外部数据。

CREATE EXTERNAL TABLE sales_2010_ext ( LIKE sales_1_prt_yr_1) 
  LOCATION ( 'gpfdist://gpdb_test/sales_2010' )
  FORMAT 'csv' ;

step 4.从叶子子分区中拷贝数据到该可写外部表。这个INSERT命令从分区表的叶子子分区拷贝数据到外部表。

INSERT INTO my_sales_ext SELECT * FROM sales_1_prt_yr_1 ;

step 5.用该外部表交换现有的叶子子分区。这个ALTER TABLE命令指定了EXCHANGE PARTITION子句来切换可读外部表和叶子子分区。

ALTER TABLE sales ALTER PARTITION yr_1 
   EXCHANGE PARTITION yr_1 
   WITH TABLE sales_2010_ext WITHOUT VALIDATION;

以表名sales_1_prt_yr_1成为叶子子分区的外部表，并且旧的叶子子分区变成表sales_2010_ext。

为了确保针对分区表的查询返回正确的结果，外部表数据必须针对叶子子分区上的CHECK约束有效。在这种情况下，数据会从其上定义有CHECK约束的叶子子分区表中取出。

step 6.删除滚出分区表的表。

DROP TABLE sales_2010_ext ;

用户可以重命名该叶子子分区的名称来表明 sales_1_prt_yr_1是一个外部表。

这个示例命令把partitionname改为 yr_1_ext把叶子子分区表的名称改为 sales_1_prt_yr_1_ext。

ALTER TABLE sales RENAME PARTITION yr_1 TO  yr_1_ext ;

5.6.使用压缩(只支持Append-only表)

不要在压缩文件系统中使用压缩表只能是AO表。两种压缩方式：表级压缩和列级压缩。

压缩可以作用于整表，也可以是特定列，可以对不同的列使用不同的压缩算法。

行或列	可用压缩类型	支持压缩算法
行	表级	ZLIB, ZSTD, and QUICKLZ（开源版本不可用）
列	表级或列级	RLE_TYPE, ZLIB, ZSTD, and QUICKLZ（开源版本不可用））

选择压缩方式和级别的考虑因素：

CPU性能
压缩比
压缩速度
解压速度或查询效率应保证不会显著提高压缩时间和查询效率的前提下最有效的压缩减少数据尺寸。ZLIB压缩率高于QUICKLZ，但速度较低。最好根据相应的比较测试来确定。

压缩算法

压缩算法有很多，较为通用的有 zlib, QuickLZ, LZO, LZ4, Zstandard 。前两者已经原生内嵌在 GPDB 系统中（因版权问题，QuickLZ 在最新的开源版本中已被移除），可直接调用接口使用，但 zlib 的实际使用效果并不理想。而 LZO 和 LZ4，凭借快速压缩解压的特点，在 hive, spark, lucene 等框架中被广泛采用，但压缩率逊与 zlib 。近年，Facebook 在 LZ4 作者 Collet 之前所做的工作基础上发布并开源了 Zstandard（简称 Zstd ），在资源占用和压缩效果方面都优于 zlib 。

GBDB已支持列级别的的运行长度编码(Run-length Encoding/RLE)压缩算法。对于重复的元素很有效。RLE有4中压缩级别。级别越高，压缩效率越高。

表级压缩

创建一张5级ZLIB压缩表

=> CREATE TABLE tb_zlib_01(a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5);

注意：QUICKLZ只有一中压缩级别，而ZLIB有1-9可选。

列级压缩

使用参数格式： [ ENCODING ( storage_directive [,…] ) ]

存储参数可以应用在单独的列上，还可以作为所有列的默认值：

C1 char ENCODING (compresstype=quicklz, blocksize=65536) 

COLUMN C1 ENCODING (compresstype=quicklz, blocksize=65536)

DEFAULT COLUMN ENCODING (compresstype=quicklz)

压缩设置的优先级在越低级别的设置具有越高的优先级：

子分区的列压缩设置将覆盖分区、列和表级的设置
分区的列压缩设置将覆盖列和表级的设置
列的压缩设置将覆盖整个表级的设置注意：存储设置不可以被继承

检查压缩与分布情况

查看某表是否分布不均：

select gp_segment_id,count(*) from fact_tablegroup by gp_segment_id

在segment一级，可以通过的方式检查每张表的数据是否均匀存放

select gp_segment_id,count(*) from fact_table group by gp_segment_id

在 AO 表上，Greenplum 针对压缩率与数据分布情况分别提供了对应的内置函数可以查询

函数	返回类型	描述
get_ao_distribution(name)get_ao_distribution(oid)	集合类型(dbid, tuplecount)	展示AO表的分布情况，每行对应segid和记录数
get_ao_compression_ratio(name)get_ao_compression_ratio(oid)	float8	计算AO表的压缩率。如果该信息未得到，将返回-1。

示例：

=# select * from get_ao_distribution('bar');
 segmentid | tupcount 
-----------+----------
         2 |     3247
         1 |     3385
         0 |     3368
(3 rows)

=# select * from get_ao_compression_ratio('bar');
 get_ao_compression_ratio 
--------------------------
                        1
(1 row)

6.创建和使用序列

用户可以使用序列在为一个表增加记录时自动增加唯一的ID列值。序列常常被用来为加入到表中的行分配唯一的标识号。用户可以把一个标识符列声明为类型SERIAL以隐式地创建一个用于该列的序列。

创建一个序列

CREATE SEQUENCE 命令用给定的序列名称创建并且初始化一个特殊的单行序列生成器表。序列名称必须和同一个方案中任何其他序列、表、索引或者视图的名称不同。例如：

CREATE SEQUENCE myserial START 101;

使用序列

在使用CREATE SEQUENCE创建了一个序列生成器表之后，用户可以使用nextval函数来操作该序列。例如，要向表中插入一个得到序列中下个值的行：

INSERT INTO vendors VALUES (nextval('myserial'), 'acme');

用户还可以用setval函数来重置一个序列的计数器值。例如：

SELECT setval('myserial', 201);

一次nextval操作永不会被回滚。一个被取出的值被认为是已经用掉，即便执行nextval的事务失败也是如此。这意味着失败的事务可能在该序列的已分配值中留下未使用的空洞。 setval操作也永不会被回滚。

注意如果启用了镜像，在UPDATE或DELETE语句中不允许使用nextval函数。在Greenplum数据库中不支持函数 currval和。

为了检查一个序列的当前设置，可查询该序列表：

SELECT * FROM myserial;

修改一个序列

ALTER SEQUENCE命令可更改一个现有序列生成器的参数。例如：

ALTER SEQUENCE myserial RESTART WITH 105;

任何在ALTER SEQUENCE命令中没有设置的参数会保持它们之前的设置。

删除一个序列

DROP SEQUENCE命令移除一个序列生成器表。例如：

DROP SEQUENCE myserial;

默认序列值

一个序列可以被用在CREATE TABLE命令中（取代 SERIAL或者BIGSERIAL类型）：

CREATE TABLE ... (
            id INT4 DEFAULT nextval('id_seq')
            );

或者，可以修改列让序列作为默认值：

ALTER TABLE ... ALTER COLUMN id SET DEFAULT nextval('id_seq');

7.索引

在大部分传统数据库中，索引能够极大地改善数据访问时间。在 Greenplum之类的分布式数据库中，索引应该被更保守地使用。Greenplum数据库会执行非常快的顺序扫描，索引则使用一种随机搜索的模式在磁盘上定位记录。Greenplum的数据分布在Segment上，因此每个Segment会扫描全体数据的一小部分来得到结果。通过表分区，要扫描的数据量可能会更少。因为商业智能（BI）查询负载通常会返回非常大的数据集，使用索引并不是很有效。

首先在不加索引时尝试用户的查询负载。索引更有可能为OLTP负载改进性能，在那种场景中查询会返回一个单一记录或者数据的一个小的子集。在被压缩过的追加优化表上，索引也可以提高返回一个目标行集合的查询的性能，因为优化器在适当的时候可以使用一种索引访问方法而不是全表扫描。对于压缩过的数据，使用一种索引访问方法意味着只有必要的行会被解压。

Greenplum数据库会自动为带有主键的表创建PRIMARY KEY约束。要在一个被分区的表上创建索引，就在用户创建的分区表上创建一个索引。该索引会被传播到Greenplum数据库所创建的所有子表上。不支持在Greenplum数据库为分区表创建的子表上创建索引。

注意一个UNIQUE CONSTRAINT（例如PRIMARY KEY CONSTRAINT）会隐式地创建一个UNIQUE INDEX，它必须包括分布键中所有的列以及任何分区键。UNIQUE CONSTRAINT会在整个表上被强制要求，包括所有的表分区（如果有）。

索引会增加一些数据库负担，它们使用存储空间并且在表被更新时需要被维护。要确保查询负载会用到用户创建的索引，并且检查用户增加的索引是否改进了查询性能（与表的顺序扫描相比）。要确定是否使用了索引，检查查询的EXPLAIN计划。

在创建索引时请考虑以下几点:

用户的查询负载。索引能改进查询返回单一记录或者非常小的数据集的性能，例如OLTP负载。
压缩表。在被压缩过的追加优化表上，索引也可以提高返回一个目标行集合的查询的性能。对于压缩过的数据，一种索引访问方法意味着只有必要的行会被解压。
避免在频繁更新的列上建立索引。在一个被频繁更新的列上建立索引会增加该列被更新时所要求的写操作数据量。
创建选择性的B-树索引。索引选择度是一个列中具有的可区分值的数量除以表中行数得到的比例。例如，如果一个表有1000行并且一个列中有800个可区分的值，则该索引的选择度为0.8，这还不错。唯一索引的选择度总是1.0，这是最好的选择度。Greenplum数据库只允许在分布键列上的唯一索引。
为低选择度的列使用位图索引。Greenplum数据库的位图索引类型在常规的PostgreSQL中不可用。
索引在连接中用到的列。在被用于频繁连接的一个列（例如一个外键列）上的索引能够提升连接性能，因为这让查询优化器有更多的连接方法可以使用。
索引在谓词中频繁使用的列。频繁地在WHERE子句中被引用的列是索引的首选。
避免重叠的索引。具有相同前导列的索引是冗余的。
批量载入前删掉索引。对于载入大量数据到一个表中，请考虑先删掉索引并且在数据装载完成后重建它们。这常常比更新索引更快。
考虑一个聚簇索引。聚簇一个索引意味着记录会根据索引被物理排序后存储在磁盘上。如果用户需要的数据被随机分布在磁盘上，数据库必须在磁盘上来回寻找以取得所需的记录。如果这些记录被存储得彼此临近，那么取得它们的操作就会更高效。例如，一个在日期列上的聚簇索引中数据会按照日期顺序存放。针对一个指定日期范围的查询将会导致对磁盘的一次有序地读取，这会利用快速的顺序访问。

在Greenplum数据库中聚簇一个索引

使用CLUSTER命令根据一个索引从物理上重新排序一个非常大的表可能会花费很长的时间。为了更快达到同样的结果，用户可以通过创建一个中间表并且按照想要的顺序重载数据来手工在磁盘上重排数据。例如：

CREATE TABLE new_table (LIKE old_table) 
       AS SELECT * FROM old_table ORDER BY myixcolumn;
DROP old_table;
ALTER TABLE new_table RENAME TO old_table;
CREATE INDEX myixcolumn_ix ON old_table;
VACUUM ANALYZE old_table;

索引类型

Greenplum数据库数据库支持Postgres索引类型B-树和GiST，不支持Hash和GIN索引。每一种索引类型都使用一种不同的算法，它们最适合的查询类型也不同。B-树索引适合于最常见的情况并且是默认的索引类型。

只有索引键的列与Greenplum分布键相同（或者是其超集）时，Greenplum数据库才允许唯一索引。在追加优化表上不支持唯一索引。在分区表上，唯一索引无法在一个分区表的所有子表分区之间被实施。唯一索引只能在一个分区内实施。

关于位图索引

Greenplum数据库提供位图索引类型。位图索引最适合于拥有大量数据、很多临时查询以及少量数据修改（DML）事务的数据仓库应用和决策支持系统。

一个索引提供了指向表中包含一个给定键值的行的指针。常规索引存储了每个键存储了一个元组ID的列表，列表中的元组ID对应于具有那个键值的行。位图索引为每一个键值都存储一个位图。常规索引可能会比表中的数据大几倍，但位图索引提供了和常规索引相同的功能并且只需要被索引数据尺寸的一小部分。

位图中的每一个位对应于一个可能的元组ID。如果该位被设置，则具有相应元组ID的行包含该键值。一个映射函数负责将这个位的位置转换成一个元组ID。位图被压缩存储。如果可区分键值的数量很小，位图索引会小很多同时也会被压缩得更好，并且比常规索引节省可观的空间。一个位图索引的尺寸与该表中行数乘以被索引列中可区分值数量的结果成比例。

位图索引对于在WHERE子句中包含多个条件的查询最有效。满足某些但不是全部条件的行在访问表之前就会被过滤掉。这通常会极大地改善响应时间。

何时使用位图索引

位图索引最适合用户只查询数据而不更新数据的数据仓库应用。对于拥有100至100,000个可区分值的列并且当被索引列常常与其他被索引列联合查询时，位图索引表现最好。低于100个可区分值的列通常无法从任何类型的索引受益，例如有两个可区分值的性别列（男和女）。而在具有超过100,000个可区分值的列上，位图索引的性能和空间效率会衰退。

位图索引能够提升ad hoc查询的查询性能。在将结果位图转换成元组ID之前，一个查询的WHERE子句中的AND以及OR条件可以通过在位图上直接执行相应的布尔操作快速地解决。如果结果行数很小，查询能够在不做全表扫描的情况下很快地被回答。

何时不用位图索引

不要为唯一列或者具有高基数数据的列使用位图索引，例如顾客姓名或者电话号码。位图索引的性能增益和磁盘空间优势在具有100,000或者更多唯一值的列上开始减小，这与表中的行数无关。

位图索引不适合有大量并发事务修改数据的OLTP应用。

请保守地使用位图索引。测试并且比较使用索引和不使用索引的查询性能。只有被索引列的查询性能有提升时才增加索引。

创建一个索引

CREATE INDEX命令在一个表上定义一个索引。例如，要在表employee的gender列上创建一个B-树索引：

CREATE INDEX gender_idx ON employee (gender);

要在表films中的列title上创建一个位图索引：

CREATE INDEX title_bmp_idx ON films USING bitmap (title);

检查索引使用

Greenplum数据库的索引并不要求维护和调优。用户可以检查实际的查询负载使用了哪些索引。使用EXPLAIN命令可以检查一个查询的索引使用。

查询计划展示了数据库将用来回答一个查询的步骤或者计划节点以及每一个计划节点的时间估计。要检查索引的使用，请在用户的EXPLAIN输出中寻找以下查询计划节点类型：

索引扫描 – 一次索引的扫描。
位图堆扫描 – 检索所有由BitmapAnd、BitmapOr或者BitmapIndexScan生成的位图并且访问堆以检索相关的行。
位图索引扫描 – 计算一个由所有来自底层索引的满足查询谓词的位图通过OR操作形成的位图。
BitmapAnd或者BitmapOr – 取得从多个BitmapIndexScan节点生成的位图，把它们AND或者OR在一起，并且生成一个新的位图作为其输出。

用户必须做实验来确定要创建哪些索引。请考虑以下几点

在创建或者更新一个索引后运行ANALYZE。 ANALYZE会收集表统计信息。查询优化器使用表统计信息来估算一个查询所返回的行数并且为每一种可能的查询计划赋予实际开销。
实验中使用真实数据。使用测试数据建立索引会告诉用户该测试数据需要什么样的索引，但也仅此而已。
不要使用非常小的测试数据集，因为它们的结果很可能是不真实的或者倾斜的。
在开发测试数据时要小心。相似的、完全随机的或者排序后插入的值都将使统计信息偏离真实数据的分布。
通过使用运行时参数来关闭特定的计划类型，用户可以强制使用索引来进行测试。例如，关闭顺序扫描（enable_seqscan）以及嵌套循环连接（enable_nestloop）两种最基本的计划来强制系统使用一种不同的计划。对用户的查询使用索引和不用索引的执行进行计时，并且使用EXPLAIN ANALYZE命令来比较结果。

管理索引

使用REINDEX命令可以重建一个表现不好的索引。 REINDEX使用存储在一个索引的基表中的数据重建该索引来替换该索引。

要重建一个表上的所有索引

REINDEX my_table;

REINDEX my_index;

删除一个索引 DROP INDEX命令移除一个索引。例如：

DROP INDEX title_idx;

在载入数据时，删除所有索引、载入数据然后重建索引会更快。

8.创建和管理视图

视图允许用户保存常用的或者复杂的查询，然后在一个 SELECT语句中把它们当作表来访问。视图在磁盘上并没有被物理存储：当用户访问视图时查询会作为一个子查询运行。

如果一个子查询与一个单一查询相关联，考虑使用SELECT命令的WITH子句而不是创建一个很少使用的视图。

创建视图

CREATE VIEW 命令定一个查询的视图。例如：

CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'comedy';

视图会忽略存储在视图中的ORDER BY以及SORT操作。

删除视图

DROP VIEW命令删除一个视图。例如：

DROP VIEW topten;

DROP VIEW … CASCADE命令也可以移除所有依赖的对象。例如，如果另一个视图依赖于将要被删除的视图，这个其他的视图也将被删除。如果没有CASCADE选项，这个 DROP VIEW命令将会失败。

本文参与?腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-10-29，如有侵权请联系?cloudcommunity@tencent.com 删除

数据库

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与?腾讯云自媒体分享计划? ，欢迎热爱写作的你一起参与！