使用INSERT批量插入 带SELECT子句使用批量插入操作来实现高性能数据插入。 如果需要将数据或数据子集从一个表移动到另一个表，可以使用INSERT和CREATE TABLE AS命令。 如果从指定表插入数据到当前表，例如在数据库中创建了一个表customer_t1的备份表customer_t2，现在需要将表customer_t1中的数据插入到表customer_t2中，则可以执行如下命令。 1 2 3 4 5 6 7 8 openGauss=# CREATE TABLE customer_t2 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); openGauss=# INSERT INTO customer_t2 SELECT * FROM customer_t1; 上面的示例等价于： 1 openGauss=# CREATE TABLE customer_t2 AS SELECT * FROM customer_t1;

使用INSERT多行插入 插入如果不能使用copy命令，而您需要进行sql插入，可以根据情况使用多行插入。如果您使用的是列存表，一次只插入一行或几行，则数据压缩效率低下。 多行插入是通过批量进行一系列插入而提高性能。下面的示例使用一条insert语句向一个三列表插入三行。这仍属于少量插入，只是用来说明多行插入的语法。创建表的步骤请参考3.6-创建和管理表。 向表customer_t1中插入多行数据： openGauss=# insert into customer_t1 values (68, 'a1', 'zhou','wang'), (43, 'b1', 'wu', 'zhao'), (95, 'c1', 'zheng', 'qian'); 有关更多详情和示例，请参阅INSERT。

使用gsql元命令导入数据 \copy命令在任何psql客户端登录数据库成功后可以执行导入数据。与COPY命令相比较，\copy命令不是读取或写入指定文件的服务器，而是直接读取或写入文件。 这个操作不如COPY命令有效，因为所有的数据必须通过客户端/服务器的连接来传递。对于大量的数据来说COPY命令可能会更好。 有关如何使用\copy命令的更多信息，请参阅使用gsql元命令导入数据。 \COPY只适合小批量，格式良好的数据导入，不会对非法字符做预处理，也无容错能力，无法适用于含有异常数据的场景。导入数据应优先选择COPY。

选择存储模型 进行数据库设计时，表设计上的一些关键项将严重影响后续整库的查询性能。表设计对数据存储也有影响：好的表设计能够减少I/O操作及最小化内存使用，进而提升查询性能。 表的存储模型选择是表定义的第一步。客户业务属性是表的存储模型的决定性因素，依据下面表格选择适合当前业务的存储模型。 存储模型 适用场景 行存 点查询（返回记录少，基于索引的简单查询）。 增删改比较多的场景。 父主题： 表设计最佳实践

场景一：磁盘满后快速定位存储倾斜的表 首先，通过pg_stat_get_last_data_changed_time(oid)函数查询出近期发生过数据变更的表，鉴于表的最后修改时间只在进行IUD操作的CN记录，要查询库内1天(间隔可在函数中调整)内被修改的所有表，可以使用如下封装函数： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE OR REPLACE FUNCTION get_last_changed_table(OUT schemaname text, OUT relname text) RETURNS setof record AS $$ DECLARE row_data record; row_name record; query_str text; query_str_nodes text; BEGIN query_str_nodes := 'SELECT node_name FROM pgxc_node where node_type = ''C'''; FOR row_name IN EXECUTE(query_str_nodes) LOOP query_str := 'EXECUTE DIRECT ON (' || row_name.node_name || ') ''SELECT b.nspname,a.relname FROM pg_class a INNER JOIN pg_namespace b on a.relnamespace = b.oid where pg_stat_get_last_data_changed_time(a.oid) BETWEEN current_timestamp - 1 AND current_timestamp;'''; FOR row_data IN EXECUTE(query_str) LOOP schemaname = row_data.nspname; relname = row_data.relname; return next; END LOOP; END LOOP; return; END; $$ LANGUAGE 'plpgsql'; 然后，通过table_distribution(schemaname text, tablename text)查询出表在各个DN占用的存储空间。 1 SELECT table_distribution(schemaname,relname) FROM get_last_changed_table();

