索引類(lèi)型

B-tree 索引：

適用場(chǎng)景：范圍查詢(xún)、等值查詢(xún)、排序操作。

特點(diǎn)：適用于大部分查詢(xún)場(chǎng)景，是 PostgreSQL 默認(rèn)的索引類(lèi)型。

哈希索引：

適用場(chǎng)景：等值查詢(xún)，對(duì)于頻繁的等值查詢(xún)有性能優(yōu)勢(shì)。

特點(diǎn)：不支持范圍查詢(xún)和排序操作，大小寫(xiě)敏感。

GIN 索引：

適用場(chǎng)景：全文搜索、數(shù)組包含查詢(xún)、JSONB 數(shù)據(jù)類(lèi)型查詢(xún)。

特點(diǎn)：支持對(duì)復(fù)雜查詢(xún)條件的優(yōu)化，如使用數(shù)組和 JSONB 類(lèi)型的數(shù)據(jù)。

GiST 索引：

適用場(chǎng)景：空間數(shù)據(jù)類(lèi)型（如幾何形狀）、全文搜索。

特點(diǎn)：支持多種數(shù)據(jù)類(lèi)型的復(fù)雜查詢(xún)優(yōu)化。

BRIN 索引：

適用場(chǎng)景：大表的列存儲(chǔ)，適合有序數(shù)據(jù)。

特點(diǎn)：適合于大數(shù)據(jù)量表的存儲(chǔ)，減少索引的存儲(chǔ)空間。

Partial 索引：

適用場(chǎng)景：對(duì)表中特定子集數(shù)據(jù)的查詢(xún)優(yōu)化。

特點(diǎn)：只對(duì)表中滿足條件的行建立索引，節(jié)省存儲(chǔ)空間和提高查詢(xún)性能。

SP-GiST 索引：

適用場(chǎng)景：空間數(shù)據(jù)類(lèi)型（如幾何形狀）、全文搜索。

特點(diǎn)：支持多種數(shù)據(jù)類(lèi)型的復(fù)雜查詢(xún)優(yōu)化。

bloom 索引：

適用場(chǎng)景：適用于高基數(shù)列的等值查詢(xún)優(yōu)化。

特點(diǎn)：布隆過(guò)濾器索引，適合于檢查元素是否屬于一個(gè)集合，但可能存在誤報(bào)（false positive），因此需要使用實(shí)際數(shù)據(jù)再次驗(yàn)證

多列索引

只有B 樹(shù)、GiST、GIN 和 BRIN索引類(lèi)型支持多列鍵索引。索引是否可以有多個(gè)鍵列與是否可以向索引中添加列無(wú)關(guān)。每個(gè)索引最多可以有32列，包括鍵列

示例 
CREATE INDEX test2_mm_idx ON test2 (major, minor);

1）B 樹(shù)索引

多列B 樹(shù)索引可以與涉及索引任意子集的查詢(xún)條件一起使用，但在約束條件應(yīng)用于前導(dǎo)（最左邊）列時(shí)效率最高。

對(duì)于多列索引，等式約束應(yīng)用于前導(dǎo)列，并且在第一個(gè)沒(méi)有等式約束的列上應(yīng)用不等式約束，這些約束將限制掃描索引的部分。

對(duì)于后續(xù)列的約束也會(huì)在索引中檢查，這樣可以減少對(duì)實(shí)際表的訪問(wèn)次數(shù)，但并不會(huì)減少需要掃描的索引部分。

2）GiST 索引

多列GiST索引可以與涉及任意子集的查詢(xún)條件一起使用。對(duì)額外列的條件限制會(huì)限制索引返回的條目，但第一列上的條件最為重要，影響需要掃描的索引部分。

3）GIN 索引：

多列GIN索引可以與涉及任意子集的查詢(xún)條件一起使用。與B 樹(shù)或GiST不同的是，無(wú)論查詢(xún)條件使用哪些索引列，索引搜索的效果都是相同的。

4）BRIN 索引：

多列BRIN索引可以與涉及任意子集的查詢(xún)條件一起使用。與GIN類(lèi)似，與B 樹(shù)或GiST不同的是，無(wú)論查詢(xún)條件使用哪些索引列，索引搜索的效果都是相同的。

