DBMind 索引推荐功能在民生银行的生产实践

1. 索引推荐的使用背景

1.1 索引推荐的使用意义

作为关系型数据库系统中一个重要问题，索引推荐受到越来越多的关注。索引的目的在于提高查询效率，这就如同字典的检索页，试想一下，如果没有检索页的定位，对于数据库这个乱序插入的字典来说，每次都要查看所有行，才能找到所需数据，对于动辄万级百万级记录的表来说，代价是不可接受的。不同的场景下，对于索引配置也有不同的要求。比如，数据库长时间不会做更新操作，追求极致查询性能，可以添加尽量多的索引。相反，如果数据库经常更新，过多的索引会拖累整体性能，影响系统吞吐，那么就要求索引更为精简化。

openGauss 自动驾驶平台 DBMind 提供了智能索引推荐服务，根据检测到的数据库负载，识别性能不佳以及可以提升的 SQL，基于全量 workload 的代价提升并综合考虑所推荐索引的'性价比'，给出索引配置结果。此外，对于系统中的冗余索引给出提示，运维人员据此对索引进行清理。

1.2 民生银行的业务特点

民生银行拥有非常庞大的用户群体， openGauss 在民生银行内承载了很多类型的生产业务，绝大多数业务以复杂查询为主。某些典型场景中，复杂业务的 SQL 语句甚至超过 40kb.对于这种形式的 SQL 语句，如果使用人工经验进行索引调优，仅仅将 SQL 语句浏览完都是一件很痛苦的事情，为其进行索引调优显然更难。

同时，在民生银行的生产场景中，还具有以下的业务特点：

1. 基本都是使用 Java connector 连接到数据库执行 SQL 语句的，其执行的 SQL 语句都是使用 prepare-execute 形式执行的 PBE（Parse Bind Execute）语句，看不到 SQL 语句参数的具体值；

2. 由于在业务层使用了 ORM（Object-Relational Mapping）框架（如 MyBatis），业务 SQL 语句大多数都是自动生成的，人工理解的难度就更大；

3. 同时，民生银行的数据库节点规模数以百计，随着业务的发展，如果每个实例都需要人工配置，其工作量可想而知。

由于在民生银行中数据库的使用场景很多，针对索引推荐的要求也有不同。具体来说，有以下的几个业务场景：

1. 当前正在跑的业务很慢，希望针对当前正在运行的业务进行索引推荐，此时需要针对 pg_stat_activity 系统表中展示的 SQL 语句进行索引推荐；

2. 需要针对过去某个时间段内的 SQL 语句流水进行分析，那么可以通过 ASP 功能，从 pg_asp 系统表中获取 SQL 语句的 id, 然后与 dbe_perf.statement 表中记录的 SQL 语句进行关联，获取到该段时间采样到的 SQL 语句进行，然后对他们进行分析；

3. 需要对全量的业务 SQL 语句进行分析，而又缺乏部署 SQL 流水监控平台，那么就需要从 pg_log 数据库日志中获取 SQL 执行日志流水。

通过民生银行一段时间的生产实践，采用各种复杂的边界场景进行测验，进一步强化了 DBMind 的索引推荐功能，并在民生银行的生产实践中获得了令人满意的效果，在不同场景上，获得了从 50%到数倍不等的性能提升。

接下来，对整个索引推荐过程，我们以下述几种 SQL 流水采集方式为例，具体阐述如何收集 SQL 流水，进而对该段时间内的 workload 进行索引配置。

2. SQL 流水采集

索引推荐是基于用户给定的负载文件进行推荐和分析的，其格式为由一批分号分割的 SQL 语句，例如：

SELECT c1, c2 FROM t1;SELECT count(1) from t2;SELECT c1, c2 FROM t1, t2 WHERE t1.id = t2.id;SELECT count(1) from t2;SELECT c1, c2 FROM t1, t2 WHERE t1.id = t2.id;…

openGauss 支持多种 SQL 流水采集，在用户无法提供 SQL 流水时，帮助用户采集 SQL 流水。

2.1 从日志进行 SQL 流水采集

a)分别查询当前数据库的 GUC 参数：

• log_statement: 将 SQL 流水打印到 pg_log 错误日志中

• log_min_duration_statement: 慢 SQL 记录的阈值

• log_line_prefix: pg_log 错误日志的打印格式，用于日志文件的解析，一般不修改

log_statement 和 log_min_duration_statement 两个参数此时可以记录一下，以便用来后续的参数复原。

tpcc=# show log_statement; log_statement --------------- none(1 row) tpcc=# show log_min_duration_statement; log_min_duration_statement ---------------------------- 1min(1 row) tpcc=# show log_line_prefix;  log_line_prefix   -------------------- %m %u %d %h %p %S (1 row)

