第 6 章 系统查询性能优化

6.1       基于缓存的优化

常见的缓存方式是将查询SQL语句作为key,查询结果作为value,但这种 缓存方式有很大的缺陷：当查询 SQL 语句不确定，查询条件经常变化时，缓存命 中率会非常低。在数据报表场景下，用户选择的筛选条件和时间范围灵活多变， 不能直接使用SQL语句作为缓存key。为了提升报表查询性能，同时提升缓存命 中率，论文提出基于时间片的汇总指标缓存方案。

6.1.1      汇总指标缓存设计

本系统将展示数据分为两大类，一类是报表的总览数据，也可以称作汇总指 标，即通过 sum、count、avg、max 等聚合函数以选择的时间范围为粒度计算得 到的汇总数据（非group by操作）；另一类是维度数据，展示的是对某维度group by 后的基本信息和聚合数据指标，字段多且数据量较大。汇总指标示例如图 6.1 所示，展示的是当前用户名下所有账户的汇总结果，明细数据如表 7.3 所示，以 列表形式分页展示广告维度基本字段和数据指标。显然，维度数据展示灵活且数 据量较大，不适合缓存，所以本系统只对报表的汇总指标进行缓存优化。

账户大盘概览

图 6.1 数据报表汇总指标图

为了提升缓存复用率，系统将查询语句拆分成多条单个指标查询语句进行缓 存 ， 例如 SQL 语 句 Q1 ：select sum(cost) as total_cost, count(account_id) as num, avg(cost) as avg_cost from account_cost_info where cost > 0 and p_date between 2022.01.01 and 2022.01.02 可以拆分成三条查询语句:

q1：select sum(cost) as total_cost from account_cost_info where ...；

q2：select count(account_id) as num from account_cost_info where ...；

q3：select avg(cost) as avg_cost from account_cost_info where ... 当有其他查询包含 total_cost 和 avg_cost 展示指标且 where 筛选条件相等时，便可 以复用 q1 和 q3 语句对应的缓存结果。

汇总指标虽然筛选条件简单但是时间范围选择灵活，为了提高缓存命中率， 系统需要生成一套基于时间范围的缓存：当查询在缓存中没有直接命中时，先扫 描缓存中是否已存在部分时间范围的结果，未命中再查询数据库。为了方便匹配， 避免低质量缓存结果，同时兼顾实时查询场景，系统以固定时间片来缓存查询结 果，粒度可以为小时和天级别，使用剔除时间范围条件的 SQL 语句作为缓存 key。 此外，使用缓存时需要将用户输入的查询语句按照指标和时间范围与缓存 key 进 行匹配，若匹配失败则将查询按照时间片粒度拆分成多条语句并行执行，将结果 按照指标拆分写入缓存中，最后汇总结果返回给调用方。

综上所述，汇总指标的缓存方案设计如图 6.2 所示：

图 6.2 汇总指标缓存方案设计图

为了避免时间片太小导致数据库查询并发量升高，本系统对今日实时数据采 用小时作为分隔时间段的单位时间，对今日之前的数据用天作为单位时间。

6.1.2    最小代价缓存淘汰策略

因为报表系统主要是数据展示，没有更新报表数据操作，所以不涉及缓存一 致性问题，缓存过期时间也可以设置为很长(本文设置为永久)，但是缓存容量 有限，当缓存满了就会触发缓存淘汰机制。系统使用 Redis 作为缓存实现，常用 的缓存淘汰机制包括 LRU、LFU 和随机淘汰等，为了提高缓存的命中率同时降低 计算成本，本文使用基于查询间隔和耗时为“代价”的缓存淘汰策略。

若是直接淘汰访问时间最早的缓存，但它有可能计算起来比较耗时，若直接 淘汰计算时间最短的缓存，它又有可能被经常访问，所以代价最小的缓存是不被 经常访问且计算耗时短的缓存。在实际操作中，我们需要综合考虑缓存结果的计 算时间和访问间隔来计算缓存淘汰代价(Obsolete Cost, OC )，每次淘汰OC值最 小的缓存即可。0C的计算公式为：OC = QT+C/TI,其中QT ( Query Time )表示 查询耗费时间，单位为秒；TI ( Time Interval)表示距离上次命中的时间间隔，以 小时为单位；C是平衡系数可避免耗时或间隔比重过大。经统计，系统报表指标 的平均 QT 在 0.8s 左右，平均 TI 在 20 个小时左右，为了方便计算，本系统将 C 设置为 16，以保证查询耗时和时间间隔各占 OC 值一半左右的比重。

查询耗费时间通过在代码中统计 SQL 语句执行时间获得，在写入缓存结果时 同时记录该 SQL 语句的执行时间、第一次写入时间和最近一次命中时间等参数。 当缓存容量达到上限时，查询所有缓存 key 对应的查询耗时并计算距离上次命中 的时间间隔，若写入后还未发生二次命中，则命中时间为写入时间，然后根据 OC 计算公式计算所有缓存的淘汰代价并按照升序排序，最后淘汰首位即可。为 了减少淘汰发生频率提升性能，当触发淘汰机制时可采用堆排序方式进行排序， 并一次性淘汰代价排名靠前的多个缓存。

6.1.3    查询缓存执行过程

为了满足汇总指标查询结果的缓存及定位，提高缓存复用性，系统需要对指 标的查询语句进行拆分，如 6.1.1 小节中的查询语句 Q 被拆解成 q1、q2 和 q3 三 条查询语句，之后移除掉时间筛选字段以字符串形式作为缓存的key。缓存初始时 value 值为空，所以查询缓存不会命中，则会查询数据库，并将结果按照 SQL 作为缓存 key 和 hash 表形式的 value 写入缓存，表的 key 为时间范围起始时间拼 接后的字符串，value为指标的计算结果，指标查询具体执行步骤如下所示： 步骤一：判断 SQL 是否为汇总指标查询且需要缓存； 步骤二：解析 SQL 语句得到查询汇总指标和时间范围；

