首页  思维导图  详情

gaussDB

2024-11-11 23:47:56   0  举报





AI智能生成

GaussDB数据库认证考试知识点整理

gaussDB

作者其他创作

大纲/内容

鲲鹏生态

分布式执行框架

GTM-Lite技术

NUMA-Aware事务处理

高性能

跨AZ/Region容灾，并行回放极致RTO

Recovery Time Objectives

Recovery Point Objectives

RPO=0，RTO<30秒

高可用

Scale-out在线横向扩展

新增分片支持在线重分布

高扩展

访问控制、加密认证、数据库审计、动态数据脱敏、全密态

高安全性

全栈自研

优点：易于扩展、内部处理自动化并行、优化I/O、增加节点扩展性能

运维管理模块(Operation Manager)。提供集群日常运维、配置管理的管理接口、工具

集群管理模块(Cluster Manager)。管理和监控分布式系统中各个功能单元和物理资源的运行情况，确保整个系统的稳定运行

全局事务管理器(Global Transaction Manager)，负责生成和维护全局事务ID、事务快照、时间戳、sequence信息等全局唯一的信息

GTM

协调节点(Coordinator Node)。负责接收来自应用的访问请求，并向客户端返回执行结果；负责分解任务，并调度任务分片在各DN上并行执行

数据节点(Data Node)。负责存储业务数据（支持行存、列存、混合存储）、执行数据查询任务以及向CN返回执行结果

分布式键值存储系统(Editable Text Configuration Daemon)。用于共享配置和服务发现（服务注册和查找）

ETCD

服务器的存储资源

Storage

关键角色

基于数据库日志复制的热备，单机性能可满足需求的情况下，提供高可用

主备部署

高扩展、高可用

全分布式部署

部署形态

每个数据节点都有完整的表数据

REPLICATION

对指定的列进行Hash，通过映射，把数据分布到指定DN

HASH

对指定列按照范围进行映射，把数据分布到对应DN

RANGE

对指定列按照具体值进行映射，把数据分布到对应DN

LIST

数据分布策略

ACID :Atomicity、Consistency、Isolation、Durability

全局强一致的分部署事务

READ UNCOMMITTED

READ COMMITTED：

REPEATABLE READ

SERIALIZABLE暂不支持

事务隔离级别

无锁、多版本、高并发事务

分布式强一致，提供全局事务快照和提交号管理，强一致性，无中心节点性能瓶颈

GTM-Lite

不支持分布式强一致性读，消除GTM单点瓶颈，事务处理性能更高

GTM-Free

硬件冗余、实例冗余、数据冗余

同城跨AZ双活

两地三中心容灾

分钟级

1、同步DN元信息

与数据量相关，小时级

2、数据扩容重分布

秒级

3、表切换

在线横向扩展

内网模式：JDBC Client做负载均衡，CN感知时间30秒

外网模式：ELB做负载均衡，故障感知时间默认3秒

多CN提供统一入口

负载均衡

单租户OBS带宽15G

单分片备份2T约4小时，5分钟备份数据量约200M

支持增量备份和全量备份

备份与恢复

热补丁、灰度、滚动升级

在线平滑升级

DN分片最大256，单表最大32T*节点数，单行最大1600*1G，单字段最大1G，单表行数/单表索引个数/单表约束个数均2的23次方，单表列数1600，对象名63字节，并发连接10w，

AZ内：RPO = 0、RTO < 10s；AZ外：RPO = 0、RTO < 60s

技术指标

全局事务管理器GTM

运行在linux操作系统

元命令：反斜杠开头命令（不带引号）

gsql

数据库列表功能

SQL操作功能

库管理功能

导入功能

导出功能

华为云数据管理服务

Data Admin Service

DAS

图形化界面

DBeaver

客户端工具

sharding nothing架构

第一章介绍

基于主机的认证

口令认证

SSL加密

接入认证

数据库驱动

子主题

JAVA标准

基本功能，生成链接、执行语句、准备语句、批处理

java.sql

扩展功能，连接管理，分布式事务，连接池，行集

javax.sql

主要的两个包

JDBC API

JDBC Driver Manager

JDBC Driver

三层

支持SHA256加密方式登录支持对接实现sf4j接口的第三方日志框架支持连接级别的分布式负载均衡支持容灾切换

Gauss JDBC

加载驱动、连接数据库、执行SQL语句、处理结果集、关闭连接

基本操作

DriverManager.getConnection

管理数据库的驱动

DriverManager

Connection接口表示应用程序与数据库的连接对象

Connection接口

创建Statement对象：使用JDBC连接对象的createStatement()方法返回一个Statement对象

运行查询并检索结果集对象：如果要查询数据库，使用Statement对象的executeQuery()方法，此方法将SQL语句作为输入，并返回ResultSet对象

Statement接口

在执行查询后，请使用ResultSet的next()方法遍历结果

Statement - 处理结果

next()、previous()、beforeFirst()、afterLast()、first()、last()、absolute()、relative()

ResultSet对象具有指向其当前数据行的光标

ResultSet接口

用于收集ResultSet的所有元数据信息，例如列的类型和属性，列数，列的名称，列的数据类型等。

ResultSetMetaData接口

PreparedStatement接口表示预编译的 SQL 语句的对象，是Statement的子接口，它允许数据库预编译SQL语句（这些SQL语句通常都带有参数），以后每次只改变SQL命令的参数，避免数据库每次都需要编译SQL语句，因此性能更好

PreparedStatement接口

与Statement不同，PreparedStatement可以执行带有不同输入参数集的语句

PreparedStatement

addBatch()、executeBatch()

批处理

使用游标获取结果

用于提供到此DataSource对象所表示的物理数据源的连接

DataSource

关闭

false

true

balance

roundrobin

轮询

前2个的优先级

priority n

优先级

autoBalance= shuffle

随机

autoBalance=[value]

周期刷新可使用参数refreshCNIpListTime配置，默认10秒

JDBC负载均衡

targetServerType=master

读写节点

