原文地址：https://pganalyze.com/blog/postgres-create-index
原文作者：Lukas Fittl
翻譯：多米爸比

翻譯說明：非完全按照原文進(jìn)行全部翻譯，且原文有一處錯誤，細(xì)心讀者可以對比查找。

前言

大多數(shù)開發(fā)人員面臨這樣的挑戰(zhàn)：他們剛編寫的程序部署到生產(chǎn)環(huán)境后，應(yīng)用系統(tǒng)突然變慢了。經(jīng)過一些工具及監(jiān)控系統(tǒng)的分析，發(fā)現(xiàn)是新編的代碼里使用新的Query語句引起。進(jìn)一步分析根因是由Query不能使用index引起。

那什么時候Query可以使用索引，再Postgres里我們?nèi)绾握_的創(chuàng)建索引呢。本文我們將通過CREATE
INDEX命令詳細(xì)了解它各個有用的方面，同時我們也會分析PostgreSQL查詢的操作符及數(shù)據(jù)類型，這樣我們就可以如何選擇最佳的索引定義。

本文將主要圍繞這張圖展開

我們?nèi)绾蝿?chuàng)建索引

首先，我們來看一下Postgres里面創(chuàng)建索引的基本方式，它的語法是

CREATE INDEX ON [table] ([column1]); 

示例：比如用戶表有一個針對email地址的查詢語句。

SELECT * FROM users WHERE users.email = 'test@example.com';

我們知道這個語句是針對email這一列進(jìn)行查詢，因此我們的索引應(yīng)該創(chuàng)建在用戶表的這個特定列上。

CREATE INDEX ON users (email);

當(dāng)我們執(zhí)行create index on users命令之后，Postgres就幫我們創(chuàng)建好了索引。

需要注意的是：索引只是一個冗余的數(shù)據(jù)結(jié)構(gòu)，也就是說我們即使刪除了剛才創(chuàng)建的索引，我們也不會丟失任何的數(shù)據(jù)。使用索引的好處是幫助我們加速搜索表上特定的一些記錄行。

Postgres如何領(lǐng)會Query

當(dāng)Postgres運行用戶的語句時，會經(jīng)歷多個階段，大概可以分成下面四個階段：

1. Parsing
2. Parse analysis
3. Planning
4. Execution

在上面這些階段里，Query語句不再僅僅只是一個文本串，而是一個語法tree，每個階段都會修改這棵樹以及對它的結(jié)構(gòu)進(jìn)行注解，直到最后的執(zhí)行階段。

為了理解Postgres數(shù)據(jù)庫是如何使用索引，我們首先需要理解Parse analysis階段做了哪些事情。

我們再看稍微復(fù)雜一點的示例：

SELECT * FROM users WHERE users.email = 'test@example.com' AND users.deleted_at IS NULL;

根據(jù)email地址的查詢語句，同時多了一個查詢條件。

我們?nèi)绻蜷_數(shù)據(jù)庫debug_print_parse參數(shù)(生產(chǎn)環(huán)境禁用)，可以從數(shù)據(jù)庫日志看到詳細(xì)的日志內(nèi)容。

