第5章


openGauss查询优化



随着数据和用户体量的快速增长，传统的数据库面临多方面难以解决的性能优化问题，比如如何按需配置数据库参数、如何准确估计查询基数、如何选择物理执行计划、如何自动诊断造成性能瓶颈的查询语句等。针对以上问题，本章首先介绍查询优化的整体流程(5.1节)； 然后分别从查询命令和人工智能算法两个方面提供性能优化能力： 一方面，openGauss提供SQL执行信息的查询接口(如解释命令(EXPLAIN)、分析命令(ANALYZE))和SQL物理优化接口(如HINT)，方便数据库用户手动分析和优化查询语句(5.2~5.4节)； 另一方面，openGauss内置了一些智能调优算法，利用人工智能算法(如卷积网络、循环神经网络)高维拟合和自学习等能力，支撑特定场景下的性能优化，
如自动参数优化、查询性能预测、索引推荐(5.5~5.7节)等。

5.1查询优化




由于一条SQL语句可能对应多种等价的执行计划，而且计划的执行效率受到数据库多方面组件(如优化器参数、并发负载)的影响，openGauss主要从两个方面提供查询优化能力。一方面，openGauss通过提供执行信息的接口(如解释命令、分析命令、提示命令)，方便用户和数据库运维人员查看物理计划、估计的执行效率和真实的执行效率，并对物理连接顺序、连接类型进行指定； 另一方面，openGauss利用人工智能技术优化数据库的性能，从而获得更好的执行表现，包括自动参数优化、查询性能预测和索引推荐。如表51所示，首先，openGauss根据数据库历史负载和当前状态推荐合适的参数(5.5节)； 其次，opengGauss在数据库运行中，通过预测未来负载的执行时间，决定是否执行查询诊断和修复等功能(5.6节)； 最后，针对执行效率较低的查询或整个负载，openGauss可以利用强化学习等算法自动推荐合适的索引(见5.7节)。


表51查询优化功能



查询优化方式功能功 能 描 述

查询优化接口

解释命令打印出执行计划和估计的执行开销
分析命令打印出执行计划和实际的执行时间
提示命令指定物理执行逻辑，包括算子类型、连接顺序等

智能查询优化

参数调优利用多种统计和强化学习算法自动调整参数配置
性能预测利用深度图嵌入等算法估计查询的执行时间
索引推荐利用爬山法、强化学习等算法推荐索引


5.2查询解释命令




查询解释命令(EXPLAIN)是提供查询优化信息的第一环。对于复杂、执行效率低的SQL语句，数据库使用者通常会利用查询解释命令打印出的执行计划分析SQL语句。openGauss在传统PostgreSQL 查询解释命令的语法基础上做了进一步扩充。本节将从功能描述、语法格式、参数说明和具体案例四个方面详细介绍查询解释命令的使用。


openGauss数据库实战指南
第5章openGauss查询优化
5.2.1功能描述
EXPLAIN用于显示查询语句的执行计划。执行计划将显示查询语句所引用的表会采用什么样的扫描方式，如
简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的连接算法。执行计划
最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长
时间。若指定分析(ANALYZE)选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内
的时间总开销(毫秒为单位)和实际返回的总行数。这对判断计划生成器的估计是否接近现实非常有用。
5.2.2语法格式
EXPLAIN字段的基本语法用于显示查询语句的执行计划，支持多种选项，对选项顺序无要求，格式如下： 



EXPLAIN ［ ( option ［, ...］ ) ］ statement;






其中选项option字段可以通过指定参数查看物理执行计划、执行开销、调试状态等多种信息，语法如下： 



ANALYZE ［ boolean ］ |

ANALYSE ［ boolean ］ |

VERBOSE ［ boolean ］ |

COSTS ［ boolean ］ |

CPU ［ boolean ］ |

DETAIL ［ boolean ］ |

NODES ［ boolean ］ |

NUM_NODES ［ boolean ］ |

BUFFERS ［ boolean ］ |

TIMING ［ boolean ］ |

PLAN ［ boolean ］ |

FORMAT { TEXT | XML | JSON | YAML }






其中，当需要显示SQL语句的执行计划时，要按顺序给出选项： 



EXPLAIN { ［ { ANALYZE | ANALYSE } ］ ［ VERBOSE ］ | PERFORMANCE } statement;






在指定ANALYZE选项时，语句会被执行。如果用户想使用解释语句分析插入(INSERT)、更新(UPDATE)、删除(DELETE)、创建新表(CREATE TABLE AS)或执行(EXECUTE)语句，而不想改动数据(执行这些语句会影响数据)，请
采用下面这种方法： 



START TRANSACTION;

EXPLAIN ANALYZE ...;

ROLLBACK;






5.2.3参数说明
5.2.2节举例介绍了ANALYZE、VERBOSE等参数在openGauss中的应用。
表52给出openGauss数据库中解释命令支持的所有参数。


表52openGauss数据库中解释命令支持的所有参数



参数名含义

STATEMENT指定要分析的查询语句
ANALYZE boolean | ANALYSE boolean显示实际运行时间和其他统计数据
VERBOSE boolean显示有关计划的额外信息
COSTS boolean包括每个规划节点的估计总成本，以及估计的行数和每行的宽度
CPU boolean打印CPU的使用情况的信息
DETAIL boolean打印数据库节点上的信息
NODES boolean打印查询执行的节点信息
NUM_NODES boolean打印执行中的节点的个数信息
BUFFERS boolean包括缓冲区的使用情况的信息
TIMING boolean
包括实际的启动时间和花费在输出节点上的时间信息
PLAN是否将执行计划存储在plan_table中。当该选项开启时，会将执行计划存储在PLAN_TABLE中，不打印到当前屏幕，因此该选项为on时，不能与其他选项同时使用
FORMAT指定输出格式： TEXT(默认值)、XML、JSON和YAML