targetServerType=slave

只读节点

targetServerType=preferSlave

只读优先

targetServerType=any

任意节点

targetServerType=[value]

读写分离与故障转移

loggerLevel={OFF、INFO、DEBUG、TRACE}

日志级别

loggerFile=目录和文件名

日志目录

slf4j-API 、JdkLogger

logger

日志框架

日志记录

JDBCjava数据库连接

unixODBC和iODBC

驱动

安装配置

申请句柄资源

SQLAllocHandle

释放与指定环境、连接、语句或描述符相关的资源

SQLFreeHandle

将数据缓冲区绑定到结果集的列中

SQLBindCol

将SQL语句中的一个参数标志和一个缓冲区绑定起来

SQLBindParameter

返回结果集中某一列的描述符信息

SQLColAttribute

在驱动程序和数据源之间建立连接

SQLConnect

关闭一个与特定连接句柄相关的连接

SQLDisconnect

使用参数的当前值，执行一条准备好的语句

SQLExecDirect

使用标记参数的当前值，执行一条准备好的语句

SQLExecute

从结果集中取下一行的数据，并返回所有被绑定列的数据

SQLFetch

准备一个将要进行的SQL语句

SQLPrepare

返回结果集中某一列的数据

SQLGetData

返回诊断记录中的信息

SQLGetDiagRec

设置控制连接方面的属性

SQLSetConnectAttr

设置控制环境方面的属性

SQLSetEnvAttr

设置语句相关属性

SQLSetStmtAttr

常用接口

想要把多条语句放在一个事务中，可以关闭autocommit开关，并且在事务结束时调用SQLEndTran

连续执行两条select语句会报错the cursor is open

常见问题

ODBC开放式数据库连接

是一种用于执行SQL语句的PythonAPI

关闭连接

处理结果集

执行sql

连接db

加载驱动

处理gaussdb的连接，是线程安全的，可以在多个线程之间共享可以作为context管理器，context包装了事务，如果context成功退出则提交事务

connect方法，执行SQL语句，返回新的connection

connection类

游标对数据库的任何更改，都可以立即被其他游标看到

返回cursor对象

cursor方法

执行给定的SQL语句，可执行被参数化的SQL语句

execute方法

能够执行SQL命令所有参数序列或序列中的SQL映射。但是此方法并不比execute()在循环中执行快

executemany方法

执行给定的SQL语句，能够提取查询结果集的下一行，并返回一个元组

fetchone方法

执行给定的SQL语句，能够获取查询结果的所有（剩余）行，并将它们作为元组列表返回

fetchall方法

从类文件对象文件中读取数据，并将它们附加指定的表中

copy_from方法

将指定名称的表的内容写入类文件对象file

copy_to方法

Cursor类

Psycopg

第二章应用程序开发指引

避免使用保留或非保留关键字命名数据库对象

避免使用双引号括起来的字符串来定义数据库对象名称，除非需要限制数据库对象名称的大小写。数据库对象名称大小写敏感会使定位问题难度增加

多个单词，下划线分隔

变量具有描述性

数据库对象命名风格务必统一

临时表：tmp_+后缀

非日志：ul_+后缀

外表：f_+后缀

不能用“redis_”为前缀

表对象具有表特征

使用schema进行业务隔离

业务创建新的database，不使用默认库

编码使用UTF-8

Database

默认

字符串转整形时，输入不合法被转换成0

mysql

teradata

空字符串作为null，date被替换为timestamp（0）without time zone

oracle

char和varchar以字符为计数单位，其他以字节为计数单位

postgreSQL语法

兼容性

用户不具有sysadmin权限或不是owner，要访问schema下对象，需要同时给用户赋予schema的usage权限和对象的权限

要在schema下创建对象，需要授予创建create权限

owner具有schema下对象所有权限

schema

选择合适的分布列，避免数据切斜

将表的扫描压力分散在各个dn上，避免扫描压力集中

分区表的剪枝机制，可以减少数据的扫描量

避免数据shuffle(在节点传输)

表设计

整数>浮点数>numeric

高效数据类型建议

多张表的同一含义字段使用相同数据类型

不建议使用定长字段

字符串，使用变长字符串类型，指定最大长度

字段设计

优先从业务层附默认值，其次使用default约束

不为null的设置not null约束

其他约束显式命名

一张表只能建一个pck，可包含多列，一般不超过2列

可以通过min/max稀疏索引实现事实表快速过滤，选区分度大的列建PCK

一般形式col op const，其中col为列名，op为操作符 =、>、>=、<=、<，const为常量值

局部聚簇 PCK

命名：UNI+字段

行存表、列存表均支持唯一索引

唯一约束

行存、列存均支持

命名：PK+字段名

主键约束

行存支持，列存不支持

命名：CK+字段名

检查约束

约束

除非强依赖，否则不建议视图嵌套

视图中避免排序操作

视图

表之间关联字段尽量少

关联字段类型应一致

命名体现关联关系，如命名相同

关联表

数据库命名和设计建议

hash散列分布，适合数据量大的事实表

提升读写性能

hash

小表、维度表

每个DN上都有全量数据

join时避免数据重分布，减少网络开销，减少了plan segment数量

缺点：数据冗余

Replication

按范围分布到DN，用户自定义规则

选择灵活，有一定数据抽象能力要求

Range

指定列值集映射分布到DN，用户自定义规则

List

分布方式

列值应离散，保证均匀分布

考虑选择查询的连接条件为分布列，join可下推到dn执行，减少DN间通讯

dn相差5%视为倾斜，10%以上需要调整分布列

数据倾斜性检查

Hash

选择分布列

优点：改善查询性能、增加可用性、方便维护

缺点：索引扫描代价高

建议：按时间或地区做Range分区

范围分区表

分区表

高效数据类型int>浮点数>字符串

尽量使用短字段

表关联列，使用一致的数据类型

数据类型选择

尽量避免分布式事务设计

分布式事务

表设计最佳实践

建议包含所有分布键等值查询条件

