云数据库 PostgreSQL 智能索引推荐（tencentdb_index_advisor）

一、概述

tencentdb_index_advisor 是云数据库 PostgreSQL 提供的智能索引推荐扩展，能够对单条 SQL 或批量工作负载自动分析并推荐最优索引方案，无需实际创建物理索引即可评估效果。

核心价值：

·智能分析：自动识别 WHERE、JOIN、ORDER BY、GROUP BY 等条件中的候选索引列。

·零成本评估：通过假设索引（Hypothetical Index）评估效果，不产生任何磁盘开销。

·多场景覆盖：支持 SELECT、UPDATE、DELETE 等多种 SQL 语句的索引推荐。

·批量优化：支持工作负载级别的索引推荐，一次分析多条 SQL 找到全局最优方案。

·安全可靠：只读分析不修改数据，推荐结果由 DBA 审核后手动创建。

典型场景：

·新业务上线前，对核心查询进行索引规划。

·慢查询排查，快速找到缺失的索引。

·数据库性能巡检，批量分析 TOP SQL 的索引优化空间。

·索引调优，对现有查询评估是否需要覆盖索引。

二、环境要求

四、安装扩展

— 安装依赖扩展 hypopg（如未安装）CREATE EXTENSION IF NOT EXISTS hypopg;– 安装索引推荐扩展CREATE EXTENSION IF NOT EXISTS tencentdb_index_advisor;

五、验证安装

SELECT extname, extversion FROM pg_extension WHERE extname IN ('hypopg', 'tencentdb_index_advisor')ORDER BY extname;

预期输出：

七、函数签名

tencentdb_index_advise(query_string text)RETURNS TABLE ( schema_name text, table_name text, am_name text, index_size bigint, index_def text)

参数：

八、输出字段

九、GUC 配置参数

参数设置示例：

— 关闭覆盖索引推荐（减少索引大小）SET tencentdb_index_advisor.enable_covering_index = off;– 限制最多推荐 3 个索引SET tencentdb_index_advisor.max_index_recommend = 3;– 跳过变异优化阶段（加快推荐速度）SET tencentdb_index_advisor.try_variation_timeout = 0;– 批量分析时启用工作负载压缩SET tencentdb_index_advisor.enable_workload_compression = on;

十一、场景一：单条慢查询的索引推荐

当某条 SQL 执行缓慢时，直接获取推荐索引：

— 对慢查询进行索引推荐SELECT schema_name, table_name, am_name, pg_size_pretty(index_size) AS est_size, index_defFROM tencentdb_index_advise( 'SELECT * FROM orders WHERE customer_id = 123 AND status = ''shipped'' AND order_date > ''2025-01-01''');

输出示例：

注意：说明：

注意：推荐在 orders 表上创建（customer_id, status）联合索引。等值条件列（customer_id, status）优先排列，范围条件列（order_date）作为辅助。

十二、场景二：JOIN 查询的跨表索引推荐

SELECT schema_name, table_name, am_name, pg_size_pretty(index_size) AS est_size, index_defFROM tencentdb_index_advise( 'SELECT o.id, c.name, o.amount FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.city = ''Beijing'' AND o.status = ''delivered''');

输出示例：

注意：说明：

注意：推荐为两个表分别创建索引。orders 表索引以 JOIN 和 WHERE 条件列为主键，customers 表索引覆盖过滤条件 city。

十三、场景三：批量分析 TOP SQL（工作负载推荐）

将多条慢查询一起分析，找到全局最优的索引组合：

— 同时分析多条业务 SQLSELECT schema_name, table_name, pg_size_pretty(index_size) AS est_size, index_defFROM tencentdb_index_advise(' SELECT * FROM orders WHERE customer_id = 100; SELECT * FROM orders WHERE status = ''pending'' AND region = ''east''; SELECT * FROM products WHERE category = ''electronics'' AND price < 200; SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.city = ''Beijing'';');

输出示例：

注意：说明：

注意：工作负载推荐会综合考虑所有 SQL 的需求，找到收益最大的索引组合。这比单条分析更加合理。

十四、场景四：覆盖索引对比

— 开启覆盖索引推荐SET tencentdb_index_advisor.enable_covering_index = on;SELECT index_def FROM tencentdb_index_advise( 'SELECT customer_id, amount FROM orders WHERE status = ''shipped'' AND region = ''east''');– 结果: CREATE INDEX ON public.orders USING btree (region, status, customer_id, amount)– 关闭覆盖索引推荐SET tencentdb_index_advisor.enable_covering_index = off;SELECT index_def FROM tencentdb_index_advise( 'SELECT customer_id, amount FROM orders WHERE status = ''shipped'' AND region = ''east''');– 结果: CREATE INDEX ON public.orders USING btree (region, status)

