★ FEATURED ARTICLE 1. 三个名字一个东西先把概念理清楚刚接触 Oracle 数据库那会儿我一度被“物化视图”“快照”“实体化视图”这三个词搞懵了。查资料的时候有人写物化视图有人写快照还有人写实体化视图我一度以为是三种不同的技术分别去翻文档结果越翻越乱。后来才搞明白这三个词在 Oracle 的语境里说的其实是同一个东西——Materialized View。“物化视图”是标准译法“实体化视图”是另一种中文翻译而“快照”则是 Oracle 早期版本大概 8i 之前用的叫法。Oracle 官方后来统一改口叫 Materialized View但在很多老项目、老文档、老 DBA 的口口相传里“快照”这个词一直活到了今天。所以你在公司里听到有人说“建个快照”别慌他说的就是物化视图。那它到底是个啥一句话概括物化视图是一张物理存在的表它的数据来自一条查询语句并且可以按照你设定的策略自动刷新。这句话里有两个关键点值得掰开说。第一“物理存在的表”。普通视图View你查它的时候它才去执行底层的 SQL本身不存数据本质上就是一段被保存下来的 SQL 语句。而物化视图不一样它是真真切切占磁盘空间的数据是提前算好、存下来的。你查物化视图查的就是这张表本身不会再去跑那条复杂的查询。这就是它“物化”两个字的含义——把查询结果变成了实实在在的数据。第二“可以自动刷新”。这是物化视图区别于普通表的灵魂。普通表的数据你得自己写程序往里灌物化视图可以配置成定时刷新、或者底层数据一变就刷新把源表的变化同步过来。它解决的问题也很明确当你需要频繁执行一条很重的查询比如多表关联、聚合统计而底层数据又不是实时性要求极高的时候物化视图能把这条查询的代价从“每次执行”降到“查一张表”。适合谁来学我觉得三类人最需要一是做数据仓库、报表系统的开发和运维这类场景是物化视图的主战场二是做系统集成的经常要把多个库的数据汇总到一处三是日常要写复杂 SQL 但又嫌慢的普通开发学会物化视图能帮你省下不少优化功夫。哪怕你只是刚入门的 DBA理解物化视图的刷新机制也是绕不开的一课。下面我就按自己踩过坑的顺序把物化视图从设计思路到实操细节再到问题排查完整地捋一遍。2. 为什么要有物化视图从一次慢查询说起2.1 普通视图的软肋在哪里要理解物化视图的价值得先看普通视图的局限。假设你有一个订单系统订单表几千万行订单明细表上亿行还有商品表、客户表、地区表。业务方要一张“每个地区每个月的销售总额”报表。这条 SQL 大概是这样的订单表关联订单明细表再关联地区表按地区和月份分组求和。这条查询跑一次如果索引和统计信息都正常可能几秒到几十秒。问题是这张报表可能一天要被查几百次每次都要重新跑一遍这个几表关联加聚合。CPU 在烧IO 在跑业务方还在等。你可能会想那我建个普通视图把这 SQL 存起来不就行了不行。普通视图只是把 SQL 存下来了你查视图的时候Oracle 会把视图定义展开还是去跑那条完整的查询。它省的是你敲 SQL 的力气不是数据库执行的力气。2.2 物化视图的核心思路用空间换时间物化视图的思路就完全不一样了。它把这条查询的结果预先算好、存成一张表。业务方再查的时候直接查这张表几千万行的关联聚合变成了对一张结果表的简单查询可能几十毫秒就出来了。这就是典型的用空间换时间。你多花了一些磁盘空间存这份结果换来的是查询性能的数量级提升。但这里有个新问题源表的数据是会变的。今天下了新订单明天改了订单金额你存的那份结果就过期了。所以物化视图必须配套一套刷新机制把源表的变化同步到物化视图里。2.3 刷新策略的取舍什么时候刷刷多少刷新策略是物化视图设计的核心也是最能体现经验的地方。Oracle 提供了几种刷新方式我按实际使用频率从高到低说。完全刷新Complete Refresh把物化视图整个清空重新执行一遍定义查询把结果全量灌进去。简单粗暴但代价大。如果源表数据量大一次完全刷新可能跑很久。适合源表数据量不大、或者刷新频率很低的场景。快速刷新Fast Refresh只把源表自上次刷新以来变化的部分同步过来。代价小、速度快但限制非常多。不是所有物化视图都能快速刷新它对定义查询的写法有严格要求比如不能有某些函数、某些关联方式。这个后面会详细讲。按需刷新On Demand不自动刷你想刷的时候手动执行刷新命令。适合数据实时性要求不高、或者刷新时机需要人工控制的场景。按提交刷新On Commit源表一有事务提交物化视图立刻刷新。实时性最好但对源库的性能影响最大因为每次源表写入都要触发物化视图的维护。一般只在数据量小、实时性要求极高的场景用。定时刷新On Demand Job这是实际项目里用得最多的组合。配置一个定时任务比如每天凌晨两点刷新一次。既保证了数据的新鲜度又避开了业务高峰期。选哪种取决于三个因素源表数据量、业务对数据实时性的要求、源库能承受的额外压力。这三个因素往往是互相矛盾的你得根据实际情况权衡。我的经验是绝大多数报表场景定时刷新就够了别一上来就追求实时。3. 物化视图的几种类型与适用场景3.1 基于主键的物化视图这是最常见的一类。物化视图的定义查询里包含了源表的主键Oracle 就能通过主键来识别哪些行是新增的、哪些是修改的、哪些是删除的从而实现快速刷新。建这类物化视图的时候源表必须有主键而且定义查询里要把主键列选出来。比如源表是订单表主键是订单 ID那你的物化视图查询里就得包含订单 ID 这一列。基于主键的物化视图快速刷新的支持相对好一些是首选方案。3.2 基于 ROWID 的物化视图如果源表没有主键或者定义查询里不方便带主键可以用 ROWID。ROWID 是 Oracle 里每一行数据的物理地址唯一标识一行。物化视图记录源表行的 ROWID刷新的时候通过 ROWID 定位变化。但 ROWID 有个坑如果源表做了分区维护、数据迁移之类的操作ROWID 可能会变导致物化视图刷新出问题。所以能用主键就别用 ROWID。3.3 聚合类物化视图这类物化视图的定义查询里带 GROUP BY 和聚合函数比如按地区分组求销售总额。它是数据仓库场景的常客。聚合类物化视图的快速刷新限制最多。Oracle 需要能通过源表的增量变化推算出聚合结果的变化。比如某个地区新增了一笔订单Oracle 得知道要把这个地区的总额加上这笔订单的金额。这要求聚合函数是可增量计算的像 SUM、COUNT 这类可以但有些复杂的聚合就不行。3.4 只读物化视图与可更新物化视图绝大多数物化视图是只读的你只能查不能直接往里写数据。但在某些高级场景下可以建可更新的物化视图允许你对物化视图做 DML 操作然后通过一套机制同步回源表。这个用法比较少见配置也复杂一般项目用不到这里就不展开了。4. 动手建一个物化视图完整实操流程4.1 环境准备与权限确认动手之前先确认几件事。第一你的账号得有创建物化视图的权限。通常需要CREATE MATERIALIZED VIEW权限。如果要创建物化视图日志快速刷新必需还需要CREATE TABLE权限因为物化视图日志本质上是一张表。第二确认源表的情况。源表有没有主键数据量多大有没有分区这些直接决定了你能不能用快速刷新。第三确认刷新所需的资源。完全刷新会消耗大量临时表空间和 undo 空间尤其是源表数据量大的时候。提前跟 DBA 打个招呼别把库跑挂了。4.2 创建物化视图日志如果你想用快速刷新第一步是在源表上创建物化视图日志。这个日志的作用是记录源表自上次刷新以来的所有变化物化视图刷新的时候就读这个日志知道哪些行变了。CREATE MATERIALIZED VIEW LOG ON orders WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;这里几个参数解释一下。WITH PRIMARY KEY表示日志里记录主键信息WITH ROWID表示也记录 ROWID。INCLUDING NEW VALUES表示日志里同时记录变化前后的值某些聚合类物化视图的快速刷新需要这个。物化视图日志建好之后源表的每一次 DML 操作都会往这个日志表里写一条记录。所以它会带来额外的写入开销源表写入越频繁这个开销越明显。这是快速刷新的代价你得心里有数。4.3 创建物化视图本体日志建好了接下来建物化视图。CREATE MATERIALIZED VIEW mv_region_sales REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT o.region_id, TRUNC(o.order_date, MM) AS sale_month, SUM(o.amount) AS total_amount, COUNT(*) AS order_count FROM orders o GROUP BY o.region_id, TRUNC(o.order_date, MM);逐行拆解一下。REFRESH FAST ON DEMAND表示用快速刷新按需刷新不自动刷需要手动或定时任务触发。ENABLE QUERY REWRITE是个很有用的选项。它允许 Oracle 的查询优化器在合适的时候自动把对源表的查询改写成对物化视图的查询。比如业务方写了一条查源表聚合的 SQL优化器发现物化视图里已经有这个结果了就直接查物化视图省去重算。这个功能叫查询重写是物化视图的一大杀器后面单独讲。AS后面就是定义查询。注意这里用了TRUNC(o.order_date, MM)把日期截断到月然后按地区和月份分组。4.4 验证快速刷新是否真的生效建完之后别急着高兴先验证快速刷新到底行不行。很多人建完以为配了REFRESH FAST就是快速刷新了其实 Oracle 可能悄悄降级成了完全刷新。验证方法是查数据字典SELECT mview_name, refresh_mode, refresh_method, last_refresh_type FROM user_mviews WHERE mview_name MV_REGION_SALES;重点看LAST_REFRESH_TYPE这一列。如果显示FAST说明上次刷新走的是快速刷新。如果显示COMPLETE说明快速刷新没生效被降级了。如果被降级了怎么排查Oracle 提供了一个诊断工具可以告诉你为什么不能快速刷新EXEC DBMS_MVIEW.EXPLAIN_MVIEW(MV_REGION_SALES);执行完之后查MV_CAPABILITIES_TABLE这张表里面会列出每一条快速刷新的能力检查项哪一项是N不支持就说明卡在哪里了。这个表是排查快速刷新问题的利器一定要会用。4.5 配置定时刷新任务按需刷新的物化视图得有人去触发它。实际项目里一般用定时任务。BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name JOB_REFRESH_MV_REGION_SALES, job_type PLSQL_BLOCK, job_action BEGIN DBMS_MVIEW.REFRESH(MV_REGION_SALES, F); END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY; BYHOUR2; BYMINUTE0, enabled TRUE ); END; /这里DBMS_MVIEW.REFRESH的第二个参数F表示强制快速刷新。如果快速刷新不可用会报错而不是悄悄降级成完全刷新。这样你能及时发现问题。如果你希望它自动降级可以用C完全刷新或者不指定。repeat_interval用的是日历表达式FREQDAILY; BYHOUR2表示每天凌晨两点执行。这个时间点选得有讲究要避开业务高峰也要避开其他定时任务扎堆的时间免得资源争抢。5. 查询重写物化视图的隐藏大招5.1 查询重写到底做了什么前面提到ENABLE QUERY REWRITE这个功能值得单独拎出来讲因为很多人建了物化视图却没用上它白白浪费了性能红利。查询重写的逻辑是这样的业务方写了一条查询源表的 SQL优化器在生成执行计划的时候会去检查有没有可用的物化视图如果物化视图的数据能满足这条查询就把查询改写成查物化视图。整个过程对业务方是透明的业务方甚至不知道物化视图的存在。举个例子业务方写SELECT region_id, SUM(amount) FROM orders WHERE order_date TRUNC(SYSDATE, MM) GROUP BY region_id;如果物化视图MV_REGION_SALES里正好有按地区和月份聚合的数据优化器可能就把这条查询改写成查物化视图性能瞬间提升。5.2 让查询重写生效的几个条件查询重写不是自动就能生效的得满足几个条件。第一物化视图必须启用查询重写建的时候加ENABLE QUERY REWRITE。第二当前会话或系统的查询重写参数得打开。相关参数是QUERY_REWRITE_ENABLED一般默认是TRUE但有些环境可能被关了得确认一下。第三物化视图的数据得是新鲜的。如果物化视图过期了源表变了但物化视图没刷优化器默认不会用它来重写除非你设置了QUERY_REWRITE_INTEGRITY为STALE_TOLERATED。但这个设置有风险可能查到过期数据一般不建议。第四物化视图的定义查询和业务查询得能对得上。这个“对得上”有讲究不是字面一样就行优化器会做等价变换。但如果你物化视图里做了很复杂的处理业务查询又没做同样的处理可能就对不上了。5.3 用 EXPLAIN PLAN 确认重写是否发生怎么知道查询重写有没有生效看执行计划。EXPLAIN PLAN FOR SELECT region_id, SUM(amount) FROM orders GROUP BY region_id; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果执行计划里出现了MAT_VIEW REWRITE ACCESS之类的字样说明重写生效了。如果还是TABLE ACCESS FULL扫源表说明没重写得去排查原因。我踩过的一个坑是物化视图建的时候没加ENABLE QUERY REWRITE后来想补上得用ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITE改改完还得重新收集统计信息不然优化器可能还是不认。6. 常见问题与排查技巧实录物化视图这东西建起来容易用起来稳当不容易。下面这些坑都是我或者身边同行实际踩过的整理成速查表希望能帮你少走弯路。问题现象可能原因排查与解决配了 FAST 刷新实际走的是 COMPLETE定义查询不满足快速刷新条件用DBMS_MVIEW.EXPLAIN_MVIEW查MV_CAPABILITIES_TABLE定位具体哪项不支持快速刷新报错 ORA-12008物化视图日志缺失或损坏检查源表上有没有物化视图日志日志是否被意外删除刷新特别慢完全刷新数据量大或快速刷新但日志积压太多看刷新类型完全刷新考虑改增量日志积压考虑提高刷新频率查询重写不生效物化视图未启用重写或数据过期检查ENABLE QUERY REWRITE检查物化视图是否 STALE源表 DML 变慢物化视图日志带来额外写入开销评估日志必要性非快速刷新场景可以不要日志刷新时临时表空间爆了完全刷新需要大量排序和临时空间提前扩容临时表空间或改在低峰期刷新物化视图数据对不上源表刷新失败但没报错或刷新策略配置错误查USER_MVIEWS的STALENESS列看是否 STALE除了表格里这些还有几个经验性的点值得强调。第一物化视图日志不是越多越好。每个源表上的日志都会带来写入开销而且日志表本身也会占空间。如果一个源表被多个物化视图引用日志是共享的这没问题。但如果一个源表只是偶尔被物化视图引用且用的是完全刷新那日志就是纯负担该删就删。第二刷新时间要错开。如果你有十几个物化视图都配在凌晨两点刷新它们会互相抢资源整体刷新时间被拉长。我的做法是错峰比如按 2:00、2:30、3:00 这样排开或者用DBMS_MVIEW.REFRESH的并行参数但要小心并行度过高反而拖垮库。第三物化视图的统计信息要维护。物化视图本质是张表优化器需要统计信息才能生成好的执行计划。刷新之后尤其是完全刷新之后最好重新收集一下统计信息。可以配在刷新任务后面自动执行。第四别把物化视图当实时同步工具。它的定位是“准实时”或“定时同步”不是毫秒级的实时复制。如果你需要强实时那得考虑别的方案比如流复制或者应用层双写。硬用物化视图做实时源库压力会很大。第五删除物化视图时别忘了日志。删物化视图用DROP MATERIALIZED VIEW但物化视图日志是独立的得单独DROP MATERIALIZED VIEW LOG ON 源表。如果日志不再被任何物化视图使用就该清理掉不然源表一直背着这个写入开销。7. 一些进阶玩法和个人体会物化视图用熟了之后还能玩出一些花样。比如物化视图嵌套一个物化视图基于另一个物化视图建做分层聚合。底层物化视图做明细汇总上层做更高层次的汇总。这样刷新可以分层进行底层刷完再刷上层避免每次都对源表做全量聚合。再比如分区物化视图把物化视图也做成分区的刷新的时候可以按分区刷只刷变化的分区进一步提升效率。这个配置起来复杂一些但对超大数据量的场景很值得。还有物化视图和查询重写配合做透明加速业务方完全无感知SQL 不用改性能就上去了。这是我觉得物化视图最优雅的用法前提是重写条件都满足。我个人在实际操作中的体会是物化视图的难点从来不在“怎么建”而在“怎么让它按你预期的方式刷新”。快速刷新的限制、查询重写的条件、刷新任务的调度这三块是最容易出问题的地方。我的建议是每建一个物化视图都先用EXPLAIN_MVIEW把能力检查一遍确认快速刷新可行建完之后用EXPLAIN PLAN确认查询重写生效上线之后盯着LAST_REFRESH_TYPE和STALENESS确保刷新按预期走。这三步做完基本就稳了。最后再分享一个小技巧如果你不确定一个物化视图该用完全刷新还是快速刷新可以先按完全刷新建跑一段时间看看刷新耗时和源库压力。如果完全刷新耗时能接受就别折腾快速刷新了因为快速刷新的限制和维护成本有时候比省下来的那点刷新时间更值钱。技术选型嘛够用就好别为了炫技给自己挖坑。
阅读完成 · 觉得有帮助？
