hive物化视图表元数据 mysql oracle物化视图优缺点

转载

mob64ca13fa6a3c 2024-01-12 14:47:59

文章标签 hive物化视图表元数据 mysql oracle 物化视图 SQL 数据 文章分类 Hive 大数据

需求描述

一个需求的相关查询，使用索引优化到了10秒左右，但客户仍然需要希望对性能做进一步优化。（需求为：rohs平台的总清单）

对业务经过分析发现：多表连接数超过20且都是必要的无法精简。

用户可以接受查询结果非最新（每隔一段时间更新），故考虑使用物化视图来做进一步优化。

物化视图优缺点

优点

可以显著提升查询性能。物化视图其实可以看做一种特殊的物理表，查询时不会去访问基础表而是直接访问物化视图表。也可以给物化视图建立索引进一步提升性能。

相对于人工建表并维护其中的数据，物化视图可以省去这部分工作量。它支持多种数据自动刷新方式以及多种刷新触发条件。支持基表数据有变动自动同步到对应的物化视图。

物化视图有视图的优点，视图可以简化用户的操作，可以隐藏数据表之间的关系（引申出来能够对机密数据提供安全保护），可以对重构数据库提供一定的逻辑独立性等等。

缺点

数据及时性降低。不适用于对数据及时性以及准确性要求较高的业务场景。

虽然物化视图支持多种自动刷新方式。但查询语句越复杂用到的基表越多，刷新需要的时间也会越长。

SQL参考

物化视图

--创建物化视图
CREATE MATERIALIZED VIEW PUR_ITEM_COMPREHENSIVE
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('2022-01-18 01:00:00', 'YYYY-MM-DD HH24:MI:SS')
NEXT SYSDATE + 1/(24*6)
DISABLE QUERY REWRITE
AS
--视图sql,创建以后也可以单独修改.
select a.*, b.*, c.*
from tableA a
inner join tableB b on a.id = b.id
inner join tableC c on b.id = c.id

--物化视图数据查询
select * from PUR_ITEM_COMPREHENSIVE;

--物化视图手动刷新
exec dbms_mview.refresh('PUR_ITEM_COMPREHENSIVE');

--物化视图刷新日志 2022-01-19 14:49:03
SELECT * FROM dba_mview_analysis WHERE owner='AGILE';

Job定时任务

主要用于定时刷新物化视图。物化视图自己也可以配置刷新规则，但目前还没有试出来。有兴趣的童鞋也可以研究下。（案例SQL是每隔10分钟刷新一次，可根据实际情况修改）

--给用户赋权
Grant MANAGE SCHEDULER To agile;
grant create job to agile;

--系统参数修改
show parameter job;
alter system set job_queue_processes=10;

--创建定时任务刷新物化视图
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"AGILE"."PUR_JOB_REFRESH_MVIEW"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
 dbms_mview.refresh(''PUR_ITEM_COMPREHENSIVE'');
end;',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2022-01-19 16:00:04.000000000 ASIA/SHANGHAI','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"AGILE"."PUR_JOB_REFRESH_MVIEW"', 
             attribute => 'restartable', value => TRUE);
     
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"AGILE"."PUR_JOB_REFRESH_MVIEW"', 
             attribute => 'store_output', value => TRUE);
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"AGILE"."PUR_JOB_REFRESH_MVIEW"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);

    DBMS_SCHEDULER.enable(
             name => '"AGILE"."PUR_JOB_REFRESH_MVIEW"');
END;

--定时任务工具表
-- job信息
select * from dba_scheduler_jobs where owner = 'AGILE'; 
--  job日志
select * from dba_Scheduler_Job_Log where owner = 'AGILE';
-- job运行日志
select * from dba_scheduler_job_run_details where owner = 'AGILE';
--正在运行的job
select * from user_scheduler_running_jobs where owner = 'AGILE';