不得不說，管理一個大型數(shù)據(jù)庫系統(tǒng)，需要有相當(dāng)高的技術(shù)和業(yè)務(wù)水平。比如說，到底什么時候才應(yīng)該創(chuàng)建索引？這個問題，可能沒有大多數(shù)人想象的那么簡單。

介紹

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

在非?；镜膶用嫔?，我們應(yīng)該記住，索引不是免費的。它在帶來好處的同時，也伴隨著性能和資源消耗方面的成本。以下是過度使用索引可能導(dǎo)致的十種問題/開銷的列表。這篇文章是關(guān)于 PostgreSQL 的，但大多數(shù)問題也適用于其他數(shù)據(jù)庫系統(tǒng)。

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

索引會拖慢事務(wù)

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

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

內(nèi)存使用情況

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

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

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

索引比表需要更多的緩存

由于隨機的寫和讀，索引需要更多頁面才能包含在緩存中。索引對緩存的要求通常遠(yuǎn)高于關(guān)聯(lián)的表。

WAL 生成

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

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

越來越多的 I/O

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

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

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

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

對 VACUUM/AUTOVACUUM 的影響

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

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

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

調(diào)優(yōu)時的隧道視野

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

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

更大的存儲需求

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

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

索引更容易損壞

這不只是在談?wù)撃切┖苌侔l(fā)生的與索引相關(guān)的軟件錯誤，例如 PostgreSQL 14 存在的索引損壞，或由于 glibc 排序規(guī)則更改導(dǎo)致的索引損壞，這些錯誤會時不時地出現(xiàn)，甚至在今天也會影響許多環(huán)境。隨著索引數(shù)量的增加，發(fā)生索引損壞的概率也會增加。

我們該怎么辦？

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

現(xiàn)有的索引也需要在一段時間內(nèi)進(jìn)行嚴(yán)格審查。應(yīng)考慮刪除所有未使用的索引（pg_stat_user_indexes 中 idx_scan 為零的索引）。像 pgexperts 這樣的腳本可以幫助進(jìn)行更多分析。

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

總結(jié)

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