索引优化

适用于： Azure Database for PostgreSQL 灵活服务器

索引优化是 Azure Database for PostgreSQL 灵活服务器中的一项功能，它通过分析跟踪的查询并提供索引建议来自动提高工作负荷的性能。

它是 Azure Database for PostgreSQL 灵活服务器中的内置产品/服务，是基于使用查询存储监视性能来构建的。 索引优化可分析查询存储跟踪的工作负荷，并生成索引建议，以提高已分析工作负荷的性能，或者删除重复索引或未使用的索引。

• 识别创建哪些索引是有益的，因为它们可以显著改进索引优化会话期间分析的查询。
• 识别完全重复且可以消除的索引，以减少它们的存在和维护对系统整体性能的影响。
• 确定可配置期间内未使用的索引，这些索引可能是要消除的候选索引。

索引优化算法的一般说明

当 index_tuning.mode 服务器参数配置为 report 时，优化会话将以服务器参数 index_tuning.analysis_interval 中配置的频率自动启动，以分钟表示。

在第一阶段，优化会话搜索数据库列表，在该列表中，它认为可能产生的任何建议都可能对系统的整体性能产生重大影响。 为此，它会收集查询存储记录的所有查询，它们的执行是在此优化会话所关注的查找间隔内捕获的。 查找间隔当前从优化会话的开始时间起一直持续到过去 index_tuning.analysis_interval 分钟。

对于所有用户启动的查询，其执行记录在查询存储中，并且其运行时统计信息未重置，系统会根据它们的聚合总执行时间对它们进行排名。 它根据查询的持续时间将注意力集中在最突出的查询上。

以下查询已从该列表中排除：

• 系统启动的查询。 （即，由 azuresu 角色执行的查询）
• 在任何系统数据库（azure_sys、template0、template1 和 azure_maintenance）的上下文中执行的查询。

算法在目标数据库上循环访问，搜索可能提高所分析工作负载性能的索引。 它还搜索可以消除的索引，因为这些索引被标识为重复项或未用于可配置的时间段。

CREATE INDEX 建议

对于每个识别为要进行分析以生成索引建议的候选数据库，在查找间隔期间和该特定数据库的上下文中执行的所有 SELECT、UPDATE、INSERT 和 DELETE 查询都会考虑在内。

根据查询的聚合总执行时间对结果集进行排名，并分析前 index_tuning.max_queries_per_database 个查询以获得可能的索引建议。

潜在建议旨在提高这些类型查询的性能：

• 带有筛选器的查询（即 WHERE 子句中带有谓词的查询），
• 查询联接多个关系，无论它们是否遵循用 JOIN 子句表达联接的语法，还是联接谓词是否在 WHERE 子句中表达。
• 组合筛选器和联接谓词的查询。
• 带分组的查询（带 GROUP BY 子句的查询）。
• 组合筛选器和分组的查询。
• 带排序的查询（带 ORDER BY 子句的查询）。
• 组合筛选器和排序的查询。

如果查询引用表的一列且该表没有统计信息，它会跳过整个查询，并且不会生成任何索引建议来改进其执行。

可以使用 ANALYZE 命令或通过 autovacuum 守护程序自动触发收集统计信息所需的分析。

index_tuning.max_indexes_per_table 指定可以建议的索引数，不包括在优化会话期间由任意数量的查询引用的任何单个表的表上可能已经存在的任何索引。

index_tuning.max_index_count 指定为优化会话期间分析的任何数据库的所有表生成的索引建议数。

对于要发出的索引建议，优化引擎必须估计它以 index_tuning.min_improvement_factor 指定的因子改进所分析工作负载中至少一个查询。

同样，会检查所有索引建议，以确保它们不会在用 index_tuning.max_regression_factor 指定的因子的工作负载中的任何单个查询上引入回归。

前面段落中提到的所有参数、它们的默认值和有效范围都在配置选项中进行了描述。

与创建索引的建议一起生成的脚本遵循以下模式：

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

它包括子句 concurrently。 有关此子句效果的更多信息，请访问 CREATE INDEX 的 PostgreSQL 官方文档。

索引优化会自动生成建议索引的名称，它们通常由不同键列的名称组成，由“_”（下划线）分隔，后缀为常量“_idx”。 如果名称的总长度超过 PostgreSQL 限制，或者与任何现有关系冲突，则名称略有不同。 它可以截断，并且可以在名称的末尾追加数字。

计算 CREATE INDEX 建议的影响

创建索引建议的影响是根据 IndexSize（兆字节）和 QueryCostImprovement（百分比）来度量的。

IndexSize 是单一值，表示索引的估计大小，考虑到表的当前基数和建议索引引用的列大小。

QueryCostImprovement 由值数组组成，其中每个元素表示每个查询的计划成本改进，如果存在此索引，其计划的成本估计将得到改进。 每个元素都显示查询的标识符（已查询）以及如果实施了建议，计划成本将改善的百分比（维度）。

DROP INDEX 和 REINDEX 建议

对于为其确定了“索引优化”功能的每个数据库，它应该会启动新会话，并且在 CREATE INDEX 建议阶段完成后，它会根据以下条件建议删除索引或重新编制现有索引:

• 如果被视为其他副本，则删除。
• 如果它的使用时间不多，则删除。
• 重新编制被标记为无效的索引。

删除重复索引

删除重复索引的建议：首先，确定哪些索引有重复项。

重复项是根据可归因于索引的不同函数和其估计大小进行排名的。

最后，它建议删除所有排名低于其引用前导的重复项，并描述为什么每个重复项都按照原有方式进行排名。

要将两个索引视为重复项，它们必须：

