1. 索引推荐的使用背景
1.1 索引推荐的使用意义
作为关系型数据库系统中一个重要问题，索引推荐受到越来越多的关注。索引的目的在于提高查询效率，这就如同字典的检索页，试想一下，如果没有检索页的定位，对于数据库这个乱序插入的字典来说，每次都要查看所有行，才能找到所需数据，对于动辄万级百万级记录的表来说，代价是不可接受的。不同的场景下，对于索引配置也有不同的要求。比如，数据库长时间不会做更新操作，追求极致查询性能，可以添加尽量多的索引。相反，如果数据库经常更新，过多的索引会拖累整体性能，影响系统吞吐，那么就要求索引更为精简化。
openGauss自动驾驶平台DBMind提供了智能索引推荐服务，根据检测到的数据库负载，识别性能不佳以及可以提升的SQL，基于全量workload的代价提升并综合考虑所推荐索引的'性价比'，给出索引配置结果。此外，对于系统中的冗余索引给出提示，运维人员据此对索引进行清理。
1.2 民生银行的业务特点
民生银行拥有非常庞大的用户群体， openGauss在民生银行内承载了很多类型的生产业务，绝大多数业务以复杂查询为主。某些典型场景中，复杂业务的SQL语句甚至超过40kb.对于这种形式的SQL语句，如果使用人工经验进行索引调优，仅仅将SQL语句浏览完都是一件很痛苦的事情，为其进行索引调优显然更难。
同时，在民生银行的生产场景中，还具有以下的业务特点：
-
基本都是使用Java connector连接到数据库执行SQL语句的，其执行的SQL语句都是使用prepare-execute形式执行的PBE（Parse Bind Execute）语句，看不到SQL语句参数的具体值；
-
由于在业务层使用了ORM（Object-Relational Mapping）框架（如MyBatis），业务SQL语句大多数都是自动生成的，人工理解的难度就更大；
-
同时，民生银行的数据库节点规模数以百计，随着业务的发展，如果每个实例都需要人工配置，其工作量可想而知。
由于在民生银行中数据库的使用场景很多，针对索引推荐的要求也有不同。具体来说，有以下的几个业务场景：
-
当前正在跑的业务很慢，希望针对当前正在运行的业务进行索引推荐，此时需要针对pg_stat_activity系统表中展示的SQL语句进行索引推荐；
-
需要针对过去某个时间段内的SQL语句流水进行分析，那么可以通过ASP功能，从pg_asp系统表中获取SQL语句的id, 然后与dbe_perf.statement 表中记录的SQL语句进行关联，获取到该段时间采样到的SQL语句进行，然后对他们进行分析；
-
需要对全量的业务SQL语句进行分析，而又缺乏部署SQL流水监控平台，那么就需要从pg_log数据库日志中获取SQL执行日志流水。
接下来，对整个索引推荐过程，我们以下述几种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;SELECTc1, c2FROM t1, t2WHERE 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$ lspostgresql-2022-06-06_115802.log postgresql-2022-06-22_000000.logpostgresql-2022-07-25_000000.log postgresql-2022-09-04_000000.logpostgresql-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流水时的一些问题
-
从dbe_perf.statement 和 dbe_perf.statement_history视图中获取的SQL语句，默认是不包含具体数值的，该具体数值在openGauss中会被替换为问号(?)字符，以便起到数据保密的效果。DBMind的索引推荐可以对该“模板”形式的SQL语句进行分析，但是分析的粒度也是基于“模板”的形式。如果用户希望关闭该参数匿名化过程，则可以设置GUC参数track_stmt_parameter 的值为 on;
-
对于PBE形式的SQL流水，DBMind也是针对该PBE形式进行推荐的。PBE过程是带有参数占位符“$”的SQL语句形式，他们在openGauss数据库内部的执行逻辑与带有具体数值的SQL语句的执行流程是不同的。与参数匿名化（数值替换为为?）过程也不相同。所以，如果就是想对PBE形式的SQL语句进行索引推荐，则无需设置参数track_stmt_parameter的值为on;
-
如果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的具体实现过程中，存在很多优化细节和改进的实现方式，关于工程实现的内容不是本文的重点，不进行详细介绍。此处，总体介绍一下各部分的流程：
-
基于索引生成算法对单独的SQL语句推荐索引
-
基于openGauss优化器对推荐结果进行筛选与验证，进而生成候选索引
-
通过贪心策略，生成当前workload上的最优索引配置
-
整个索引推荐过程使用虚拟索引，避免索引创建过程带来的不可避免的时间和空间的开销
虚拟索引的逻辑与真实优化器规划的逻辑相同，无需要担心由于评估结果错误导致的问题。
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. 索引推荐的其他常见问题
-
由于系统的字符限制，会导致部分SQL被截断，索引推荐将会自动识别无效的SQL语句，并跳过这部分SQL，如果希望避免系统截断SQL语句，则可以按照前文介绍的方式，调高GUC参数 track_activity_query_size的数值；但是这个过程会消耗额外的内存空间，所以也可以在索引推荐功能使用完毕后再调回去；
-
从部分系统表（或系统视图）中抽取的SQL语句，缺少频率信息，这会导致索引推荐对workload级别的代价收益评估不准；例如，只是从pg_stat_activity中获取当前执行的SQL语句，语句信息固然是有了，但是增删查改的比例却不一定准，DBMind的索引推荐功能是会考虑到增删查改不同业务的比例的，进而抉择推荐出的索引数量；
-
由于用户提供的SQL流水很难覆盖全量SQL，当前负载相关的无效索引的展示只能作为参考，慎重删除相关索引，一般需要与业务方进行全面沟通评估。
附：DBMind的下载方式：
当前DBMind已经从openGauss社区数据库内核代码的仓库中分离出来了，该仓库路径是：https://gitee.com/opengauss/openGauss-DBMind或阅读原文获取最新的DBMind版本，并按照readme的说明进行安装部署即可。目前openGauss发行后自带的gs_dbmind命令是稳定版本，但没有上述索引推荐的部分新功能。
本文分享自微信公众号 - openGauss（openGauss）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“ OSC源创计划 ”，欢迎正在阅读的你也加入，一起分享。
1917
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
