一、开篇场景报表SQL从30分钟到1秒的魔法某数据仓库的日报SQL需要关联5张大表进行复杂的GROUP BY和SUM聚合执行时间长达30分钟。业务部门每天需要等半小时才能看到报表数据。DBA创建了一个物化视图CREATEMATERIALIZEDVIEWmv_daily_sales BUILD IMMEDIATE REFRESH FASTONDEMANDENABLEQUERY REWRITEASSELECTproduct_id,region_id,TRUNC(sale_date)ASsale_day,SUM(amount)AStotal_amount,COUNT(*)ASorder_countFROMsalesGROUPBYproduct_id,region_id,TRUNC(sale_date);神奇的事情发生了——Oracle自动将原来的复杂SQL重写为查询物化视图执行时间从30分钟降到1秒。这就是物化视图的魔力。它是预先计算并存储的查询结果集就像一张“缓存表”可以自动响应相关查询而无需重新执行复杂SQL。今天这篇文章带你深入理解物化视图的查询重写机制、刷新策略和性能优化。二、全景架构图物化视图的工作机制先通过一张架构图看清物化视图从创建到查询重写的完整流程物化视图日志基表DML操作记录ROWID和变更类型支持FAST REFRESH查询阶段可以重写不能重写用户提交原始SQLCBO评估查询重写重写为查询物化视图正常执行原始SQL扫描物化视图-极快扫描基表-可能很慢刷新阶段FASTCOMPLETEFORCE基表数据变更刷新模式使用物化视图日志增量刷新完全重建物化视图优先FAST-失败则COMPLETE更新物化视图数据创建阶段定义物化视图SQLBUILD IMMEDIATE/DEFERRED物化视图容器表物化视图日志-可选关键模块解读橘色容器表物化视图物理存储的表实际保存数据。绿色FAST刷新增量刷新只更新变化的数据效率最高。蓝色查询重写CBO自动将原始SQL重写为查询物化视图。红色重写决策CBO评估重写是否可行、是否代价更低。三、物化视图的核心概念1.物化视图的本质定义精确描述物化视图Materialized View是预先计算并物理存储的查询结果集。它不同于普通视图只存储SQL定义物化视图实际存储数据因此查询时不需要重新执行基表的SQL。物化视图与普通视图的区别对比维度普通视图View物化视图Materialized View存储内容只存储SQL定义存储实际数据查询性能每次执行原始SQL直接查询预计算结果数据新鲜度实时基表最新取决于刷新策略占用空间不占用占用额外存储空间索引不能单独建索引可以创建索引分区不能分区可以分区2.物化视图日志Materialized View Log精确描述物化视图日志是基表上的一个内部表记录自上次刷新以来基表发生的所有DML变更INSERT/UPDATE/DELETE。它是FAST REFRESH增量刷新的前提条件。创建物化视图日志-- 创建物化视图日志必须包含ROWIDCREATEMATERIALIZEDVIEWLOGONsalesWITHROWID,SEQUENCE(product_id,region_id,sale_date,amount)INCLUDING NEWVALUES;-- 查看物化视图日志SELECTmaster,log_table,rowids,sequences,last_refresh_dateFROMdba_mview_logsWHEREmasterSALES;物化视图日志的内容SYS.SNAPLOG$_SALES 表 - SNAPTIME$$快照时间 - DMLTYPE$$操作类型IINSERT, UUPDATE, DDELETE - OLD_NEW$$旧值/新值标记 - CHANGE_VECTOR$$变更向量 - M_ROW$$被修改行的ROWID四、物化视图的刷新策略1.刷新模式的三种类型刷新模式命令关键字说明适用场景FASTREFRESH FAST增量刷新只更新变更的数据大表、少量变更COMPLETEREFRESH COMPLETE完全刷新重建整个物化视图小表、大量变更FORCEREFRESH FORCE优先FAST无法FAST则COMPLETE推荐默认选择2.刷新时机的四种选择ON COMMIT提交时刷新-- 基表COMMIT时自动刷新CREATEMATERIALIZEDVIEWmv_sales_summary REFRESH FASTONCOMMITASSELECTproduct_id,SUM(amount)FROMsalesGROUPBYproduct_id;数据实时性最高。每次COMMIT都有额外开销适合基表变更频率低且对实时性要求高的场景。ON DEMAND按需刷新-- 手动或定时刷新CREATEMATERIALIZEDVIEWmv_sales_summary REFRESH FASTONDEMANDASSELECTproduct_id,SUM(amount)FROMsalesGROUPBYproduct_id;-- 手动刷新EXECDBMS_MVIEW.REFRESH(MV_SALES_SUMMARY,F);灵活性最高。适合数据仓库、报表系统可以定时批量刷新。使用DBMS_JOB/DBMS_SCHEDULER定时刷新-- 创建定时刷新任务BEGINDBMS_SCHEDULER.CREATE_JOB(job_namerefresh_mv_sales,job_typePLSQL_BLOCK,job_actionBEGIN DBMS_MVIEW.REFRESH(MV_SALES_SUMMARY, F); END;,start_dateSYSTIMESTAMP,repeat_intervalFREQHOURLY; INTERVAL1,enabledTRUE);END;/NEVER REFRESH永不刷新-- 静态快照创建后不刷新CREATEMATERIALIZEDVIEWmv_static_snapshot NEVER REFRESHASSELECT*FROMsalesWHEREsale_dateDATE2024-01-01;3.FAST REFRESH的前置条件不是所有物化视图都支持FAST REFRESH。必须满足以下条件-- 检查物化视图是否支持FAST REFRESHEXECDBMS_MVIEW.EXPLAIN_MVIEW(MV_SALES_SUMMARY);-- 查看分析结果SELECTcapability_name,possible,related_text,msgtxtFROMmv_capabilities_tableWHEREcapability_nameLIKE%FAST%ORDERBYseq;-- 结果中possibleY表示支持FAST REFRESH支持FAST REFRESH的常见聚合函数COUNT, SUM, AVG, MIN, MAXCOUNT(*), COUNT(column)VARIANCE, STDDEV部分支持不支持FAST REFRESH的场景使用了DISTINCT某些情况。使用了CONNECT BY。使用了子查询某些情况。使用了ANALYTIC函数。五、查询重写Query Rewrite1.查询重写的本质精确描述查询重写是CBO优化器的自动机制当用户提交的SQL与物化视图的定义相匹配时CBO自动将SQL重写为查询物化视图而用户完全无感知。查询重写的三种级别级别说明EXACT MATCH查询SQL与物化视图定义完全一致PARTIAL MATCH查询SQL可以从物化视图的数据中推导GENERAL REWRITE使用更复杂的推导规则如聚合上卷2.查询重写的实例演示场景一精确匹配-- 物化视图定义CREATEMATERIALIZEDVIEWmv_sales_summaryENABLEQUERY REWRITEASSELECTproduct_id,SUM(amount)AStotal_amountFROMsalesGROUPBYproduct_id;-- 用户SQL与物化视图完全一致SELECTproduct_id,SUM(amount)FROMsalesGROUPBYproduct_id;-- CBO自动重写为SELECT * FROM mv_sales_summary;场景二部分匹配上卷-- 用户SQL查询所有产品的总和SELECTSUM(amount)FROMsales;-- CBO自动重写为SELECT SUM(total_amount) FROM mv_sales_summary;场景三部分匹配JOIN回基表-- 用户SQL查询产品名称和总金额SELECTp.product_name,SUM(s.amount)FROMsales sJOINproducts pONs.product_idp.product_idGROUPBYp.product_name;-- CBO自动重写为-- SELECT p.product_name, m.total_amount-- FROM mv_sales_summary m JOIN products p ON m.product_id p.product_id;3.启用查询重写的参数-- 会话级别启用查询重写ALTERSESSIONSETquery_rewrite_enabledTRUE;-- 系统级别启用查询重写ALTERSYSTEMSETquery_rewrite_enabledTRUE;-- 物化视图级别启用查询重写ALTERMATERIALIZEDVIEWmv_sales_summaryENABLEQUERY REWRITE;-- 查看查询重写是否生效EXPLAINPLANFORSELECTSUM(amount)FROMsales;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);-- 执行计划中出现MAT_VIEW REWRITE ACCESS FULL表示重写成功查询重写的限制会话必须设置QUERY_REWRITE_INTEGRITY为TRUSTED或STALE_TOLERATED。物化视图的数据必须足够“新鲜”基于刷新状态判断。查询SQL不能包含某些Hint如NO_REWRITE。六、物化视图的索引与分区1.为物化视图创建索引-- 物化视图上创建索引CREATEINDEXidx_mv_sales_productONmv_sales_summary(product_id);-- 创建主键需要物化视图包含主键列ALTERMATERIALIZEDVIEWmv_sales_summaryADDPRIMARYKEY(product_id);2.物化视图分区-- 创建分区物化视图CREATEMATERIALIZEDVIEWmv_sales_partitionedPARTITIONBYRANGE(sale_day)(PARTITIONp202401VALUESLESS THAN(DATE2024-02-01),PARTITIONp202402VALUESLESS THAN(DATE2024-03-01),PARTITIONp202403VALUESLESS THAN(DATE2024-04-01))REFRESH FASTONDEMANDENABLEQUERY REWRITEASSELECTproduct_id,region_id,TRUNC(sale_date)ASsale_day,SUM(amount)AStotal_amountFROMsalesGROUPBYproduct_id,region_id,TRUNC(sale_date);七、物化视图的监控与维护1.查看物化视图状态-- 查看物化视图的基本信息SELECTmview_name,container_name,refresh_mode,refresh_method,last_refresh_type,last_refresh_date,stale_since,compile_stateFROMdba_mviewsWHEREmview_nameMV_SALES_SUMMARY;-- 查看物化视图大小SELECTsegment_name,SUM(bytes)/1024/1024ASsize_mb,SUM(blocks)ASblocksFROMdba_segmentsWHEREsegment_nameMV_SALES_SUMMARYGROUPBYsegment_name;-- 查看查询重写统计SELECTname,valueFROMv$sysstatWHEREnameLIKE%rewrite%;2.物化视图失效处理-- 查看失效的物化视图SELECTmview_name,compile_state,stalenessFROMdba_mviewsWHEREcompile_stateNEEDS_COMPILE;-- 重新编译物化视图ALTERMATERIALIZEDVIEWmv_sales_summary COMPILE;-- 完全刷新物化视图EXECDBMS_MVIEW.REFRESH(MV_SALES_SUMMARY,C);八、总结记住这个“预计算”类比就够了【物化视图是提前做好的菜而不是现做的菜】普通视图一道菜的配方SQL定义。每次点菜查询都要现做执行SQL。物化视图提前做好并放在保温柜里的菜预计算的结果集。点菜时直接上菜查询物化视图速度快得多。FAST REFRESH只有食材变化时才更新那道菜而不是重做整桌菜。查询重写你点的菜虽然和保温柜里的不完全一样但厨师发现可以用保温柜里的菜稍微加工一下上卷、JOIN比从头做快。物化视图日志食材变更记录本记录哪些食材被修改了。物化视图是数据仓库和报表系统的核心优化手段。合理使用物化视图查询重写可以将复杂聚合查询的响应时间从分钟级降到秒级。但要注意刷新策略的选择——ON COMMIT保证实时性但有开销ON DEMAND灵活但数据有延迟。你在生产环境中使用过哪些类型的物化视图是否遇到过查询重写不生效的情况欢迎在评论区分享你的实战经验。