步骤三：将指标拼接成多条查询SQL,并移除时间范围筛选条件； 步骤四：使用拼接后的 SQL 字符串和缓存中 key 匹配，若成功获得 key 对应 的 hash 表则执行步骤五，否则执行步骤七；

步骤五：将查询语句中的时间范围拼成字符串，判断 hash 表中是否包含该时 间范围，若包含则直接返回对应结果，否则执行步骤六；

步骤六:将时间范围按照时间粒度分割成固定的时间片段,遍历执行判断hash

表中是否存在该缓存，若不存在则执行步骤七，否则执行步骤八； 步骤七：缓存未命中，则执行 SQL 语句查询数据库，并将结果写入缓存； 步骤八：在内存中拼接结果，并返回至前端展示。

6.1.4 缓存优化结果对比

经过日志埋点统计，系统中汇总指标查询次数约占所有数据查询展示频次的 20%左右，主要发生在用户打开数据报表页面时刻，起到数据总览数的作用。通 过日志监控记录系统用户请求，流量回放测试汇总指标在无缓存、传统 SQL 缓存 和基于时间片缓存三种情况下的平均查询时间，结果如图 6.3 所示：

图 6.3 缓存优化查询结果对比柱状图

可以看出传统 SQL 缓存方式，因为用户查询报表时间范围筛选不固定，所以查询 优化程度有限，无法达到系统性能优化要求。而使用基于时间片的缓存方式，随 着查询次数的增加，平均查询时间明显减少，最终稳定在 150ms 左右。

论文为基于时间片的缓存方案设计了最小代价缓存替换策略，相比于传统的 LRU、LFU 等缓存方式，此方案综合考虑了缓存指标的计算时间及最近使用时间 使用代价值决定缓存是否淘汰。通过日志监控记录系统用户请求，流量回放测试 汇总指标在基于时间片的缓存方式下，使用 LRU、LFU 和最小代价三种缓存替换 策略的命中率以及对应的平均查询时间，结果如图 6.4 所示：

图 6.4 缓存优化查询耗时和命中率对比图 

随着查询次数增加，三种缓存策略命中率均逐渐提升，其中 LRU 缓存命中率最 高，最小代价次之。指标平均查询时间随着查询次数增加而降低，而使用最小代 价替换策略方式的平均查询耗时最低，这说明最小代价缓存替换方式虽然命中率 不及 LRU 缓存方式，但是具有更低的平均查询耗时，也证明本文设计的缓存替 换策略有效，符合系统查询性能优化需要。

6.2 基于物化视图的优化

缓存只能减少汇总指标的查询时间，但是报表系统以多维度下的明细数据指 标为主，且存在查询耗时较长的场景，比如：以客户维度计算客户近半年的总消 耗、日均消耗等指标，数据量太大不适合使用缓存，因此，报表系统针对底层数 据结构设计了物化视图，结合视图路由算法，实现大幅降低报表整体响应时间。

6.2.1 物化视图方案设计

物化视图［43］是指针对据仓中开销较大的查询语句，事先进行预聚合计算并将 结果存储到数据表中，可以看做是表部分数据的“快照”，之后进行相同或相似 的查询时，直接从物化视图中获取结果。报表系统针对慢查询设计了两个层面的 物化视图进行优化，一个是维度层面的物化，一个是时序层面的物化。

维度物化视图［44］是事先根据高频维度做预聚合操作。假设一个数据源的原始 维度有 8 列，通过分析查询请求发现，其中的某 4 个维度经常同时出现，剩余的 4 个维度查询频率较低，但是剩余维度中有高聚合维度（细粒度）字段，比如创 意 id 等，当对上述 4 个维度做 group by 查询操作时，会耗费较多的时间和计算资 源。针对这种情况，可以将上述 4 个维度和数据指标抽取创建物化视图，之后查 询时，先分析查询语句中的维度，如果查询维度集合是新建物化视图集合的子集， 则直接查询物化视图获得结果，不需要访问原始的数据表，具体如图 6.5 所示：

图 6.5 维度物化视图设计图

时序物化视图［45］是事先根据时间维度做聚合操作。在面对大跨度时间范围的 查询时，ClickHouse查询无法及时完成计算，比如数据源的聚合维度是分钟级别, 但需要查询最近半年的消耗数据，则需要把过去半年的所有分区文件全部扫描一 遍，然后进行聚合计算，当数据量比较大时耗时较久。为了解决这个问题，报表 系统在分钟级别数据源上新建小时、天、周甚至到月的物化视图，当收到一个新 的查询请求时，如果查询要统计的粒度是天级别或者是更高级别的查询粒度，系 统将查询请求路由到对应级别的物化视图上，具体如图6.6所示：

图 6.6 时序物化视图设计图

报表系统底层数据表中字段可以分为三类：时间字段、维度字段和数据指标 字段，时间字段和维度字段主要用来做 group by 聚合，伴随着上卷、下钻等 OLAP 分析操作将数据以不同粒度展示给用户，数据指标是数值类型数据，以求和、求 平均值、求最大值和最小值等聚合计算为主，维度物化视图和时序物化视图都是 根据字段进行预聚合操作，只是聚合维度方向不同。

6.2.2 物化视图划分与创建

实现物化视图有两个难点：一是如何花费最小成本创建物化视图，并设计最 优的物化视图划分、路由策略，二是如何保证原数据表和物化视图数据一致［49］。 本系统 ClickHouse 中数据只添加不更改，且数据时效要求不高，所以只需要设置 insert数据时更新物化视图即可，而且大部分数据库（包括ClickHouse ）都支持此 同步方式，所以本文重点关注难点一中视图创建、划分和路由的设计实现。

物化视图划分依据来自于系统的日常使用和用户反馈。通过收集记录系统慢 查询情况，我们总结出以下情况下的数据表需要创建物化视图：

1） 单表日增数据或全量更新数据超千万行；

2） 聚合维度多余10个，并存在高维度字段（创意id、素材id和账户id等）；

