KingbaseES V8R6 fillfactor 对于表的影响
前言
fillfactor
表的填充因子是一个介于 10 和 100 之间的百分数。100是默认值。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本(MVCC)放置在其它后面的页上更高效。对于一个从不更新或很少更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，较小的填充因子执行效率更高。该参数对toast表不生效。
索引的填充因子也是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于B-tree，在初始的索引构建过程中，叶子页面会被填充至该百分数，B-tree默认的填充因子是90，可以设置为10-100的任何整数值。如果表是静态的，那么填充因子100是最好的，这样索引占用空间最小。对于更新频繁的表，设置较小的值可以最小化索引分裂。
测试环境使用版本为V8R6，少部分内容在R3上进行了测试。
测试
--创建表test1并设置fillfactor=100
test=# create table test1(n_id int,cc varchar(300)) with (fillfactor=100);
CREATE TABLE
--创建表test2并书设置fillfactor=70
test=# create table test2(n_id int,cc varchar(300)) with (fillfactor=70);
CREATE TABLE
--添加主键
test=# alter table test1 add primary key(n_id);
ALTER TABLE
test=# alter table test2 add primary key(n_id);
ALTER TABLE
--插入数据耗时差别不大，test2表设置了fillfactor=70，占用空间更大。索引占用空间一样。
test=# insert into test1 select generate_series(1,1000000),'tttt'||generate_series(1,1000000);
INSERT 0 1000000
Time: 2500.057 ms (00:02.500)
test=# insert into test2 select generate_series(1,1000000),'tttt'||generate_series(1,1000000);
INSERT 0 1000000
Time: 2576.616 ms (00:02.577)
test=# vacuum analyze test1;
VACUUM
test=# vacuum analyze test2;
--查看表的页数
test=# select relpages,reltuples from sys_class where relname = 'test1';
relpages | reltuples
----------+-----------
5435 | 1e+06
(1 row)
test=# select relpages,reltuples from sys_class where relname = 'test2';
relpages | reltuples
----------+-----------
7752 | 1e+06
(1 row)
--查看表结构
TEST=# \d+ test1
Table "public.test1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
n_id | integer | | not null | | plain | |
cc | character varying(300 char) | | | | extended | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (n_id)
Access method: heap
Options: fillfactor=100
TEST=# \d+ test2
Table "public.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
n_id | integer | | not null | | plain | |
cc | character varying(300 char) | | | | extended | |
Indexes:
"test2_pkey" PRIMARY KEY, btree (n_id)
Access method: heap
Options: fillfactor=70
--查看表大小，fillfactor设置越大占用的空间越小
TEST=# select sys_size_pretty(sys_relation_size('test1'));
sys_size_pretty
-----------------
42 MB
(1 row)
Time: 0.642 ms
TEST=# select sys_size_pretty(sys_relation_size('test2'));
sys_size_pretty
-----------------
61 MB
(1 row)
Time: 0.820 ms
--查看主键大小
test=# select sys_size_pretty(sys_relation_size('test1_pkey'));
sys_size_pretty
----------------
22 MB
(1 row)
test=# select sys_size_pretty(sys_relation_size('test2_pkey'));
sys_size_pretty
----------------
22 MB
(1 row)
更新数据时，R6版本，更新后，ctid没有明显区别，都是在第一页更新
R3版本更新后citd位置不同，
设置了fillfactor=100后，没有预留update空间，更新数据会在最后一页插入一条数据
而设置fillfactor=70，数据会在当前页插入一条数据
R6版本更新test1
test=# select ctid,* from test1 where n_id =1;
ctid | n_id | cc
-------+------+-----------
(0,1) | 1 | tttt1
(1 row)
test=# update test1 set cc='ll' where n_id = 1;
UPDATE 1
更新test1， fillfactor为100，更新后，数据仍然插入到了第一页ctid为(0,185)
TEST=# select ctid,* from test1 where n_id =1;
ctid | n_id | cc
---------+------+----
(0,185) | 1 | ll
(1 row)
更新test2
test=# select ctid,* from test2 where n_id =1;
ctid | n_id | cc
-------+------+-----------
(0,1) | 1 | tttt1
(1 row)
Time: 1.392 ms
test=# update test2 set cc='ll' where n_id = 1;;
UPDATE 1
test2表的fillfactor为70，还剩余20%的空间可以利用，更新后数据可以在第一页插入这条数据，ctid为 (0,130)
TEST=# select ctid,* from test2 where n_id =1;
ctid | n_id | cc
---------+------+----
(0,130) | 1 | ll
(1 row)
下面进行R3版本更新测试，重复以上步骤...
更新test1
TEST=# select ctid,* from test1 where n_id =1;
CTID | N_ID | CC
-------+------+-------
(0,1) | 1 | tttt1
(1 row)
Time: 0.714 ms
TEST=# update test1 set cc='ll' where n_id = 1;
UPDATE 1
Time: 2.846 ms
TEST=# select ctid,* from test1 where n_id =1;
CTID | N_ID | CC
-----------+------+----
(5405,76) | 1 | ll
(1 row)
更新test2
test=# select ctid,* from test2 where n_id =1;
ctid | n_id | cc
-------+------+-----------
(0,1) | 1 | tttt1
(1 row)
Time: 1.392 ms
test=# update test2 set cc='ll' where n_id = 1;;
UPDATE 1
R3版本更新test2表，fillfactor为70，更新后数据也可以在第一页插入这条数据
TEST=# select ctid,* from test2 where n_id =1;
ctid | n_id | cc
---------+------+----
(0,130) | 1 | ll
(1 row)
更新效率
--为了看出明显的效果，先将autovacuum关闭掉，更新test1的所有数据
在全量数据update的时候fillfactor=70的效率略高，少量数据update更新时候，低fillfactor表的效果更明显。
TEST=# update test1 set cc = cc||'xx';
UPDATE 1000000
Time: 4954.257 ms (00:04.954)
TEST=# update test2 set cc = cc||'xx';
UPDATE 1000000
Time: 3893.509 ms (00:03.894)
TEST=# update test1 set cc = cc||'ee';
UPDATE 1000000
Time: 5649.721 ms (00:05.650)
TEST=# update test2 set cc = cc||'ee';
UPDATE 1000000
Time: 5041.635 ms (00:05.042)
TEST=# update test1 set cc = cc||'tt';
UPDATE 1000000
Time: 5893.175 ms (00:05.893)
TEST=# update test2 set cc = cc||'tt';
UPDATE 1000000
Time: 5347.697 ms (00:05.348)
--经过三次全部更新来看，设置fillfactor=70时，更新的速度略快。
--更新小范围数据，test2表速度更快
TEST=# update test1 set cc = cc||'xxxx' where n_id>100 and n_id <200;
UPDATE 99
Time: 10.343 ms
TEST=# update test2 set cc = cc||'xxxx' where n_id>100 and n_id <200;
UPDATE 99
Time: 2.016 ms
更新后索引大小
可以看到设置了fillfactor=70表的索引比fillfactor=100的索引要小，而fillfactor=100膨胀的更快，这里 fillfactior和HOT技术结合起来看就不能理解，
当有空闲空间的时候，更新可能会用到HOT，数据是在一页内变动，所以索引会比默认fillfactor小。
--test1表的索引更大
TEST=# select sys_size_pretty(sys_relation_size('test1_pkey'));
sys_size_pretty
-----------------
86 MB
(1 row)
Time: 4.932 ms
TEST=# select sys_size_pretty(sys_relation_size('test2_pkey'));
sys_size_pretty
-----------------
69 MB
(1 row)
Time: 0.587 ms
总结
1.初始插入数据时，设置了不同fillfactor耗时差别不大，设置了fillfactor=70的表占用空间更大，新建的索引占用空间一样。
2.R3版本数据库中设置了fillfactor=100后，更新数据会在最后一页插入数据，而设置fillfactor=70，数据会在当前页空闲空间插入数据。
而R6版本数据库做了相关优化，fillfactor=100的表也可以插入数据到第一页。
3.在更新较频繁的表降低fillfactor可以提高更新效率，因为HOT技术，减小fillfactor的表，可以减小其索引膨胀。
4.在update（全量）的时候fillfactor=70的效率略高，少量数据更新的时候设置低的fillfactor效果更高。
KINGBASE研究院