LOG:  parse tree:
DETAIL:     {QUERY 
	   ...
	      :quals 
	         {BOOLEXPR 
	         :boolop and 
	         :args (
	            {OPEXPR 
	            :opno 98 
	            :opfuncid 67 
	            :opresulttype 16 
	            :opretset false 
	            :opcollid 0 
	            :inputcollid 100 
	            :args (
	               ...
	            )
	            :location 38
	            }
	            {NULLTEST 
	            :arg 
	               ...
	            :nulltesttype 0 
	            :argisrow false 
	            :location 80
	            }
...

日志輸出的格式很難直接讀懂，但我們可以通過下面的可視化來展示。

我們可以看到兩個重要的解析節(jié)點，WHERE子句里的OpExpr節(jié)點和NullTest節(jié)點。首先來關(guān)注第一個OpExpr節(jié)點。

理解操作符Operators和數(shù)據(jù)類型Data Types

數(shù)據(jù)類型我們一般都很熟悉，這個我們再schema里面創(chuàng)建表時都用到過。操作符Operator定義了一個數(shù)據(jù)類型值或者多個數(shù)據(jù)類型值如何進(jìn)行比較。OpExpr節(jié)點就代表著使用一個操作符去比較一個或多個給定的數(shù)據(jù)類型值的表達(dá)式。

在示例中我們使用的操作符可以從pg_operator系統(tǒng)表查詢。

SELECT oid, oid::regoperator, oprcode, oprnegate::regoperator
  FROM pg_operator
 WHERE oprname = '=' AND oprleft = 'text'::regtype AND oprright = 'text'::regtype;

oid |     oid      | oprcode |   oprnegate   
-----+--------------+---------+---------------
 98 | =(text,text) | texteq  | <>(text,text)
(1 row)

從上面也可以看到操作符對應(yīng)的內(nèi)部實現(xiàn)函數(shù)。如果我們想弄清楚它的實現(xiàn)函數(shù)，可以查看texteq函數(shù)的源碼。

/*
 * Comparison functions for text strings.
 */
Datum
texteq(PG_FUNCTION_ARGS)
{
    ...
    if (lc_collate_is_c(collid) ||
		collid == DEFAULT_COLLATION_OID ||
		pg_newlocale_from_collation(collid)->deterministic)
    {
        ...
    	result = (memcmp(VARDATA_ANY(targ1), VARDATA_ANY(targ2),
				  len1 - VARHDRSZ) == 0);
        ...
    }
    else
	{
        ...
        result = (text_cmp(arg1, arg2, collid) == 0);
        ...
    }
    ...
}

當(dāng)然我們也可以創(chuàng)建自定義的操作符來為自定義數(shù)據(jù)類型服務(wù)。

操作符是創(chuàng)建正確索引最基本且最重要的因素之一。它決定我們?nèi)绾嗡阉鲾?shù)據(jù)表的值。例如我們可以使用等號操作符來匹配輸入值是否與列值相等，也可以使用兩個@符號來對全文搜索列執(zhí)行一個文本text值的匹配。

尋找正確的索引類型

那當(dāng)我們使用不同的索引類型時，它的數(shù)據(jù)結(jié)構(gòu)也限定了它所支持的操作符。比如Postgres里最常用的索引類型是B-tree索引，它支持等號、范圍比較<,?<=,?=>,?>, ~?,?~*。

我們再來看一個例子，users表的全文搜索列上，我們需要使用兩個@操作符來搜索這個列。

SELECT * FROM users WHERE about_text_search @@ to_tsquery('index');

如果我們只是簡單的對這個列創(chuàng)建索引，不指定索引類型，那我們的查詢語句還是會跟沒創(chuàng)建索引一樣繼續(xù)使用全表掃描。

CREATE INDEX ON users(about_text_search);

pgaweb=# EXPLAIN SELECT * FROM users WHERE about_text_search @@ to_tsquery('index');
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on users  (cost=10000000000.00..10000000006.51 rows=1 width=4463)
   Filter: (about_text_search @@ to_tsquery('index'::text))
(2 rows)

因為通用的b-tree索引并沒有支持全文檢索類型數(shù)據(jù)結(jié)構(gòu)，也并沒有支持該操作符的操作符類。我們可以反過來通過全文檢索操作符來確定我們可以使用哪種索引類型。

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
  FROM pg_am am,
       pg_opfamily opf,
       pg_amop amop
 WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid
       AND amop.amopopr = '@@(tsvector,tsquery)'::regoperator;

 index_method | opfamily_name |  opfamily_operator   
--------------+---------------+----------------------
 gist         | tsvector_ops  | @@(tsvector,tsquery)
 gin          | tsvector_ops  | @@(tsvector,tsquery)
(2 rows)

從上面的結(jié)果我們知道可以使用gist或者gin索引。那我們使用using gin創(chuàng)建索引之后，再次執(zhí)行語句，可以看到能使用索引。

=# EXPLAIN SELECT * FROM users WHERE about_text_search @@ to_tsquery('index');
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=8.25..12.51 rows=1 width=4463)
   Recheck Cond: (about_text_search @@ to_tsquery('index'::text))
   ->  Bitmap Index Scan on users_about_text_search_idx1  (cost=0.00..8.25 rows=1 width=0)
         Index Cond: (about_text_search @@ to_tsquery('index'::text))