• 在同一表上创建。
• 是完全相同类型的索引。
• 匹配其键列，对于多列索引键，匹配它们被引用的顺序。
• 匹配其谓词的表达式树。 仅适用于部分索引。
• 匹配所有非简单列引用的表达式树。 仅适用于对表达式创建的索引。
• 匹配键中引用的每列排序规则。

删除未使用的索引

建议删除未使用的索引，以识别那些索引：

• 至少有 index_tuning.unused_min_period 天没有使用。
• 在创建索引的表上显示(每日平均)最少 index_tuning.unused_dml_per_table DML 数。
• 在创建索引的表上显示(每日平均)最少 index_tuning.unused_reads_per_table 读取数。

重新编制无效索引

重新编制现有索引的建议，该索引可识别被标记为无效的索引。 要了解有关索引标记为无效的原因和时间的更多信息，请参阅 PostgreSQL 官方文档中的 REINDEX。

计算 DROP INDEX 建议的影响

删除索引建议的影响是从两个维度来衡量的：效益（百分比）和 IndexSize（兆字节）。

权益是目前可以忽略的单一值。

IndexSize 是单一值，表示索引的估计大小，考虑到表的当前基数和建议索引引用的列大小。

配置索引优化

可以通过一组控制其行为的参数来启用、禁用和配置索引优化。

启用索引优化后，它会以 index_tuning.analysis_interval 服务器参数（默认为 720 分钟或 12 小时）中配置的频率进行唤醒，并开始分析查询存储在该时间段内记录的工作负荷。

请注意，如果更改 index_tuning.analysis_interval 的值，则仅在下一个计划执行完成后才会观察到该值。 例如，如果在上午 10:00 启用索引优化一天，由于 index_tuning.analysis_interval 的默认值为 720 分钟，则第一次执行计划于同一天的晚上 10:00 开始。 对上午 10:00 到晚上 10:00 之间的 index_tuning.analysis_interval 的值所做的任何更改都不会影响初始计划。 仅当计划的运行完成时，它将读取为 index_tuning.analysis_interval 设置的当前值，并根据该值计划下一次执行。

以下选项可用于配置索引优化参数：

如果使用 CLI 命令 az postgres flexible-server index-tuning show-settings 并 az postgres flexible-server index-tuning set-settings 显示或修改任何索引优化设置，则接受为参数参数的值 --name 是上表的 “参数 ”列中显示的值，但不包括前缀 index_tuning.。

索引优化产生的信息

如何读取、解释和使用索引优化产生的建议详细描述了如何获得和使用索引优化产生的建议。

限制和可支持性

下面是索引优化的限制和可支持性范围的列表。

自动删除建议

建议会在最后一次生成后的 35 天被系统自动删除。 要使此自动删除机制正常工作，必须启用索引优化。

对 hypopg 扩展的依赖项

若要进行索引优化以生成 CREATE INDEX 建议，请使用 hypopg 扩展。

如果在优化会话开始时扩展已存在，则会在创建该扩展的架构上使用它。 当优化会话完成时，不会删除该扩展。 例外情况是如果扩展是在 pg_catalog 架构中创建的。 如果是这种情况，索引优化会删除扩展。

如果扩展一开始就不存在，或者由于它是在 pg_catalog 架构中创建的而我们将其删除，则索引优化将在名为 ms_temp_recommendations709253 的架构下创建它，并且当优化会话成功完成后，会删除该扩展并移除该架构。

属于 azure_pg_admin 角色成员的用户可以随时删除 hypopg 扩展，即使它是由索引优化功能创建的。 但是，在索引优化会话运行时删除它可能会导致该会话失败，并且不生成任何建议。

支持的计算层和 SKU

所有当前可用层都支持索引优化：可突发、常规用途和内存优化，以及任何当前支持的计算 SKU（至少有 4 个 vCore）。

PostgreSQL 的受支持版本

Azure Database for PostgreSQL 灵活服务器的主要版本 12 或更高版本支持索引优化。

使用 search_path

索引优化使用 search_path 的 列中保留的值，以便在分析每个查询时，在最初执行查询时设置的 search_path 值与设置用于分析可能的建议的值是相同的。

参数化查询

对使用 PREPARE 或使用扩展查询协议创建的参数化查询进行分析，并据此生成索引建议。

对于参数化查询的分析，索引优化要求在查询存储捕获查询执行时将 pg_qs.parameters_capture_mode 设置为 capture_first_sample。 它还要求在执行查询时，查询存储正确捕获参数。 换句话说，对于要分析的查询，query_store.qs_view 的列 parameters_capture_status 必须设置为 succeeded。

只读模式和只读副本

由于索引优化依赖于查询存储，这是只读副本或实例处于只读模式时不支持的，因此我们不支持只读副本或处于只读模式的实例。

只读副本上显示的任何建议都是在分析主副本上执行的工作负载后，在主副本上生成的。

缩减计算

如果在服务器上启用了索引优化，并且将该服务器的计算缩减为小于所需 vCore 的最小值，该功能将保持启用状态。 由于该功能在少于 4 个 vCore 的服务器上不受支持，因此它不会运行以分析工作负荷并生成建议，即使 index_tuning.mode 已设置为 ON 在计算缩减时也是如此。 由于服务器不满足最低要求，所有 index_tuning.* 服务器参数都无法访问。 每当将服务器备份缩放到满足最低要求的计算时，index_tuning.mode 都会使用设置的任何值进行配置，然后再将其缩减到不满足要求的计算。

高可用性和只读副本

如果服务器上配置了高可用性或只读副本，请注意当执行建议索引时，在主服务器上生成写入密集型工作负载所带来的影响。 创建大小估计很大的索引时要特别小心。

相关内容

• 查询存储。
• 配置索引优化。
• 使用索引建议。