前面介紹了 PostgreSQL 數(shù)據(jù)類型和運(yùn)算符、常用函數(shù)、鎖操作、執(zhí)行計(jì)劃、視圖與觸發(fā)器、存儲(chǔ)過(guò)程相關(guān)的知識(shí)點(diǎn)，今天將為大家介紹?PostgreSQL 索引?相關(guān)知識(shí)，希望大家能夠從中收獲多多！如有幫助，請(qǐng)點(diǎn)在看、轉(zhuǎn)發(fā)支持一波?。。?/p>

概述

索引主要被用來(lái)提升數(shù)據(jù)庫(kù)性能，不當(dāng)?shù)氖褂脮?huì)導(dǎo)致性能變差。 PostgreSQL 提供了多種索引類型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一種索引類型使用了一種不同的算法來(lái)適應(yīng)不同類型的查詢。默認(rèn)情況下，CREATE INDEX 命令創(chuàng)建適合于大部分情況的 B-tree 索引。

• B-樹(shù)（默認(rèn)）：B-樹(shù)是一個(gè)自平衡樹(shù)（self-balancing tree），按照順序存儲(chǔ)數(shù)據(jù)，支持對(duì)數(shù)時(shí)間復(fù)雜度（O(logN)）的搜索、插入、刪除和順序訪問(wèn)。

• 哈希：哈希索引（Hash index）只能用于簡(jiǎn)單的等值查找（=），也就是說(shuō)索引字段被用于等號(hào)條件判斷。因?yàn)閷?duì)數(shù)據(jù)進(jìn)行哈希運(yùn)算之后不再保留原來(lái)的大小關(guān)系。

• GiST：GiST 代表通用搜索樹(shù)（Generalized Search Tree），GiST 索引單個(gè)索引類型，而是一種支持不同索引策略的框架。GiST 索引常見(jiàn)的用途包括幾何數(shù)據(jù)的索引和全文搜索。

• SP-GiST：SP-GiST 代表空間分區(qū) GiST，主要用于 GIS、多媒體、電話路由以及 IP 路由等數(shù)據(jù)的索引。與 GiST 類似， SP-GiST 也支持“最近鄰”搜索。

• GIN：GIN 代表廣義倒排索引（generalized inverted indexes），主要用于單個(gè)字段中包含多個(gè)值的數(shù)據(jù)，例如 hstore、 array、 jsonb 以及 range 數(shù)據(jù)類型。一個(gè)倒排索引為每個(gè)元素值都創(chuàng)建一個(gè)單獨(dú)的索引項(xiàng)，可以有效地查詢某個(gè)特定元素值是否存在。Google、百度這種搜索引擎利用的就是倒排索引。

• BRIN：BRIN 代表塊區(qū)間索引（block range indexes），存儲(chǔ)了連續(xù)物理范圍區(qū)間內(nèi)的數(shù)據(jù)摘要信息。BRIN 也相比 B-樹(shù)索引要小很多，維護(hù)也更容易。對(duì)于不進(jìn)行水平分區(qū)就無(wú)法使用 B-樹(shù)索引的超大型表，可以考慮 BRIN。

在使用上，除了常見(jiàn)的單列索引，還有多列索引、唯一索引、表達(dá)式索引、部分索引、覆蓋索引等。更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(xù)更新中。

• 多列索引：目前，只有 B-tree、GiST、GIN 和 BRIN 索引類型支持多列索引，最多可以指定32個(gè)列（該限制可以在源代碼文件?pg_config_manual.h?中修改，但是修改后需要重新編譯PostgreSQL）。

• 唯一索引：目前，只有 B-tree 能夠被聲明為唯一。

• 表達(dá)式索引：從表的一列或多列計(jì)算而來(lái)的一個(gè)函數(shù)或者標(biāo)量表達(dá)式。索引表達(dá)式的維護(hù)代價(jià)較為昂貴，因?yàn)樵诿恳粋€(gè)行被插入或更新時(shí)都得為它重新計(jì)算相應(yīng)的表達(dá)式。然而，索引表達(dá)式在進(jìn)行索引搜索時(shí)卻不需要重新計(jì)算，因?yàn)樗鼈兊慕Y(jié)果已經(jīng)被存儲(chǔ)在索引中了。

• 部分索引：一個(gè)部分索引是建立在表的一個(gè)子集上，而該子集則由一個(gè)條件表達(dá)式（被稱為部分索引的謂詞）定義。而索引中只包含那些符合該謂詞的表行的項(xiàng)。使用部分索引的一個(gè)主要原因是避免索引公值（查詢結(jié)果行在一個(gè)表中占比超過(guò)一定百分比的值不會(huì)使用索引）。

