索引微調是適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體中的一項功能，可藉由分析追蹤的查詢並提供索引建議，自動改善工作負載的效能。
這是 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 型樹狀結構。
如果查詢參考資料表的一個資料行，而且該資料表沒有統計資料，則會略過整個查詢，而且不會產生任何索引建議來改善其執行。
您可使用 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天未使用。 - 在顯示建立索引的資料表上顯示最小 (每日平均)
index_tuning.unused_dml_per_tableDML 數目。 - 在顯示建立索引的資料表上顯示最小 (每日平均)
index_tuning.unused_reads_per_table讀取數目。
針對無效索引進行重新編製索引
針對現有索引進行重新編製索引的建議會識別標示為無效的索引。 若要深入了解索引標示為無效的原因和時機，請參閱 PostgreSQL 官方文件中的 REINDEX。
計算 DROP INDEX 建議的影響
卸除索引建議的影響是利用兩個維度加以測量：Benefit (百分比) 和 IndexSize (MB)。
Benefit 是目前可以忽略的單一值。
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 設定的現行值，並且根據該值排程下一次執行。
下列選項可用於設定索引微調參數：
| 參數 | 說明 | 預設值 | 範圍 | 單位 |
|---|---|---|---|---|
index_tuning.analysis_interval |
設定當 index_tuning.mode 設為 REPORT 時，觸發每個索引最佳化工作階段的頻率。 |
720 |
60 - 10080 |
minutes |
index_tuning.max_columns_per_index |
可以成為建議索引中索引鍵一部分的資料行數目上限。 | 2 |
1 - 10 |
|
index_tuning.max_index_count |
在一個最佳化工作階段期間，每個資料庫的建議索引數上限。 | 10 |
1 - 25 |
|
index_tuning.max_indexes_per_table |
每個資料表的可建議索引數上限。 | 10 |
1 - 25 |
|
index_tuning.max_queries_per_database |
每個資料庫中可以建議索引的最慢查詢數。 | 25 |
5 - 100 |
|
index_tuning.max_regression_factor |
對於在一個最佳化工作階段期間內分析的任何查詢，建議索引產生的可接受迴歸。 | 0.1 |
0.05 - 0.2 |
百分比 |
index_tuning.max_total_size_factor |
任何指定資料庫可以使用的所有建議索引總大小上限，以總磁碟空間百分比表示。 | 0.1 |
0 - 1 |
百分比 |
index_tuning.min_improvement_factor |
針對至少一個在一個最佳化工作階段期間分析的查詢，建議索引必須提供的成本改善。 | 0.2 |
0 - 20 |
百分比 |
index_tuning.mode |
將索引最佳化設定為停用 (OFF) 或啟用，以便只發出建議。 將 pg_qs.query_capture_mode 設定為 TOP 或 ALL，要求啟用查詢存放區。 |
OFF |
OFF, REPORT |
|
index_tuning.unused_dml_per_table |
影響資料表的每日平均 DML 作業數目下限，因此會考慮卸除未使用的索引。 | 1000 |
0 - 9999999 |
|
index_tuning.unused_min_period |
根據系統統計資料，考慮要將其卸除的索引未使用天數下限。 | 35 |
30 - 70 |
|
index_tuning.unused_reads_per_table |
影響資料表的每日平均讀取作業數目下限，因此會考慮卸除未使用的索引。 | 1000 |
0 - 9999999 |
如果您使用 CLI 命令 az postgres flexible-server index-tuning show-settings ，並 az postgres flexible-server index-tuning set-settings 顯示或修改任何索引微調設定，接受做為 參數自變數 --name 的值是上表的 Parameter 資料行中顯示的值，但不包含前置詞 index_tuning.。
索引微調產生的資訊
如何讀取、解譯和使用索引微調產生的建議詳細說明如何取得和使用索引微調產生的建議。
限制和支援能力
以下是索引微調的限制和支援範圍清單。
自動刪除建議
建議會在產生後的 35 天後自動刪除。 若要讓此自動刪除機制能夠運作，必須啟用索引調整。
對 hypopg 延伸模組的相依性
若要讓索引微調產生 CREATE INDEX 建議，它會使用 hypopg 延伸模組。
如果延伸模組在微調工作階段開始時已經存在，則會在建立所在的結構描述上使用。 當微調工作階段完成時，不會卸除延伸模組。 例外狀況是在 pg_catalog 結構描述中建立延伸模組時。 如果是這種情況，索引微調會卸除延伸模組。
如果延伸模組不存在於第一個位置，或我們將其卸除，因為它是在 pg_catalog 結構描述中建立的，索引微調會在稱為 ms_temp_recommendations709253 的結構描述下建立它，而且，當微調工作階段順利完成時，它會卸除延伸模組並移除結構描述。
屬於 azure_pg_admin 角色成員的使用者可以隨時卸除 hypopg 延伸模組，即使是由索引微調功能建立也一樣。 不過，在執行索引微調工作階段時卸除它可能會導致該工作階段失敗，且不會產生任何建議。
支援的計算層和 SKU
所有目前可用的層級都支援索引微調：可高載、一般用途和記憶體最佳化，以及在至少使用 4 個虛擬核心的任何目前支援的計算 SKU 上也支援此功能。
PostgreSQL 的支援版本
適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體主要版本 12 或更新版本支援索引微調。
使用 search_path
索引微調會取用保存於 search_path 的 資料行中的值，因此，分析每個查詢時，執行查詢時所設定之 search_path 的相同值，原本就是設定為分析可能建議的值。
參數化查詢
使用 PREPARE 或使用擴充查詢通訊協定所建立的參數化查詢會經過剖析和分析，以產生索引建議。
若要分析參數化查詢，索引微調需要 pg_qs.parameters_capture_mode 在查詢存放區擷取查詢執行時設定為 capture_first_sample。 它也需要在執行查詢時，查詢存放區正確地擷取參數。 換句話說，針對要分析的查詢，query_store.qs_view 的資料行 parameters_capture_status 必須設定為 succeeded。
唯讀模式和讀取複本
由於索引微調依賴的查詢存放區是在讀取複本中不支援或當執行個體處於唯讀模式時，我們不支援讀取複本或處於唯讀模式的執行個體。
讀取複本上看到的任何建議，都是在單獨分析主要複本上執行的工作負載之後，在主要複本上產生的建議。
縮小計算
如果在伺服器上啟用索引微調，而且您將該伺服器的計算縮小為小於所需的虛擬核心數目下限，此功能仍會保持啟用。 由於此功能在少於 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 慢的其中之一。
- 是在一個特定資料庫的內容中執行 (當那些查詢中沒有任何查詢在伺服器層級上被識別為最慢的查詢時)。