SQL 提速:高并发帐户查询

【摘要】
从原理上分析 SQL 语句慢的原因,用代码示例给出提速办法。点击了解SQL 提速:高并发帐户查询

问题描述

高并发帐户查询的应用场景有很多,例如:手机银行查流水、网上营业厅查明细、手游帐户查充值记录等等。这些场景共同的特点是:涉及众多帐户的历史数据,总数据量巨大(几千万甚至上亿),需要外存;每个帐户的数据量不大(几条到几千条),而且就是简单查询,几乎没有什么运算;查询用户多、频率高,并发访问量很大;要求极致性能,响应速度要达到秒级甚至更快。

设帐户明细表 T 包含的字段为:帐号 id、日期 tdate、金额 amt 等。则帐户查询的 SQL 一般是下面的形式:

select id,amt,tdate,… from T

where id='10100'

and tdate>= to_date('2021-01-10', 'yyyy-MM-dd')

and tdate<to_date('2021-01-25', 'yyyy-MM-dd')

and …

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

create index index_T_1 on T(id)

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

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

提速方案

一、数据预处理,有序行存

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

之所以不采用列式存储,是因为列存时各列连续存储,一个帐号 id 的字段分散在不同的列中,还是会造成硬盘读取不连续数据的情况。特别是有并发任务时,列存造成的硬盘不连续访问程度要比行存严重的多。列存时每多一个并发计算任务会多出几个(涉及列数)对磁盘的并发访问请求,行存则只会多一个磁盘并发请求。

而且,为行存数据建立索引时,整条记录的位置可以用一个数值表示,记到索引中就可以了;而列存的一条记录中,每个列都有各自的位置,如果都记下来,那么索引就几乎和原表一样大了,访问成本变高很多,空间占用也太大。

二、新增数据

预处理之后的数据是按照帐号 id 排序的,新增数据中则可能包含任意一个帐号 id,如果直接追加到有序数据的最后,就不能保持整体上对帐号有序了。而直接将有序数据和新增数据一起重新做常规大排序,会非常耗时。

可以将新增数据排序后,和原有序数据一起,用低成本的有序归并算法生成新的有序数据,这样整体复杂度相当于把所有数据读写一遍,可以避免常规大排序中产生很多临时外存缓存的现象,从而获得更好的性能。新的有序数据生成之后,也要建立对应的索引。

更进一步,可以另外保持一个小规模的有序数据(以下称为补数据)。新增数据排序后和补数据归并,原有序数据不变。经过适当的时间后,补数据积累到合适大小时,再和原有序数据归并。有序数据和补数据发生这些变动后,都要同步更新索引。利用索引查找时,需要从有序数据和补数据中分别读数,会比只有一份有序数据时性能下降一些,但还是要比不连续(甚至乱序)的数据快很多。

上面说的这个适当时间的确定,与新增数据的周期有关。比如每天都有新增数据,则每个月做一次原有序数据和补数据的归并。补数据不会超过一个月的数据量,原有序数据存储一个月之前的所有数据。也就是说补数据可能会比原有序数据小很多,所以每天归并的数据量相对较小,很快就能完成数据追加。每个月才需要完成一次全量有序归并,耗时长一些也可以接受了。

三、带值索引

高并发帐户查询属于查找计算,有时候系统也需要兼顾遍历计算的性能。对于遍历来说,列存的性能要好于行存。这种情况下,可以将帐户明细数据采用列存和行存各存一份,应对不同的计算需求。但是,这样做占用的硬盘空间会增加很多。如果高并发查询读取的字段不太多,我们可以采用带值索引加列存的方案,同时实现高性能遍历和查找。

带值索引是指在给有序数据建立索引时,把需要的字段值一起复制过来,索引本身保存字段值并使用行存存储。只要涉及字段在索引中,在查找时就可以不再读取有序数据。这样,有序数据就可以采用列存存储,用以提高遍历计算的性能。带值索引能获得更好的性能,但也会比普通索引占用更大的存储空间,不太适合查找时涉及字段非常多的情况。

代码示例

一、数据预处理,有序行存、普通索引


A

B

1

=file("T-original.ctx").open().cursor(id,tdate,amt,…)

2

=A1.sortx(id)

=file("T.ctx")

3

=B2.create@r(#id,tdate,amt,…).append@i(A2)

4

=B2.open().index( file("T.idx");id)

A1:打开原始组表 T-original.ctx,建立游标。

A2:对 A1 游标按照 id 做外存排序。

B2:定义新的组表文件 T.ctx。

A3:建立新的组表,@r 表示行存,# 表示按照 id 有序,追加 A2 排序后的游标数据。

A4:打开新组表,对 id 建立索引,索引文件是 T.idx。

二、数据预处理,有序列存、带值索引


A

B

1

=file("T-original.ctx").open().cursor(id,tdate,amt,…)

2

=A1.sortx(id)

=file("T.ctx")

3

=B2.create@r(#id,tdate,amt,…).append@i(A2)

4

=B2.open().index(file("T.idx");id;tdate,amt,…)

A1:打开原始组表 T-original.ctx,建立游标。

A2:对 A1 游标按照 id 做外存排序。

B2:定义新的组表文件 T.ctx。

A3:建立新的组表,列存,# 表示按照 id 有序,追加 A2 排序后的游标数据。

A4:打开新组表,对 id 建立带值索引,最后一个分号后是在索引中冗余的字段。

行存 + 普通索引、列存 + 带值索引,根据实际需求二选一即可,后续代码不需要变动。

三、索引预加载

在系统初始化或者索引发生变动时,要加载索引到内存中,每次查询计算的时候可以节省加载索引的时间。


A

B

1

if !ifv(T)

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

2


=env(T,B1)

A1:判断全局变量 T 是否存在,如果存在,表示已经加载了索引。

B1:如果全局变量 T 不存在,那么打开组表加载三级索引。@2 或者 @3 表示加载 2 或者 3 级索引。3 级索引性能更好,但需要更大的内存。具体加载 2 级还是 3 级,需要根据索引的大小和内存的容量确定。

B2:全局变量 T 赋值为 B1。

四、帐户查询

因为预先加载了带索引的组表,所以查询代码非常简单:

=T.icursor(;id==10100 && tdate>=date("2021-01-10") && tdate<date("2021-01-25") && …).fetch()

icursor 是带索引的游标。

实际应用中,帐户 id、时间段等条件,要通过参数传入。

五、新增数据

假设 T 表新增数据存储在 T_new.btx 中,且字段名称、顺序与 T.ctx 相同。


A

B

1

=file("T.ctx").open()


2

=file("T_new.btx").cursor@b().sortx(id)

3

if (day(now())==1)

>A1.reset()

4

>A1.append@a(A2)


5

=A1.index(file("T.idx");id;tdate,amt,…)

6

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

=env(T,A6)

A1:打开组表 T.ctx。

A2:定义 T_new.btx 的游标并排序,通常每天新增数据量不大,这里虽然用 sortx,但实际上经常会是内存排序,速度很快。

A3:判断日期是否为 1 号,如果不是则执行 A4,用 append@a 只在补数据上归并。如果是 1 号,那么就执行 B3,用 reset 把原有序数据和补数据有序归并成新的有序数据。这里以每天新增数据,每月重新归并有序数据为例,需要其他时间周期时,可以修改这里的判断条件。

A5:重建索引文件。

A6、B6:预加载到内存中的带索引的组表对象需要手工更新。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