• 覆蓋索引：目前，B-樹(shù)索引總是支持只用索引的掃描。GiST 和 SP-GiST 索引只對(duì)某些操作符類支持只用索引的掃描。其他索引類型不支持這種掃描。僅訪問(wèn)索引就可獲取查詢所需的全部數(shù)據(jù)，無(wú)需回表（Index-Only Scan）。

語(yǔ)法

CREATE?[?UNIQUE?]?INDEX?[?CONCURRENTLY?]?[?[?IF?NOT?EXISTS?]?name?]?ON?[?ONLY?]?table_name?[?USING?method?]
????(?{?column_name?|?(?expression?)?}?[?COLLATE?collation?]?[?opclass?[?(?opclass_parameter?=?value?[,?...?]?)?]?]?[?ASC?|?DESC?]?[?NULLS?{?FIRST?|?LAST?}?]?[,?...]?)
????[?INCLUDE?(?column_name?[,?...]?)?]
????[?WITH?(?storage_parameter?[=?value]?[,?...?]?)?]
????[?TABLESPACE?tablespace_name?]
????[?WHERE?predicate?]

UNIQUE：唯一索引，在索引被創(chuàng)建時(shí)（如果數(shù)據(jù)已經(jīng)存在）或者加入數(shù)據(jù)時(shí)檢查重復(fù)值。

CONCURRENTLY：在構(gòu)建索引時(shí)不會(huì)取得任何會(huì)阻止該表上并發(fā)插入、更新或者刪除的鎖。而標(biāo)準(zhǔn)的索引構(gòu)建將會(huì)把表鎖住以阻止對(duì)表的寫(xiě)（但不阻塞讀），這種鎖定會(huì)持續(xù)到索引創(chuàng)建完畢。

IF NOT EXISTS：如果一個(gè)同名關(guān)系已經(jīng)存在則不要拋出錯(cuò)誤。

INCLUDE：指定一個(gè)列的列表，其中的列將被包括在索引中作為非鍵列。不能作為索引掃描的條件，主要作用是相關(guān)數(shù)據(jù)索存儲(chǔ)在索引中，訪問(wèn)時(shí)無(wú)需訪問(wèn)該索引的基表。當(dāng)前，有B-樹(shù)和GiST索引訪問(wèn)方法支持這一特性。

name：要?jiǎng)?chuàng)建的索引名稱。這里不能包括模式名，因?yàn)樗饕偸潜粍?chuàng)建在其基表所在的模式中。如果索引名稱被省略，PostgreSQL 將基于基表名稱和被索引列名稱選擇一個(gè)合適的名稱。

ONLY：如果該表是分區(qū)表，指示不要在分區(qū)上遞歸創(chuàng)建索引。默認(rèn)會(huì)遞歸創(chuàng)建索引。

table_name：要被索引的表的名稱（可以被模式限定）。

method：要使用的索引方法的名稱。可以選擇 btree、hash、 gist、spgist、gin以及brin。默認(rèn)方法是 btree。

column_name：一個(gè)表列的名稱。

expression：一個(gè)基于一個(gè)或者更多個(gè)表列的表達(dá)式。如語(yǔ)法中所示，表達(dá)式通常必須被寫(xiě)在圓括號(hào)中。不過(guò)，如果該表達(dá)式是一個(gè)函數(shù)調(diào)用的形式，圓括號(hào)可以被省略。

collation：要用于該索引的排序規(guī)則的名稱。

opclass：一個(gè)操作符類的名稱。

opclass_parameter：運(yùn)算符類參數(shù)的名稱。

ASC：指定上升排序（默認(rèn)）。

DESC：指定下降排序。

NULLS FIRST：指定把空值排序在非空值前面。在指定DESC時(shí)，這是默認(rèn)行為。

NULLS LAST：指定把空值排序在非空值后面。在沒(méi)有指定DESC時(shí)，這是默認(rèn)行為。

storage_parameter：索引方法相關(guān)的存儲(chǔ)參數(shù)的名稱?？蛇x的WITH子句為索引指定存儲(chǔ)參數(shù)。每一種 索引方法都有自己的存儲(chǔ)參數(shù)集合。

B-樹(shù)、哈希、GiST以及SP-GiST索引方法都接受這個(gè)參數(shù)：

