當(dāng)前位置:首頁 >  站長(zhǎng) >  數(shù)據(jù)庫 >  正文

PostgreSQL的B-tree索引用法詳解

 2021-06-03 17:10  來源: 腳本之家   我來投稿 撤稿糾錯(cuò)

  域名預(yù)訂/競(jìng)價(jià),好“米”不錯(cuò)過

B-tree索引適合用于存儲(chǔ)排序的數(shù)據(jù)。對(duì)于這種數(shù)據(jù)類型需要定義大于、大于等于、小于、小于等于操作符。

通常情況下,B-tree的索引記錄存儲(chǔ)在數(shù)據(jù)頁中。葉子頁中的記錄包含索引數(shù)據(jù)(keys)以及指向heap tuple記錄(即表的行記錄TIDs)的指針。內(nèi)部頁中的記錄包含指向索引子頁的指針和子頁中最小值。

B-tree有幾點(diǎn)重要的特性:

1、B-tree是平衡樹,即每個(gè)葉子頁到root頁中間有相同個(gè)數(shù)的內(nèi)部頁。因此查詢?nèi)魏我粋€(gè)值的時(shí)間是相同的。

2、B-tree中一個(gè)節(jié)點(diǎn)有多個(gè)分支,即每頁(通常8KB)具有許多TIDs。因此B-tree的高度比較低,通常4到5層就可以存儲(chǔ)大量行記錄。

3、索引中的數(shù)據(jù)以非遞減的順序存儲(chǔ)(頁之間以及頁內(nèi)都是這種順序),同級(jí)的數(shù)據(jù)頁由雙向鏈表連接。因此不需要每次都返回root,通過遍歷鏈表就可以獲取一個(gè)有序的數(shù)據(jù)集。

下面是一個(gè)索引的簡(jiǎn)單例子,該索引存儲(chǔ)的記錄為整型并只有一個(gè)字段:

該索引最頂層的頁是元數(shù)據(jù)頁,該數(shù)據(jù)頁存儲(chǔ)索引root頁的相關(guān)信息。內(nèi)部節(jié)點(diǎn)位于root下面,葉子頁位于最下面一層。向下的箭頭表示由葉子節(jié)點(diǎn)指向表記錄(TIDs)。

等值查詢

例如通過"indexed-field = expression"形式的條件查詢49這個(gè)值。

root節(jié)點(diǎn)有三個(gè)記錄:(4,32,64)。從root節(jié)點(diǎn)開始進(jìn)行搜索,由于32≤ 49 < 64,所以選擇32這個(gè)值進(jìn)入其子節(jié)點(diǎn)。通過同樣的方法繼續(xù)向下進(jìn)行搜索一直到葉子節(jié)點(diǎn),最后查詢到49這個(gè)值。

實(shí)際上,查詢算法遠(yuǎn)不止看上去的這么簡(jiǎn)單。比如,該索引是非唯一索引時(shí),允許存在許多相同值的記錄,并且這些相同的記錄不止存放在一個(gè)頁中。此時(shí)該如何查詢?我們返回到上面的的例子,定位到第二層節(jié)點(diǎn)(32,43,49)。如果選擇49這個(gè)值并向下進(jìn)入其子節(jié)點(diǎn)搜索,就會(huì)跳過前一個(gè)葉子頁中的49這個(gè)值。因此,在內(nèi)部節(jié)點(diǎn)進(jìn)行等值查詢49時(shí),定位到49這個(gè)值,然后選擇49的前一個(gè)值43,向下進(jìn)入其子節(jié)點(diǎn)進(jìn)行搜索。最后,在底層節(jié)點(diǎn)中從左到右進(jìn)行搜索。

(另外一個(gè)復(fù)雜的地方是,查詢的過程中樹結(jié)構(gòu)可能會(huì)改變,比如分裂)

非等值查詢

通過"indexed-field ≤ expression" (or "indexed-field ≥ expression")查詢時(shí),首先通過"indexed-field = expression"形式進(jìn)行等值(如果存在該值)查詢,定位到葉子節(jié)點(diǎn)后,再向左或向右進(jìn)行遍歷檢索。