索引失效

避免使用函数或表达式

避免使用相同表字段比较

过滤条件排序，较小记录排前面

避免使用“!=”与null做比较

多用等值操作，少用非等值操作

禁止对索引字段使用“!=”

模糊匹配，%不放在前面

in不超过500

如varchar转bigint

避免涉及隐式数据类型转换

where

避免对大字段执行order by，Group By引起排序的曹组

避免使用count获取大表行数

避免select *

避免目标列使用子查询

不用select(col)替代count(*)

count(distinct col)用来计算不充分，且非NULL的数量

select

insert on DUPLICATE KEY UPDATE不支持对主键或唯一约束列上执行update，多条数据之间不存在主键/唯一约束冲突禁止对存在多个唯一约束的表执行insert on DUPLICATE KEY UPDATE

insert

不支使用limit

不支持多表更新

必须有where

不支持更新多个列时，被更新列是更新源

禁止使用order by，group by

使用主键，索引做条件

update

不支持使用limit

GMT-FREE模式下不允许跨节点事务，删除hash分布表数据时，必须在where中指定分布列等值过滤条件

不支持多表删除

不使用order by 、group by

清空表用truncate

where结合主键/索引

delete

嵌套深度必须小于8

指定个表连接条件on，避免产生笛卡尔积

指明连表方式，避免用join，应该用inner/left/cross/right join

多表使用别名

整形高于numeric和浮点型

连表字段类型相同

少用嵌套子查询，多用表关联

过滤is not null条件

关联查询

禁止出现重复子查询

结果为1个值

少用标量子查询

select目标列中不要使用子查询，计划无法下推影响性能

子查询嵌套不超过2层

子查询

GTM-FREE模式下，跨节点事务会报错

大对象不支持事务

其中包含对象操作时，如果中间对象操作失败，会重新开启新事务执行后续语句

避免拼接多条sql为一条语句执行

事务

SQL编写最佳实践

第三章开发设计建议

专注于异构数据库对象迁移和应用迁移的专业化工具

数据库版本、源库采集时间、数据库连接信息、网络稳定性

基础信息

用户是否有DDL权限及数据库动态视图访问权限

预检查

默认可访问所有schema

模式选择

INDEX、SEQUENCE、SYNONYM、TABLE、FUNCTION、VIEW、PACKAGE、PROCEDURE、TRIGGER、TYPE_BODY

对象类型选择

任务确认

查看任务详情

源库基本信息、性能信息、特征分析

所有对象的详情

源库到目标库的兼容性

查看评估任务信息

确认需要迁移的目标库

创建评估项目

收集源数据库信息

源数据库分析

选择数据库

目标数据库类型与版本

目标数据库连接细腻系

测试连接后创建

创建迁移项目

用户密码设置

迁移计划

源库与目标库之间表空间的映射配置

迁移配置

触发迁移，查询错误报告，将源SQL转换成目标SQL

进行迁移

迁移

失败对象，手工校正

查询对象校正，再次验证

对象校正

验证&应用

数据库对象迁移任务

数据库迁移评估

UGO数据迁移服务Database and Application Migration UGO

数据库在线迁移和数据库实时同步的云服务

流程：调研、方案制定、技术验证、迁移演练、迁移实施

业务自由回滚方案，一个正向DRS+一个反向DRS链路

全量同步

增量同步

技术原理

流动方向：入云

ORACLE

源库引擎

目标引擎

网络类型

同步类型：全量+增量/全量

源库和目标库的连接信息

表级，库级

同步对象

同步类型

流模式

分片记录数

同步位点

全量配置

日志抓取并发数

回放任务并发数

回放启动策略

冲突策略

增量配置

预校验

启动同步任务

查看同步进度

对象级：数据库、索引、表、视图

数据级：表的行数和内容对比

同步对比

其他库到GuassDB同步任务

填写源库和目标库的连接信息

GaussDB到其他库的同步任务

源库权限

目标库权限

主键冲突、列不存在、长度超长

DRS数据复制服务Data Replication Service

第四章GaussDB数据库迁移

创建：postgres=# CREATE SCHEMA test_sche {AUTHORIZATION test_user1};

修改：Alter schema

删除：DROP SCHEMA

模式（schema）

用户、角色、系统权限、对象权限

角色和用户之间的区别在于角色默认没有login权限

分类：初始用户/系统管理员（SYSADMIN）

三权分立：将系统管理员的部分权限分给安全管理员和审计管理员

开启三权分立：enableseparationofduty=on

SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE和USAGE

授权：GRANT

撤销授权：REVOKE

对象隔离特性：开启后，数据库默认为系统表增加行级访问控制策略

开启对象隔离特性：ALTER DATABASE database_name ENABLE PRIVATE OBJECT

权限管理

是操作系统的一个目录，可以存在多个，存储的是所包含的数据库的物理文件管理功能依赖于文件系统

可以在不同的分区上创建和使用表空间

可以设置占用的磁盘空间，防止表空间占相同分区的其他空间

可控制数据库占用的磁盘空间，90%时只读模式

优点

存储系统目录、用户表、用户表index、临时表、临时表index

pg_default

存放系统字典表

pg_global

自带2个表空间

postgres=# create tablespace tbs2 relative location ‘tablespace/tbs2’ maxsize ‘100G’;

创建

select * from pg_tablespace_location((select oid from pg_tablespace where spcname='tbs2'));

查询

postgres=# alter tablespace tbs3 rename to tbs4; --修改表空间名postgres=# alter tablespace tbs4 owner to jack; --修改表空间所有者postgres=# alter tablespace tbs4 resize maxsize unlimited; --修改表空间上限大小postgres=# alter tablespace tbs4 reset (random_page_cost); --修改表空间属性

修改

drop tablespace tbs4;

删除

管理

表空间

用户

两个模版数据库template0、template1

创建数据库默认拷贝template0

