数据库物化视图是什么？物化视图的创建、刷新及应用指南-SQL-PHP中文网

物化视图（materialized view，mv）是数据库中一种物理存储对象，用于存储复杂查询的预计算结果，以提升查询性能。1. 它与普通视图不同，普通视图仅保存查询定义，每次查询时实时执行；而物化视图实际存储数据，查询时直接读取结果。2. 物化视图适用于频繁执行、复杂且对数据实时性要求不高的场景，如报表系统、数据分析和数据仓库。3. 创建物化视图需使用create materialized view语句，并可指定构建方式（build immediate/deferred）、刷新方式（complete/fast/force）和刷新时机（on demand/on commit/定时）。4. 常见刷新策略包括夜间批量刷新、增量刷新结合定时任务、少量高频更新采用on commit。5. 使用物化视图可能面临数据延迟、刷新开销大、锁问题、存储消耗、管理复杂、优化器未使用mv、fast刷新限制等挑战，需通过合理设计、测试、监控和维护来应对。选择物化视图还是普通视图，应根据业务对数据实时性和性能需求进行权衡。

数据库物化视图是什么？物化视图的创建、刷新及应用指南

数据库物化视图，简单来说，就是查询结果的“快照”或“缓存”，它把一个复杂查询（比如涉及到多表连接、聚合计算）的结果预先计算好，然后存储起来，就像一张普通的表一样。这么做的核心目的，就是为了显著提升后续对这些复杂查询的响应速度。它牺牲了一点点数据的新鲜度（因为需要定时刷新），换取了查询性能上的巨大飞跃，特别是在数据分析、报表生成和数据仓库这类场景下，简直是性能优化的利器。

解决方案

物化视图（Materialized View，简称MV）本质上是数据库中一种特殊的物理存储对象，它存储的是某个查询语句的实际结果集。与普通视图（View）不同，普通视图仅仅是存储了一个查询定义，每次查询视图时都会实时执行其背后的SQL语句来获取数据；而物化视图则直接存储了查询执行后的数据，相当于把查询结果“物化”了。

它最常见的应用场景，就是那些需要频繁执行、但底层数据又不需要实时更新到毫秒级的复杂分析型查询。比如，你有一个每天都要跑的销售额统计报表，涉及到几百张表的连接和各种聚合函数，直接跑可能需要几分钟甚至几十分钟。如果把这个查询的结果做成物化视图，报表查询就变成了直接查询这张“预计算好”的表，响应时间可能从分钟级直接跳到秒级，甚至毫秒级。这种性能提升，对于用户体验和系统资源消耗来说，都是质的飞跃。当然，这种便利性并非没有代价，它需要额外的存储空间，并且需要定期或在特定事件发生时刷新数据，以保持与源数据的一致性。

物化视图与普通视图有何不同，我该如何选择？

这确实是很多人初次接触物化视图时会有的疑问。我的理解是，它们就像是两种不同类型的“透镜”来看待数据。

普通视图（View）更像是一个“虚拟表”，它不存储任何数据，只是一个预定义的查询语句。每次你查询普通视图时，数据库都会实时地执行这个查询，然后把结果呈现给你。它的优点是数据永远是最新的，因为它是实时从基表（原始数据表）中获取的；它也不占用额外的存储空间。但缺点也很明显，如果底层查询很复杂，每次查询视图都会消耗大量的计算资源和时间。我通常会在需要简化复杂查询、隐藏底层表结构、或者进行权限控制时使用普通视图。比如，我只想让某些用户看到员工的基本信息，而不能看到薪资，就可以创建一个只包含基本信息的视图。

而物化视图（Materialized View）则完全不同。它是一个“物理表”，它把查询的结果实实在在地存储在了磁盘上。这意味着你查询物化视图时，数据库不需要重新计算，直接从磁盘读取预存的结果就行了。它的优点是查询速度飞快，因为数据已经准备好了。但缺点也随之而来：它需要占用额外的存储空间；而且，最关键的是，它的数据不是实时更新的，你需要手动或自动地去“刷新”它，才能让它与基表的数据保持同步。在实际选择时，我通常会这样考虑：

