### 索引訪問方法介紹

##### 訪問方法屬性中的四種型別
- can_order: 訪問方法能夠在建立索引時指定值的排序順序（僅適用於“ btree”）
- can_unique:支援唯一約束和主鍵，適用於btree
- can_multi_col:可以在多列建立索引
- can_exclude:支援排它約束

``` sql
 select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
from pg_am a,
     unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
where a.amname = 'btree'
order by a.amname;

##### 特定索引屬性
- clusterable:可以根據索引對行進行重新排序（對其進行cluster操作）
- index_scan:支援索引掃描。儘管此屬性可能看起來很奇怪，但並非所有索引都能一次返回TID-有些返回結果一次全部返回，並且僅支援點陣圖掃描
- bitmap_scan: 支援點陣圖掃描
- backward_scan：可以按建立索引時指定的相反順序返回結果

> clusterable:可以根據索引對錶的行重新排序,直接使用cluster命令，語法如下:

``` sql
mydb=# \h cluster 
Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

URL: https://www.postgresql.org/docs/13/sql-cluster.html
```
#####  列屬性

- asc，desc，nulls_first，nulls_last，orderable：這些屬性與值的排序有關，只有btree索引支援排序
- distance_orderable:可以按照操作確定的排序順序返回結果（到目前為止僅適用於GiST和RUM索引）
- returnable：在不訪問表的情況下使用索引的可能性，即僅索引掃描的支援
- search_array：支援使用表示式搜尋多個值。
- search_nulls：透過IS NULL和IS NOT NULL條件進行搜尋的可能性。

``` sql
select p.name,
     pg_index_column_has_property('tbl_a_pkey'::regclass,1,p.name)
from unnest(array[
       'asc','desc','nulls_first','nulls_last','orderable','distance_orderable','returnable','search_array','search_nulls']) p(name);

#### 運算子類和運算子族

除了需要知道各種操作方法相關的屬性之外，我們還需要知道各種不同的操作方法分別支援哪些資料型別和哪些運算子。pg中提供了運算子類和運算子族的概念。

運算子類包含用於索引的最小運算子集（可能還有輔助函式），以操作某種資料型別。

某些運算子族中包括一個運算子類別。此外，如果一個通用的運算子族具有相同的語義，則它們可以包含多個運算子類。例如，integer_ops系列包括型別bigint，integer和smallint的int8_ops，int4_ops和int2_ops類，它們的大小不同但含義相同

##### 可操作的型別
``` sql
--如整型和日期為例
select opfname, opcname, opcintype::regtype
from pg_opclass opc, pg_opfamily opf
where opf.opfname = 'integer_ops'
and opc.opcfamily = opf.oid
and opf.opfmethod = (
   select oid from pg_am where amname = 'btree'
 );
   opfname   | opcname  | opcintype 
-------------+----------+-----------
 integer_ops | int2_ops | smallint
 integer_ops | int4_ops | integer
 integer_ops | int8_ops | bigint
(3 rows)

--datetime_ops系列包含用於操作日期（有時間和無時間）的運算子類：

select opfname, opcname, opcintype::regtype
from pg_opclass opc, pg_opfamily opf
where opf.opfname = 'datetime_ops'
and opc.opcfamily = opf.oid
and opf.opfmethod = (
   select oid from pg_am where amname = 'btree'
 );
   opfname    |     opcname     |          opcintype          
--------------+-----------------+-----------------------------
 datetime_ops | date_ops        | date
 datetime_ops | timestamptz_ops | timestamp with time zone
 datetime_ops | timestamp_ops   | timestamp without time zone
(3 rows)
```

運算子族還可以包括其他運算子，以比較不同型別的值。分組到族中使計劃者可以將具有不同型別值的謂詞使用索引。一個族還可以包含其他輔助功能。
在大多數情況下，我們不需要了解操作員的族和類。通常，我們只建立索引，預設情況下使用某個運算子類。

但是，我們可以顯式指定運算子類。這是當需要顯式規範時的一個簡單示例：==在排序規則不同於C的資料庫中，常規索引不支援LIKE操作：==
``` sql
explain select * from t where b like 'A%';
--這種情況我們就可以透過使用運算子類 text_pattern_ops建立索引來克服此限制：
create index on t(b text_pattern_ops);
```

我們可以透過查詢這些系統目錄解決很多問題，例如：某種訪問方法可以操縱哪些資料型別:

**btree方法可以操作以下資料型別**

**運算子類包含哪些運算子**
``` sql
select amop.amopopr::regoperator
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'array_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'btree'
and amop.amoplefttype = opc.opcintype;
        amopopr        
-----------------------
 =(anyarray,anyarray)
 >(anyarray,anyarray)
(5 rows)
```

#### 相關資料字典

資料字典 | 描述
---|---
pg_proc | procedure
pg_operator | operator
pg_amop | access method's operator
pg_opfamily | operator family
pg_amproc | access method's support procedure
pg_type | datatype
pg_opclass| operator class
pg_am | access method

#### 索引支援的新資料型別

``` sql
--建立一個新的組合型別
create type complex as (re float, im float);

--建立表
create table t1(x complex);
insert into t1 values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0));
insert into t1 values((1.0,2.0)),((2.0,2.0));
```
預設情況，對於組合型別排序是分開的，先比較第一個欄位再比較第二個，也可以透過其他方式排序。