fillfactor (integer):索引的填充因子是一個(gè)百分?jǐn)?shù)，它決定索引方法將嘗試填充索引頁(yè)面的充滿程度。對(duì)于B-樹(shù)，在初始的索引構(gòu)建過(guò)程中，葉子頁(yè)面會(huì)被填充至該百分?jǐn)?shù)，當(dāng)在索引右端擴(kuò)展索引（增加新的最大鍵值）時(shí)也會(huì)這樣處理。如果頁(yè)面后來(lái)被完全填滿，它們就會(huì)被分裂，導(dǎo)致索引的效率逐漸退化。B-樹(shù)使用了默認(rèn)的填充因子 90，但是也可以選擇為 10 到 100 的任何整數(shù)值。如果表是靜態(tài)的，那么填充因子 100 是最好的，因?yàn)樗梢宰屗饕奈锢沓叽缱钚』５菍?duì)于更新負(fù)荷很重的表，較小的填充因子有利于最小化對(duì)頁(yè)面分裂的需求。其他索引方法以不同但是大致類似的方式使用填充因子，不同方法的默認(rèn)填充因子也不相同。

deduplicate_items (boolean)：B 樹(shù)重復(fù)數(shù)據(jù)刪除技術(shù)的使用。設(shè)置為 ON 或 OFF 以啟用或禁用優(yōu)化。默認(rèn)值為ON。

vacuum_cleanup_index_scale_factor：指定在以前的統(tǒng)計(jì)信息收集過(guò)程中計(jì)數(shù)到的堆元組總數(shù)的一個(gè)分?jǐn)?shù)，插入不超過(guò)這一數(shù)量所代表的元組不會(huì)導(dǎo)致VACUUM清理階段的索引掃描。這個(gè)設(shè)置當(dāng)前僅適用于B-樹(shù)索引。

buffering (enum)：適用于 GiST 索引，決定是否用緩沖構(gòu)建技術(shù)來(lái)構(gòu)建索引。OFF 會(huì)禁用它，ON 則啟用該特性，如果設(shè)置為 AUTO 則初始會(huì)禁用它，但是一旦索引尺寸到達(dá) effective_cache_size 就會(huì)隨時(shí)打開(kāi)。默認(rèn)值是 AUTO。

fastupdate (boolean)：適用于 GIN 索引，這個(gè)設(shè)置控制快速更新技術(shù)的使用。它是一個(gè)布爾參數(shù)：ON 啟用快速更新，OFF 禁用。默認(rèn)是 ON。

gin_pending_list_limit (integer)：適用于 GIN 索引，設(shè)置 fastupdate 被啟用時(shí)可以使用的 GIN 索引的待處理列表的最大尺寸。 如果該列表增長(zhǎng)到超過(guò)這個(gè)最大尺寸，會(huì)通過(guò)批量將其中的項(xiàng)移入索引的主 GIN 數(shù)據(jù)結(jié)構(gòu)來(lái)清理列表。 如果指定值時(shí)沒(méi)有單位，則以千字節(jié)為單位。默認(rèn)值是四兆字節(jié)（4MB）?？梢酝ㄟ^(guò)更改索引的存儲(chǔ)參數(shù)來(lái)為個(gè)別 GIN 索引覆蓋這個(gè)設(shè)置。

pages_per_range (integer)：使用于 BRIN 索引，定義用于每一個(gè)BRIN索引項(xiàng)的塊范圍由多少個(gè)表塊組成。默認(rèn)是128。

autosummarize (boolean)：定義是否只要在下一個(gè)頁(yè)面上檢測(cè)到插入就為前面的頁(yè)面范圍運(yùn)行概要操作。

更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(xù)更新中。

B-tree 索引

Btree 結(jié)構(gòu)

meta page和root page是一定有的，meta page需要一個(gè)頁(yè)來(lái)存儲(chǔ)，表示指向root page的page id。隨著記錄數(shù)的增加，一個(gè)root page可能存不下所有的heap item，就會(huì)有l(wèi)eaf page,甚至branch page，甚至多層的branch page。一共有幾層branch和 leaf，可以用btree page元數(shù)據(jù)的level來(lái)表示。

安裝擴(kuò)展準(zhǔn)備數(shù)據(jù)

--使用pageinspect擴(kuò)展工具查看結(jié)構(gòu)，數(shù)據(jù)準(zhǔn)備
create?extension?pageinspect;
--主鍵索引使用的是btree索引，索引名字?tb_order_pkey
create?table?tb_order(id?int?primary?key,?order_no?varchar(255));?
insert?into?tb_order?select?generate_series(1,100),?md5(random()::varchar);
--analyze?統(tǒng)計(jì)數(shù)據(jù)庫(kù)表數(shù)據(jù)，統(tǒng)計(jì)結(jié)果存儲(chǔ)到pg_statistic系統(tǒng)表中
--vacuum?用于清理死亡元組占用的存儲(chǔ)空間
vacuum?analyze?tb_order;

btree索引演變過(guò)程

btree索引一層結(jié)構(gòu)

1、查看meta塊

select?*?from?bt_metap('tb_order_pkey');--查看meta塊

此時(shí)level為0即高度為1，root塊為1。

2、根據(jù)root 的?page id =1查看 root page的stats