create database mydb3 with owner=jack encoding=‘UTF-8’ LC_COLLATE=‘zh_CN.UTF-8’ LC_CTYPE=‘zh_CN.UTF-8’ DBCOMPATIBILITY=‘A’ TABLESPACE=tbs1 CONNECTION LIMIT=1000; --指定了数据库mydb的拥有者(owner)、编码(encoding)、字符集(LC_COLLATE)、字符分类(LC_CTYPE)、兼容模式(DBCOMPATIBILITY)、默认表空间(TABLESPACE)、并发连接限制(CONNECTION LIMIT)

postgres=# alter database mydb3 rename to mydb4; --修改数据库名postgres=# alter database mydb2 owner to user1; --修改数据库所有者postgres=# alter database mydb2 set tablespace tbs1; --修改数据库所属表空间

postgres=# drop database mydb4;

postgres=# \\l –使用元命令查看数据库postgres=# select * from pg_database; --通过系统表查看数据库

数据库管理

数据库概述

一行叫元组tuple

每个表有多个列，也称为属性attribute

普通表

以行式存储，每行所有属性存储到一起

适合查询一行所有属性，insert，update效率高

行存表（默认）

以列示存储，每列多个记录存储到一起

适用于海量数据查询，减少磁盘访问数据量，但insert、update较为麻烦

列存表

OLTP场景，推荐行存储

OLAP场景，推荐列存储，存储在分区上

存储模型

create table emp1 as select * from emp where sal<2000

create table emp2 as table emp;

行存表创建

列存表创建

drop table {table_name}

删除表

alter table {table_name} enable row level security

行级访问控制

普通表管理

逻辑上的一张表根据某种方案分成几张物理块进行存储是一张逻辑表，不存储数据，数据实际是存储在分区上

范围分区

hash分区

列表分区

分区方案

列存表只支持范围分区，行存表支持范围、间隔、哈希、列表

优势：查询性能，可用性，可维护性，IO均衡

alter table pt1 drop partition for(90);alter table pt1 drop partition p3;

删除分区

alter table pt1 add partition p3 values less than (95);alter table pt1 add partition p4 values less than (MAXVALUE);

增加分区

表的所有者有权限执行ALTER TABLE命令，系统管理员默认拥有此权限不能修改分区表的tablespace，但可以修改分区的tablespace不支持修改存储参数ORIENTATION不支持增加自增列，或者增加DEFAULT值中包含nextval()表达式的列不支持对外表、临时表开启行访问控制开关通过约束名删除PRIMARY KEY约束时，不会删除NOT NULL约束，如果有需要，需手动删除NOT NULL约束使用JDBC时，支持通过PrepareStatement对DEFAUTL值进行参数化设置列存表只支持PARTIAL CLUSTER KEY表级约束，不支持主外键等表级约束列存表支持的字段约束包括NULL、NOT NULL和DEFAULT常量值；对字段约束的修改当前只支持对DEFAULT值的修改（SET DEFAULT）和删除（DROP DEFAULT），暂不支持对非空约束NULL/NOT NULL的修改

注意事项

减少元组搜索的时间，提升数据访问速度，增加插入、更新、删除的时间，要更新索引信息

创建索引列：经常查询的字段，where过滤的字段、order by、Group by，distinct，连表字段

B+树来存储，适合比较及范围查询

B-Tree

倒排索引，可以处理多个键的值

GIN

几何和地理等多维数据类型

Gist

引擎索引

唯一索引，多字段索引（最多32个字段），部分索引，表达式索引

行存表

Psort

稀疏索引，列存引擎每个列自带min/max稀疏索引，查询条件不在min，max范围内就不需要读取cu

聚簇索引：partial sort index

列存索引

修改：alter index t1_fn_idx rename to t1_fn_idx2;alter index t1_fn_idx2 set tablespace tbs2;alter index t1_lttbs_idx unusable;alter index t1_lttbs_idx rebuild;alter index pt1_id_idx rebuild partition p1_id_idx;alter index pt1_id_idx modify partition p1_id_idx unusable;alter index pt1_id_idx rename partition p1_id_idx to p1_id_idx2;alter index pt1_id_idx move partition p1_id_idx2 tablespace tbs1;

删除：drop index t1_lttbs_idx;

重建：reindex index t1_lttbs_idx; -- 重建单个索引reindex table t1;--重建所有索引

索引

封装查询，物理上不存在

创建：create view v1 as SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; -- 创建视图create materialized view mv1 tablespace tbs1 as SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; -- 创建物化视图

查询视图定义：select pg_get_viewdef('v1');

管理：alter view v1 rename to v2; -- 重命名视图alter view v2 owner to jack; -- 修改视图属主alter view v2 set schema jack; -- 修改视图schemarefresh materialized view mv1; -- 刷新物化视图

删除：drop view jack.v2; -- 删除视图drop materialized view mv1;-- 删除物化视图

视图

产生唯一整数的数据库对象，这也是Sequence常被用作主键的原因，序列的值是按照一定规则自增的整数，可以看作是存放等差数列的特殊表

创建：create sequence seq01;create sequence seq02 increment by 1 minvalue 1 maxvalue 99999 cache 1 nocycle;

修改：alter sequence seq01 maxvalue 99999;alter sequence seq01 owner to jack;

删除：drop sequence seq01;drop sequence seq02 cascade;

序列

创建：create synonym syn_t1 for t1; -- 创建表的同义词create synonym syn_emp for v_emp; -- 创建视图的同义词create synonym syn_add for func_add_sql; -- 创建函数同义词create synonym syn_proc_emp for proc_emp; -- 创建存储过程同义词

删除：drop synonym syn_add;

同义词SYNONYM

数据库对象基本操作

系统表

系统视图

\\l：列出数据库集簇中所有数据库的名称、所有者、字符集编码以及使用权限等\\d：列出当前search_path中模式下所有的表、视图和序列\\db 列出所有可用的表空间 \\dn 列出所有的模式（名称空间）\\du 列出所有数据库角色 \\dt 列出数据库中的表

gsql常用元命令

数据字典