3） 时间范围选择在一个月以上，单个聚合指标查询时间超过 3 秒；

4） ClickHouse 单表 QPS 超过 100（ ClickHouse 不支持高并发）。 基于上述条件，可以发现广告创意宽表、计费宽表和素材宽表等存在上述问题， 需要创建物化视图来优化报表数据查询性能。

本文以维度物化和时序物化两个层面对上述宽表进行查询优化，以广告创意

广告创意宽表各维度聚合后数据量级从大到小是：创意、广告计划、广告组、广 告主、客户、代理商，基于业务需求，将创意宽表物化成广告主、客户、代理商 三个维度的视图，原表每日新增记录两亿多条，物化后降为百万、十万到万级别。 时序物化视图可在原来的分钟粒度之上物化聚合为日、周、月粒度，数据量呈指 数级减少，视图维度和数据指标尽量与原表保持一致。当创意表聚合成广告主维 度后，数量级为百万级别，所以不必对广告主等维度物化视图再做时序物化。

ClickHouse 支持通过 SQL 方式创建物化视图，首先需要确定源表，然后通过 “create materialized view" SQL语句创建物化视图，通过“asselect"子句从源表 中查询需要的列和需要聚合计算的指标， ClickHouse 会根据 SQL 自动创建前缀为 “.inner”的基础表，用来持久化物化视图中的数据，之后便可以像查询正常数据 那样查询物化视图，来加速查询过程，具体架构如图 6.7 所示：

图 6.7 ClickHouse 物化视图架构图 

此外， ClickHouse 还支持通过自定义基础表方式实现物化视图到基础表的挂载，SQL 格式为:“create materialized view 物化视图名 to 表名 asselect...”,采用 to 关键字为视图表指定存储位置，这种方式创建视图可以实现嵌套视图(多个物化 视图继续聚合一个新的视图)，自动创建基础表方式无法实现嵌套视图。

以广告创意宽表为例，创建广告账户维度的维度物化视图SQL语句如下所示：

CREATE MATERIALIZED VIEW ad_dw_account_materialized_view_di

ENGINE 二 MergeTree PARTITION BY toYYYYMMDD(p_date)

OREDER BY (id, p_date) PRIMARY KEY (id, pedate)

AS SELECT p_dater account_id as id, account_name,

client_id, client_name,agent_id,agent_name・・・ //维度字段 sum(cost_totai), sum(cash_charged), sum(credit_charged) sum(ad_item_click), sum(ad_item_click)・・・           //数据指标字段

FROM ad_dw_creative_report_di GROUP BY account_id ORDER BY p_date, account_id;

以account_id进行聚合，会使创意明细字段失效，所以需要将其移除，另外所有 的数据指标需要以广告主维度进行累加处理。时序物化视图的创建与上述步骤类 似，区别在于group by语句中需要指定聚合的时间粒度，比如语句：“GROUP BY toYYYYMMDD(p_date)”代表取表中记录时间的日期前缀为聚合条件，生成“日” 时间级别的物化视图。查询物化视图和查询普通表方式类似，编写标准 SQL 语句， 将表名改为物化视图名称即可实现对数据查询分析。

6.2.3 物化视图请求路由策略

根据时间和维度字段创建物化视图完成后，需要对用户请求做判断处理。在实际应用中，用户输入的维度和时间筛选范围不固定也没有规则，所以需要对维 度和时间进行拆分和判断，之后映射到对应的物化视图。物化视图的选择主要由 请求中维度字段和时间粒度决定，经过实践发现，维度物化后数据量更小，且与 系统业务更加匹配，即当满足维度物化条件时，优先走维度物化视图。

维度物化视图路由判断比较简单，首先计算满足条件的维度物化视图集合， 查询维度是否为物化视图维度的子集即为满足条件；之后返回满足条件的物化视 图中数据量最小的视图即可。时序物化视图判断起来复杂，因为用户输入的时间 范围和时间聚合粒度不确定。报表系统目前支持的时间聚合粒度从下到大有小 时、天、周、月、年以及汇总，小时粒度适用于实时数据报表，汇总是对选择的 时间范围粒度做聚合处理。为了保证充分利用物化视图特性，最优化查询性能， 报表系统采用“贪心”策略来实现对时序物化视图的路由选择。

时序视图路由算法步骤如下：

步骤 1：根据用户输入的时间聚合粒度、维度字段和计算指标，获得时序物 化视图候选集，规模较小时可以选择手动实现；

步骤 2：对物化视图候选集按照时间粒度从大到小进行排序，时间相同按维 度数量从少到多进行排序；

步骤 3：若时间聚合粒度是非汇总或者需要按维度进行 group by 操作，则直 接返回物化视图候选集第一个视图，否则执行步骤 4；

步骤 4：若为汇总查询，则采用贪心策略将用户请求中的时间范围分割成月、 周、日、小时形式，其中时间粒度越粗，则对应数据量越少，查询速度越快，分 割时优先选取最粗的时间粒度，迭代划分；

步骤 5：按照分割后的时间片生成查询语句，到对应的物化视图并行执行查 询，最后在内存中对结果汇总返回。

以时间范围 2020.11.11~2021.11.11 为例，对其进行判断拆分，对应的时序物 化视图路由选择如图 6.8 所示：

图 6.8 时序物化视图路由选择图

2020.12.01 至 2021.11.30 时间范围内包含 11 个整月份，所以使用月粒度的物化视 图进行计算为最优，两侧的时间范围可以继续划分为周粒度物化视图和日物化视图。当时间范围划分成多个不等时间片后，生成对应时间的查询 SQL 语句并行执行，最终将结果在内存中合并返回，路由算法核心代码如下：

1        public AdCHMVView getClickHouseMVBYQuery(QueryParam queryParam) {

2                  AdCHMVView result = new AdCHMVViewt);

3                  //获得数据库,表和字段信息

4                  AdDblnfo dblnfo = reportDBMapService

.getDBInfoByReportType(queryParam.getReportType());

6             //根据CH表id得到对应的维度物化视图信息

