基于查询历史自动构建物化视图 - 云原生数据仓库AnalyticDB

在OLAP分析场景中，复杂查询常面临资源消耗高和响应延迟长等问题。AnalyticDB for MySQL支持物化视图功能，用户无需手动开发复杂的ETL作业，即可通过声明式语法在一张表中同时定义数据加工逻辑与调度策略，使用户能够专注于报表的业务逻辑本身。然而，传统物化视图的构建依赖DBA人工分析SQL模式、提取子查询与构建结果表，随后需协同业务团队重新编写SQL以适配新架构，成本高昂。AnalyticDB for MySQL支持通过分析历史查询，能够自动推荐并创建物化视图，实现对业务无感的透明查询加速。

物化视图智能推荐

物化视图智能推荐能力，能够将传统物化视图的构建流程自动化：

智能分析与推荐。自动分析集群近期的SQL查询历史（默认为大于1s的慢查询），根据用户设定的慢查询阈值或每个子查询能够关联的Pattern（SQL文本去常量后的模板化表达式，用来归类总结相似的查询）数量等参数，智能化提炼出可复用的公共子查询。
量化评估收益。观测公共子查询的SQL文本及历史收益，判断是否需要创建为物化视图。
一键创建。通过控制台一键创建物化视图，并启用透明改写。

物化视图创建后，会在后台自动定期刷新。对于定时更新的底层原始数据，可以在物化视图中定义更短的调度间隔，以维持物化视图结果数据和底层基表数据的一致性。

适用场景

在OLAP场景中，自动物化视图（AutoMV）能显著降低复杂查询的资源消耗并提升效率。例如：

固定报表开发：系统自动预计算每日销售数据，业务人员可秒级获取报表结果，无需等待实时计算。
用户行为分析：每小时更新用户特征快照，数据分析师无需手动关联大表，即可快速提取特征。
实时风控预警：通过缓存关键指标，系统可在毫秒级触发交易风险预警，避免高峰时段资源争抢。
多维数据钻取：自动适配时间、地域、品类等多维分析需求，业务人员无需依赖IT团队即可灵活探索数据。

核心优势

节省成本：避免重复扫描全量数据，减少计算资源浪费。
加速响应：查询延迟从分钟级降至秒级，支持实时决策。
简化运维：系统自动维护预计算结果，无需开发复杂的ETL流程。
灵活扩展：业务侧可自主调整分析需求，无需反复申请数据支持。

这种模式让企业既能高效利用现有数据资源，又能专注于业务创新而非底层技术实现。

实现原理

识别与归纳查询模式。

系统将自动解析历史SQL，剔除其中的常量，将其抽象为标准化的查询模板。

例如，以下5个对students的单表查询，过滤条件及投影列各异。

-- sql1/pattern1
SELECT gender, count(*) FROM students WHERE graduated = 'true' GROUP BY gender;

-- sql2/pattern1
SELECT gender, count(*) FROM students WHERE graduated = 'false' GROUP BY gender;

-- sql3/pattern2
SELECT s.age as a, count(s.id) as cnt FROM STUDENTS s WHERE s.gender between 1 and 3;
  
-- sql4/pattern2
SELECT s.age as a, count(s.id) as cnt FROM STUDENTS s WHERE s.gender between 3 and 6;

-- sql5/pattern3
SELECT gender, avg(age) FROM students GROUP BY gender;

如果上述查询，在历史记录中均大于用户预期的慢查询阈值，系统会将其归纳为三个可复用的查询模式（pattern）。

-- pattern1
SELECT gender, count(*) FROM students WHERE graduated = ? GROUP BY gender;

-- pattern2
SELECT s.age as a, count(s.id) as cnt FROM STUDENTS s WHERE s.gender between ? and ?;
  
-- pattern3
SELECT gender, avg(age) FROM students GROUP BY gender;

合并与生成公共子查询。
AnalyticDB for MySQL的优化器会先为每类pattern会生成一个对应的临时物化视图，并判断是否可以合并。最终将基于同一基表的多个模式合并，生成一个公共子查询。将这个子查询物化后，便可以加速原始SQL的性能。
```
SELECT gender, graduated, count(id), avg(age), count(*)
FROM STUDENTS
GROUP BY gender,graduated;
```

版本限制

集群内核版本为3.2.5及以上。

说明

请在云原生数据仓库AnalyticDB MySQL控制台集群信息页面，配置信息区域，查看和升级内核版本。

操作步骤

步骤一：创建子查询推荐任务

创建一个定时任务，该任务会定期扫描历史查询，并分析集群里相似的子查询。

说明

任务创建和运行不消耗集群的ACU资源，也不会产生费用。
建议您手动开启，辅助开发者定期观测集群查询历史的可复用性。

登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表。
单击目标集群ID，在左侧导航栏，单击数据管理 > 物化视图自动推荐。
在任务管理页签下，单击右上角的创建任务。