一些SQL语句组成的记录集合，这些SQL语句代码按照预定设计来实现一些功能，例如对单表或多表的增删改查，可直接调用

优点：先编译后执行的，所以执行效率要比SQL语句高网络中交互可以替代大堆的SQL语句，能降低网络的通信量，提高通信速率存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全

缺点：对于分布式系统，存储过程内中一条语句不能下推，则存储过程不能下推，受限较多对于复杂存储过程，调试不方便。

存储过程

Anonymous Block，一般用于不频繁执行的脚本或不重复进行的活动，它们在一个会话中执行，并不被存储

匿名块

在数据库内定义的子程序，可以从内置SQL语句中被调用同时支持PG风格和O风格

1、函数必须返回单个值，存储过程可以返回多个值或者没有返回值

2、函数可以放在select语句中，存储过程不行

与存储过程的不同点

创建：CREATE [ OR REPLACE ] FUNCTION function_name

重载：create or replace function package_func_overload(col int)

管理函数参数

修改名称，所属者，函数模式

管理函数属性

CALL func_name()

调用函数

DROP FUNCTION [ IF EXIST ] Function_name() CASCADE

删除函数

函数

仅支持O风格

创建：CREATE [ OR REPLACE ] PROCEDURE procedure_name

修改：ALTER PROCEDURE Procedure_name ( { [argmode] [argname] argtype } […] ) expression […] [RESTRICT];

删除：DROP PROCEDURE [ IF EXISTS ] procedure_name;

定义变量：DECLAREemp_id INTEGER :=7788; --定义变量并赋值outer_var INTEGER :=6688; --定义变量并赋值

基本语句

EXECUTE IMMEDIATE方法：DECLAREstaff_count VARCHAR2(20);BEGINEXECUTE IMMEDIATE 'select count(*) from hr.staffs' INTO staff_count;dbe_output.print_line(staff_count);END;

动态语句

RETURN ；

RETURN NEXT ;

RETURN QUERY;

返回语句

IF_THEN

IF_THEN_ELSE

IF_THEN_ELSE IF

IF_THEN_ELSEIF_ELSE

条件语句

LOOP END LOOP

LOOP

WHILE LOOP END LOOP

WHILE_LOOP

FOR LOOP END LOOP

FOR_LOOP

FOR_LOOP查询语句

FORALL

循环语句

CASE pi_resultWHEN 1 THEN pi_return := 111;WHEN 2 THEN pi_return := 222;WHEN 3 THEN pi_return := 333;END CASE;

CASE_WHEN

分支语句

GOTO语句可以实现从GOTO位置到目标语句的无条件跳转

GOTO语句

控制语句

显示游标主要用于对查询语句的处理，尤其是在查询结果为多条记录的情况下。静态游标：就是定义一个游标名，以及其相对应的SELECT语句

静态游标

动态游标在声明时不绑定SQL语句，在打开游标时通过OPEN FOR动态绑定SQL语句

动态游标

显示游标

1、禁止使用存储过程、触发器实现业务逻辑，避免对数据库产生逻辑依赖

2、禁止使用存储过程实现数据库脚本升级

3、仅创建对固定入参有固定返回值的函数，函数必须设为IMMUTABLE和SHIPPABLE类型。

4、不允许使用 C UDF (User-Deined Function)。

函数/存储过程设计规范

RAISE INFO

使用未声明的变量

PL（Procedural Language，程序语言）语法错误

SQL语法错误

语义不正确

拼写错误

存储过程调试

UDF：User Define Function，用户自定义函数

第五章GaussDB数据库操作与管理

Instance Time

Memory

Session、Thread

Event、Utility

系统级集群或节点级别指标

Database、Table、Index

File

Lock

对象级数据库对象指标

Transation、Statement

Active session profile

slow query

Full SQL trace

应用级应用负载性能

性能指标体系

系统级的时间消耗细分。判断整个系统是否存在负载、网络、IO、CPU上的瓶颈select * from dbe_perf.instance_time

select * from dbe_pref.MEMORY_NODE_DETAIL

MEMORY_NODE_DETAIL实例级内存分配、使用率

select * from dbe_pref.SHARED_MEMORY_DETAILwhere level =2 order by usedsize desc limit 10

SHARED_MEMORY_DETAIL共享内存上下文分配/使用率

会话级负载强度、用于识别负载强度高，占用资源多的会话

SESSION_STAT

会话时间细分，识别不同时间维度上session的消耗

SESSION_TIME

定位单个session在特性级上的内存问题

SESSION_MEMORY、SESSION_MEMORY_DETAIL

获取实时系统活跃会话列表

SESSION_STAT_ACTIVITY

Session

获取实例工作线程/辅助线程列表，判断线程的运行状态当前正阻塞在哪个事件上，正在等待哪个锁，或被哪个会话阻塞

THREAD_WAIT_STATUS

Thread

各功能模块中IO、LOCK、LWLOCK、STATUS四类事件的等待次数，可以帮助定位特性级细粒度时延性能问题。

WAIT_EVENTS

Event

COMM_DELAY、COMM_RECV_STREAM、COMM_SEND_STREAM、COMM_STATUS：获取通信组件时延信息，接收和发送流状态，用于诊断通信链路容量和时延故障

REPLICATION_STAT：获取分布式部署形态下主备同步状态信息，用于诊断主备时延，复制性能故障。

GLOBAL_GET_BGWRITER_STATUS、GLOBAL_PAGEWRITER_STATUS：获取后台全量/增量检查点信息，buffer中待落盘脏页信息，后台刷脏工作线程的状态将影响buffer pool的效率，磁盘IO繁忙程度，这些信息可以帮助优化缓存和IO性能。

POOLER_STATUS：分布式部署形态下，CN和DN之间的连接池将影响事务的执行效率和成功率，pooler状态可以帮助诊断连接池瓶颈。

Utility

系统级

select * from dbe_pref.STAT_DATABASE where datbane =''

STAT_DATABASE：数据库级别的活跃连接数，负载强度，块读写性能，行活动，死锁，临时下盘文件等信息