注意：说明：

注意：覆盖索引包含了查询所需的 customer_id 和 amount 列，可以实现仅索引扫描（Index-Only Scan），避免回表。但索引体积更大，需根据实际查询频率权衡。

十五、场景五：UPDATE/DELETE 语句的索引优化

— UPDATE 语句索引推荐SELECT index_def FROM tencentdb_index_advise( 'UPDATE orders SET amount = 0 WHERE customer_id = 123 AND status = ''cancelled''');– 结果: CREATE INDEX ON public.orders USING btree (customer_id, status)– DELETE 语句索引推荐SELECT index_def FROM tencentdb_index_advise( 'DELETE FROM orders WHERE customer_id = 999 AND order_date < ''2025-01-01''');– 结果: CREATE INDEX ON public.orders USING btree (customer_id, order_date)

注意：说明：

注意：UPDATE 和 DELETE 语句中的 WHERE 条件同样可以从索引中受益，减少需要扫描的行数。

十七、推荐流程

输入 SQL 语句 │ ▼步骤1: 查询解析 └── 解析 WHERE/JOIN/GROUP BY/ORDER BY 等子句 │步骤2: 候选索引列生成 └── 按优先级识别候选列： EQUALITY(等值) > RANGE(范围) > JOIN(连接) > GROUP_ORDER(排序/分组) > COVERING(覆盖) │步骤3: 假设索引创建 └── 通过 hypopg 创建假设索引（不占磁盘空间） │步骤4: 代价评估 └── 利用 PostgreSQL 优化器评估有无索引的执行代价差异 │步骤5: 贪心选择 + 变异优化 └── 贪心算法选择收益最大的索引组合 变异阶段尝试不同组合寻找更优方案 │步骤6: 输出推荐 └── 返回 CREATE INDEX 语句和预估索引大小

十八、候选列优先级

20、慢查询索引优化工作流

步骤1: 从 pg_stat_statements 提取 TOP 慢查询 └── SELECT query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; │步骤2: 批量输入到索引推荐 └── SELECT * FROM tencentdb_index_advise('慢查询1; 慢查询2; …'); │步骤3: 评估推荐索引 ├── 查看预估索引大小，评估磁盘空间影响 ├── 用 EXPLAIN 验证查询计划变化 └── 确认无重复/冗余索引 │步骤4: 低峰期创建索引 └── CREATE INDEX CONCURRENTLY … （在线创建，不锁表） │步骤5: 验证效果 └── 对比创建前后的查询耗时和执行计划

21、推荐索引效果验证方法

在正式创建索引前，可以用 EXPLAIN 先验证效果：

— 第一步：查看当前执行计划和代价EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';– 结果: Seq Scan, cost=1218.0– 第二步：获取推荐索引SELECT index_def FROM tencentdb_index_advise( 'SELECT * FROM orders WHERE customer_id = 123 AND status = ''shipped''');– 结果: CREATE INDEX ON public.orders USING btree (customer_id, status)– 第三步：创建索引（生产环境建议用 CONCURRENTLY）CREATE INDEX CONCURRENTLY idx_orders_cust_status ON orders USING btree (customer_id, status);– 第四步：验证优化效果EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';– 结果: Index Scan, cost=8.31 (性能提升 99.3%)

22、参数调优建议

23、名词解释

24、注意事项

·仅主库可用：在备库上无法执行索引推荐。

·依赖 hypopg：必须先安装 hypopg 扩展。

·权限要求：需要 pg_tencentdb_superuser 角色成员权限。

·推荐≠执行：推荐结果需要 DBA 审核后手动创建，index_def 字段可直接作为 DDL 执行。

·CONCURRENTLY 创建：生产环境创建索引时建议使用 CREATE INDEX CONCURRENTLY 避免锁表

·索引不是越多越好：过多索引会增加写入开销和存储成本，建议根据查询频率和收益综合评估。

·定期维护：业务变化后建议重新运行索引推荐，清理不再需要的索引。

·多条 SQL 分析：工作负载推荐比逐条分析更准确，能找到全局最优的索引组合。

·LIKE 查询：对于前缀匹配（LIKE 'prefix%'），扩展会推荐带 text_pattern_ops 的索引；后缀匹配（LIKE '%suffix'）无法使用 B-tree 索引。