配置任务参数，单击确定。

参数		说明
任务运行周期		本文示例：每天。
任务启动时间		本文示例：00:00:00。
扫描查询范围(天）		默认每次扫描过去3天的查询历史。
子查询筛选条件说明集群内核版本为3.2.6及以上支持修改此参数。	慢查询阈值	如果一个查询语句的耗时高于这个阈值，才能被这个任务扫描到。一般建议配置为1s，支持按需修改。
	最小可加速的Pattern数量	默认配置为1。
	Pattern匹配的最少慢查询个数	默认配置为5。

说明

如果您不确定如何配置参数，可以创建多个不同配置的任务并行运行，以对比和调试效果（任务运行不占用实际的计算资源）。
目前仅支持从单表查询里构建子查询。WITH CTE里的子查询也会纳入推荐范围，但多表Join部分不参与推荐和统计。

（可选）手动触发任务。任务创建后，您可以等待下一个调度周期自动调度，或手动触发以提前验证和观测效果。
单击目标任务所在行操作列的手动运行。任务的运行比较快，一般在秒级或者分钟级就会完成结果推荐。

步骤二：查看任务推荐结果

任务运行完成后，您可以通过以下方式查看并评估推荐的子查询，以判断其优化价值。

通过控制台查看推荐结果

单击目标任务所在行操作列的详情。

单击相应任务运行历史所在行操作列的查看子查询，可以通过控制台查看本次调度的输出结果。

推荐结果	说明
子查询	系统从查询历史里抽取出来的完整子查询query body。
成功加速的查询数量	本次运行，扫描的若干原始查询中，可以加速的query数量占比。
节约扫描的字节数量	本次运行，如果借助这个子查询进行提前构建物化视图（预计算），可以节约的扫描的字节数量。
关联的基础表	查询命中的base表。

通过SQL搜索推荐结果

为实现更灵活的筛选和分析，您可以通过SQL查看推荐结果。例如，以下SQL可以查询特定任务在某天针对lineitem表相关的子查询。

-- 用户可通过sql查询系统表须版本大于等于326
SELECT statement AS mv_define, can_refresh_fast, hit_queries_cnt, est_saved_scan_bytes
FROM INFORMATION_SCHEMA.MV_RECOMMEND_RESULTS
WHERE 
task_name = 'task_xxxxx'
AND base_tables LIKE '%lineitem%'
AND DATE(create_time) = '2025-09-11';

步骤三：基于子查询快速构建物化视图

单击目标子查询所在行操作列的生成物化视图。
输入物化视图的名称后，即可快速生成一张物化视图的DDL，您只需复制到SQL编辑器或相应客户端执行即可创建。
AnalyticDB for MySQL通过智能化手段以及覆盖大部分场景的通用配置，简化物化视图DDL定义：
- 是否开启查询改写：创建物化视图时默认启用，尽可能加速单表查询。
- 刷新任务执行模式：系统会自动解析SQL语句的类型，并识别是否可以通过增量刷新来运行。如果可以，会默认配置刷新任务执行模式为FAST（增量刷新），刷新过程里吞吐效率最优。
- 资源组：如果物化视图可以增量刷新，此处默认会基于Interactive资源组来执行刷新，刷新性能最优；如果物化视图只能基于全量刷新，则默认会基于名为Job的Serverless资源组执行刷新（仅刷新任务运行期间涉及资源拉起，成本最优）。
- 刷新间隔：如果物化视图可以增量刷新，此处默认5分钟执行一次刷新；如果物化视图只能基于全量刷新，默认2个小时刷新一次。
如果您对物化视图的定义策略有更个性化的配置诉求，比如自定义列名称或者修改更加频繁的刷新间隔，可以在DDL的基础上进行二次修改。

步骤四：观测物化视图的线上收益

物化视图发布上线后，优化器会自动改写部分语句的执行并重定向至物化视图，从而提升query的执行效率，实现透明加速。

物化视图改写能力，存在全局开关MV_QUERY_REWRITE_ENABLED，默认开启，无需手动再次进行全局开启，仅需打开物化视图的表级别开关。

查看隐式命中次数

在集群详情页，左侧导航栏，单击数据管理 > 数据目录。
单击目标数据库。
在物化视图页签下，单击希望查看的物化视图。
查看物化视图最近7天的隐式命中次数，此参数代表了优化器成功改写并路由到这个物化视图的客户端查询次数。

通过SQL执行计划查看加速效果

执行EXPLAIN命令分析原始查询。如果输出结果中的TableScan算子显示为物化视图的名称而非原始表名，则证明查询改写已生效，查询已被成功加速。

示例

EXPLAIN SELECT course_id,course_name,max(course_grade) AS max_grade FROM tb_courses;

-- 可看到输出结果中， 基表变成了adb_mv的名字，代表改写生效
+---------------+
| Plan Summary |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: adb_mv, Est rowCount: 1.0}