List<AdCHMVInfo> dimMvList = adReportCHMVMapService

.getMVByld(dbTablelnfo.getTableld(), AdCHMVTypeEnum.DIM);

//获得满足条件的维度物化视图信息

List<AdCHMVInfo> satisfyDimMvList = dimMvList.st ream().filter(MVTableInfo -> checkCHDimMv(MVTableInfof dblnfo)).collect(Collectors.toListt)); if (satisfyDimMvList.size() > 0) { //走维度物化视图

result.setMVType(AdCHMVTypeEnum.DIM); result.setDimMVList(Arrays.asList(getMinScaleDimMV(satisfyDimMvList)));

} else {         //走时序物化视图

//根据CH表id得到对应的时序物化视图信息

List<AdCHMVInfo> timeMvList = adReportCHMVMapService

.getMVByld(dbTablelnfo.getTableld(), AdCHMVTypeEnum.TIME);

if (timeMvList.size() == 0) {

result.setMVType(AdCHMVTypeEnum.ORIGIN); } else {//分割时间范围，得到时序视图和时间段的映射关系

Map<String, AdCHMVInfo> resultTimeMVMap = caITimeMVByTimeRange(timeMvList, queryParam); result.setMVType(AdCHMVTypeEnum.TIME);

result.setTimeMVMap(resultTimeMVMap);}

ret urn r esult;

28 }

 首先根据报表类型 reportType 获得对应的数据表，并根据 tableId 查询其对应的维 度物化视图，之后计算满足条件的维度物化视图请从中选出数据量级(创建视图 时指定)最小的，若没有满足条件的维度视图则开始查找时序视图，并计算时序 视图和时间段的映射关系，若没有满足条件的时序视图，则查询原表。

时序视图将时间范围划成多个时间段，首先获得已创建时序物化视图和其对 应时间维度的映射关系，之后筛选出符合请求参数中时间汇总条件的视图，即视 图的时间聚合粒度小于参数指定的粒度，最后根据筛选得到的物化视图集递归计 算给定时间范围可以划分的时间段及对应时序视图。系统目前支持“年/月/周/天/ 小时” 5 个时间粒度级别，将传参中的时间范围与上述的时间粒度从大到小逐级 比对，即使用“贪心”策略，从最大粒度开始，核心计算逻辑如下所示：

1       long diff = endTime - startTime;

2       if (diff > 2 * YEAR * DAY_MILLIS && yearMV != null) {                           //可按照”年”维度划分

3                  //获得包含的年份

4                  TimeRange timeRange = getYearStartAndEndTime(startTime, endTime);

5                  result.put(timeRange.getLeft() +                          + timeRange.getRight(), yearMV);

//向两侧区间递归遍历计算

recursionDividefstartTime, timeRange・getLeft(), resuIt, timeMVMap); recursionDivide(timeRange・getRight(), endTime,result, timeMVMap);

9       }

10          if (diff > YEAR * DAY_MILLIS && ha If Yea rMV != null) {                      //可按照”半年”维度划分

11                    //获得包含的半年份

TimeRange timeRange = getHalfYearStartAndEndTime(startTime# endTime); result.put(timeRange.getLeft() +                         + timeRange.getRight(), haIfYearMV);

14                    //向两侧区间递归遍历计算

15                    recursionDivide(startTime, timeRange・getLeft(), resuIt, timeMVMap);

16                    recursionDivideftimeRange.getRight(), endTime,result, timeMVMap);

17          }

以“年”和“半年”维度为例，首先判断输入的时间范围是否大于2倍的年时间 (2*365 天换算成毫秒)，若大于，则必包含一个完整的年份，同理，若大于一 年对应的毫秒时间，则一定包含完整的半年，之后将时间范围划分为 3段，中间 部分即为所求的时间范围，两侧的时间段继续递归计算，直到时间范围区间长度 为 0 时结束计算。通过上述的计算可以得到输入时间划分成各粒度时间片的最优 解，之后生成查询SQL并使用线程池进行并行计算，最终在内存中汇总结果。汇 总结果时需要注意，将 group by 语句后的维度字段(一个或多个)对应的数据值 拼接成key,将key相同的数据指标进行累加得到汇总结果。

若查询条件包含排序和分页，使用上述方式进行计算会产生汇总结果不正确 的问题，针对该问题，系统采用“二次查询”方式解决。以排序来讲，第一次查询只计算排序字段对应的数据列，返回得到最小粒度主键(如account_id )集合, 然后将该集合作为筛选条件再次进行查询即可，分页处理同理。“二次查询”的 两次查询过程依然可以正常使用物化视图方式进行优化。

6.2.4 物化视图优化结果对比

物化视图是典型的空间换时间方案，尤其是在面对海量数据聚合计算时，可 大幅减少查询分析时间，以 5.6.1 小节中广告创意表的物化视图为例进行计算。

( 1)维度物化性能对比

设计 SQL 对比前后结果，以 2020.11.1~2020.11.30 为时间范围，计算广告投 放消耗排名ToplO的广告主、客户和代理商的数据指标，查询SQL为：

SELECT SUM(total_cost) as cost FROM ad_dw_creative_xxx_di

WHERE p_date >= :start_time AND p_date <= :end_time

GROUP BY :key_id ORDER BY cost limit 10;

参数key」d分别对应account_id、client_id和agent_id,也分别对应着广告主、客 户和代理商三个物化视图，通过改变参数值可生成 3 条对应物化视图的 SQL 和 3 条查询原表的SQL,执行上述的6条SQL语句，得到对比结果如表6.3所示：

表 6.3 维度物化视图查询结果对比表

从表 5.6 中可以看出，查询物化视图比查询原表快了十几倍甚至几十倍，而且当 查询的数据指标变多时，性能提升会更加明显。

( 2)时序物化性能对比 时序物化性能对比需要控制查询时间范围，以计算指定时间范围内广告投放 汇总消耗 Topl0 客户及其日平均消耗指标为例，查询 SQL 为：

