PostgreSQL的常用索引

索引其實就是一種資料結構，將資料庫中的資料以一定的資料結構演算法進行儲存。當表資料量越來越大時查詢速度會下降，建立合適的索引能夠幫助我們快速的檢索資料庫中的資料， 快速定位到可能滿足條件的記錄，不需要遍歷所有記錄。

索引自身也佔用儲存空間、消耗計算資源，建立過多的索引將對資料庫效能造成負面影響（尤其影響資料匯入的效能，建議在資料匯入後再建索引）。因此，僅在必要時建立索引。postgresql裡的所有索引都是“從屬索引”，也就是索引在物理上與它描述的表檔案分離。索引是一種資料庫物件，每個索引在pg_class裡都有記錄。不同種類的索引有著不同的訪問方法和內部構造。PG裡所有的索引訪問方法都透過頁面來組織索引的內部結構。從本質來講，索引是一些資料的鍵值和元組識別符號（TID）之間的對映。在查詢資料時如果一個page中的每條資料都能有助於定位資料記錄的位置，這將會減少磁碟I/O次數，提高查詢效率。（想了解索引頁內容的話，可以透過pageinspect外掛）。

PostgreSQL 擁有眾多開放特性，例如開放的資料型別介面，除了傳統資料庫支援的型別，還支援GIS，JSON，RANGE，IP，ISBN，影像特徵值，化學，DNA等等擴充套件的型別，使用者還可以根據實際業務擴充套件更多的型別。不同的索引介面針對的資料型別、業務場景是不一樣的，接下來針對常用索引，介紹一下它的原理和應用場景。

索引型別
pg_am儲存關於關係訪問方法的資訊。系統支援的每種訪問方法在這個目錄中都有一行
postgres=# SELECT * FROM pg_am where amtype=‘i’;

oid  | amname |  amhandler  | amtype
------+--------+-------------+--------
  403 | btree  | bthandler   | i
  405 | hash   | hashhandler | i
  783 | gist   | gisthandler | i
2742 | gin    | ginhandler  | i
4000 | spgist | spghandler  | i
3580 | brin   | brinhandler | i

pg_am為每一種索引方法都包含一行（內部被稱為訪問方法）。PostgreSQL中內建了對錶 常規訪問的支援，但是所有的索引方法則是在pg_am中描述。可以透過編寫必要的程式碼並且 在pg_am中建立一項來增加一種新的索引訪問方法. 一個索引方法的例程並不直接瞭解它將要操作的資料型別。而是由一個運算子類標識索引方 法用來操作一種特定資料型別的一組操作。

PostgreSQL提供了多種索引型別： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN等。
每一種索引型別使用了 一種不同的演算法來適應不同型別的查詢。預設情況下， CREATE INDEX命令 建立適合於大部分情況的B-tree 索引。

一、B-tree
B-tree可以在可排序資料上的處理等值和範圍查詢。特別地，PostgreSQL的查詢規劃器會 在任何一種涉及到以下運算子的已索引列上考慮使用B-tree索引： < <= = >= > 將這些運算子組合起來，例如BETWEEN和IN，也可以用B-tree索引搜尋實現。同樣，在索引 列上的IS NULL或IS NOT NULL條件也可以在B-tree索引中使用。

B樹是平衡的，也就是說，每個葉頁面與根都由相同數量的內部頁面分隔開。因此，搜尋任何值都需要花費相同的時間。
B樹是多分支的，即每個頁面（通常為8 KB）包含許多（數百個）ctid。因此，B樹的深度很小，對於非常大的表，實際上可以達到4–5的深度。 當欄位超過單個索引頁的1/4時，不適合b-tree索引。

一個簡單的單欄位的btree索引大致如上圖所示，索引的第一頁是一個元頁，它引用索引根。內部節點位於根下方，葉子頁面位於最底行。向下箭頭表示從葉節點到錶行（ctid）的引用。PostgreSQL的btree索引是可以儲存null值的(oracle中不可以)。並支援按條件IS NULL和IS NOT NULL進行搜尋。在索引中null值儲存在索引的一端，取決於建立索引時指定nulls first還是nulls last。

如果查詢包括排序，則這一點很重要：如果SELECT命令在其ORDER BY子句中指定的NULL順序與構建索引指定的順序相同（NULLS FIRST或NULLS LAST），則可以使用索引，否則將無法使用索引。不過在資料庫中null是無法和其它值進行比較的，例如：

ysla@ysla=>select null < 10; 
?column?
---------- (1 row)

