PostgreSQL的B-tree索引

yzs87發表於2019-06-06
SQL索引

結構

B- tree 索引適合用於儲存排序的資料。對於這種資料型別需要定義大於、大於等於、小於、小於等於操作符。


通常情況下， B-tree 的索引記錄儲存在資料頁中。葉子頁中的記錄包含索引資料（ keys ）以及指向 heap tuple 記錄（即表的行記錄 TIDs ）的指標。內部頁中的記錄包含指向索引子頁的指標和子頁中最小值。

 

B-tree 有幾點重要的特性：

1 、 B-tree 是平衡樹，即每個葉子頁到 root 頁中間有相同個數的內部頁。因此查詢任何一個值的時間是相同的。

2 、 B-tree 中一個節點有多個分支，即每頁（通常 8KB ）具有許多 TIDs 。因此 B-tree 的高度比較低，通常 4 到 5 層就可以儲存大量行記錄。

3 、索引中的資料以非遞減的順序儲存（頁之間以及頁內都是這種順序），同級的資料頁由雙向連結串列連線。因此不需要每次都返回 root ，通過遍歷連結串列就可以獲取一個有序的資料集。

 

下面是一個索引的簡單例子，該索引儲存的記錄為整型並只有一個欄位：

該索引最頂層的頁是後設資料頁，該資料頁儲存索引 root 頁的相關資訊。內部節點位於 root 下面，葉子頁位於最下面一層。向下的箭頭表示由葉子節點指向表記錄（ TIDs ）。

等值查詢

例如通過 " indexed-field   =   expressio n " 形式的 條件查詢 49 這個值。

root 節點有三個記錄： (4,32,64) 。從 root 節點開始進行搜尋，由於 32 ≤ 49 < 64 ，所以選擇 32 這個值進入其子節點。通過同樣的方法繼續向下進行搜尋一直到葉子節點，最後查詢到 49 這個值。

 

實際上，查詢演算法遠不止看上去的這麼簡單。比如，該索引是非唯一索引時，允許存在許多相同值的記錄，並且這些相同的記錄不止存放在一個頁中。此時該如何查詢？我們返回到上面的的例子，定位到第二層節點 (32,43,49) 。如果選擇 49 這個值並向下進入其子節點搜尋，就會跳過前一個葉子頁中的 49 這個值。因此，在內部節點進行等值查詢 49 時，定位到 49 這個值，然後選擇 49 的前一個值 43 ，向下進入其子節點進行搜尋。最後，在底層節點中從左到右進行搜尋。

 

( 另外一個複雜的地方是，查詢的過程中樹結構可能會改變，比如分裂 )

 

非等值查詢

通過 "indexed-field ≤ expression" (or "indexed-field ≥ expression") 查詢時，首先 通過 " indexed-field   =   expressio n " 形式進行等值（如果存在該值）查詢，定位到葉子節點後，再向左或向右進行遍歷檢索。

 

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

大於和小於可以通過同樣的方法進行查詢。查詢時需要排除等值查詢出的值。

 

範圍查詢

  範圍查詢 "expression1 ≤ indexed-field ≤ expression2" 時，需要通過 "expression1 ≤ indexed-field =expression2" 找到一匹配值，然後在葉子節點從左到右進行檢索，一直到不滿足 "indexed-field ≤ expression2" 的條件為止；或者反過來，首先通過第二個表示式進行檢索，在葉子節點定位到該值後，再從右向左進行檢索，一直到不滿足第一個表示式的條件為止。

 

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

案例

下面是一個查詢計劃的例項。通過 demo database 中的 aircraft 表進行介紹。該表有 9 行資料，由於整個表只有一個資料頁，所以執行計劃不會使用索引。為了解釋說明問題，我們使用整個表進行說明。 

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;

（更準確的方式： create index on aircrafts using btree(range)，建立索引時預設構建B-tree索引。）

等值查詢的執行計劃： 

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)

非等值查詢的執行計劃： 

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)

範圍查詢的執行計劃： 

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)

排序

再次強調，通過 index 、 index-only 或 bitmap 掃描， btree 訪問方法可以返回有序的資料。因此如果表的排序條件上有索引，優化器會考慮以下方式：表的索引掃描；表的順序掃描然後對結果集進行排序。

排序順序

當建立索引時可以明確指定排序順序。如下所示，在 range列上建立一個索引，並且排序順序為降序： 

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

本案例中，大值會出現在樹的左邊，小值出現在右邊。為什麼有這樣的需求？這樣做是為了多列索引。建立 aircraft的一個檢視，通過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)

 

然後建立一個索引（使用下面表示式）： 

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

 

現在，可以通過索引以升序的方式獲取排序的資料： 

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)

 

同樣，可以以降序的方式獲取排序的資料： 

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)

