您可以用任意查询语法引用物化视图,例如SELECT,INSERT INTO SELECT,WITH等。

权限要求

  • 需要有物化视图的SELECT权限。
  • 不需要物化视图涉及的基表权限。

您可以使用GRANT给用户添加物化视图的SELECT权限。

GRANT SELECT ON test.mymv TO 'user'@'%'

自动改写

说明 该功能目前处于试验性阶段。

物化视图如果开启了QUERY REWRITE功能,查询会被自动改写到有权限的物化视图上。将物化视图当作自定义的缓存使用意味着可以不用对业务SQL进行改造。

如果要使用该功能,除了在视图上ENABLE QUERY REWRITE外,还需在查询上添加hint指定这条SQL开启这个功能。

/*+MV_QUERY_REWRITE_ENABLED=true*/
SELECT ...

您可以通过EXPLAIN语法检测改写是否生效。

# 创建
CREATE MATERIALIZED VIEW mv_max_cost
AS
SELECT max(cost) as max_cost FROM orders

# 测试
EXPLAIN SELECT max(cost) as max_cost FROM orders
# 通过 EXPLAIN 输出的查询计划,可以看到查询实际去查的是 mv_max_cost 存储的结果
# 而不是查询 orders 再计算

如果自动改写没有生效,请检查如下几个方面:

  • 物化视图是否存在限制。
  • 查询者是否拥有对应物化视图的SELECT权限。
  • 物化视图是否过期或正在刷新中。

使用限制

物化视图如果包含以下内容,将不会用于自动改写:
  • Self Join(同一个表重复出现)。
  • Order By,Limit,或者Offset语句。
  • Union或Union All语句。
  • Group By语句中出现Grouping Sets、Cube或Rollup。
  • Having语句。
  • Window Functions。
  • Full Outer Join。
  • 系统表。
  • Where语句中包含子查询。
  • 非确定性函数(Non-Deterministic Functions),如nowcurrent_timestamprandom等函数,以及用户定义函数(User-defined functions)。
任何数据修改语句中涉及的查询也不会被改写:
  • CREATE TABLE AS SELECT
  • INSERT INTO SELECT
  • INSERT OVERWRITE SELECT
  • REPLACE INTO SELECT
  • DELETE或UPDATE
其它情况

查询语句如果是没有过滤条件或聚合函数的单表查询,将不会对其应用自动改写。

改写范围

AnalyticDB MySQL版通过提取查询语句的结构信息,和物化视图进行匹配并改写。查询语句不需要和物化视图完全相同,只要满足逻辑等价的条件就会进行改写。AnalyticDB MySQL版会尝试改写查询及其子查询,一个查询不同部分可能会被多个物化视图改写。

AnalyticDB MySQL版可以改写包含Join,Filter和Project运算符的任意语句,重写Aggregation,并在必要时进行汇总。如果物化视图不包含全部结果,会尝试通过Union运算符进行改写。

目前支持Join,Aggregation,Aggregation Rollup,Query Partial,Union,Union with Aggregation改写规则。

以下通过示例说明AnalyticDB MySQL版自动改写的能力,所有示例基于的表结构为:

CREATE TABLE depts(
  deptno INT NOT NULL,
  deptname VARCHAR(20),
  PRIMARY KEY (deptno)
);
CREATE TABLE locations(
  locationid INT NOT NULL,
  state CHAR(2),
  PRIMARY KEY (locationid)
);
CREATE TABLE emps(
  empid INT NOT NULL,
  deptno INT NOT NULL,
  locationid INT NOT NULL,
  empname VARCHAR(20) NOT NULL,
  salary DECIMAL (18, 2),
  PRIMARY KEY (empid),
  FOREIGN KEY (deptno) REFERENCES depts(deptno),
  FOREIGN KEY (locationid) REFERENCES locations(locationid)
);
子查询改写
查询
SELECT t1.cnt, t2.deptname from (
  SELECT deptno, COUNT(*) AS cnt
  FROM emps
  GROUP BY deptno) t1
JOIN depts t2 ON t1.deptno = t2.deptno
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT deptno, COUNT(*) AS cnt
FROM emps
GROUP BY deptno
改写结果
SELECT t1.cnt, t2.deptname
FROM MV t1
JOIN depts t2 ON t1.deptno = t2.deptno
Join改写
查询
SELECT empid
FROM depts
JOIN (
  SELECT empid, deptno
  FROM emps
  WHERE empid = 1) AS subq
ON depts.deptno = subq.deptno
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid 
FROM emps
    JOIN depts on emps.deptno, depts.deptno
改写结果
SELECT empid
FROM mv
WHERE empid = 1
Aggregation改写
查询
SELECT empid, SUM(salary) AS s
FROM emps
GROUP BY empid
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, SUM(salary) AS s, COUNT(*) AS c
FROM emps
GROUP BY empid
改写结果
SELECT empid, s
FROM mv
Aggregation Rollup改写
查询
SELECT empid, COUNT(*) AS c, SUM(salary) AS s
FROM emps
GROUP BY empid
where deptno = 0
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptno, COUNT(*) AS c, SUM(salary) AS s
FROM emps
GROUP BY empid, deptno
改写结果
SELECT empid, SUM(c), SUM(s)
FROM mv
GROUP BY empid
where deptno = 0
Query Partial改写
查询
SELECT deptname, state, SUM(salary) AS s
FROM emps
JOIN depts ON emps.deptno = depts.deptno
JOIN locations ON emps.locationid = locations.locationid
GROUP BY deptname, state
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptno, state, SUM(salary) AS s
FROM emps
JOIN locations ON emps.locationid = locations.locationid
GROUP BY empid, deptno, state
改写结果
SELECT deptname, state, SUM(s)
FROM mv
JOIN depts ON mv.deptno = depts.deptno
GROUP BY deptname, state
Union改写
查询
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 12000
改写结果
SELECT empid, deptname
FROM mv
UNION ALL
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000 AND salary <= 12000
Union with Aggregation改写
查询
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000
GROUP BY empid, deptname
物化视图
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 12000
GROUP BY empid, deptname
改写结果
SELECT empid, deptname, SUM(s)
FROM (
  SELECT empid, deptname, s
  FROM mv
  UNION ALL
  SELECT empid, deptname, SUM(salary) AS s
  FROM emps
  JOIN depts ON emps.deptno = depts.deptno
  WHERE salary > 10000 AND salary <= 12000
  GROUP BY empid, deptname) AS subq
GROUP BY empid, deptname