物化视图设计和实践

1. 背景

本产品每天 80% 以上的查询都可以通过命中图表缓存的方式进行优化，这部分查询主要包括图表默认的查询条件和一些规律筛选分析行为。但是对于一些不规律的灵活的筛选分析行为就很难通过缓存的方式来优化查询性能，这部分查询行为有以下问题：

• 1）大部分只查询“部分行”的数据，比如最近 30 天的数据或者地区=“东北”的数据等，每次却都需要进行全表扫描。
• 2）大部分只查询“部分列”的数据，比如一个模型里有 200 个字段，某个图表上却只用到其中 3 个字段。
• 3）大部分模型存在复杂的关联关系，每条 SQL 查询都有多表 join 的计算性能消耗。

因此我们在 BI 产品上增加物化视图功能，主要目标也是为了解决上面这三个问题，从而进一步提高图表的查询性能。

2. 产品功能

2.1 基于模型的物化

我们的物化视图基于模型的物化，不区分数据源，从而可以在 BI 上支持不同查询引擎的物化，一个模型可以根据查询优化需要，创建多个物化视图。

• 创建入口：
  

• 支持多个物化视图：
  

2.2 物化视图配置

如上图所示，其实物化视图的配置主要包括两个部分：列筛选和行筛选，这个也主要是为了解决我上文背景里提到的大部分查询只查看“部分行”和“部分列”的问题，只需要把图表查询经常用到的数据给物化下来，保证这部分数据的高性能查询。

（1）列筛选：

• 手动指定筛选列

点击添加指定字段，会出现如下弹窗，用户可以根据自己的需要选择模型里的部分维度和度量添加到物化字段里。

• 快速生成物化视图

因为大部分物化视图的使用场景是针对某几个报告进行优化，但是报告里图表太多，很难准确的判断出到底要物化哪些字段，或者要消耗大量的时间，因此我们提供了“快速生成物化视图”这个功能，点击该按钮，出现如下弹窗：

该弹窗里会列出所有使用到当前模型的报告，用户可以根据自己优化目标选择需要的报告（可以选择多个），然后程序会自动计算出来所选择的报告都依赖了当前模型上哪些字段，自动添加到配置面板。

• 生成动态物化视图

考虑到报告依赖的字段经常发生变化，比如昨天报告里某个图表展示的不同地区的销售额，于是在昨天做了一个物化视图列筛选里包含了两个字段“地区”、“销售额”，但是今天该图表被修改为展示不同省份的销售额后，因为旧的物化视图里没有包含”省份“字段会导致无法命中物化视图，所以编辑者还得去手动修改物化视图，非常麻烦。为此我们提供了"动态物化视图"的功能。

如果其中某个物化视图只是为了其中某几个报告而创建的，那么我们可以通过生成动态物化视图，选择需要绑定的报告。在物化时，每次选择的列字段会根据物化的需要动态生成，不需要用户自己设置，配置界面和“快速生成物化视图”类似。

（2）行筛选：

因为 90% 以上的查询可能只是访问一些热点的数据，比如最近一周、最近一个月等，如果把全量的数据物化，对应的物化视图的数据量也会很大，优化效果不会达到极致。因此我们可以在物化配置时，通过“添加筛选”的功能将需要的数据行物化下来，如果图表查询的数据在该筛选范围内的话就会命中物化视图，如下图：

（3）聚合物化：

通过“聚合度量”开关我们可以选择到底物化的是明细数据、还是聚合后的数据：

这里举个例子来讲下明细物化和聚合物化的区别，假如原始表里有如下几行数据：

我们要选择地区、销售额两个字段进行物化，如果没有勾选“聚合物化”这个开关，那么最后物化后的数据如下：

如果勾选了“聚合物化”这个开关，这里我们销售额按照“SUM”的聚合方式进行计算，那么最后物化后的数据如下：

通过上面数据对比可以看到，开启聚合功能后，数据行数会少很多，对应的查询性能也会高很多。但是开启聚合后也有它的局限性，比如想查询每个地区的销售额的平均值，就没法命中开启聚合的这个物化视图。“聚合物化”一般建议针对单个图表进行优化，比如筛选器列表查询等。

2.3 抽取调度配置

物化视图背后的逻辑还是将需要物化的数据作为单表抽取到我们的 MPP 数据库里，所以我们需要进行抽取调度的配置，这里的抽取调度配置和”表抽取“和 ”ETL 抽取“调度配置类似，如下图，我这里简单介绍下：

（1）触发方式：

抽取的触发有两种方式，一种是定时抽取、一种是依赖执行，两种方式也可以同时并存。