SELECT SUM(total_cost) as cost,

SUM(total_cost)/(DATEDIFF(:start_time,:end_time))

FROM ad_dw_creative_xxx_di

WHERE p_date >= :start_time AND p_date <= :end_time GROUP BY client_id ORDER BY cost limit 10;

通过改变传参start_time和end_time,在不同表中查询在此时间段内的广告总消 耗和日均消耗排名 Topl0 的客户，并记录耗时情况，得到结果如图 6.9 所示：

査原表—物化视图（传统）         物化视图（贪心策略）

从图 6.9 中明显可以看出，随着筛选时间范围的变大，查原表耗时迅速增加，在 查询走物化视图后，时间范围越大查询优化越明显，查询耗时随时间范围增加变 化不大，证明使用物化视图可大幅降低数据报表的响应时间。此外，传统的物化 视图路由方式是根据时间粒度直接查询匹配的物化视图，而本文使用基于贪心策 略的物化视图路由算法，即将查询语句根据时间片拆分得到多个子语句，并分别 映射到不同时间粒度的物化视图中进行查询，最终在内存中汇总结果返回，通过 对测试结果进行分析对比可知：基于贪心策略的物化视图路由策略在面对时间跨 度较大的查询，性能优于查原表和传统路由物化视图。

6.3 本章小结

本章主要介绍系统查询性能优化方案的设计与实现。第 1 节介绍的是基于时 间片的汇总指标缓存优化方式，将查询 SQL 按照固定时间粒度拆分成多个查询语 句，并行查询并缓存结果以加速查询过程，并结合最小代价缓存淘汰策略来提升 缓存命中率，实现汇总指标的极速查询。第 2 节介绍的基于底层数据结构的物化 视图优化方案，通过将底表划分成维度和时间物化视图，并结合基于贪心思想的 视图路由算法实现多维度、大时间跨度指标查询的秒级响应。

第 7 章 系统测试

7.1 系统运行环境

系统采用微服务架构实现代码开发和部署，总体分为两部分：一是承接前端HTTP请求的API服务层，二是提供业务服务的RPC服务层。RPC服务层可分为 多维取数、主题报表和自定义报表组成的业务服务，和用户权限、数据下载组成 的基础服务，每个服务单独开发部署，服务架构如图 7.l 所示：

系统使用公司内部的微服务平台实现服务发现和负载均衡，保证流量均匀发送到 多机房的多台实例上。为了充分利用服务器资源，采用容器云（Docker）方式部 署微服务，在服务器集群之上创建容器云平台，将服务器资源映射成可伸缩的云 容器计算单元，所有的实例部署在远端机房的云容器中。

系统通过公司内部的“HALO”容器云部署平台拉取Git仓库代码，一键打包 部署上线，各服务部署在容器云上，为了防止机器宕机或者实例挂掉造成线上事 故，采用双机房多实例方式部署。根据各服务对CPU和内存的需求不同选择合适 的容器云资源套餐，具体如表 7.l 所示：

表 7.1 报表系统运行环境表

 报表系统使用的软件及版本如表 7.2 所示：

表 7.2 报表系统使用软件及版本表

 7.2 系统功能性测试

系统采用黑盒测试方式，通过设计测试用例和预期结果来对系统各功能进行 测试检验，通过前端页面和展示数据查看测试结果。

（1）多维取数功能测试

多维取数功能测试用例和测试结果如表 7.3 所示：

表 7.3 多维取数功能测试用例和测试结果表

数据库中查询；4）对比两种结果

展示指标正1）选择固定筛选条件和聚合维度；2）选择所有的 相等 确性测试 指标；3）根据条件拼接SQL查询并对比两种结果

多维取数功能配置包括汇总方式、时间范围、字段匹配筛选、基本聚合维度 和数据展示指标等配置模块，以广告主为聚合维度，封面点击数、素材曝光数、 行为数、素材CPM、封面CPC等指标为例，配置页面如图7.2所示：

图 7.2 多维取数配置页面

用户在此页面根据需求进行灵活配置取数条件，目前系统取数功支持 50 多个筛 选条件和近 200 多个展示指标，可以满足大部分用户的取数需求。用户在取数配 置页面配置完毕后，点击“查询”按钮，系统执行取数逻辑并查询结果以列表形 式展示，以图 7.2 配置为例，取数展示结果如图 7.3 所示：

64

（2）主题报表功能测试

主题报表功能测试用例和测试结果如表 7.4 所示：

表 7.4 多维取数功能测试用例和测试结果表

主题报表展示形式以面积图、折线图、柱状图和表格为主，通常会有时间范围筛选和对应业务维度筛选条件，具体展现形式如图7.4~7.6 所示：

以面积图形式展示近七日各行业的汇总消耗以及趋势图，方便决策者查看分析行 业数据，调整业务运营策略。

以折线图形式展示代理商报表所有类型的代理商近七日的汇总消耗趋势

以柱状图形式展示品牌运营报表运营人员的项目、任务和排期等指标信息。

（3）自定义报表功能测试 自定义报表功能测试用例和测试结果如表 7.5 所示：

表 7.5 自定义报表功能测试用例和测试结果表

用户可以在模板管理页面查看自己的模板，也可以查看组内所有人员的模板，还可以对模板进行查看、编辑、复制、下线等操作，具体如图 7.7 所示：

点击“查看”按钮，跳转至自定义报表配置页面，之后点击“图表展示”功能跳转至可视化界面并以表格形式展示自定义报表数据，如图 7.8 所示：

（4）权限管理功能测试 权限管理功能测试用例和测试结果如表 7.6 所示：

表 7.6 权限管理功能测试用例和测试结果表

用户数据权限配置如图 7.9 所示，管理员可以在角色管理-功能配置功能模块 为角色绑定功能权限，之后在人员管理-编辑人员功能模块中为用户绑定角色，此 时用户即拥有了所配置功能的权限。用户数据权限配置如图7.10 所示，管理员可 以在该页面对用户的数据权限进行配置管理，可以为用户赋予架构下的用户权限 以及字段范围权限。新用户在登录系统时需申请需要的功能和数据权限。表 7.7 多维取数功能测试用例和测试结果表

