金仓数据库索引技术在复杂查询场景下的表现与优化-青学会&金仓专栏(12)
张鹏约 25 分钟点击上方蓝字，关注我们
想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。
加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。
同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。
如果你有想了解的知识点希望我们发文可以后台私信。
本期投稿人
张鹏，东华软件金融大数据DBA，墨天轮社区专家。
正文开始
为响应青学会MOP社区，在学习kingbase的过程中，对相关感兴趣的问题做了验证并记录投稿，内容如下：
一、kingbase B+索引分裂及Null值测试验证
二、Kingbase使用索引表连接情况测试验证
三、Kingbase Null值行为验证
准备工作：安装kingbase数据库，版本如下：
testdb=# select version(); version
\----------------------------------------------------------------------------------------------------------------------
KingbaseES V009R001C001B0030 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
一、kingbase B+索引分裂及Null测试验证
参见大佬们的文章， B+树索引的基本结构如下，下面验证一下此结构
-- 测试表drop table testnull cascade constraints;
create table testnull(a int primary key,b char(1000));
create index idx_b on testnull(b);
insert into testnull values(1,lpad('a',1000,'a'));
insert into testnull values(2,lpad('b',1000,'b'));
insert into testnull values(3,lpad('c',1000,'c'));
insert into testnull values(4,lpad('d',1000,'d'));
insert into testnull values(5,lpad('e',1000,'e'));
insert into testnull values(6,lpad('f',1000,'f'));
insert into testnull values(7,lpad('g',1000,'g'));
testdb=#
testdb=# vacuum analyze testnull;
VACUUM
testdb=#
-- 查看索引meta元数据
testdb=# select * from bt_metap('idx_b');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+------+-------+----------+-----------+-------------+-------------------------
340322 | 4 | 1 | 0 | 1 | 0 | 0 | 7
(1 row)
以上可知，root页是page1，索引只有1层，只有一个root节点，查看page1状态
testdb=# select * from bt_page_stats('idx_b',1); blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
1 | l | 7 | 0 | 32 | 8192 | 7864 | 0 | 0 | 0 | 3
(1 row)
btpo_flags=3 同样说明只有一个root节点，下面查看page1内容
testdb=# select * from bt_page_items('idx_b',1); itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------------------------------------------------------
1 | (0,1) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 61 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
2 | (0,2) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 62 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
3 | (0,3) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 63 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
4 | (0,4) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 64 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
5 | (0,5) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 65 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
6 | (0,6) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 66 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
7 | (0,7) | 32 | f | t | 5a 00 00 00 e8 03 00 00 1e 67 0f 01 ff 0f 01 ff 0f 01 ff 0f 01 a2 00 00
itemlen=32 ,远小于键值长度1000，因此，可知kingbase/pg对键值为重复字符时做了优化，不会存储整个键值，因此测试时必须使用随机数来生成键值以观察分裂，这里用函数生成998个字符加前两位固定值以利于查找。
-- 生成随机字符串函数CREATE OR REPLACE FUNCTION generate_random_string(length INT)
RETURNS TEXT AS $$
DECLARE
chars TEXT := 'abcdefghijklmnopqrstuvwxyz0123456789';
result TEXT := '';
i INT;
BEGIN
FOR i IN 1..length LOOP
result := result || substr(chars, floor(random() * length(chars) + 1)::INT, 1);
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;
-- 创建测试用表drop table testnull;
create table testnull(a int primary key,b char(1000));
create index idx_b on testnull(b);
-- 对于索引页，包括键值和指针，key值为1000 byte,页指针加上其它属性大致16 bute，一个8K的索引页能存储7个键值，插入8个键值时，必然引起分裂，下面验证这个过程
insert into testnull values(1,'11'||generate_random_string(998));
insert into testnull values(2,'22'||generate_random_string(998));
insert into testnull values(3,'33'||generate_random_string(998));
insert into testnull values(4,'44'||generate_random_string(998));
insert into testnull values(5,'55'||generate_random_string(998));
insert into testnull values(6,'66'||generate_random_string(998));
insert into testnull values(7,'77'||generate_random_string(998));
-- 收集一下相关信息testdb=# vacuum analyze testnull;
testdb=# select * from bt_metap('idx_b');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+------+-------+----------+-----------+-------------+-------------------------
340322 | 4 | 1 | 0 | 1 | 0 | 0 | 7
(1 row)
-- 由上可以看到root/fastroot页为 page 1testdb=# select * from bt_page_stats('idx_b',1);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
1 | l | 7 | 0 | 1016 | 8192 | 976 | 0 | 0 | 0 | 3
(1 row)
--由上btpo_flags=3可以得出，page1 为 root页和leaf页-- 查看page1页内容
testdb=# select * from bt_page_items('idx_b',1);
itemoffset | ctid | itemlen | nulls | vars |
1 | (0,1) | 1016 | f | t | b0 0f 00 00 31 31 6a 7a 66 62 6f 74 64 33 6a 37 61 73 6e 79 79 73 6a 39 61 68 66 79 61 7a 66 71 73 39 35 6b 69 66 6b 32 38 30 30 61 79 3
2 | (0,2) | 1016 | f | t | b0 0f 00 00 32 32 66 38 6c 68 79 6e 31 64 36 38 73 78 6d 6c 75 63 70 31 37 77 77 70 32 6c 6f 72 31 62 34 38 7a 6c 76 63 65 69 61 39 6c 6
3 | (0,3) | 1016 | f | t | b0 0f 00 00 33 33 31 33 31 73 78 6c 6e 79 73 64 71 67 79 62 79 6c 7a 67 62 62 72 30 73 78 64 35 36 79 6d 78 62 63 31 32 33 63 37 66 62 6
4 | (0,4) | 1016 | f | t | b0 0f 00 00 34 34 76 7a 68 74 70 38 70 79 79 7a 65 65 76 68 74 71 6c 61 35 31 31 36 6e 34 36 6a 78 71 30 6b 6d 61 77 63 76 78 30 79 75 7
5 | (0,5) | 1016 | f | t | b0 0f 00 00 35 35 37 7a 64 37 61 37 34 37 71 6c 38 61 6a 66 6e 66 38 34 63 38 38 39 30 36 35 69 66 67 61 71 34 75 37 74 30 74 38 78 69 6
6 | (0,6) | 1016 | f | t | b0 0f 00 00 36 36 77 6d 62 36 7a 66 6a 77 6d 6e 6c 67 39 35 63 67 73 7a 63 37 78 75 6e 69 31 37 68 38 7a 7a 35 68 76 35 6a 31 73 75 33 7
7 | (0,7) | 1016 | f | t | b0 0f 00 00 37 37 36 76 36 68 6c 6c 66 6c 31 67 71 6e 35 36 66 6b 30 34 7a 36 64 72 73 64 35 78 6d 6c 37 6e 6a 61 72 6b 69 35 6a 61 77 7
-- 以上可以看出 page1里存储了7个键值，键值+指=1016字节，vars即是存储的键值，对应b列数据的ascii值， 3131 即11 ，3232即22 ，因为leaf页存储的ctid值即是表数据的地址，因此表ctid=(0,1) 即表数据page 0 存值的是此键值对应的记录
-- 验证此指针对应的表记录
testdb=# select * from testnull where ctid='(0,1)';
1 | 11jzfbotd3j7asnyysj9ahfyazfqs95kifk2800ay17odhjv....
(1 row)
下面验证索引页分裂
-- 插入两条记录引起索引分裂insert into testnull values(8,'88'||generate_random_string(998));
insert into testnull values(9,'99'||generate_random_string(998));
-- 更新统计信息
testdb=# vacuum analyze testnull;
VACUUM
-- 查看索引页元数据
testdb=# select * from bt_metap('idx_b');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+------+-------+----------+-----------+-------------+-------------------------
340322 | 4 | 3 | 1 | 3 | 1 | 0 | 9
(1 row)
-- 以上 level=1 ,此时索引树分裂为两层，root/fastroot=3
-- 查看root页状态
testdb=# select * from bt_page_stats('idx_b',3);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
3 | r | 2 | 0 | 512 | 8192 | 7084 | 0 | 0 | 1 | 2
(1 row)
-- btpo_flags=2 表示为root页
-- 查看root页的内容
testdb=# select * from bt_page_items('idx_b',3);
itemoffset | ctid | itemlen | nulls | vars |
1 | (1,0) | 8 | f | f |
2 | (2,1) | 1016 | f | t | b0 0f 00 00 37 37 36 76 36 68 6c 6c 66 6c 31 67 71 6e 35 36 66 6b 30 34 7a 36 64 72 73 64 35 78 6d 6c 37 6e 6a 61 72 6b 69 35 6a 61 77 7
--以上可知，因为索引树已分裂为两层，索引页 page1 page2分别指向了索引树的两个leaf页，这里itemlen=8 ,指针大致8 byte
(1,0)指针指向了索引树的最左侧节点
(2,1)指针指向了索引树的最右侧节点
--查看索引树 page1内容testdb=# select * from bt_page_items('idx_b',1);
itemoffset | ctid | itemlen | nulls | vars |
1 | (0,1) | 1016 | f | t | b0 0f 00 00 37 37 36 76 36 68 6c 6c 66 6c 31 67 71 6e 35 36 66 6b 30 34 7a 36 64 72 73 64 35 78 6d 6c 37 6e 6a 61 72 6b 69 35 6a 61 77 7
2 | (0,1) | 1016 | f | t | b0 0f 00 00 31 31 6a 7a 66 62 6f 74 64 33 6a 37 61 73 6e 79 79 73 6a 39 61 68 66 79 61 7a 66 71 73 39 35 6b 69 66 6b 32 38 30 30 61 79 3
3 | (0,2) | 1016 | f | t | b0 0f 00 00 32 32 66 38 6c 68 79 6e 31 64 36 38 73 78 6d 6c 75 63 70 31 37 77 77 70 32 6c 6f 72 31 62 34 38 7a 6c 76 63 65 69 61 39 6c 6
4 | (0,3) | 1016 | f | t | b0 0f 00 00 33 33 31 33 31 73 78 6c 6e 79 73 64 71 67 79 62 79 6c 7a 67 62 62 72 30 73 78 64 35 36 79 6d 78 62 63 31 32 33 63 37 66 62 6
5 | (0,4) | 1016 | f | t | b0 0f 00 00 34 34 76 7a 68 74 70 38 70 79 79 7a 65 65 76 68 74 71 6c 61 35 31 31 36 6e 34 36 6a 78 71 30 6b 6d 61 77 63 76 78 30 79 75 7
6 | (0,5) | 1016 | f | t | b0 0f 00 00 35 35 37 7a 64 37 61 37 34 37 71 6c 38 61 6a 66 6e 66 38 34 63 38 38 39 30 36 35 69 66 67 61 71 34 75 37 74 30 74 38 78 69 6
7 | (0,6) | 1016 | f | t | b0 0f 00 00 36 36 77 6d 62 36 7a 66 6a 77 6d 6e 6c 67 39 35 63 67 73 7a 63 37 78 75 6e 69 31 37 68 38 7a 7a 35 68 76 35 6a 31 73 75 33 7
-- 以上可知 itemoffset=1 记录了max值，即索引树下个页的最小值，即本页的值要小于此值， itemoffset=2-7 记录了6个key值，11 22 33 44 55 66 ,即原来的root页发生了分裂，77值已移到下页，下面验证
--查看索引树的page2页的内容
testdb=# select * from bt_page_items('idx_b',2);
itemoffset | ctid | itemlen | nulls | vars |
1 | (0,7) | 1016 | f | t | b0 0f 00 00 37 37 36 76 36 68 6c 6c 66 6c 31 67 71 6e 35 36 66 6b 30 34 7a 36 64 72 73 64 35 78 6d 6c 37 6e 6a 61 72 6b 69 35 6a 61 77 7
2 | (1,1) | 1016 | f | t | b0 0f 00 00 38 38 35 6a 62 32 71 39 72 7a 70 35 78 75 7a 79 67 6e 73 31 61 31 38 61 7a 71 75 70 37 69 61 37 37 39 39 76 77 30 61 32 30 3
3 | (1,2) | 1016 | f | t | b0 0f 00 00 39 39 66 72 75 6c 6f 37 64 69 6a 6f 72 35 6f 33 67 65 73 39 65 32 6e 68 7a 64 67 31 35 6f 76 33 6f 34 35 79 77 31 65 73 36 6
-- 以上可知，此页是索引树的最右侧节点，因此没有max值，里面的三条记录为 77 88 99
-- 查看索引页page1中指向数据页的指针对应的数据，即验证了索引分裂的过程
testdb=# select * from testnull where ctid='(0,1)';
1 | 11jzfbotd3j7asnyysj9ahfyazfqs95kifk2800ay17odhjv....
-- kingbase文档上有 create index idx_t1_c1 on t1(c1 nulls first);语句，下面验证一下kingbase索引树中确实存储null值
insert into testnull values(10,null);
insert into testnull values(11,null);
-- 更新统计信息
testdb=# vacuum analyze testnull;
-- 查看索引树元数据
testdb=# select * from bt_metap('idx_b');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+------+-------+----------+-----------+-------------+-------------------------
340322 | 4 | 3 | 1 | 3 | 1 | 0 | 11
(1 row)
-- 查看root页状态
testdb=# select * from bt_page_stats('idx_b',3);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
3 | r | 2 | 0 | 512 | 8192 | 7084 | 0 | 0 | 1 | 2
(1 row)
-- 查看root页内容
testdb=# select * from bt_page_items('idx_b',3);
itemoffset | ctid | itemlen | nulls | vars |
1 | (1,0) | 8 | f | f |
2 | (2,1) | 1016 | f | t | b0 0f 00 00 37 37 36 76 36 68 6c 6c 66 6c 31 67 71 6e 35 36 66 6b 30 34 7a 36 64 72 73 64 35 78 6d 6c 37 6e 6a 61 72 6b 69 35 6a 61 77 7
-- 查看page2中索引信息
testdb=#
testdb=# select * from bt_page_items('idx_b',2);
1 | (0,7) | 1016 | f | t | b0 0f 00 00 37 37 36 76 36 68 6c 6c 66 6c 31 67 71 6e 35 36 66 6b 30 34 7a 36 64 72 73 64 35 78 6d 6c 37 6e 6a 61 72 6b 69 35 6a 61 77 7
2 | (1,1) | 1016 | f | t | b0 0f 00 00 38 38 35 6a 62 32 71 39 72 7a 70 35 78 75 7a 79 67 6e 73 31 61 31 38 61 7a 71 75 70 37 69 61 37 37 39 39 76 77 30 61 32 30 3
3 | (1,2) | 1016 | f | t | b0 0f 00 00 39 39 66 72 75 6c 6f 37 64 69 6a 6f 72 35 6f 33 67 65 73 39 65 32 6e 68 7a 64 67 31 35 6f 76 33 6f 34 35 79 77 31 65 73 36 6
4 | (0,8) | 16 | t | f |
5 | (0,9) | 16 | t | f |
(5 rows)
-- 以上可知 4 /5 即null值对应的两条记录，nulls=t ,查看数据页对应记录信息
testdb=# select * from testnull where ctid='(0,8)';
a | b
----+---
10 |
(1 row)
testdb=# select * from testnull where ctid='(0,9)';
a | b
----+---
11 |
(1 row)
-- 因数据页较少，直接seq scan 扫描
explain analyze select * from testnull where b is null;
-- 插入大量数据后
insert into testnull select generate_series(10001,20001),'22'||generate_random_string(998);
testdb=# vacuum analyze testnull;
VACUUM
testdb=#
testdb=#
testdb=# explain analyze select * from testnull where b is null;
QUERY PLAN
\--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testnull (cost=4.80..12.60 rows=2 width=1008) (actual time=0.019..0.020 rows=2 loops=1)
Recheck Cond: (b IS NULL)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_b (cost=0.00..4.80 rows=2 width=0) (actual time=0.015..0.015 rows=2 loops=1)
Index Cond: (b IS NULL)
Planning Time: 0.314 ms
Execution Time: 0.053 ms
(7 rows)
以上可知，kingbase索引中存储null值，is null 可以使用索引
二、Kingbase使用索引表连接情况测试验证
创建测试关系表：city-human
drop table city cascade constraints;create table city(id varchar(32) primary key,name varchar(256),description varchar(1000));
create index idx_city_name on city(name);
insert into city select 'city_id_'||generate_series(1,100),'city_name_'||generate_series(1,100),'beautiful city';
drop table human cascade constraints;
create table human(id varchar(32) primary key,name varchar(256),city_id varchar(32));
create index idx_human_city_id on human(city_id);
insert into human select 'human_id_'||generate_series(1,100),'human_name_'||generate_series(1,100),'city_id_1';
insert into human select 'human_id_'||generate_series(101,200),'human_name_'||generate_series(101,200),'city_id_2';
insert into human select 'human_id_'||generate_series(201,220),'human_name_'||generate_series(201,220),'city_id_3';
insert into human select 'human_id_'||generate_series(221,223),'human_name_'||generate_series(221,223),'city_id_4';
insert into human select 'human_id_'||generate_series(224,350),'human_name_'||generate_series(224,350),'city_id_5';
insert into human select 'human_id_'||generate_series(351,1000),'human_name_'||generate_series(351,1000),'city_id_6';
insert into human select 'human_id_'||generate_series(1001,1002),'human_name_'||generate_series(1001,1002),'city_id_7';
explain analyze select a.*,b.name from human a,city b where a.city_id=b.id and a.id='human_id_1000' and b.name='city_id_6';
Nestloop Join方式：
NL连接通常应用被驱动表通过索引返回极少数据场景，举例如下：
testdb=# explain analyze select a.*,b.name from human a,city b where a.city_id=b.id and a.id='human_id_1000' and b.name='city_id_6'; QUERY PLAN
\---------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..10.55 rows=1 width=48) (actual time=0.044..0.044 rows=0 loops=1)
Join Filter: ((a.city_id)::text = (b.id)::text)
-> Index Scan using human_pkey on human a (cost=0.28..8.29 rows=1 width=36) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: ((id)::text = 'human_id_1000'::text)
-> Seq Scan on city b (cost=0.00..2.25 rows=1 width=22) (actual time=0.022..0.022 rows=0 loops=1)
Filter: ((name)::text = 'city_id_6'::text)
Rows Removed by Filter: 100
Planning Time: 0.562 ms
Execution Time: 0.105 ms
不适应场景：
NL连接如果驱动表返回大量数据，内层即使有索引也不适用，参见for { for .....}循环，遇到这种情况建议使用Merge Join或者Hash Join
Merge Join 方式：
Merge Join 连接方式通常用于大量数据相连时，连接列上有索引，通过索引的有序性各自排序后相连，通常结果按照索引列排序场景。同时应用于大量数据非等值相连时无法使用Hash Join的场景。
testdb=# explain analyze select a.*,b.name from human a,city b where a.city_id=b.id order by b.id; QUERY PLAN
\-----------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=5.60..73.49 rows=1002 width=58) (actual time=0.352..0.757 rows=1002 loops=1)
Merge Cond: ((a.city_id)::text = (b.id)::text)
-> Index Scan using idx_human_city_id on human a (cost=0.28..55.30 rows=1002 width=36) (actual time=0.016..0.183 rows=1002 loops=1)
-> Sort (cost=5.32..5.57 rows=100 width=22) (actual time=0.310..0.314 rows=68 loops=1)
Sort Key: b.id
Sort Method: quicksort Memory: 32kB
-> Seq Scan on city b (cost=0.00..2.00 rows=100 width=22) (actual time=0.009..0.020 rows=100 loops=1)
Planning Time: 0.271 ms
Execution Time: 0.834 ms
Hash Join 方式：
Hash Join 连接方式通常用于大量数据的非等值相连
testdb=# explain analyze select a.*,b.name from human a,city b where a.city_id=b.id; QUERY PLAN
\-----------------------------------------------------------------------------------------------------------------
Hash Join (cost=3.25..25.01 rows=1002 width=48) (actual time=0.112..0.503 rows=1002 loops=1)
Hash Cond: ((a.city_id)::text = (b.id)::text)
-> Seq Scan on human a (cost=0.00..19.02 rows=1002 width=36) (actual time=0.010..0.104 rows=1002 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=22) (actual time=0.093..0.093 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Seq Scan on city b (cost=0.00..2.00 rows=100 width=22) (actual time=0.007..0.021 rows=100 loops=1)
Planning Time: 0.219 ms
Execution Time: 0.564 ms
(8 rows)
Hash Join退化
-- 制造数据严重倾斜场景 **insert** **into** human **select** 'human_id_'||**generate_series**(300001,2000000),'human_name_'||**generate_series**(1003,100000),'city_id_8';
testdb=# explain analyze select * from city b,human a where a.city_id=b.id;
QUERY PLAN
\--------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=3.25..42460.75 rows=2000000 width=75) (actual time=0.060..940.590 rows=2000000 loops=1)
Hash Cond: ((a.city_id)::text = (b.id)::text)
-> Seq Scan on human a (cost=0.00..36985.00 rows=2000000 width=38) (actual time=0.012..188.266 rows=2000000 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=37) (actual time=0.036..0.036 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on city b (cost=0.00..2.00 rows=100 width=37) (actual time=0.004..0.013 rows=100 loops=1)
Planning Time: 0.374 ms
Execution Time: 1020.983 ms
(8 rows)
hash桶数:1024
**insert** **into** city **select** 'city_id_'||**generate_series**(101,1000000),'city_name_'||**generate_series**(101,1000000),'beautiful city';
testdb=# explain analyze select * from city b,human a where a.city_id=b.id;
QUERY PLAN
\-------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=40636.00..122911.02 rows=2000000 width=83) (actual time=700.157..2050.172 rows=2000000 loops=1)
Hash Cond: ((a.city_id)::text = (b.id)::text)
-> Seq Scan on human a (cost=0.00..36985.00 rows=2000000 width=38) (actual time=0.024..265.018 rows=2000000 loops=1)
-> Hash (cost=19346.00..19346.00 rows=1000000 width=45) (actual time=699.040..699.041 rows=1000000 loops=1)
Buckets: 65536 Batches: 32 Memory Usage: 2914kB
-> Seq Scan on city b (cost=0.00..19346.00 rows=1000000 width=45) (actual time=0.012..218.145 rows=1000000 loops=1)
Planning Time: 39.460 ms
Execution Time: 2171.528 ms
(8 rows)
hash桶数: 65536 桶的数量明显增加
退化原因：当一个hash键值内数量太多的时候，此时hash join性能就会明显退化，一般转化为Merge Join连接方式解决
解决：kingbase提供了hint功能
-- 开启hint
set enable_hint=enable;
-- 使用mergejoin hint
testdb=# explain analyze select /*+ leading(a) mergejoin(a b) */ * from city b,human a where a.city_id=b.id;
QUERY PLAN
\----------------------------------------------------------------------------------------------------------------------------------------------
\------
Merge Join (cost=0.89..149757.13 rows=2000000 width=83) (actual time=0.023..1527.012 rows=2000000 loops=1)
Merge Cond: ((b.id)::text = (a.city_id)::text)
-> Index Scan using city_pkey on city b (cost=0.42..59937.63 rows=1000000 width=45) (actual time=0.009..234.922 rows=777780 loops=1)
-> Index Scan using idx_human_city_id on human a (cost=0.43..76068.43 rows=2000000 width=38) (actual time=0.007..407.629 rows=2000000 loo
ps=1)
Planning Time: 0.414 ms
Execution Time: 1610.044 ms
(6 rows)
2171.528 ms -->1610.044 ms
由上可知，merge join 比hash join 有了一定的进步，相对较优。
总结：
kingbase针对数据严重倾斜的场景，象oracle一样同样需要人为干预，毕竟只有用户知道自己的数据分布，无可厚非。
三、Kingbase Null值行为验证
drop table test1;drop table test2;
drop table test3;
drop table test4;
create table test1(id int ,name varchar2(32));
create table test2(id int ,name varchar2(32));
create table test3(id int ,name varchar2(32));
create table test4(id int ,name varchar2(32));
insert into test1 values(1,'id1');
insert into test1 values(1, null);
insert into test2 values(null,null);
insert into test3 values(1,'id1');
insert into test3 values(2,'id2');
insert into test3 values(3,'id3');
insert into test4 values(1,'id1');
insert into test4 values(2,'id2');
insert into test4 values(3,null);
commit;
select * from test2 where not exists (select name from test4 where test2.name=test4.name);
select * from test2 where name not in (select name from test4);
select * from test1 where id = all(select id from test4 where name='id5');
select * from test1 where id = any(select id from test4 where name='id5');
select * from test1 where id < (select max(id) from test3 where name='id5');
select * from test1 where id = any (select id from test4 where name='id5');
select * from test1 where id = all (select id from test4 where name='id5');
select * from test2 where id = any (select id from test4 where name='id5');
select * from test2 where id = all (select id from test4 where name='id5');
select max(name) from test1 where name='id3';
经过验证，kingbase测试结果如下，与oracle行为一致
where优先集：
and: false > unknown > true
or : true > unknown > false
举例：
NULL in (a,b,c) = (NULL=a or NULL=b or NULL=c) = (unknown or unknown or unknown)
= unknown ------->unkown
NULL in (a,b,c) = unkown
NULL in (a,b,NULL) = unknown
NULL not in (a,b,c) = unknown
NULL not in (a,b,NULL) = unkown
NULL exists (a,b,c) = false
NULL exists (a,b,NULL) = false
NULL not exists(a,b,c) = true
NULL not exists(a,b,NULL) = true
NULL & any (a,b,c) = unknown
NULL & any (a,b,NULL) = unknown
NULL & all (a,b,c) = unknown
NULL & all (a,b,NULL) = unknown
NUll & any ({}) = any ( NULL & {}) = unknown ------> unknown
NULL & all ({}) = all( NULL & {}) = true ------->true 返回所有行
举例：
x in (a,b,NULL) = (x=a or x=b or x=NULL)
= (true/false or true/false or unknown)
= true/unknown ------------->true/unknown
\#集合内的NULL不影响存在性判断,x非NULL
x in (a,b,NULL) = true/unknown
\#集合内的NULL影响存在性判断,只会返回false
x not in (a,b,NULL) = false/unknown
\#集合内的NULL不影响存在性判断
x exists (a,b,NULL) = true/false
\#集合内的NULL不影响存在性判断
x not exists(a,b,NULL) = true/false
\#集合内的NULL不影响存在性判断
x & any (a,b,NULL) = true/unknown
\#集合内的NULL影响存在性判断
x & all (a,b,NULL) = false/unkown
x & any ({}) = any ( x & {}) = unknown ------> unknown
x & all ({}) = all( x & {}) = true ------->true 返回所有行
count() 当输入是空集时返回0，max() min() avg() 等极值函数返回 NULL
all 当输入是空集的时候，返回true ,行全部返回，如果不想返回，则使用极值函数返回NULL,不返回行
any 当输入是空集的时候，返回false
经过简单试用，kingbase数据库中规中矩，相关测试性能良好，对我司产品适配良好，是我司国产化信创项目有力的候选数据库。
此外，如果您对kingbase数据库感兴趣，请继续关注我们的专栏。
往期文章回顾
MOP社区新闻
金仓专栏
告别繁琐！KingbaseES v9数据库一键安装-青学会&金仓专栏（1）
KingbaseES v9数据库Docker安装-青学会&金仓专栏（2）
DBA实战小技巧
实战：记一次RAC故障排查
DBA实战运维小技巧安装篇（一）Oracle 主流版本不同架构下的静默安装指南
DBA实战运维小技巧存储篇（一）根目录满了如何处理
DBA实战运维小技巧存储篇（二）打包迁移单机数据库至新存储
MOP社区投稿-内核开发
简单解析 IvorySQL 增强 Oracle xml 兼容能力的原理
简单讨论 PostgreSQL C语言拓展函数返回数据表的方式
简单分析 pg_config 程序的作用与原理
Redis 日志机制简介（一）：SlowLog
Redis 日志机制简介（二）：AOF 日志
Redis 日志机制简介（三）：RDB 日志
pg_cron插件使用介绍
Redis 的指令表实现机制简介
pg几款源码工具介绍
Redis 事务功能简介