SPL 实践:高并发帐户查询

问题描述

数据结构与规模

帐户明细表 T 数据结构:

字段名称

字段类型

字段注释

示例数据

Id

String

帐号

28 位数字

Tdate

Date

日期

2023-10-22

Tamt

Number

金额

23345.70

Ttype

Number

分类

1、0

Tcorp

String

网点

A210172




网点表 C 数据结构:

字段名称

字段类型

字段注释

示例数据

cid

String

网点编号

A210172

cname

String

网点名称

X Bank New York Branch

caddress

String

地址

226 East 56th Street, New York, NY 10022, U.S.A.




T 表有 3 亿条记录,用户帐号 id 由 28 位数字构成,去重后总数有 1 千万。

C 表有 3000 条记录,通过 cid 和 T 表的 tcorp 字段关联。

要求:以指定 id 和日期区间为条件查找明细数据,结果要包括 id、tdate、tamt、cname、caddress。

环境和期望

在 6 台 40C256G 服务器集群下,期望 60 并发查找响应时间不超过 1 秒。

使用某知名分布式数据仓库无法达到预期。

同样的硬件环境下,使用 ES 实现查找需求,可以达到性能要求。但 ES 不支持多表关联,必须将 C 表数据冗余到 T 表形成宽表。这样做,当网点数据变动时,必须重新生成宽表的 3 亿条数据,耗时太长。

问题分析

关系数据库实现这个查询的 SQL 语句很简单:

select id,tamt,tdate,cname,caddress 
from T left join C on C.cid=T.tcorp
where id='1110101014992000000000000219'
    and tdate>= to_date('2023-01-10', 'yyyy-MM-dd')
    and tdate<to_date('2023-10-25', 'yyyy-MM-dd')
    and …

为了提高查询响应速度,一般都会对 T 表的 id 字段建索引:

create index index_T_1 on T(id)

数据库先在索引中找到帐号 id 对应的原表位置信息,再从原表中取得数据。前者一般都很快,后者的速度却经常不理想。这是因为,数据库并不保证同一个帐号 id 对应的数据在物理上会连续存放,很可能是乱序的。而硬盘有最小读取单位,通常远大于一条记录占用的空间。所以,在读取不连续数据时,会读出很多无关内容,查询就会变慢。虽然每个帐户 id 只有几条到几千条数据,但是在高并发访问的时候,每个查询都慢一点,总体性能就会很差了。

因此,对于高并发帐户查询这种追求极致性能的场景,还需要有更高速的解决方案。

物理有序存储

提升高并发账户查询最重要的手段是物理有序存储。我们要将明细数据按照帐号 id 排序,以行式存储的方式转存在硬盘上。这样做,同一个帐户 id 的数据会连续存储,从硬盘上读取出的数据块几乎全部都是需要的目标值,性能提升会相当明显。

之所以不采用列式存储,是因为列存是将每列数据存储在一起,一个帐号 id 的字段分散在不同的列中,还是会造成硬盘读取不连续数据的情况。

使用索引

帐号明细表按照 id 物理有序存储后,如果直接用二分法查找,就需要多次读取数据文件才能定位到目标值,而且过程中还有不少读取是多余的。

为了达到极致性能,我们还要为 id 字段建立索引,用目标值在索引中迅速获得原表物理位置,然后再读取目标值。

索引中只有 id 和对应的物理位置,占用的存储空间比原表小很多。但数据量很大时索引仍然会比较大,SPL 会自动建立多级索引,即给索引再建索引,以提升索引的查找速度。

大量并发查找时,如果每次都从硬盘上加载索引,这个过程会被重复很多次。SPL 提供索引预加载机制,在系统初始化时将 2 级或 3 级索引主动预先加载到内存中,可以避免重复加载浪费时间。

行式存储建立索引,相比列存更有优势。为行存数据建立索引时,整条记录的位置可以用一个数值表示,记到索引中就可以了;而列存的一条记录中,每个列都有各自的位置,不可能都记录下来,只能记录序号,查找时就会多一步动作,性能会下降。

高并发帐户查询属于查找计算,如果帐号名细表还要用于遍历计算,可采用带值索引将查找结果需要的字段复制到索引中,查找时不需再读取原表。这样,原表就可以列存,适合遍历计算。

不过,带值索引会比普通索引占用更大的存储空间,不太适合查找时涉及字段非常多的情况,且重建索引也很麻烦,需要根据项目的实际情况权衡选择。

维表序号化

这个任务中还有个关联运算,是个典型的小维表外键连接。SQL 数据库一般用哈希连接的方式来计算。