STAT_USER_TABLES、STAT_SYS_TABLES、STAT_ALL_TABLES、STATIO_USER_TABLES、STATIO_SYS_TABLES、STATIO_ALL_TABLES

GLOBAL_STAT_HOTKEYS_INFO：识别表级别热key

Table

STAT_USER_INDEXES、STAT_SYS_INDEXES、STAT_ALL_INDEXES、STATIO_USER_INDEXES、STATIO_SYS_INDEXES、STATIO_ALL_INDEXES

index scan次数，index scan返回的索引项，通过index scan返回的表行数等，索引页的缓存效率等，用以评估索引收益和效率

Index

FILE_IOSTAT：数据（数据，索引）文件的IO性能统计指标（读写数目，耗时，时延），帮助建立数据文件物理访存的模型，识别文件级别的物理IO强度和瓶颈

FILE_REDO_IOSTAT、STAT_BAD_BLOCK：获取操作Redo文件的性能，帮助诊断Redo日志操作的性能瓶颈

LOCKS：对象锁涉及到的对象，事务，会话，锁信息，实时显示当前系统锁等待关系，识别热点锁

LOCK

STATIO_USER_SEQUENCES、STATIO_SYS_SEQUENCES、STATIO_ALL_SEQUENCES：sequence的缓存效率

Sequence

对象级

STATEMENT_COUNT，STATEMENT：DDL，DML（ select，insert，update，delete），DCL语句的分布比率，帮助建立负载特征模型

语句级别（归一化SQL，模板SQL）的响应时间，执行次数，行活动，软硬解析比，时间模型，网络开销，排序性能（时间，内存，溢出），执行器HASH性能（时间，内存，溢出）；可以识别热点语句，定位语句性能瓶颈，建立语句性能基线

Statement

活跃会话概要信息，通过采样实例活跃会话的状态信息，低成本复现过去一段时间的系统活动

最近用户session最耗资源的事件最近比较占资源的session/SQL把资源都消耗在哪些event上最近执行时间/执行次数最多的是哪些SQL(进而可以找出表，数据库)。最近最耗资源的用户的信息。最近阻塞其他session最多的session。

LOCAL_ACTIVE_SESSION默认采样评率是1秒，内存视图ASP的默认采样频率是10s，持久化在存储

ASP：Active Session Profile

L1：性能影响<3%，建议常开，规划存储

L2：性能影响<30%，建议短暂开启

应用级

整体性能问题分析

单语句性能分析

性能问题分析

WDR报告是GaussDB提供的一种性能收集和分析工具，提供一个时间段内整个系统资源使用情况的报告

定时采集dbe.perf下性能视图，生成快照

默认关闭，通过GUC参数enable_wdr_snapshot开启

默认采集周期60分钟，通过guc参数wdr_snapshot_interval调整采集周期

默认保存8天，通过wdr_snapshot_retention_days管理

可以通过执行create_wdr_snapshot系统函数

Detail：时间模型、SQL统计信息、SQL具体信息、系统等待事件Cache/IO统计、应用统计、对象统计系统配置

内容：

gsql方式连接数据库，切换至postgres库\\a\\t\\o报告路径（\\data1/cluster.html）

生成WDR报告：

不支持生成WDR报告：两次snapshot之间有节点重启、drop database、主备切换重置dbe_pref.statement视图

限制

TOP SQL

命中率：Instance Efficiency Percentages

Wait Events

Database Stat

Load Profile

Instance Efficiency Percentages

Top 10 Events by Total Wait Time

Wait Classes by Total Wait Time

Host CPU

IO Profile

Memory Statistics

Time Model

SQL Statistics

Cache IO Stats(Table)

Cache IO Stats(Index)

Utility status(Background writer stat)

Utility status(Replication slot)

Utility status(Replication stat)

Object Stats(User Table)

Object Stats(User Index)

Object Stats(Bad block)

Configuration settings

SQL Detail

示例

WDR snapshot

WDR报告

词法语法解析

语义分析

查询解析Parser

查询重写

路径&执行计划生成

查询优化Planner

执行Executor

执行机制

扫描表数据路径

访问路径

多表连接顺序

连接顺序

多表连接方式

连接方式

执行计划

ANALYZE [ boolean ] -- 执行语句，并显示实际运行时间和其他统计数据ANALYSE [ Boolean ] -- (同上)VERBOSE [ Boolean ] -- 显示计划额外信息COSTS [ Boolean ] -- 显示代价CPU [ boolean ] -- 显示cpu使用DETAIL [ boolean ] -- 打印节点信息NODES [ boolean ] -- 显示执行节点NUM_NODES [ boolean ] -- 显示节点数量BUFFERS [ boolean ] -- 显示buffer使用TIMING [ boolean ] -- 显示耗时PLAN [ boolean ] -- 显示计划FORMAT { TEXT | XML | JSON | YAML }

EXPLAIN

顺序扫描行存储引擎

SeqScan

扫描列存储引擎

CstoreScan

顺序扫描HDFS存储引擎

DfsScan

利用bitmap获取元组

BitmapHeapScanBitmapIndexScan

通过Tid获取元组

TidScan

索引扫描

IndexScan

直接从索引返回元组

IndexOnlyScan

外部表扫描

ForeignScan

扫描中间结果集

WorkTableScan

扫描Value列表

ValueScan

扫描网络算子（分布式数据库特有）

Stream

子查询扫描

SubQueryScan

扫描CommTableExpr

CteScan

函数扫描

FunctionScan

扫描算子（Scan plan Node）

Result

INSERT/UPDATE/DELETE操作的算子

ModifyTable

多个关系集合的追加操作

Append

多个有序关系集合的追加操作

MergeAppend

执行Recursive subquery

RecursiveUnion

控制算子（Control Plan Node）

物化

Materialize

对下层数据进行排序

Sort

对下层已经排序的数据进行分组

Group

对下层数据进行分组（无序）

Agg

