PostgreSQL Partial Index - 通過查詢條件索引選定的行

說起「 Partial Index 」，估計很多人沒聽說過。在 PostgreSQL 中，它的含義是指：通過查詢條件索引選定的行，而不是所有的行。雖然 MySQL 也有此概念，但是其更接近前綴索引的含義：比如你想索引一個 VARCHAR(255) 的字段，根據數據分布情況，你可以僅索引前面若干個字符，如此通過降低索引體積來達到提升性能的目的。

例一：

有一個 users 表，里面有一個 mobile 字段，缺省值為 null。用戶可以不填寫手機號，如果填寫，每個手機號只能關聯一個用戶，怎么辦？

聽起來這就是一個唯一索引，但是直接創建的話肯定會失敗，因為字段里有空值，這個問題放到 MySQL 里就無解了，只能通過新建一個 users_mobiles 表來約束唯一性。下面看看在 PostgreSQL 中如何利用 Partial Index 來搞定：

sql> CREATE UNIQUE INDEX on users (mobile)
     WHERE mobile IS NOT NULL;

通過在創建索引的時候過濾掉無關的數據，從而實現創建唯一索引的目的。

例二：

有一個 questions 表，里面有一個 created_at 字段表示創建時間，一個 answer_count 字段表示答案數量，查詢不同時間范圍里答案數量大于 10 個的問題：

sql> SELECT * FROM questions
     WHERE answer_count > 10 AND created_at > 1455555555
     LIMIT 100;

插播知識廣告：查詢分為兩種類型：其一是 point 查詢，比如 foo = 123；其二是 range 查詢，比如 foo > 123。通常，一個索引里只能用到一個 range 類型的查詢字段。

如此說來，本例如何創建索引呢？因為兩個查詢條件都是 range 類型的，所以一般情況下創建索引時必須二選一。下面看看在 PostgreSQL 中如何利用 Partial Index 來搞定：

先看看沒有使用 Partial Index 時查詢的效果：

sql> CREATE INDEX created_at on questions (created_at);

沒有使用 Partial Index

再看看使用 Partial Index 時查詢的效果：

sql> CREATE INDEX partial_index on questions (created_at)
     WHERE answer_count > 10;

使用 Partial Index

對比前后兩次查詢可以發現，在沒有使用 Partial Index 的時候，只能先通過索引拿到結果，然后再通過 Filter 過濾另一個條件；而在使用 Partial Index 的時候，可以直接通過 Partial Index 拿到結果，無需二次過濾，在本例中查詢效率有百倍提升。

當然了，只有那些相對固定的條件適合用在 Partial Index 中，比如本例中，答案數量大于 10 的條件是作為熱門問題的判斷依據存在的，是明確的業務邏輯，如此則適合；而另一個創建時間的條件這是頻繁變化的，如此則不適合。

推薦閱讀： Handling Growth with Postgres: 5 Tips From Instagram 。

來自： http://huoding.com/2016/04/28/510