SPL 可以将维表全内存,并采用外键序号化实现这个关联计算。做法是:预先将 T 表的 tcorp 字段,转换为 C 表记录的位置序号。在关联时,就可以直接用 T 表中的序号到内存 C 表的对应位置上取得记录,而不再需要计算、比对哈希值了。

外键序号化将 T 表中的 tcorp 从字符串转换整数存储。整数存储效率、计算速度都好于字符串和日期型,非常有利于提高性能。

除了 tcorp 字段外,我们还可以用 days@o 函数将 tdate 转换为整数,实现整数化

id 字段是 28 位纯数字,超过了 long(19 位)取值范围不能转为整数,仍用字符串存储。

实践过程

准备数据


A

B

1

=to(3000).new("A"/(210000+~):cid,"X bank Branch"/~:cname,~/"East 56th Street, New York, NY 10022, U.S.A.":caddress)

2

=file("C.txt").export@t(A1)

3

2023-01-01

2023-10-27

4

=periods(A3,B3)

5

=file("T.txt")

=movefile(A5)

6

for A4

=to(1000000)

7

=B6.new("1110101014992000000"/pad(string(rand(10000000)+1),"0",9):id,A6:tdate,rand(2):ttype,"A"/(210000+rand(3000)+1):tcorp,rand()*10000:tamt)

8

=A5.export@at(B7)

这段代码生成文本文件 T.txt 包含 3 亿条明细记录,C.txt 包含 3000 条网点记录,用来模拟从数据库或其他数据源导出的原始数据。

实际应用中,帐号明细一般是按照数据产生的顺序存储的,所以 T.txt 按照日期有序。

历史数据查找

一、数据预处理


A

B

1

=T("C.txt").sort(cid)

=file("C.btx").export@b(A1)

2

=A1.derive@o().keys@i(cid)

3

