Postgres是如何管理空值的
创建表test,y字段插入null.
test=# create table test(x bigint,y bigint,z text); CREATE TABLE test=# insert into test values(11,null,'abcdefg'); INSERT 0 1 test=# select * from test; x | y | z ----+---+--------- 11 | | abcdefg (1 row)
这条记录存储在数据页里面是:
(gdb) p *lpp
$17 = {lp_off = 8152, lp_flags = 1, lp_len = 40}
占了40个字节,TupleHeader是24个字节，40-24=16。
我们再插入一条数据看看
test=# insert into test values(22,100,'xyz'); INSERT 0 1 test=# select * from test; x | y | z ----+-----+--------- 11 | | abcdefg 22 | 100 | abcdefg (2 rows)
再看看新插入的记录:
(gdb) p *lpp
$19 = {lp_off = 8104, lp_flags = 1, lp_len = 48}
这2条数据对比
第一条记录:
insert into test values(11,null,'abcdefg');
字段x是bigint,8字节
字段y是bigint,8字节
字段z是text,字符串strlen('abcdefg')=7,8字节对齐。
8152+40=8192
第二条记录：
insert into test values(22,100,'abcdefg');
8+8+8=24
24+TupleHeader=48字节
8104+48=8152
我们来看看第一条是如何存储NULL的：
代码:src/include/access/tupmacs.h
#define att_isnull(ATT, BITS) (!((BITS)[(ATT) >> 3] & (1 << ((ATT) & 0x07))))
这就是计算字段是否为NULL的宏
PG通过t_bits来标记是否为null.
struct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap;
DatumTupleFields t_datum;
} t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a
* speculative insertion token) */
/* Fields below here must match MinimalTupleData! */
uint16 t_infomask2; /* number of attributes + various flags */
uint16 t_infomask; /* various flag bits, see below */
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */
/* MORE DATA FOLLOWS AT END OF STRUCT */
};
t_bits是一个字节，那么就有8位。所有上面的宏需要根据ATT来移3位。ATT>>3
这是算这个字段是在第几个数组下标
例如我这里查询的是第一个字段1>>3 = 0 就是在第一个数组下标
ATT & 0x07 求字段顺序 第一条是 0 & 0x07 = 0 ,如果是第八个字段也是0，范围就是0-7
test=# select * from test; x | y | z ----+-----+--------- 11 | | abcdefg 22 | 100 | abcdefg (2 rows)
第一条数据t_bits是这样的
0 0 0 0 0 1 0 1
根据上面的宏计算结果:
第一个字段:
(BITS)[(ATT) >> 3] = BITS[0]
(ATT) & 0x07 = 0 & 0x07 = 0
1 << 0 = 1
结果就是
0 0 0 0 0 1 0 1
0 0 0 0 0 0 0 1
!(BITS[0] & 1) = 0
代表数据不算为NULL.
第二个字段:
(BITS)[(ATT) >> 3] = BITS[0]
(ATT) & 0x07 = 1 & 0x07 = 1
1 << 1 = 2
结果就是
0 0 0 0 0 1 0 1
0 0 0 0 0 0 1 0
!(BITS[0] & 2) = 1
代表第二个字段为NULL
我们再插入一条数据
test=# insert into test values(null,null,'abcdefg');
INSERT 0 1
test=# select * from test;
x | y | z
----+-----+---------
11 | | abcdefg
22 | 100 | abcdefg
| | abcdefg
(3 rows)
我们主要是看新增加的这条数据
(gdb) p *lpp
$42 = {lp_off = 8072, lp_flags = 1, lp_len = 32}
(gdb)
新插入的数据只占了32个字节 TupleHeader+8
t_bits = 0 0 0 0 0 1 0 0
利用上面的宏也很好的算出前面2个字段为NULL
我们来看看超过8个字段的情况
test=# create table test_more_column(
test(# col1 bigint,
test(# col2 bigint,
test(# col3 bigint,
test(# col4 bigint,
test(# col5 bigint,
test(# col6 bigint,
test(# col7 bigint,
test(# col8 bigint,
test(# col9 bigint,
test(# col10 bigint
test(# );
CREATE TABLE
test=# insert into test_more_column values(1,2,null,4,5,null,7,8,null,10);
INSERT 0 1
test=# select * from test_more_column ;
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10
------+------+------+------+------+------+------+------+------+-------
1 | 2 | | 4 | 5 | | 7 | 8 | | 10
(1 row)
test=#
首先看看item
(gdb) p *lpp
$1 = {lp_off = 8104, lp_flags = 1, lp_len = 88}
(gdb)
总共88个字节
(gdb) p *tuple->t_data
$3 = {t_choice = {t_heap = {t_xmin = 4414, t_xmax = 0, t_field3 = {t_cid = 0, t_xvac = 0}}, t_datum = {datum_len_ = 4414, datum_typmod = 0, datum_typeid = 0}}, t_ctid = {ip_blkid =
{bi_hi = 0, bi_lo = 0}, ip_posid = 1}, t_infomask2 = 10, t_infomask = 2305, t_hoff = 32 ' ', t_bits = 0x7f8c9af9ef5f "\333\002"}
首先看看头偏移量就改变了不是前面的24个字节。是因为字段超过了8 需要用2个bit来标记NULL，而PG又是8字节对齐所以是24+8=32
88-32=56 总共88字节减去头32 数据占56字节
7 * 8 = 56 上面总共有7个字段存储了值，每个占8字节就是56字节
(gdb) p bp $8 = (bits8 *) 0x7f8c9af9ef5f "\333\002" (gdb) p sizeof(bp) $9 = 8 (gdb)
数组的值
(gdb) p bp[0] $10 = 219 '\333' (gdb) p bp[1] $11 = 2 '\002'
bp[0] = 1 1 0 1 1 0 1 1 = 1 +2 +8 +16 +64 +128 = 219
bp[1] = 0 0 0 0 0 0 1 0 = 2
bp[3] = 0 0 0 0 0 0 0 0 = 0
......
bp[7] = 0 0 0 0 0 0 0 0 = 0
根据上面的宏att_isnull 就能很好的判断出那个字段是NULL。这样就非常的节省了数据存储空间。
