openGauss学习笔记-295 openGauss AI特性-AI4DB数据库自治运维-DBMind的AI子功能-Index-advisor：索引推荐-虚拟索引
虚拟索引功能支持用户在数据库中直接进行操作，本功能将模拟真实索引的建立，避免真实索引创建所需的时间和空间开销，用户基于虚拟索引，可通过优化器评估该索引对指定查询语句的代价影响。
本功能涉及的系统函数接口如下表所示：
表 1 虚拟索引功能的接口
| 函数名 | 参数 | 功能 |
|---|---|---|
| hypopg_create_index | 创建索引语句的字符串 | 创建虚拟索引。 |
| hypopg_display_index | 无 | 显示所有创建的虚拟索引信息。 |
| hypopg_drop_index | 索引的oid | 删除指定的虚拟索引。 |
| hypopg_reset_index | 无 | 清除所有虚拟索引。 |
| hypopg_estimate_size | 索引的oid | 估计指定索引创建所需的空间大小。 |
本功能涉及的GUC参数如下：
表 2 虚拟索引功能的GUC参数
| 参数名 | 功能 | 默认值 |
|---|---|---|
| enable_hypo_index | 是否开启虚拟索引功能 | off |
295.1 使用步骤
295.1.1 案例一：使用虚拟索引，调优等值查询
在此案例中，存在表bmsql_customer，该表是TPC-C benchmark中的一张表，此处演示在该表的c_w_id列上创建一个索引，是否可以提升某个等值查询的性能，如果该索引被使用了，则预估执行代价(cost)是多少。
-
使用函数hypopg_create_index创建虚拟索引。例如：
openGauss=> 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，该参数控制数据库的优化器进行EXPLAIN时是否考虑创建的虚拟索引。通过对特定的查询语句执行explain，用户可根据优化器给出的执行计划评估该索引是否能够提升该查询语句的执行效率。例如：
openGauss=> set enable_hypo_index = on; SET开启GUC参数前，执行EXPLAIN + 查询语句：
openGauss=> 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 + 查询语句：
openGauss=> 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展示所有创建过的虚拟索引。例如：
openGauss=> 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估计虚拟索引创建所需的空间大小（单位：字节）。例如：
openGauss=> select * from hypopg_estimate_size(329730); hypopg_estimate_size ---------------------- 15687680 (1 row) -
删除虚拟索引。
使用函数hypopg_drop_index删除指定oid的虚拟索引。例如：
openGauss=> select * from hypopg_drop_index(329726); hypopg_drop_index ------------------- t (1 row)使用函数hypopg_reset_index一次性清除所有创建的虚拟索引。例如：
openGauss=> select * from hypopg_reset_index(); hypopg_reset_index -------------------- (1 row)
295.1.2 案例二：虚拟索引联合Hint，预测调优效果
Hint可以手动要求数据库优化器使用某种方式生成执行计划，因此，对于某些数据库优化器难以生成最优执行计划的场景，可以手动指定执行计划。例如对某张表中的数据进行扫描操作（Scan），可以采用tablescan, indexscan, indexonlyscan, 其分别对应了表扫描、索引扫描、覆盖索引扫描。对于后两种扫描形式，必须要求先在数据库表上存在索引才可以操作。而虚拟索引则可以实现在不创建索引的情况下，测试某个索引扫描的效果。
-
创建一张表t1，并生成一定量数据，供后续测试。
create table t1 (id int, name text); insert into t1 select generate_series(0, 100000), 'test'; analyze t1; -
测试当前优化器默认的范围检索执行计划，并获取其总代价；由于没有创建索引，该SQL语句使用的是全表扫描（SeqScan）。
explain select * from t1 where id > 1; -
在t1表的id列上新建虚拟索引。
-- 开启参数，以便后续执行explain时能够采用虚拟索引 set enable_hypo_index = on; -- 创建session级别虚拟索引，该session退出后，这个虚拟索引信息也会被自动清理掉 select hypopg_create_index('create index on t1(id)','session'); -
通过explain语句，查看该SQL语句是否能够采用该索引；由于该列的distinct值很大，且涉及回表，优化器默认不会采用该索引，该语句执行计划与步骤2无变化，仍是全表扫描（SeqScan）。
explain select * from t1 where id > 1; -
通过hint操作，手动要求走索引扫描，查看能否成功；由于指定了hint, 且存在该索引（尽管是虚拟的），仍然可以通过explain看到优化器使用了索引扫描 IndexScan。
-- 其中<57762>btree_t1_id是自动生成的虚拟索引名，实际操作中以创建虚拟索引时的返回值为准 explain select /*+ indexscan(t1 "<57762>btree_t1_id") */ * from t1 where id > 1;
说明：
- 执行EXPLAIN ANALYZE不会涉及虚拟索引功能。
- 创建的虚拟索引是数据库实例级别的，各个会话（session）之间可共享设置，关闭会话后虚拟索引仍可存在，但是重启数据库后将被清空。
- 本功能暂不支持视图、物化视图、列存表。
- 开启虚拟索引功能并执行EXPLAIN语句时，可以生成创建虚拟索引之后的执行计划；同时，indexscan/indexonlyscan hint支持虚拟索引。
- 会话级别虚拟索引在各个会话间的设置互不影响，关闭会话后将被清空。
- 与真实索引不同，虚拟索引的相关操作不可回滚。
- 虚拟索引相关函数，不支持dblink远程调用。
👍 点赞，你的认可是我创作的动力！
⭐️ 收藏，你的青睐是我努力的方向！
✏️ 评论，你的意见是我进步的财富！
