什麼是物化視圖的查詢改寫功能 - AnalyticDB

AnalyticDB for 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語句為查詢者添加許可權，詳情請參見許可權要求。