问题描述

数据结构与规模

帐户明细表 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 获得极致性能的主要手段，是将数据按照帐号有序存储且加索引。

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

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