定时抽取：根据设置的定时频率和开始时间来触发任务的执行 依赖触发： 因为模型依赖的原始表的数据产出的时间不一定，通过定时任务触发实时性不够，而且可能出现在数据还没准备好就执行抽取，所以提供了依赖触发的功能，用户可以在表数据产出以后通过接口的方式告知有数，然后有数实时触发抽取。这里因为一个模型可能有多张表，到底是其中一张表产出后物化还是所有表产出才物化，所以我们提供提供了物化规则配置，选择权交给用户。 如果客户购买了猛犸平台，那么这里可以和猛犸平台进行打通，自动实现依赖产出功能。

（2）失败重跑：

因为抽取任务可能因为网络、数据库异常等出现失败，可以通过自动重跑配置来提高抽取的成功率。

2.4 抽取任务管理

物化视图配置好以后，我们可以在“项目中心 -> 资源管理 -> 数据任务 -> 物化视图” 这个路径下找到物化视图的相关任务，查看这些任务的执行情况，比如我们可以看到某个物化任务的历史执行情况：

这里的功能还包括：容量管理、任务的禁用、错误日志排查等功能，和有数 BI 之前的抽取功能基本类似，这里就不做详细的介绍。

3. 技术实现原理和优势

3.1 实现原理

物化视图整体技术实现架构图如上，主要分为以下几个部分：

（1）物化 ETL 转换：

模型物化的过程其实就是按照我们配置的行筛选、列筛选、聚合等逻辑进行数据的清洗和计算，然后抽取到我们的 MPP 引擎中，这个过程其实就是一个 ETL 的过程。正好可以借助我们已有的“数据准备”这个技术能力，将物化配置转换成一个 ETL 任务。那么后续的调度和任务执行等功能都可以实现能力的复用。

（2）物化元信息生成：

一个多表关联的模型最终被物化成 MPP 里的一张单表，我们需要记录原始模型和 MPP 单表的一些元信息，用于后续的物化改写，比如物化后的表名、字段名、字段类型等，以及原始模型和 MPP 单表的字段的映射关系等；还有物化的逻辑是什么？做了哪些列筛选、哪些行筛选、是否聚合等。这些所有的信息都会被保存下来用于后续的物化改写。

（3）物化的查询改写：

图表在数据查询的过程中，我们会将当前图表依赖的所有物化视图的元信息都带到数据查询引擎中，数据查询引擎会判断当前查询可以命中哪几个物化视图，如果没有命中物化视图那么就走原先的查询逻辑，如果可以命中多个物化视图的话，我们会根据查询的最小代价选择最优的物化视图进行查询，根据物化视图的元信息进行查询 SQL 的改写，改写完以后重新组装数据返回回来，对上层基本透明。

（4）任务的抽取和调度：

因为第一步将物化视图转换成 ETL 任务以后，这里的任务抽取和调度完全复用我们已有的能力。

3.2 优势和特点

（1）支持不同的查询引擎

传统数据库的物化视图只能针对特定的数据库单独开发物化逻辑，而有数 BI 的物化视图是基于逻辑模型的物化，可以支持不同数据源模型的物化，甚至可以做到同一个物化视图跨查询引擎进行物化，完全突破数据源的限制。

（2）高性能的单表 MPP 查询引擎（CK）

目前产品已经集成了 ClickHouse 作为我们主要的 MPP 查询引擎，所有命中物化视图的查询都只会查询 MPP 中的一张表，正好可以借助 ClickHouse 单表查询的优势，将图表查询性能发挥到极致。

（3）实现成本低

和传统的物化视图实现原理不一样，我们是基于查询 DSL 的改写，也就是在生成 SQL 之前就可以完成 DSL 的改写，不需要区分数据源的差别，行筛选、列筛选、是否聚合这些功能都是我们之前图表上已有的概念，所以改写起来较为容易。

（4）更好的易用性和扩展性

首先我们可以在产品界面进行物化视图的创建和管理，无需代码开发能力。其次我们后续可以根据用户图表的查询信息做更多的优化和扩展，比如根据用户的查询历史日志自动推荐物化视图配置、或者根据用户的查询行为自动进行物化视图的增删改，甚至都不需要用户介入等。

4. 最佳实践

4.1 不妨先用全量明细数据试下效果

物化全量明细数据是最简单的物化视图配置，不需要做任何配置，只需要点击下确定按钮即可，这种情况下所有基于当前模型的图表查询都可以命中该物化视图。如果抽取的数据量不算特别大（比如小于 5 G），抽取耗时（小于半小时）还可以接受，图表的查询性能还较为突出（5秒以内），那可以直接全量明细物化就可以了。