在單個(gè)表上，多個(gè)BRIN索引通常沒(méi)有必要，除非需要使用不同的存儲(chǔ)參數(shù)(pages_per_range)。

總結(jié)：

每種索引類(lèi)型對(duì)多列索引的支持和效果略有不同，應(yīng)根據(jù)具體查詢(xún)模式、數(shù)據(jù)類(lèi)型和性能需求選擇合適的索引類(lèi)型。

多列索引應(yīng)謹(jǐn)慎使用，因?yàn)閱瘟兴饕ǔＲ呀?jīng)足夠提供良好的性能，并節(jié)省存儲(chǔ)空間和維護(hù)成本。

對(duì)于超過(guò)三列的索引，除非表的使用非常特殊，否則可能不會(huì)有幫助。

索引和ORDER BY

目前 PostgreSQL 支持的索引類(lèi)型中，只有 B 樹(shù)能夠產(chǎn)生排序的輸出結(jié)果 — 其他索引類(lèi)型返回的匹配行的順序是未指定的，依賴(lài)于具體實(shí)現(xiàn)。

默認(rèn)情況下，B 樹(shù)索引以升序存儲(chǔ)條目，空值排在最后。這意味著對(duì)于列 x 的索引正向掃描會(huì)生成滿足 ORDER BY x 或 ORDER BY x ASC NULLS LAST 的輸出。索引也可以反向掃描，生成滿足 ORDER BY x DESC 或 ORDER BY x DESC NULLS FIRST 的輸出。

在創(chuàng)建 B 樹(shù)索引時(shí)，可以通過(guò)包括 ASC、DESC、NULLS FIRST 和 NULLS LAST 選項(xiàng)來(lái)調(diào)整索引的排序順序。

例如：
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST); 
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

在單列索引中，這些選項(xiàng)可能看起來(lái)有些冗余，但在多列索引中它們非常有用?？紤]一個(gè)兩列索引 (x, y)：正向掃描可以滿足 ORDER BY x, y，反向掃描可以滿足 ORDER BY x DESC, y DESC。但如果應(yīng)用程序頻繁需要使用 ORDER BY x, y ASC，則普通索引無(wú)法提供此順序，但可以通過(guò)定義為 (x ASC, y ASC) 或 (x DESC, y ASC) 來(lái)實(shí)現(xiàn)。

顯然，具有非默認(rèn)排序順序的索引是一種相對(duì)特殊的功能，但有時(shí)它們可以為某些查詢(xún)帶來(lái)巨大的性能提升。是否值得維護(hù)這樣的索引取決于查詢(xún)中需要特定排序順序的頻率。

組合多個(gè)索引

單索引限制：

• 單個(gè)索引掃描僅能使用涉及索引列和相應(yīng)操作符的查詢(xún)子句。例如，復(fù)合條件如 (a = 5 AND b = 6) 可以使用索引，但像 (a = 5 OR b = 6) 這樣的查詢(xún)則不能直接使用索引。

多索引組合優(yōu)勢(shì)：

• PostgreSQL 提供了能力來(lái)結(jié)合多個(gè)索引（包括同一索引的多次使用），以處理單個(gè)索引無(wú)法覆蓋的查詢(xún)情況。系統(tǒng)可以通過(guò)多次索引掃描形成 AND 和 OR 條件來(lái)實(shí)現(xiàn)復(fù)雜的查詢(xún)需求。

操作原理：

• 結(jié)合多個(gè)索引時(shí)，系統(tǒng)會(huì)對(duì)每個(gè)索引進(jìn)行掃描，生成一個(gè)位圖表示匹配該索引條件的表行位置。這些位圖根據(jù)查詢(xún)的需要進(jìn)行 AND 和 OR 運(yùn)算。最終，實(shí)際的表行按物理順序訪問(wèn)并返回結(jié)果。

性能考慮：

• 盡管多索引組合可以解決復(fù)雜查詢(xún)，每個(gè)額外的索引掃描會(huì)增加時(shí)間成本。因此，對(duì)于需要大量掃描的查詢(xún)，可能會(huì)選擇使用簡(jiǎn)單的索引掃描，而不是結(jié)合多個(gè)索引。

索引設(shè)計(jì)策略：

