什么是物化视图的查询改写功能 - 云原生数据仓库AnalyticDB

云原生数据仓库 AnalyticDB MySQL 版不仅支持查询物化视图，还支持查询改写，即执行用户查询时，自动将查询改写成对物化视图的查询，提高查询效率。本文介绍如何使用物化视图的查询改写功能。

前提条件

AnalyticDB for MySQL集群版本为3.1.4.0及以上。
说明
- 查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
- 查看和升级数仓版集群的内核版本，请参见查看和升级版本。
使用物化视图需要有以下相关权限：
- 创建视图需要有数据库或表级别的CREATE权限。
- 刷新物化视图需要有数据库或表级别的INSERT权限。
- 需要有物化视图所涉及的所有表的相关列（或整个表）的SELECT权限。
- 如果在创建物化视图时指定物化视图为自动刷新模式，需要具备通过服务器本地（即127.0.0.1）或者任意IP（即'%'）刷新视图的权限。

功能介绍

您既可直接查询物化视图，也可通过开启物化视图的查询改写功能，将未使用物化视图的查询自动改写到物化视图上。此时物化视图可作为缓存，无需改造查询SQL也可实现查询加速。AnalyticDB for MySQL不仅支持改写与物化视图结构相同的查询，还支持通过一系列改写规则改写与物化视图等价的查询。

查询改写方式
AnalyticDB for MySQL会先提取查询语句的结构信息，判断查询语句的结构是否与物化视图结构一致，并根据判断结果选择查询改写方式。AnalyticDB for MySQL支持如下改写方式：
- 完全匹配改写
  若查询语句的结构和物化视图的结构完全相同，AnalyticDB for MySQL会修改查询语句使其直接使用物化视图进行查询。该方式是最基本的改写方式，简单直接且使用限制较少。使用限制详情请参见使用限制。
- 高级查询改写
  如果查询和物化视图结构不一致，AnalyticDB for MySQL会运用改写规则，来检查能否从物化视图中找到查询所需的数据进行计算。AnalyticDB for MySQL会尝试对查询及子查询的每个部分进行改写，且查询中的不同部分可能会使用不同的物化视图。高级查询改写方式支持的改写范围和使用限制详情，请参见改写范围和使用限制。
查询改写级别
AnalyticDB for MySQL当前仅支持STALE_TOLERATED级别的改写，即使物化视图中的数据不是最新的，只要可以提高查询性能，依然可以将用户查询改写成对该物化视图的查询。这种方式可以提供最大范围的查询改写，但此数据可能无法反映物化视图基表的最新数据，可能导致查询结果不准确。建议您在使用查询改写功能前，先刷新物化视图获取最新结果后再执行查询。刷新视图的方法，请参见全量刷新物化视图。

使用方法

开启查询改写功能

AnalyticDB for MySQL支持如下开启方法：

创建物化视图时，通过指定ENABLE QUERY REWRITE关键字开启，详情请参见关键字说明。
创建物化视图后，通过ALTER MATERIALIZED VIEW <mv_name> ENABLE QUERY REWRITE;语句开启，详情请参见管理物化视图。

关闭查询改写功能

AnalyticDB for MySQL支持如下关闭方法：

通过ALTER MATERIALIZED VIEW <mv_name> DISABLE QUERY REWRITE;语句关闭目标物化视图的查询改写功能，详情请参见管理物化视图。
通过添加Hint来禁止目标查询使用自动查询改写功能，语法如下：

/*+MV_QUERY_REWRITE_ENABLED=false*/
SELECT ...

示例

创建物化视图时开启查询改写功能，语句如下：

CREATE MATERIALIZED VIEW adb_mv
REFRESH START WITH now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT course_id,course_name,max(course_grade) AS max_grade FROM tb_courses;

执行查询，语句如下：

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

通过EXPLAIN语句验证查询改写效果，语句如下：

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

返回结果如下：

+---------------+
| 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}

从上述执行计划可以看出，查询时实际使用的是存储在物化视图adb_mv中的数据，而不是tb_courses表中的数据。

使用限制

物化视图中存在不支持改写的内容，改写功能会失效。
AnalyticDB for MySQL支持完全匹配改写和高级查询改写两种方式，两种方式的使用限制不同，其中：
- 当物化视图中存在如下内容时，完全匹配改写会失效：
  - 非确定性函数（Non-Deterministic Functions），例如now，current_timestamp，random等函数。
  - 用户自定义函数（User-defined Functions）。
- 当物化视图存在如下内容时，高级查询改写会失效：
  - Order By、Limit、或Offset语句。
  - Union或Union All语句。
  - Group By语句中出现Grouping Sets、Cube或Rollup。
  - Window Functions。
  - Full Outer Join。
  - 系统表。
  - 关联子查询。
  - 非确定性函数（Non-Deterministic Functions），例如now，current_timestamp，random等函数。
  - 用户定义函数（User-defined Functions）。
  - Having语句。
  - Self Join（同一个表重复出现）。
