在 PostgreSQL 中創(chuàng)建索引時，為每個列創(chuàng)建一個索引更好，還是為 WHERE 子句的所有列創(chuàng)建一個復(fù)合索引更好？

復(fù)合索引和多個索引

關(guān)于索引的使用，有一個最常見問題：是為每個列創(chuàng)建一個索引更好，還是為WHERE子句的所有列創(chuàng)建一個索引更好？在大多數(shù)情況下，答案非常簡單：一個包含多個列的索引會更好，即多列索引或復(fù)合索引。”多列索引“ 的文檔中有詳細的解釋。

然而，無論您如何定義索引，都存在單個索引無法完美完成的查詢；例如，帶有兩個或多個獨立范圍條件的查詢，如下例所示：

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) < ?
   AND date_of_birth    < ?

在沒有過濾謂詞的情況下，不可能定義出支持此查詢的 B 樹索引。為了解釋，你只需要記住一個索引就是一個鏈表。

如果將索引定義為UPPER(LAST_NAME), DATE_OF_BIRTH（按此順序），則鏈表以 A 開頭，以 Z 結(jié)尾。僅當(dāng)有兩名員工同名時，才會考慮出生日期。如果以DATE_OF_BIRTH, UPPER(LAST_NAME)的方式定義索引，它將從最年長的員工開始，以最年輕的員工結(jié)束。在這種情況下，名稱對排序順序的影響很小。

無論您如何扭轉(zhuǎn)和調(diào)整索引的定義，條目始終沿一條鏈排列。小的條目在一端，大的條目在另一端。因此，一個索引只能支持一個范圍條件作為訪問謂詞。支持兩個獨立的范圍條件需要第二個軸線，比如像一個棋盤。然后，上面的查詢將匹配來自棋盤一角的所有條目，但索引不像一個棋盤 — 它就像一條鏈。沒有角落。

當(dāng)然，您可以接受過濾謂詞，并使用多列索引。不管怎樣，在許多情況下，這是最好的解決方案。然后，索引的定義應(yīng)該首先提及選擇率更高的列，以便它可以同訪問謂詞一起使用。每次創(chuàng)建一個復(fù)合索引時，都必須明智地選擇列的順序。但是，有一種誤解是，您應(yīng)該始終將選擇率最高的列放在第一個位置；那是錯誤的。

重要：在定義一個復(fù)合索引時，最重要的考慮點是如何選擇列的順序，以便可以盡可能頻繁地使用它。

另一種選擇是使用兩個單獨的索引，每個列一個。然后，數(shù)據(jù)庫必須首先掃描這兩個索引，然后合并結(jié)果。只是重復(fù)的索引查找，就已經(jīng)涉及更多的工作了，因為數(shù)據(jù)庫必須遍歷兩個索引樹。此外，數(shù)據(jù)庫需要大量內(nèi)存和 CPU 時間，來組合中間結(jié)果。

注意：一個索引的掃描比兩個索引的掃描要快。

組合多個索引

PostgreSQL 能夠組合多個索引，來處理單個索引掃描無法實現(xiàn)的情況。“組合多個索引” 的文檔中詳細解釋了相關(guān)的算法。

在一個數(shù)據(jù)倉庫的世界中，會有許多不可預(yù)測的臨時查詢。只需單擊幾下，即可將任意條件組合到您選擇的查詢中。無法預(yù)測出WHERE子句中可能出現(xiàn)的列的組合，這使得索引的使用幾乎不可能。

多個索引的優(yōu)點是，它們可以很容易地組合。這意味著在單獨地索引每個列時，您可以獲得不錯的性能。相反，如果您提前知道查詢，以便您可以創(chuàng)建定制的多列 B 樹索引，則它仍然會比組合多個索引更快。

在沒有更好的訪問路徑的情況下，PostgreSQL 會將多個 B 樹索引掃描的結(jié)果轉(zhuǎn)換為內(nèi)存中的位圖結(jié)構(gòu)。這些結(jié)果可以高效地組合起來。位圖結(jié)構(gòu)不是持久化存儲的，而會在語句執(zhí)行后被丟棄，從而避免了寫數(shù)據(jù)時擴展性差的問題。缺點是它需要大量的內(nèi)存和 CPU 時間。畢竟，這種方法也算是優(yōu)化器最后的一種選擇了。