下载任务管理如图 7.11 所示，用户可以在此查看管理自己的下载任务。当下 载任务生成后，用户可以在此查看任务状态（下载中、超时、失败和成功等）， 失败后可以点击重试进行再次下载，成功后用户点击“点击下载”链接导出文件， 还可以查看下载任务的配置详情，方便用户对数据进行校验。

从表 7.3~7.7 中的测试结果以及图 7.2~7.11中的页面展示结果可以看出，系统 功能正常实现运行，页面简洁易操作，可以满足用户取数分析、主题报表数据指 标查看、自定义报表配置展示、专属权限控制校验和报表数据下载等需求。

7.3      系统非功能性测试

（1）性能测试

性能测试可以从报表响应时间、并发请求角度进行测试［46］。 报表响应时间受用户筛选条件和聚合维度影响，若将时间范围边长，则查询 时间也会变慢，但是在实际应用中超过 90%以上的请求，时间范围不会超过 3 个 月，选择展示的数据指标数量也影响查询时间，数量越多响应时间越慢，因为多 维取数底层数据基本涵盖系统的广告投放相关数据。本文以多维取数功能为性能 测试场景，以客户 id=1~10 为查询条件，以消耗、计费和效果等主要数据指标为 展示指标，通过控制变量法测试报表响应时间结果如表 7.8 所示：

表 7.8 系统查询性能测试结果表

从表中数据可以看出，随着时间范围的变大和指标数量的增加，查询平均响应时 间变长，且聚合维度量级越小响应时间越短，时间汇总粒度越小响应时间越长， 由于缓存和物化视图的存在，大部分查询时间可以控制在 3 秒内。

并发性能测试即在同一时刻对同一请求增加访问数量，观测系统响应时间， 测试系统能承受的最大并发请求数。论文采用 JMeter 模拟用户请求对系统进行并 发测试，测试场景为查询效果广告宽表 2021.11.1~2021.11.10 时间范围内累计消 耗排名为 Top10 的客户广告消耗、计费、转化数据，具体结果如表 7.9 所示：

表 7.9 多维取数并发测试结果表

从表中数据可以看出，随着并发量的增加，系统响应时间变慢，尤其是当并发量 达到 200 以上时响应时间明显变慢，且查询成功率骤减，这和 ClickHouse 的特性 有关：ClickHouse在进行计算时会耗费大量的CPU资源，导致单机服务不能支持 高并发查询。后期可以通过搭建 ClickHouse 集群来提高并发查询能力。

系统通过接入Grafana[47]监测平台来实时监测多维取数和主题报表的请求情 况，多维取数 QPS 请求如图 7.12 所示：

图 7.12 多维取数模块 QPS 监测图 主题报表功能各报表 QPS 请求如图 7.13 所示：图 7.13 主题报表模块各报表 QPS 监测图 

通过对图 7.12 和图 7.13 进行分析可以发现：多维取数模块的 QPS 比较低，只有 个位数左右，主题报表模块中的各报表平均 QPS 在 15 以下，峰值在 30 左右，系 统所提供的并发能力完全可以支撑用户正常使用。

（2）安全性测试 安全性是指对用户访问或使用系统进行控制，系统主要提供以下控制： 1）接 入 API 网关，设置访问 IP 白名单，外网不允许访问；2）内网允许访问，但用户 需要申请该系统域名权限；3）使用单点登录，设置 Cookie 过期时间，不登录无法使用系统；4）通过日志记录管理用户日常操作。系统安全性测试用例和测试 结果如表 7.10 所示：

表 7.10 系统安全性测试用例和测试结果表

系统通过接入公司内部的API网关，以及添加用户登录校验组件SDK实现对 用户请求域名和状态进行拦截校验，通过引入日志 SDK 实现拦截用户请求，记 录用户操作日志。此外，系统在实现时还需要对用户进行状态判断，如：用户id 校验、用户输入参数校验等。

7.4 本章小结

本章主要介绍系统测试。第 1节介绍系统运行环境和软件版本。第2 节是功 能测试，通过分析系统需求为各功能模块编写测试用例，查看页面展示效果，来 检测系统功能模块运行是否正常。第 3 节是非功能性测试，通过控制变量设计指 标查询场景，测试系统在不同条件下的查询时间，以此检验系统查询性能，通过 JMeter并发工具测试系统支持的并发量。

第 8 章 总结与展望

8.1 工作总结

本系统是面向短视频平台广告业务，以广告数据为基础，以销售、运营人员 具体业务需求为依据，以 web 开发技术和 OLAP 引擎为手段进行设计与开发的一 款数据可视化报表系统。为了实现该系统，作者主要做了以下工作：

（1）  收集用户需求并分析，确认报表系统的需求背景和实现目标。本报表系 统是专门为广告部门的业务人员打造，主要目的是通过合理存储广告业务底层数 据来提供快速分析计算能力，开发业务指标为领导侧提供决策帮助，支持数据实 时计算分析，提高销售、运营等人员工作效率。

（2）  确定系统的实现方案。首先根据需求确定系统功能模块和服务能力，之 后确定系统架构和开发技术方案，然后设计数据存储方式，包括：使用 MySQL 数据库存储系统功能相关数据，使用 Elasticsearch 和 ClickHouse 存储来自数据仓 库的底层数据，用于报表指标展示，最后确定系统查询优化方案。

（3）  使用 ClickHosue 存储广告业务数据和日志数据。系统是即席查询的数 据报表系统，面对海量的历史数据和实时数据，如何做到快速实时查询是重点。 为了实现报表数据的实时查询分析，系统引入高性能 OLAP 引擎 ClickHosue 以宽 表形式存储来自数据仓库的底层数据，为用户提供实时查询分析服务。