select?*?from?bt_page_stats('tb_order_pkey',?1);--查看page的統(tǒng)計(jì)狀態(tài)信息

圖中參數(shù)說(shuō)明如下：

ive_items:存活的索引行
dead_items：死亡的索引行
avg_item_size:平均索引行大小
page_size:塊大小，詳細(xì)看最后說(shuō)明
free_size：塊空余大小
btpo_prev：塊左邊
btpo_next：塊右邊
btpo：當(dāng)前塊層次，0代表處于0層
btpo_flags：當(dāng)前塊類型，3代表：他既是leaf又是root，即2+1
meta?page
root?page:表示為btpo?flags=2
branch?page?:表示為btpo?flags=0
leaf?page:表示為btpo?flags=1

3、查看指定索引塊內(nèi)容

select?*?from?bt_page_items('tb_order_pkey',?1);--查看指定索引塊內(nèi)容

索引：ctid+索引列的值

4、通過(guò)索引ctid訪問(wèn)數(shù)據(jù)

select?*?from?tb_order?where?ctid='(0,1)';?--通過(guò)索引ctid訪問(wèn)數(shù)據(jù)

btree索引二層結(jié)構(gòu)

包括 meta page, root page, leaf page

--使用pageinspect擴(kuò)展工具查看結(jié)構(gòu)，數(shù)據(jù)準(zhǔn)備
create?extension?pageinspect;
--主鍵索引使用的是btree索引，索引名字?tb_order_pkey
create?table?tb_order2(id?int?primary?key,?order_no?varchar(255));?
insert?into?tb_order2?select?generate_series(1,10000),?md5(random()::varchar);
--analyze?統(tǒng)計(jì)數(shù)據(jù)庫(kù)表數(shù)據(jù)，統(tǒng)計(jì)結(jié)果存儲(chǔ)到pg_statistic系統(tǒng)表中
--vacuum?用于清理死亡元組占用的存儲(chǔ)空間
vacuum?analyze?tb_order2;

1、查看meta塊

Vacuum用于清理死亡元組占用的存儲(chǔ)空間，默認(rèn)刪除或因更新過(guò)期（為了MVVC）的元組不會(huì)被物理刪除。因此需要周期性的進(jìn)行Vacuum，尤其是頻繁更新的表。

Analyze命令用于統(tǒng)計(jì)數(shù)據(jù)庫(kù)表數(shù)據(jù)，統(tǒng)計(jì)結(jié)果存儲(chǔ)到pg_statistic系統(tǒng)表中。數(shù)據(jù)庫(kù)進(jìn)行基于成本的優(yōu)化（CBO）時(shí)通過(guò)統(tǒng)計(jì)數(shù)據(jù)優(yōu)化SQL語(yǔ)句的解釋計(jì)劃。

select?*?from?bt_metap('tb_order2_pkey');

此時(shí)level為1即高度為2，root塊id為3

2、根據(jù)root 的?page id =3查看 root page的stats**

select?*?from?bt_page_stats('tb_order2_pkey',?3);--根據(jù)root?的?page?id?=3查看stats

live_items:存活的頁(yè)塊
dead_items：死亡的頁(yè)塊
avg_item_size:平均索引行大小
page_size:塊大小，詳細(xì)看最后說(shuō)明
free_size：塊空余大小
btpo_prev：塊左邊
btpo_next：塊右邊
btpo：當(dāng)前塊層次，1代表處于2層，表示下面還有一層
btpo_flags：當(dāng)前塊類型，3代表：他既是leaf又是root，即2+1
-?meta?page
-?root?page:表示為btpo?flags=2
-?branch?page?:表示為btpo?flags=0
-?leaf?page:表示為btpo?flags=1

3、查看指定索引塊內(nèi)容

select?*?from?bt_page_items('tb_order2_pkey',?3);

總共28個(gè)頁(yè)塊，

4、查看每個(gè)的頁(yè)塊統(tǒng)計(jì)

select?*?from?bt_page_stats('tb_order2_pkey',?1);

btpo?flags=1表示為leaf?page
btpo_prev：0
btpo_next：2

5、查看每個(gè)的頁(yè)塊內(nèi)容

select?*?from?bt_page_items('tb_order2_pkey',?1);

6、通過(guò)ctid查看數(shù)據(jù)

select?*?from?tb_order2?where?ctid='(3,1)';?

btree索引三層結(jié)構(gòu)

包括 meta page, root page, leaf page，branch page。更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(xù)更新中。

實(shí)例

postgres=#?create?index?idx_test_id?on?test(id);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_id"?btree?(id)