=file("T_r.ctx").create@pyr(#id,tdate,ttype,tcorp,tamt)

4

=file("T.txt").cursor@t(id:string,tdate,ttype,tcorp,tamt)

5

=A4.sortx(id)


6

=A5.run(tcorp=A2.pfind(tcorp),tdate=days@o(tdate))

7

=A3.append(A6)


8

=A3.index(file("T_r.idx");id)


A1、B1 将维表 C.txt 按照 cid 排序,存入集文件 C.btx 用于后续计算。

A2:内存中的维表建立带索引的主键 cid。

A3 中 create 函数带 @r 选项,建立的是行存组表。

A4:建立 T.txt 的游标,id 指定是字符串类型。

A5:T.txt 数据对日期有序,这里要对 id 做外存大排序。

A6 将 tcorp 转换为维表记录的序号,完成维表序号化。用 days@o 函数将 tdate 转换为整数,完成整数化

A7 存入组表的是对 id 有序的明细数据,实现物理有序存储

A8 建立行存组表的索引。

二、预加载索引和维表


A

B

1

if !ifv(T_r)

=file("T_r.ctx").open().index@3(file("T_r.idx"))

2


=env(T_r,B1)

3

if !ifv(corp)

=T("C.btx")

4


=env(corp,B3)

在系统初始化时完成索引预加载。这里网点维表也实现了预先加载。

由于事实表中的 tcorp 已经预先完成序号化,两表关联时直接用事实表中的位置去维表中取记录,用不到维表的主键和索引,所以这里不需要建立维表主键、索引。

三、查找 id


A

1

=T_r.icursor(id,tdate,tcorp,tamt;id=="1110101014992000000000000219" && tdate>=days@o(date("2023-01-10")) && tdate<days@o(date("2023-10-25"));file("T_r.idx")).fetch()

2

=A1.new(id,date@o(tdate):tdate,tamt,corp(tcorp).cname,corp(tcorp).caddress)

A1 中用全局变量 T_r 完成索引查找,索引文件需要指定。

这里的过滤条件中 id 和日期是常数,实际应用中一般是作为网格参数传入。

比如有三个传入参数:arg_id,arg_startdate,arg_enddate,这时 A1 要改成:

=T_r.icursor(id,tdate,tamt;id==arg_id && tdate>=days@o(arg_startdate) && tdate<days@o(arg_enddate);file("T_r.idx")).fetch()

A2:查找结果用 date@o 将整数转换为日期,再利用维表序号取得网点名称和地址,实现连接计算。

如果要使用列存组表加带值索引方案,那么数据预处理代码需要修改成这样:


A

B

1

=T("C.txt").sort(cid)

=file("C.btx").export@b(A1)

2

=A1.derive@o().keys@i(cid)

3

=file("T.ctx").create@py(#id,tdate,ttype,tcorp,tamt)

4

=file("T.txt").cursor@t(id:string,tdate,ttype,tcorp,tamt)

5

=A4.sortx(id)


6

=A5.run(tcorp=A2.pfind(tcorp),tdate=days@o(tdate))

7

=A3.append(A6)


8

=A3.index(file("T.idx");id;tdate,tamt,tcorp)

A3 中 create 函数没有 @r 选项,生成的是列存组表。

A8 中 index 函数的最后一组参数 tdate,tamt,tcorp 是索引要带的字段。

id 查找代码也要有所调整:


A

1

=T.icursor(id,tdate,tcorp,tamt;id=="1110101014992000000000002427";file("T.idx")).select(tdate>=days@o(date("2023-01-10")) && tdate<days@o(date("2023-10-31"))).fetch()

2

=A1.new(id,date@o(tdate):tdate,tamt,corp(tcorp).cname,corp(tcorp).caddress)

A1:带值索引的查找条件只能是索引字段,所以日期条件要另外写 select 函数。每个 id 数据量都不大,单独写 select 函数对性能影响很小。

新增数据处理及查找

实际业务中,每天都会产生新的明细数据。假设每天产生的新数据保存在 newdata.txt 文件中。

组表 T 或者 T_r 按照 id 有序存储,而新增明细数据的日期比组表中已有的数据晚,但 id 还是同样的一批值。直接在组表的最后追加新增数据,会破坏 id 的顺序,不可行。

如果用新旧数据一起对 id 做大排序,再重新生成组表,则耗时会非常长。

我们可以将组表文件分为两部分,历史数据组表 T.ctx 和增量数据组表 T_new.ctx,每次追加只对增量数据组表重新排序,一段时间后(比如一个月),再将增量数据与历史数据归并。


A

B

C

1

=file("T_new.ctx")

=file("T_new.idx")

=file("newdata.csv")

2

=C1.cursor@ct(id:string,tdate,ttype,tcorp,tamt).sortx(id)

3

if day(now())==1

=file("T.ctx")

=file("T.idx")

4


=A1.open()

=B4.cursor()

5


=B3.reset(;C4)

>B4.close()

6


>movefile(A1),movefile(B1),movefile(C3)

7


=B3.open()

=B7.index(C3;id;tdate,tamt,tcorp)

8


=B7.index@3(C3)

=env(T,B8)

9


>B7.close()


10

if !A1.exists()

=A1.create@py(#id,tdate,ttype,tcorp,tamt)

11


=B10.append@i(A2)

=B10.close()

12

else

=A1.reset(;A2)


13

=A1.open()

=movefile(B1)


14

=A13.index(B1;id;tdate,tamt,tcorp)

15

=A13.index@3(B1)

=env(T_new,A15)

>A13.close()

如果不是当月 1 日,那么执行 A10-B12,将 newdata.txt 和并到 T_new.ctx 中。再执行 A13-C15,重建 T_new.ctx 索引并重新预加载。

每月 1 日,还要执行 B5 将 T_new.ctx 的数据合并到 T.ctx 中。B6 删除 T_new.ctx、索引以及 T.ctx 的索引文件。B7-B9 重新生成 T.ctx 的索引,重新预加载。


这时候,查找要基于两个组表:


A

1

=T.icursor(id,tdate,tamt; id,tdate,tcorp,tamt;id=="1110101014992000000000000219";file("T.idx")).select(tdate>=days@o(date("2023-01-10")) && tdate<days@o(date("2023-10-25"))).fetch()

2

=T_new.icursor(id,tdate,tamt; id,tdate,tcorp,tamt;id=="1110101014992000000000000219";file("T_new.idx")).select(tdate>=days@o(date("2023-01-10")) && tdate<days@o(date("2023-10-25"))).fetch()

3

=[A1,A2].merge(id)

实践效果

单台 40C256G 服务器,3 亿条明细数据,60 并发查询的情况下,SPL 平均响应时间是 0.5 秒。

后记

高并发帐户查询场景的共同特点是:

1、 涉及众多帐户的历史数据,总数据量巨大(几千万甚至上亿),需要外存;

2、 每个帐户的数据量不大(几条到几千条),而且就是简单查询,几乎没有什么运算;

3、 查询用户多、频率高,并发访问量很大;要求极致性能,响应速度要达到秒级甚至更快。

4、 还可能有多个维表需要关联。

对这种场景,SPL 获得极致性能的主要手段,是将数据按照帐号有序存储且加索引。

如果应用中仅存在查找计算,那么采用有序行存加普通索引方案即可。如果应用不仅有查找需求,还有遍历计算,那么可以考虑采用列存加上带值索引的方案。

关联的维表一般比较小,可以全部装入内存,且对事实表关联字段预先序号化。连接时直接用序号取得维表记录,性能要比数据库的哈希连接好很多。