然而，如果一列以升序一列以降序的方式獲取排序的資料的話，就不能使用索引，只能單獨排序： 

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)

（注意，最終執行計劃會選擇順序掃描，忽略之前設定的 enable_seqscan = off。因為這個設定並不會放棄表掃描，只是設定他的成本----檢視costs on的執行計劃）

 

若有使用索引，建立索引時指定排序的方向： 

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)

列的順序

當使用多列索引時與列的順序有關的問題會顯示出來。對於 B-tree，這個順序非常重要：頁中的資料先以第一個欄位進行排序，然後再第二個欄位，以此類推。

 

下圖是在 range和model列上構建的索引：

 

 

當然，上圖這麼小的索引在一個 root頁足以存放。但是為了清晰起見，特意將其分成幾頁。

從圖中可見，通過類似的謂詞 class = 3（僅按第一個欄位進行搜尋）或者class = 3 and model = 'Boeing 777-300'（按兩個欄位進行搜尋）將非常高效。

 

然而，通過謂詞 model = 'Boeing 777-300'進行搜尋的效率將大大降低：從root開始，判斷不出選擇哪個子節點進行向下搜尋，因此會遍歷所有子節點向下進行搜尋。這並不意味著永遠無法使用這樣的索引----它的效率有問題。例如，如果aircraft有3個classes值，每個class類中有許多model值，此時不得不掃描索引1/3的資料，這可能比全表掃描更有效。

 

但是，當建立如下索引時： 

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

索引欄位的順序會改變：

 

通過這個索引， model = 'Boeing 777-300'將會很有效，但class = 3則沒這麼高效。

 

NULLs

PostgreSQL的B-tree支援在NULLs上建立索引，可以通過IS NULL或者IS NOT NULL的條件進行查詢。

考慮 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)

 

NULLs位於葉子節點的一端或另一端，這依賴於索引的建立方式（NULLS FIRST或NULLS LAST）。如果查詢中包含排序，這就顯得很重要了：如果SELECT語句在ORDER BY子句中指定NULLs的順序索引構建的順序一樣（NULLS FIRST或NULLS LAST），就可以使用整個索引。

 

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

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)

下面的例子，順序不同，優化器選擇順序掃描然後進行排序： 

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和其他這比較的結果無法預知： 

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

 

這和 B-tree的概念背道而馳並且不符合一般的模式。然而NULLs在資料庫中扮演者很重要的角色，因此不得不為NULL做特殊設定。

 

由於 NULLs可以被索引，因此即使表上沒有任何標記也可以使用索引。（因為這個索引包含表航記錄的所有資訊）。如果查詢需要排序的資料，而且索引確保了所需的順序，那麼這可能是由意義的。這種情況下，查詢計劃更傾向於通過索引獲取資料。

屬性

下面介紹 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能夠排序資料並且支援唯一性。同時還支援多列索引，但是其他訪問方法也支援這種索引。我們將在下次討論EXCLUDE條件。

  

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

 

Btree訪問方法可以通過以下兩種方式獲取資料：index scan以及bitmap scan。可以看到，通過tree可以向前和向後進行遍歷。

  

        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）進行排序並且NULLs在後面（nulls_last）。也可以有其他組合。

 

search_array的特性支援向這樣的表示式： 

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，由於索引本身也儲存索引值所以這是合理的。下面簡單介紹基於B-tree的覆蓋索引。

具有額外列的唯一索引

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

 

假設我們查詢所需要的列新增到唯一索引，新的組合唯一鍵可能不再唯一，同一列上將需要 2個索引：一個唯一，支援完整性約束；另一個是非唯一，為了覆蓋索引。這當然是低效的。

 

在我們公司 Anastasiya Lubennikova @ lubennikovaav 改進了btree，額外的非唯一列可以包含在唯一索引中。我們希望這個補丁可以被社群採納。實際上PostgreSQL11已經合了該補丁。

 

考慮表 bookings： 

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)

 

這個表中，主鍵（ book_ref,booking code）通過常規的btree索引提供，下面建立一個由額外列的唯一索引： 

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

然後使用新索引替代現有索引： 

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;

然後表結構： 

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)

此時，這個索引可以作為唯一索引工作也可以作為覆蓋索引： 

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)

建立索引

眾所周知，對於大表，載入資料時最好不要帶索引；載入完成後再建立索引。這樣做不僅提升效率還能節省空間。

 

建立 B-tree索引比向索引中插入資料更高效。所有的資料大致上都已排序，並且資料的葉子頁已建立好，然後只需構建內部頁直到root頁構建成一個完整的B-tree。

 