postgres=#?explain?analyze?select?*?from?test?where?id?between?100?and?200;
????????????????????????????????QUERY?PLAN
------------------------------------------------------------------------------------------------------------------------
?Index?Scan?using?idx_test_id?on?test??(cost=0.43..10.49?rows=103?width=15)?(actual?time=0.006..0.058?rows=101?loops=1)
???Index?Cond:?((id?>=?100)?AND?(id?<=?200))
?Planning?Time:?0.408?ms
?Execution?Time:?0.072?ms
(4?rows)

Hash 索引

Hash索引結(jié)構(gòu)

哈希索引項(xiàng)只存儲(chǔ)每個(gè)索引項(xiàng)的哈希代碼，而不是實(shí)際的數(shù)據(jù)值

應(yīng)用場(chǎng)景

• hash索引存儲(chǔ)的是被索引字段VALUE的哈希值，只支持等值查詢。

• hash索引特別適用于字段VALUE非常長(zhǎng)（不適合b-tree索引，因?yàn)閎-tree一個(gè)PAGE至少要存儲(chǔ)3個(gè)索引行，所以不支持特別長(zhǎng)的VALUE）的場(chǎng)景，例如很長(zhǎng)的字符串，并且用戶只需要等值搜索，建議使用hash index。

實(shí)例

postgres=#?create?index?idx_test_id?on?test?using?hash(id);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_id"?hash?(id)

postgres=#?explain?analyze?select?*?from?test?where?id?=?100;
????????????????????????????????????????????????????QUERY?PLAN
-------------------------------------------------------------------------------------------------------------------
?Index?Scan?using?idx_test_id?on?test??(cost=0.00..8.02?rows=1?width=15)?(actual?time=0.014..0.015?rows=1?loops=1)
???Index?Cond:?(id?=?100)
?Planning?Time:?0.142?ms
?Execution?Time:?0.029?ms
(4?rows)

GiST 索引

GiST的意思是通用的搜索樹(shù)(Generalized Search Tree)。 它是一種平衡樹(shù)結(jié)構(gòu)的訪問(wèn)方法,在系統(tǒng)中作為一個(gè)基本模版,可以使用它實(shí)現(xiàn)任意索引模式。B-trees, R-trees和許多其它的索引模式都可以用GiST實(shí)現(xiàn)。

與Btree索引比較的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

Gist索引適用于多維數(shù)據(jù)類型和集合數(shù)據(jù)類型，和Btree索引類似，同樣適用于其他的數(shù)據(jù)類型。和Btree索引相比，Gist多字段索引在查詢條件中包含索引字段的任何子集都會(huì)使用索引掃描，而B(niǎo)tree索引只有查詢條件包含第一個(gè)索引字段才會(huì)使用索引掃描。

缺點(diǎn)

Gist索引創(chuàng)建耗時(shí)較長(zhǎng)，占用空間也比較大。

實(shí)例

postgres=#?create?index?idx_t_gist_pos?on?t_gist?using?gist(pos);
CREATE?INDEX
postgres=#?\d?t_gist
???????????????Table?"public.t_gist"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?pos????|?point???|???????????|??????????|
Indexes:
????"idx_t_gist_pos"?gist?(pos)

postgres=#?explain?analyze?select?*?from?t_gist?where?circle?'((100,100)?10)'??@>?pos;
????????????????????????????????????????????????????????QUERY?PLAN
--------------------------------------------------------------------------------------------------------------------------
?Bitmap?Heap?Scan?on?t_gist??(cost=5.06..271.70?rows=100?width=20)?(actual?time=0.048..0.092?rows=29?loops=1)
???Recheck?Cond:?('<(100,100),10>'::circle?@>?pos)
???Heap?Blocks:?exact=29
???->??Bitmap?Index?Scan?on?idx_t_gist_pos??(cost=0.00..5.03?rows=100?width=0)?(actual?time=0.027..0.027?rows=29?loops=1)
?????????Index?Cond:?(pos?<@?'<(100,100),10>'::circle)
?Planning?Time:?0.092?ms
?Execution?Time:?0.136?ms
(7?rows)

SP-GiST 索引

SP-GiST 中的GiST說(shuō)明它跟 GiST 訪問(wèn)方法有一些相似性。兩者的相似性在于，都是通用搜索樹(shù)，都為構(gòu)建各種訪問(wèn)方法提供了框架。SP代表空間分區(qū)（space partitioning），這里的空間，就是我們常說(shuō)的空間，比如二維平面。我們將會(huì)看到，它可以表示任意搜索空間。

GIST索引不是單獨(dú)一種索引類型，而是一種架構(gòu)，可以在這種架構(gòu)上實(shí)現(xiàn)很多不同的索引策略。因此，可以使用GIST索引的特定操作符類型高度依賴于索引策略(操作符類)。

