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

原文：查询时长下降10倍！网易有数 BI 物化视图设计要点与内部实践|数据库|sql|网易_新浪新闻

　　导读众所周知，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.物化视图的智能推荐