5.2.4示例
本节给出一个openGauss中使用EXPLAIN解释查询语句的示例。
(1)  创建一个表tpcds.customer_address_p1： 



opengauss=# CREATE TABLE tpcds.customer_address_p1 AS TABLE tpcds.customer_address;






(2)  修改explain_perf_mode为normal： 



opengauss=# SET explain_perf_mode=normal;






(3)  显示简单查询的执行计划： 



opengauss=# EXPLAIN SELECT * FROM tpcds.customer_address_p1;

QUERY PLAN

--------------------------------------------------

Data Node Scan (cost=0.00..0.00 rows=0 width=0)

Node/s: All dbnodes

(2 rows)






(4)  以JSON格式输出的执行计划(explain_perf_mode为normal时)： 



opengauss=# EXPLAIN(FORMAT JSON) SELECT * FROM tpcds.customer_address_p1;

QUERY PLAN

----------------------------------------------------------------------

［ +

{ +

"Plan": { +

"Node Type": "Data Node Scan",+

"Startup Cost": 0.00, +

"Total Cost": 0.00, +

"Plan Rows": 0, +

"Plan Width": 0,+

"Node/s": "All dbnodes" +

} +

} +

］

(1 row)






(5)  如果有一个索引，使用一个带索引条件(WHERE)的查询时，可能会显示一个不同的计划： 



opengauss=# EXPLAIN SELECT * FROM tpcds.customer_address_p1 WHERE ca_address_sk=10000;

QUERY PLAN

----------------------------------------------------------------------

Data Node Scan (cost=0.00..0.00 rows=0 width=0)

Node/s: dn_6005_6006

(2 rows)






(6)  以YAML格式输出的执行计划(explain_perf_mode为normal时)： 



opengauss=# EXPLAIN(FORMAT YAML) SELECT * FROM tpcds.customer_address_p1 WHERE ca_address_sk=10000;









QUERY PLAN

---------------------------------

- Plan:+

Node Type: "Data Node Scan"+

Startup Cost: 0.00 +

Total Cost: 0.00 +

Plan Rows: 0 +

Plan Width: 0+

Node/s: "dn_6005_6006"

(1 row)






(7)  禁止开销估计的执行计划： 



opengauss=# EXPLAIN(COSTS FALSE)SELECT * FROM tpcds.customer_address_p1 WHERE ca_address_sk=10000;

QUERY PLAN

----------------------------------------------------------------------

Data Node Scan

Node/s: dn_6005_6006

(2 rows)






(8)  带有聚集函数查询的执行计划： 



opengauss=# EXPLAIN SELECT SUM(ca_address_sk) FROM tpcds.customer_address_p1 WHERE ca_address_sk<10000;

QUERY PLAN

----------------------------------------------------------------------

Aggregate(cost=18.19..14.32 rows=1 width=4)

->Streaming (type: GATHER)(cost=18.19..14.32 rows=3 width=4)

Node/s: All dbnodes

->Aggregate(cost=14.19..14.20 rows=3 width=4)

->Seq Scan on customer_address_p1(cost=0.00..14.18 rows=10 width=4)

Filter: (ca_address_sk < 10000)

(6 rows)






(9)  删除表tpcds.customer_address_p1： 



opengauss=# DROP TABLE tpcds.customer_address_p1;







5.3查询分析命令




查询分析命令(ANALYZE)本质上是一个查询性能分析工具，可以详细
显示出查询语句执行过程中，比如计划在哪儿花费了多少时间。它会做出查询计划，并且会实际执行查询计划，以测量
查询计划中各个关键点的实际指标，例如耗时、条数，最后详细
打印出来，方便用户分析执行表现和慢查询的原因。本节分别从功能描述、语法格式
和具体案例3个方面详细介绍查询分析命令的使用。
5.3.1功能描述
ANALYZE收集与数据库中普通表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据，以确定最有效的执行计划。如果没有指定参数，ANALYZE会分析当前数据库中的每个表和分区表。同时也可以通过指定表名、列名和分区名参数把分析限定在特定的表、列或分区表中。ANALYZE|ANALYSE VERIFY用于检测数据库中普通表(行存储、列存储)的数据文件是否损坏。
5.3.2语法格式
ANALYZE方便用户分步解析一条查询语句在执行过程中的表信息、分区表信息、列信息等统计数据。
(1) 如果用户需要收集表的统计信息，则执行如下命令： 



{ ANALYZE | ANALYSE } ［ VERBOSE ］

［ table_name ［ ( column_name ［, ...］ ) ］ ］;






(2) 如果用户需要收集分区表的统计信息，则执行如下命令： 



{ ANALYZE | ANALYSE } ［ VERBOSE ］

［ table_name ［ ( column_name ［, ...］ ) ］ ］

PARTITION ( patrition_name ) ;






普通分区表目前支持针对某个分区的统计信息的语法，但功能上不支持针对某个分区的统计信息收集。
(3) 如果用户需要收集多列统计信息，则执行如下命令： 



{ANALYZE | ANALYSE} ［ VERBOSE ］

table_name (( column_1_name, column_2_name ［, ...］ ));