GIST是廣義搜索樹(shù)generalized search tree的縮寫(xiě)。這是一個(gè)平衡搜索樹(shù)。

用于解決一些B-tree，GIN難以解決的數(shù)據(jù)減少問(wèn)題，例如，范圍是否相交，是否包含，地理位置中的點(diǎn)面相交，或者按點(diǎn)搜索附近的點(diǎn)。

Postgresql也實(shí)現(xiàn)了以下幾種類型的SP-Gist索引的操作類，我們可以在這些類型上直接建立SP-Gist索引。

實(shí)例

postgres=#?create?index?idx_t_spgist_p?on?t_spgist?using?spgist(p);
CREATE?INDEX
postgres=#?\d?t_spgist
??????????????Table?"public.t_spgist"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?p??????|?point???|???????????|??????????|
Indexes:
????"idx_t_spgist_p"?spgist?(p)

postgres=#?explain?analyze?select?*?from?t_spgist?where?p?>^?point?'(2,7)';
???????????????????????????????????????????????????????????QUERY?PLAN
---------------------------------------------------------------------------------------------------------------------------------
?Bitmap?Heap?Scan?on?t_spgist??(cost=305.78..1067.78?rows=10000?width=20)?(actual?time=7.387..15.011?rows=99245?loops=1)
???Recheck?Cond:?(p?>^?'(2,7)'::point)
???Heap?Blocks:?exact=637
???->??Bitmap?Index?Scan?on?idx_t_spgist_p??(cost=0.00..303.28?rows=10000?width=0)?(actual?time=7.302..7.303?rows=99245?loops=1)
?????????Index?Cond:?(p?>^?'(2,7)'::point)
?Planning?Time:?0.124?ms
?Execution?Time:?17.611?ms
(7?rows)

更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(xù)更新中。

GIN 索引

gin索引結(jié)構(gòu)

GIN是Generalized lnverted Index的縮寫(xiě)。就是所謂的倒排索引，它處理的數(shù)據(jù)類型的值不是原來(lái)的，而是由元素構(gòu)成。我們稱之為復(fù)合類型。

存儲(chǔ)被索引字段的VALUE或VALUE的元素，以及行號(hào)的list或tree。

col_val:(tid_list?or?tid_tree),col_val_elements:(tid_list?or?tid_tree)

比如('hank','15:3 21:4')中，表示hank在15:3和21:4這兩個(gè)位置出現(xiàn)過(guò)

應(yīng)用場(chǎng)景

• 當(dāng)需要搜索多值類型內(nèi)的VALUE時(shí)，適合多值類型，例如數(shù)組、全文檢索、TOKEN。（根據(jù)不同的類型，支持相交、包含、大于、在左邊、在右邊等搜索)

• 當(dāng)用戶的數(shù)據(jù)比較稀疏時(shí)，如果要搜索某個(gè)VALUE的值，可以適應(yīng)btree_gin支持普通btree支持的類型。(支持btree的操作符)

• 當(dāng)用戶需要按任意列進(jìn)行搜索時(shí)，gin支持多列展開(kāi)單獨(dú)建立索引域，同時(shí)支持內(nèi)部多域索引的bitmapAnd, bitmapor合并，快速的返回按任意列搜索請(qǐng)求的數(shù)據(jù)。

實(shí)例

postgres=#?create?index?idx_ts_doc_tsv?on?ts?using?gin(doc_tsv);
CREATE?INDEX
postgres=#?\d?ts
??????????????????Table?"public.ts"
?Column??|???Type???|?Collation?|?Nullable?|?Default
---------+----------+-----------+----------+---------
?doc?????|?text?????|???????????|??????????|
?doc_tsv?|?tsvector?|???????????|??????????|
Indexes:
????"idx_ts_doc_tsv"?gin?(doc_tsv)
postgres=#?set?enable_seqscan?=?off;
SET
postgres=#?explain?analyze?select?*?from?ts?where?doc_tsv?@@?to_tsquery('many?&?slitter');
???????????????????????????????????????????????????????QUERY?PLAN
------------------------------------------------------------------------------------------------------------------------
?Bitmap?Heap?Scan?on?ts??(cost=12.25..16.51?rows=1?width=64)?(actual?time=0.046..0.047?rows=1?loops=1)
???Recheck?Cond:?(doc_tsv?@@?to_tsquery('many?&?slitter'::text))
???Heap?Blocks:?exact=1
???->??Bitmap?Index?Scan?on?idx_ts_doc_tsv??(cost=0.00..12.25?rows=1?width=0)?(actual?time=0.042..0.042?rows=1?loops=1)
?????????Index?Cond:?(doc_tsv?@@?to_tsquery('many?&?slitter'::text))
?Planning?Time:?0.176?ms
?Execution?Time:?0.071?ms
(7?rows)

