查看原文
其他

大清单报表应当怎么做?

蒋步星 数据蒋堂 2022-07-30

在数据查询时,有时会碰到数据量很大的清单报表。用户输入的查询条件很宽泛,可能会从数据库中查出几百上千万行甚至过亿的记录。如果等着把这些记录全部检索出来再生成报表呈现,那需要很长时间,用户体验恶劣;而且报表一般采用内存运算机制,大多数情况下也装不下这么多数据。所以,我们一般都是使用分页呈现的方式,尽量快速地呈现出第一页,然后可以随意翻页显示,每次只显示一页,也不会造成内存溢出。


那么,一般的报表工具或BI系统都是怎么实现这一机制的呢?

绝大多数产品都是使用数据库分页的方法来做的。

具体来讲,就是利用数据库提供的返回指定行号范围内记录的语法。界面端根据当前页号计算出行号范围(每页显示固定行数)作为参数拼入SQL中,数据库就会只返回当前页的记录,从而实现分页呈现的效果。

这样做,会有两个问题:

1. 翻页时效率较差

用这种办法呈现出第一页来一般都会比较快,但如果向后翻页时,这个原始取数的SQL会被再次执行,并且将前面页涉及的记录跳过。有些数据库没有OFFSET关键字,就只能由界面端自行跳过这些数据(取出后丢弃),像ORACLE还需要用子查询产生一个序号才能再用序号做过滤,这些动作都会浪费时间,前几页还感觉不明显,但如果翻到的页号比较大时,就会有等待感了。

2. 可能出现数据不一致

一般来说,每次按页取数时发出的SQL是独立的。这样,如果在两页取数之间数据库又有了插入删除动作,这时取出来的数据将是最新的,很可能和原来的页号匹配不上了。比如第1页取出20行记录后,在取第2页前,第1页的20行记录中被删除了1行,那么这时候取出来的第2页的第1行就会是原来的第22行记录,原来的第21行会落到第1页去了,要再倒翻页才能看到。如果基于这些数据做汇总统计,那会出现错误的结果。


还有一种不常用的方法。向数据库发出取数SQL生成游标,从中取出一页后呈现,但并不终止这个游标,要取下一页的时候再继续取数。这种方法能克服上述两个问题,不会发生不一致的现象,但绝大多数的数据库游标只能向后取数而不是倒回去,这样在界面上的表现就是只能向后翻页了,这一点很难向业务用户解释,所以很少用这种办法。

也可以是两种办法的结合,向后翻页时用后一种办法,一旦发生向前翻页时,则重新执行取数SQL。这样比每次分页取数的体验略好一些,但并没有根本上解决问题。


还有什么好办法呢?

把取数和呈现做成两个异步线程,取数线程发出SQL后就不断取出数据并缓存到本地存储中,呈现线程根据页数计算出行数到本地缓存中去获取数据显示。这样,只要已经取过的数据就能快速呈现,不会有等待感,还没取到的数据需要等待一下也是正常可理解的;而取数线程只涉及一句SQL,在数据库中是同一个事务,也不会有不一致的问题。这样,两个问题都能得到解决。不过这需要设计一种可以按行号随机访问记录的存储格式,不然要靠遍历把记录数出来,那反应仍然会很迟钝。

在当前数据库系统不直接支持这种机制时,只能是报表工具或BI系统受累自己写这些程序了,对于有大清单报表呈现需求的用户,就要认真考察这些功能点了。


《数据蒋堂》官方技术交流群

欢迎各路技术大咖入群,与作者交流

(该二维码七天后失效)

本期共有两篇相关话题文章

请关注公众号或【阅读原文】查看


大清单报表应当怎么做?(本文)

大清单报表的打印?

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存