(4 rows)

前面查詢Postgres內(nèi)部表時，還看到有一個tsvector_ops的名稱，它有什么作用呢？

對于一個確定的操作符，可能會有多種不同的操作符分類，不同的操作符分類是指用于操作特定數(shù)據(jù)類型索引的最小運算符集，每個操作符分類定義著對某個指定索引類型的數(shù)據(jù)展現(xiàn)。

明確指定Operator Classes

我們來查詢前面查詢語句里用到的“=(text,text)”操作符

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
  FROM pg_am am,
       pg_opfamily opf,
       pg_amop amop
 WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid
       AND amop.amopopr = '=(text,text)'::regoperator;

 index_method |  opfamily_name   | opfamily_operator 
--------------+------------------+-------------------
 btree        | text_ops         | =(text,text)
 hash         | text_ops         | =(text,text)
 btree        | text_pattern_ops | =(text,text)
 hash         | text_pattern_ops | =(text,text)
 spgist       | text_ops         | =(text,text)
 brin         | text_minmax_ops  | =(text,text)
 gist         | gist_text_ops    | =(text,text)
(7 rows)

創(chuàng)建索引時如果我們不顯示指定operator class，也可以看到索引對應(yīng)的默認(rèn)operator class。文本類型的operator class是我們通常所使用的。再一些場景下我們也需要設(shè)置特定的operator class。

當(dāng)我們數(shù)據(jù)庫里運行一個like查詢。DB上的collation不是大C，索引是不支持like操作的。

CREATE INDEX ON users (email);

pgaweb=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on users  (cost=10000000000.00..10000000001.26 rows=1 width=4463)
   Filter: ((email)::text ~~ 'lukas@%'::text)
(2 rows)

我們可以看到查詢還是走全表掃描。如果我們進(jìn)行引導(dǎo)，索引也能對這種場景生效：1）我們在數(shù)據(jù)庫創(chuàng)建時指定collation為大C(也就是告訴數(shù)據(jù)庫我們不想綁定具體語言的排序比較規(guī)則)；2）使用文本模式操作符text_pattern_ops。

CREATE INDEX ON users (email text_pattern_ops);

pgaweb=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Scan using users_email_idx on users  (cost=0.14..8.16 rows=1 width=4463)
   Index Cond: (((email)::text ~>=~ 'lukas@'::text) AND ((email)::text ~<~ 'lukasA'::text))
   Filter: ((email)::text ~~ 'lukas@%'::text)
(3 rows)

可以看到明確指定文本模式的操作符后，也能使用索引。

到這里我們知道了對列使用不同的索引類型以及操作符分類。接下來我們將討論創(chuàng)建索引的一些其他方面。

使用多列屬性

再創(chuàng)建索引的語句定義里面，還有一個基本的特性是使用多列選項。

CREATE INDEX ON [table] ([column_a], [column_b]);

對于多列屬性，不同索引類型的數(shù)據(jù)結(jié)構(gòu)有不同的展現(xiàn)形式，有些索引類型比如Hash索引、spgist索引是不支持多列屬性的。

對于B-tree多列索引，列的順序非常重要，如果部分查詢語句只使用column_a，而大部分查詢語句都使用column_b，那我們應(yīng)該把column_b放在前面。如果我們不遵守這個規(guī)則，我們最終的查詢需要跳過所有前導(dǎo)列。另一方面，如果我們使用GIST索引，那這個順序就無所謂了，我們可以以任意順序指定。

