欧美成人精品手机在线观看_69视频国产_动漫精品第一页_日韩中文字幕网 - 日本欧美一区二区

不得不說，管理一個大型數據庫系統，需要有相當高的技術和業務水平。比如說，到底什么時候才應該創建索引？這個問題，可能沒有大多數人想象的那么簡單。

介紹

在 SQL 性能調優方面，索引通常被認為是靈丹妙藥，PostgreSQL 支持不同類型的索引，以滿足不同的場景。人們發現，創建越來越多的索引的沖動，在許多系統中造成了嚴重的損害。很多時候，為了整體系統的效益，在考慮任何新的索引之前，我們應該首先刪除索引。驚訝嗎？了解索引的后果和開銷，有助于做出明智的決策，并可能使系統免于許多潛在問題。

在非常基本的層面上，我們應該記住，索引不是免費的。它在帶來好處的同時，也伴隨著性能和資源消耗方面的成本。以下是過度使用索引可能導致的十種問題/開銷的列表。這篇文章是關于 PostgreSQL 的，但大多數問題也適用于其他數據庫系統。

過多的索引會損害 PostgreSQL 性能的 10 種方式

索引會拖慢事務

添加索引后，我們可能會看到 SELECT 語句的性能有所提高。但是，我們不應忘記，在提高性能的同時，也會伴隨著同一個表上的事務的成本增加。從概念上講，表上的每次 DML 都需要更新表的所有索引。盡管有很多優化措施來減少寫入放大，但這是一個相當大的開銷。

例如，假設一個表上有五個索引；表中的每次 INSERT 都需要往這五個索引 INSERT 索引記錄。從邏輯上講，也將更新五個索引頁。因此，實際上，開銷是 5 倍。

內存使用情況

索引頁必須要在內存中，無論是否有任何查詢會使用它們，因為它們需要被事務進行更新。實際上，可用于表頁的內存會減少。索引越多，有效的緩存對內存的要求就越高。如果我們不增加可用內存，這就會開始損害系統的整體性能。

隨機寫: 更新索引的成本更高

與 INSERT 新記錄到表中不同，行不太可能插入到同一頁面中。眾所周知，像 B 樹索引這樣的索引會引發更多的隨機寫。

索引比表需要更多的緩存

由于隨機的寫和讀，索引需要更多頁面才能包含在緩存中。索引對緩存的要求通常遠高于關聯的表。

WAL 生成

除了表更新的 WAL 記錄外，還會有索引的 WAL 記錄。這有助于崩潰恢復和復制。如果您正在使用任何等待事件分析工具/腳本（如 pg_gather），則 WAL 生成的開銷將清晰可見。實際的影響取決于索引類型。

這是一個綜合性測試用例，但如果與 WAL 相關的等待事件顯示為任何排名靠前的等待事件，那么這是一個事務系統需要關注的問題，我們應該采取一切措施來解決這個問題。

越來越多的 I/O

不僅會生成 WAL 記錄；我們也會有更多頁面被弄臟。當索引頁被弄臟時，必須將它們寫回到文件，從而再次引發更多的 I/O - “DataFileWrite” 等待事件，如前一個屏幕截圖所示。

另一個副作用是索引會增加活躍數據集的總大小。我所說的“活躍數據集”是指經常查詢和使用的表和索引。隨著活躍數據集大小的增加，緩存的效率會越來越低。效率較低的緩存會引發更多的數據文件讀取，因此讀取 I/O 會增加。這是為特定查詢從存儲中讀取其他索引頁所需的 I/O 以外的額外代價。

同樣，另一個主要包含 SELECT 查詢的系統，它的 pg_gather 報告也顯示了這個問題。正如活躍數據集上升所體現的，PostgreSQL 別無選擇，只能從存儲中取出頁面。

持續時間越長，“DataFileRead” 百分比越大，則表明活躍數據集要大得多，這是不可緩存的。

對 VACUUM/AUTOVACUUM 的影響

如前面幾點所述，開銷不僅僅是插入或更新索引頁。維護它也會產生開銷，因為索引還需要清理舊的元組引用。

我見過這樣的情況：由于表的大小，最重要的是，由于表上的索引數量過多，單個表上的 autovacuum worker 運行時間很長。事實上，很多用戶看到過他們的 autovacuum worker “卡住”了幾個小時，在較長的時間內沒有顯示出任何進展。發生這種情況的原因是 autovacuum 的索引清理是 autovacuum 中的隱藏階段，并且在 pg_stat_progress_vacuum 這樣的視圖中不可見，除非該 VACUUM 階段被指明為正在清理索引。

隨著時間的推移，索引可能會變得臃腫，并且訪問效率降低。許多系統中可能需要定期維護 索引（REINDEX）。

調優時的隧道視野

隧道視野是視野的喪失。用戶可能正在專注于特定的 SQL 語句，試圖進行“調優”，并決定是否創建索引。通過創建用于優化查詢的索引，我們將更多的系統資源轉移到該查詢。然后，它可能會通過損害其他方面，來為該特定語句提供更多性能。

但是，隨著我們不斷創建越來越多的索引，來調優其他查詢，資源將再次轉向其他查詢。這會導致這樣一種情況，即調優每個查詢的努力會損害所有其他查詢。最終，每個查詢都會受到傷害，在這場資源爭奪的戰爭中剩下的只有失敗者。嘗試調優的人應該考慮系統的每個部分如何共存（最大化業務價值），而不是特定查詢的性能絕對最大化。

更大的存儲需求

幾乎每天，我都會看到索引比表占用更多存儲空間的情況。

對于那些有更多錢花在存儲上的人來說，這聽起來可能太傻了，但我們應該記住，這會產生連鎖反應。數據庫總大小增長到實際數據量的多倍。因此，很明顯，備份需要更多的時間、存儲和網絡資源，然后同樣的備份會給主機帶來更多的負載。這也將增加還原備份和恢復備份的時間。更大的數據庫會影響很多事情，包括需要更多時間來構建備用實例。

索引更容易損壞

這不只是在談論那些很少發生的與索引相關的軟件錯誤，例如 PostgreSQL 14 存在的索引損壞，或由于 glibc 排序規則更改導致的索引損壞，這些錯誤會時不時地出現，甚至在今天也會影響許多環境。隨著索引數量的增加，發生索引損壞的概率也會增加。

我們該怎么辦？

在考慮創建新的索引前，應該注意考慮一些關鍵問題：是否必須擁有此索引，還是有必要以引入更多索引為代價來加快查詢速度？有沒有辦法重寫查詢，以獲得更好的性能？拋開微小的收益，沒有這個索引的系統能運行嗎？

現有的索引也需要在一段時間內進行嚴格審查。應考慮刪除所有未使用的索引（pg_stat_user_indexes 中 idx_scan 為零的索引）。像 pgexperts 這樣的腳本可以幫助進行更多分析。

即將推出的 PostgreSQL 16，在 pg_stat_user_indexes / pg_stat_all_indexes 中增加了一列，名稱為 last_idx_scan，它可以告訴我們最后一次使用索引的時間（timestamp）。這將有助于我們充分了解系統中的所有索引。

總結

簡單作個總結：索引并不便宜。它是有代價的，而且代價可能是多方面的。索引并不總是好的，順序掃描也并不總是壞的。建議您，避免在改進單個查詢的第一步就去創建索引，因為這是一個滑坡。自上而下的系統調優方法，從調優主機、操作系統、PostgreSQL 參數、數據架構等開始，會產生更好的結果。在創建索引之前，一次客觀的“成本效益分析”是很重要的。