下圖是查詢 n ≤ 35的示意圖:

大于和小于可以通過同樣的方法進(jìn)行查詢。查詢時(shí)需要排除等值查詢出的值。

范圍查詢

范圍查詢"expression1 ≤ indexed-field ≤ expression2"時(shí),需要通過 "expression1 ≤ indexed-field =expression2"找到一匹配值,然后在葉子節(jié)點(diǎn)從左到右進(jìn)行檢索,一直到不滿足"indexed-field ≤ expression2" 的條件為止;或者反過來,首先通過第二個(gè)表達(dá)式進(jìn)行檢索,在葉子節(jié)點(diǎn)定位到該值后,再從右向左進(jìn)行檢索,一直到不滿足第一個(gè)表達(dá)式的條件為止。

下圖是23 ≤ n ≤ 64的查詢示意圖:

案例

下面是一個(gè)查詢計(jì)劃的實(shí)例。通過demo database中的aircraft表進(jìn)行介紹。該表有9行數(shù)據(jù),由于整個(gè)表只有一個(gè)數(shù)據(jù)頁,所以執(zhí)行計(jì)劃不會(huì)使用索引。為了解釋說明問題,我們使用整個(gè)表進(jìn)行說明。

demo=# select * from aircrafts;
 aircraft_code |  model  | range
---------------+---------------------+-------
 773   | Boeing 777-300  | 11100
 763   | Boeing 767-300  | 7900
 SU9   | Sukhoi SuperJet-100 | 3000
 320   | Airbus A320-200  | 5700
 321   | Airbus A321-200  | 5600
 319   | Airbus A319-100  | 6700
 733   | Boeing 737-300  | 4200
 CN1   | Cessna 208 Caravan | 1200
 CR2   | Bombardier CRJ-200 | 2700
(9 rows)
demo=# create index on aircrafts(range);
demo=# set enable_seqscan = off;

 

(更準(zhǔn)確的方式:create index on aircrafts using btree(range),創(chuàng)建索引時(shí)默認(rèn)構(gòu)建B-tree索引。)

等值查詢的執(zhí)行計(jì)劃:

demo=# explain(costs off) select * from aircrafts where range = 3000;
     QUERY PLAN     
---------------------------------------------------
 Index Scan using aircrafts_range_idx on aircrafts
 Index Cond: (range = 3000)
(2 rows)

 

非等值查詢的執(zhí)行計(jì)劃:

demo=# explain(costs off) select * from aircrafts where range < 3000;
     QUERY PLAN    
---------------------------------------------------
 Index Scan using aircrafts_range_idx on aircrafts
 Index Cond: (range < 3000)
(2 rows)

 

范圍查詢的執(zhí)行計(jì)劃:

demo=# explain(costs off) select * from aircrafts
where range between 3000 and 5000;
      QUERY PLAN     
-----------------------------------------------------
 Index Scan using aircrafts_range_idx on aircrafts
 Index Cond: ((range >= 3000) AND (range <= 5000))
(2 rows)

 

排序

再次強(qiáng)調(diào),通過index、index-only或bitmap掃描,btree訪問方法可以返回有序的數(shù)據(jù)。因此如果表的排序條件上有索引,優(yōu)化器會(huì)考慮以下方式:表的索引掃描;表的順序掃描然后對(duì)結(jié)果集進(jìn)行排序。

排序順序

當(dāng)創(chuàng)建索引時(shí)可以明確指定排序順序。如下所示,在range列上建立一個(gè)索引,并且排序順序?yàn)榻敌颍?/p>

1demo=# create index on aircrafts(range desc);

本案例中,大值會(huì)出現(xiàn)在樹的左邊,小值出現(xiàn)在右邊。為什么有這樣的需求?這樣做是為了多列索引。創(chuàng)建aircraft的一個(gè)視圖,通過range分成3部分:

demo=# create view aircrafts_v as
select model,
  case
   when range < 4000 then 1
   when range < 10000 then 2
   else 3
  end as class
from aircrafts;
 
demo=# select * from aircrafts_v;
  model  | class