b)通过 gs_guc 功能设置 GUC 参数，从而在数据库节点开启全量 SQL 流水收集：

gs_guc reload -D $DATADIR  -c "log_min_duration_statement = 0" -c "log_statement= 'all'"

参数说明

其中，将 log_min_duration_statement 设置为 0，表示采集全量 SQL 语句；将 log_statement 设置为 all 表示在 pg_log 错误日志中记录 SQL 语句信息。这里面的 gs_guc 命令可以用于修改 postgresql.conf 配置文件，从而修改数据库参数。此处的参数 –D 用于指定 postgresql.conf 配置文件所在目录。关于 gs_guc 其他的配置参数，可以使用--help 命令查看，或者参考该命令的文档。

注：相关参数对性能有一定影响，谨慎使用。

c)日志文件解析，采集指定时间段的 SQL 流水

gs_dbmind component extract_log $GAUSSLOG workload.sql '%m %c %d %p %a %x %n %e' -d postgres -U omm --start_time '2021-07-06 00:00:00'

参数说明

gs_dbmind 是 openGauss 的 DBMind 功能的调用命令；$GAUSSLOG 用来指定 pg_log 日志的存放目录，该目录包含多个不同时间段的日志文件:

dbmind_user@linux173 ~/test/data/pg_log      > $ ls                                            postgresql-2022-06-06_115802.log  postgresql-2022-06-22_000000.log  postgresql-2022-07-25_000000.log  postgresql-2022-09-04_000000.log postgresql-2022-09-28_000000.log

日志内容如下，其中包含了时间，数据库，SQL 等信息：

输出结果 workload.sql 如下：

SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = '4' AND s_quantity < '15' AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = '4' AND d_id = '6'         )     ) AS L;SELECT c_first, c_middle, c_last, c_balance     FROM bmsql_customer     WHERE c_w_id = '4' AND c_d_id = '10' AND c_id = '1021';SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = '4' AND o_d_id = '10' AND o_c_id = '1021'       AND o_id = (          SELECT max(o_id)               FROM bmsql_oorder               WHERE o_w_id = '4' AND o_d_id = '10' AND o_c_id = '1021'          );

d)数据库节点恢复相关 GUC 参数

gs_guc reload -D $DATADIR  -c "log_min_duration_statement = 1min" -c "log_statement= none"

使用 pg_log 日志形式记录 SQL 语句的优点是获取的 SQL 语句全面，不容易遗漏；缺点是采集数据量大，需要关注磁盘空间使用情况。

注：GUC 参数需复原，避免日志文件膨胀。

2.2 基于 ASP 系统表的 SQL 采集

如用户对一段时间的 ASP 抽样 SQL 较为关注，需保证数据库开启 ASP 相关参数，通过系统表 gs_asp 获取指定时间段内的 SQL. 由于 ASP 表中不记录具体的 SQL 语句内容，因此我们需要与 dbe_perf.statement 视图（查询该视图必须具有 sysadmin 权限或者 monitor admin 权限）进行关联，进而获取 SQL 语句的内容。由于 dbe_perf.statement 表只能在 postgres 数据库下查询到，因此，我们需要在 postgres 数据库下执行下述查询语句：

SELECT regexp_replace((CASE WHEN query like '%;' THEN query ELSE query || ';' END), E'[\n\r]+', ' ', 'g') as q FROM dbe_perf.statement S INNER JOIN gs_asp G ON G.unique_query_id = S.unique_sql_id INNER JOIN pg_database D ON G.databaseid = D.oid WHERE D.datname = '{database}' AND G.sample_time > '{start_time}' and G.sample_time < '{end_time}';

用户可以将上述查询语句中的 {database}, {start_time} 以及 {end_time} 中的内容替换为自己希望查询的值。

使用该方式收集 SQL 语句的优点是占用存储空间少，但是依赖的 dbe_perf.statement 系统表中的 SQL 语句数据已经匿名化，存在一定程度的失真；同时，ASP 机制是抽样采集，可能会覆盖不全面。

2.3 基于 statement 系统表的 SQL 采集

如果数据库未开启 ASP（即 enable_asp 参数值为 off），则我们无法显性获知一段时间内的 SQL 流水。此时，则可通过视图 dbe_perf.statement 获取指定数据库所有 SQL 信息。同样地，查询该视图必须具有 sysadmin 权限或者 monitor admin 权限，可替换下述语句中的 {database} 以及 {schema} 字段内容：

select regexp_replace((CASE WHEN query like '%;' THEN query ELSE query || ';' END), E'[\n\r]+', ' ', 'g') as q from dbe_perf.statement where db_name='{database}' and schema_name='{schema}';

该种方式是在 ASP 没有开启的情况下的一种备用手段，无法显性获取一段时间内的 SQL 语句信息，拿到的信息是全局视野下的统计信息。