你要相信全量明细物化的性能也会有很大的提升，主要有两个原因：1）每次查询不需要多表的 join 计算逻辑。2）基于 CK 优异的单表查询 MPP 性能。

4.2 字段选择策略

如果在全量明细数据无法满足优化需求的情况下，我可以选择部分字段进行物化，那么这里的字段选择其实也有一些讲究：

（1）都要选择哪些字段才能命中物化视图

这里的字段选择不能只考虑图表的行或者列上拖了哪些字段，要把当前图表或者报告的 SQL 里用到的所有字段都考虑进去，包括图表的筛选器字段、筛选器控件上的字段、报告和页面筛选器字段、图表行列上的字段、行列权限中用的字段等，缺少一个字段都无法命中物化视图，所以很多情况下没有命中物化视图很有可能是字段没有选择完全。

（2）尽量避免物化大的 JSON 或者字符串字段

很多模型的原始数据里可能包含一些大的 JSON 字段，这些大字段一般不会用于制作图表，但是如果将其物化下来会占用大量的 MPP 空间，并且在抽取过程中会消耗大量的计算资源和时间，所以可以首先把这些大的 JSON 字段排除掉。

4.3 热点数据选择的重要性

虽然整个模型宽表有很多行数据，但是大部分图表查询可能只落在某个查询行范围内的，尤其是大部分 BI 报表都有日期筛选器，比如整个模型宽表有 5 年的历史数据，一共 10 亿条数据，但是 98% 的图表查询都查最近半年的数据，那么我们就可以在物化视图配置时筛选范围选择最近半年，这样可以保证 98% 的查询只需要扫描 5000 万的 MPP 单表数据，性能自然会提高。

关于热点数据如何选择，目前没有现成的产品功能来推荐（后续产品上会考虑支持），一般可以根据经验来配置一个较小的时间范围，这个时间范围最起码要覆盖日期筛选器控件的默认时间范围，然后根据物化的命中率尝试调整这个时间范围，最终找到一个较为合理的范围，既可以兼顾抽取和查询的性能也可以保证的物化视图的命中率。

4.4 预览者无法修改的筛选范围物化

在有数报告中存在一些筛选器，在预览的情况下是无法修改取值的，主要有以下几种情况：

• 图表上配置的筛选器：

• 报告或者页面筛选器：

• 预览视野外的筛选器控件：

针对上面这三类筛选器，因为预览用户无法修改筛选器，最终查询的数据一定被限定在编辑者配置的范围内，那么这种情况下我们就可以直接用户在报告里配置的筛选范围进行物化。

4.5 什么情况下使用聚合？

前文里也提过，其实聚合的使用成本还是很高的，不同图表的聚合粒度不一样、聚合方式也不一样，一般每个报告里有很多图表，那么如果使用聚合的话，一个报告可能就要建很多物化视图，维护成本是非常高的。所以建议如果能使用明细物化解决性能问题的可以尽量使用明细物化，下面一些情况可以选择聚合物化：

（1）需要全表扫描的列表成员筛选器：

因为列表筛选器需要去重获取维度成员列表，性能相对较差，并且如果这些列表筛选器没有设置任何其他筛选条件，需要进行全表扫描查询才可以获取到所有的成员列表，这个时候我们可以单独为该列表筛选器配置一个物化视图，将去重后的成员物化下来，性能会有显著提升。

（2）特殊的慢图表：

报告里同一个模型下有某个图表查询特别慢，即使配置了基于明细的物化视图该图表查询依然很慢，那么有可能该图表的计算逻辑非常复杂，那么我们可以考虑按照该图表的聚合粒度和聚合方式单独为该图表配置一个独立的物化视图，将图表的计算逻辑直接在物化时完成。

4.6 多个物化视图尽量不要存在数据的包含关系

同一个模型我们可以创建一个最近7天的物化视图1，同时还可以创建一个最近30天的物化视图2，显然视图2的数据是包含视图1的数据的。如果查最近7天内的数据既可以命中视图1，也可以命中视图2，我们会优先选择视图1进行优化，但是如果使用视图2优化性能上差距不是很大的话，就没必要创建视图1了，物化视图创建太多会带来维护的成本，并且还是消耗更多的 MPP 资源和抽取资源。

除非两个视图的性能查询差距非常大，这样即使有数据包含关系也可以创建多个物化视图的。

4.7 物化目标选择策略

这里物化目标是指同一个物化视图到底为了优化哪些查询，一般是根据用户的需求，发现哪里有性能问题去针对优化，这里建议可以从下面几个维度进行考虑：