场景二：常规数据倾斜巡检 在库中表个数少于1W的场景，直接使用倾斜视图查询当前库内所有表的数据倾斜情况。 1 SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC; 在库中表个数非常多（至少大于1W）的场景，因PGXC_GET_TABLE_SKEWNESS涉及全库查并计算非常全面的倾斜字段，所以可能会花费比较长的时间（小时级），建议参考PGXC_GET_TABLE_SKEWNESS视图定义，直接使用table_distribution()函数自定义输出，减少输出列进行计算优化，例如： 1 2 3 4 5 6 SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' GROUP BY schemaname,tablename;

选择分布列 Hash分布表的分布列选取至关重要，需要满足以下原则： 列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。 在满足上述条件的情况下，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。 对于Hash分表策略，如果分布列选择不当，可能导致数据倾斜，查询时出现部分DN的I/O短板，从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性。 1 2 3 4 5 select xc_node_id, count(1) from tablename group by xc_node_id order by xc_node_id desc; 示例如下： CREATE TABLE t1(c1 int) distribute by hash(c1); INSERT INTO t1 values(generate_series(1,100)); select xc_node_id, count(1) from t1 group by xc_node_id order by xc_node_id desc; DROP TABLE t1; 其中xc_node_id对应DN，一般来说，不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列。 GaussDB 支持多分布列特性，可以更好地满足数据分布的均匀性要求。 Range/List分布表的分布列由用户根据实际需要进行选择。除了需选择合适的分布列，还需要注意分布规则对数据分布的影响。 父主题： 表设计最佳实践

选择分布方式 复制表（Replication）方式将表中的全量数据在集群的每一个DN实例上保留一份。主要适用于记录集较小的表。这种存储方式的优点是每个DN上都有该表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment(每个plan segment都会起对应的线程)；缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。 哈希（Hash）表将表中某一个或几个字段进行hash运算后，生成对应的hash值，根据DN实例与哈希值的映射关系获得该元组的目标存储位置。对于Hash分布表，在读/写数据时可以利用各个节点的I/O资源，大大提升表的读/写速度。一般情况下大表定义为Hash表。 范围（Range）和列表（List）分布是由用户自定义的分布策略，根据分布列的取值落入满足一定范围或者具体值的对应目标DN，这两种分布方式便于用户灵活地进行数据管理，但对用户本身的数据抽象能力有一定的要求。 策略 描述 适用场景 Hash 表数据通过hash方式散列到集群中的所有DN实例上。 数据量较大的事实表。 Replication 集群中每一个DN实例上都有一份全量表数据。 小表、维度表。 Range 表数据对指定列按照范围进行映射，分布到对应DN。 用户需要自定义分布规则的场景。 List 表数据对指定列按照具体值进行映射，分布到对应DN。 用户需要自定义分布规则的场景。 如图1所示，复制表如图中的表T1，哈希表如图中的表T2。 图1 复制表和哈希表 父主题： 表设计最佳实践

使用分区表 分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点： 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。 GaussDB支持的分区表为范围分区表。 范围分区表：将数据基于范围映射到每一个分区。这个范围是由创建分区表时指定的分区键决定的。分区键经常采用日期，例如将销售数据按照月份进行分区。 父主题： 表设计最佳实践

使用分区表 分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点： 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。 GaussDB数据库 支持的分区表为一级分区表和二级分区表，其中一级分区表包括范围分区表、间隔分区表、列表分区表、哈希分区表四种，二级分区表包括范围分区、列表分区、哈希分区两两组合的九种。 范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。 间隔分区表：是一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。 列表分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。 哈希分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。 二级分区表：由范围分区、列表分区、哈希分区任意组合得到的分区表，其一级分区和二级分区均可以使用前面三种定义方式。 父主题： 表设计最佳实践

背景 一个数据库可能有很多的用户需要访问，为了方便管理这些用户，将用户组成一个数据库角色。一个数据库角色可以视为一个数据库用户或者一组数据库用户。 对于数据库来说，用户和角色是基本相同的概念，不同之处在于，使用CREATE ROLE创建角色，不会创建同名的SCHEMA，并且默认没有 LOG IN权限；而使用CREATE USER创建用户，会自动创建同名的SCHEMA，默认有LOGIN权限。换句话说，一个拥有LOGIN权限的角色可以被认为是一个用户。在业务设计中，仅建议通过ROLE来组织权限，而不是用来访问数据库。