收集多列统计信息时，请设置GUC参数default_statistics_target为负数，以使用百分比采样方式。此外，每组多列统计信息最多支持32列，而且目前openGauss不支持收集多列统计信息的表。
(4) 当用户需要检测当前库的数据文件时，执行如下命令： 



{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE};






执行检测数据文件的命令时，需要注意以下几点： 
①  支持对全库进行操作，由于涉及的表较多，建议以重定向保存结果，命令如下：



gsql -d database -p port -f "verify.sql"> verify_warning.txt 2>&1






②  对外提示(NOTICE)只核对外可见的表，内部表的检测会包含在它所依赖的外部表中，不对外显示和呈现。

③  命令的可容错级别的处理。由于调试版本的断言(Assert)可能导致core无法继续执行命令，建议在发布模式(Release Mode)下操作。全库操作时，若关键系统表出现损坏，则直接报错，不再继续执行。

④ ANALYZE参数非临时表不能在一个匿名块、事务块、函数或存储过程内被执行。支持存储过程中分析临时表，不支持统计信息回滚操作。ANALYZE VERIFY 操作处理的大多为异常场景检测，需要使用发布版本。ANALYZE VERIFY 场景不触发远程读，因此远程读参数不生效。关键系统表出现错误被系统检测出页面损坏时，将直接报错，不再继续检测。
(5) 当需要检测表和索引的数据文件时，执行以下命令： 



{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE} table_name|index_name ［CASCADE］;






支持对普通表的操作和对索引表的操作，但不支持对索引表索引使用瀑布操作(CASCADE Operation)，原因是瀑布模式用于处理主表的所有索引表，当单独对索引表进行检测时，无须使用瀑布模式(CASCADE Mode)。

此外，对主表进行检测会同步检测主表的内部表，例如toast表、cudesc表等。当提示索引表损坏时，建议使用重新索引(Reindex)命令进行重建索引操作。
(6) 如果需要检测分区表的数据文件，则执行以下命令： 



{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE} table_name PARTITION {(patrition_name)}［CASCADE］;






该语法支持对表的单独分区进行检测操作，但不支持对索引表index使用CASCADE操作。
5.3.3示例

本节给出一个openGauss中使用ANALYZE解释查询语句的示例。
(1) 创建表： 



opengauss=# CREATE TABLE customer_info

(

WR_RETURNED_DATE_SK INTEGER ,

WR_RETURNED_TIME_SK INTEGER ,

WR_ITEM_SKINTEGER NOT NULL,

WR_REFUNDED_CUSTOMER_SK INTEGER);






(2) 创建分区表： 



opengauss=# CREATE TABLE customer_par

(

WR_RETURNED_DATE_SK INTEGER ,

WR_RETURNED_TIME_SK INTEGER ,

WR_ITEM_SKINTEGER NOT NULL,

WR_REFUNDED_CUSTOMER_SK INTEGER

)

PARTITION BY RANGE(WR_RETURNED_DATE_SK)

(

PARTITION P1 VALUES LESS THAN(2452275),

PARTITION P2 VALUES LESS THAN(2452640),

PARTITION P3 VALUES LESS THAN(2453000),

PARTITION P4 VALUES LESS THAN(MAXVALUE)

)

ENABLE ROW MOVEMENT;






(3) 使用ANALYZE语句更新统计信息： 



opengauss=# ANALYZE customer_info;

opengauss=# ANALYZE customer_par;






(4) 使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息： 



opengauss=# ANALYZE VERBOSE customer_info;

INFO:analyzing "cstore.pg_delta_3394584009"(cn_5002 pid=53078)

INFO:analyzing "public.customer_info"(cn_5002 pid=53078)

INFO:analyzing "public.customer_info" inheritance tree(cn_5002 pid=53078)

ANALYZE






若环境有故障，需查看数据库主节点的log。
(5) 删除表，恢复原始实验环境： 



opengauss=# DROP TABLE customer_info;

opengauss=# DROP TABLE customer_par;







5.4优化提示命令




优化提示命令(HINT)可以帮助用户对查询语句的执行计划进行“提示”
，比如改变部分表的执行顺序、连接方式等。
5.4.1功能描述
HINT为用户提供了直接影响执行计划生成的手段。SQL HINT影响执行计划的生成、
SQL查询性能的提升。用户可以通过指定连接顺序连接流处理、表扫描等方法，或者指定结果行数等多个手段进行执行计划的调优，以提升查询的性能。
5.4.2连接顺序提示
连接顺序提示用于指明连接的顺序，包括不指定内外表顺序和指定内外表顺序。
(1) 仅指定连接顺序，不指定内外表顺序： 



leading(join_table_list)






(2) 同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效： 



leading((join_table_list))






其中，join_table_list为表示表连接顺序的提示字符串，可以包含当前层的任意个表(别名)，或对于子查询提升的场景，也可以包含子查询的提示别名，同时任意表可以使用括号指定优先级，表之间使用空格分隔。注意：  表只能用单个字符串表示，不能带schema； 表如果存在别名，需要优先使用别名表示该表。
此外，join table list中指定的表需要满足以下要求，否则会报语义错误。
(1) 表必须在当前层或提升的子查询中存在。
(2) 表在当前层或提升的子查询中必须是唯一的。如果不唯一，需要使用不同的别名进行区分。
(3) 同一个表只能在list里出现一次。