PostgreSQL的B-tree索引與Oracle的B-tree索引區別還比較大，就我目前發現的區別來說，主要是以下4點：
1.PostgreSQL中索引會儲存NULL，而Oracle不會；
2.PostgreSQL中建立索引時，可以使用where來建立部分索引，而Oracle不能；
3.PostgreSQL中可以對同一列建立兩個相同的索引，而Oracle不能；
4.PostgreSQL中可以使用concurrently關鍵字達到建立索引時不阻塞表的DML的功能，Oracle也有online引數實現類似的功能。

使用concurrently關鍵字建立索引的各階段如下：

1、開啟事務1，拿到當前snapshot1。
2、掃描B表前，等待所有修改過B表（寫入、刪除、更新）的事務結束。
3、掃描B表，並建立索引。
4、結束事務1。
5、開啟事務2，拿到當前snapshot2。
6、再次掃描B表前，等待所有修改過B表（寫入、刪除、更新）的事務結束。
7、在snapshot2之後啟動的事務對B表執行的DML，會修改這個idx_b_2的索引。
8、再次掃描B表，更新索引。（從TUPLE中可以拿到版本號，在snapshot1到snapshot2之間變更的記錄，將其合併到索引）
9、上一步更新索引結束後，等待事務2之前開啟的持有snapshot的事務結束。
10、結束索引建立。索引可見。

注意點：
1.此選項只能指定一個索引的名稱
2.普通CREATE INDEX命令可以在事務內執行，但是CREATE INDEX CONCURRENTLY不可以在事務內執行
3.列存表、分割槽表和臨時表不支援CONCURRENTLY方式建立索引。

為了減少等待的時間，儘量避免建立索引過程中，兩次SCAN之前對被建立索引表實施長事務，並且長事務中包含修改被建立索引的表。在第二次SCAN前，儘量避免開啟長事務。

二、hash

適用場景：
hash索引儲存的是被索引欄位VALUE的雜湊值，只支援等值查詢。
hash索引特別適用於欄位VALUE非常長（不適合b-tree索引，因為b-tree一個PAGE至少要儲存3個ENTRY，所以不支援特別長的VALUE）的場景，例如很長的字串，並且使用者只需要等值搜尋，建議使用hash index。

在pg10之前是不提倡使用hash索引的，因為hash索引不會寫wal日誌。不過從pg10開始解決了這一問題，並且對hash索引進行了一些加強

hash索引其主要目的就是對於某些資料型別（索引鍵）的值，我們的任務是快速找到匹配的行的ctid。
當插入索引時，讓我們計算鍵的雜湊函式。PostgreSQL中的雜湊函式總是返回integer型別，其範圍為2的32次方≈40億個值。儲存桶的數量最初等於2，然後根據資料大小動態增加。bucket編號可以使用位演算法從雜湊碼中計算出來。這是我們將放置ctid的bucket。當搜尋索引時，我們計算鍵的雜湊函式並獲取bucket編號。現在，仍然需要遍歷bucket的內容，並僅返回具有適當雜湊碼的匹配ctid。由於儲存的“hash code - ctid”對是有序的，因此可以高效地完成此操作。

如圖所示，雜湊索引包含4種頁：meta page, primary bucket page, overflow page, bitmap page。

meta page（0號頁） , 包含了HASH索引的控制資訊，指導如何找到其他頁面（每個bucket的primary page）
index將儲存劃分為多個bucket（邏輯概念），每個bucket中包含若干page（每個bucket的page數量不需要一致），當插入資料時，根據計算得到的雜湊，透過對映演算法，對映到某個bucket，也就是說資料首先知道應該插入哪個bucket中，然後插入bucket中的primary page，如果primary page空間不足時，會擴充套件overflow page，資料寫入overflow page。在page中，資料是有序儲存（TREE），page內支援二分查詢(binary search)，而page與page之間是不保證順序的，所以hash index不支援order by。

overflow page，是bucket裡面的頁，當primary page沒有足夠空間時，擴充套件的塊稱為overflow page。

bimap page，記錄primary , overflow page是否為空可以被重用。

