通过

你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

Azure Database for PostgreSQL 灵活服务器中的索引优化

  • 项目

适用于： 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 子句的查询）。
  • 组合筛选器和排序的查询。

注意

系统目前建议的唯一索引类型是 B-Tree 类型的索引。

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

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

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

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

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

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

注意

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 是单一值，表示索引的估计大小，考虑到表的当前基数和建议索引引用的列大小。

配置索引优化

可以通过一组控制索引优化行为的参数来启用、禁用和配置索引优化，例如优化会话的运行频率。

在如何启用、禁用和配置索引优化中，探索有关正确配置索引优化功能的所有详细信息。

索引优化产生的信息

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

限制和可支持性

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

对 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

索引优化使用 query_store.qs_view 的 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 都会使用设置的任何值进行配置，然后再将其缩减到不满足要求的计算。

高可用性和只读副本

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

相关内容