---------------------+-------
 Boeing 777-300  |  3
 Boeing 767-300  |  2
 Sukhoi SuperJet-100 |  1
 Airbus A320-200  |  2
 Airbus A321-200  |  2
 Airbus A319-100  |  2
 Boeing 737-300  |  2
 Cessna 208 Caravan |  1
 Bombardier CRJ-200 |  1
(9 rows)

 

然后創(chuàng)建一個(gè)索引(使用下面表達(dá)式):

demo=# create index on aircrafts(
 (case when range < 4000 then 1 when range < 10000 then 2 else 3 end),
 model);

 

現(xiàn)在,可以通過索引以升序的方式獲取排序的數(shù)據(jù):

demo=# select class, model from aircrafts_v order by class, model;
 class |  model 
-------+---------------------
  1 | Bombardier CRJ-200
  1 | Cessna 208 Caravan
  1 | Sukhoi SuperJet-100
  2 | Airbus A319-100
  2 | Airbus A320-200
  2 | Airbus A321-200
  2 | Boeing 737-300
  2 | Boeing 767-300
  3 | Boeing 777-300
(9 rows)
 
demo=# explain(costs off)
select class, model from aircrafts_v order by class, model;
      QUERY PLAN     
--------------------------------------------------------
 Index Scan using aircrafts_case_model_idx on aircrafts
(1 row)

 

同樣,可以以降序的方式獲取排序的數(shù)據(jù):

demo=# select class, model from aircrafts_v order by class desc, model desc;
 class |  model 
-------+---------------------
  3 | Boeing 777-300
  2 | Boeing 767-300
  2 | Boeing 737-300
  2 | Airbus A321-200
  2 | Airbus A320-200
  2 | Airbus A319-100
  1 | Sukhoi SuperJet-100
  1 | Cessna 208 Caravan
  1 | Bombardier CRJ-200
(9 rows)
demo=# explain(costs off)
select class, model from aircrafts_v order by class desc, model desc;
       QUERY PLAN      
-----------------------------------------------------------------
 Index Scan BACKWARD using aircrafts_case_model_idx on aircrafts
(1 row)

然而,如果一列以升序一列以降序的方式獲取排序的數(shù)據(jù)的話,就不能使用索引,只能單獨(dú)排序:

demo=# explain(costs off)
select class, model from aircrafts_v order by class ASC, model DESC;
     QUERY PLAN    
-------------------------------------------------
 Sort
 Sort Key: (CASE ... END), aircrafts.model DESC
 -> Seq Scan on aircrafts
(3 rows)

(注意,最終執(zhí)行計(jì)劃會(huì)選擇順序掃描,忽略之前設(shè)置的enable_seqscan = off。因?yàn)檫@個(gè)設(shè)置并不會(huì)放棄表掃描,只是設(shè)置他的成本----查看costs on的執(zhí)行計(jì)劃)

若有使用索引,創(chuàng)建索引時(shí)指定排序的方向:

demo=# create index aircrafts_case_asc_model_desc_idx on aircrafts(
 (case
 when range < 4000 then 1
 when range < 10000 then 2
 else 3
 end) ASC,
 model DESC);
 
demo=# explain(costs off)
select class, model from aircrafts_v order by class ASC, model DESC;
       QUERY PLAN      
-----------------------------------------------------------------
 Index Scan using aircrafts_case_asc_model_desc_idx on aircrafts
(1 row)

列的順序

當(dāng)使用多列索引時(shí)與列的順序有關(guān)的問題會(huì)顯示出來。對(duì)于B-tree,這個(gè)順序非常重要:頁中的數(shù)據(jù)先以第一個(gè)字段進(jìn)行排序,然后再第二個(gè)字段,以此類推。

下圖是在range和model列上構(gòu)建的索引:

當(dāng)然,上圖這么小的索引在一個(gè)root頁足以存放。但是為了清晰起見,特意將其分成幾頁。

從圖中可見,通過類似的謂詞class = 3(僅按第一個(gè)字段進(jìn)行搜索)或者class = 3 and model = 'Boeing 777-300'(按兩個(gè)字段進(jìn)行搜索)將非常高效。

