DATABASE
在GaussDB(DWS)中，database是对业务的物理隔离，不同database的之间的对象不能相互访问。比如在databaseA中无法访问databse B中的对象。因此登录集群的时候必须显示指定要连接的databse。
在GaussDB(DWS)中创建database时，需要重点关注字符集编码(ENCODING)和兼容性(DBCOMPATIBILITY)两个配置项。ENCODING指明了数据库存储的数据的编码格式，为了适应全球化，创建DATABASE的时候建议使用UTF-8编码。DBCOMPATIBILITY 指明了DATABASE的兼容性选项，GaussDB(DWS)支持Oracle、Teradata和MySQL三种兼容模式，分别兼容Oracle、Teradata和MySQL语法；若不指定DBCOMPATIBILITY，则默认为ORA。需要注意的是, 数据库一旦创建，这两个属性就不能修改，甚至语法层就没有提供修改这两个属性的接口
SCHEMA
在GaussDB(DWS)中，schema是DATABASE下一个特殊的对象，实现对数据库对象的逻辑隔离，从功能上类似于一些编程语言中namespace的概念。同一个schema下，不能存在同名的数据库对象；但是不同scheam下的对象名可以重复。
SEARCHPATH
search_path又称之为模式搜索路径，本身是一个guc参数。对于未定义schema的对象，会根据search_path的配置赋予默认的schema或者默认的schema搜索范围
1）创建对象时，会在search_path指定的schema列表中的第一个schema下创建对象
USER
查询对象时，如果对象没有显式指明schema，GaussDB(DWS)会按照search_path中指明的schema列表，顺序查找指定名称的对象。如果遍历完所有的schema都没有查找到同名对象，数据库会直接报错。
系统表
pg_users
pg_database
pg_tables
pg_tablespace
pg_statistic
在PG_TABLES系统表中查看public Schema中包含的前缀为search_table的表
gaussdb=#
SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public' AND TABLENAME LIKE 'search_table%';
查看和停止正在运行的查询语句
通过视图PG_STAT_ACTIVITY可以查看正在运行的查询语句。方法如下：
设置参数track_activities为on。
SET track_activities = on;
当此参数为on时，数据库系统才会收集当前活动查询的运行信息。
查看正在运行的查询语句。以查看正在运行的查询语句所连接的数据库名、执行查询的用户、查询状态及查询对应的PID为例：
SELECT datname, usename, state,pid FROM pg_stat_activity;
如果state字段显示为idle，则表明此连接处于空闲，等待用户输入命令。
如果仅需要查看非空闲的查询语句，则使用如下命令查看：
SELECT datname, usename, state, pid FROM pg_stat_activity WHERE state != 'idle';
若需要取消运行时间过长的查询，通过PG_TERMINATE_BACKEND函数，根据线程ID（即2中查询结果的pid字段）结束会话。
SELECT PG_TERMINATE_BACKEND(12800);
查看最耗时SQL
SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity where state != 'idle' ORDER BY 1 desc;
使用gsql的\d+命令查询表的属性
gaussdb=# \d+ customer_t1;
执行如下命令将搜索路径设置为myschema、public，首先搜索myschema。
gaussdb=#
SET SEARCH_PATH TO myschema, public;
撤销PUBLIC在public模式下创建对象的权限，下面语句中第一个“public”是模式，第二个“PUBLIC”指的是所有角色。
gaussdb=#
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
执行如下命令查询系统和用户定义的所有索引。
gaussdb=#
SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i';
更新统计信息
ANALYZE tablename; --更新单个表的统计信息
ANALYZE; --更新全库的统计信息
表的选择
1.行存储
默认创建表的类型。数据按行进行存储，即一行数据是连续存储。适用于对数据需要经常更新的场景
gaussdb=# CREATE TABLE customer_t1
(
state_ID CHAR(2),
state_NAME VARCHAR2(40),
area_ID NUMBER
);
--删除表
gaussdb=# DROP TABLE customer_t1;
2列存储
数据按列进行存储，即一列所有数据是连续存储的。单列查询IO小，比行存表占用更少的存储空间。适合数据批量插入、更新较少和以查询为主统计分析类的场景。列存表不适合点查询。
gaussdb=# CREATE TABLE customer_t2
(
state_ID CHAR(2),
state_NAME VARCHAR2(40),
area_ID NUMBER
)
WITH (ORIENTATION = COLUMN);
--删除表
gaussdb=# DROP TABLE customer_t2;
3行存表和列存表的选择
更新频繁程度
数据如果频繁更新，选择行存表。
插入频繁程度
频繁的少量插入，选择行存表。一次插入大批量数据，选择列存表。
表的列数
表的列数很多，选择列存表。
查询的列数
如果每次查询时，只涉及了表的少数（<50%总列数）几个列，选择列存表。
压缩率
列存表比行存表压缩率高。但高压缩率会消耗更多的CPU资源。
表设计
在分布式框架下，数据分布在各个DN上。一个或者几个DN的数据存在一块物理存储设备上，好的表定义至少需要达到以下几个目标：
表数据均匀分布在各个DN上，以防止单个DN对应的存储设备空间不足造成集群有效容量下降。选择合适分布列，避免数据分布倾斜可以实现该点。
1.表Scan压力均匀分散在各个DN上，以避免单DN的Scan压力过大，形成Scan的单节点瓶颈。
2.分布列不选择基表上等值filter中的列可以实现该点。
3.减少扫描数据数据量。通过分区的剪枝机制可以实现该点。
4.尽量极少随机IO。通过聚簇/局部聚簇可以实现该点。
5.尽量避免数据shuffle，减小网络压力。通过选择join-condition或者group by列为分布列可以更好的实现这点。
1.选择存储模型
表的存储模型选择是表定义的第一步。客户业务属性是表的存储模型的决定性因素，依据下面表格选择适合当前业务的存储模型。
| 存储模型 | 适用场景 |
|---|---|
| 行存 | 点查询(返回记录少，基于索引的简单查询)。增删改比较多的场景 |
| 列存 | 统计分析类查询 (group , join多的场景) |
2.选择分布方式
复制表（Replication）方式将表中的全量数据在集群的每一个DN实例上保留一份。主要适用于记录集较小的表。这种存储方式的优点是每个DN上都有该表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment(每个plan segment都会起对应的线程)；缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。
哈希（Hash）表将表中某一个或几个字段进行hash运算后，生成对应的hash值，根据DN实例与哈希值的映射关系获得该元组的目标存储位置。对于Hash分布表，在读/写数据时可以利用各个节点的IO资源，大大提升表的读/写速度。一般情况下大表定义为Hash表。
范围（Range）和列表（List）分布是由用户自定义的分布策略，根据分布列的取值落入满足一定范围或者具体值的对应目标DN，这两种分布方式便于用户灵活地进行数据管理，但对用户本身的数据抽象能力有一定的要求。
复制表和哈希表
3.选择分布列
Hash分布表的分布列选取至关重要，需要满足以下原则：
1.列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。
2.在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’)，那么就应当尽量不用zqdh做分布列。
3.在满足前两条原则的情况，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。
对于Hash分表策略，如果分布列选择不当，可能导致数据倾斜，查询时出现部分DN的I/O短板，从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性
select
xc_node_id, count(1)
from tablename
group by xc_node_id
order by xc_node_id desc;
其中xc_node_id对应DN，一般来说，不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列。
GaussDB支持多分布列特性，可以更好地满足数据分布的均匀性要求。
4.使用局部聚簇
局部聚簇（Partial Cluster Key）是列存下的一种技术。这种技术可以通过min/max稀疏索引较快的实现基表扫描的filter过滤。Partial Cluster Key可以指定多列，但是一般不建议超过2列
5.使用分区表
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点：
- 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
- 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
- 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
GaussDB支持的分区表为范围分区表。
范围分区表： 将数据基于范围映射到每一个分区。这个范围是由创建分区表时指定的分区键决定的。分区键经常采用日期，例如将销售数据按照月份进行分区
6.选择数据类型
高效数据类型，主要包括以下三方面：
- 尽量使用执行效率比较高的数据类型
一般来说整型数据运算(包括=、＞、＜、≧、≦、≠等常规的比较运算，以及group by)的效率比字符串、浮点数要高。比如某客户场景中对列存表进行点查询，filter条件在一个numeric列上，执行时间为10+s；修改numeric为int类型之后，执行时间缩短为1.8s左右。 - 尽量使用短字段的数据类型
长度较短的数据类型不仅可以减小数据文件的大小，提升IO性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint。 - 使用一致的数据类型
表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销
创建和管理分区表
GaussDB Kernel数据库支持的分区表为范围分区表，列表分区表，哈希分区表。
范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。
列表分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。
哈希分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
分区表和普通表相比具有以下优点：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。
普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表
创建和管理索引
索引可以提高数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询的条件或者被要求排序的字段来确定是否建立索引。
索引建立在数据库表中的某些列上。因此，在创建索引时，应该仔细考虑在哪些列上创建索引。
在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。
在经常使用连接的列上创建索引，可以加快连接的速度。
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
在经常使用WHERE子句的列上创建索引，加快条件的判断速度。
为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。
优化
1.批量更新或删除数据后，会在数据文件中产生大量的删除标记，查询过程中标记删除的数据也是需要扫描的。故多次批量更新/删除后，标记删除的数据量过大会严重影响查询的性能。建议在批量更新/删除业务会反复执行的场景下，定期执行VACUUM FULL以保持查询性能。
query执行流程
下图描述了GaussDB的SQL引擎从接收客户端SQL语句到执行SQL语句需要经历的关键步骤，以及各个流程中可能对执行产生影响的因素
- 词法&语法解析
按照约定的SQL语句规则，把输入的SQL语句从字符串转化为格式化结构(Stmt)，如果SQL语句存在语法错误，都会在这个环节报错。
- 语义解析
语义解析类似一个翻译器，把外部输入的可视化的对象翻译为数据库内部可识别的对象（比如把Stmt中以字符串记录的表名称转化为数据库内部可识别的oid），如果语句存在语义错误(比如查询的表对象不存在)，数据库会在这个环节报错。
- 查询重写
根据规则将“语义解析”的输出等价转化为执行上更为优化的结构，比如把查询语句中的视图逐层展开至最低层的表查询。
- 查询优化
数据库确认SQL执行方式、生成执行计划的过程
- 查询执行
根据执行计划执行SQL并输出结果的过程
整个执行流程中，优化器决定了查询语句的具体执行方式，对SQL语句的性能起着关键性的作用。数据库查询优化器分为两类：基于规则的优化器(Rule-Based Optimizer，RBO) 和基于代价的优化器(Cost-Based Optimizer，CBO)。RBO是一种基于规则的优化，对于指定的场景采用指定的执行方式，这种优化模型对数据不敏感；SQL的写法往往会影响执行计划，不了解RBO的细则的人员开发的SQL性能不可控，因此RBO逐渐被抛弃，目前GaussDB等数据库厂商的优化器都是CBO模型。CBO模型是根据SQL语句生成一组可能被使用的执行计划，并估算出每种执行计划的代价，最终选择选择一个代价最小的执行方式。
CBO模型
数据库执行SQL语句的时候，会把执行拆分为若干步骤，如下SQL
select *
from t1 join t2 on t1.a=t2.b
where t1.b = 2 and t2.a = 3;
在具体执行的时候会拆分为表扫描和表关联两个主要查询动作。这两个查询动作都存在多种执行方式，比如表扫描均存在SeqScan、IndexScan、IndexOnlyScan、BitmapScan等多种执行方式、表关联存在NestLoop、HashJoin、MergeJoin三种执行方式，那么在具体的业务场景下什么样的查询动作才是代价最小的执行方式，这就是优化器的核心工作。
CBO主要工作原理是通过代价模型(Cost Model)和统计信息估算每种执行方式的代价，然后选择一种执行代价最优的执行方式。这里面代价模型是核心算法逻辑，统计信息是cost计算的数据源，二者配合完成cost计算；如果统计信息缺失，计算时代价模型会使用默认值来计算cost，当然这时cost会跟真实值存在较大偏差，大概率会出现选择非最优执行计划的情况，因此统计信息是CBO模型中 cost计算的数据输入，是CBO最核心的科技之一。
统计信息
表规模信息
系统表pg_class中的reltuples和relpages两个字段能够反映表规模信息信息，其中relpages记录了表数据存储到几个page页里面，主要用于表从存储接口扫描数据的代价计算；reltuples记录了表记录条数，主要用于扫描结果集行数估算。
查询pg_class中的表规模估算信息，显示表为2400
select reltuples,relpages from pg_class
单列统计信息
单列统计信息是指表的单列的数据特征信息，存储在系统表pg_statistic中。因为pg_statistic会存储一些关键采样值来描述数据特征，因此pg_statistic数据是敏感的，只有超级用户才可以访问pg_statistic。通常我们推荐用户使用查询系统视图pg_stats来查询当前用户有查询权限的表的统计信息，同时pg_stats信息的可读性更强，pg_stats字段信息如下
| 名称 | 描述 |
|---|---|
| schemaname | 统计对象所在的namespace名 |
| tablename | 统计对象名 |
| attname | 统计列的名称 |
| inherited | 如果为真，那么统计分析时采样样本包括继承表数据 |
| null_frac | 该字段NULL值的个数比率 |
| avg_width | 该字段非NULL值的平均字节宽度 |
| n_distinct | 字段中非NULL值的distinct值。如果大于0，则表示实际distinct值个数；如果小于0，则它的绝对值表示distinct值占全部非NULL值个数比例。例如-1表示distinct值的数目与行数相同 |
| n_dndistinct | 第一个DN上该字段非NULL值的distinct值，取值含义与n_distinct一样 |
| most_common_vals | 高频非空值按照出现的频次排序的列表，列表中的值我们一般简称为MCV值 |
| most_common_freqs | 对应每个MCV值出现的频率列表，列表中的每个值表示对应的MCV值出现的次数与表的总记录数(包含NULL值)的比例 |
| histogram_bounds | 去除NULL值和most_common_vals之外的其它值按照’<’操作符排序，然后按照个数等分的边界值。如果此字段的数据类型没有<操作符或取值都在most_common_vals中出现过，则这个字段为NULL |
| correlation | 字段值的物理行序和按照’<’排序的逻辑行序的相关性，我们一般称之为排序相关性，取值范围为-1到+1；数据越按照’<’操作符排序，取值越接近1；数据越按照’>’操作符排序，取值越接近-1。取值越接近于-1或者+1，说明索引扫描时引入的随机IO开销越小，索引扫描的随机IO的cost值也越小。如果字段数据类型没有<操作符，则这个字段为NULL。 |
| most_common_elems | 数组类型的最常用的非空元素的列表，类似most_common_vals，但记录的不是字段值，而是构成数组字段的元素 |
| most_common_elem_freqs | most_common_elems中每个元素出现的频次与该字段非NULL值的记录数的比例，同时还在字段尾部依次追加了元素的最小值、最大值、NULL值个数的比例，所以此字段元素的个数总是比most_common_elems元素的个数多3个 |
| elem_count_histogram | 数组类型非NULL distinct值的直方图信息，末尾为平均唯一值个数 |
select * from pg_statistic where schema = 'pubic'
and tablename = 'test' and attname = 'a'
扩展信息统计
通过统计新可以看出public.test的a列的NULL值比例为0，存在120个distinct值， 120是MCV值，每个出现的概率是0.0254167；211200出现在在直方图统计信息中；
以查询语句“SELECT count(1) FROM public.test WHERE a < 44;”为例说明统计信息在优化过程中行数估算场景下的作用
a) 所有MCV值均满足a < 44，所有MCV值的比例为0.0254167 * 20 = 0.5083340
b) 44为直方图中第三个边界，直方图中满足a < 44的值的比例为(1-0.5083340)/100 *(3-1)= .0098333200
那么表中满足a<56的tuples的个数为1243.6015680 ≈1244，通过explain打印执行计划如下
扩展统计信息
扩展统计信息存储在系统表pg_statistic_ext里面，当前只支持多列统计信息这一种扩展统计信息类型。pg_statistic_ext会存储一些关键采样值来描述数据特征，因此pg_statistic_ext数据是敏感的，只有超级用户才可以访问pg_statistic_ext，通常我们推荐用户使用查询系统视图pg_ext_stats来查询当前用户有查询权限的扩展统计信息。
| 名称 | 描述 |
|---|---|
| schemaname | 统计对象所在的namespace名 |
| tablename | 统计对象名 |
| attname | 扩展统计信息涉及列编号的数组 |
| inherited | 如果为真，那么统计分析时采样样本包括继承表数据 |
| null_frac | 该字段组合中所有字段均为NULL值的个数比率 |
| avg_width | 该字段组合的平均字节宽度 |
| n_distinct | 字段中非NULL值的distinct值。大于零的数值是多字段组合的不同值的实际数，小于零是多字段组合的distinct值占全部非NULL值个数比例的负数 |
| n_dndistinct | 第一个DN上的n_distinct值 |
| most_common_vals | 字段组合里最常用数值的列表，此字段要求多列的每个列都不存在NULL值 |
| most_common_freqs | most_common_vals中每个值出现的频率列表，列表中的每个元素描述了most_common_vals中对应值出现的次数与表的总记录数(包含NULL值)的比例 |
| most_common_vals_null | 高频非空值按照出现的频次排序的列表，此字段要求多列中的至少1列包含NULL值，但又不全部是NULL值 |
| most_common_freqs_null | most_common_vals_null中每个值出现的频率列表 |
表的多个列有相关性且查询中有同时基于这些列的过滤条件、关联条件或者分组操作的时候，可尝试收集多列统计信息。扩展统计信息需要手动进行收集(具体收集方法，下个小节会介绍)，如下为test表(a,b)两列的统计信息
如何生成统计信息
4.1 显式收集统计信息
4.1.1 单列统计信息
通过如下命令收集单列统计信息：
{ ANALYZE | ANALYSE } [ VERBOSE ] [ table_name [ ( column_name [, …] ) ] ];
如语法描述，我们支持对指定列做统计信息，但是实际上我们很难统计实际业务SQL中到底使用了当前哪些表的列进行了代价估算，因此建议通常情况下对全表收集统计信息。
4.1.2 扩展统计信息
通过如下命令收集多列统计信息：
{ANALYZE | ANALYSE} [ VERBOSE ] table_name (( column_1_name, column_2_name [, …] ));
需要注意的是，当前只支持在百分比采样模式下生成扩展统计信息，因此在收集扩展统计信息之前请确保GUC参数default_statistics_target为负数
4.2 提升统计信息质量
analyze是按照随机采样算法从表上采样，根据样本计算表数据特征。采样数可以通过配置参数default_statistics_target进行控制，default_statistics_target取值范围为-100~10000，默认值为100。
1） 当default_statistics_target > 0时；采样的样本数为300*default_statistics_target，default_statistics_target取值越大，采样的样本也越大，样本占用的内存空间也越大，统计信息计算耗时也越长
2） 当default_statistics_target < 0时，采样的样本数为 (default_statistics_target)/100*表的总行数，default_statistics_target取值越小，采样的样本也越大。但是default_statistics_target < 0时会把采样数据下盘，不存在样本占用的内存空间的问题，但是因为样本过大，计算耗时长的问题同样存在
default_statistics_target < 0时，实际采样数是(default_statistics_target)/100*表的总行，所以我们又称之为百分比采样。
4.3 自动收集统计信息
当配置参数autoanalyze打开时，查询语句走到优化器发现表不存在统计信息，会自动触发统计信息收集，以满足优化器的需求。以文档的case为列
注：只有对统计信息敏感的复杂查询动作(多表关联等操作)的SQL语句执行时才会触发自动收集统计信息；简单查询(比如单点，单表聚合等) 不会触发自动收集统计信息
什么时候收集统计信息
5.1 大规模数据变化
大规模数据导入/UPDATE/DELETE等操作，会导致表数据行数变化，新增的大量数据也会导致数据特征发生大的变化，此时需要对表重新收集统计信息
5.2 查询新增数据
常见于业务表新增数据查询场景，这个也是收集业务中最常见、最隐蔽的统计信息没有及时更新的问题，这种场景最主要的特征如下
-
存在一个按照时间增长的业务表 -
业务表每天入库新一天的数据 -
数据入库之后查询新增数据进行数据加工分析
在最后步骤的数据加工分析时，最长的方法就是使用Filter条件从分区表中筛选数据，如passtime > ‘2020-01-19 00:00:00’ AND pastime < ‘2020-01-20 00:00:00’，假如新增数据入库之后没有做analyze，优化器发现Filter条件中的passtime取值范围超过了统计信息中记录的passtime值的上边界，会把估算满足passtime > ‘2020-01-19 00:00:00’ AND pastime < ‘2020-01-20 00:00:00’的tuple个数为1条，导致估算行数验证失真
WHO：谁来收集统计信息
AP场景下业务表数据量一般都很大，单次导入的数据量也比较大，而且经常是数据导入即用，因此建议在业务开发过程中，根据数据变化量和查询特征在需要的地方主动对相关表做analyze。
1914
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
