【我和openGauss的故事】openGauss价值特性

openGauss是一款高性能、易运维的开源关系型数据库。openGauss提供了众多的价值特性，可以极大地帮助数据库开发和运维工程师方便地进行数据库管理和维护。本文将对openGauss所提供的plan hint、索引推荐、闪回恢复做一个简单的介绍。

Plan Hint调优

Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序、join、scan方法、指定结果行数等多个手段来进行执行计划的调优，以提升查询的性能。

支持范围

当前版本Plan Hint支持的范围如下，后续版本会进行增强。

指定Join顺序的Hint - leading hint
指定Join方式的Hint，仅支持除semi/anti join、unique plan之外的常用hint。
指定结果集行数的Hint
指定Scan方式的Hint，仅支持常用的tablescan、indexscan和indexonlyscan的hint。
指定子链接块名的Hint

注意事项

不支持Agg、Sort、Setop和Subplan的hint。
仅指定join顺序，不指定内外表顺序。
语法： leading(t1 t2 t3)
仅指定join顺序，指定内外表顺序。
语法： leading((t1 t2 t3)）

实践操作

1、预置数据如下：

 table: employee 职员表--==============================================================CREATE TABLE employee(empid int not null, --职员工号empname text not null, --职员姓名deptid int not null, --部门IDsalary int not null, --工资constraint pk_employee primary key (empid));
--==============================================================-- table: department 部门表--==============================================================CREATE TABLE department(deptid int not null, ---部门IDdeptname text not null, ---部门名称parentid int not null, --上级部门IDconstraint pk_department primary key (deptid));

2、测试：语法： leading((t1 t2 )）

openGauss=# explain select /*+ leading((department employee)) */ * from department join employee on employee.deptid = department.deptid;QUERY PLAN-------------------------------------------------------------------------------Hash Join (cost=69853.65..113081.05 rows=1462340 width=33)Hash Cond: (department.deptid = employee.deptid)-> Seq Scan on department (cost=0.00..164.00 rows=10000 width=17)-> Hash (cost=30153.40..30153.40 rows=1462340 width=16)-> Seq Scan on employee (cost=0.00..30153.40 rows=1462340 width=16)(5 rows)
Time: 1.059 msopenGauss=# explain select /*+ leading((employee department)) */ * from department join employee on employee.deptid = department.deptid;QUERY PLAN-----------------------------------------------------------------------------Hash Join (cost=289.00..50549.58 rows=1462340 width=33)Hash Cond: (employee.deptid = department.deptid)-> Seq Scan on employee (cost=0.00..30153.40 rows=1462340 width=16)-> Hash (cost=164.00..164.00 rows=10000 width=17)-> Seq Scan on department (cost=0.00..164.00 rows=10000 width=17)(5 rows)
Time: 0.856 ms

3、结果：改变内外表顺序，对SQL的执行还是由影响的

openGauss=# CREATE INDEX idx_empdepid on employee (deptid);
openGauss=# explain select /*+ leading((department employee)) */ * from department join employee on employee.deptid = department.deptid;QUERY PLAN-------------------------------------------------------------------------------Hash Join (cost=38030.00..52806.50 rows=1000000 width=33)Hash Cond: (department.deptid = employee.deptid)-> Seq Scan on department (cost=0.00..164.00 rows=10000 width=17)-> Hash (cost=25530.00..25530.00 rows=1000000 width=16)-> Seq Scan on employee (cost=0.00..25530.00 rows=1000000 width=16)(5 rows)
Time: 0.833 ms

结论

通过上述实践，在连接时，如果关联列没有索引，那么选择行数比较少的表做内表，可以提升查询效率；如果关联列有索引，选择建了索引的表做内表，可以提升查询效率。

索引推荐（Index-Advisor）

Index-Advisor共包含三个子功能，单查询索引推荐，虚拟索引和负载级别索引推荐。

单查询索引推荐

功能支持用户在数据库中直接进行操作，本功能基于查询语句的语义信息和数据库的统计信息，对用户输入的单条查询语句生成推荐的索引。本功能涉及的函数接口如下。

gs_index_advise() 功能：只针对单条的SQL语句

环境如下：

openGauss=# select count(*) from employee;count---------1000000(1 row)
-----------------------------------------------------------------------------
openGauss=# \d employee;Table "public.employee"Column | Type | Modifiers---------+---------+-----------empid | integer | not nullempname | text | not nulldeptid | integer | not nullsalary | integer | not nullIndexes:"idx_empdepid" btree (deptid) TABLESPACE pg_default
openGauss=# ANALYZE employee;ANALYZE

下面开始测试：

openGauss=# SELECT * FROM gs_index_advise('select * from employee where empid<1');schema | table | column | indextype--------+----------+--------+-----------public | employee | empid |(1 row)
openGauss=# SELECT * FROM gs_index_advise('select * from employee where empid>1');schema | table | column | indextype--------+----------+--------+-----------public | employee | |(1 row)

上述结果表明：当empid <1的时候，应在employee 表上的empid 列上创建索引。说明这个功能相当准确适用。

虚拟索引

虚拟索引功能支持用户在数据库中直接进行操作，本功能将模拟真实索引的建立，避免真实索引创建所需的时间和空间开销，用户基于虚拟索引，可通过优化器评估该索引对指定查询语句的代价影响。

命令：hypopg_create_index

下面开始测试：

openGauss=# select * from hypopg_create_index('create index on employee(empid)');indexrelid | indexname------------+-----------------------------32768 | <32768>btree_employee_empid(1 row)
openGauss=# explain select * from employee where empid=10;QUERY PLAN-------------------------------------------------------------Seq Scan on employee (cost=0.00..14973.40 rows=1 width=16)Filter: (empid = 10)(2 rows)

开启GUC参数后：

enable_hypo_index：该参数控制数据库的优化器进行EXPLAIN时是否考虑创建的虚拟索引。通过对特定的查询语句执行explain，用户可根据优化器给出的执行计划评估该索引是否能够提升该查询语句的执行效率。

openGauss=# set enable_hypo_index=on;SETopenGauss=# explain select * from employee where empid=10;
QUERY PLAN---------------------------------------------------------------------------------------------Index Scan using <32768>btree_employee_empid on employee (cost=0.00..8.27 rows=1 width=16)Index Cond: (empid = 10)(2 rows)

结论：通过对比二个执行计划，用户可以考虑是否需要创建对应的真实索引。

负载级别索引

对于workload级别的索引推荐，用户可通过运行数据库外的脚本使用此功能，本功能将包含有多条DML语句的workload作为输入，最终生成一批可对整体workload的执行表现进行优化的索引。同时，本功能提供从日志中抽取业务数据SQL流水的功能。

查询脚本位置：

[omm@huaweidb index_advisor]$ find /opt/huawei -name index_advisor_workload.py/opt/huawei/install/app_02c14696/bin/dbmind/components/index_advisor/index_advisor_workload.pycat yangkai.sqlselect distinct deptname from emp_order_insurance,department where department.deptid in(select deptid from employee where employee.empid=emp_order_insurance.empid);[omm@huaweidb index_advisor]$ python3 index_advisor_workload.py 15400 postgres yangkai.sql --schema public --max_index_num 10 --multi_iter_modePassword for database user:######################################################################################################################## Generate candidate indexes ########################################################################################################################No candidate indexes generated!############################################################################################################################## Created indexes ##############################################################################################################################public: CREATE UNIQUE INDEX pk_department ON department USING btree (deptid) TABLESPACE pg_default;public: CREATE INDEX idx_empdepid ON employee USING btree (deptid) TABLESPACE pg_default;public: CREATE UNIQUE INDEX employee_pkey ON employee USING btree (empid) TABLESPACE pg_default;public: CREATE UNIQUE INDEX pk_insurance ON insurance USING btree (insuranceid) TABLESPACE pg_default;##################################################################################################################### Current workload useless indexes #####################################################################################################################DROP INDEX idx_empdepid;############################################################################################################################# Redundant indexes #############################################################################################################################No redundant index!

闪回恢复

闪回概念本质是为了回退错误操作产生的，避免人为的“灾难”，并且要能够快速回退。

闪回恢复功能是数据库恢复技术的一环，可以有选择性的撤销一个已提交事务的影响，将数据从人为不正确的操作中进行恢复。在采用闪回技术之前，只能通过备份恢复、PITR等手段找回已提交的数据库修改，恢复时长需要数分钟甚至数小时。采用闪回技术后，恢复已提交的数据库修改前的数据，只需要秒级，而且恢复时间和数据库大小无关。

openGauss的闪回分为闪回查询、闪回表两类。

闪回查询

基于MVCC多版本的数据恢复：适用于误删除、误更新、误插入数据的查询和恢复，用户通过配置旧版本保留时间，并执行相应的查询或恢复命令，查询或恢复到指定的时间点或CSN点。

前提条件（下面三个缺一不可）

undo_retention_time：参数用于设置undo旧版本的保留时间。
undo_zone_count=16384 ---代表的时候undo log的一种资源个数
enable_default_ustore_table=on --默认指定用户创建表时使用USTORE存储引擎。

存储引擎:Ustore

Ustore存储引擎将最新版本的“有效数据”和历史版本的“垃圾数据”分离存储。将最新版本的“有效数据”存储在数据页面上，并单独开辟一段UNDO空间，用于统一管理历史版本的“垃圾数据”，因此数据空间不会由于频繁更新而膨胀，“垃圾数据”集中回收效率更高。

设置参数命令如下：

gs_guc set -N all -I all -c "undo_retention_time=2000s"gs_guc set -N all -I all -c "undo_zone_count=16384"gs_guc set -N all -I all -c "enable_default_ustore_table=on"

设置完重启数据库：

gs_om -t restart

重启后验证参数：

openGauss=# show undo_retention_time;undo_retention_time---------------------2000s(1 row)
openGauss=# show undo_zone_count;undo_zone_count-----------------16384(1 row)
openGauss=# show enable_default_ustore_table;enable_default_ustore_table-----------------------------on(1 row)
openGauss=#

下面开始演示：

创建表：

openGauss=# CREATE TABLE flashback_tab(id int not null,name text not null);openGauss-# CREATE TABLE

查看表存储引擎：

openGauss=# \d+ flashback_tab;Table "public.flashback_tab"Column | Type | Modifiers | Storage | Stats target | Description--------+---------+-----------+----------+--------------+-------------id | integer | not null | plain | |name | text | not null | extended | |Has OIDs: noOptions: orientation=row, compression=no, storage_type=USTORE, toast.storage_type=USTORE

插入数据：

insert into flashback_tab values (1,'ybj');

查询当前日期;

openGauss=# select current_timestamp;pg_systimestamp-------------------------------2022-09-29 01:13:32.691158+08(1 row)

查询数据：

openGauss=# select * from flashback_tab;id | name----+------1 | ybj(1 row)

插入数据：

openGauss=# insert into flashback_tab values (2,'yangkai');INSERT 0 1

查询结果：

openGauss=# select * from flashback_tab;id | name----+---------1 | ybj2 | yangkai(2 rows)

---基于timestamp的闪回查询：

openGauss=# SELECT * FROM flashback_tab TIMECAPSULE TIMESTAMP to_timestamp ('2022-09-29 01:13:32.691158', 'YYYY-MM-DD HH24:MI:SS.FF');id | name----+------1 | ybj(1 row)

---查询timestamp对应的CSN

openGauss=# select snptime,snpcsn from gs_txn_snapshot where snptime between '2022-09-29 01:13:32.691158' and ' 2022-09-29 01:15:24.921426'openGauss-# ;snptime | snpcsn-------------------------------+--------2022-09-29 01:13:32.841985+08 | 21122022-09-29 01:13:35.87922+08 | 21142022-09-29 01:13:38.924031+08 | 21162022-09-29 01:13:41.966247+08 | 21182022-09-29 01:13:45.013022+08 | 21202022-09-29 01:13:48.04741+08 | 21222022-09-29 01:13:51.078498+08 | 21242022-09-29 01:13:54.101686+08 | 21262022-09-29 01:13:57.123891+08 | 21282022-09-29 01:14:00.147156+08 | 21302022-09-29 01:14:03.169433+08 | 21322022-09-29 01:14:06.192879+08 | 21342022-09-29 01:14:09.216963+08 | 21362022-09-29 01:14:12.240249+08 | 21382022-09-29 01:14:15.26606+08 | 21402022-09-29 01:14:18.288409+08 | 21422022-09-29 01:14:21.309986+08 | 21442022-09-29 01:14:24.332801+08 | 21462022-09-29 01:14:27.378095+08 | 21482022-09-29 01:14:30.416234+08 | 21512022-09-29 01:14:33.460251+08 | 21532022-09-29 01:14:36.508431+08 | 2155

---基于CSN的闪回查询

openGauss=# SELECT * FROM flashback_tab TIMECAPSULE CSN 2116;id | name----+------1 | ybj(1 row)

闪回表

基于类似windows系统回收站的恢复：适用于误DROP、误TRUNCATE的表的恢复。用户通过配置回收站开关，并执行相应的恢复命令，可以将误DROP、误TRUNCATE的表找回。

前置条件：开启回收站、设置回收站对象保留时间

enable_recyclebin=on 启用回收站。

recyclebin_retention_time=30min 参数用于设置回收站对象保留时间，超过该时间的回收站对象将被自动清理。

命令如下：

 gs_guc set -N all -I all -c "enable_recyclebin=on"
 gs_guc set -N all -I all -c "recyclebin_retention_time=30min"

设置完重启数据库：

gs_om -t restart
[omm@huaweidb ~]$ gsql -d postgres -p 15400gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.
openGauss=# show recyclebin_retention_time;recyclebin_retention_time---------------------------30min(1 row)
openGauss=# show enable_recyclebin;enable_recyclebin-------------------on(1 row)
openGauss=#

下面开始操作演示：

[omm@huaweidb ~]$ gsql -d postgres -p 15400gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.

--查询当前表

openGauss=# select * from flashback_tab ;id | name----+---------1 | ybj2 | yangkai(2 rows)--truncate表openGauss=# truncate flashback_tab;TRUNCATE TABLEopenGauss=# select * from flashback_tab ;id | name----+------(0 rows)

---闪回表

openGauss=# timecapsule table flashback_tab to before truncate;TimeCapsule Table

--查询结果

openGauss=# select * from flashback_tab ;id | name----+---------1 | ybj2 | yangkai(2 rows)

--drop 表

openGauss=# drop table flashback_tab;DROP TABLEopenGauss=# select * from flashback_tab;ERROR: relation "flashback_tab" does not exist on dn_6001LINE 1: select * from flashback_tab;^

--查看回收站：

openGauss=# sELECT rcyname,rcyoriginname,rcytablespace FROM GS_RECYCLEBIN;rcyname | rcyoriginname | rcytablespace------------------------------+----------------------+---------------BIN$3C7C4EB8014$30BE34C8==$0 | pg_toast_32783_index | 0BIN$3C7C4EB8012$30BE3AA0==$0 | pg_toast_32783 | 0BIN$3C7C4EB800F$30BE40B0==$0 | flashback_tab | 0BIN$3C7C4EB800F$30BEA658==$0 | flashback_tab | 0BIN$3C7C4EB8014$30BEAF78==$0 | pg_toast_32783_index | 0BIN$3C7C4EB8012$30BEB560==$0 | pg_toast_32783 | 0(6 rows)

--通过回收站闪回表并命名flashback_yangkai;

openGauss=# timecapsule table flashback_tab to before drop rename to flashback_yangkai;TimeCapsule TableopenGauss=# select * from flashback_yangkai;id | name----+---------1 | ybj2 | yangkai(2 rows)

总结

openGauss闪回功能非常强大，基本可以满足日常运维需求，希望后期能够做出类似snapshot standby等功能就更完美了。

本文分享自微信公众号 - openGauss（openGauss）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

关于作者

作者的专辑

作者的其它热门文章

热门资讯

热门软件

OSCHINA 社区

在线工具

攻略

公众号

视频号

问题反馈