（4）  设计并实现系统主要功能模块。本文先是对各功能模块进行设计，包括 取数指标、主题划分、SQL生成策略、权限模型等设计；后是功能实现，采用 Elasticsearch 加速多维取数查询过程，主题报表使用统一服务层实现动态扩展， 自定义报表采用API接口 +SQL模板形式实现，数据下载以任务形式实现。

（5）  对系统查询性能进行优化。本文针对汇总指标设计了基于时间片的缓存 方案以及最小代价缓存淘汰策略来加速查询过程；针对普通指标设计了基于维度 和时序的物化视图存储预聚合数据，并采用贪心思想的视图路由策略，使系统可 以根据用户请求自动选择合适的物化视图进行查询优化。

（6）  系统功能和非功能测试。功能测试通过分析需求编写测试用例并验证。 非功能测试分为性能测试和安全性测试，前者通过设置控制变量来测试不同条件 下的查询时间，后者是对用户访问、使用系统的进行安全控制检测。

8.2 未来展望

本文中的报表系统相比于传统的报表系统具有以下特点：

1） 支持多样化的数据可视化形式，包括柱状图、折线图、饼图、表格等；

2） 除了定制开发的主题报表外，还支持自定义实现报表，灵活满足用户需求；

3） 极速的查询性能，采用高性能 OLAP 引擎 ClickHouse 并设计物化视图和 路由算法进行优化，实现指标查询秒级响应；

4） 灵活的用户权限控制，支持细粒度（行级别）数据权限控制，除此之外还 支持数据定时下载、离线下载和调度管理等功能。

当然除了上述的优点之外，系统也还存在一些不足和值得优化的地方：

（1 ）数据存储方面：系统使用 ClikHouse 提供实时分析查询，但是数据在 Hive 数据仓库中已经存在，存在重复存储，未来可以考虑采用 Kappa 存储架构［48］， 即不再使用Hive仓库，直接将业务系统数据存储到ClikHouse。

（2） 自定义报表方面：目前报表系统支持自定义实现报表，但是目前前端是 通过再次调用接口根据数据方式，手动实现数据展示，未来可以通过 UI 组件方 式实现自动化调用接口并设计更多的展现形式；而且目前只支持 ClickHouse 和 MySQL两种数据源，未来可以加入更多数据源，比如Druid和Hive等。

（3）  数据指标方面：目前系统主要以定制报表为主，具有使用、实现方便 等特点，可以帮助业务的快速推进，但是随着公司业务不断发展，底层数据量和 指标会逐渐膨胀，主题报表数量过多也不利于后续开发和维护，未来可以考虑将 指标和报表进行剥离，只开发维护具体指标，报表由用户自己组装指标来实现， 数据权限也可以收敛到更细的指标层级。

（4） 查询性能优化方面：使用物化视图可以降低大部分指标的查询时间，但 是付出了较大的存储成本，数据冗余存储明显，未来需要制定更优秀的物化视图 创建和选择策略，使用更低的存储成本实现更大化的收益。

综上所述，本报表系统有很多的优点也有很多不足，但是比较契合公司目前 的业务发展状况，未来随着业务发展会逐渐进行迭代改进。

参考文献

[1]     中国互联网络信息中心.第48次中国互联网络发展状况统计报告[R].北京:中国互联网络信 息中心,2021.

[2]     廖大宇.5G商业模式:重塑商业化未来[M].北京:中华工商联合出版社,2021.

[3]     郭振中.亚马逊跨境品牌流量闭环:全阶广告、社交短视频与合伙人制度[M].北京：电子工业 出版社, 2021.

[4]     Thomsen, C., Pedersen, T. B. A Survey of Open Source Tools for Business Intelligence[J]. International Journal of Data Warehousing and Mining (IJDWM), 2009,5(3): 56-75.

[5]     A. Nanda, S. Gupta, M. Vijrania. A Comprehensive Survey of OLAP: Recent Trends[J]. 2019 3rd International conference on Electronics, Communication and Aerospace Technology (ICECA), 2019: 425-430.

[6]     胡杜，张笑燕.基于BI工具的OLAP SQL语句生成系统研究与实现[J].互联网天地, 2014,(12):31-35.

[7]     I. Kovacevic,I. Mekterovic.Novel BI data architectures[C] //2018 41st International Convention on Information and Communication Technology.Electronics and Microelectronics (MIPRO), 2018: 1191-1196.

[8]     A. Wickramasekara, M. P. P. Liyanage, U. Kumarasinghe. A comparative study between the capabilities of MySQL and ClickHouse in low-performance Linux environment[C] //2020 20th International Conference on Advances in ICT for Emerging Regions (ICTer), 2020: 276-277.

[9]     Heller, M. How to choose a cloud data warehouse[J]. InfoWorld.Com.

[10]     P. K. Kumar, R. Agarwal, R. Shivaprasad, et al. Performance Characterization of Communication Protocols in Microservice Applications[C] //2021 International Conference on Smart Applications, Communications and Networking (SmartNets), 2021: 1-5.

[11]     U. Taware,N. Shaikh. Heterogeneous Database System for Faster Data Querying Using Elasticsearch[J].2018 Fourth International Conference on Computing Communication Control and Automation (ICCUBEA), 2018: 1-4.

[12]     范朗.Elasticsearch海量数据存储查询优化[J].工业控制计算机,2020,33(10):3.

[13]     J. Li, X. Li, J. Lv. Selecting Materialized Views Based on Top-k Query Algorithm for Lineage Tracing[J]. 2012 Third Global Congress on Intelligent Systems, 2012: 46-49.

[14]     Y. Man, J. Zhang. Design and implementation of the OLAP cache mechanism based on incremental learning naive Bayesian algorithm[J]. 2016 First IEEE International Conference on Computer Communication and the Internet (ICCCI), 2016: 459-462.

[15]     M. Lawrence, F. Dehne, A. Rau-Chaplin. Implementing OLAP Query Fragment Aggregation and Recombination for the OLAP Enabled Grid[J]. 2007 IEEE International Parallel and Distributed Processing Symposium, 2007: 1-8.