2.4 基于 pg_stat_activity 系统表的 SQL 采集

用户需要对当前执行的 SQL 语句进行优化时，通过 pg_stat_activity 获取当前执行语句，替换{database}字段值为希望查询的数据库名。该方式的优点是采集代价小，过程时间短，缺点是只能看到当前正在执行的语句。

SELECT regexp_replace((CASE WHEN query like '%;' THEN query ELSE query || ';' END), E'[\n\r]+', ' ', 'g') as q FROM pg_stat_activity WHERE state != 'idle' and datname='{database}';

2.5 获取 SQL 流水时的一些问题

1. 从 dbe_perf.statement 和 dbe_perf.statement_history 视图中获取的 SQL 语句，默认是不包含具体数值的，该具体数值在 openGauss 中会被替换为问号(?)字符，以便起到数据保密的效果。DBMind 的索引推荐可以对该“模板”形式的 SQL 语句进行分析，但是分析的粒度也是基于“模板”的形式。如果用户希望关闭该参数匿名化过程，则可以设置 GUC 参数 track_stmt_parameter 的值为 on;

2. 对于 PBE 形式的 SQL 流水，DBMind 也是针对该 PBE 形式进行推荐的。PBE 过程是带有参数占位符“$”的 SQL 语句形式，他们在 openGauss 数据库内部的执行逻辑与带有具体数值的 SQL 语句的执行流程是不同的。与参数匿名化（数值替换为为?）过程也不相同。所以，如果就是想对 PBE 形式的 SQL 语句进行索引推荐，则无需设置参数 track_stmt_parameter 的值为 on;

3. 如果 SQL 语句的长度（length）很长，则有可能会超过 openGauss 数据库中为该字符串分配的长度，因此在数据库内部的系统表或视图中记录的值是有可能会被截断的。也就是说，从数据库的系统表或视图中获取到的 SQL 语句是不完整的。此时，如果希望尽可能完整地记录 SQL 语句全貌，则需要将该截断阈值设得更大一点，该参数由 GUC 参数 track_activity_query_size 决定，默认值为 4096 个字符，实际生产场景中，可以设得大一点，例如 40960 个字符，不过需要考虑到内存的实际情况，如果内存不够用，则需要进行权衡。

3. 索引推荐的使用

3.1 索引推荐算法简述

图示中各部分的含义如下：

Indexable Columns：候选索引列，是候选联合索引中的列的来源

Multi-Column Index Generation: 候选联合索引

Atomic configurations: 原子索引配置

Configuration Enumeration: 通过贪心算法对索引配置进行枚举

上述图示中所示的方法是典型的 workload 级别索引推荐的工程实现框架，在 DBMind 的具体实现过程中，存在很多优化细节和改进的实现方式，关于工程实现的内容不是本文的重点，不进行详细介绍。此处，总体介绍一下各部分的流程：

1. 基于索引生成算法对单独的 SQL 语句推荐索引

2. 基于 openGauss 优化器对推荐结果进行筛选与验证，进而生成候选索引

3. 通过贪心策略，生成当前 workload 上的最优索引配置

4. 整个索引推荐过程使用虚拟索引，避免索引创建过程带来的不可避免的时间和空间的开销

虚拟索引的逻辑与真实优化器规划的逻辑相同，无需要担心由于评估结果错误导致的问题。

3.2 OLTP 场景

On-line Transaction Processing, 在线交易处理强调的是处理大量在线的例行性交易数据。该场景操作的数据量小，事务往往比较快，涉及增删改查。索引配置时，仅保留提升幅度大的索引，并对有相关性的索引进行整合，避免引入的不必要的写入代价。

这是因为，索引的维护代价在 OLTP 场景中会比较明显，如果为表中的字段创建了很多的索引，在查询的时候固然不会受到什么影响，但是在 update, delete, insert 操作时，会存在索引维护的写放大效应。我们选用的 TPC-C benchmark 就存在大量的数据修改操作，因此这是一个很好的演示范例。此处为大家演示一下索引推荐的功能使用方法。此处，选用 10 仓的 TPC-C，TPC-C 的 benchmark 则使用 benchmark-sql5.0：

echo password | gs_dbmind components index_advisor $port$database workload.sql

--max-n-distinct 1 --min-reltuples 10 --use-all-columns --multi-iter-mode --min-improved_rate 0.5 --max-index-columns 3 --show-benefits

参数说明

其中，--max-n-distinct 指定 distinct 数的倒数的最大值为 1，即 distinct 数最小为 1，--min-reltuples，指定最小记录数为 10. 红色字体为关键参数，multi-iter-mode 指定贪心算法，min-improve_rated 指定最小提升比为 50%，max-index-columns 3 指定最大联合索引的列数为 3。echo password 表示通过管道把密码输入到 stdin 中，后面就不需要我们再交互式地输入密码了。$port$database 表示用户的数据库端口和数据库名。

