適用於 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 查詢。

該流程根據總執行時間對結果查詢進行排名，並分析頂尖 index_tuning.max_queries_per_database 查詢以尋找可能的索引推薦。

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

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

如果查詢只參考資料表的某一欄，而該資料表沒有統計資料，程序不會產生任何索引建議來改善執行。 然而，它會提出建議對表格進行分析。

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。 欲了解更多關於此條款影響的資訊，請參閱 PostgreSQL 官方文件中的 CREATE INDEX。

自主調音會自動產生推薦索引的名稱，這些索引通常由不同鍵欄位的名稱組成，中間以「_」（底線）分隔，並加上固定的「_idx」後綴。 如果名稱的總長度超過 PostgreSQL 限制，或與任何現有關聯發生衝突，則名稱會略有不同。 名稱可以被截斷，且可以在名稱結尾附加數字。

計算 CREATE INDEX 建議的影響

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

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

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

DROP INDEX 和 REINDEX 建議

每當資料庫被識別為候選者時，程序會啟動一個新的會話。 在建立 索引 建議階段結束後，建議根據以下標準取消或重新索引現有索引：

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

卸除重複的索引

刪除重複索引的建議首先要辨識哪些索引有重複。

重複項目會根據您可指派給索引的不同功能以及其預估大小進行排序。

流程最後建議刪除排名低於參考領導者的重複項目，並說明為何每個重複項目會被這樣排序。

兩個索引要被視為重複，必須：

• 透過相同的資料表建立。
• 為完全相同類型的索引。
• 比對其關鍵欄位；對於多欄索引鍵，還要比對其被參照的順序。
• 比對述詞的運算式樹狀架構。 此條件僅適用於部分索引。
• 比對所有非簡單資料行參考的運算式樹狀架構。 此條件僅適用於建立在表達式上的索引。
• 比對索引鍵中參考的每個資料行定序。

卸除未使用的索引

移除未使用索引的建議會識別符合下列條件的索引：

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

針對無效索引進行重新編製索引

現有索引重新索引的建議會標示那些被標記為無效的索引。 想了解更多為什麼以及何時索引會被標記為無效，請參閱 PostgreSQL 官方文件中的 REINDEX 。

計算 DROP INDEX 建議的影響

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

好處是一個您暫時可以忽略的單一值。

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

表格推薦

對於每個被識別為分析候選對象的資料庫，該流程都會啟動一個工作階段，以產出資料表層級的建議。 這些建議會請您對查詢檢查期間所存取的資料表執行ANALYZE或VACUUM。 調校引擎認為執行這些指令能提升你的工作負載效能。

ANALYZE 資料表建議

建議在分析表格時識別以下表格：

• 在查詢中被引用，且該表的某欄被用於其某個謂詞（WHERE， ， JOINORDER BYGROUP BY， ），且同時符合以下兩個條件之一：
  • 從未經過分析。
  • 曾經分析過，但現在缺乏統計資料（通常是因為伺服器在統計資料被保存到硬碟前就當機了）。

VACUUM 資料表建議

清理資料表的建議會識別膨脹的資料表。 只有在分析工作負載時，若 `autovacuum_enabled` 在伺服器層級未設為 `off`，此流程才會產生這些建議。

設定自主調音

你可以透過一組參數來啟用、停用並配置自動調校，這些參數控制其行為。

當你啟用自動調校時，它會在參數設定 index_tuning.analysis_interval 的頻率喚醒（預設為 720 分鐘或 12 小時），並開始分析查詢庫在該期間記錄到的工作負載。

如果你更改了 的 index_tuning.analysis_interval值，新的值只有在下一次排程執行完成後才會生效。 舉例來說，如果你在某天上午 10：00 啟用自動調整，因為預設 index_tuning.analysis_interval 值是 720 分鐘，第一次執行會排定在同一天晚上 10：00 開始。 你對上午10點到晚上10點之間的任何數值 index_tuning.analysis_interval 所做的更改，都不會影響該初始排程。 只有當排程執行完成後，它才會讀取目前設定的 index_tuning.analysis_interval 值，並依該值排程下一次執行。

請使用以下選項來設定自主調諧參數：

如果你使用 CLI 指令 az postgres flexible-server autonomous-tuning show-settings 並 az postgres flexible-server autonomous-tuning set-settings 顯示或修改任何自主調整設定，接受作為參數參數的值 --name 即為前一表 參數欄中 所示的值，但未包含前綴 index_tuning.。

由自主調音產生的資訊

《使用自主調校建議 》詳細說明如何取得並使用自主調校所產生的建議。

限制和支援能力

以下列表說明了自主調音的限制與支援範圍。

