pgsql索引詳解

一、mysql索引

MySQL索引跟pgsql索引類似，都是為了加速查詢而創建的。MySQL索引有主鍵索引、唯一索引、普通索引、全文索引、空間索引，其中最常用的是B-Tree索引，而MyISAM存儲引擎還有Hash索引。

二、pgsql索引行超出btree長度

一般情況下，我們創建索引時指定列和索引類型即可。但當列長度過長時，如果直接創建索引，則在查詢時會出現「行行超過btree最大長度」的錯誤。這時我們就需要使用函數對列進行截斷處理。例如：

CREATE INDEX idx_temp ON table1 (substring(col1, 1, 10));

三、pgsql索引的內存

索引被存儲在內存中，索引越小，查詢效率越高。當我們使用「EXPLAIN ANALYZE」來分析查詢計劃時，可以看到索引佔用的內存大小。如果內存不足，可以通過調整「shared_buffers」參數來加大索引佔用的內存空間。例如：

shared_buffers = 1024MB

四、pgsql索引創建

我們可以使用CREATE INDEX命令來創建索引。參數包括表名、列名和索引類型等。例如：

CREATE INDEX idx_name ON table_name (column_name);

五、pgsql索引類型

pgsql支持多種索引類型，包括B-Tree、哈希表、GiST和SP-GiST等。B-Tree是pgsq最常用的索引類型，哈希表可以在精確匹配時效率更高，GiST可以在多維空間中使用，而SP-GiST則是空間分割的基礎。下面是創建不同類型索引的示例：

--創建B-Tree索引
CREATE INDEX idx_name ON table_name USING btree (column_name);

--創建哈希表索引
CREATE INDEX idx_name ON table_name USING hash (column_name);

--創建GiST索引
CREATE INDEX idx_name ON table_name USING gist (column_name);

--創建SP-GiST索引
CREATE INDEX idx_name ON table_name USING spgist (column_name);

六、pgsql索引原理

B-Tree索引是在數據結構中利用了平衡樹的思想。它的原理是將數據按照索引列的值進行排序，然後將排序後的數據按照一定的規則劃分成多個塊（也稱之為節點），並生成多層樹狀結構，每個節點都包含了自己範圍內的元素。這整個過程就是使用B-Tree演算法生成索引的過程。簡單來說，就是把數據存到一棵樹里，然後根據每個節點的取值範圍，在樹的各個層級中查找。

七、pgsql索引結構

B-Tree索引結構由根節點、中間節點和葉子節點三部分組成。每個節點都存儲一定數量的鍵和指針。中間節點以及根節點都存儲鍵和指針，用於連接下一層節點。葉子節點存儲實際數據和行指針。下面是一個B-Tree索引結構的圖示：

    +------+---+---+---+---+
    |      |   |   |   |   |
    |   K  | P | P | P | P |
    |      |   |   |   |   |
    +------+---+---+---+---+
     /  | |  \  /   \  | | \
   /  |  | |   /     \ | |  \
  /  |   \ /  /       \ \ /   \
L  K  P  K  P  K       K P  K   R

（其中，K代表索引鍵，P代表指針）

八、pgsql索引優化

優化索引可以在查詢效率上有所提升。例如，在查詢欄位少但需要返回的數據比較多時，我們可以使用索引包含需要返回的數據。如果涉及到多表查詢時，將複雜查詢拆解成多個簡單查詢，使用索引連接返回的數據。如果使用索引查詢以後還需要對數據進行排序、分組等操作，那麼我們可以考慮新建索引，或者在現有的索引基礎上新增欄位。

九、pgsql索引分區

當數據量過大時，我們可以使用索引分區來對數據進行劃分，這樣不僅可以加快查詢速度，還可以在維護數據時提高效率。例如：

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    data TEXT NOT NULL
) PARTITION BY RANGE(created_at);

--創建分區表
CREATE TABLE table_name_2019_01 PARTITION OF table_name
FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00');

--創建索引
CREATE INDEX ON table_name (created_at);

十、pgsql索引含null選取

當我們在查詢含有null值的列時，通常會使用「is null」或「is not null」來進行篩選，而一般情況下，資料庫並不會將null值存儲在B-Tree索引中。如果將含有null值的列添加到B-Tree索引中，大量的null值會大大影響查詢效率。因此，我們在添加索引時，應該確定索引不含有null值。例如：

CREATE INDEX idx_name ON table_name (column_name) WHERE column_name IS NOT NULL;