在一段时间后，我们可以获得如下的推荐结果：

与原始索引相比，推荐索引提升大约 25%左右（tpm 32479.92 提升至 41600.54 ）

这里面可以看到返回了两个报告段，一个为”generate candidate indexes”表示根据 workload 文件挑选出来的备选索引，“determine optimal indexes”表示识别到的最佳索引，并给出了创建索引的 DDL 语句。

3.3 OLAP 场景

On-line Analytical Processing，联机分析处理是在基于数据仓库多维模型的基础上实现的面向分析的各类操作的集合。该场景的特点是操作数据量较大，主要以查询为主，几乎很少涉及到数据的变更。索引配置时，可以考虑保留更多的索引。为了方便演示，我们以通用的 benchmark TPC-H 为例，演示索引推荐过程。使用下面的 SQL 语句推荐索引：

echo password | gs_dbmind components index_advisor $port$database workload.sql --schema public --min-reltuples 10 --max-n-distinct 1 --use-all-columns --multi-iter-mode --min-improved-rate 0 --max-index-columns 5 --show-benefits

参数说明

其中，min-improved_rate 设为 0%，即保留所有可提升的索引，同时放宽最大联合索引的列数为 5. echo password, $port以及$database 的含义与上文相同。

执行一段时间后，可以获得推荐结果：

这里我们比较了索引推荐的前后差异，我们测试的结果表明：相较原始索引提升 20%，tpch 22 条语句总耗时由 10194ms 缩短至 8524ms. 当然，这里我们使用的数据量并不大，当数据量更大的时候，效果可能会更加明显。

4. 索引推荐结果解读

在上文中，我们介绍过“generate candidate indexes”与“determine optimal indexes”的含义，这是最基本的两个输出段。通过在民生银行的生产实践中，我们进一步优化了显示报告的结果，增加了细粒度的索引推荐效果评估报告。索引推荐报告自上而下分为：候选索引，最终索引，索引收益，已有索引，当前负载的无用索引，冗余索引与历史有效索引。

对于索引收益而言，优先展示对负载提升更大的索引，相应的 SQL 按 cost 提升比例由大到小展示，突出关键索引及 SQL，其结果如下图所示：

细粒度的 SQL 语句索引性能提升效果如下图所示，通过这个图，用户可以进一步决定应该创建哪个索引，哪个索引对当前的业务更有必要。

下面这个图则演示了，可以分析出当前系统中已有的索引效果，这为用户提供了索引维护的依据，即哪些索引对于目前还是有效的，哪些或许可以删除。根据在民生银行的生产实践来看，删除索引其实还是有风险的，需要与业务方进行评估。因为采集到的 SQL 流水毕竟只是一种抽样的形式，如果抽样没有抽到低频的业务，那么贸然地删除索引，可能会对某些任务造成较大的影响。

索引推荐过程消耗的时间与业务 SQL 语句的复杂程度、表的复杂程度、SQL 流水的规模等都具有正相关的关系。故而，索引推荐过程的具体时间消耗也不一定。为此，我们也通过民生银行的复杂生产场景优化了索引推荐功能，使用并行计算、非阻塞 IO、缓存、优化算法等方式，极大地降低了索引推荐过程的开销。把民生银行的某个复杂业务推荐时间由 40 分钟优化到了 4 分钟，进一步增强了该功能的可用性。除此之外，通过该过程，我们也实现了推荐进度条功能，便于用户直观地看到当前的推荐进度，增加了易用性，其效果如下：

5. 索引推荐的其他常见问题

1. 由于系统的字符限制，会导致部分 SQL 被截断，索引推荐将会自动识别无效的 SQL 语句，并跳过这部分 SQL，如果希望避免系统截断 SQL 语句，则可以按照前文介绍的方式，调高 GUC 参数 track_activity_query_size 的数值；但是这个过程会消耗额外的内存空间，所以也可以在索引推荐功能使用完毕后再调回去；

2. 从部分系统表（或系统视图）中抽取的 SQL 语句，缺少频率信息，这会导致索引推荐对 workload 级别的代价收益评估不准；例如，只是从 pg_stat_activity 中获取当前执行的 SQL 语句，语句信息固然是有了，但是增删查改的比例却不一定准，DBMind 的索引推荐功能是会考虑到增删查改不同业务的比例的，进而抉择推荐出的索引数量；

3. 由于用户提供的 SQL 流水很难覆盖全量 SQL，当前负载相关的无效索引的展示只能作为参考，慎重删除相关索引，一般需要与业务方进行全面沟通评估。