(4) 如果表存在别名，则list中的表需要使用别名。例如，leading(t1 t2 t3 t4 t5)表示t1，t2，t3，t4，t5先join，
5个表的join顺序及内外表不限。leading((t1 t2 t3 t4 t5))表示： t1和t2先join，t2做内表； 再和t3 join，t3做内表； 再和t4 join，t4做内表； 再和t5 join，t5做内表。leading(t1 (t2 t3 t4) t5)表示： t2，t3，t4先join，内外表不限； 再和t1，t5 join，内外表不限。leading((t1 (t2 t3 t4) t5))表示： t2，t3，t4先join，内外表不限； 在最外层，t1再和t2，t3，t4的join表join，t1为外表，再和t5 join，t5为内表。leading((t1 (t2 t3) t4 t5)) leading((t3 t2))表示： t2，t3先join，t2做内表； 然后再和t1 join，t2，t3的join表做内表； 
之后依次跟t4，t5做join，t4，t5做内表。对示例中的原语句使用如下的HINT： 



explain

select /*+ leading((((((store_sales store) promotion) item) customer) ad2) store_returns) leading((store store_sales))*/ i_product_name product_name ...






该提示命令表示表之间的join关系是： store_sales和store先join，store_sales做内表，然后依次
与promotion，item，customer，ad2，store_returns做join。生成的计划如图51所示。


图51包括顺序提示的执行计划


5.4.3连接方式提示
连接方式提示用于选择Join使用的方法，包括Nested Loop、Hash Join和Merge Join，命令语法如下： 



［no］ nestloop|hashjoin|mergejoin(table_list)






(1) no表示HINT的join方式不使用。例如，no nestloop(t1 t2 t3)表示生成t1，t2，t3三表连接计划时，不使用nestloop。

(2) table_list表示HINT表集合的字符串，该字符串中的表与join_table_list相同，只是中间不允许出现括号指定join的优先级。

(3) 三表连接计划可能是t2，t3先join，再与t1 join，或t1，t2先join，再与t3 join。
对于多表连接，HINT只能指定最后一次join使用的连接方式，对于两表连接的方法不HINT。如果需要，可以单独指定。
例如，任意表均不允许nestloop连接，且希望t2，t3先join，则增加HINT： no nestloop(t2 t3)。示例如下： 



explain

select /*+ nestloop(store_sales store_returns item) */ i_product_name product_name ...






该HINT表示表之间的join关系是： store_sales和store先join，store_sales做内表，然后依次与promotion，item，customer，ad2，store_returns做join。
5.4.4行数方式提示
行数方式提示用于指明中间结果集的大小，支持绝对值和相对值的HINT： 