還有一個值得討論的問題：我們是創(chuàng)建單個多列索引合適還是創(chuàng)建多個單列索引更好?

CREATE INDEX ON [table] ([column_a]);
CREATE INDEX ON [table] ([column_b]);
--- or
CREATE INDEX ON [table] ([column_a], [column_b]);

如果從單個查詢語句的角度來看，大部分的答案是創(chuàng)建單個多列索引匹配度更高，單個多列索引也會比多個索引更快。但是如果我們是一個很大的工作負(fù)載，我們應(yīng)該需要創(chuàng)建多個單列索引，同時我們也要意識到Postgres在這種場景下也會做更多的工作，我們也應(yīng)該通過explain命令或者來實際驗證索引的選擇情況。

使用函數(shù)及表達(dá)式索引

除了對指定列創(chuàng)建索引，我們也可以對列的表達(dá)式創(chuàng)建索引，比較典型的例子是email嵌套小寫函數(shù)之后的比較。

SELECT * FROM users WHERE lower(email) = $1

如果我們簡單的對email列創(chuàng)建索引，通過explain去觀察，會發(fā)現(xiàn)并不會走索引，因為它并不匹配表達(dá)式。

我們知道lower函數(shù)是一個“immutable”屬性的函數(shù)，所以我們可以創(chuàng)建函數(shù)表達(dá)式索引。

CREATE INDEX ON users (lower(email));

使用表達(dá)式索引之后，我們的查詢就可以使用索引了。注意并不是所有的函數(shù)都可用于創(chuàng)建表達(dá)式索引，比如使用now函數(shù)創(chuàng)建就會報錯。

CREATE INDEX ON users (now());

ERROR:  functions in index expression must be marked IMMUTABLE

另外需要注意創(chuàng)建了表達(dá)式索引后，只有符合表達(dá)式的語句才能使用索引，如果只是引用獨立的列，是不會走索引的。

運用WHERE子句創(chuàng)建部分索引

我們再回到剛開始的介紹，我們介紹了兩個節(jié)點，第一個OpExpr節(jié)點已經(jīng)介紹過了。我們再來看一下NullTest表達(dá)式。

我們的應(yīng)用查詢只需要返回沒有被標(biāo)記為刪除的數(shù)據(jù)。根據(jù)我們的工作負(fù)載，有可能需要被忽略的記錄（也就是打了刪除標(biāo)記的記錄）非常多時，我們可以在創(chuàng)建索引時包含刪除標(biāo)記這一列，我們實際不需要關(guān)注有刪除標(biāo)記的數(shù)據(jù)。因為如果包含在索引條目里，那將是非常大的空間浪費。

Postgres針對這種場景有更好的解決方式：使用部分索引，我們可以限制索引條目的數(shù)據(jù)行。當(dāng)然如果沒有這個限制，數(shù)據(jù)行不會保存到索引中，這會節(jié)省空間。在查詢執(zhí)行期間，在很多案例里部分索引可以節(jié)省大量時間花銷的，因為計劃生成器會做簡單的檢測，判定部分索引是否匹配，忽略不匹配的所有數(shù)據(jù)。

實際生產(chǎn)中，我們需要做的是在索引的定義里增加一個where子句。

CREATE INDEX ON users(email) WHERE deleted_at IS NULL;

需要注意的是：面臨下面兩個問題時我們應(yīng)該放棄使用部分索引。
第一：添加了部分索引的限制，意味著只有包含表達(dá)式的語句才可以使用索引。那如果我們既需要查詢帶限制條件，也需要不帶限制條件，那我們可能要創(chuàng)建兩個索引。
第二：添加大量的部分索引也會加重Postgres計劃生成器的花銷，因為它需要做大量昂貴的分析來判定應(yīng)該使用哪個索引。

使用INCLUDE關(guān)鍵字創(chuàng)建Covering Index

