"问题描述灵活数据结构的意思是指数据表的每条记录的数据结构不相同。所有字段通常能分成为两部分，一部分是所有记录的共同字段，另一部分是各自不同的字段，其总数量可能多达数百种，但每条记录只占少数几 .."

Charname
乾学院 1750 号会员
2 回帖 • 980 浏览 • 1 年前

SPL 实践：海量灵活结构数据查询

计算＆AI

问题描述

灵活数据结构的意思是指数据表的每条记录的数据结构不相同。所有字段通常能分成为两部分，一部分是所有记录的共同字段，另一部分是各自不同的字段，其总数量可能多达数百种，但每条记录只占少数几种。更复杂的情况，这些灵活字段还会再分类。

没分类的简单场景举例，学生成绩单：

共同字段				灵活字段
学号	姓名	性别	年龄	语文	英语	数学	物理	化学	生物
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&lt;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,nA4).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())