查询时长下降10倍！网易有数 BI 物化视图设计要点与内部实践

DataFunTalk 2023-05-14

397

5月16日，亚马逊云科技游戏开发者大会将如期而至！30+专家携手行业先行者，一起探索【大模型、用户增长、数据治理、数据分析】等话题，还有数据架构、出海数据安全合规等热门话题。扫码快速报名👇

导读众所周知，BI 工具因其灵活的使用方式和便捷的结果展示，已成为生产运营和辅助管理决策的重要工具。网易有数 BI 在用户实际使用中，发现多表复杂关联、无效扫描全表数据等许多拖慢性能的现象。为提升用户体验，加速查询性能，有数 BI 设计开发了物化视图产品功能体系，取得了非常好的效果。

今天的介绍会围绕下面六点展开：

1. 有数 BI 介绍与性能痛点

2. 数据库物化视图的基本原理

3. 有数 BI 物化视图产品设计

4. 有数 BI 物化视图内部案例

5. 有数 BI 物化视图未来展望

分享嘉宾｜胡凡网易资深服务端开发工程师

编辑整理｜李铭多点dmall

出品社区｜DataFun

有数 BI 介绍与性能痛点

首先给大家介绍下有数 BI。

1. 有数 BI 介绍

有数 BI 最大的特点是使用 PPT 制作的方式来制作报表。平台的使用方式为：

① 首先准备一个数据源，可以是 excel，也可以 MySQL、Oracle 等，当然 Hive 和 Impala 等很多其他数据源也是可以的。

② 制作数据模型。可以通过多张底层数据表，或者是自定义 SQL 将它们关联在一起就形成了数据模型。

③ 在数据模型之上可以构建数据应用。例如数据报表、数据大屏、数据门户等。

2. BI 性能痛点

在用户使用过程中，有数 BI 发现了一些性能痛点：

（1）痛点 1-多表关联查询慢

因为数据模型是由多表关联构成的，复杂的关联会带来巨大的性能开销，导致报告查询迟迟无法响应。

（2）痛点 2-只查部分却扫全部

这里基本可分为两类场景。

场景一：行的数据。常常出现的场景是仅需要近期的数据计算，但是因为模型是多表关联查询的，每次落库查询都会导致全表扫描。

场景二：列的数据。很多时候报表展示仅需要部分列信息，但是因为模型的关联查询，导致需要全字段关联落库，也会带来很大的性能损耗。

（3）痛点 3-筛选需求因人而异

有些报表会提供多个维度的查询筛选器，筛选器的默认值无法满足需求，不同用户的查询条件不同，导致预加载缓存无法命中，使得落库查询性能降低。

数据库物化视图基本原理

基于以上痛点，我们开始尝试通过物化视图的方式解决问题。

1. 什么是物化视图

物化视图的概念来自数据库，例如 Oracle、Doris 就有这样的概念（MySQL 没有）。它的本质是通过预计算保存 SQL 查询的结果数据，相较于普通视图仅仅是一段静态的 SQL 文本，预计算的数据一旦命中能更好的加速执行性能。

常见分类：

① 关联表数量：单表物化视图、多表物化视图。

② 是否聚合：明细物化视图、聚合物化视图。

③ 更新策略：全量更新、增量更新。

接下来给出一个简单的物化视图的例子。

2. 物化视图示例

上图中可以看到：

① V1 是我们创建的物化视图，数据来源为 T1 JOIN T2，时间跨度从 T1 日期的 2022-07-01 到 2022-07-31，视图包含三个结果字段“地区”、“类别”、“利润”。

② 用户查询了一个 SQL，我们可以看到，查询的数据来源也是 T1 JOIN T2，时间跨度从 2022-07-10 到 2022-07-20，所查询的字段也在 V1 范围内。

③ 因此我们可以将上述用户的 SQL 改写为直接从 V1 物化视图读取数据，并使用用户 SQL 的过滤条件，形成了物化视图改写后的 SQL。

3. 数据库物化视图基本架构

上图是一般情况下数据库的物化视图基本架构。

图左边是用户通过物化视图的 DDL 进行视图的创建、管理和更新。当然数据库本身也会进行视图数据的同步和元数据管理。数据同步的流向就是从原始表中抽取同步到物化视图中。