然而,通過謂詞model = 'Boeing 777-300'進(jìn)行搜索的效率將大大降低:從root開始,判斷不出選擇哪個(gè)子節(jié)點(diǎn)進(jìn)行向下搜索,因此會(huì)遍歷所有子節(jié)點(diǎn)向下進(jìn)行搜索。這并不意味著永遠(yuǎn)無法使用這樣的索引----它的效率有問題。例如,如果aircraft有3個(gè)classes值,每個(gè)class類中有許多model值,此時(shí)不得不掃描索引1/3的數(shù)據(jù),這可能比全表掃描更有效。

但是,當(dāng)創(chuàng)建如下索引時(shí):

demo=# create index on aircrafts( model, (case when range < 4000 then 1 when range < 10000 then 2 else 3 end));

索引字段的順序會(huì)改變:

通過這個(gè)索引,model = 'Boeing 777-300'將會(huì)很有效,但class = 3則沒這么高效。

NULLs

PostgreSQL的B-tree支持在NULLs上創(chuàng)建索引,可以通過IS NULL或者IS NOT NULL的條件進(jìn)行查詢。

考慮flights表,允許NULLs:

demo=# create index on flights(actual_arrival);
demo=# explain(costs off) select * from flights where actual_arrival is null;
      QUERY PLAN     
-------------------------------------------------------
 Bitmap Heap Scan on flights
 Recheck Cond: (actual_arrival IS NULL)
 -> Bitmap Index Scan on flights_actual_arrival_idx
   Index Cond: (actual_arrival IS NULL)
(4 rows)

 

下面的例子中,他們的順序相同,因此可以使用索引:

demo=# explain(costs off)
select * from flights order by actual_arrival NULLS LAST;
      QUERY PLAN     
--------------------------------------------------------
 Index Scan using flights_actual_arrival_idx on flights
(1 row)

 

下面的例子,順序不同,優(yōu)化器選擇順序掃描然后進(jìn)行排序:

demo=# explain(costs off)
select * from flights order by actual_arrival NULLS FIRST;
    QUERY PLAN   
----------------------------------------
 Sort
 Sort Key: actual_arrival NULLS FIRST
 -> Seq Scan on flights
(3 rows)

 

NULLs必須位于開頭才能使用索引:

demo=# create index flights_nulls_first_idx on flights(actual_arrival NULLS FIRST);
demo=# explain(costs off)
select * from flights order by actual_arrival NULLS FIRST;
      QUERY PLAN     
-----------------------------------------------------
 Index Scan using flights_nulls_first_idx on flights
(1 row)

 

像這樣的問題是由NULLs引起的而不是無法排序,也就是說NULL和其他這比較的結(jié)果無法預(yù)知:

demo=# \pset null NULL
demo=# select null < 42;
 ?column?
----------
 NULL
(1 row)

 

這和B-tree的概念背道而馳并且不符合一般的模式。然而NULLs在數(shù)據(jù)庫中扮演者很重要的角色,因此不得不為NULL做特殊設(shè)置。

由于NULLs可以被索引,因此即使表上沒有任何標(biāo)記也可以使用索引。(因?yàn)檫@個(gè)索引包含表航記錄的所有信息)。如果查詢需要排序的數(shù)據(jù),而且索引確保了所需的順序,那么這可能是由意義的。這種情況下,查詢計(jì)劃更傾向于通過索引獲取數(shù)據(jù)。

屬性

下面介紹btree訪問方法的特性。

 amname |  name  | pg_indexam_has_property
--------+---------------+-------------------------
 btree | can_order  | t
 btree | can_unique | t
 btree | can_multi_col | t
 btree | can_exclude | t

 

可以看到,B-tree能夠排序數(shù)據(jù)并且支持唯一性。同時(shí)還支持多列索引,但是其他訪問方法也支持這種索引。我們將在下次討論EXCLUDE條件。

  name  | pg_index_has_property
---------------+-----------------------
 clusterable | t
 index_scan | t
 bitmap_scan | t
 backward_scan | t

 

