共用方式為

適用於 PostgreSQL 的 Azure 資料庫 中的索引微調 - 彈性伺服器

  • 發行項

適用範圍：適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

索引微調是適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的一項功能，可藉由分析追蹤查詢並提供索引建議，自動改善工作負載的效能。

它是 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器的內建供應專案，其以具有查詢存放區功能的監視效能為基礎。 索引微調會分析查詢存放區所追蹤的工作負載，併產生索引建議，以改善分析工作負載的效能，或卸除重複或未使用的索引。

索引微調演算法的一般說明

當 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 查詢都會納入其中。

注意

索引微調不僅會分析 SELECT 語句，也會分析 DML （UPDATE、INSERT 和 DELETE） 語句。

系統會依據彙總的執行時間總計來排名產生的一組查詢，並分析前 index_tuning.max_queries_per_database 名查詢以取得可能的索引建議。

可能的建議旨在改善這些查詢類型的效能：

  • 使用篩選條件的查詢 (亦即，在 WHERE 子句中使用述詞的查詢)。
  • 聯結多個關聯的查詢，不論其採用的語法中聯結是否以 JOIN 子句表示，或是否以 WHERE 子句表示聯結述詞。
  • 結合篩選和聯結述詞的查詢。
  • 使用分組的查詢 (使用 GROUP BY 子句的查詢)。
  • 結合篩選和分組的查詢。
  • 使用排序的查詢 (使用 ORDER BY 子句的查詢)。
  • 結合篩選和排序的查詢。

注意

系統目前建議的唯一索引類型是 B 型樹狀結構。

如果查詢參考數據表的一個數據行，而且該數據表沒有統計數據，則會略過整個查詢，而且不會產生任何索引建議來改善其執行。

您可使用 ANALYZE 命令手動觸發收集統計數據所需的分析，或由自動資料清理精靈自動觸發。

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。 如需此子句效果的詳細資訊，請瀏覽 PostgreSQL 的 CREATE INDEX 官方文件。

索引微調會自動產生建議索引名稱，通常包含以 "_" (底線) 分隔的不同索引鍵資料行名稱和常數 "_idx" 後置詞。 如果名稱的總長度超過 PostgreSQL 限制，或與任何現有關聯發生衝突，則名稱會略有不同。 名稱可以被截斷，且可以在名稱結尾附加數字。

計算 CREATE INDEX 建議的影響

建立索引建議的影響是以 IndexSize (MB) 和 QueryCostImprovement (百分比) 來測量。

IndexSize 是單一值，代表預估的索引大小，必須考慮資料表目前的基數，以及建議索引所參考的資料行大小。

QueryCostImprovement 是由值的陣列組成，其中每個元素都代表此索引存在時預估的每個查詢計劃成本改善。 每個元素都會顯示查詢的識別碼 (已查詢)，以及實作建議時計劃成本的改善百分比 (維度)。

DROP INDEX 和 REINDEX 建議

針對決定索引微調功能的每個資料庫，它應該起始新的會話，並在 CREATE INDEX 建議階段完成之後，根據下列準則，建議卸除或重新編製現有索引的索引：

  • 如果它被視為其他人重複，請卸除。
  • 如果它未用於可設定的時間量，請卸除。
  • 重新編製標示為無效的索引。

卸除重複的索引

卸除重複索引的建議：首先，識別哪些索引重複。

重複索引會根據可歸因於索引的不同函式，以及其估計大小來排名。

最後，建議卸除排名低於其參考領導項目的所有重複索引，並描述每個重複索引的排名方式。

若要將兩個索引視為重複，它們必須：

  • 透過相同的資料表建立。
  • 為完全相同類型的索引。
  • 比對索引鍵資料行，且若是多資料行索引鍵，則比對參考的順序。
  • 比對述詞的運算式樹狀架構。 僅適用於部分索引。
  • 比對所有非簡單資料行參考的運算式樹狀架構。 僅適用於在運算式上建立的索引。
  • 比對索引鍵中參考的每個資料行定序。

卸除未使用的索引

卸除未使用索引的建議會識別下列情況的索引：

  • 至少 index_tuning.unused_min_period 天未使用。
  • 在建立索引的數據表上顯示最小（每日平均）的 DML 數目 index_tuning.unused_dml_per_table 。
  • 在建立索引的數據表上顯示最小（每日平均）讀取 index_tuning.unused_reads_per_table 次數。

重新編製無效索引的索引

重新編製現有索引索引的建議會識別標示為無效的索引。 若要深入瞭解索引標示為無效的原因和時機，請參閱 PostgreSQL 官方檔中的 REINDEX 。

計算 DROP INDEX 建議的影響

卸除索引建議的影響是利用兩個維度加以測量：Benefit (百分比) 和 IndexSize (MB)。

Benefit 是目前可以忽略的單一值。

IndexSize 是單一值，代表預估的索引大小，必須考慮資料表目前的基數，以及建議索引所參考的資料行大小。

設定索引微調

您可以透過一組可控制其行為的參數來啟用、停用及設定索引微調，例如微調會話的執行頻率。

探索如何啟用、停用及設定索引微調功能中正確設定索引微調功能的所有詳細數據。

索引微調產生的資訊

如何讀取、解譯和使用索引微調產生的建議詳細說明如何取得和使用索引微調產生的建議。

限制和支援能力

以下是索引微調的限制和支援範圍清單。

支援的計算層和 SKU

所有目前可用的層級都支援索引微調：可高載、一般用途和記憶體最佳化，以及在至少使用 4 個虛擬核心的任何目前支援的計算 SKU 上也支援此功能。

PostgreSQL 的支援版本

適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器的主要版本 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。

唯讀模式和讀取複本

由於索引調整依賴 讀取複本不支援的查詢存放區， 或當實例處於只讀模式時，我們不支援讀取複本或處於只讀模式的實例。

在對主要複本執行之工作負載進行獨佔分析之後，讀取複本上看到的任何建議都會在主要複本上產生。

相應減少計算

如果在伺服器上啟用索引調整，而且您會將該伺服器的計算相應減少為小於所需的虛擬核心數目下限，此功能仍會保持啟用。 由於此功能在少於 4 個虛擬核心的伺服器上不受支援，因此不會執行 來分析工作負載並產生建議，即使已設定ON為在計算縮減時也一樣index_tuning.mode。 雖然伺服器不符合最低需求，但無法存取所有 index_tuning.* 伺服器參數。 每當您將伺服器相應增加至符合最低需求的計算時， index_tuning.mode 都會使用設定的任何值進行設定，再將它縮減為不符合需求的計算。

高可用性和讀取複本

如果您在伺服器上設定 高可用性 或 讀取複 本，請注意在實作建議索引時，與在主伺服器上產生大量寫入工作負載相關的影響。 建立預估大小為大型的索引時，請特別小心。

相關內容