``` sql
--建立相關函式
create function modulus(a complex) returns float as $$
select sqrt(a.re*a.re + a.im*a.im);
$$ immutable language sql;

--定義5種運算子函式：
create function complex_lt(a complex, b complex) returns boolean as $$
select modulus(a) < modulus(b);
$$ immutable language sql;

create function complex_le(a complex, b complex) returns boolean as $$
select modulus(a) <= modulus(b);
$$ immutable language sql;

create function complex_eq(a complex, b complex) returns boolean as $$
select modulus(a) = modulus(b);
$$ immutable language sql;

create function complex_ge(a complex, b complex) returns boolean as $$
select modulus(a) >= modulus(b);
$$ immutable language sql;

create function complex_gt(a complex, b complex) returns boolean as $$
select modulus(a) > modulus(b);
$$ immutable language sql;

```
建立對應的運算子
``` sql
create operator #=#(leftarg=complex, rightarg=complex, procedure=complex_ge);

create operator #>#(leftarg=complex, rightarg=complex, procedure=complex_gt);

--比較數字
select (1.0,1.0)::complex # modulus(b) then 1
else 0
end; $$ language sql;
```
**建立一個運算子類**
``` sql
create operator class complex_ops
default for type complex
using btree as
operator 1 #=#,
operator 5 #>#,
function 1 complex_cmp(complex,complex);

--檢視排序結構
mydb=# select * from t1 order by x;
   x    
--------
 (1,1)
 (1,3)
 (0,10)
(3 rows)

--檢視可以使用此查詢獲取支援的函式：
select amp.amprocnum,
amp.amproc,
amp.amproclefttype::regtype,
amp.amprocrighttype::regtype
from pg_opfamily opf,
pg_am am,
pg_amproc amp
where opf.opfname = 'complex_ops'
and opf.opfmethod = am.oid
and am.amname = 'btree'
and amp.amprocfamily = opf.oid;

管理運算子
``` sql
--檢視 運算子 和運算子類
select * from pg_operator;
select * from pg_opclass;

--檢視運算子型別
select o.oid,o.oprname,
    (select t.typname from pg_type t 
     where o.oprleft=t.oid) as oprleft,
    (select t.typname  from pg_type t
     where o.oprright=t.oid)  as oprright
from pg_operator o  where  o.oprname like '#%#';

--刪除運算子
drop operator # 在資料庫中null是無法和其它值進行比較的

#### 內部結構
PostgreSQL的btree索引的演算法可以參考：src/backend/access/nbtree/README

其中PostgreSQL 的B-Tree索引頁分為幾種類別，我們可以透過btpo_flags的值去區分.
```
meta page    
root page         #  btpo_flags=2    
branch page       #  btpo_flags=0    
leaf page         #  btpo_flags=1        
leaf&root page    #  btpo_flags=3

--詳情見：src/include/access/nbtree.h
/* Bits defined in btpo_flags */
#define BTP_LEAF                (1 << 0)        /* leaf page, i.e. not internal page */
#define BTP_ROOT                (1 << 1)        /* root page (has no parent) */
#define BTP_DELETED             (1 << 2)        /* page has been deleted from tree */
#define BTP_META                (1 << 3)        /* meta-page */
#define BTP_HALF_DEAD   (1 << 4)        /* empty, but still in tree */
#define BTP_SPLIT_END   (1 << 5)        /* rightmost page of split group */
#define BTP_HAS_GARBAGE (1 << 6)        /* page has LP_DEAD tuples */
#define BTP_INCOMPLETE_SPLIT (1 << 7)   /* right sibling's downlink is missing */
```
其中meta page和root page是必須有的，meta page需要一個頁來儲存，表示指向root page的page id。

隨著記錄數的增加，一個root page可能存不下所有的heap item，就會有leaf page，甚至branch page，甚至多層的branch page。

一共有幾層branch 和 leaf，就用btree page後設資料的 level 來表示。

**可以透過pageinspect外掛研究所以你內部結構**

- bt_metap(relname text)：返回record，bt_metap用來返回關於一個B樹索引元頁的資訊。
- bt_page_stats(relname text, blkno int)：返回record，bt_page_stats返回有關
B-樹索引單一頁面的總計資訊
- bt_page_items(relname text, blkno int)：返回record，bt_page_items返回一個
B-樹索引頁面上項的所有細節資訊

#### 參考文件
- https://www.freesion.com/article/6456622935

### HASH索引

雜湊表是根據鍵（Key）而直接訪問在記憶體儲存位置的資料結構。它透過計算一個關於鍵值的函式，將所需查詢的資料對映到表中一個位置來訪問記錄，這加快了查詢速度。這個對映函式稱做雜湊函式，存放記錄的陣列稱做雜湊表

#### 索引結構

當插入索引時，讓我們計算鍵的雜湊函式。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**
- metapage（0號頁）,包含了HASH索引的控制資訊，指導如何找到其他頁面（每個bucket的primary page），以及當前儲存概貌。其他索引的0號頁基本都是這一個套路。
- primary bucket page，hash 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命令從頭開始重建索引

#### 總結

hash索引介紹：
src/backend/access/hash/README

使用場景：

- 1、等值查詢；
- 2、btree索引不支援的大欄位型別。

使用限制：

- 1、不支援對null的處理；
- 2、不能使用index only scan；
- 3、不支援多列索引。

#### 參考文件
- https://www.freesion.com/article/1966627561/

【INDEX】Postgresql索引介紹

相關文章