1 前言

    一般情况下，报表查询的数据量都比较大，访问报表时会出现加载比较慢或者卡顿的现象，本篇指南我们通过分析一个生产环境报表优化的案例来了解下在代码和sql层面的优化关键点和方法，以及通过介绍苍穹平台提供的报表分批加载查询方法，案例的实现，为开发者在后续优化报表问题时提供更清晰的思路，从而更好的处理海量数据报表查询。

2 优化案例

    本案例场景是某客户系统的员工统计相关报表，整个报表涉及到10+张表的关联查询且取数业务逻辑较复杂。优化前，在主表数据1W多的情况下，报表加载需要1分钟左右，优化后加载时间大概是1s-2s。

    2.1优化步骤

    报表涉及多表关联查询，加载较慢，首先需定位的是哪些代码或是哪些表执行较慢，需要充分利用平台的Monitor分析日志，查看每段代码（即某一业务逻辑）或者是sql的执行时间，为了更方便的定位和统计查询时间，可以在涉及到业务查询的代码处加上查询开始和结束时间日志。（如何在Monitor获取并分析日志请参考苍穹定制化开发性能优化专题系列（三）相关内容）

    2.2优化关键点

   1. 示例：如下图所示：

    （1）问题：循环中查询数据库

注：不要在循环里面查询数据库因为查询数据库要通过url建立连接，初始化，获取连接池，再把资源放回连接池，关闭等操作，十分的消耗资源。如果在循环里面查询数据库，会频繁的进行上面的那些操作，消耗掉大量的资源，导致延迟，卡死，等待等诸多状况。。

    （2）优化方法：传入集合进行查询，通过sql中in的方式，如in中的字段过多，可能同样会对性能有些影响，可以采用分批次in查询或者是在in的数量超过某一阈值时，转临时表方式处理。目前在苍穹平台提供对in查询的方式转临时表的优化（orm.opt.in.enable和orm.opt.in.type参数）。

    2. 示例：查询身份证和银行卡附件不为空的总数，关联project表做分组计数

优化前代码：

（其中fsmk_worker_idcard_a.id,entryentity.fsmk_base_bank_annex.id为身份证和银行卡附件表）

对应的sql语句：

    （1）问题：关联单据体查询时，使用QueryServiceHelper.queryDataSet将所有子单据体作为列直接查询，该用法会使用left join进行关联查询，代码中虽然关联的条件是索引字段ID和EntryId，但查询的条件为ID or EntryId is not null，会导致索引失效。在执行语句时，有多个join的情况下，会将前面的 Join 结果集作为循环基础数据，再通过循环查询条件到下一个表中查询数据，上面的示例中，身份证和银行卡附件表都是1万多数据，它们在一个查询中join关联，笛卡尔积较大，效率较低。

    （2）优化方法：将关联的子单据体身份证和银行卡附件表拆分，分别查询

示例：优化后代码：

 优化后对应的sql语句：

经过测试，身份证和银行卡附件表都是1万多数据的基础上，需要80S左右的查询时间，优化后只需要600毫秒左右。

     3. 示例：使用查询的结果集循环对附件数去重

    （1）问题：代码中存在对大数据量的循环计数和去重

    （2）优化方法：充分利用平台提供的Algo的性能优势，使用Algo语法中的分组去重，计数等，如group by、count等。（Algo是通过宿主机内存计算代替数据库计算，减轻数据库压力，解决数据库性能瓶颈，适用于大数据量且需要进行多次计算的场景）

对应的sql语句：

    4. 充分理解业务取数逻辑，善于复用已查询的数据，减少对表的查询次数或避免对同一张表做多次查询

    （1）如报表中的某一字段是可以通过前面已经查询的结果进行数据组装或计算得到的，优先通过代码进行处理；

    （2）如某一子查询的结果集dataSet，是可以通过前面查询的数据进行组装得出的，优先使用JAVA集合做处理和计算，创建空的dataSet。Algo支持创建dataSet，创建dataSet方式：

Algo.create(this.getClass().getName()).createDataSet(inputs)，其中inputs为CollectionInput。如下图所示：

5. sql语句优化：查询时，需关注报表中的每一处子查询是否增加索引或者是否正确使用索引（慢sql优化相关内容可参考苍穹定制化开发性能优化专题系列（一））

3限定最大展示记录数

    针对大数据量报表，完成代码和sql语句层面的优化后，可以通过开发插件时限定最大展示记录数，即将数据分批展示，控制一次展示的数量。

    1. 苍穹平台查询结果集方法QueryServiceHelper.queryDataSet支持传入orderbys和top参数，在对某一字段排序后查询出前top的数据

    2. 设置分页条数

4分批加载查询

    实际使用中，有些报表确实需要查询海量数据的，限制数量的方式不符合要求，基于该场景，平台提供了分批加载查询海量数据的优化方案（数据量超过10W建议采用）。

目前，在苍穹中常见的开发报表都是通过继承报表查询插件AbstractReportListDataPlugin类，重写query方法实现的，该方法实现方式是服务端一次性加载所有数据到内存，前端报表进行分页请求取数时，服务端从内存中取对应的分页数据返回前端，直至报表页签关闭后才会释放内存，所以在数据量较大的情况下，查询报表时会出现卡顿，不可用等现象；而分批加载查询的方式，服务端优化为分批次加载数据到内存，内存中不保存全部批次，且内存不足时会释放掉部分缓存（内存中的最大缓存数据行数由插件根据业务场景自行指定）。

    4.1使用方式

     报表插件需继承AbstractReportListDataPlugin类，重写queryBatchBy方法和query方法，报表插件执行时会先执行queryBatchBy返回用于分批取数的依据，再执行query方法基于分批的数据集再进行过滤查询。目前，树型报表暂时不支持分批加载。

（注：该方案版本V3.0.007_0328以上支持）

    4.2开发案例实现

    场景：查询某报表数据，日期范围作为过滤条件，实现以日期为分批依据，10天一个批次加载数据到内存中。

    1. 实现代码：

    （1）重写queryBatchBy方法

其中queryParam.byBatchInfo().setCountPerBatch为设置一次调用query时传入分批数据集的数量；

queryParam.byBatchInfo().setMaxRowCountCached为设置报表整体缓存在内存中的记录数，默认为10万。

    (2) 重写query方法

其中reportQueryParam.byBatchInfo().getCurrentBatchRows()为获取当前批次过滤数据集；reportQueryParam.byBatchInfo.isLargeData()为判断是否重写queryBatchBy，是否处理大数据量查询。

    2. 实现效果：分批次查询2020.1.1-2021.5.1的数据

4.3分批依据选择

    分批依据的确认一般要根据具体报表的业务数据特性来定，主要原则如下：

    1. 用来分批的字段，其每一批次对应的报表数据需较为均衡，以避免一批次就把内存耗光；

    2. 尽量用过滤条件中必录条件或用于排序的字段；

    3. 分批依据字段可一或多个字段，注意的是queryBatchBy返回的DataSet会全部缓存在内存中，尽量控制不要过大；

    4. 返回的分批依据集是按业务需求排序过的，分批传入query时是按顺序传入。

参考资料：http://kdblog.yesfree.cn/posts/bigDataReport.html

如有疑问或者更好的建议，欢迎各位小伙伴留言或私聊~