[16]     W. Chen, H. Wang, X. Zhang, Q. Lin. An optimized distributed OLAP system for big data[J]. 2017 2nd IEEE International Conference on Computational Intelligence and Applications (ICCIA), 2017: 36-40.

[17]     M. A. Jibril, P. Gotze, D. Broneske, K. Sattler. Selective Caching: A Persistent Memory

Approach for Multi-Dimensional Index Structures[C]//2020 IEEE 36th International Conference on Data Engineering Workshops (ICDEW), 2020: 115-120.

[18]      R. Sawarkar, M. M. Baig.Performance Tuning of Queries in Distributed System using Secure Materialized View Approach[C]//2020 International Conference on Smart Innovations in Design, Environment, Management, Planning and Computing (ICSIDEMPC), 2020: 216-218.

[19]      P. Ghosh, S. Sen.Materialized view replacement using Markov's analysis[J]. 2014 IEEE International Conference on Industrial Technology (ICIT), 2014: 771-775.

[20]      A. Kamel, T. Ezzedine.Dynamic selection of indexes and views materialize with algorithm Knapsack[C]//2019 International Conference on Internet of Things, Embedded Systems and Communications (IINTEC), 2019: 214-219.

[21]      Khan R A , Quadri S.Business Intelligence: An Integrated Approach[J]. Business Intelligence Journal, 2012, 5(1): 21.

[22]      Thusoo A , Sarma J S , Jain N, et al. Hive - a petabyte scale data warehouse using Hadoop[C]// Proceedings of the 26th International Conference on Data Engineering, ICDE 2010, March 1-6, 2010, Long Beach, California, USA. IEEE, 2010.

[23]      王雪松.ETL数据整合与处理(Kettle)[M].北京:人民邮电出版社.2021.

[24]      李闻一，张洪梅，李栗，等.基于业财税融的管理会计报告研究[J].2021(2020-l):21-29.

[25]      冯雷.Greenplum从大数据战略到实现[M].北京:机械工业出版社.2019.

[26]      Performance comparison of analytical DBMS， https://clickhouse.yandex/benchmark.html.

[27]       K. Zhang， M. Sadoghi， H. -A. Jacobsen. DL-Store: A Distributed Hybrid OLTP and OLAP Data Processing Engine[J].2016 IEEE 36th International Conference on Distributed Computing Systems (ICDCS)， 2016: 769-770.

[28]       W. Zhenyuan， H. Haiyan.OLAP Technology and Its Business Application[J]. 2010 Second WRI Global Congress on Intelligent Systems， 2010: 92-95.

[29]       A. Kewo. OLAP best solution For multidimensional grocery business model[C]//2013 International Conference of Information and Communication Technology (ICoICT)，2013: 286-291.

[30]       C. Ordonez， Z. Chen， A. Cuzzocrea， J. Garcia-Garcia.An Intelligent Visual Big Data Analytics Framework for Supporting Interactive Exploration and Visualization of Big OLAP Cubes[C]//2020 24th International Conference Information Visualisation (IV)， 2020: 421-427.

[31]      周亮，王震.远程过程调用技术在分布式软件系统中的应用[J].航空电子技术,2020.

[32]       P. Mironela. The Importance of Web Services Using the RPC and REST Architecture[C]// 2009 International Conference on Computer Technology and Development， 2009: 377-379.

[33]       Joshua B. Humphries， Robert Ross， David Muto， et al. Practical gRPC[M]. Packt Publishing， 2019.

[34]      赵瑜颢.基于Grpc的分布式远程过程调用框架设计与开发[J].现代信息科技,2021,5(04):8&

[35]      杨开振,周吉文，等.JavaEE互联网轻量级框架整合开发[M].电子工业出版社,2017.

[36]      ClickHouse-fast open-source OLAP DBMS[DB/OL].http://www.ClickHouse.com.

[37]       Imasheva, B. The Practice of Moving to Big Data on the Case of the NoSQL Database[J], ClickHouse.2020.

[38]       D. Lugovoy, E. Pobedinskaya. Advertising in popular instant messengers[J]. 2018 IEEE Communication Strategies in Digital Society Workshop (ComSDS), 2018: 39-41.

[39]     朱凯.ClickHouse原理解析与应用实践[M].北京:机械工业出版社,2020.

[40]     S. Xia, H. Jiang, Y. Zhang, D. Peng. Internet Advertising Investment Analysis Based on Beijing and Jinhua Signaling Data[C]//2019 IEEE International Conference on Computational Science and Engineering (CSE) and IEEE International Conference on Embedded and Ubiquitous Computing (EUC), 2019: 419-426.

[41]      Zachary Tong,Clinton Gormley.Elasticsearch: The Definitive Guide[DB/OL]. https://www. elastic.co/guide/en/elasticsearch/guide/master/index.html

[42]     Ferraiolo D, Kuhn R. Role-Based Access Control[C]//In 15th National Computer Security Conference, 1992: 554-565

[43]      Alka, A. Gosain. A Comparative Study of Materialised View Selection in Data Warehouse Environment[C]//2013 5th International Conference and Computational Intelligence and Communication Networks, 2013: 455-459.

[44]      S. U. Khan.Data warehouse enhancement manipulating materialized view hierarchy[J].Eighth International Conference on Digital Information Management (ICDIM 2013), 2013: 369-372.

[45]      Bachtarzi C, Bachtarzi F, Benchikha F. A model-driven approach for materialized views definition over heterogeneous databases[C]// First International Conference on New Technologies of Information and Communication. IEEE, 2016: 1-5.

[46]     王婉人,高优.计算机应用系统性能测试技术及应用研究.信息系统工程, 2017(05): 86.

[47]      黄静,陈秋燕.基于Prometheus + Grafana实现企业园区信息化PaaS平台监控[J].数字通信 世界,2020,189(9): 70-72.

[48]     肖睿,许红涛，等.基于Kappa架构的实时日志分析平台研究与实践[J].中国金融电脑,2021.