SPL 实践:海量灵活结构数据查询
问题描述
灵活数据结构的意思是指数据表的每条记录的数据结构不相同。所有字段通常能分成为两部分,一部分是所有记录的共同字段,另一部分是各自不同的字段,其总数量可能多达数百种,但每条记录只占少数几种。更复杂的情况,这些灵活字段还会再分类。
没分类的简单场景举例,学生成绩单:
共同字段 | 灵活字段 | ||||||||
学号 | 姓名 | 性别 | 年龄 | 语文 | 英语 | 数学 | 物理 | 化学 | 生物 |
1 | 张三 | 男 | 16 | 67 | 50 | 90 | 100 | 80 | 80 |
2 | 李四 | 女 | 18 | / | / | / | 99 | / | 70 |
3 | 王五 | 男 | 17 | 98 | 88 | 78 | / | / | / |
… | … | … | … | … | … | … | … | … | … |
有的同学没有相应的课程,也就没有该课程的成绩了,如表格中各斜线框。
有分类的复杂场景举例,体育生成绩表:
共同字段 | 灵活字段 | ||||||||
学号 | 姓名 | 性别 | 年龄 | 篮球 | 足球 | ||||
评语 | 是否国家队 | 评分 | 评语 | 是否国家队 | 评分 | ||||
1 | 张三 | 男 | 16 | 经常… | 是 | 100 | / | / | / |
2 | 李四 | 女 | 18 | / | / | / | 偶尔… | / | 95 |
3 | 王五 | 男 | 17 | 善于… | 是 | 20 | 擅长… | 是 | 10 |
… | … | … | … | … | … | … | … | … | … |
共同字段是每个人的基本属性。灵活字段,分为不同属性(这里是篮球、足球);不同的体育运动下,又有相同分类(这里是评语[字符串]、是否国家队[布尔]、评分[数值])
下面的讨论将主要针对有分类的复杂情况,无分类情况是有分类情况的特例。
查询条件举例:
共同字段(主表)中满足,性别为“男”
且
灵活字段(子表)中满足,属性为“篮球”的 “是否国家队” 分类值为“是” 且属性为“足球”的 “评分” 分类值小于60的那些记录。
本质上,这两个例子描述的数据结构,在逻辑上可以看作是一张大宽表,但是当属性和分类过多时,这张表的字段就会有几千列(共同字段 + 所有可能的属性 * 分类)。如果在物理上也处理成大宽表,会造成字段过多而且大多数都是空白。
SQL中一般会使用两个数据表来描述这样的数据结构:
主表main(共同字段)
字段名 | 类型 | 含义 |
id | 数值 | id号(自增) |
name | 字符串 | 姓名 |
gender | 布尔 | 性别 |
age | 数值 | 年龄 |
子表sub(各自不同的字段)
字段名 | 类型 | 含义 |
id | 数值 | id号同主表 |
atr | 数值 | 属性(可枚举,数字化存储) |
comment | 字符串 | 评语(人工填写) |
nt | 布尔 | 是否国家队 |
rate | 数值 | 评分 |
结构示例中,分类就是comment、nt、rate这些字段。如果要查性别为男,属性为篮球的是否国家队分类为是,属性为足球的评分分类小于60的记录,SQL将会这样写:
SELECT *
FROM main m, subs
WHERE m.id= s.id AND
(SELECT count(*)
FROM sub ss
WHERE m.id= ss.id AND (ss.atr= 1 AND ss.nt= 1) or (ss.atr=2 AND ss.rate<60) ) = 2
SPL 中,通常有三种办法来处理这种情况:
一、 使用json式字段
这种灵活数据结构通常会使用json式字段。
无分类时,json串是一条记录。例如,学生成绩单中,李四的成绩:
{
"物理": 99,
"生物": 70
}
有分类时会是个序表。例如,体育生成绩中,王五的成绩:
[
{
"属性":"篮球",
"评语":"善于…",
"是否国家队":"是",
"评分":"20"
},
{
"属性":"足球",
"评语":"擅长…",
"是否国家队":"是",
"评分":"10"
}
]
json字段存储量会较大(每个记录或序表都要存储一遍字段名),而且总要解析json为记录或序表后才能参与运算,性能不会好。SPL处理json式字段很方便,相应的代码比较常规,这里就不详细说了。
下面介绍另外两种手段。
二、 使用序列字段
以前述的“体育生成绩表”为例,使用序列字段是将子表的所有属性当成一个序列字段,分类也随之转为一一对应的序列,如下:
共同字段 | 各自不同的字段 | ||||||
学号 | 姓名 | 性别 | 年龄 | 属性 | 评语 | 是否国家队 | 评分 |
1 | 张三 | 男 | 16 | [篮球] | [经常…] | [是] | [100] |
2 | 李四 | 女 | 18 | [足球] | [偶尔…] | [null] | [95] |
3 | 王五 | 男 | 17 | [篮球,足球] | [善于…, 擅长…] |
[是,是] | [20,10] |
… | … | … | … | … | … | … | … |
2.1数据结构
定义测试数据结构如下:
主表main(共同字段)
字段名 | 类型 | 含义 |
id | 数值 | 学号(自增) |
name | 字符串 | 姓名 |
gender | 布尔 | 性别 |
age | 数值 | 年龄 |
class | 数值 | 等级(可枚举,数字化存储) |
子表sub(各自不同的字段)
字段名 | 类型 | 含义 |
id | 数值 | 学号,同主表 |
atr | 数值 | 属性(可枚举,数字化存储) |
comment | 字符串 | 评语(人工填写) |
nt | 布尔 | 是否国家队 |
rate | 数值 | 评分 |
2.2从数据库转储数据
从数据库中取数的代码如下:
A | |
1 | =connect@l("oracle12c") |
2 | =A1.cursor("SELECT * FROM MAIN ORDER BY ID") |
3 | =A1.cursor@x("SELECT * FROM SUB ORDER BY ID") |
4 | =A3.group(id;~.(atr):atr,~.(comment): comment,~.(nt): nt,~.(rate): rate) |
5 | =joinx(A2:main,id;A4:sub,id) |
6 | =A5.new(main.id,main.name,main.gender,main.age,main.class,sub.atr,sub.comment,sub.nt,sub.rate) |
7 | =file("seq_all.ctx").create(#id,name,gender,age,class,atr,comment,nt,rate) |
8 | >A7.append@i(A6) |
2.3 生成测试数据
为实验方便,我们写段代码直接生成seq_all.ctx组表文件,脚本如下:
A | B | |
1 | >rand@s(1) | |
2 | >n=100000 | |
3 | =file("seq_all.ctx").create(#id,name,gender,age,class,atr,comment,nt,rate) | |
4 | for 30 | =to(n*(A4-1)+1,n*A4).new(~:id,"name"/~:name,rand(2)+1:gender,rand(17)+18:age,rand(10)+1:class,20.sort(rand()).to(rand(5)+5):atr,atr.len().(rands("qwertyuiopasdfghjklzxcvbnm",rand(200)+2)):comment,atr.len().([true,false,null](rand(3)+1)):nt,atr.len().(rand(100)+1):rate) |
5 | >A3.append@i(B4.cursor()) |
2.4查询
场景1: 年龄大于等于20,小于等于25,且等级包含(等级1,等级3,等级6)
场景2: 属性值为"篮球"的是否国家队分类为"是",属性值为"足球"的评分分类值小于60
A | |
1 | /case1:年龄大于等于20,小于等于25,且等级包含(等级1,等级3,等级6) |
2 | [1,3,6] |
3 | age>=20 && age<=25 && A2.pos(class) |
4 | /case2:属性值为"篮球"的是否国家队分类为"是",属性值为"足球"的评分分类值小于60 |
5 | j(atr,nt,rate).count((#1==1 && #2==true) || (#1==2 && #3<60))==2 |
6 | /主表字段 |
7 | id,name,gender,age,class |
8 | /子表字段 |
9 | atr,comment,nt,rate |
10 | /case1_query |
11 | =now() |
12 | =file("seq_all.ctx").open() |
13 | =A12.cursor@v(${A7},${A9};${A3}) |
14 | =A13.fetch(5000) |
15 | =interval@ms(A11,now()) |
16 | /case2_query |
17 | =now() |
18 | =file("seq_all.ctx").open() |
19 | =A18.cursor@v(${A7},${A9};${A5}) |
20 | =A19.fetch(5000) |
21 | =interval@ms(A17,now()) |
A5是个条件表达式,j将多序列字段合成一个序表,然后count出每条记录满足条件的个数,当数量与提的条件相等,说明这条记录需要被取出
三、 使用附表
2.1的数据结构,可以视为主子表。主子表关系时,以共同字段部分为基表,灵活字段部分作为附表。附表的使用可以参考:《SPL附表》
3.1 从数据库转储数据
A | |
1 | =connect@l("oracle12c") |
2 | =A1.cursor("SELECT * FROM MAIN ORDER BY ID") |
3 | =A1.cursor@x("SELECT * FROM SUB ORDER BY ID") |
4 | =file("attach_all.ctx").create(#id,name,gender,age,class) |
5 | =A4.attach(sub,atr,comment,nt,rate) |
6 | =A4.append@i(A2) |
7 | =A5.append@i(A3) |
3.2 生成测试数据
基于2.3生成的文件,再生成附表数据的脚本如下:
A | |
1 | =file("seq_all.ctx").open().cursor(id,name,gender,age,class) |
2 | =file("seq_all.ctx").open().cursor(id,atr,comment,nt,rate).news(#2;id,~:atr,comment(#):comment,nt(#):nt,rate(#):rate) |
3 | =file("attach_all.ctx").create(#id,name,gender,age,class) |
4 | =A3.attach(sub,atr,comment,nt,rate) |
5 | =A3.append@i(A1) |
6 | =A4.append@i(A2) |
3.3 查询
查询条件同2.4
A | |
1 | /case1:年龄大于等于20,小于等于25,且等级包含(等级1,等级3,等级6) |
2 | [1,3,6] |
3 | age>=20 && age<=25 && A2.pos(class) |
4 | /case2:属性值为"篮球"的是否国家队分类为"是",属性值为"足球"的评分分类值小于60 |
5 | sub.count((atr==1 && nt==true) || (atr==2 && rate<60))==2 |
6 | /主表字段 |
7 | id,name,gender,age,class |
8 | /子表字段 |
9 | atr,comment,nt,rate |
10 | /case1_query |
11 | =now() |
12 | =file("attach_all.ctx").open() |
13 | =A12.cursor@v(${A7},sub{${A9}}:sub;${A3}) |
14 | =A13.fetch(5000) |
15 | =interval@ms(A11,now()) |
16 | /case2_query |
17 | =now() |
18 | =file("attach_all.ctx").open() |
19 | =A18.cursor@v(${A7},sub{${A9}}:sub) |
20 | =A19.select@v(${A5}) |
21 | =A20.fetch(5000) |
22 | =interval@ms(A17,now()) |
3.4 两遍查找
使用序列字段时,可以使用游标前过滤,提升性能。但是附表中的子表上的条件需要先把所有列都读出来,即使用不上的字段。如果条件过滤出的结果很少,就会多读很多没用的字段。
所以,在用附表时,先只用条件相关的字段取出主键再找,很可能是更快的。而序列字段则不需要这样处理。
先根据条件,找出满足条件记录的主键序列,再用主键序列找结果,脚本如下:
A | |
1 | /case1:年龄大于等于20,小于等于25,且等级包含(等级1,等级3,等级6) |
2 | [1,3,6] |
3 | age>=20 && age<=25 && A2.pos(class) |
4 | /case2:属性值为"篮球"的是否国家队分类为"是",属性值为"足球"的评分分类值小于60 |
5 | sub.count((atr==1 && nt==true) || (atr==2 && rate<60))==2 |
6 | /主表字段 |
7 | id,name,gender,age,class |
8 | /子表字段 |
9 | atr,comment,nt,rate |
10 | /case1_query |
11 | id |
12 | atr,nt,rate |
13 | /keys |
14 | =now() |
15 | =file("attach_all.ctx").open() |
16 | =A15.cursor@v(${A11},sub{${A12}}:sub;${A3}) |
17 | =A16.fetch(5000) |
18 | =interval@ms(A14,now()) |
19 | /find by keys |
20 | =now() |
21 | =file("attach_all.ctx").open() |
22 | =A21.cursor@v(${A7},sub{${A9}}:sub;A17.find(id)) |
23 | =A22.fetch(5000) |
24 | =interval@ms(A20,now()) |
25 | /case2_query |
26 | =now() |
27 | =file("attach_all.ctx").open() |
28 | =A27.cursor@v(${A11},sub{${A12}}:sub) |
29 | =A28.select@v(${A5}) |
30 | =A29.fetch(5000) |
31 | =interval@ms(A26,now()) |
32 | /find by keys |
33 | =now() |
34 | =file("attach_all.ctx").open() |
35 | =A34.cursor@v(${A7},sub{${A9}}:sub;A30.find(id)) |
36 | =A35.fetch(5000) |
37 | =interval@ms(A33,now()) |
四、 使用行存
SPL列存采用了数据分块并压缩的算法,这样对于遍历运算来讲,访问数据量会变小,也就会具有更好的性能。但对于基于索引随机取数的场景,由于要有额外的解压过程,而且每次取数都会针对整个分块,运算复杂度会高很多。因此,从原理上分析,这时候的性能应当会比行存要差。
同一份数据不能在遍历运算和随机取数这两方面都达到最优性能,要追求极致性能,可以再冗余一份行存组表文件,并对该组表的主键建立索引。
转为行存并建立索引,脚本如下:
A | |
1 | /col2row |
2 | =file("seq_all.ctx") |
3 | =file("seq_all_row.ctx") |
4 | =A2.reset@r(A3) |
5 | /create_index |
6 | =A3.open().index(idx;id) |
先从附表找出主键,再用主键在行存索引中找结果,脚本如下:
A | |
1 | /case1:年龄大于等于20,小于等于25,且等级包含(等级1,等级3,等级6) |
2 | [1,3,6] |
3 | age>=20 && age<=25 && A2.pos(class) |
4 | /case2:属性值为"篮球"的是否国家队分类为"是",属性值为"足球"的评分分类值小于60 |
5 | sub.count((atr==1 && nt==true) || (atr==2 && rate<60))==2 |
6 | /主表字段 |
7 | id,name,gender,age,class |
8 | /子表字段 |
9 | atr,comment,nt,rate |
10 | /case1_query |
11 | id |
12 | atr,nt,rate |
13 | /keys |
14 | =now() |
15 | =file("attach_all.ctx").open() |
16 | =A15.cursor@v(${A11},sub{${A12}}:sub;${A3}) |
17 | =A16.fetch(5000).(#1) |
18 | =interval@ms(A14,now()) |
19 | /find by keys |
20 | =now() |
21 | =file("seq_all_row.ctx").open() |
22 | =A21.icursor(${A7},${A9};A17.contain(id)) |
23 | =A22.fetch(5000) |
24 | =interval@ms(A20,now()) |
25 | /case2_query |
26 | =now() |
27 | =file("attach_all.ctx").open() |
28 | =A27.cursor@v(${A11},sub{${A12}}:sub) |
29 | =A28.select@v(${A5}) |
30 | =A29.fetch(5000).(#1) |
31 | =interval@ms(A26,now()) |
32 | /find by keys |
33 | =now() |
34 | =file("seq_all_row.ctx").open() |
35 | =A34.icursor(${A7},${A9};A30.contain(id)) |
36 | =A35.fetch(5000) |
37 | =interval@ms(A33,now()) |
英文版