使用场景

物化视图通过物理存储SQL查询的结果，使得未来的查询可以直接读取预计算的结果，而不是每次都重新计算相同的逻辑。这可以显著提高查询性能，特别是对于频繁运行的查询，尤其是涉及连接（joins）和聚合（aggregates）操作的查询。

当物化视图启用时，优化器会自动将符合条件的查询重写为使用物化视图，而不是扫描基础表。

Spark物化视图支持以下场景：

• 聚合场景：物化视图可以预先计算并存储聚合结果，如SUM、COUNT、AVG等。

  创建物化视图示例：

  CREATE MATERIALIZED VIEW mv AS
  SELECT empid, deptno
  FROM emps WHERE deptno > 5
  GROUP BY empid, deptno

  原始查询语句：

  SELECT deptno FROM emps
  WHERE deptno > 10 GROUP BY deptno

  改写后的查询语句：

  SELECT deptno FROM mv
  WHERE deptno > 10
  GROUP BY deptno

• Join场景：如果查询的表连接方式与物化视图匹配或物化视图可以补偿该连接方式。

  创建物化视图示例：

  CREATE MATERIALIZED VIEW mv AS
  SELECT o.order_id, c.customer_id
  FROM orders o INNER JOIN customers c ON o.cust_id = c.customer_id;

  原始查询语句：

  SELECT order_id FROM orders INNER JOIN customers
  ON orders.cust_id = customers.customer_id;

  改写后的查询语句：

  SELECT order_id FROM mv

• Predicate/Filter场景：如果查询有可下推的过滤条件，物化视图可以预先计算并存储包含过滤条件的结果。

  创建物化视图示例：

  CREATE MATERIALIZED VIEW mv AS
  SELECT * FROM sales WHERE region = 'ASIA';

  原始查询语句：

  SELECT customer_id
  FROM sales WHERE region = 'ASIA' AND revenue > 1000;

  改写后的查询语句：

  SELECT customer_id from mv WHERE revenue > 1000;

  优化器可以使用物化视图，并应用额外的过滤条件来筛选revenue > 1000的数据。

• Aggregate Rollup场景：当物化视图（MV）包含更细粒度的预聚合数据时，查询可以进行“向上汇总”（roll up）。

  原始查询语句：

  SELECT deptno, COUNT(*) AS c, SUM(salary) AS s
  FROM emps
  GROUP BY deptno

  创建物化视图示例：

  CREATE MATERIALIZED VIEW mv AS
  SELECT empid, deptno, COUNT(*) AS c, SUM(salary) AS s
  FROM emps
  GROUP BY empid, deptno

  改写后的查询语句：

  SELECT deptno, SUM(c), SUM(s)
  FROM mv
  GROUP BY deptno

• Partial Join场景：查询中连接表是物化视图中连接表的超集的场景。

  样例一

  创建物化视图示例：

  CREATE MATERIALIZED VIEW join_mv1 AS
  SELECT lo_orderkey, lo_linenumber, lo_revenue, lo_partkey, c_name, c_address
  FROM lineorder INNER JOIN customer
  ON lo_custkey = c_custkey;

  原始查询语句：

  SELECT lo_orderkey, lo_linenumber, lo_revenue, c_name, c_address, p_name
  FROM lineorder INNER JOIN customer ON lo_custkey = c_custkey
  INNER JOIN part ON lo_partkey = p_partkey;

  改写后的查询语句：

  SELECT lo_orderkey, lo_linenumber, lo_revenue, c_name, c_address, p_name
  FROM join_mv1 INNER JOIN part ON lo_partkey = p_partkey;

  样例二

  创建物化视图示例：

  CREATE MATERIALIZED VIEW mv 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(salary) AS s
  FROM emps
  JOIN depts ON emps.deptno = depts.deptno
  JOIN locations ON emps.locationid = locations.locationid
  GROUP BY deptname, state

  改写后的查询语句：

  SELECT deptname, state, SUM(s)
  FROM mv
  JOIN depts ON mv.deptno = depts.deptno
  GROUP BY deptname, state;

• 表达式场景：重写带有复杂表达式（如算术运算、字符串函数和OR谓词）的连接查询。

  创建物化视图示例：

  CREATE MATERIALIZED VIEW join_mv1
  AS
  SELECT lo_orderkey, lo_linenumber, lo_revenue, lo_partkey, c_name, c_address
  FROM lineorder INNER JOIN customer
  ON lo_custkey = c_custkey;

  原始查询语句：

  SELECT
  lo_orderkey,
  lo_linenumber,
  (2 * lo_revenue + 1) * lo_linenumber,
  upper(c_name),
  substr(c_address, 3)
  FROM lineorder INNER JOIN customer
  ON lo_custkey = c_custkey;

• Join传递场景：指物化视图中的连接类型与查询中的连接类型不一致，但物化视图的连接结果却包含查询连接结果的一种场景。

  样例一

  原始查询语句：

  SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
  FROM lineorder LEFT OUTER JOIN customer
  ON lo_custkey = c_custkey
  WHERE customer.c_address = 'Sb4gxKs7' and customer.c_address is not null;

  创建物化视图示例：

  CREATE MATERIALIZED VIEW join_mv4
  AS
  SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
  FROM lineorder INNER JOIN customer
  ON lo_custkey = c_custkey;

  样例二

  原始查询语句：

  SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
  FROM lineorder INNER JOIN customer
  ON lo_custkey = c_custkey WHERE  c_custkey IS NOT NULL;

  创建物化视图示例：

  CREATE MATERIALIZED VIEW join_mv3
  AS
  SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
  FROM lineorder LEFT OUTER JOIN customer
  ON lo_custkey = c_custkey WHERE c_custkey IS NOT NULL;