大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说解析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)
代码100分
代码100分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)
代码100分bt_page_items函数是用来：返回关于B-树索引页面上所有项的详细信息，在B树叶子页面中，ctid指向一个堆元组。在内部页面中，ctid的块编号部分指向索引本身中的另一个页面。
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)
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)
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)
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)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8012.html