Btree訪問方法可以通過以下兩種方式獲取數(shù)據(jù):index scan以及bitmap scan??梢钥吹?,通過tree可以向前和向后進(jìn)行遍歷。

  name   | pg_index_column_has_property
--------------------+------------------------------
 asc    | t
 desc    | f
 nulls_first  | f
 nulls_last   | t
 orderable   | t
 distance_orderable | f
 returnable   | t
 search_array  | t
 search_nulls  | t

 

前四種特性指定了特定列如何精確的排序。本案例中,值以升序(asc)進(jìn)行排序并且NULLs在后面(nulls_last)。也可以有其他組合。

search_array的特性支持向這樣的表達(dá)式:

demo=# explain(costs off)
select * from aircrafts where aircraft_code in ('733','763','773');
       QUERY PLAN      
-----------------------------------------------------------------
 Index Scan using aircrafts_pkey on aircrafts
 Index Cond: (aircraft_code = ANY ('{733,763,773}'::bpchar[]))
(2 rows)

 

returnable屬性支持index-only scan,由于索引本身也存儲(chǔ)索引值所以這是合理的。下面簡(jiǎn)單介紹基于B-tree的覆蓋索引。

具有額外列的唯一索引

前面討論了:覆蓋索引包含查詢所需的所有值,需不要再回表。唯一索引可以成為覆蓋索引。

假設(shè)我們查詢所需要的列添加到唯一索引,新的組合唯一鍵可能不再唯一,同一列上將需要2個(gè)索引:一個(gè)唯一,支持完整性約束;另一個(gè)是非唯一,為了覆蓋索引。這當(dāng)然是低效的。

在我們公司 Anastasiya Lubennikova @ lubennikovaav 改進(jìn)了btree,額外的非唯一列可以包含在唯一索引中。我們希望這個(gè)補(bǔ)丁可以被社區(qū)采納。實(shí)際上PostgreSQL11已經(jīng)合了該補(bǔ)丁。

考慮表bookings:d

demo=# \d bookings
    Table "bookings.bookings"
 Column |   Type   | Modifiers
--------------+--------------------------+-----------
 book_ref  | character(6)    | not null
 book_date | timestamp with time zone | not null
 total_amount | numeric(10,2)   | not null
Indexes:
 "bookings_pkey" PRIMARY KEY, btree (book_ref)
Referenced by:
TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

這個(gè)表中,主鍵(book_ref,booking code)通過常規(guī)的btree索引提供,下面創(chuàng)建一個(gè)由額外列的唯一索引:

1demo=# create unique index bookings_pkey2 on bookings(book_ref) INCLUDE (book_date);

然后使用新索引替代現(xiàn)有索引:

demo=# begin;
demo=# alter table bookings drop constraint bookings_pkey cascade;
demo=# alter table bookings add primary key using index bookings_pkey2;
demo=# alter table tickets add foreign key (book_ref) references bookings (book_ref);
demo=# commit;

然后表結(jié)構(gòu):

demo=# \d bookings
    Table "bookings.bookings"
 Column |   Type   | Modifiers
--------------+--------------------------+-----------
 book_ref  | character(6)    | not null
 book_date | timestamp with time zone | not null
 total_amount | numeric(10,2)   | not null
Indexes:
 "bookings_pkey2" PRIMARY KEY, btree (book_ref) INCLUDE (book_date)
Referenced by:
TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

此時(shí),這個(gè)索引可以作為唯一索引工作也可以作為覆蓋索引:

demo=# explain(costs off)
select book_ref, book_date from bookings where book_ref = '059FC4';
     QUERY PLAN    
--------------------------------------------------
 Index Only Scan using bookings_pkey2 on bookings
 Index Cond: (book_ref = '059FC4'::bpchar)
(2 rows)

創(chuàng)建索引

眾所周知,對(duì)于大表,加載數(shù)據(jù)時(shí)最好不要帶索引;加載完成后再創(chuàng)建索引。這樣做不僅提升效率還能節(jié)省空間。