图右边是用户开始查询 SQL，该 SQL 进入到数据库后，通过命中校验模块判别是否存在匹配的物化视图，如果存在则通过改写模块对 SQL 进行改写。

4. BI 场景下直接使用数据库物化视图的问题

在 BI 场景下直接按照数据库的逻辑使用物化视图是会存在一些问题的。

① 以 SQL 为粒度，无法与 BI 模型建立直接绑定，也存在构建的人工沟通成本。

② 无法利用 BI 的模型、报告、图表的整体信息及查询信息创建更好的物化视图。

③ BI 产品支持很多种类的数据库，它们的物化视图特点不同，无法统一支持，需要分别适配和管理。甚至有的数据库不支持物化视图。

④ 无法跨数据库种类进行关联后再创建物化视图。

⑤ 通常缺乏物化视图管理 UI 界面，只能通过 DDL 管理。

因此我们考虑是否可以在 BI 层做物化视图，该物化视图以模型为粒度，可以充分利用模型的信息，可以屏蔽底层的数据源类型，我们也可以为这个功能提供 UI 界面方便管理。基于这个想法，我们设计了自己的物化视图。

有数 BI 物化视图产品设计

接下来先来看一下我们的产品设计。

1. 物化视图配置入口

上面提到，有数 BI 的物化视图是以模型为单位的，所以在模型的右上角可以点击物化视图的选项，之后就会进入到物化视图的配置页面。从页面中可以看出，一个模型支持创建多个物化视图，我们也提供了一个统一开关可以控制物化视图的开启和关闭。在创建物化视图按钮或者点击某个物化视图的编辑按钮后，就跳转到了物化视图的配置页面。

2. 物化视图配置页面

从页面上我们可以看到：

① 对于物化视图，系统默认会物化全部明细数据。

② 用户可以根据需求选择物化部分字段。

③ 系统支持物化聚合数据。

④ 系统支持对物化字段进行数据筛选。

3. 物化视图执行计划页面

在这个页面中：

① 支持配置建表方式和物化引擎。

② 支持配置执行计划。

③ 支持配置依赖执行的物化规则。

④ 支持配置定时计划。

4. 物化视图管理页面

在这个页面中：

① 提供各物化视图的模型信息、状态、调度时间、占用空间、成功率、创建人等基本信息。

② 提供立即执行物化视图任务的功能。

③ 提供物化视图的历史物化记录。

④ 提供任务的告警设置。

⑤ 提供物化状态的日报定时推送功能。

有数 BI 物化视图实现原理

1. 有数 BI 物化视图架构

整个架构从左至右共分为三块结构。

最左边是数据源，有数 BI 支持 Excel、MySQL、Spark 等数据源，物化视图即是将它们内部的数据在关联和其他操作后写入到 MPP 中。

中间的是数据模型。通过左边的数据源关联构建数据模型。物化视图的物化配置也在这个结构中，包括维度、度量、聚合信息、筛选范围等配置项。这些配置项再加上模型配置，可以转换为有数 BI 的 ETL 任务配置，以进行数据同步和实际的数据物化。通过对该 ETL 任务配置进行元信息推导，可以生成 ETL 元信息，该 ETL 元信息将用于右侧数据查询过程的物化视图改写模块，以进行数据查询的物化视图改写。

右边的是数据应用，通过数据模型构建而来，包括可视化报告、自助取数、数据大屏等。这些应用中的查询会生成查询 DSL 给数据物化查询改写模块，结合上述中间结构中传递来的物化视图配置和 ETL 元信息，进行物化查询的改写和调整，最终生成改写后的物化 SQL，然后将物化 SQL 发送给 MPP 进行数据查询。MPP 中的数据就是上述生成的 ETL 任务从数据源中抽取写入的。

接下来详细阐述这些结构和流程。

2. 物化视图 ETL 生成

首先，将物化视图的配置和模型的配置转换为 ETL 配置，这里会用到 ETL 的输入节点、关联节点、清洗节点、聚合节点、输出节点。之后进行 ETL 元信息的推导，生成视图表信息、字段信息、字段映射关系和聚合信息。

上图的下半部分给出了一个例子。