若涉及的查询存在于如下语句中，查询改写功能会失效。
- CREATE TABLE AS SELECT。
- INSERT INTO SELECT。
- INSERT OVERWRITE SELECT。
- REPLACE INTO SELECT。
- DELETE或UPDATE。
其它情况。
若查询语句没有设置过滤条件或聚合函数的单表查询，查询改写功能会失效。

改写范围

改写范围示例中都使用了相同的表，表创建语句如下：

CREATE TABLE part (
  partkey INTEGER NOT NULL,
  name VARCHAR(55) NOT NULL,
  type VARCHAR(25) NOT NULL
);

CREATE TABLE lineitem (
  orderkey BIGINT,
  partkey BIGINT NOT NULL,
  suppkey BIGINT NOT NULL,
  extendedprice DOUBLE NOT NULL,
  discount DOUBLE NOT NULL,
  returnflag CHAR(1) NOT NULL,
  linestatus CHAR(1) NOT NULL,
  shipdate DATE NOT NULL,
  shipmode VARCHAR(25) NOT NULL,
  commitdate DATE NOT NULL,
  receiptdate DATE NOT NULL
);
  
CREATE TABLE orders (
  orderkey BIGINT PRIMARY KEY,
  custkey BIGINT NOT NULL,
  orderstatus VARCHAR(1) NOT NULL,
  totalprice DOUBLE NOT NULL,
  orderdate DATE NOT NULL
);
CREATE TABLE partsupp (
  partkey INTEGER NOT NULL PRIMARY KEY,
  suppkey INTEGER NOT NULL,
  availqty INTEGER NOT NULL,
  supplycost DECIMAL(15,2) NOT NULL
);

完全匹配改写

AnalyticDB for MySQL会提取查询语句的结构信息，若查询语句的结构和物化视图的结构完全相同，AnalyticDB for MySQL会直接将查询改写为对物化视图的查询。

示例

原查询语句如下：

SELECT
  l.returnflag,
  l.linestatus, 
  SUM(l.extendedprice * (1 - l.discount)), 
  COUNT(*) AS count_order
FROM lineitem AS l 
GROUP BY l.returnflag, l.linestatus;

物化视图语句如下：