創(chuàng)建B-tree索引比向索引中插入數(shù)據(jù)更高效。所有的數(shù)據(jù)大致上都已排序,并且數(shù)據(jù)的葉子頁已創(chuàng)建好,然后只需構(gòu)建內(nèi)部頁直到root頁構(gòu)建成一個(gè)完整的B-tree。

這種方法的速度依賴于RAM的大小,受限于參數(shù)maintenance_work_mem。因此增大該參數(shù)值可以提升速度。對(duì)于唯一索引,除了分配maintenance_work_mem的內(nèi)存外,還分配了work_mem的大小的內(nèi)存。

比較

前面,提到PG需要知道對(duì)于不同類型的值調(diào)用哪個(gè)函數(shù),并且這個(gè)關(guān)聯(lián)方法存儲(chǔ)在哈希訪問方法中。同樣,系統(tǒng)必須找出如何排序。這在排序、分組(有時(shí))、merge join中會(huì)涉及。PG不會(huì)將自身綁定到操作符名稱,因?yàn)橛脩艨梢宰远x他們的數(shù)據(jù)類型并給出對(duì)應(yīng)不同的操作符名稱。

例如bool_ops操作符集中的比較操作符:

postgres=# select amop.amopopr::regoperator as opfamily_operator,
   amop.amopstrategy
from  pg_am am,
   pg_opfamily opf,
   pg_amop amop
where opf.opfmethod = am.oid
and  amop.amopfamily = opf.oid
and  am.amname = 'btree'
and  opf.opfname = 'bool_ops'
order by amopstrategy;
 opfamily_operator | amopstrategy
---------------------+--------------
 <(boolean,boolean) |   1
 <=(boolean,boolean) |   2
 =(boolean,boolean) |   3
 >=(boolean,boolean) |   4
 >(boolean,boolean) |   5
(5 rows)

 

這里可以看到有5種操作符,但是不應(yīng)該依賴于他們的名字。為了指定哪種操作符做什么操作,引入策略的概念。為了描述操作符語義,定義了5種策略:

1 — less

2 — less or equal

3 — equal

4 — greater or equal

5 — greater

postgres=# select amop.amopopr::regoperator as opfamily_operator
from  pg_am am,
   pg_opfamily opf,
   pg_amop amop
where opf.opfmethod = am.oid
and  amop.amopfamily = opf.oid
and  am.amname = 'btree'
and  opf.opfname = 'integer_ops'
and  amop.amopstrategy = 1
order by opfamily_operator;
 pfamily_operator
----------------------
 <(integer,bigint)
 <(smallint,smallint)
 <(integer,integer)
 <(bigint,bigint)
 <(bigint,integer)
 <(smallint,integer)
 <(integer,smallint)
 <(smallint,bigint)
 <(bigint,smallint)
(9 rows)

一些操作符族可以包含幾種操作符,例如integer_ops包含策略1的幾種操作符:

正因如此,當(dāng)比較類型在一個(gè)操作符族中時(shí),不同類型值的比較,優(yōu)化器可以避免類型轉(zhuǎn)換。

索引支持的新數(shù)據(jù)類型

文檔中提供了一個(gè)創(chuàng)建符合數(shù)值的新數(shù)據(jù)類型,以及對(duì)這種類型數(shù)據(jù)進(jìn)行排序的操作符類。該案例使用C語言完成。但不妨礙我們使用純SQL進(jìn)行對(duì)比試驗(yàn)。

創(chuàng)建一個(gè)新的組合類型:包含real和imaginary兩個(gè)字段

1postgres=# create type complex as (re float, im float);

創(chuàng)建一個(gè)包含該新組合類型字段的表:

postgres=# create table numbers(x complex);postgres=# insert into numbers values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0));

現(xiàn)在有個(gè)疑問,如果在數(shù)學(xué)上沒有為他們定義順序關(guān)系,如何進(jìn)行排序?

已經(jīng)定義好了比較運(yùn)算符:

postgres=# select * from numbers order by x;
 x
--------
 (0,10)
 (1,1)
 (1,3)
(3 rows)