选择普通视图：
- 对数据实时性要求极高，哪怕几秒钟的延迟都不能接受。
- 基表数据更新非常频繁，频繁刷新物化视图的开销可能比直接查询还大。
- 查询本身并不复杂，或者执行频率不高，性能瓶颈不在这里。
- 主要目的是为了简化SQL、提供数据抽象或进行权限控制。
选择物化视图：
- 查询非常复杂，涉及大量连接、聚合，执行时间长，且执行频率高。
- 对数据实时性有一定容忍度，可以接受几分钟、几小时甚至一天的数据延迟。
- 系统资源（CPU、I/O）在执行这些复杂查询时负载过高。
- 在数据仓库、OLAP分析、报表系统等场景下，物化视图几乎是标配。

所以，选择哪一个，很大程度上取决于你的业务场景对数据实时性和查询性能的权衡。没有银弹，只有最适合的方案。

如何创建和刷新物化视图，有哪些常见策略？

创建和刷新物化视图是其生命周期中最重要的两个环节。以主流数据库（如Oracle或PostgreSQL）为例，它们的基本语法和概念是相通的。

创建物化视图：

基本的创建语法大致是这样的：

-- PostgreSQL 示例
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue
FROM
    orders
JOIN
    order_items ON orders.order_id = order_items.order_id
GROUP BY
    product_id;

-- Oracle 示例（更复杂的选项）
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE -- 立即构建，即创建时就填充数据
REFRESH COMPLETE ON DEMAND -- 刷新方式为完全刷新，刷新时机为按需（手动）
ENABLE QUERY REWRITE -- 允许查询重写，让优化器自动使用MV
AS
SELECT
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue
FROM
    orders
JOIN
    order_items ON orders.order_id = order_items.order_id
GROUP BY
    product_id;

登录后复制

这里面有几个关键的选项：

BUILD IMMEDIATE / BUILD DEFERRED： IMMEDIATE表示在创建物化视图的同时就填充数据，这是最常见的。DEFERRED表示先创建空视图，之后再手动刷新填充数据。
REFRESH 子句： 这定义了物化视图的刷新方式和时机，是物化视图最核心的部分之一。
- 刷新方式：
  - COMPLETE：完全刷新。每次刷新都会重新执行一遍物化视图的查询语句，然后清空旧数据，插入新数据。对于数据量大、变化不频繁的物化视图来说，可能是一个耗时且资源消耗大的操作。
  - FAST：快速刷新（增量刷新）。这是最理想的方式，它只对基表中发生变化的数据进行增量更新，而不是重新计算整个视图。这通常需要基表上有特殊的日志（如Oracle的MATERIALIZED VIEW LOG）或满足特定条件（如PostgreSQL在某些情况下可以自动判断）。如果配置得当，FAST刷新速度非常快。
  - FORCE：强制刷新。数据库会尝试进行FAST刷新，如果条件不满足（比如没有MV日志），则会自动降级为COMPLETE刷新。这是一个比较安全的默认选择。
- 刷新时机：
  - ON DEMAND：按需刷新。这意味着你需要手动执行刷新命令。
  - ON COMMIT：提交时刷新。当基表发生DML操作（INSERT/UPDATE/DELETE）并提交事务时，物化视图会自动刷新。这种方式可以保证数据相对实时，但如果基表更新频繁，每次提交都触发刷新，可能会严重影响事务性能。通常只适用于更新量很小、实时性要求又比较高的场景。
  - START WITH ... NEXT ... (Oracle)：定时刷新。你可以设置一个时间点开始刷新，然后定义一个刷新间隔。比如START WITH SYSDATE NEXT SYSDATE + 1/24表示每小时刷新一次。PostgreSQL通常通过pg_cron扩展或外部调度工具（如Linux的cron）来模拟定时刷新。

刷新物化视图：

阿里云-虚拟数字人

阿里云-虚拟数字人是什么？ ...

查看详情

手动刷新是最直接的方式：

-- PostgreSQL / Oracle
REFRESH MATERIALIZED VIEW sales_summary;

登录后复制

如果是在Oracle中使用了FAST刷新，并且基表有MV日志，那么在基表数据变化后，执行REFRESH命令时，数据库就会利用MV日志进行增量更新。

常见刷新策略：

我的经验是，选择刷新策略需要根据实际的业务需求和数据特性来定：

