单表七千六百万数据量(oracle)进行实时汇总,sql很慢客户不能忍一下,该怎么办?

简介: 单表七千六百万数据量(oracle)进行实时汇总,sql很慢客户不能忍一下,该怎么办?

问题背景

描述

某省系统界面需要展示业务指标,该指标来自一张表如下图所示,数据量巨大。旧方案的统计逻辑是:java cron定时任务执行sql汇总插入汇总表,汇总频率一小时一次。


在进行定时汇总的时候,由于需要统计历史所有数据,尽管sql已经优化避免全表扫描但是查询sql执行时间很久。


导致:界面长时间没数据。


限制因素

  • 数据库服务器配置一般,普通Pc配置(16g内存,500g硬盘,cpu不清楚),没有ssd;
  • 上集群、大数据分析、hadoop?想多了,估计没人会(除了我,这里不谦虚了),运维成本比较高,客户也不会给那么多机器;其实我还是很想搞这个实时计算的。
  • 表没有分区,如果分区可能会好点,重建表进行表分区,但是ogg可能要重新搭建;
  • 不想改架构,把数据分表会好点,但是查询逻辑要变化;

7.png


结合问题场景分析

每一小时都要把近几年的历史数据统计一遍,历史数据又没变化,真的有必要?

该指标也不需要做到有秒级的延迟,何况提供数据的友商一天才给2次数据,数据实时性也不高;

解决方案oracle物理视图

**大道至简:用空间换时间;这是数据结构与算法里面常见的解决方案。 **

也即:把不变动的历史历史数据都加工后存储,定时汇总任务直接查加工后的数据,数据量大幅降低,提升查询速度。虽然数据不是最新的,但是至少有数据可以容忍。


物化视图概述

Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。


物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。


物化视图特点

  • 使用物化视图的目的是为了提高查询性能;
  • 物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性;
  • 物化视图需要占用存储空间;
  • 当基表发生变化时,物化视图也应当刷新。
  • 这里我定的是物化视图一天更新一次。

创建语法

CREATE MATERIALIZED VIEW XX
  REFRESH  [[fast | complete | force] 
         [on demand | commit] 
         [start with date] 
         [next date] 
         [with {primary key | rowid}] 
       ]
      [ENABLE | DISABLE] QUERY REWRITE

Refresh 刷新子句

描述 当基表发生了DML操作后,实体化视图何时采用哪种方式和基表进行同步 。


  • 取值 FAST 采用增量刷新,只刷新自上次刷新以后进行的修改 ;
  • COMPLETE 对整个实体化视图进行完全的刷新 ;
  • FORCE(默认) Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用Fast方式,否则采用Complete的方式,Force选项是默认选项;
  • ON DEMAND(默认) 实体化视图在用户需要的时候进行刷新,可以手工通过 DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新 ;
  • ON COMMIT 实体化视图在对基表的DML操作提交的同时进行刷新 ;

**START WITH 第一次刷新时间 **

**

**NEXT 刷新时间间隔 **

**

**WITH PRIMARY KEY(默认) **

生成主键实体化视图,也就是说实体化视图是基于表的主键,而不是ROWID(对应于ROWID子句)。 为了生成PRIMARY KEY子句,应该在表上定义主键,否则应该用基于ROWID的实体化视图。主键实体化视图允许识别实体化视图表而不影响实体化视图增量刷新的可用性


REWRITE 字句

**

包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。

分别指出创建的实体化视图是否支持查询重写。查询重写是指当对实体化视图的基表进行查询时,Oracle会自动判断能否通过查询实体化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的实体化视图中读取数据 默认 DISABLE QUERY REWRITE

demo

 CREATE MATERIALIZED VIEW bm_dqd_znzdhs
    refresh force on demand start with sysdate next trunc(sysdate+1)
as
select ksdm swjgdm ,
       count(distinct djxh) znzdhs,
       to_date(to_char(lrrq, 'YYYY-MM-DD'), 'YYYY-MM-DD') lrrq
  from t_temp_ywtj_mx r
 where ywlx = '自助' and ksdm is not null
 group by ksdm, to_char(lrrq, 'YYYY-MM-DD');

参考

https://www.cnblogs.com/andy-wcl/p/3430995.html


感谢关注!


相关文章
|
SQL 缓存 监控
一个慢SQL引起的惨案
突然接到客服那边的消息,说接到大量用户投诉,页面打不开了。小齐心里一咯噔,最近就自己发布了新代码,加了一个新功能,不会是那部分代码出问题了吧?!!
373 0
|
SQL Oracle 关系型数据库
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一第3章 查 询 转 换
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第3章,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1100 0
|
7月前
|
SQL 算法 安全
教你几招快速创建MySQL千万级数据,学习上百种优化技巧
如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数据优化个毛线啊。生产库里数据多,但谁敢直接在生产环境动手啊,想被提前优化吗?
|
9月前
|
SQL Oracle 安全
Oracle优化01-引起数据库性能问题的因素
Oracle优化01-引起数据库性能问题的因素
110 0
|
11月前
|
SQL
【SQL开发实战技巧】系列(七):从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
本篇文章讲解的主要内容是:***如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值,否则查询结果为空。***
【SQL开发实战技巧】系列(七):从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
|
1月前
|
存储 安全 关系型数据库
4个MySQL优化工具AWR,帮你准确定位数据库瓶颈!
4个MySQL优化工具AWR,帮你准确定位数据库瓶颈!
22 0