例子中有一个具体的模型，从模型配置上看，它是由三张表进行关联的。对于物化配置，指定了 4 个维度和 3 个度量；还有一个筛选范围，选的是发货日期的前 7 天。接下来，根据上述两个配置可以转化为 ETL 的配置。

在 ETL 的配置中首先有关联节点，将三张表关联起来。接下来是一个清洗节点，用于数据筛选，例子中物化配置中的发货日期的前 7 天的筛选条件就会置于清洗节点中。接下来是聚合节点，配置了物化配置中的维度和度量。最后是一个内部输出节点，表示数据将输出到内部的 MPP 中。

接下来会从 ETL 配置自动推导出元信息，图中示例主要展示了字段的信息。

3. 物化视图查询流程

数据应用查询的时候会生成查询 DSL，包括了维度、度量、筛选器、排序等一些配置。DSL 解析之后会生成一组查询 AST。这些 AST 将依据物化配置和 ETL 元信息，通过优先级排序、命中校验等步骤，最终转化为物化 AST。之后经过 SQL 生成和优化的环节，最终到达 MPP 引擎进行数据查询。

通过这个流程可以看到，在 BI 层做物化视图相对数据库而言是存在一些优势的：

① 基于结构化的查询 AST，相比于通过解析 SQL 更可控。

② 以模型为粒度，减少校验成本，传统的查询引擎会需要匹配所有的物化视图，效率降低。

③ 任意数据库类型可用，屏蔽了底层数据库类型，解决了部分数据库不支持物化视图的问题。

④ 支持跨数据库种类关联，不需要限制单数据库物化。

4. 物化视图优先级排序

同一个模型可能会匹配到多个物化视图，那么通过优先级排序选择更合适的视图就显得尤为重要了。涉及物化视图优先级排序的原则如下：

① 预聚合：优先使用预聚合的物化视图。

② 聚合维度数量：优先使用聚合维度个数更少的物化视图。

③ 谓词数量：优先使用谓词个数更多的物化视图。

④ 谓词范围：优先使用谓词范围更小的物化视图。

5. 物化视图命中校验

在上图中我们可以看到，通过查询语法树的配置和物化视图的配置，可以进行物化视图的命中校验。校验大体分为三个阶段：字段校验、谓词校验和关联校验。

（1）字段校验：主要校验目标为查询字段是否存在于物化视图

① 第一点需要考虑查询和物化视图的聚合状态分类：明细查询显然无法使用聚合类型的物化视图；

② 第二点需要考虑模型表字段与子查询字段：模型表的字段是否均在物化视图中，但如果是子查询，因为是内部生成的，就可以跳过这层考虑；

③ 第三点需要考虑二次聚合的等价性：查询较复杂时，可能需要在已经聚合了一次的物化视图基础上，在查询中进行第二次聚合；但是很明显，这种二次聚合是需要考虑等价性的，例如不能通过对物化视图中的平均值聚合结果进行第二次平均值聚合，来得到整体的平均值，因为这从数学逻辑上是不等价的。

（2）谓词校验：查询谓词是否包含于物化视图

① 首先以物化视图的筛选谓词为基准：也就是说物化视图中筛选的谓词都要在查询中找到同样的筛选谓词且查询范围相同或更小；

② 相同种类的谓词存在包含关系；

③ 等价谓词优化：例如物化视图中是日期范围筛选，但是查询中是列表筛选，这时可以判断列表中的筛选项是否都在日期筛选范围中。

（3）关联校验：确认表关联是否能满足等价要求

① 同样需要考虑查询和物化视图的聚合状态分类；

② 考虑模型表和子查询：当查询中是关联表和子查询关联时，直接将关联表替换为物化视图可能存在不等价的情况；

③ 子查询递归校验：当查询中包含子查询时，需要递归判断每一层的子查询全部命中才能确认可以替换为物化视图

经历三个阶段校验后，可以得到校验结果，其中包括是否命中，命中视图的信息以及未命中的原因。

6. 物化查询改写

接下来到了物化查询改写的阶段，这个阶段中使用到 ETL 元信息和查询语法树。这个阶段主要的操作为三个方面：模型替换，字段替换，等价优化。

（1）模型替换：基础数据模型替换为物化模型表

① 第一点需要考虑关联表和子查询；

② 第二点需要考虑子查询递归替换；