自動刪除建議

系統會在上次產生推薦後35天自動刪除。 要讓這個自動刪除機制運作，你必須啟用自主調整。

對 hypopg 延伸模組的相依性

若要產生 CREATE INDEX 建議，自動調校會使用 hypopg 擴充功能。

如果該擴充功能在調校工作階段開始時已存在，此程序會在建立該擴充功能的綱要中使用它。 當調校工作階段結束時，處理程序不會卸除擴充功能。 此規則的例外是，如果擴充功能是在 pg_catalog 架構中建立的。 這種情況下，自主調節會捨棄延伸模組。

如果該擴充功能一開始就不存在，或因為它是在 pg_catalog 結構描述中建立而被此程序捨棄，自主調整會在名為 ms_temp_recommendations709253 的結構描述下建立它。 當調校工作成功結束後，程序會丟棄該擴充功能並移除該結構。

屬於 azure_pg_admin 角色的使用者可隨時刪除 hypopg 擴充功能，即使該擴充功能是由自主調整功能所建立。 不過，在自動調校會話執行時丟棄它可能會導致該會話失敗，且不會產生任何建議。

支援的計算層和 SKU

適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器在所有目前可用的層級上皆支援自動調整，包括 Burstable、General Purpose 和 Memory Optimized。 它也支援對目前支援且至少有 4 個 vCore 的 計算 SKU 進行自主調校。

PostgreSQL 的支援版本

適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器支援 主要版本 12 或更新版本的自主調校。

使用 search_path

自主調校使用search_path欄位中的值。 分析每個查詢時，會使用查詢最初執行時設定的相同 search_path 值來分析可能的推薦。

參數化查詢

使用 PREPARE 或使用 擴展查詢協定 建立的參數化查詢會被解析和分析，以產生索引推薦。

如需分析參數化的查詢，自主微調需要您在查詢存放區擷取查詢執行時，將 pg_qs.parameters_capture_mode 設為 capture_first_sample。 這也要求查詢存放區在查詢執行時正確擷取參數。 換句話說，對於被分析的查詢，parameters_capture_statusquery_store.qs_view 中的欄位必須設為 succeeded。

唯讀模式和讀取複本

由於自主調節依賴查詢儲存區在azure_sys資料庫本機持續存留的資料，且不支援讀取複本或伺服器處於唯讀模式時，因此此功能在讀取複本或處於唯讀模式的伺服器上不受支援。

您在讀取複本上看到的任何建議，都是在僅分析主要複本上執行的工作負載後，於主要複本上產生的。

縮小計算

如果你在伺服器上啟用自動調校，然後將該伺服器的運算量縮減到低於最低要求的 vCore 數量，這個功能仍然會開啟。 因為此功能不支援 vCore 少於 4 個的伺服器，所以即使您在縮減計算資源時已將 index_tuning.mode 設為 ON，它也不會執行以分析工作負載並產生建議。 雖然伺服器未達最低要求，但所有 index_tuning.* 參數皆無法存取。 只要您將伺服器重新擴增至符合最低需求的計算，index_tuning.mode 就會設為您之前將它縮減到不符合需求的計算時所設定的值。

高可用性和讀取複本

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

自動調校可能無法為某些查詢產生建立索引的建議的原因

自主調校不會為以下類型的查詢產生 CREATE INDEX 推薦：

• 當自主調諧引擎在分析階段嘗試取得 EXPLAIN 輸出時，遇到錯誤的查詢。
• 參考pg_statistic系統目錄中沒有其內容統計資料之資料表的查詢。 在這些資料表上執行 ANALYZE ，讓調校引擎未來能考慮這些查詢。
• 查詢存放區中查詢文字遭截斷的查詢 當查詢文字長度超過 pg_qs.max_query_text_length設定的值時，會發生截斷。
• 參考您在分析前捨棄或重新命名之物件的查詢。 這些查詢在語法上仍然有效，但語意上並不有效。
• 存取暫存資料表或暫存資料表上的索引的查詢。
• 存取視圖或實體化視圖的查詢。
• 存取分割資料表的查詢
• 查詢被識別為效用語句。 效用語句或效用指令基本上是指任何未被視為 SELECT、 INSERT、 UPDATE、 DELETE、 MERGE或 的陳述句，以及包含這些陳述其中一種的某些指令。
• 對於所分析的資料庫和時段，非屬於前幾慢 index_tuning.max_queries_per_database 之列的查詢。
• 在特定資料庫的情境下執行的查詢，且在伺服器層級中沒有一個查詢被認定為最慢的。

相關內容

• 查詢存放區。
• 設定自動調校。
• 請使用自主調音建議。