解决方案 数据库用户 数据库用户的主要用途是使用该用户账号连接数据库、访问数据库对象和执行SQL语句。在连接数据库时，必须使用一个已经存在的数据库用户。因此，作为数据库管理员，需要为每一个需要连接数据库的使用者规划一个数据库用户。 在创建数据库用户时，至少需要指定用户名和密码。 默认情况下，数据库用户可以分为两大类，详细信息请参见表1。 表1 用户分类 分类 描述 初始用户 具有数据库的最高权限，并且具有所有的系统权限和对象权限。初始用户不受对象的权限设置影响。这个特点类似UNIX系统的root的权限。从安全角度考虑，除了必要的情况，建议尽量避免以初始用户身份操作。 在安装数据库或者初始化数据库时，可以指定初始用户名和密码。如果不指定用户名则会自动生成一个与安装数据库的OS用户同名的初始用户。如果不指定密码则安装后初始用户密码为空，需要通过gsql客户端设置初始用户的密码后才能执行其他操作。 说明： 基于安全性考虑，GaussDB Kernel禁止了所有用户trust方式的远程登录方式，禁止了初始用户的任何方式的远程登录。 普通用户 默认可以访问数据库的默认系统表和视图（pg_authid、pg_largeobject、pg_user_status和pg_auth_history除外），可以连接默认的数据库postgres以及使用public模式下的对象（包括表、视图和函数等）。 可以通过CREATE USER、ALTER USER指定系统权限，或者通过GRANT ALL PRIVILEGE授予SYSADMIN权限。 可以通过GRANT语句授予某些对象的权限。 可以通过GRANT语法将其他角色或用户的权限授权给该用户。 数据库权限分类 通过权限和角色，可以控制用户访问指定的数据，以及执行指定类型的SQL语句。详细信息请参见表2。 系统权限只能通过CREATE/ALTER USER、CREATE/ALTER ROLE语句指定（其中SYSADMIN还可以通过GRANT/REVOKE ALL PRIVILEGES的方式赋予、回收），无法从角色继承。 表2 权限分类 分类 描述 系统权限 系统权限又称为用户属性，可以在创建用户和修改用户时指定，包括SYSADMIN、MONADMIN、OPRADMIN、POLADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。 系统权限一般通过CREATE/ALTER USER语句指定。除了SYSADMIN外的其他系统权限，无法通过GRANT/REVOKE进行授予和撤销。并且，系统权限无法通过ROLE被继承。 对象权限 对象权限是指在表、视图、索引和函数等数据库对象上执行各种操作的权限，对象权限包括SELECT、INSERT、UPDATE和DELETE等。 只有对象的所有者或者系统管理员才可以执行GRANT/REVOKE语句来分配/撤销对象权限。 角色 角色是一组权限的集合，可以将一个角色的权限赋予其他角色和用户。 由于无法给其他角色和用户赋予系统权限，所以角色只有是对象权限的集合时才有意义。 数据库权限模型 系统权限模型 默认权限机制 图1 权限架构 权限架构如图1，默认权限机制下sysadmin具有大多数的权限。 初始安装用户：集群安装过程中自动生成的账户，拥有系统的最高权限，能够执行所有的操作。 SYSADMIN：系统管理员权限，权限仅次于初始安装用户，默认具有与对象所有者相同的权限，但不包括监控管理员权限和运维管理员权限。 MONADMIN：监控管理员权限，具有监控模式dbe_perf及模式下视图和函数的访问权限和授予权限。 OPRADMIN：运维管理员权限，具有使用Roach工具执行备份恢复的权限。 CREATEROLE：安全管理员权限，具有创建、修改、删除用户/角色的权限。 AUDITADMIN：审计管理员权限，具有查看和维护数据库审计日志的权限。 CREATEDB：具有创建数据库的权限。 POLADMIN：安全策略管理员权限，具有创建资源标签，数据动态脱敏策略和统一审计策略的权限。 三权分立机制 图2 三权分立机制 SYSADMIN：系统管理员权限，不再具有创建、修改、删除用户/角色的权限，也不再具有查看和维护数据库审计日志的权限。 CREATEROLE：安全管理员权限，具有创建、修改、删除用户/角色的权限。 AUDITADMIN：审计管理员权限，具有查看和维护数据库审计日志的权限。 一个用户/角色只能具有SYSADMIN、CREATEROLE和AUDITADMIN中的一项系统权限。 对象权限模型 对象权限：指在数据库、模式、表等数据库对象上执行特定动作的权限，比如：SELECT、INSERT、UPDATE、DELETE、CONNECT等。 针对不同的数据库对象有不同的对象权限，相应地可以被授予用户/角色。 通过GRANT/REVOKE来传递对象权限，对象权限可以通过角色被继承。 角色权限模型 GaussDB Kernel提供了一组默认角色，以gs_role_开头命名。它们提供对特定的、通常需要高权限操作的访问，可以将这些角色GRANT给数据库内的其他用户或角色，让这些用户能够使用特定的功能。在授予这些角色时应当非常小心，以确保它们被用在需要的地方。表3描述了内置角色允许的权限范围。 表3 内置角色权限 角色 权限描述 gs_role_copy_files 具有执行copy … to/from filename的权限，但需要先打开GUC参数enable_copy_server_files。 gs_role_signal_backend 具有调用函数pg_cancel_backend()、pg_terminate_backend()和pg_terminate_session()来取消或终止其他会话的权限，但不能操作属于初始用户和PERSISTENCE用户的会话。 gs_role_tablespace 具有创建表空间（tablespace）的权限。 gs_role_replication 具有调用逻辑复制相关函数的权限，例如kill_snapshot()、pg_create_logical_replication_slot()、pg_create_physical_replication_slot()、pg_drop_replication_slot()、pg_replication_slot_advance()、pg_create_physical_replication_slot_extern()、pg_logical_slot_get_changes()、pg_logical_slot_peek_changes()，pg_logical_slot_get_binary_changes()、pg_logical_slot_peek_binary_changes()。 gs_role_account_lock 具有加解锁用户的权限，但不能加解锁初始用户和PERSISTENCE用户。 gs_role_pldebugger 具有执行dbe_pldebugger下调试函数的权限。 gs_role_directory_create 具有执行创建directory对象的权限，但需要先打开GUC参数enable_access_server_directory。 gs_role_directory_drop 具有执行删除directory对象的权限，但需要先打开GUC参数enable_access_server_directory。 系统权限配置 默认权限机制配置方法 初始用户 数据库安装过程中自动生成的账户称为初始用户。初始用户也是系统管理员、监控管理员、运维管理员和安全策略管理员，拥有系统的最高权限，能够执行所有的操作。如果安装时不指定初始用户名称则该账户与进行数据库安装的操作系统用户同名。如果在安装时不指定初始用户的密码，安装完成后密码为空，在执行其他操作前需要通过gsql客户端修改初始用户的密码。如果初始用户密码为空，则除修改密码外无法执行其他SQL操作以及升级、扩容、节点替换等操作。 初始用户会绕过所有权限检查。建议仅将此初始用户作为DBA管理用途，而非业务用途。 系统管理员 gaussdb=#CREATE USER u_sysadmin WITH SYSADMIN password '********'; --或者使用如下SQL，效果一样，需要该用户已存在。 gaussdb=#ALTER USER u_sysadmin01 SYSADMIN; 监控管理员 gaussdb=#CREATE USER u_monadmin WITH MONADMIN password '********'; --或者使用如下SQL，效果一样，需要该用户已存在。 gaussdb=#ALTER USER u_monadmin01 MONADMIN; 运维管理员 gaussdb=#CREATE USER u_opradmin WITH OPRADMIN password "xxxxxxxxx"; --或者使用如下SQL，效果一样，需要该用户已存在。 gaussdb=#ALTER USER u_opradmin01 OPRADMIN; 安全策略管理员 gaussdb=#CREATE USER u_poladmin WITH POLADMIN password "xxxxxxxxx"; --或者使用如下SQL，效果一样，需要该用户已存在。 gaussdb=#ALTER USER u_poladmin01 POLADMIN; 三权分立机制配置方式 此模式需要设置guc参数“enableSeparationOfDut”y的值为“on”，该参数为POSTMASTER类型参数，修该完之后需要重启数据库。 gs_guc set -Z datanode -N all -I all -c "enableSeparationOfDuty=on" gs_om -t stop gs_om -t start 创建和配置相应的用户权限的语法和默认权限一致。 角色权限配置 --创建数据库test gaussdb=#CREATE DATABASE test; --创建角色role1，创建用户user1 gaussdb=#CREATE ROLE role1 PASSWORD '********'; gaussdb=#CREATE USER user1 PASSWORD '********'; --赋予CREATE ANY TABLE权限角色role1 gaussdb=#GRANT CREATE ON DATABASE test TO role1; --将角色role1赋予给用户user1,则用户user1属于组role1,继承role1的相应权限可以在test数据库中创建模式。 gaussdb=#GRANT role1 TO user1; --查询用户和角色信息 gaussdb=#\du role1|user1; List of roles Role name | Attributes | Member of -----------+--------------+----------- role1 | Cannot login | {} user1 | | {role1}