下面我們介紹Postgres索引的另外一個新特性，使用include關(guān)鍵字來創(chuàng)建覆蓋索引，讓更多場景可以使用index-only掃描。

首先我們來看下index掃描和index-only掃描的區(qū)別。index-only掃描可以直接從索引中獲取數(shù)據(jù)，而不需要再從磁盤中去讀取數(shù)據(jù)。

另外index-only只有當(dāng)表最近做過vacuum操作才會起作用。不然Postgres需要頻繁的對每個索引條目去檢查可見性，這樣大多情況下Postgres會優(yōu)先選擇index scan。

讓我們來看下面兩個例子，上邊的語句能使用index-only scan，下邊的語句不能，因為查詢的列不一樣。

CREATE INDEX ON users (email, id);

=# EXPLAIN SELECT id FROM users WHERE email = 'test@example.com';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Index Only Scan using users_email_id_idx on users  (cost=0.14..4.16 rows=1 width=4)
   Index Cond: (email = 'test@example.com'::text)
(2 rows)

=# EXPLAIN SELECT id, fullname FROM users WHERE email = 'test@example.com';
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Index Scan using users_email_id_idx on users  (cost=0.14..8.15 rows=1 width=520)
   Index Cond: ((email)::text = 'test@example.com'::text)
(2 rows)

如果想讓下邊的語句也能使用index-only scan，在我們創(chuàng)建多列索引時，可以把fullname列添加進(jìn)去。

CREATE INDEX ON users (email, id, fullname);

=# EXPLAIN SELECT id, fullname FROM users WHERE email = 'test@example.com';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Index Only Scan using users_email_id_fullname_idx on users  (cost=0.14..4.16 rows=1 width=520)
   Index Cond: (email = 'test@example.com'::text)
(2 rows)

然而這樣做卻有一些小小的限制：比如我們要使用唯一索引、索引可能會發(fā)生膨脹。

此時使用include關(guān)鍵字會是一個更好的選擇：

CREATE INDEX ON users (email, id) INCLUDE (fullname);

這會讓我們的索引更加輕量，也不用擔(dān)心唯一約束的問題，同時include添加的列也很明確，就是為了支持index only scan。

這個特性也需要非常節(jié)儉的去使用：因為隨著我們添加更多的數(shù)據(jù)到索引中，索引的值也會非常的大，這也會產(chǎn)生問題，添加太多的列到include子句中其實并不是一個很好的方法。

生產(chǎn)環(huán)境安全地添加及刪除索引

生產(chǎn)環(huán)境下，如果我們不使用CONCURRENTLY參數(shù)，則會獲取exclusive lock，會阻塞表的讀寫操作。Postgres能夠在不獲取任何鎖的情況下添加索引。它使用CONCURRENTLY參數(shù)來執(zhí)行此操作。

CREATE INDEX CONCURRENTLY ON users (email) WHERE deleted_at IS NULL;

刪除索引操作也是使用同樣的關(guān)鍵字。

小結(jié)

通過本文我們應(yīng)該基本了解與索引相關(guān)的操作符和操作符類，了解這些概念對于復(fù)雜查詢創(chuàng)建最佳索引至關(guān)重要。我們還研究了CREATE INDEX命令的一些補充功能特性，這些特性為Postgre判定使用哪個索引非常關(guān)鍵。

本文實際上還有一些方面沒有討論：比如使用特定表空間創(chuàng)建索引，使用索引存儲參數(shù)（尤其是對GIN索引類型特別有用?。┮约爸付械呐判蝽樞?。作者也非常鼓勵大家通過Postgres文檔進(jìn)一步閱讀本文的一些概念。

保持聯(lián)系

從2019年12月開始寫第一篇文章，分享的初心一直在堅持，本人現(xiàn)在組建了一個PG樂知樂享交流群，歡迎關(guān)注我文章的小伙伴進(jìn)群吹牛嘮嗑，交流技術(shù)，互贊文章。

如果群二維碼失效可以加我微信。