2020-06-09 19:31:01
一、疑問
前段時間；QQ群里有人對“這個表（0,4）這行數據我做了update操作，查看索引的page數據，看到索引一直指向（0,4），用ctid='(0,4)’查詢業務表是查不到數據的；然後我做了表的vacuum，reindex甚至drop/create index，還是這樣的”感到疑惑。
在PostgreSQL8.3實現了（heap only tuple）HOT特性。它存在的目的就是消除表非索引列更新對索引影響。但是它如何工作的呢？
二、解析
我們來模擬環境
postgres=# create table tbl_hot(id int primary key, info text); CREATE TABLE postgres=# insert into tbl_hot select generate_series(1, 4), 'lottu'; INSERT 0 4 postgres=# select ctid ,t.* from tbl_hot t; ctid | id | info -------+----+------- (0,1) | 1 | lottu (0,2) | 2 | lottu (0,3) | 3 | lottu (0,4) | 4 | lottu (4 rows) postgres=# \d tbl_hot Table "public.tbl_hot" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | not null | info | text | | | Indexes: "tbl_hot_pkey" PRIMARY KEY, btree (id)
我們創建表tbl_hot；並插入4條記錄。這是我們更新（0,4）這條記錄。如下
postgres=# update tbl_hot set info = 'rax' where id = 4; UPDATE 1 postgres=# select ctid ,t.* from tbl_hot t; ctid | id | info -------+----+------- (0,1) | 1 | lottu (0,2) | 2 | lottu (0,3) | 3 | lottu (0,5) | 4 | rax (4 rows)
更新之後我們看下索引有變化沒？
postgres=# select * from bt_page_items('tbl_hot_pkey', 1); itemoffset | ctid | itemlen | nulls | vars | data ------------+-------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 (4 rows)
bt_page_items函數是用來：返回關於B-樹索引頁面上所有項的詳細信息，在B樹恭弘=叶 恭弘子頁面中，ctid指向一個堆元組。在內部頁面中，ctid的塊編號部分指向索引本身中的另一個頁面。
我們可以看出索引沒變化。索引存放是表數據的ctid+索引值。使用索引可以快速找到對應記錄的ctid。現在 記錄id=4 索引的ctid（0,4）跟表對應ctid(0,5)不一致。那是不是索引失效了。我們來測試下
postgres=# explain select id from tbl_hot where id = 4; QUERY PLAN --------------------------------------------------------------------------------- Index Only Scan using tbl_hot_pkey on tbl_hot (cost=0.15..8.17 rows=1 width=4) Index Cond: (id = 4) (2 rows)
索引沒失效；那如何找到對應的記錄呢？我們先來看下錶存儲的page情況
get_raw_page: 根據參數表明、數據文件類型（main、fsm、vm）以及page位置，將當前表文件中的page內容返回。還有一個函數於此同名，只有兩個參數，是將第二個參數省略，直接使用'main'。
heap_page_items: 參數是函數get_raw_page的返回值，返回值是將page內的項指針(ItemIddata)以及HeapTupleHeaderData的詳細信息。
其中理解下下面字段含義
lp：這是插件自己定義的列，在源碼中其實沒有，這個是項指針的順序。
lp_off：tuple在page中的位置
lp_flags： 含義如下
#define LP_UNUSED 0 /* unused (should always have lp_len=0) */
#define LP_NORMAL 1 /* used (should always have lp_len>0) */
#define LP_REDIRECT 2 /* HOT redirect (should have lp_len=0) */
#define LP_DEAD 3 /* dead, may or may not have storage */
t_ctid： 這個是指物理ID
t_infomask2：表字段的個數以及一些flags；其中flag含義
#define HEAP_NATTS_MASK 0x07FF
/* 11 bits for number of attributes *//* bits 0x1800 are available */
#define HEAP_KEYS_UPDATED 0x2000
/* tuple was updated and key cols* modified, or tuple deleted */
#define HEAP_HOT_UPDATED 0x4000 /* tuple was HOT-updated */
#define HEAP_ONLY_TUPLE 0x8000 /* this is heap-only tuple */
#define HEAP2_XACT_MASK 0xE000 /* visibility-related bits */
postgres=# select * from heap_page_items(get_raw_page('tbl_hot', 0)); lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------ 1 | 8152 | 1 | 34 | 554 | 0 | 0 | (0,1) | 2 | 2306 | 24 | | | \x010000000d6c6f747475 2 | 8112 | 1 | 34 | 554 | 0 | 0 | (0,2) | 2 | 2306 | 24 | | | \x020000000d6c6f747475 3 | 8072 | 1 | 34 | 554 | 0 | 0 | (0,3) | 2 | 2306 | 24 | | | \x030000000d6c6f747475 4 | 8032 | 1 | 34 | 554 | 555 | 0 | (0,5) | 16386 | 1282 | 24 | | | \x040000000d6c6f747475 5 | 8000 | 1 | 32 | 555 | 0 | 0 | (0,5) | 32770 | 10498 | 24 | | | \x0400000009726178 (5 rows)
我們來理下：我們通過條件id=4；如何找到對應的記錄
- 找到指向目標數據tuple的索引tuple(0,4)
- 根據獲取索引tuple的位置(0,4)；找到行指針lp為4的位置。即對應的ctid為(0,5)
- 根據ctid為(0,5)；我們可以找到兩條tuple。根據PG的MVCC機制連判斷哪條tuple可見
- 可以找到對應tuple
更新多次原理也差不多。
這個時候你會有一個疑問“執行vacuum；清理表tuple(0,4);少了步驟2；那上面的流程就走不通了”。我們來解析下：
postgres=# vacuum tbl_hot; VACUUM postgres=# select lp, lp_off, lp_flags, t_ctid, t_infomask2 from heap_page_items(get_raw_page('tbl_hot', 0)); lp | lp_off | lp_flags | t_ctid | t_infomask2 ----+--------+----------+--------+------------- 1 | 8152 | 1 | (0,1) | 2 2 | 8112 | 1 | (0,2) | 2 3 | 8072 | 1 | (0,3) | 2 4 | 5 | 2 | | 5 | 8040 | 1 | (0,5) | 32770 (5 rows)
這時；為了解決這個問題，postgresql會在合適的時候進行行指針的重定向（redirect）,這個過程稱為修剪。現在按照這種情況我們來理下：我們通過條件id=4；如何找到對應的記錄
- 找到指向目標數據tuple的索引tuple(0,4)
- 根據獲取索引tuple的位置(0,4)；找到行指針lp為4的位置；這是lp_flags為2表示指針重定向lp為5；即行指針對應的位置是8040
- 通過指針可以找到對應tuple。
這是tuple(0,4)；既然vacuum；表示可以再使用；但是這是標記是LP_REDIRECT；表明tuple非dead tuple；未進行回收；不可以重複使用。這時你可能會有一個疑問“那什麼時候可以回收？”；答案是這個tuple(0,4)不會標記dead tuple。但是執行vacuum;該page是可以回收空間；這個是PG的MVCC處理機制-vacuum的內容；可以分到下個篇幅再講。這裏我們可以簡單演示下：
postgres=# update tbl_hot set info = 'postgres' where id = 4; UPDATE 1 postgres=# select lp, lp_off, lp_flags, t_ctid, t_infomask2 from heap_page_items(get_raw_page('tbl_hot', 0)); lp | lp_off | lp_flags | t_ctid | t_infomask2 ----+--------+----------+--------+------------- 1 | 8152 | 1 | (0,1) | 2 2 | 8112 | 1 | (0,2) | 2 3 | 8072 | 1 | (0,3) | 2 4 | 5 | 2 | | 5 | 8040 | 1 | (0,6) | 49154 6 | 8000 | 1 | (0,6) | 32770 (6 rows) postgres=# vacuum tbl_hot; VACUUM postgres=# select lp, lp_off, lp_flags, t_ctid, t_infomask2 from heap_page_items(get_raw_page('tbl_hot', 0)); lp | lp_off | lp_flags | t_ctid | t_infomask2 ----+--------+----------+--------+------------- 1 | 8152 | 1 | (0,1) | 2 2 | 8112 | 1 | (0,2) | 2 3 | 8072 | 1 | (0,3) | 2 4 | 6 | 2 | | 5 | 0 | 0 | | 6 | 8032 | 1 | (0,6) | 32770 (6 rows) postgres=# select ctid,t.* from tbl_hot t; ctid | id | info -------+----+---------- (0,1) | 1 | lottu (0,2) | 2 | lottu (0,3) | 3 | lottu (0,5) | 5 | lottu (0,6) | 4 | postgres (5 rows)
最後；當更新的元祖是在其他page；這是索引也會更新；這可以理解是行遷移。這在oracle也是存在這種情況。但是相比oracle更頻繁；當然可以設置降低fillfactor；減少這種情況出現。
三、參考
https://blog.csdn.net/xiaohai928ww/article/details/98603707
https://www.postgresql.org/docs/12/pageinspect.html
本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理
【其他文章推薦】
※別再煩惱如何寫文案,掌握八大原則!
※網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!
※超省錢租車方案
※教你寫出一流的銷售文案?
※網頁設計最專業,超強功能平台可客製化
※產品缺大量曝光嗎?你需要的是一流包裝設計!
※台中搬家遵守搬運三大原則,讓您的家具不再被破壞!