默認(rèn)情況下,對(duì)于組合類型排序是分開的:首先比較第一個(gè)字段然后第二個(gè)字段,與文本字符串比較方法大致相同。但是我們也可以定義其他的排序方式,例如組合數(shù)字可以當(dāng)做一個(gè)向量,通過模值進(jìn)行排序。為了定義這樣的順序,我們需要?jiǎng)?chuàng)建一個(gè)函數(shù):

postgres=# create operator class complex_ops
default for type complex
using btree as
 operator 1 #<#,
 operator 2 #<=#,
 operator 3 #=#,
 operator 4 #>=#,
 operator 5 #>#,
function 1 complex_cmp(complex,complex);
 
//排序結(jié)果:
postgres=# select * from numbers order by x;
 x
--------
 (1,1)
 (1,3)
 (0,10)
(3 rows)
 
//可以使用此查詢獲取支持的函數(shù):
 
postgres=# 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;
 amprocnum | amproc | amproclefttype | amprocrighttype
-----------+-------------+----------------+-----------------
   1 | complex_cmp | complex  | complex
(1 row)

 

//排序結(jié)果:postgres=# select * from numbers order by x; x -------- (1,1) (1,3) (0,10)(3 rows)

//可以使用此查詢獲取支持的函數(shù):

postgres=# select amp.amprocnum, amp.amproc, amp.amproclefttype::regtype, amp.amprocrighttype::regtypefrom pg_opfamily opf, pg_am am, pg_amproc ampwhere opf.opfname = 'complex_ops'and opf.opfmethod = am.oidand am.amname = 'btree'and amp.amprocfamily = opf.oid; amprocnum | amproc | amproclefttype | amprocrighttype-----------+-------------+----------------+----------------- 1 | complex_cmp | complex | complex(1 row)

內(nèi)部結(jié)構(gòu)

使用pageinspect插件觀察B-tree結(jié)構(gòu):

1demo=# create extension pageinspect;

索引的元數(shù)據(jù)頁:

demo=# select * from bt_metap('ticket_flights_pkey');
 magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
 340322 |  2 | 164 |  2 |  164 |   2
(1 row)

 

值得關(guān)注的是索引level:不包括root,有一百萬行記錄的表其索引只需要2層就可以了。

Root頁,即164號(hào)頁面的統(tǒng)計(jì)信息:

demo=# select type, live_items, dead_items, avg_item_size, page_size, free_size
from bt_page_stats('ticket_flights_pkey',164);
 type | live_items | dead_items | avg_item_size | page_size | free_size
------+------------+------------+---------------+-----------+-----------
 r |   33 |   0 |   31 |  8192 |  6984
(1 row)

 

該頁中數(shù)據(jù):

demo=# select itemoffset, ctid, itemlen, left(data,56) as data
from bt_page_items('ticket_flights_pkey',164) limit 5;
 itemoffset | ctid | itemlen |       data      
------------+---------+---------+----------------------------------------------------------
   1 | (3,1) |  8 |
   2 | (163,1) |  32 | 1d 30 30 30 35 34 33 32 33 30 35 37 37 31 00 00 ff 5f 00
   3 | (323,1) |  32 | 1d 30 30 30 35 34 33 32 34 32 33 36 36 32 00 00 4f 78 00
   4 | (482,1) |  32 | 1d 30 30 30 35 34 33 32 35 33 30 38 39 33 00 00 4d 1e 00
   5 | (641,1) |  32 | 1d 30 30 30 35 34 33 32 36 35 35 37 38 35 00 00 2b 09 00
(5 rows) 

第一個(gè)tuple指定該頁的最大值,真正的數(shù)據(jù)從第二個(gè)tuple開始。很明顯最左邊子節(jié)點(diǎn)的頁號(hào)是163,然后是323。反過來,可以使用相同的函數(shù)搜索。

PG10版本提供了"amcheck"插件,該插件可以檢測(cè)B-tree數(shù)據(jù)的邏輯一致性,使我們提前探知故障。

文章來源:腳本之家

來源地址:https://www.jb51.net/article/204311.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道,分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!

相關(guān)文章

熱門排行

信息推薦