BRIN 索引

簡(jiǎn)介

BRIN 索引是塊級(jí)索引，有別于B-TREE等索引，BRIN記錄并不是以行號(hào)為單位記錄索引明細(xì)，而是記錄每個(gè)數(shù)據(jù)塊或者每段連續(xù)的數(shù)據(jù)塊的統(tǒng)計(jì)信息。因此BRIN索引空間占用特別的小，對(duì)數(shù)據(jù)寫(xiě)入、更新、刪除的影響也很小。

BRIN屬于LOSSLY索引，當(dāng)被索引列的值與物理存儲(chǔ)相關(guān)性很強(qiáng)時(shí)，BRIN索引的效果非常的好。例如時(shí)序數(shù)據(jù)，在時(shí)間或序列字段創(chuàng)建BRIN索引，進(jìn)行等值、范圍查詢時(shí)效果很好。與我們已經(jīng)熟悉的索引不同，BRIN避免查找絕對(duì)不合適的行，而不是快速找到匹配的行。BRIN是一個(gè)不準(zhǔn)確的索引:不包含表行的tid。

表被分割成ranges(好多個(gè)pages的大小)：因此被稱作block range index(BRIN)。在每個(gè)range中存儲(chǔ)數(shù)據(jù)的摘要信息。作為規(guī)則，這里是最小值和最大值，但有時(shí)也并非如此。假設(shè)執(zhí)行了一個(gè)查詢，該查詢包含某列的條件;如果所查找的值沒(méi)有進(jìn)入?yún)^(qū)間，則可以跳過(guò)整個(gè)range;但如果它們確實(shí)在，所有塊中的所有行都必須被查看以從中選擇匹配的行。在元數(shù)據(jù)頁(yè)和摘要數(shù)據(jù)之間，是reverse range map頁(yè)(revmap)。是一個(gè)指向相應(yīng)索引行的指針(TIDs)數(shù)組。

在BRIN索引中，PostgreSQL會(huì)為每個(gè)8k大小的存儲(chǔ)數(shù)據(jù)頁(yè)面讀取所選列的最大值和最小值，然后將該信息（頁(yè)碼以及列的最小值和最大值）存儲(chǔ)到BRIN索引中。一般可以不把BRIN看作索引，而是看作順序掃描的加速器。 如果我們把每個(gè)range都看作是一個(gè)虛擬分區(qū)，那么我們可以把BRIN看作分區(qū)的替代方案。BRIN適合單值類型，當(dāng)被索引列存儲(chǔ)相關(guān)性越接近1或-1時(shí)，數(shù)據(jù)存儲(chǔ)越有序，塊的邊界越明顯，BRIN索引的效果就越好。

實(shí)例

postgres=#?create?index?idx_test_id?on?test?using?brin(id);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_id"?brin?(id)

postgres=#?explain?analyze?select?*?from?test?where?id?between?100?and?200;
?????????????????????????????????????????????????????????QUERY?PLAN
----------------------------------------------------------------------------------------------------------------------------
?Bitmap?Heap?Scan?on?test??(cost=12.03..27651.36?rows=1?width=15)?(actual?time=0.079..4.006?rows=101?loops=1)
???Recheck?Cond:?((id?>=?100)?AND?(id?<=?200))
???Rows?Removed?by?Index?Recheck:?23579
???Heap?Blocks:?lossy=128
???->??Bitmap?Index?Scan?on?idx_test_id??(cost=0.00..12.03?rows=23585?width=0)?(actual?time=0.067..0.067?rows=1280?loops=1)
?????????Index?Cond:?((id?>=?100)?AND?(id?<=?200))
?Planning?Time:?0.240?ms
?Execution?Time:?4.045?ms
(8?rows)

更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(xù)更新中。

其他用法

多列索引

postgres=#?create?index?idx_test_dl?on?test(id,name);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_dl"?btree?(id,?name)

postgres=#?explain?analyze?select?*?from?test?where?id?=?100?and?name?=?'val:100';
???????????????????????????????????????????????????????QUERY?PLAN
------------------------------------------------------------------------------------------------------------------------
?Index?Only?Scan?using?idx_test_dl?on?test??(cost=0.43..8.45?rows=1?width=15)?(actual?time=0.086..0.087?rows=1?loops=1)
???Index?Cond:?((id?=?100)?AND?(name?=?'val:100'::text))
???Heap?Fetches:?1
?Planning?Time:?0.224?ms
?Execution?Time:?0.104?ms
(5?rows)

唯一索引

postgres=#?create?unique?index?idx_test_wy??on?test(id);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_wy"?UNIQUE,?btree?(id)