（2）字段替换：原模型字段替换为物化字段

① 需要考虑查询和物化视图的聚合状态分类；

② 考虑维度和度量二者处理逻辑不同；

③ 考虑二次聚合的等价性。

（3）等价优化：对等价场景进行优化

① 当物化聚合粒度等于查询聚合粒度时，显然不需要在查询时再次做 group by 的聚合操作，此时可以将聚合查询退化为明细查询；

② 当物化筛选谓词等于查询的筛选谓词时，在查询时无需继续使用该筛选谓词，可以直接抵消；

经历这个阶段后，就会生成物化语法树，下一步生成对应的 SQL，正式进入 MPP 进行查询。

7. 调度与抽取管理

如上图所示，这里其实复用了有数 BI 的数据准备即 ETL 的调度体系。首先我们支持许多种类的数据源，接下来这些数据源经过数据清洗创建了数据 ETL 的配置，再通过 ETL 的配置生成抽取的 SQL，最终通过抽取 SQL 将数据抽取到 MPP 数仓中。因为 ClickHouse 对于常见的物化视图形成的大宽表有非常好的性能体现，所以我们现在更多的使用 ClickHouse 作为 MPP 数仓的引擎。

接下来详细表述下“数据抽取”这个阶段：

① 支持通过 Spark 引擎进行高性能传输。

② 支持错误的监控报警。

③ 支持多样的调度方式：手动调度、依赖调度、定时调度等。

④ 支持丰富的抽取方式：例如全量物化、增量物化等。

⑤ 支持执行计划配置：可以对依赖表进行表信息配置和定时任务配置。

⑥ 容量管理：对于整个 MPP 的容量管理，或者物化视图自己的容量管理。

⑦ 状态管理：可以看到当前物化视图的物化状态、以及开启关闭状态等。

⑧ 调度历史：历史调度信息、成功率、物化时长等。

8. 物化视图的智能推荐

在模型和报表中使用的字段很多，光靠用户手动进行物化视图配置效率较低。通过模型信息和物化历史信息，我们会给出一些物化配置的推荐。现在这个模块还在不断推进开发中。

有数 BI 物化视图内部案例

在网易严选这个场景中，存在许多需要高性能查询的重点报告，为此我们根据这些重点报告，构建出了 60+ 个物化视图。在实际使用中发现，配置了物化视图的这些重点报告中，每天有超过 90% 的 SQL 查询能命中物化视图，在命中了物化视图的查询组件中，平均查询时长从 5000ms 下降到 500ms。

有数 BI 物化视图未来展望

对于有数 BI 物化视图的未来展望，主要包括以下四点：

① 更强大的物化视图配置能力：例如将物化字段扩大到任意计算字段，让物化视图更接近于实际的查询。

② 更精准的物化视图命中校验：包括范围更广的谓词校验，更强的等价命中校验能力等。

③ 更智能的物化视图推荐配置：根据模型的使用情况一键物化，根据物化视图历史命中情况优化配置。

④ 更飞速的物化视图查询体验：更快的物化抽取速度，更快的 MPP 引擎查询速度。

今天的分享就到这里，谢谢大家。

分享嘉宾

INTRODUCTION

胡凡

网易

资深服务端开发工程师

2017年硕士毕业于浙江大学计算机专业，而后加入网易，从事有数BI的智能可视化与数据查询优化引擎的研发工作，在大数据BI领域具有较为丰富的开发和实践经验。

▌2023亚马逊云科技游戏开发者大会

部分精彩议题预告：

🔥Stable Diffusion在游戏资产管线上的应用

🙍分享嘉宾：Gary Guo 完美世界技术美术总监

👥【同类型议题推荐】

●AIGC在游戏行业的应用

●如何利用AI创作平台实现降本增效

●机器学习在文字及图片内容审核中的应用

🔥游戏行业用户增长之道

🙍分享嘉宾：程筱净汇量科技

👥【同类型议题推荐】

●数据分析在游戏行业中的典型场景与架构

●大模型在用户行为分析中的应用

👇更多议题，点击“阅读原文”查看

物化视图 bi工具

文章转载自DataFunTalk，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

查询时长下降10倍！网易有数 BI 物化视图设计要点与内部实践

评论