openGauss学习笔记-296 openGauss AI特性-AI4DB数据库自治运维-DBMind的AI子功能-Index-advisor：索引推荐-workload级别索引推荐296.1 前提条件296.2 业务数据抽取296.2.1 日志中的SQL采集296.2.2 系统表中的SQL采集296.3 索引推荐脚本使用步骤

openGauss学习笔记-296 openGauss AI特性-AI4DB数据库自治运维-DBMind的AI子功能-Index-advisor：索引推荐-workload级别索引推荐

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

296.1 前提条件

• 数据库状态正常、客户端能够正常连接。

• 当前执行用户下安装有gsql工具，该工具路径已被加入到PATH环境变量中。

296.2 业务数据抽取

296.2.1 日志中的SQL采集

1. 设置相关GUC参数：log_min_duration_statement = 0log_statement= 'all'

2. 执行根据日志抽取SQL语句的功能，命令如下：

   gs_dbmind component extract_log [l LOG_DIRECTORY] [f OUTPUT_FILE] [p LOG_LINE_PREFIX] [-d DATABASE] [-U USERNAME][--start_time] [--sql_amount] [--statement] [--json] [--max_reserved_period] [--max_template_num]

   其中的输入参数依次为：

   使用示例：

   gs_dbmind component extract_log $GAUSSLOG/pg_log/dn_6001 sql_log.txt '%m %c %d %p %a %x %n %e' -d postgres -U omm --start_time '2021-07-06 00:00:00' --statement

   说明：

   若指定-d/-U参数，日志打印每条日志信息的前缀格式需包含%d、%u，若需要抽取事务，必须指定%p，详见log_line_prefix参数。max_template_num参数设置建议不超5000条，避免workload索引推荐执行时间过长。

• LOG_DIRECTORY：pg_log的存放目录。

• OUTPUT_PATH：输出SQL流水文件的保存路径，即抽取出的业务数据存放的文件路径。

• LOG_LINE_PREFIX：指定每条日志信息的前缀格式。

• DATABASE：（可选）数据库名称，不指定默认所有数据库。

• USERNAME：（可选）用户名称，不指定默认所有用户。

• start_time：（可选）日志收集的开始时间，不指定默认所有文件。

• sql_amount：（可选）收集SQL数量的最大值，不指定默认收集所有SQL。

• statement：（可选）表示收集pg_log日志中statement标识开头的SQL，不指定默认不收集。

• json：（可选）指定收集日志的文件存储格式为SQL归一化后的json，不指定默认格式每条SQL占一行。

• max_reserved_period：（可选）指定json模式下，增量收集日志中保留的模板的最大的更新时长，不指定默认都保留，单位/天。

• max_template_num：（可选）指定json模式下保留的最大模板数量，不指定默认都保留。

296.2.2 系统表中的SQL采集

执行系统表抽取SQL语句功能，命令如下：

echo PASSWORD | gs_dbmind component fetch_statement [DB_PORT] [DATABASE] [OUTPUT] [--db-host DB_HOST] [-U DB_USER] [--schema SCHEMA]
[--statement-type {asp,slow,history,activity}] [--start-time STAET_TIME] [--end-time END_TIME] [--verify] [--driver]

其中的输入参数依次为：

• DB_PORT：连接数据库的端口号。

• DATABASE：连接数据库的名字。

• OUTPUT：包含SQL流水的输出文件。

• DB_HOST：（可选）连接数据库的主机名。

• DB_USER：（可选）连接数据库的用户名，用户需具有sysadmin或monitor admin权限。

• SCHEMA：模式名称，仅在statement-type为history时使用，默认为public。

• statement-type：SQL流水类型，分为asp,slow,history,activity四种:

• asp：从gs_asp中提取SQL流水，需保证开启GUC参数enable_asp

• slow：指定抽取当前活跃的慢SQL

• history：指定抽取历史慢SQL

• activity：指定抽取当前活跃SQL

• START_TIME：起始时间，仅在statement-type为asp时使用，且为必选。

• END_TIME：结束时间，仅在statement-type为asp时使用，且为必选。

• verify：是否校验SQL合法性。

• driver：是否使用python驱动器连接数据库，默认gsql连接。

296.3 索引推荐脚本使用步骤

1. 准备好包含有多条DML语句的文件作为输入的workload，文件中每条语句占据一行。用户可从数据库的离线日志中获得历史的业务语句。

2. 运行本功能，命令如下：

   echo PASSWORD | gs_dbmind component index_advisor [p DB_PORT] [d DATABASE] [f FILE] [--h DB_HOST] [-U DB_USER] [--schema SCHEMA]
   [--max_index_num MAX_INDEX_NUM][--max_index_storage MAX_INDEX_STORAGE] [--multi_iter_mode] [--max-n-distinct MAX_N_DISTINCT]
   [--min-improved-rate MIN_IMPROVED_RATE] [--max-candidate-columns MAX_CANDIDATE_COLUMNS] [--max-index-columns MAX_INDEX_COLUMNS] 
   [--min-reltuples MIN_RELTUPLES] [--multi_node]  [--json] [--driver] [--show_detail] [--show-benifits]

   其中的输入参数依次为：

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

   create index ind0 on public.bmsql_stock(s_i_id,s_w_id);
   create index ind1 on public.bmsql_customer(c_w_id,c_id,c_d_id);
   create index ind2 on public.bmsql_order_line(ol_w_id,ol_o_id,ol_d_id);
   create index ind3 on public.bmsql_item(i_id);
   create index ind4 on public.bmsql_oorder(o_w_id,o_id,o_d_id);
   create index ind5 on public.bmsql_new_order(no_w_id,no_d_id,no_o_id);
   create index ind6 on public.bmsql_customer(c_w_id,c_d_id,c_last,c_first);
   create index ind7 on public.bmsql_new_order(no_w_id);
   create index ind8 on public.bmsql_oorder(o_w_id,o_c_id,o_d_id);
   create index ind9 on public.bmsql_district(d_w_id);

• DB_PORT：连接数据库的端口号。

• DATABASE：连接数据库的名字。

• FILE：包含workload语句的文件路径。

• DB_HOST：（可选）连接数据库的主机号。

• DB_USER：（可选）连接数据库的用户名。

• SCHEMA：模式名称。

• MAX_INDEX_NUM：（可选）最大的索引推荐数目。

• MAX_INDEX_STORAGE：（可选）最大的索引集合空间大小。

• MAX_N_DISTINCT：distinct值个数的倒数，默认为0.01。

• MIN_IMPROVED_RATE：最小提升比例，默认为0.1。

• MAX_CANDIDATE_COLUMNS：（可选）索引最大的候选列个数。

• MAX_INDEX_COLUMNS：索引最大列数，默认为4。

• MIN_RELTUPLES：最小记录数，默认为10000。

• multi_node：（可选）指定当前是否为分布式数据库实例。

• multi_iter_mode：（可选）算法模式，可通过是否设置该参数来切换算法。

• json：（可选）指定workload语句的文件路径格式为SQL归一化后的json，默认格式每条SQL占一行。

• driver：（可选）指定是否使用python驱动器连接数据库，默认gsql连接。

• show-detail：（可选）是否显示当前推荐索引集合的详细优化信息。

• show-benefits：（可选）是否显示索引收益。

👍 点赞，你的认可是我创作的动力！

⭐️ 收藏，你的青睐是我努力的方向！

✏️ 评论，你的意见是我进步的财富！

PS：因为公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。点“在看”支持我吧!