高斯数据库
0、安装opengauss
1、https://opengauss.org/zh/download/下载安装包
2、上传服务器
3、将安装包加载为docker镜像
docker load -i /usr/soft/openGauss-Docker-6.0.2-x86_64.tar
通过docker image进行验证
4、启动opengauss
docker run --name opengauss --privileged=true -d -p 5432:5432 -e GS_PASSWORD=Enmo@123 -v /opengauss:/var/lib/opengauss opengauss:6.0.2
--name opengauss：为容器起名字opengauss-d：让容器以后台进程运行-p 5432:5432: 设置端口映射。-e：配置容器内进程运行时的一些参数-v：指定挂载目录
5、dbeaver连接opengauss
首先创建一个连接模板，指定驱动包
类名：org.postgresql.Driver
URL模板：jdbc:postgresql://{host}:{port}/{database}
端口：5432
安装好opengauss后，会默认创建数据库postgres，用户名是gaussdb，密码是我们启动docker时指定的
1、数据库系统概述
1.1 数据库逻辑结构图
- Tablespace，即表空间，表空间是一个目录，实例中可以存在多个表空间，其中存储的是它 所包含的数据库的各种物理文件。每个表空间可以对应多个Database。
- Database，即数据库，用于管理各类数据对象，各数据库间相互隔离。数据库管理的对象可 分布在多个Tablespace上。
- Datafile Segment，即数据文件，通常每张表只对应一个数据文件。如果某张表的数据大于 1GB，则会分为多个数据文件存储。
- Table，即表，每张表只能属于一个数据库，也只能对应到一个Tablespace。每张表对应的数 据文件必须在同一个Tablespace中。
- Block，即数据块，是数据库管理的基本单位，默认大小为8KB。
1.2 管理事务
GaussDB数据库支持的事务隔离级别有READ COMMITTED、REPEATABLE READ和 SERIALIZABLE，SERIALIZABLE等价于REPEATABLE READ。
GaussDB默认的隔离级别：READ COMMITTED。查看语句如下：
show transaction isolation level;
在事务管理上，GaussDB采取了MVCC（多版本并发控制）结合两阶段锁的方式，其特点是读写之间不阻塞。
2、数据库安全
三权分立。三权分立后，系统管理员将不再具有CREATEROLE属性（安全管理员）和 AUDITADMIN属性（审计管理员），即不再拥有创建角色和用户的权限，也不再拥有 查看和维护数据库审计日志的权限。
2.1 管理员
系统管理员
系统管理员是指具有SYSADMIN属性的账户，默认安装情况下具有与对象所有者相同的权限，但不包括dbe_perf模式的对象权限。
要创建新的系统管理员，请以初始用户或者系统管理员身份连接数据库，并使用带 SYSADMIN选项的CREATE USER语句或ALTER USER语句进行设置。
CREATE USER sysadmin WITH SYSADMIN password "********";
或者
ALTER USER joe SYSADMIN;
安全管理员
安全管理员是指具有CREATEROLE属性的账户，具有创建、修改、删除用户或角色的 权限，和授予或者撤销任何非系统管理员、内置角色、永久用户、运维管理员的权限。
创建安全管理员：
CREATE USER createrole WITH CREATEROLE password "********";
或者：
ALTER USER joe CREATEROLE;
审计管理员
数据库审计功能对数据库系统的安全性至关重要。数据库审计管理员可以利用审计日 志信息，重现导致数据库现状的一系列事件，找出非法操作的用户、时间和内容等。
审计管理员是指具有AUDITADMIN属性的账户，具有查看和删除审计日志的权限。
创建审计管理员
CREATE USER auditadmin WITH AUDITADMIN password "********";
或者
ALTER USER joe AUDITADMIN;
2.2 用户
数据库系统包含一个或 多个数据库，用户和角色在整个数据库系统范围内是共享的，但是其数据并不共享。 即用户可以连接任何数据库，但当连接成功后，任何用户都只能访问连接请求里声明的数据库。
创建用户
#创建用户joe，并设置用户拥有CREATEDB属性。
CREATE USER joe WITH CREATEDB PASSWORD "********";
2.3 实操
1、通过docker切换opengauss容器
docker exec -it opengauss bash
2、切换omm用户
su - omm
3、输入gsql命令，使用omm用户创建数据库、用户和schema
create database testdb;
create user testuser with IDENTIFIED BY '******';
create schema testschema;
4、将schema赋给用户
GRANT ALL PRIVILEGES ON SCHEMA testschema TO testuser;
3、数据库使用入门
3.1 gsql
在集中式数据库实例环境中，当需要连接主节点时，假设数据库实例三个节点的IP分 别是10.10.0.11、10.10.0.12和10.10.0.13，可以执行以下命令：
gsql -d postgres -h 10.10.0.11,10.10.0.12,10.10.0.13 -U jack -p 8000
gsql会按从前往后的顺序依次连接三个IP，如果当前连接的IP地址不是主节点则断开尝试连接下一个IP地址，直到找到主节点为止。
3.2 管理数据库
- 查看数据库。登录数据库命令行，执行如下命令：
\l
或者执行如下命令：
SELECT datname FROM pg_database;
3.3 表空间
通过使用表空间，管理员可以控制一个数据库安装的磁盘布局。
GaussDB自带了两个表空间：pg_default和pg_global。
- 默认表空间pg_default：用来存储非共享系统表、用户表、用户表index、临时 表、临时表index、内部临时表的默认表空间。对应存储目录为实例数据目录下的 base目录。
- 共享表空间pg_global：用来存放共享系统表的表空间。对应存储目录为实例数据 目录下的global目录。
3.3.1 创建表空间
CREATE TABLESPACE fastspace RELATIVE LOCATION 'tablespace/tablespace_1';
数据库系统管理员执行如下命令将“fastspace”表空间的访问权限授予数据用户 jack。
GRANT CREATE ON TABLESPACE fastspace TO jack
3.3.2 使用表空间
如果用户拥有表空间的CREATE权限，就可以在表空间上创建数据库对象，比如：表和索引等。
在表空间中创建对象有以下两种方式，以创建表为例。
1、执行如下命令在指定表空间创建表
CREATE TABLE foo(i int) TABLESPACE fastspace;
2、先使用SET default_tablespace设置默认表空间，再创建表。
SET default_tablespace = 'fastspace';
CREATE TABLE foo2(i int);
3.3.3 查询表空间及使用率
#查看表空间1
SELECT spcname FROM pg_tablespace;
#查看表空间2
\db
#查询表空间使用情况，单位为字节
SELECT pg_tablespace_size('pg_default');
表空间使用率=pg_tablespace_size/表空间所在目录的磁盘大小。
3.3.4 修改表空间命名
ALTER TABLESPACE fastspace RENAME TO fspace
3.3.5 删除表空间
DROP TABLESPACE fspace;
3.4 查看和停止正在运行的查询语句
通过视图PG_STAT_ACTIVITY可以查看正在运行的查询语句。方法如下：
1、设置参数track_activities为on。
SET track_activities = on;
当此参数为on时，数据库系统才会获取当前活动查询的运行信息。
2、查看正在运行的查询语句。以查看正在运行的查询语句所连接的数据库名、执行查询的用户、查询状态及查询对应的PID为例。
SELECT datname, usename, state, pid FROM pg_stat_activity;
如果state字段显示为idle，则表明此连接处于空闲，等待用户输入命令。
3、若需要取消运行时间过长的查询，通过pg_terminate_backend(pid int)函数，根据线程ID结束会话，请执行如下命令。
SELECT PG_TERMINATE_BACKEND(pid);
3.5 分区表
GaussDB数据库支持的分区表为范围分区表、间隔分区表、列表分区表和哈希分区表。
- 范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式被广泛应用，并且分区键经常采用日期，例如将销售数据按照月份进行分区。
- 间隔分区表：是一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。
- 列表分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。
- 哈希分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
分区表和普通表相比具有以下优点：
-
改善查询性能：对分区对象的查询可以仅搜索与查询条件匹配的分区数据，提高检索效率。
-
增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
-
方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
示例
以创建RANGE分区为例
1、创建分区表
CREATE TABLE tpcds.customer_address
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)
PARTITION BY RANGE (ca_address_sk)
(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;
2、删除分区
ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
3、增加分区
ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
4、重命名分区
ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
5、查询分区
SELECT * FROM tpcds.web_returns_p2 PARTITION (P6);
3.6 索引
可以通过以下条件判断是否创建索引，选择创建索引的列。
- 在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
- 在作为主键的列上创建索引，强制要求主键唯一、强制要求主键有序排列。
- 在经常使用连接的列上创建索引，可以加快连接的速度。
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 在经常使用WHERE子句的列上创建索引，加快条件的判断速度。
- 为经常出现在关键字ORDER BY、GROUP BY和DISTINCT后面的字段建立索引。
分区表索引分为LOCAL索引与GLOBAL索引，一个LOCAL索引对应一个具体分区，而 GLOBAL索引则对应整个分区表。
1、创建分区表LOCAL索引tpcds_web_returns_p2_index1
CREATE INDEX tpcds_web_returns_p2_index1 ON tpcds.web_returns_p2(ca_address_id) LOCAL;
2、创建分区表GLOBAL索引tpcds_web_returns_p2_global_index
CREATE INDEX tpcds_web_returns_p2_global_index ON tpcds.web_returns_p2(ca_street_number) GLOBAL;
3、查询索引。查询系统和用户定义的所有索引
SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';
GaussDB支持4种创建索引的方式
3.7 其他
- 命令查看当前数据库存储编码
show server_encoding;
- 如果过滤条件只有OR表达式，可以将OR表达式转化为UNION ALL以提升性能。 使用OR的SQL语句经常无法优化，导致执行速度变慢。
四、开发设计建议
4.1 表设计
选择分区方案
当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则：
- 使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。
- 分区名称应当体现分区的数据特征。例如，关键字+区间特征。
- 将分区上边界的分区值定义为MAXVALUE，以防可能出现的数据溢出。
创建Range分区表
CREATE TABLE staffS_p1
(
staff_ID NUMBER(6) not null,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
employment_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(4,2),
MANAGER_ID NUMBER(6),
section_ID NUMBER(4)
)
PARTITION BY RANGE (HIRE_DATE)
(
PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'),
PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'),
PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE)
);
创建Interval分区表，初始两个分区，插入分区范围外的数据会自动新增分区
CREATE TABLE sales
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL('1 day')
( PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00'),
PARTITION p2 VALUES LESS THAN ('2019-02-02 00:00:00')
);
创建List分区表
CREATE TABLE test_list (col1 int, col2 int)
partition by list(col1)
(
partition p1 values (2000),
partition p2 values (3000),
partition p3 values (4000),
partition p4 values (5000)
);
创建Hash分区表
CREATE TABLE test_hash (col1 int, col2 int)
partition by hash(col1)
(
partition p1,
partition p2
);
4.2 数据加载和卸载
在批量数据入库之后，或者数据增量达到一定阈值后，建议对表进行ANALYZE操 作，防止统计信息不准确而导致的执行计划劣化。
4.3 函数
- 时间相关
五、应用程序开发教程
5.1 连接参数
六、SQL 调优指南
6.1 调优手段
统计信息
GaussDB优化器是典型的基于代价的优化(Cost-Based Optimization，简称CBO)。在 这种优化器模型下，数据库根据表的元组数、字段宽度、NULL记录比率、distinct 值、MCV值、HB值等表的特征值，以及一定的代价计算模型，计算出每一个执行步骤 的不同执行方式的输出元组数和执行代价(cost)，进而选出整体执行代价最小/首元组 返回代价最小的执行方式进行执行。这些特征值就是统计信息。
通过ANALYZE语法收集整个表
注意，DDL可能会导致统计信息发生变化，进而导致计划跳变。当表上做了DDL操作 后，应注意统计信息是否需要重新收集。
可以通过如下方法确认查询中涉及到的表或列有没有做过analyze收 集统计信息
explain verbose xxxsql
6.2 执行计划
6.2.1 SQL 执行计划概述
SQL执行计划是一个节点树，显示GaussDB执行一条SQL语句时执行的详细步骤。每一个步骤为一个数据库运算符。
使用EXPLAIN命令可以查看优化器为每个查询生成的具体执行计划。EXPLAIN给每个 执行节点都输出一行，显示基本的节点类型和优化器为执行这个节点预计的开销值。
- 最底层节点是表扫描节点，它扫描表并返回原始数据行。不同的表访问模式有不 同的扫描节点类型：顺序扫描、索引扫描等。
- 如果查询需要连接、聚集、排序或者对原始行做其他操作，那么就会在扫描节点 上添加其他节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出 现不同的执行节点类型。
- 第一行(最上层节点)是执行计划总执行开销的预计。这个数值就是优化器试图最小化的数值。
执行计划显示格式
GaussDB对执行计划提供了normal、pretty、summary、run四种显示格式：
- normal：代表使用默认的打印格式。
- pretty：代表使用GaussDB改进后的新显示格式。新的格式层次清晰，计划包含了 plan node id，性能分析简单直接。
- summary：在pretty的基础上增加了对打印信息的分析。
- run：在summary的基础上，将统计的信息输出到csv格式的文件中，以便于进一 步分析
pretty格式执行计划示例：通过设置GUC参数explain_perf_mode，可以显示不同格式的执行计划。
执行计划显示信息
可以通过不同的EXPLAIN用法，显示不同详 细程度的执行计划信息。常见有如下几种
- EXPLAIN statement：只生成执行计划，不实际执行。其中statement代表SQL语句。
- EXPLAIN ANALYZE statement：生成执行计划，进行执行，并显示执行的概要信 息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花费的总时间 (以毫秒计)和它实际返回的行数。
- EXPLAIN PERFORMANCE statement：生成执行计划，进行执行，并显示执行期间的全部信息。
为了测量运行时在执行计划中每个节点的开销，EXPLAIN ANALYZE或EXPLAIN PERFORMANCE会在当前查询执行上增加性能分析的开销。在一个查询上运行 EXPLAIN ANALYZE或EXPLAIN PERFORMANCE有时会比普通查询明显地花费更多的时 间。超出的时间多少取决于查询本身复杂程度和使用的平台。
因此，当定位SQL运行慢问题时，如果SQL长时间运行未结束，建议通过EXPLAIN命令 查看执行计划，进行初步定位。如果SQL可以运行出结果，则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及其实际的运行信息，以便更精确 地定位问题原因。
6.2.2 执行计划详解
6.2.2.1 执行计划
以如下SQL语句为例：
CREATE TABLE t1 (c1 int, c2 int);
CREATE TABLE t2 (c1 int, c2 int);
-
执行计划字段解读（横向）：
- id：执行算子节点编号。
- operation：具体的执行节点算子名称。
- E-rows：每个算子估算的输出行数。
- E-width：每个算子输出元组的估算宽度。
- E-costs：每个算子估算的执行代价。
- E-costs是优化器根据成本参数定义的单位来衡量的，习惯上以磁盘页面顺序抓取为1个单位， 其它开销参数将参照它来设置。
- 开销只反映了优化器关心的东西，并没有把结果行传递给客户端的时间考虑进去。虽然这个时间可能在实际的总时间里占据相当重要的分量， 但是被优化器忽略了，因为它无法通过修改规划来改变。
-
执行计划层级解读（纵向）：
-
第一层：Seq Scan on t2
表扫描算子，用Seq Scan的方式扫描表t2。这一层的作用是把表t2的数据从 buffer或者磁盘上读上来输送给上层节点参与计算。
-
第二层：Hash
Hash算子，作用是把下层计算输送上来的算子计算hash值，为后续hash join 操作做数据准备。
-
第三层：Seq Scan on t1
表扫描算子，用Seq Scan的方式扫描表t1。这一层的作用是把表t1的数据从 buffer或者磁盘上读上来输送给上层节点参与hash join计算。
-
第四层：Hash Join
join算子，主要作用是将t1表和t2表的数据通过hash join的方式连接，并输出 结果数据。
-
6.2.3 算子详解
6.2.3.1 关键字概述
表访问方式
-
Seq Scan：全表顺序扫描。
-
Index Scan
索引扫描。
索引扫描可以分为以下几类，它们之间的差异在于索引的排序机制。
- Bitmap Index Scan。使用位图索引抓取数据页。
- Index Scan using index_name。使用简单索引搜索，该方式按照索引键的顺序在索引表中抓取数据。该方式 最常用于在大数据量表中只抓取少量数据的情况，或者通过ORDER BY条件匹 配索引顺序的查询，以减少排序时间。
- Index-Only Scan。当需要的所有信息都包含在索引中时，仅索引扫描便可获取所有数据，不需要引用表。
表连接方式
-
Nested Loop
嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。
-
(Sonic) Hash Join
哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同，不影响执行的结果集。
-
Merge Join
归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行归并连接时，并不需要再排序，此时归并连接的性能优于哈希连接。
运算符
- Sort。对结果集进行排序。
- Filter。意味着规划节点为它扫描的每一行检查该条件，并且只输出符合条件的行。
6.2.3.2 表访问方式
6.2.3.2.1 Seq Scan
Seq Scan算子是所有扫描算子中具有普适性的一种，这个算子本质上的原理为对表按 某个方向（前向/后向）进行顺序扫描，然后返回符合筛选条件的所有行。
典型场景
- 表无索引，需要对表进行扫描操作。
- 表有索引，但需要对表大部分数据进行扫描操作。
示例
示例1：表无索引，需要对表进行扫描操作。
示例2：表有索引，但需要对表大部分数据进行扫描操作。优化器认为索引扫描效率不如全表扫描，最终选择了全表扫描。因为要查询的数据占了大多数。
6.2.3.2.2 Index Scan
在索引扫描中，数据库使用语句指定的索引列，通过遍历索引树来检索行。数据库为一个值扫描索引时，发生n次I/O 就能找到其要查找的值，其中n即B-tree索引的高度。
典型场景
- 查询某个表中的特定行：当查询语句中包含WHERE子句时，如果WHERE子句中 的条件可以通过索引列进行匹配，那么GaussDB就可能会使用Index Scan来查找 符合条件的行。
- 排序：当查询语句中包含ORDER BY子句时，如果ORDER BY子句中的列可以通过 索引进行排序，那么GaussDB就可能会使用Index Scan来进行排序操作。
- 聚合：当查询语句中包含GROUP BY子句时，如果GROUP BY子句中的列可以通过 索引进行分组，那么GaussDB就可能会使用Index Scan来进行聚合操作。
- 连接：当查询语句中包含JOIN操作时，如果JOIN操作中的列可以通过索引进行匹 配，那么GaussDB就可能会使用Index Scan来进行连接操作。
6.2.3.2.3 Index Only Scan
Index Only Scan是GaussDB中的一种查询优化技术，它可以通过只扫描索引而不需要 访问表数据来提高查询性能。在执行查询时，如果查询条件只涉及到表的某个索引 列，就可以使用Index Only Scan来优化查询。Index Only Scan会直接扫描索引，从而 减少了I/O操作和CPU开销，提高了查询性能。类似于索引覆盖
只需要查询索引列的值，而不需要访问表中的其他列。例如，查询一个表中的某个列 的最大值或最小值，或者查询一个列的不同值的数量。
6.2.3.3 表连接方式
6.2.3.3.1 Nested Loop Join
嵌套循环连接（Nested Loop Join）是最简单的连接方法，也是所有关系数据库系统中 都会实现的连接操作。这种方法的基本思想是“把两个表中的数据两两比较，看是否 满足连接条件”。
在GaussDB中，Nested Loop Join的工作原理是，对于外部表（Outer Table）中的每 一行，扫描内部表（Inner Table），查找符合连接条件的行。这类似于两个嵌套的循 环，外部循环遍历外部表，内部循环遍历内部表，因此得名 。
Nested Loop Join的时间复杂度是O(n*m), 其中n和m分别代表两个表的行数，如果内 部表可以用索引来扫描，那么时间复杂度可以降低到O(nlogm)。
典型场景
- 当内部表或外部表（或者两者都）非常小的时候。
- 当内部表能够根据连接条件快速定位到满足条件的行时，例如内部表的连接字段 已经建了索引。
- Nested Loop Join对连接的条件没有限制，任何连接条件Nested Loop Join都可以 执行。
6.2.3.3.2 Hash Join
哈希连接（Hash Join）是一种高效的连接方法，它依赖于哈希技术。在进行哈希连接 时，GaussDB会先选取两个表中的一个（通常是小表），接下来根据连接条件，建立 一个哈希表。哈希表的键是小表的连接字段，值是小表的其他字段。然后，对于大表 中的每一行，计算连接字段的哈希值，并在哈希表中查找是否有匹配的行。
Hash Join的时间复杂度为O(n+m), 其中n和m分别代表两个表的行数。然而，如果内 部表过大，以至于哈希表无法完全放入内存，则可能需要额外的磁盘I/O操作，这会导 致性能降低。
典型场景
当两个表的行数差距很大，并且进行连接操作，且两个表的连接字段是等值连接 （比如使用=运算符）。
6.2.3.3.3 Merge Join
合并连接（Merge Join）是一种高效的连接方法，它依赖于排序操作。在进行合并连 接时，GaussDB会对两个表的连接字段进行排序，然后同步扫描两个表，寻找匹配的 行。 Merge Join的时间复杂度为O(n+m), 其中n和m分别代表两个表的行数。然而， 如果需要排序操作，这个排序操作的时间复杂度可能会达到max(O(logn), O(logm)), 这通常会比直接的Merge Join操作更加耗时。 在GaussDB中，优化器更倾向于选择 Hash Join，即使需要连接的两张表已经经过排序。
典型场景
- 当两个表的大小接近时。
- 当两个表的连接字段已经被排序或者已经有序时，比如通过索引保持排序。
七、SQL参考
7.1 常量与宏
7.2 返回集合的函数
7.2.1 序列号生成函数
generate_series(start, stop)
描述：生成一个数值序列，从start到stop，步长为1。
参数类型：int、bigint、numeric
返回值类型：setof int、setof bigint、setof numeric（与参数类型相同）
generate_series(start, stop, step)
描述：生成一个数值序列，从start到stop，步长为step。
参数类型：int、bigint、numeric
返回值类型：setof int、setof bigint、setof numeric（与参数类型相同）
generate_series(start, stop, step interval)
描述：生成一个数值序列，从start到stop，步长为step。
参数类型：timestamp或timestamp with time zone
返回值类型：setof timestamp或setof timestamp with time zone（与参数类型相 同）
八、最佳实践
8.1 COPY 导入导出最佳实践
GaussDB提供了COPY语法，可用于从数据库导出数据到文件中。数据文件支持四种格 式，分别是CSV格式、BINARY格式、FIXED格式和TEXT格式。同时，COPY语法也支持 将这四种格式文件导入到指定表。
8.1.1 推荐使用 CSV 格式
CSV格式通过换行符将整个文件划分为多条记录，再通过分隔符（delimiter，默认值 为逗号’,‘）将每条记录划分为多个字段，每一个字段可以通过封闭符（quote，默认值 为引号’"'）包裹起来。通过这种方式，无需对特殊字符进行转义，即可解决字段内容 中出现的行结束符和分隔符等特殊字符问题。同时它是一项通用标准，具备跨平台兼 容性和行业普适性，所以优先推荐采用该格式。
建议导出命令：
copy {data_source} to '/path/export.csv' delimiter ',' quote '"' escape '"' encoding {server_encoding} csv;
--data_source 可以是一个表名称，也可以是一个select语句
--server_encoding 可以通过show server_encoding获得
对应导入命令：
copy {data_destination} from '/path/export.csv' delimiter ',' quote '"' escape '"' encoding {file_encoding} csv;
--data_destination 只能是一个表名称
--file_encoding 为该文件导出时指定的编码格式
8.1.2 导入导出的数据文件在 GSQL 客户端的场景
当使用GSQL执行COPY命令导出数据时，生成的数据文件默认存储在数据库服务端， 这可能导致用户获取数据文件时面临一定不便。针对此场景，建议采用\COPY命令进 行操作，该命令会将导出的数据文件直接生成在客户端本地。
COPY 与\COPY 的区别
-
数据文件的位置差异：COPY导入生成的文件与导入时读取的文件均在服务端节点 上，而\COPY导入生成的文件与导入时读取的文件均在客户端节点上。
-
性能差异：由于\COPY是在客户端读取文件流后传输给服务端完成数据的导入， 所以性能上会比COPY导入低。
-
功能差异：\COPY在COPY的基础上额外支持基于客户端并行导入的能力
\COPY 命令示例
\COPY的导出命令与COPY命令的区别为把命令中的COPY换成\COPY即可，此处提供 一个简单的CSV格式COPY导出命令转换为\COPY导出命令的示例：
--COPY命令
COPY {data_source} to '/path/export.csv' encoding {server_encoding} csv;
COPY {data_source} from '/path/export.csv' encoding {server_encoding} csv;
--对应的\COPY命令
\COPY {data_source} to '/path/export.csv' encoding {server_encoding} csv;
\COPY {data_source} from '/path/export.csv' encoding {server_encoding} csv;
并行导入命令示例
--CSV格式的导入命令
\COPY {data_destination} from '/path/export.txt' encoding {file_encoding} parallel {parallel_num} csv;
--FIXED格式的导入命令
\COPY {data_destination} from '/path/export.txt' encoding {file_encoding} parallel {parallel_num} fixed;
--TEXT格式的导入命令
\COPY {data_destination} from '/path/export.txt' encoding {file_encoding} parallel {parallel_num};
--data_destination 只能是一个表名称
--file_encoding 表示该二进制文件导出时指定的编码格式
--parallel_num 表示数据导入时的客户端数量，在集群资源较为充足时建议此值为8。
九、基础概念
1. 锁
1.1 锁简介
数据库对公共资源的并发控制是通过锁实现的，使用锁的一般流程操作可以简述为3 步：加锁、临界区操作、放锁。
当对表进行DDL/DML操作时，数据库会对表进行加锁操作，在事务结束时释放。 GaussDB提供了8个级别的锁分别用于不同语句的并发，各操作对应的锁以如下表所示：
当两个事务的锁产生冲突时，未获取到锁的线程会等待锁。如果等待时间超过了系统 设置的参数lockwait_timeout（默认20分钟），则会发生锁等待超时。
锁示例：
--建表并插入数据。
gaussdb=# CREATE TABLE testl1(c1 INT, c2 VARCHAR(5));
CREATE TABLE
gaussdb=# INSERT INTO testl1 VALUES (1,'a'),(2,'b'),(3,'c');
INSERT 0 3
--查看会话参数。
gaussdb=# SHOW lockwait_timeout;
lockwait_timeout
------------------
20min
(1 row)
--在第一个会话中执行。
gaussdb=# BEGIN;
BEGIN
gaussdb=# INSERT INTO testl1 VALUES (4,'d');
INSERT 0 1
--在第二个会话中执行，该SQL会一直等到第一个会话结束才开始执行。
gaussdb=# CREATE INDEX idx_testl1_c1 ON testl1(c1);
--第一个会话中结束事务。
gaussdb=# END;
COMMIT
查询锁等待
--建表并插入数据。
gaussdb=# CREATE TABLE testl2(id INT,info VARCHAR(10));
CREATE TABLE
gaussdb=# INSERT INTO testl2 VALUES (1,'info1'),(2,'info2');
INSERT 0 2
--第一个会话中执行。
gaussdb=# BEGIN;
BEGIN
gaussdb=# CREATE INDEX idx_testl2_id ON testl2(id);
CREATE INDEX
--第二个会话中执行。
gaussdb=# INSERT INTO testl2 VALUES (3,'info3');
-
打开一个新的会话，通过如下SQL查询出等待执行的SQL。
gaussdb=# SELECT datname,pid,query,query_id,waiting FROM pg_stat_activity WHERE waiting = TRUE; datname | pid | query | query_id | waiting ----------+-----------------+----------------------------------------+------------------+--------- postgres | 140389444876032 | INSERT INTO testl2 VALUES (3,'info3'); | 3940649678789642 | t (1 row) -
通过pid查询等待事件
gaussdb=# SELECT node_name,db_name,query_id,tid,wait_status,wait_event FROM pg_thread_wait_status WHERE tid = 140389444876032 AND db_name IS NOT NULL; node_name | db_name | query_id | tid | wait_status | wait_event -----------+----------+------------------+-----------------+--------------+------------ dn_6001 | postgres | 3940649678789642 | 140389444876032 | acquire lock | relation (1 row) -
通过如下SQL查询出持锁SQL的pid
--通过如下sql查询出持锁语句关联的pid， locktype和上面查询出的wait_event保持一致。 gaussdb=# SELECT relation,pid,mode,granted FROM pg_locks WHERE pid = 140389444876032 AND locktype='relation'; relation | pid | mode | granted ----------+-----------------+------------------+--------- 26466 | 140389444876032 | RowExclusiveLock | f (1 row) --通过如下语句查询出持锁语句的pid， granted字段为t表示持锁语句。 gaussdb=# SELECT relation,pid,mode,granted FROM pg_locks WHERE relation = 26466; relation | pid | mode | granted ----------+-----------------+------------------+--------- 26466 | 140389444876032 | RowExclusiveLock | f 26466 | 140389939934976 | AccessShareLock | t 26466 | 140389939934976 | ShareLock | t (3 rows) -
通过如下SQL可以查询出持锁语句的详细信息。
如下信息显示该语句状态为"idle in transaction" 表示事务待提交。手动提交事务 后，待执行的INSERT语句才会开始执行。
gaussdb=# SELECT datname,pid,query,query_id,waiting,state FROM pg_stat_activity WHERE pid = 140389939934976; datname | pid | query | query_id | waiting | state ----------+-----------------+-------------------------------------------+----------+---------+--------------------- postgres | 140389939934976 | CREATE INDEX idx_testl2_id ON testl2(id); | 0 | f | idle in transaction (1 row)根据实际情况决定是否通过pg_terminate_backend函数去对应的节点上结束持锁 SQL的线程。
gaussdb=# SELECT pg_terminate_backend(140389939934976); pg_terminate_backend ---------------------- t (1 row)
部署形态
主备部署也叫集中式部署。
数据导入导出
1、gsql
2、copy
JDBC使用copy
3、gs_dump gs_restore
4、gs_loader
使用方法
支持position
支持列表达式
按行提交
explain
统计信息
调优工具
系统视图
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
26
0- 0
已为社区贡献2条内容
所有评论(0)