• 优先看下全量明细的物化是否可以解决问题
• 其次也可以优先物化重点报告，看下重点报告查询是不是有一些共性，保证核心报告的性能
• 再次就是可以考虑将“热点数据范围”比较相近或者存在包含关系的报告优化成一个物化视图
• 最后实在不行可以针对一些慢图表进行单独的物化优化

4.8 其它

物化视图的命名要规范，最好从名字上就可以区分这个物化视图是干什么的。 尽量使用依赖产出触发抽取，提高物化数据的实时性。 优先使用动态物化视图，减少维护成本。

5. 问题的排查和定位

最后讲下关于物化视图问题的排查，这个也是技术支持和很多客户比较关心的问题，这里我针对一些常见的问题以“问题/答案”的方式进行讲述：

问题一：如何判断当前图表查询是否命中物化视图？

方式一：在报告编辑状态下查看 SQL

点击图表右上角的更多选项查看 SQL，如果发现 SQL 中只查询了单表，或者表名很奇怪带了 “etl_” 前缀，那么一定是走了物化视图。

方式二：查看 websocket 接口返回数据

可以看下返回的数据结果里 ".result.extra.materialized" 字段，如果等于 1，说明命中了物化视图。

方式三：查看 backend 日志

如果发现 backend 日志中用的数据库是 mpp 数据库连接信息，那么一定是走了物化视图。

问题二：如果没有命中物化视图如何排查定位？

可以在 backend 日志里查看 DC 模块的返回结果进行排查，这里列举几种没有命中的情况分别说明：

• 字段不存在的情况（fieldFail）

  //这个表示查询的"类别"这个字段在物化视图里不存在
  {
  "type": "fieldFail",
  "failExpr": "Field(1,类别,String,ExprMeta(None,None))",
  "expect": [
    [
      "Field(1,类别,String,ExprMeta(None,None))"
    ]
  ]
  }

• 筛选范围不匹配（aggregateFail）

  // 这个表示物化视图配置的 ds 字段的筛选范围 >= 2022-01-01 00:00:00
  // 但是图表的筛选范围是 >= 2021-05-23 00:00:00
  // 无法精确满足条件匹配，所以不能命中物化视图
  {
  "type": "filterPredicateFail",
  "failExpr": "Date(Field(1,ds,String,ExprMeta(None,None)),ExprMeta(None,None))",
  "viewPredicate": "EComparePredicate(>=,LitStringV(2022-01-01 00:00:00))",
  "queryPredicate": "EComparePredicate(>=,LitStringV(2021-05-23 00:00:00))"
  }

• 筛选器不存在的情况（filterExprNotMatchFail）

  // 这个表示在物化视图里配置了一个订单日期的筛选器，但是图表查询上没有该筛选条件
  // 也就是图表查询的数据范围比物化视图的范围大，自然命不中物化视图
  // 这里如果筛选器字段类型转换的方式不一样也无法匹配
  {
    "type": "filterExprNotMatchFail",
    "failExpr": "Date(Field(1,订单日期（YY-MM-DD）,String,ExprMeta(None,None)),ExprMeta(None,None))"
  }

• 聚合和明细不匹配（aggregateFail）

  //如果物化视图是聚合的，但是查询的数据是明细，比如二维表的非聚合查询，那么这种情况下无法命中物化
  {
  "type": "aggregateFail",
  "viewAgg": true,
  "queryAgg": false
  }

问题三：每个物化视图的命中率如何，如何整体优化？

关于物化视图的命中率等性能指标，目前没有专门的内置系统报告进行展示，但是技术支持可以通过资源的导入导出方式将我们做好的报告导入到客户系统里进行分析，有需要的客户可以联系我们，最后的报告展示部分截图如下：

通过这个报告可以查看如下的一些指标：

• 整体的物化视图命中率
• 每个物化视图的命中率
• 每次查询的是否命中物化视图、日志地址等
• 物化视图抽取任务的成功率

6. 未来的规划

更智能的物化配置优化

• 可以自动根据历史的访问记录找到慢查询图表，自动创建物化视图
• 根据历史访问的情况自动更新热点数据，保证较高的物化命中率和查询性能

内置系统报告

• 将物化视图的统计视图做成系统报告，方便用户查看物化性能指标，排查和定位性能问题
• 物化收益的报告，通过查询性能和物化命中率帮忙用户来进行更方便的物化视图管理

物化视图性能诊断

• 将物化视图的问题排查融入到现有的性能针对体系中
• 通过性能诊断可以快速排查是否命中物化，为什么没有命中物化
• 通过性能诊断可以快速帮助用户创建物化视图

本文作者：张佃鹏