• 在設(shè)計(jì)索引時(shí)，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員需權(quán)衡決策哪些索引能夠最好地支持常見(jiàn)的查詢(xún)模式。有時(shí)候選擇多列索引是最優(yōu)的，但在某些情況下，創(chuàng)建單獨(dú)的索引并依賴(lài)索引組合功能可能更為有效。

唯一索引

聲明唯一索引：

• 使用 CREATE UNIQUE INDEX 語(yǔ)句可以創(chuàng)建唯一索引，目前只有 B 樹(shù)索引支持唯一性約束。

示例：CREATE UNIQUE INDEX name ON table (column [, ...]);

唯一索引特性：

• 聲明唯一索引后，索引列的數(shù)值在表中必須唯一，不允許出現(xiàn)相同的索引值對(duì)應(yīng)多行數(shù)據(jù)。
• 默認(rèn)情況下，唯一索引對(duì)空值不視為相同，因此允許多個(gè)空值存在于索引列中。使用 NULLS NOT DISTINCT 可以修改此行為，使得空值視為相同。

自動(dòng)創(chuàng)建唯一索引：

• 當(dāng)為表定義唯一約束或主鍵時(shí)，PostgreSQL 會(huì)自動(dòng)創(chuàng)建唯一索引。該索引覆蓋構(gòu)成主鍵或唯一約束的列（如果適用，會(huì)創(chuàng)建多列索引），并用于實(shí)施約束。

注意事項(xiàng)：

在唯一約束列上手動(dòng)創(chuàng)建索引通常是多余的，因?yàn)橄到y(tǒng)會(huì)自動(dòng)創(chuàng)建該索引。手動(dòng)創(chuàng)建索引可能會(huì)導(dǎo)致重復(fù)，不建議這樣做。

 表達(dá)式的索引

索引列不必只是基礎(chǔ)表的一列，還可以是從表的一列或多列計(jì)算得出的函數(shù)或標(biāo)量表達(dá)式。此功能對(duì)于根據(jù)計(jì)算結(jié)果快速訪問(wèn)表非常有用。

例子：大小寫(xiě)不敏感比較

• 使用函數(shù) lower 進(jìn)行大小寫(xiě)不敏感的比較：

SELECT * FROM test1 WHERE lower(col1) = 'value';

• 如果在 lower(col1) 的結(jié)果上定義了索引，這個(gè)查詢(xún)可以利用索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

• 這種索引可以防止插入值僅在大小寫(xiě)上有區(qū)別的行，以及確保實(shí)際值相同的行不會(huì)重復(fù)插入，因此索引表達(dá)式可以用于實(shí)施不能定義為簡(jiǎn)單唯一約束的約束。
• 如果經(jīng)常執(zhí)行像下面這樣的查詢(xún)：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

• 可以考慮創(chuàng)建以下索引，結(jié)合 first_name 和 last_name：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

• 在定義索引表達(dá)式時(shí)，通常需要在表達(dá)式周?chē)由侠ㄌ?hào)，如第二個(gè)例子所示。當(dāng)表達(dá)式僅為函數(shù)調(diào)用時(shí)，可以省略括號(hào)，如第一個(gè)例子。

索引表達(dá)式的性能

• 索引表達(dá)式的維護(hù)成本較高，因?yàn)閷?duì)于每次行插入和非 HOT 更新，必須計(jì)算派生表達(dá)式。然而，在索引搜索期間，不需要重新計(jì)算索引表達(dá)式，因?yàn)樗鼈円呀?jīng)存儲(chǔ)在索引中。
• 索引表達(dá)式適用于檢索速度比插入和更新速度更重要的場(chǎng)景。

部分索引

主要用途：

• 避免索引常見(jiàn)值：部分索引的一個(gè)主要原因是避免索引常見(jiàn)值。如果一個(gè)查詢(xún)搜索的是常見(jiàn)值（即占表行總數(shù)超過(guò)幾個(gè)百分點(diǎn)的值），那么索引將不會(huì)被使用，因此沒(méi)有必要在索引中保留這些行。通過(guò)部分索引，可以減小索引的大小，加快那些使用索引的查詢(xún)速度。此外，由于索引不需要在所有情況下都更新，部分索引還可以加快許多表更新操作的速度。
• 索引數(shù)據(jù)：不在索引范圍內(nèi)的數(shù)據(jù)，不能使用部分索引。

例 11.1設(shè)置部分索引以排除公共值