夜间批量完全刷新： 对于数据量巨大、白天业务繁忙、对数据实时性要求不高的报表和分析系统，我通常会选择REFRESH COMPLETE ON DEMAND，然后通过数据库的调度任务（如Oracle的DBMS_SCHEDULER，或PostgreSQL的pg_cron、Linux的cron job结合psql脚本）在业务低峰期（比如凌晨）进行一次完全刷新。这样既保证了白天查询的性能，又避免了刷新对在线业务的影响。
增量刷新结合定时任务： 如果对数据实时性有一定要求（比如可以接受1小时的延迟），并且基表支持FAST刷新（例如Oracle的MV日志），那么我会考虑REFRESH FAST ON DEMAND，然后设置一个每小时执行一次的定时任务。这能最大限度地平衡实时性和性能。
少量高频更新的ON COMMIT： 极少数情况下，如果物化视图依赖的基表更新频率不高，但每次更新后又希望物化视图能立即反映变化，并且物化视图本身数据量不大，那么ON COMMIT可以考虑。但我个人在使用时会非常谨慎，因为它对事务性能的影响是直接的。

总而言之，刷新策略的选择是一个权衡的艺术，没有绝对的对错，只有最适合你当前业务场景的方案。

物化视图在实际应用中会遇到哪些坑或挑战？

物化视图虽然强大，但在实际应用中，我确实遇到过一些让人头疼的“坑”和挑战。了解这些，能帮助我们更好地规避风险。

数据“不新鲜”的误解： 这是最大的一个坑。很多业务方或开发人员在不完全理解物化视图工作原理的情况下，会把它当作普通表来用，然后抱怨数据怎么不是实时的。务必在设计阶段就明确告知数据会有延迟，并确定可接受的延迟范围。如果业务对数据实时性要求极高（比如交易系统），物化视图就不是最佳选择。
刷新开销巨大：
- 刷新时间长： 当物化视图的数据量非常大，或者底层查询非常复杂时，一次COMPLETE刷新可能会耗时数小时，甚至更长。这期间，物化视图可能处于不可用状态（取决于数据库实现和并发控制），或者刷新过程会消耗大量系统资源，影响其他业务。我曾见过一个夜间刷新任务因为数据量暴增，导致刷新时间超出窗口期，直接影响了第二天早上的报表生成。
- 资源消耗： 刷新过程会占用CPU、内存和I/O资源。如果刷新任务和核心业务抢占资源，可能会导致整个系统性能下降。
- 锁问题： 某些刷新机制可能会对基表或物化视图本身产生锁，影响并发写入或读取。
存储空间消耗： 物化视图是物理存储的，它会占用额外的磁盘空间。对于TB级别的数据仓库来说，如果创建的物化视图过多或过大，存储成本会迅速上升。
管理复杂性：
- 依赖管理： 当基表结构发生变化时（比如增加、删除列），物化视图可能会失效，需要重新创建。如果依赖关系复杂，维护起来会很麻烦。
- 刷新调度： 需要精心设计刷新策略和调度任务，确保在业务低峰期完成刷新，并能处理刷新失败的情况。
- 监控： 必须有完善的监控机制，及时发现物化视图的刷新失败、刷新时间过长、或数据与基表不一致等问题。
查询优化器不使用物化视图： 这是一个比较隐蔽的坑。你辛辛苦苦创建了物化视图，期望它能加速查询，结果发现查询优化器根本没有选择使用它，还是去扫描基表。这可能是因为：
- 查询重写（Query Rewrite）未启用： 数据库需要配置允许查询重写，并且物化视图需要被标记为可重写。
- 查询不匹配： 用户的查询语句与物化视图的定义不完全匹配，或者优化器认为直接查询基表更优（比如数据量太小，MV的维护成本更高）。
- 数据陈旧： 如果物化视图的数据过于陈旧，优化器可能会认为使用它不如直接查询基表。
- 统计信息不足或不准确： 优化器依赖准确的统计信息来做决策。如果物化视图或基表的统计信息过时，优化器可能做出错误的选择。
FAST刷新（增量刷新）的局限性： 虽然FAST刷新很诱人，但它并非总是可用。它通常对物化视图的定义有严格要求（比如不能包含某些复杂的函数、不能有DISTINCT聚合、不能有UNION ALL等），并且需要基表支持特定的日志机制（如Oracle的MATERIALIZED VIEW LOG）。如果这些条件不满足，你就只能退而求其次选择COMPLETE刷新。