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()) |
英文版