对下层数据进行去重操作

Unique

对下层数据进行缓存，存储到一个hash表里

对下层数据进行缓存，用于处理intersect等集合操作

SetOp

窗口函数

WindowAgg

处理行级锁

LockRows

物化算子（Materialize Plan Node）

对下层两股数据流实现哈希连接操作Inner、Left-Outer-Join、Right-Outer-Join、Full-Outer-Join、Semi-Join、Anti-Join

HashJoin

对下层两股排序数据流实现归并连接操作Inner、Left-Outer-Join、Right-Outer-Join、Full-Outer-Join、Semi-Join、Anti-Join

MergeJoin

对下层两股数据流实现循环嵌套连接操作Inner、Left-Outer、Semi-Join、Anti-Join

NestLoop

内连接

Inner join

左连接

left join

右连接

right join

全连接

full join

半连接

semi join

反连接

Anti join

连接类型分

关联算子（Join Plan Node）

SQL算子

定期分析数据，把表和索引的数据分布情况保存到数据字典里，以便优化器使用，这就是统计信息

保存在数据字典里，包含表、表分区、索引、索引分区、列、列的直方图重点关注表和索引上的数据量和数据块数统计信息

执行ANALYZE对统计信息更新

PG_STATISTICS表存储有关数据库表和索引列的统计信息。需授权访问

PG_STATS视图提供对存储在PG_STATISTICS表里面的单列统计信息的访问。

统计信息

逻辑内存管理参数：max_process_memory

plan_cache_mode

执行作业可用内存：max_process_memory–shared memory (包括shared_buffers) –cstore_buffers

可下盘算子有：Hash，Agg，Sort，Material，Setop，WindowAgg以及它们的Vec版本

执行算子是否下盘参数：work_mem

内存参数调优

扫描算子控制参数：enable_**scan，支持seqscan，bitmapscan，indexonlyscan，indexscan，tidscan

连接算子控制参数：enable_hashjoin/enable_mergejoin/enable_nestloop控制开启或禁用某种连接方式

分布式执行计划控制参数：enable_stream_operator/enable_fast_query_shipping/enable_light_proxy用于控制分布式执行计划的生成。

算子参数调优

是特殊SQL语法，可用于手工干预SQL执行计划的选择，在SQL中加入hint语法，可以明确指导SQL优化器选择特定执行计划

SELECT /*+[hint](value) */ * FROM …

Scan Hint

Join Hint

Join Order Hint

Blockname Hint

Rows Hint

Plan Hint

各个DN根据下推语句生成计划，执行结果在CN上汇总

各DN执行无数据交互

下发SQL语句的计划

CN根据语句生成计划，并将计划下发至DN执行

适用各DN执行有数据交互的复杂语句

下发SQL计划的分布式计划

CN生成计划后，下推原语句的部分语句至DN，执行后结果发回CN，CN执行剩余计划

适用不满足上述计划生成条件的极少数场景

下发部分SQL的分布式计划

Hash方式散列到DN

所有DN都有全量数据

List方式分布到指定DN

Range方式分布到指定DN节点

Replication适合小表，Hash/List/Range适合数据量大的表

分布列-分布方式

数据分布

on/off

enable_light_proxy

CN轻量化

plan_cache_mode=auto/force_generic_plan/force_custom_plan

PBE

DML指定分布键过滤条件，可以下推到DN执行，事务在本地提交，减少网络开学

减少分布式事务

选择管理字段或聚合字段做分布列，减少跨节点数据分布

当前参数化路径不支持跨节点

减少数据重分布

调优思路

尽量选择聚合字段

尽量选择关联字段

保证数据均匀分布

分布列选择原则

SMP是通过算子并行来提升性能，是用资源换时间的方式

要求CPU、内存、网络、IO比较充足，否则引起性能劣化

1、观察系统资源充足，资源利用率<50%，否则放弃

2、执行SET query_dop=1，再执行explain打出执行计划，观察执行计划是否成功使用SMP特性，若成功，再设置为0或其他值

默认值为1

设置为value，强制选择为1或者value

注意，语句结束后关闭。set query_dop =1.

query_dop

步骤

配置SMP

存储层数据切斜，通常由于分布列选择不合理，调整分布列解决

explain performance，查询性能细节，是否存在切斜迹象

select table_skewness(表名); 获取存储倾斜视图

存储层倾斜

执行过程数据重分布导致倾斜（JOIN KEY、GROUP BY KEY往往不是分布列）

RLBT（Runtime Load Balance Technology）

非倾斜数据，按原HASH分布处理，切斜数据，通过轮询（RoundRobin）均匀分配到各节点特性开关：skew_option

运行ANALYZE收集

简单方法

通过HINT手动指定单表：/*+ skew(table (column) [(value)]) */中间结果：/*+ skew((join_rel) (column) [(value)]) */

复杂查询

触发后，计划中出现skew join Optimized by Statistic

RLBT配置方法

解决方案

计算层倾斜

数据倾斜调优

分布式计划

调优工具

SQL优化

第六章GaussDB数据库性能调优

无需使用IP，默认开通，华为云数据管理服务

DAS（data admin service）

同一vpc下，可连接

相同安全组下，默认互通，不同安全组下，需要设置安全组规则

内网连接

建议单独绑定弹性公网IP连接弹性云服务器

需要设置安全组规则，分为普通连接和ssl连接

公网连接

连接管理

gs_dump/gs_restore

数据库元数据导入\\导出（数据库对象）

copy

小批量数据导入导出

gsql元命令/copy

SQL文本格式建表定义

GDS

分布式大批量数据导出/导入

gs_dump postgres -U u1 -W Huawei@123 -p 16000 -s -t t1 -f /data/t1.sql –F c

-U 数据库用户名-W 数据库用户密码-p 数据库CN连接端口号-s 只导出对象定义，不导出对象数据-t 只导出指定的该表-f 将输出发送至指定文件-F 导出文件的格式，取值有四种：p 纯文本、c 自定义归档、d 目录归档和t tar归档格式