rows(table_list #|+|-|* const)






(1)  #，+，-，*为进行行数估算HINT的四种操作符号。#表示直接使用后面的行数进行HINT。+，-，*表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为1行。table_list为HINT对应的单表或多表join结果集，与Join方式
HINT中的table_list相同。

(2) const可以是任意非负数，支持科学计数法。例如，rows(t1 #5)表示
指定t1表的结果集为5行。rows(t1 t2 t3 *1000)表示指定t1，t2，t3 join完的结果集的行数乘以1000。推荐使用两个表*的HINT。对于两个表的采用*操作符的HINT，只要两个表出现在join的两端，都会触发HINT。例如，设置HINT为rows(t1 t2 * 3)，对于(t1 t3 t4)和(t2 t5 t6)join时，由于t1和t2出现在join的两端，所以其join的结果集也会应用该HINT规则乘以3。rows HINT支持在单表、多表、function table及subquery scan table的结果集上指定HINT。对示例中的原语句使用如下的HINT： 



explain

select /*+ rows(store_sales store_returns *50) */ i_product_name product_name ...






该HINT表示表之间的join关系是： store_sales和store先join，store_sales做内表，然后依次与promotion，item，customer，ad2，store_returns做join。
5.4.5提示命令的错误、冲突及告警

提示命令的结果会体现在计划的变化上，可以通过解释语句(EXPLAIN)查看变化。提示中的错误不会影响语句的执行，只是不能生效，该错误会根据语句类型以不同方式提示用户。对于解释语句，提示命令的错误会以警告(warning)形式显示在界面上； 
对于非解释语句，提示命令的错误会以调试级别日志显示在日志中，关键字为PLANHINT。HINT的错误分为以下6种类型。
(1) 语法错误： 语法规则树归约失败，会报错，指出出错的位置。例如，HINT关键字错误，leading HINT或join HINT指定2个表以下，其他HINT未指定表等。一旦发现语法错误，则立即终止HINT的解析，所以此时只有错误前面的解析完的HINT有效，
例如下面的命令： 



leading((t1 t2)) nestloop(t1) rows(t1 t2 #10)






若nestloop(t1)存在语法错误，则终止解析，可用提示命令只有之前解析的leading((t1 t2))。

(2) 语义错误： 表不存在，存在多个，或在leading或join中出现多次，均会报语义错误。scanHINT中的index不存在，会报语义错误。另外，如果子查询提升后，同一层出现多个名称相同的表，且其中某个表需要被HINT，HINT会存在歧义，无法使用，需要为相同表增加别名规避。

(3) 提示命令重复或冲突： 如果存在提示命令重复或冲突，则只有第一个提示命令生效，其他提示命令均会失效。提示重复是指
提示命令的方法及表名均相同。例如，nestloop(t1 t2) nestloop(t1 t2)。提示冲突是指
table list一样的提示命令，存在不一样的提示，提示的冲突仅对于每一类提示方法检测冲突。例如
，nestloop (t1 t2) hashjoin (t1 t2)，则后面与前面冲突，此时hashjoin的提示失效。注意，nestloop(t1 t2)和no mergejoin(t1 t2)不冲突。leading HINT中的多个表会进行拆解。例如，leading ((t1 t2 t3))会拆解成leading((t1 t2)) leading(((t1 t2) t3))，此时如果存在leading((t2 t1))，则两者冲突，后面的会被丢弃。(例外： 指定内外表的HINT若与不指定内外表的HINT重复，则始终丢弃不指定内外表的HINT。)

(4) 子链接提升后HINT失效： 子链接提升后的提示失效，会给出提示。
这种情况通常出现在子链接中存在多个表链接的场景中。提升后，子链接中的多个表不再作为一个整体出现在
链接中。

(5) 列类型不支持重分布： 对于skew HINT来说，目的是进行重分布时的调优，所以当提示列的类型不支持重分布时，提示命令将无效。

(6) 提示未被使用： 非等值join使用hashjoin HINT或mergejoin HINT； 不包含索引的表使用indexscan HINT或indexonlyscan HINT。通常，只有在索引列上使用过滤条件才会生成相应的索引路径，全表扫描将不会使用索引，因此使用indexscan HINT或indexonlyscan HINT将不会使用索引。indexonlyscan只有输出列仅包含索引列时才会使用，否则指定时
仅尝试有等值连接条件的表连接，此时没有关联条件的表之间的路径将不会生成，所以指定相应的leading，join，rows HINT将不使用。例如，t1，t2，t3表join，t1和t2，t2和t3有等值连接条件，则t1和t3不会优先连接，leading(t1 t3)不会被使用。

5.5自动参数优化




5.5.1工作原理

传统的数据库调优通常依靠雇佣专家(DBA)完成： 专家针对指定的负载，在线下反复进行瓶颈检测、参数调整和性能对比，直至达到满意效果，这是一项非常耗时的工作，而且严重依赖专家自身的经验和知识。此外，在负载动态变化的场景下，要面临更加繁多的数据库状态和负载类型，极大地增加了这项工作的难度。虽然目前有一些基于学习的调优方法，它们由于没有充分利用系统查询负载的信息，而不具备动态适应负载变化的能力。此外，它们直接利用现有的机器学习模型，如高斯过程等，缺少针对调参问题的特征融合模型，提高调参的表现和泛化能力。

为了解决以上挑战，openGauss提出了一种基于学习的数据库自动调优工具XTuner。它是一款数据库自带的参数调优工具，通过结合深度强化学习和启发式算法，实现在无须人工干预的情况下，获取最佳数据库参数的途径。
其核心思想是： 基于深度强化学习算法学习在不同的环境条件下推荐参数的策略。需要注意的是，调优程序是一个独立于数据库内核之外的工具，需要提供数据库及其所在实例的用户名和登录密码信息，以便控制数据库执行基准测试集(Benchmark)进行性能测试； 在启动调优程序前，要求用户测试环境交互正常，能够正常跑通基准测试集测试脚本、能够正常连接数据库。
5.5.2实验部署
启动调优程序之前，可以通过如下命令获取帮助信息，相关参数见表53。



python main.py --help







表53相关参数



参数参 数 说 明取 值 范 围

mode，m指定调优程序运行的模式train，tune
configfile，f调优程序的配置文件，可选—
dbname指定调优的数据库—
dbuser指定调优的数据库用户名—
port数据库的监听端口—
host数据库实例的宿主机IP—
hostuser数据库安装时的DBA用户名—
hostsshport数据库实例所在宿主机的SSH端口号，可选—
scenario指定调优的模式，对应3种不同的调优列表，用户可以对该调优列表进行修改ap，htap，tp
benchmark由用户指定的benchmark脚本文件名—
modelpath调优强化学习模型存储或加载的文件路径—
version，v返回当前工具的版本号—

部署XTuner工具的步骤如下： 
(1) 用户进行数据库安全配置，并验证调优程序所在客户机能够正常访问到数据库实例所在的服务器。
(2) 用户向数据库实例导入数据(如TPCC、TPCH)，
根据调优程序给出的示例代码编写符合自己实际业务的基准测试集(脚本路径在基准测试集目录中)，手动验证基准测试集可以正常跑通并可获得稳定的测试结果，记录下此时的测试结果，以方便后续对比调优效果。
(3) 用户在确保数据库运行正常并在无其他人使用时
备份现有参数，修改调优参数列表配置文件(文件路径在参数目录中，默认配置文件是knobs_htap.py)，设定需要调整的参数及其范围。
(4) 用户输入数据库链接信息，选择当前调优模式为“训练”或“调优”，启动参数调优程序。例如，在XTuner根目录中输入如下命令： 



python main.py -m train -db-name opengauss＼

-db-user dba -port 1234 ＼

-host 192.168.1.2 -host-user opengauss＼

-benchmark tpcc -model-path mymodel






(5) 若为“训练”模式，则输出训练后的模型，程序退出； 若为“调优”模式，则输出调优后的最优参数列表，程序退出。用户通过对比调优结果，自行判断是否应该设置为该参数，并手动设置为推荐参数或重置为调优前的参数。

5.6查询性能预测




查询性能预测(Predictor)主要预测查询的执行时间，包括工具原理和实验部署两部分。
5.6.1工作原理
查询性能预测是基于机器学习且具有在线学习能力的查询时间预测工具，通过不断学习数据库内收集的历史执行信息，实现计划的执行时间预测功能。本特性需要拉起进程AiEngine，用于模型的训练和推理。首先，为了保证openGauss处于正常状态，用户通过身份验证成功登录openGauss； 用户执行的SQL语法正确无报错，且不会导致数据库异常等； 历史性能数据窗口内openGauss并发量稳定，表结构、表数量不变，数据量无突变，涉及查询性能的guc参数不变； 进行预测时，需要保证模型已训练并收敛； AiEngine运行环境稳定。相关接口见表54。


表54对外接口



RequestAPI功能RequestAPI功能

/check检查模型是否被正常拉起/track_process查看模型训练日志
/configure设置模型参数/setup加载历史模型
/train模型训练/predict模型预测

AiEngine进程与内核进程发送请求进行通信，请求样例如下： 



curl -X POST -d '{"modelName":"modelname"}' -H 'Content-Type: application/json' 'https://IP-address:port/request-API'






使用此功能前，需使用openssl工具生成通信双方认证所需的证书，保证通信安全。
(1) 搭建证书生成环境，证书文件的保存路径为$GAUSSHOME/CA。复制证书生成脚本及相关文件： 



cp path_to_predictor/install/ssl.sh $GAUSSHOME/

cp path_to_predictor/install/ca_ext.txt $GAUSSHOME/






(2) 复制配置文件openssl.cnf到$GAUSSHOME路径下： 



cp $GAUSSHOME/share/om/openssl.cnf $GAUSSHOME/






(3) 修改openssl.conf配置参数： 



dir = $GAUSSHOME/CA/demoCA

default_md = sha256






至此，通信证书生成环境的准备工作完成。
(4) 生成证书及密钥： 



cd $GAUSSHOME ; sh ssl.sh






(5) 根据提示设置密码。要求密码至少包含3种不同类型的字符，长度至少为8位。



Please enter your password:






(6) 根据提示输入选项： 



Certificate Details:

Serial Number: 1 (0x1)

Validity

Not Before: May 15 08:32:44 2020 GMT

Not After : May 15 08:32:44 2021 GMT

Subject:

countryName = CN

stateOrProvinceName = SZ

organizationName= HW

organizationalUnitName= GS

commonName= CA

X509v3 extensions:

X509v3 Basic Constraints:

CA:TRUE

Certificate is to be certified until May 15 08:32:44 2021 GMT (365 days)

Sign the certificate? ［y/n］:y

1 out of 1 certificate requests certified, commit? ［y/n］y






(7) 输入拉起AiEngine的IP地址，如IP为127.0.0.1。



Please enter your aiEngine IP: 127.0.0.1






(8) 根据提示输入选项： 



Certificate Details:

Serial Number: 2 (0x2)

Validity









Not Before: May 15 08:38:07 2020 GMT

Not After : May 13 08:38:07 2030 GMT

Subject:

countryName = CN

stateOrProvinceName = SZ

organizationName= HW

organizationalUnitName= GS

commonName= 127.0.0.1

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Certificate is to be certified until May 13 08:38:07 2030 GMT (3650 days)

Sign the certificate? ［y/n］:y 1 out of 1 certificate requests certified, commit? ［y/n］y






(9) 输入启动openGauss的IP地址，如IP为127.0.0.1。



Please enter your gaussdb IP: 127.0.0.1

Serial Number: 3 (0x3)

Validity

Not Before: May 15 08:41:46 2020 GMT

Not After : May 13 08:41:46 2030 GMT

Subject:

countryName = CN

stateOrProvinceName = SZ

organizationName= HW

organizationalUnitName= GS

commonName= 127.0.0.1

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Certificate is to be certified until May 13 08:41:46 2030 GMT (3650 days)

Sign the certificate? ［y/n］:y

1 out of 1 certificate requests certified, commit? ［y/n］y






5.6.2实验部署
部署Predictor工具的步骤如下： 
(1) 打开数据收集，设置ActiveSQL operator信息相关参数： 



enable_resource_track=on

resource_track_level=operator






(2) 关闭数据收集，设置ActiveSQL operator信息相关参数： 



enable_resource_track=off

resource_track_level=query






(3) 执行业务查询语句，等待3min后查看当前节点上的数据。



select * from gs_wlm_plan_operator_info;






(4) 数据持久化保存，设置ActiveSQL operator信息相关参数。



enable_resource_track=on

resource_track_level=operator

enable_resource_record=on

resource_track_duration=0(默认值为60s)

resource_track_cost=10(默认值为100000)






(5) 执行业务查询语句，等待3min后查看当前节点上的数据： 



select * from gs_wlm_plan_operator_info;






1.  模型管理(系统管理员用户)
模型管理是指对训练好的机器学习或强化学习模型的存储、调度及使用策略。模型管理操作需要在数据库正常的状态下进行。
(1) 新增加一个机器学习模型： 



INSERT INTO gs_opt_model values('rlstm', 'model_name', 'datname', '127.0.0.1', 5000, 2000, 1, -1,
 64, 512, 0 , false, false, '{S, T}', '{0,0}', '{0,0}', 'Text');






(2) 修改一个机器学习模型的超参数或数据库统计参数： 



UPDATE gs_opt_model SET <attribute> = <value> WHERE model_name = <target_model_name>;






(3) 删除一个机器学习模型： 



DELETE FROM gs_opt_model WHERE model_name = <target_model_name>;






(4) 查询现有机器学习模型及其工作状态： 



SELECT * FROM gs_opt_model;






2. 模型训练(系统管理员用户)
模型训练是指当要处理新的业务场景或者机器学习表现变差时，系统管理人员需要训练现有模型，以满足用户需求。
(1) 配置/添加模型训练参数： 参考模型管理(系统管理员用户)进行模型添加、模型参数修改，来指定训练参数。



INSERT INTO gs_opt_model values('rlstm', 'default', 'opengauss', '127.0.0.1', 5000, 2000, 1, -1, 64, 512, 0 , false, false, '{S, T}', '{0,0}', '{0,0}', 'Text');






(2) 训练参数更新。



UPDATE gs_opt_model SET <attribute> = <value> WHERE model_name = <target_model_name>;






(3) 前提条件为数据库状态正常且历史数据正常收集时，删除原有的encoding数据。



DELETE FROM gs_wlm_plan_encoding_table;






(4) 进行数据编码，指定数据库名。



SELECT gather_encoding_info('opengauss');






(5) 开始模型训练。



SELECT model_train_opt('rlstm', 'default');






(6) 查看模型训练状态，返回TensorBoard工具(见图52)所用URL。



SELECT * FROM track_model_train_opt('rlstm', 'default');








图52TensorBoard工具所用URL


(7) 打开URL查看模型训练状态，返回TensorBoard可视化训练界面。
3. 模型预测(系统管理员用户|普通用户)
模型预测功能需在数据库状态正常、指定模型已被训练且收敛的条件下进行。目前，模型训练参数的标签设置中需要包含
S标签，解释命令中才可显示ptime预测值。例如： 



INSERT INTO gs_opt_model values(
'rlstm'，'default'，'opengauss'，'127.0.0.1'，5000，1000，1，-1，50，500，0 ，false，false，'{S，T}'，'{0，0}'，'{0，0}'，'Text');






(1) 调用解释命令接口： 



explain (analyze on, predictor <model_name>)






(2) 预期结果： 



Row Adapter (cost=110481.35..110481.35 rows=100 p-time=99..182 width=100) (actual time=375.158..375.160 rows=2 loops=1)






(3) 检查AiEngine是否可连接： 



opengauss=# select check_engine_status('aiEngine-ip-address',running-port);






(4) 查看模型对应日志在AiEngine侧的保存路径： 



opengauss=# select track_model_train_opt('template_name', 'model_name');







5.7索引推荐




本节介绍索引推荐(IndexAdvisor)的功能，共包含3个子功能： 单查询索引推荐、虚拟索引和负载级别索引推荐。
5.7.1单查询索引推荐
单查询索引推荐功能支持用户在数据库中直接进行操作，本功能基于查询语句的语义信息和数据库的统计信息，对用户输入的单条查询语句生成推荐的索引。本功能涉及的函数接口见表55。


表55函数接口



函数参数功能
gs_index_advise()SQL语句针对单条SQL生成推荐索引

使用上述函数，获取针对该查询生成的推荐索引，推荐结果由索引的表名和列名组成。例如： 



postgres=> select * from gs_index_advise('SELECT c_discount from bmsql_customer where c_w_id = 10');

table|column

-------------+----------

bmsql_customer | (c_w_id)

(1 row)






上述结果表明： 应在 bmsql_customer 的 c_w_id 列上创建索引。例如，可以通过下述SQL语句创建索引： 



CREATE INDEX idx on bmsql_customer(c_w_id);






某些查询语句也可能被推荐创建联合索引，例如： 



postgres=# select * from gs_index_advise('select name, age, sex from t1 where age >= 18 and age < 35 and sex = ''f'';');









table | column

-------+------------

t1| (age, sex)

(1 row)






上述语句表明，应该在表t1上创建一个联合索引 (age，sex)。可以通过下述命令创建： 



CREATE INDEX idx1 on t1(age, sex);






5.7.2虚拟索引

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


表56虚拟索引功能的接口



函数名参数功能

hypopg_create_index创建索引语句的字符串创建虚拟索引
hypopg_display_index无显示所有创建的虚拟索引信息
hypopg_drop_index索引的oid删除指定的虚拟索引
hypopg_reset_index无清除所有虚拟索引
hypopg_estimate_size索引的oid估计创建指定索引所需的空间大小

使用函数hypopg_create_index()创建虚拟索引。例如： 



postgres=> select * from hypopg_create_index('create index on bmsql_customer(c_w_id)');

indexrelid |indexname

------------+-------------------------------------

329726 | <329726>btree_bmsql_customer_c_w_id

(1 row)






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



postgres=> set enable_hypo_index = on;

SET






开启GUC参数前，执行EXPLAIN+查询语句： 



postgres=> explain SELECT c_discount from bmsql_customer where c_w_id = 10;

QUERY PLAN









----------------------------------------------------------------------

Seq Scan on bmsql_customer(cost=0.00..52963.06 rows=31224 width=4)

Filter: (c_w_id = 10)

(2 rows)






开启GUC参数后，执行EXPLAIN + 查询语句： 



postgres=> explain SELECT c_discount from bmsql_customer where c_w_id = 10;

QUERY PLAN

----------------------------------------------------------------------

［Bypass］

Index Scan using <329726>btree_bmsql_customer_c_w_id on bmsql_customer(cost=0.00..39678.69 rows=31224 width=4)

Index Cond: (c_w_id = 10)

(3 rows)






通过对比两个执行计划可以观察到，该索引预计会降低指定查询语句的执行代价，用户可考虑创建对应的真实索引。使用函数hypopg_display_index()可展示所有创建过的虚拟索引。例如： 



postgres=> select * from hypopg_display_index();

indexname| indexrelid| table|column

----------------------------------+---------+------------+----------

<329726>btree_bmsql_customer_c_w_id|329726 | bmsql_customer | (c_w_id)

<329729>btree_bmsql_customer_c_d_id_c_w_id |329729 | bmsql_customer | (c_d_id, c_w_id)

(2 rows)






使用函数hypopg_estimate_size()可估计创建虚拟索引所需的空间大小(单位： 字节)。例如： 



postgres=> select * from hypopg_estimate_size(329730);

hypopg_estimate_size

----------------------

15687680

(1 row)






删除虚拟索引。使用函数hypopg_drop_index()删除指定oid的虚拟索引。例如： 



postgres=> select * from hypopg_drop_index(329726);

hypopg_drop_index

-------------------

t

(1 row)






使用函数hypopg_reset_index()一次性清除创建的所有虚拟索引。例如： 



postgres=> select * from hypopg_reset_index();

hypopg_reset_index

--------------------



(1 row)






5.7.3负载级别索引推荐
对于负载级别索引推荐，用户可通过运行数据库外的脚本使用此功能。本功能将包含有多条DML语句的workload作为输入，最终生成一批可对整体workload的执行表现进行优化的索引。
首先，在构建负载级别索引之前，数据库需要满足3个前提： 
(1) 数据库状态正常，客户端能够正常连接。
(2) 当前执行用户下安装有gsql工具，该工具路径已被加入PATH环境变量中。
(3) 具备Python 3.6+的环境。
其次，准备好包含有多条DML语句的文件作为输入的workload，文件中每条语句占据一行。用户可从数据库的离线日志中获得历史的业务语句。运行Python脚本index_advisor_workload.py，命令如下： 



python index_advisor_workload.py ［p PORT］ ［d DATABASE］ ［f FILE］ ［--h HOST］ ［-U USERNAME］ 
［-W PASSWORD］

［--max_index_num MAX_INDEX_NUM］ ［--multi_iter_mode］






其中输入参数依次为： PORT，连接数据库的端口号； DATABASE，连接数据库的名字； FILE，包含workload语句的文件路径； HOST(可选)，连接数据库的主机号； USERNAME(可选)，连接数据库的用户名； PASSWORD(可选)，连接数据库用户的密码； MAX_INDEX_NUM(可选)，最大的推荐索引数目； multi_iter_mode(可选)，算法模式，可通过是否设置该参数来切换算法。例如： 



python index_advisor_workload.py 6001 postgres tpcc_log.txt --max_index_num 10 --multi_iter_mode






推荐结果为一批索引，以多个创建索引语句的格式显示在屏幕上，示例结果如下。



create index ind0 on bmsql_stock(s_i_id,s_w_id);

create index ind1 on bmsql_customer(c_w_id,c_id,c_d_id);

create index ind2 on bmsql_order_line(ol_w_id,ol_o_id,ol_d_id);

create index ind3 on bmsql_item(i_id);









create index ind4 on bmsql_oorder(o_w_id,o_id,o_d_id);

create index ind5 on bmsql_new_order(no_w_id,no_d_id,no_o_id);

create index ind6 on bmsql_customer(c_w_id,c_d_id,c_last,c_first);

create index ind7 on bmsql_new_order(no_w_id);

create index ind8 on bmsql_oorder(o_w_id,o_c_id,o_d_id);

create index ind9 on bmsql_district(d_w_id);







5.8小结




本章概要介绍了openGauss性能优化的相关功能，包括查询解释、查询分析、参数配置、查询性能预测等。面对大规模的数据体量和多样的用户负载，首先，5.2节和5.3节分别介绍了查询解释
命令和查询分析命令的功能和用法； 然后，为了进一步解决数据库配置导致的查询效率低下问题，
5.5节介绍了自动参数优化工具的功能和使用方法； 最后，为了预判查询的执行效率，
5.6节介绍了openGauss内置的查询性能预测工具的基本原理和使用方法。

5.9习题




1. 以下哪种方式不属于解释命令的功能？（）

A. 扫描方式B. 连接方式
C. 数据存储的分区号D. 预测的执行开销
2. (多选题)以下属于分析命令的功能有（）。
A. 收集表的统计信息B. 检测数据文件是否损坏
C. 打印缓存区的使用情况D. 更改连接方式
3. 如果需要提高代价估计的准确度，则使用（）优化提示功能。
A. 连接顺序提示B. 连接方式提示
C. 中间行数提示D. 最终行数提示
4. 以下不属于优化提示命令的功能有（）。
A. 连接顺序提示B. 连接方式提示
C. 中间行数提示D. 最终行数提示
5. (多选题)以下有关查询优化命令的说法，不正确的是()。
A. 提示命令的结果不可以通过explain查看
B. openGauss可以执行两条冲突的优化提示命令
C. explain可以获得计划的实际执行信息
D. 如果表的分布列与连接列相同，就不会生成重分布计划(redistribute)

6. (多选题)以下有关参数调优的说法，不正确的是()。
A. openGauss支持基于搜索的参数调优算法
B. 传统的数据库调优通常依靠自动化算法独立完成
C. openGauss内置的深度调优模型不需要训练
D. openGauss的调优模块需要和用户定期交互
7. (多选题)以下有关查询时间预测的说法，不正确的是()。
A. 并发和串行场景下，同一个查询的执行时间总是一样的
B. 查询的执行时间和数据库参数无关
C. 预测的查询时间可以用于负载调度、性能监控等模块
D. 预测的查询时间可以作为实际执行时间