這種方法的速度依賴於 RAM的大小，受限於引數maintenance_work_mem。因此增大該引數值可以提升速度。對於唯一索引，除了分配maintenance_work_mem的記憶體外，還分配了work_mem的大小的記憶體。

比較

前面，提到 PG需要知道對於不同型別的值呼叫哪個函式，並且這個關聯方法儲存在雜湊訪問方法中。同樣，系統必須找出如何排序。這在排序、分組（有時）、merge join中會涉及。PG不會將自身繫結到操作符名稱，因為使用者可以自定義他們的資料型別並給出對應不同的操作符名稱。

例如 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種操作符，但是不應該依賴於他們的名字。為了指定哪種操作符做什麼操作，引入策略的概念。為了描述操作符語義，定義了5種策略：

    1 — less

    2 — less or equal

    3 — equal

    4 — greater or equal

    5 — greater

 

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

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;
  opfamily_operator  
---------------------- 
 <(integer,bigint)
 <(smallint,smallint)
 <(integer,integer)
 <(bigint,bigint)
 <(bigint,integer)
 <(smallint,integer)
 <(integer,smallint)
 <(smallint,bigint)
 <(bigint,smallint)
(9 rows)

正因如此，當比較型別在一個操作符族中時，不同型別值的比較，優化器可以避免型別轉換。

索引支援的新資料型別

文件中提供了一個建立符合數值的新資料型別，以及對這種型別資料進行排序的操作符類。該案例使用 C語言完成。但不妨礙我們使用純SQL進行對比試驗。

 

建立一個新的組合型別：包含 real和imaginary兩個欄位 

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

建立一個包含該新組合型別欄位的表： 

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

 

現在有個疑問，如果在數學上沒有為他們定義順序關係，如何進行排序？

已經定義好了比較運算子： 

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

預設情況下，對於組合型別排序是分開的：首先比較第一個欄位然後第二個欄位，與文字字串比較方法大致相同。但是我們也可以定義其他的排序方式，例如組合數字可以當做一個向量，通過模值進行排序。為了定義這樣的順序，我們需要建立一個函式： 

postgres=# create function modulus(a complex) returns float as $$
    select sqrt(a.re*a.re + a.im*a.im);
$$ immutable language sql;

 

此時，使用整個函式系統的定義 5種操作符： 

postgres=# create function complex_lt(a complex, b complex) returns boolean as $$
    select modulus(a) < modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_le(a complex, b complex) returns boolean as $$
    select modulus(a) <= modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_eq(a complex, b complex) returns boolean as $$
    select modulus(a) = modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_ge(a complex, b complex) returns boolean as $$
    select modulus(a) >= modulus(b);
$$ immutable language sql;
 
postgres=# create function complex_gt(a complex, b complex) returns boolean as $$
    select modulus(a) > modulus(b);
$$ immutable language sql;

然後建立對應的操作符： 

postgres=# create operator #<#(leftarg=complex, rightarg=complex, procedure=complex_lt);
 
postgres=# create operator #<=#(leftarg=complex, rightarg=complex, procedure=complex_le);
 
postgres=# create operator #=#(leftarg=complex, rightarg=complex, procedure=complex_eq);
 
postgres=# create operator #>=#(leftarg=complex, rightarg=complex, procedure=complex_ge);
 
postgres=# create operator #>#(leftarg=complex, rightarg=complex, procedure=complex_gt);

此時，可以比較數字： 

postgres=# select (1.0,1.0)::complex #<# (1.0,3.0)::complex;
 ?column?
----------
 t
(1 row)

除了整個 5個操作符，還需要定義函式：小於返回-1；等於返回0；大於返回1。其他訪問方法可能需要定義其他函式： 

postgres=# create function complex_cmp(a complex, b complex) returns integer as $$
    select case when modulus(a) < modulus(b) then -1
                when modulus(a) > modulus(b) then 1 
                else 0
           end;
$$ language sql;

建立一個操作符類： 

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);

排序結果： 

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

可以使用此查詢獲取支援的函式： 

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)

內部結構

使用 pageinspect外掛觀察B-tree結構： 

demo=# create extension pageinspect;

索引的後設資料頁： 

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


值得關注的是索引 level：不包括root，有一百萬行記錄的表其索引只需要2層就可以了。

Root頁，即164號頁面的統計資訊： 

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)


該頁中資料： 

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)


第一個 tuple指定該頁的最大值，真正的資料從第二個tuple開始。很明顯最左邊子節點的頁號是163，然後是323。反過來，可以使用相同的函式搜尋。

PG10版本提供了"amcheck"外掛，該外掛可以檢測B-tree資料的邏輯一致性，使我們提前探知故障。

原文

https://habr.com/en/company/postgrespro/blog/443284/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31493717/viewspace-2647023/，如需轉載，請註明出處，否則將追究法律責任。

相關文章