gs_dump

gs_dump postgres -p 16000 -s -f /data/all.sql -F c

导出

gs_restore -d db1 -p 16000 /data/all.sql

恢复

gs_restore

copy t1 from '/data/input/t1.txt' delimiter '^';

导入

copy t1 to '/data/input/t1_output.txt' delimiter '^';

copy (select * from t1 where a2=1) to '/data/input/t1_output.txt' delimiter '^';

查询导出

可在编程中使用

文本格式对象定义的创建

gsql -d db1 -p 16000 -U u1 -W Huawei@123 -f /data/table.sql-d 指定数据库名-p 数据库CN连接端口号-U 数据库用户名-W 数据库用户密码-f sql脚本文件\\o 把所有的查询结果发送到文件里\\i 从文件FILE中读取内容，并将其当作键盘输入，执行查询\\copy 进行数据的导入导出，数据来源支持STDIN（标准输入）和文件

Gauss Data Service

通过DN并行导入导出，解决CN在分布式场景下性能瓶颈问题

原理：1、CN只负责任务的规划和下发，导入交给DN节点，释放CN资源2、GDS负责文件的切分，然后分发给DN3、DN收到数据分片后，解析并计算属于哪个DN4、使用多DN并发，GDS多线程并行导入、多个GDS并行

error_ftr_sbtest2

Nodeid datanode编号Begintime 错误数据写入错误表的时间Filename 错误数据行所在文本名Rownum 错误数据行在文本的行数Rawrecord 错误原始数据行Detail 错误原因说明

invalid byte sequence for encoding “”UTF8“”: 0x00：文本数据中含有对UTF8来说非法字符的编码0x00

missing data for column “a2”：错误数据行缺失列

value too long for type character varying(10)：错误数据行的字段值超过表定义字段长度

常见错误

分析错误表

启动GDS服务

创建外表

insert into t1_foreign_output select * from t1;导出的文本命名格式为t1_foreign_output.dat.0

执行导入

数据倾斜校验，先导入一部分验证

拆分并行，使用多GDS并行导入

资源充足

万兆网

网络畅通

一个raid只部署1-2个GDS

GDS和DN的数据比例在1:3到1:6之间

部署个数

最佳实践

导入导出

防止误操作

高可靠，华为OBS可靠性12个9

好处

保留在数据库中，性能更高，空间利用率低

物理备份

通用格式（CSV），支持跨库或跨平台恢复

逻辑备份

分类

集群级，单对象/单表，多对象/多表，库级

备份范围

集群级、实例级、库级、表级

恢复范围

全量备份、增量备份、日志归档

备份完整性

全量恢复、增量恢复、PITR恢复

恢复完整性

本地磁盘，远端磁盘，NBU备份、OBS备份、SAN备份

存储介质

备份全量数据，耗时长，可恢复完整数据库

全量备份

差分备份，指定时间点后的增量修改数据，耗时短，无法单独恢复出数据库默认每30分钟自动备份

增量备份

创建的备份时段自动备份

指定的备份保留期自动备份

用户指定的任意备份时刻

扩容或者大版本升级自动备份

自动备份

是由用户启动的数据库实例的全量备份，会一直保存，直到用户手动删除

手动备份

执行

全量数据文件

截止一致性barrier点的增量日志

全量备份原理

增量数据页面

增量备份原理

1、恢复全量数据2、恢复增量备份1的增量页面3、恢复增量备份2的增量页面4、恢复增量备份2的日志5、启动集群

恢复原理

按照用户设置的自动备份策略对数据库实例进行备份以压缩包的形式存储在对象存储服务上建议业务低峰时间段启动

保留7天

全量备份时间段：间隔1小时的随机时间段

全量备份，一周的每一天

增量备份，默认每30分钟一次

默认策略

“实例管理”页面，选择指定的实例；在左侧导航栏，选择“备份恢复”；在操作页签选择“创建备份”。

恢复实例

1、登录console页面，获取备份下发的节点实例号

2、登录节点，目录/home/Ruby/log/request_agent.log找到对应时间段的日志和备份转发的节点ip，关键词：Send backup request

3、登录（第2步）备份转发节点，目录$GAUSSLOG/roach/controller，如无报错，查找目录/home/Ruby/log/om_agent/agent.log

4、如果是gs_roach程序执行失败，打开主节点目录$GAUSSLOG/roach/agent

5、登录第4步中获取的xxx节点，打开目录$GAUSSLOG/roach/agent下对应时间点的日志文件，排查原因

备份恢复日志查找顺序

1、集群状态unavailable导致

2、备份过程中，发生进程重启、主备切换

3、大版本升级中，全备和增备会失败

4、备份过程中，集群扩缩容并发，导致失败

故障定位

备份恢复

Point-in-time Recovery，按时间点恢复

PITR

Storage Area Network，计算机系统与存储单元之间以及存储单元与存储单元之间数据传输的网络

SAN

第七章GaussDB数据库日常运维

通过用户指定的用户名等信息登录到数据库环境中，获取当前正在运行的workload特征信息，根据上述特征信息生成参数推荐报告。报告当前数据库中不合理的参数配置和潜在风险等；输出根据当前正在运行的workload行为和特征；输出推荐的参数配置。该模式是秒级的，不涉及数据库的重启操作，其他模式可能需要反复重启数据库

recommend

通过用户提供的benchmark信息，不断地进行参数修改和benchmark的执行。通过反复的迭代过程，训练强化学习模型，以便用户在后面通过tune模式加载该模型进行调优

train

使用优化算法进行数据库参数的调优，当前支持两大类算法，一种是深度强化学习，另一种是全局搜索算法（全局优化算法）。深度强化学习模式要求先运行train模式，生成训练后的调优模型，而使用全局搜索算法则不需要提前进行训练，可以直接进行搜索调优

tune

调优程序X-Tuner包含三种运行模式

单表大小

分区表中，单个分区的大小

最大存储容量为32T的指标是

补充题目

gaussDB