注意bucket, page都沒有提供收縮功能，即無法從OS中收縮空間，但是提供了reuse(透過 bitmap page跟蹤），如果想要減小索引大小的唯一辦法就是使用REINDEX或VACUUM FULL命令從頭開始重建索引。

三、GIN
GIN(Generalized Inverted Index, 通用倒排索引) 是一個儲存對(key, posting list)集合的索引結構。
倒排索引來源於搜尋引擎的技術，正是有了倒排索引技術，搜尋引擎才能有效率的進行資料庫查詢、刪除等操作。

正排表結構如圖所示，這種組織方法在建立索引的時候結構比較簡單，建立比較方便且易於維護;因為索引是基於文件建立的，若是有新的文件加入，直接為該文件建立一個新的索引塊，掛接在原來索引檔案的後面。若是有文件刪除，則直接找到該文件號文件對應的索引資訊，將其直接刪除。但是在查詢的時候需對所有的文件進行掃描以確保沒有遺漏，這樣就使得檢索時間大大延長，檢索效率低下。儘管正排表的工作原理非常的簡單，但是由於其檢索效率太低，除非在特定情況下，否則實用性價值不大。

倒排表以字或詞為關鍵字進行索引，表中關鍵字所對應的記錄表項記錄了出現這個字或詞的所有文件，一個表項就是一個字表段，它記錄該文件的ID和字元在該文件中出現的位置情況。
由於每個字或詞對應的文件數量在動態變化，所以倒排表的建立和維護都較為複雜，但是在查詢的時候由於可以一次得到查詢關鍵字所對應的所有文件，所以效率高於正排表。在全文檢索中，檢索的快速響應是一個最為關鍵的效能，而索引建立由於在後臺進行，儘管效率相對低一些，但不會影響整個搜尋引擎的效率。倒排表的結構圖如下圖。

GIN是一個儲存對(key, posting list)集合的索引結構
eg.(‘hello’, ‘14:2 23:4’)
posting list：一個key出現的物理位置(heap ctid,堆錶行號)的連結串列

GIN索引常用於查詢索引欄位中的部分元素值，如在text型別和json型別欄位中檢索某個關鍵字。相同的鍵值只儲存一次。
在PG中，GIN索引會為每一個鍵建立一個B-tree索引，這會導致GIN索引的更新速度非常慢，因為插入或更新一條記錄，所有相關鍵值的索引都會被更新。

PG提供gin_pending_list_limit引數來控制GIN索引的更新速度
適當將maintenance_work_mem引數增大，可以加快GIN索引的建立過程
如果查詢返回的結果集特別大，則可以用gin_fuzzy_search_limit引數來控制返回的行數，預設為0，不限制，一般建議設定為5000~20000比較合適。

四、gist

GIST 索引不是單獨一種索引型別，而是一種架構，可以在這種架構上實現很多不同的索引策略。因此，可以使用 GIST索引的特定運算子型別高度依賴於索引策略(運算子類)
GIST是廣義搜尋樹generalized search tree的縮寫。這是一個平衡搜尋樹。
用於解決一些B-tree, GIN難以解決的資料減少問題，例如，範圍是否相交，是否包含，地理位置中的點面相交，或者按點搜尋附近的點 。

五、spgist

SP代表空間分割槽。這裡的空間通常就是我們所說的空間，例如，一個二維平面。但我們會發現，任何搜尋空間，實際上都是任意值域。不相交的特性簡化了在插入和搜尋時的決策。另一方面，作為規則，樹是低分枝的。例如，四叉樹的一個節點通常有四個子節點(與b樹不同，b樹的節點有數百個)，而且深度更大。像這樣的樹很適合在記憶體中工作，但索引儲存在磁碟上，因此，為了減少I/O操作的數量，必須將節點打包到頁中。此外，由於分支深度的不同，在索引中找到不同值所需的時間也會不同。

六、brin
BRIN 索引是塊級索引，有別於B-TREE等索引，BRIN記錄並不是以行號為單位記錄索引明細，而是記錄每個資料塊或者每段連續的資料塊的統計資訊。因此BRIN索引空間佔用特別的小，對資料寫入、更新、刪除的影響也很小。

BRIN屬於LOSSLY索引，當被索引列的值與物理儲存相關性很強時，BRIN索引的效果非常的好。例如時序資料，在時間或序列欄位建立BRIN索引，進行等值、範圍查詢時效果很好。與我們已經熟悉的索引不同，BRIN避免查詢絕對不合適的行，而不是快速找到匹配的行。BRIN是一個不準確的索引:不包含錶行的tid。

表被分割成ranges(好多個pages的大小)：因此被稱作block range index(BRIN)。在每個range中儲存資料的摘要資訊。作為規則，這裡是最小值和最大值，但有時也並非如此。假設執行了一個查詢，該查詢包含某列的條件;如果所查詢的值沒有進入區間，則可以跳過整個range;但如果它們確實在，所有塊中的所有行都必須被檢視以從中選擇匹配的行。在後設資料頁和摘要資料之間，是reverse range map頁(revmap)。是一個指向相應索引行的指標(TIDs)陣列。

在BRIN索引中，PostgreSQL會為每個8k大小的儲存資料頁面讀取所選列的最大值和最小值，然後將該資訊（頁碼以及列的最小值和最大值）儲存到BRIN索引中。一般可以不把BRIN看作索引，而是看作順序掃描的加速器。 如果我們把每個range都看作是一個虛擬分割槽，那麼我們可以把BRIN看作分割槽的替代方案。BRIN適合單值型別，當被索引列儲存相關性越接近1或-1時，資料儲存越有序，塊的邊界越明顯，BRIN索引的效果就越好。