假設(shè)你在數(shù)據(jù)庫(kù)中存儲(chǔ) Web 服務(wù)器訪問(wèn)日志。大多數(shù)訪問(wèn)來(lái)自于你組織的 IP 地址范圍，但有些來(lái)自于其他地方（比如員工使用撥號(hào)連接）。如果你的 IP 地址搜索主要針對(duì)外部訪問(wèn)，那么你可能不需要索引與你組織子網(wǎng)對(duì)應(yīng)的 IP 范圍。

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

例 11.2.設(shè)置部分索引以排除不感興趣的值

如果您的表同時(shí)包含計(jì)費(fèi)訂單和未計(jì)費(fèi)訂單，其中未計(jì)費(fèi)訂單僅占總表的一小部分，但這些行是訪問(wèn)次數(shù)最多的行，則可以通過(guò)僅在未計(jì)費(fèi)行上創(chuàng)建索引來(lái)提高性能。創(chuàng)建索引的命令如下所示：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

例 11.3.設(shè)置部分唯一索引

假設(shè)我們有一個(gè)描述測(cè)試結(jié)果的表格。我們希望確保給定的主題和目標(biāo)組合只有一個(gè)“成功”條目，但可能存在任意數(shù)量的“不成功”條目。這是一種方法：

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    faill boolean
    ...
);


CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

當(dāng)成功的測(cè)試很少而失敗的測(cè)試很多時(shí)，這是一種特別有效的方法。也可以通過(guò)創(chuàng)建具有限制的唯一部分索引，在列中只允許一個(gè) null。IS NULL

例 11.4.不要使用部分索引來(lái)替代分區(qū)

在數(shù)據(jù)庫(kù)中，不應(yīng)該通過(guò)創(chuàng)建大量非重疊的部分索引來(lái)替代分區(qū)。例如，像下面這樣創(chuàng)建一組部分索引：

CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
CREATE INDEX mytable_cat_N ON mytable (data) WHERE category = N;

這種做法是不明智的！更好的方式是使用一個(gè)單獨(dú)的非部分索引，如下所示：

CREATE INDEX mytable_cat_data ON mytable (category, data);

注意事項(xiàng)

這種部分索引需要預(yù)先確定共同的值，因此最適合用于數(shù)據(jù)分布不會(huì)經(jīng)常變化的情況。這樣的索引偶爾需要重新創(chuàng)建以適應(yīng)新的數(shù)據(jù)分布，但這會(huì)增加維護(hù)工作量。

僅索引掃描和覆蓋索引

索引類(lèi)型與索引只掃描：

• 所有的索引在PostgreSQL中都是輔助索引（Secondary Index），與表的主數(shù)據(jù)區(qū)域（Heap）分開(kāi)存儲(chǔ)。
• 索引只掃描要求索引類(lèi)型必須支持，例如B-tree索引始終支持，GiST和SP-GiST索引則根據(jù)操作類(lèi)別支持不同的索引只掃描。

索引只掃描的條件：

• 查詢(xún)必須僅引用存儲(chǔ)在索引中的列，如果查詢(xún)引用了非索引列，則無(wú)法使用索引只掃描。
• 對(duì)于表掃描，還需要驗(yàn)證每個(gè)檢索的行對(duì)于查詢(xún)的MVCC快照是否可見(jiàn)，這是通過(guò)可見(jiàn)性映射（Visibility Map）實(shí)現(xiàn)的。

覆蓋索引（Covering Index）：

• 為了有效利用索引只掃描功能，可以創(chuàng)建覆蓋索引，即包含查詢(xún)所需列的索引。
• 使用INCLUDE子句可以添加非搜索鍵的列到索引中，提高特定查詢(xún)模式的性能。

注意事項(xiàng)：

• 添加非鍵列到索引可能會(huì)增加索引的大小，可能導(dǎo)致性能下降，尤其是對(duì)于寬列。
• 在表數(shù)據(jù)變化較慢的情況下，才有利于索引只掃描不訪問(wèn)堆。
• 目前表達(dá)式索引不支持作為包含列，而且只有B-tree、GiST和SP-GiST索引支持包含列。

檢查和優(yōu)化索引的使用情況

使用 EXPLAIN 命令分析單個(gè)查詢(xún)：