查看表所在节点 用户在建表时可以指定表如何在节点之间分布或者复制，详情请参见•DISTRIBUTEBY，分布方式介绍请参见选择分布方式。 用户在建表时也可设置“Node Group”来指定表所在的Group，详情请参见•TO{GROUPgroupname|...。 用户还可以通过以下命令查看表所在实例。 查询表所在的schema。 select t1.nspname,t2.relname from pg_namespace t1,pg_class t2 where t1.oid = t2.relnamespace and t2.relname = 'table1'; 上述命令中，“nspname”为schema的名称，“relname”为表、索引、视图等对象的名称，“oid”为行标识符，“relnamespace”为包含这个关系的名称空间的OID，“table1”为表名称。 查看表的relname和nodeoids。 select t1.relname,t2.nodeoids from pg_class t1, pgxc_class t2, pg_namespace t3 where t1.relfilenode = t2.pcrelid and t1.relnamespace=t3.oid and t1.relname = 'table1' and t3.nspname ='schema1'; 上述命令中，“nodeoids”为表分布的节点OID列表，“relfilenode”为这个关系在磁盘上的文件的名称，“pcrelid”为表的OID，“schema1”为1中查询出的该表所在schema。 根据查询到的表分布的节点，查询表所在实例。 select * from pgxc_node where oid in (nodeoids1, nodeoids2, nodeoids3); 上述命令中的“nodeoids1, nodeoids2, nodeoids3”为2中查询到的3个nodeoids，操作时以实际查询到的为准，各nodeoids间以“,”隔开。

表压缩级别 在创建表时，可以自定义字段的压缩级别及压缩水平。压缩不仅影响到数据加载，也影响到数据查询。表压缩级别由参数COMPRESSION控制。 参数说明： COMPRESSION指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。 取值范围： 行存表的有效值为YES/NO，默认值为NO。 客户可根据不同场景依据表2选择不同压缩级别。 表2 压缩级别适用场景说明 压缩级别 适用场景 存储方式 YES 启用表压缩：行存表压缩率较低，不建议启用。 行存 NO 禁用表压缩。 行存

选择分布列 Hash分布表的分布列选取至关重要，需要满足以下原则： 列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。 在满足上述条件的情况下，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。 对于Hash分表策略，如果分布列选择不当，可能导致数据倾斜，查询时出现部分DN的I/O短板，从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性。 1 2 3 4 5 select xc_node_id, count(1) from tablename group by xc_node_id order by xc_node_id desc; 示例如下： CREATE TABLE t1(c1 int) distribute by hash(c1); INSERT INTO t1 values(generate_series(1,100)); select xc_node_id, count(1) from t1 group by xc_node_id order by xc_node_id desc; DROP TABLE t1; 其中xc_node_id对应DN，一般来说，不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列。 GaussDB支持多分布列特性，可以更好地满足数据分布的均匀性要求。 Range/List分布表的分布列由用户根据实际需要进行选择。除了需选择合适的分布列，还需要注意分布规则对数据分布的影响。