postgres=#?explain?analyze?select?*?from?test?where?id?=?100;
????????????????????????????????????????????????????QUERY?PLAN
-------------------------------------------------------------------------------------------------------------------
?Index?Scan?using?idx_test_wy?on?test??(cost=0.43..8.45?rows=1?width=15)?(actual?time=0.099..0.101?rows=1?loops=1)
???Index?Cond:?(id?=?100)
?Planning?Time:?0.350?ms
?Execution?Time:?0.119?ms
(4?rows)

表達(dá)式索引

postgres=#?create?index?idx_test_bds?on?test((id+1000));
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_bds"?btree?((id?+?1000))

postgres=#?explain?analyze?select?*?from?test?where?id+1000?>?10000;
???????????????????????????????????????????????????????????????QUERY?PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
?Bitmap?Heap?Scan?on?test??(cost=31205.10..83233.11?rows=1666667?width=15)?(actual?time=578.175..1501.361?rows=4991000?loops=1)
???Recheck?Cond:?((id?+?1000)?>?10000)
???Heap?Blocks:?exact=26980
???->??Bitmap?Index?Scan?on?idx_test_bds??(cost=0.00..30788.43?rows=1666667?width=0)?(actual?time=574.862..574.863?rows=4991000?loops=1)
?????????Index?Cond:?((id?+?1000)?>?10000)
?Planning?Time:?0.110?ms
?Execution?Time:?1633.012?ms
(7?rows)

部分索引

postgres=#?create?index?idx_test_bf?on?test(id)?where?(id?between?1000?and?2000);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_bf"?btree?(id)?WHERE?id?>=?1000?AND?id?<=?2000

postgres=#?explain?analyze?select?*?from?test?where?id?between?1500?and?2000;
???????????????????????????????????????????????????????QUERY?PLAN
------------------------------------------------------------------------------------------------------------------------
?Index?Scan?using?idx_test_bf?on?test??(cost=0.28..27.29?rows=515?width=15)?(actual?time=0.035..0.090?rows=501?loops=1)
???Index?Cond:?(id?>=?1500)
?Planning?Time:?0.265?ms
?Execution?Time:?0.110?ms
(4?rows)

postgres=#?explain?analyze?select?*?from?test?where?id?between?500?and?1000;
???????????????????????????????????????????????????????QUERY?PLAN
-------------------------------------------------------------------------------------------------------------------------
?Seq?Scan?on?test??(cost=10000000000.00..10000102028.00?rows=272?width=15)?(actual?time=0.035..427.097?rows=501?loops=1)
???Filter:?((id?>=?500)?AND?(id?<=?1000))
???Rows?Removed?by?Filter:?4999499
?Planning?Time:?0.107?ms
?Execution?Time:?427.120?ms
(5?rows)

覆蓋索引

postgres=#?create?index?idx_test_fg?on?test(id)?include(name);
CREATE?INDEX
postgres=#?\d?test
????????????????Table?"public.test"
?Column?|??Type???|?Collation?|?Nullable?|?Default
--------+---------+-----------+----------+---------
?id?????|?integer?|???????????|??????????|
?name???|?text????|???????????|??????????|
Indexes:
????"idx_test_fg"?btree?(id)?INCLUDE?(name)

postgres=#?explain?analyze?select?*?from?test?where?id?=?100;
???????????????????????????????????????????????????????QUERY?PLAN
------------------------------------------------------------------------------------------------------------------------
?Index?Only?Scan?using?idx_test_fg?on?test??(cost=0.43..8.45?rows=1?width=15)?(actual?time=0.031..0.032?rows=1?loops=1)
???Index?Cond:?(id?=?100)
???Heap?Fetches:?1
?Planning?Time:?0.264?ms
?Execution?Time:?0.047?ms
(5?rows)

常用管理操作

--查詢
postgres=#?select?*?from?pg_indexes?where?tablename?=?'test';
?schemaname?|?tablename?|??indexname??|?tablespace?|????????????????????????????????indexdef
------------+-----------+-------------+------------+-------------------------------------------------------------------------
?public?????|?test??????|?idx_test_fg?|????????????|?CREATE?INDEX?idx_test_fg?ON?public.test?USING?btree?(id)?INCLUDE?(name)
(1?row)

--重建
postgres=#?reindex?index?idx_test_fg;
REINDEX

--重命名
postgres=#?alter?index?idx_test_fg?rename?to?idx_test_id;
ALTER?INDEX

--修改表空間
postgres=#?alter?index?idx_test_id?set?tablespace?tab1;
ALTER?INDEX

--刪除
postgres=#?drop?index?idx_test_id;
DROP?INDEX

更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(xù)更新中。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-764120.html

到了這里，關(guān)于進(jìn)階數(shù)據(jù)庫(kù)系列（十二）：PostgreSQL 索引技術(shù)詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！