使用 EXPLAIN 命令可以分析單個(gè)查詢(xún)的執(zhí)行計(jì)劃，包括查詢(xún)是否使用了索引以及使用了哪些索引。

這對(duì)于了解實(shí)際查詢(xún)工作負(fù)載中索引的使用情況非常重要。

收集統(tǒng)計(jì)信息：

在優(yōu)化索引前，始終先運(yùn)行 ANALYZE 命令。這個(gè)命令用于收集關(guān)于表中值分布的統(tǒng)計(jì)信息。

統(tǒng)計(jì)信息對(duì)于評(píng)估查詢(xún)返回行數(shù)的分布是必要的，優(yōu)化器需要根據(jù)這些信息為每個(gè)可能的查詢(xún)計(jì)劃分配合理的成本。

如果沒(méi)有實(shí)際的統(tǒng)計(jì)信息，優(yōu)化器會(huì)使用默認(rèn)值，這幾乎肯定是不準(zhǔn)確的。

使用真實(shí)數(shù)據(jù)進(jìn)行實(shí)驗(yàn)：

在設(shè)置索引時(shí)，使用真實(shí)數(shù)據(jù)進(jìn)行實(shí)驗(yàn)可以告訴你針對(duì)測(cè)試數(shù)據(jù)集需要哪些索引。

使用非常小的測(cè)試數(shù)據(jù)集通常是不可取的，因?yàn)檫@可能無(wú)法準(zhǔn)確反映真實(shí)數(shù)據(jù)的查詢(xún)性能。

強(qiáng)制使用索引：

當(dāng)索引未被使用時(shí)，可以通過(guò)運(yùn)行時(shí)參數(shù)強(qiáng)制其使用，例如關(guān)閉順序掃描 (enable_seqscan) 或嵌套循環(huán)連接 (enable_nestloop)。

如果強(qiáng)制使用索引后確實(shí)使用了索引，那么有兩種可能性：系統(tǒng)正確地判斷索引不適合使用，或者查詢(xún)計(jì)劃的成本估算不符合實(shí)際情況。

使用 EXPLAIN ANALYZE 命令：

使用 EXPLAIN ANALYZE 命令可以獲取查詢(xún)的詳細(xì)執(zhí)行統(tǒng)計(jì)信息，包括實(shí)際執(zhí)行時(shí)間和成本估算。

這對(duì)于評(píng)估查詢(xún)計(jì)劃的實(shí)際性能表現(xiàn)非常有用，特別是在調(diào)整查詢(xún)計(jì)劃的成本估算時(shí)。

調(diào)整查詢(xún)計(jì)劃的成本估算：

如果成本估算不準(zhǔn)確，可以通過(guò)調(diào)整運(yùn)行時(shí)參數(shù)來(lái)調(diào)整計(jì)劃節(jié)點(diǎn)的成本估算，或者通過(guò)優(yōu)化統(tǒng)計(jì)信息收集參數(shù)來(lái)提高查詢(xún)選擇性估算的準(zhǔn)確性。

總體而言，優(yōu)化索引的過(guò)程涉及實(shí)驗(yàn)、分析和調(diào)整，需要根據(jù)具體的查詢(xún)工作負(fù)載和實(shí)際數(shù)據(jù)來(lái)進(jìn)行。通過(guò)這些步驟，可以更有效地提升 PostgreSQL 數(shù)據(jù)庫(kù)的查詢(xún)性能和響應(yīng)速度。

索引的選擇和使用

在設(shè)計(jì)和選擇索引時(shí)，需要考慮以下因素：

• 查詢(xún)模式：經(jīng)常執(zhí)行的查詢(xún)類(lèi)型是什么？
• 數(shù)據(jù)類(lèi)型：表中存儲(chǔ)的數(shù)據(jù)類(lèi)型及其特點(diǎn)。
• 數(shù)據(jù)分布：索引列上數(shù)據(jù)的分布情況，是否均勻？
• 寫(xiě)入操作：索引對(duì)寫(xiě)入操作的影響如何？

綜上所述，每種索引類(lèi)型在不同的場(chǎng)景下都有其優(yōu)勢(shì)和劣勢(shì)。正確選擇和設(shè)計(jì)索引是優(yōu)化 PostgreSQL 數(shù)據(jù)庫(kù)性能的關(guān)鍵一步。