CREATE MATERIALIZED VIEW mv0
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.returnflag,
  l.linestatus,
  SUM (l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  count(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

开启物化视图改写功能后，若再次执行原查询，原语句会被改写为如下语句进行查询：
```
SELECT returnflag, linestatus, sum_disc_price, count_order
FROM mv0;
```

高级查询改写

若查询语句的结构和物化视图的结构不同，AnalyticDB for MySQL会运用一系列改写规则，来依次检查物化视图和查询中的Filter、Join、Aggregation和 Group By表达式，尝试用物化视图构建一个等价的查询表达式，来回答原始查询或查询中的某一部分。

高级查询改写支持如下改写规则：

Filter

当查询的谓词和物化视图的谓词不同时，AnalyticDB for MySQL会使用Filter改写规则来构造补偿表达式。若查询中出现的表达式不存在于视图中，系统也会尝试从视图中计算该表达式。示例如下：

原查询语句如下：

SELECT
    l.shipmode,
    l.extendedprice * (1 - l.discount) AS disc_price
FROM
    orders AS o,
    lineitem AS l
WHERE o.orderkey = l.orderkey
    AND l.shipmode in ('REG AIR', 'TRUCK')
    AND l.commitdate < l.receiptdate
    AND l.shipdate < l.commitdate;

物化视图创建语句如下：

CREATE MATERIALIZED VIEW mv1
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS 
SELECT
  l.shipmode,
  l.extendedprice,
  l.discount
FROM
  orders AS o,
  lineitem AS l
WHERE o.orderkey = l.orderkey
  AND l.commitdate < l.receiptdate
  AND l.shipdate < l.commitdate;

开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：

SELECT
    shipmode,
    extendedprice * (1 - discount) AS disc_price,
    discount
FROM mv1
WHERE shipmode in ('REG AIR', 'TRUCK');

Join
AnalyticDB for MySQL支持在查询和物化视图中使用Inner Join和Outer Join，且支持包括Left Join、Right Join在内的任意Join顺序。当目标查询和物化视图中包含的Join关系不同时，AnalyticDB for MySQL会尝试通过Join规则，在物化视图的基础上计算出目标查询所需的Join关系。例如，AnalyticDB for MySQL会过滤物化视图中的某行数据，使得带Outer Join的物化视图可以用来计算带有Inner Join关系的查询。示例如下：
- 原查询语句如下：
```
SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM
  part AS p,
  partsupp AS ps
WHERE p.partkey = ps.partkey
  AND p.type NOT LIKE  'MEDIUM POLISHED%';
```
- 物化视图创建语句如下：
```
CREATE MATERIALIZED VIEW mv2
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS 
SELECT 
  p.type,
  p.partkey,
  ps.suppkey
FROM
  partsupp AS ps 
  INNER JOIN part AS p ON p.partkey = ps.partkey
WHERE p.type NOT LIKE 'MEDIUM POLISHED%';
```
- 开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：
```
SELECT type, partkey, suppkey
FROM mv2;
```

Aggregation

若目标查询或物化视图中使用了不同的GROUP BY或聚合函数，AnalyticDB for MySQL会通过Aggregation规则，从物化视图中构造出相同的聚合函数。示例如下：

原查询语句如下：

SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

物化视图创建语句如下：

CREATE MATERIALIZED VIEW mv3
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
    l.returnflag,
    l.linestatus, 
    SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price, 
    COUNT(*) AS count_order
FROM lineitem AS l 
GROUP BY l.returnflag, l.linestatus;

开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：
```
SELECT returnflag, linestatus, sum_disc_price, count_order
FROM mv3;
```

Aggregation Rollup

若目标查询和物化视图中Group By的列表不同，AnalyticDB for MySQL会尝试通过Rollup规则，对物化视图进行Rollup。示例如下：

原查询语句如下：

SELECT
  l.returnflag,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.returnflag = 'R'
GROUP BY  l.returnflag;

物化视图创建语句如下：

CREATE MATERIALIZED VIEW mv4
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
    l.returnflag,
    l.linestatus, 
    SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price, 
    COUNT(*) AS count_order
FROM lineitem AS l 
GROUP BY l.returnflag, l.linestatus;

开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：

SELECT 
  returnflag, 
  linestatus,
  sum_disc_price, 
  count_order
FROM mv4
WHERE returnflag = 'R'
GROUP BY returnflag;

Subqueries

若目标查询和物化视图的子查询不同，AnalyticDB for MySQL会尝试使用Subqueries规则进行查询改写。示例如下：

原查询语句如下：

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM
  part AS p,
  (SELECT * FROM partsupp WHERE suppkey > 10) ps
WHERE p.partkey = ps.partkey;

物化视图创建语句如下：

CREATE MATERIALIZED VIEW mv5
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS 
SELECT 
  p.type,
  p.partkey,
  ps.suppkey
FROM
  part AS p,
  partsupp AS ps
WHERE p.partkey = ps.partkey;

开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：
```
SELECT 
  type,
  partkey,
  suppkey
FROM mv5
WHERE suppkey > 10;
```

Query Partial

若目标查询中涉及的表不在物化视图中，AnalyticDB for MySQL会通过Query Partial规则尝试为物化视图的结果加入缺失的表。示例如下：

原查询语句如下：

SELECT 
  p.type,
  p.partkey,
  ps.suppkey
FROM
  part AS p,
  partsupp AS ps
WHERE p.partkey = ps.partkey
    AND p.type NOT LIKE 'MEDIUM POLISHED%';

物化视图创建语句如下：

CREATE MATERIALIZED VIEW mv6
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey
FROM
  part AS p
WHERE p.type NOT LIKE  'MEDIUM POLISHED%';

开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：
```
SELECT
  mv6.type,
  mv6.partkey,
  ps.suppkey
FROM 
  mv6,
  partsupp AS ps
WHERE mv6.partkey = ps.partkey;
```

Union

如果物化视图仅包含一部分数据，AnalyticDB for MySQL会使用Union改写规则，即先使用物化视图中已有的数据进行计算获得部分查询结果，剩下的查询结果从基表中计算得到。

原查询语句如下：

SELECT
  l.linestatus,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.shipdate >= DATE '1998-01-01'
GROUP BY l.linestatus;

物化视图创建语句如下：

CREATE MATERIALIZED VIEW mv7
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.linestatus,
  COUNT(*) AS count_order
FROM lineitem AS l 
WHERE l.shipdate >= DATE '2000-01-01'
GROUP BY l.linestatus;

开启物化视图改写功能后，若再次执行原查询，原语句即会被改写为如下语句进行查询：

SELECT 
  linestatus,
  count_order
FROM (
    SELECT linestatus, count_order
  FROM mv7
UNION ALL
    SELECT 
      l.linestatus,
      COUNT(*) AS count_order
    FROM lineitem AS l 
    WHERE l.shipdate >= DATE '1998-01-01' AND l.shipdate < DATE '1998-01-01'
    GROUP BY l.linestatus) 
GROUP BY linestatus;

常见问题

Q：物化视图创建后，为什么自动改写没有生效？

A：若查询改写未生效，请检查如下信息：

是否为目标物化视图开启查询改写功能。开启方法，请参见使用方法。
物化视图是否存在限制。关于限制的详情，请参见使用限制。
执行查询的用户是否拥有对应物化视图的SELECT权限。您可以通过GRANT语句为查询者添加权限，详情请参见权限要求。