数据库优化之响应速度优化和空间回收

原创 2016年08月30日 15:49:00


 

 

系统环境

硬件环境:

服务器类型

虚拟机

Cpu

Inter (R) Xeon E7-4870 /6核

内存

8G

 

软件环境

操作系统

Windows Server 2008  R2  Enterprise

数据库版本

Oracle 10g 10.2.0.5.0

备份机制

归档模式+rman全量备份

一、问题现象

1、前台报表展现异常缓慢,出现无法正常展现曲线的情况。

2、 后台不加条件查询该表数据,执行时间超长4分钟以上。

3、 事实表数据超大3.3亿记录,历史数据查询用时响应在几小时以上。

二、优化过程

1、备份历史数据

将历史数据按年备份到备份表,根据业务特点,报表展现给用户的一般是近一年的数据,事实表中不建议保留太久的历史数据。本例中事实表(r_data)保留了2011年1月1日至今的数据,导致存储了3.3亿条数据。

向领导请示后约定事实表(r_data)保留近一年数据供查询,其余数据清理至历史表按照年份创建备份表r_data_20130713\ r_data_20140713\ r_data_20150713,2.7亿条记录总计耗时约5分钟。

insert/* +Append*/into r_data_20160713select * from r_datawhere r_date <=to_date('2013/07/1300:00:00','yyyy/mm/ddhh24:mi:ss');commit;

insert/* +Append */into r_data_20140713select * from r_datawhere r_date <=to_date('2014/07/1300:00:00','yyyy/mm/ddhh24:mi:ss');commit;

insert/* +Append */into r_data_20150713select * from r_datawhere r_date <=to_date('2015/07/1300:00:00','yyyy/mm/ddhh24:mi:ss');commit;

注:为避免产生大量归档日志故选择了append 模式直接插入,节省时间,加速操作。

2、删除历史数据

 删除刚刚备份的2.7条记录,此时根据时间分批删除,案例中删除为按月删除,每次删除1个月的数据500w条左右耗时5分30s左右。

 altertable R_datanologging;--在此模式下轨当日志还是会产生

 delete from r_datawhere r_date <=to_date('2015/07/1300:00:00','yyyy/mm/ddhh24:mi:ss') ;

注:(1)、归档模式下大量删除将产生大量归档,所以这里采用分批删除的方法,保证归档日志不至于写爆存储。

(2)、由于r_data写入较频繁,长时间的删除容易产生死锁现象,分批删除时如果发现可以及时处理。

3、回收表占用空间

收缩表需要开启表的行迁移模式,此时记录的rowid会发生变化,shrink操作可以释放存储碎片,释放存储空间。

alter table r_data enable row movement;--开启行迁移

alter table r_datashrinkspace;--收缩表数据

注:删除的记录不会释放存储空间,如果对一张表执行了大量的delete操作,最好在适当时候收缩下表。

4、重建索引

收缩表以后,记录的rowid发生变化,此时需要重建索引,保证索引的有效

alterindex PK_R_DATArebuildonline;

alterindex IDX_R_DATA_DATErebuildonline;

注:如果不想影响表的dml操作,最好加上online 参数。

5、收集统计信息

对表的统计信息进行收集,可以查看优化结果,是优化生效。

exec dbms_stats.gather_table_stats('DATA_TRAN','R_DATA');--收集表的统计信息

三、优化结果

 

优化前

优化后

表名

R_data

R_data

记录数

33200万条

5100万条

占用空间

8.5G

1.51G

查询时间

260.216s

0.218s

表类型

普通表

分区表

 

四、优化总结

新系统上线时,往往响应速度很快,随着系统使用日久,数据量增大,导致部分报表或者数据加载异常缓慢,此时从数据角度可以进行如下优化。

1、 创建分区表,oracle11g分区表可以自动创建分区,分区表可以自动均衡数据,加快查询。本例中最后将r_data表转化成为了分区表,按照时间范围分区(为减少篇幅转换操作暂时未列)。

2、 建立合适的索引,合适的索引可以大大加速查询速度。

3、 如果事实表存储较为久远的数据,注意定时备份清理历史数据,可以使系统响应速度大大加快。

4、 数据删除工作量远大于数据插入。本例中插入2.7亿数据用时5分钟左右的样子,但每删除500万条记录需要5分钟,删除操作总计耗时2天,产生了约360G的归档日志。并且删除会产生大量的存储碎片,删除完所有记录以后还需要进行收缩表(shrink),收集表的统计信息,重建索引的操作。

版权声明:本文为博主原创文章,未经博主允许不得转载。

数据库查询优化方案(处理上百万级记录如何提高处理查询速度)

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎...
  • youwenyouwen
  • youwenyouwen
  • 2014年11月18日 15:07
  • 1337

性能优化之接口优化(spring/java/http接口)

1 优化工具与措施 2 优化标准 3 发现优化点并优化 4 放水接口 5 子业务相互独立 优化工具与措施 CAT(Central Application Tracking):是基于Java开...
  • blogzlh
  • blogzlh
  • 2016年07月20日 17:07
  • 6644

Android性能优化第(八)篇---App启动速度优化上

应用的启动速度缓慢这是很多开发者都遇到的一个问题,比如启动缓慢导致的黑屏,白屏问题,大部分的答案都是做一个透明的主题,或者是做一个Splash界面,但是这并没有从根本上解决这个问题。那么如何从根本上解...
  • u013263323
  • u013263323
  • 2016年12月21日 11:16
  • 1620

Android性能优化之提高应用响应速度(了解ViewStub和推迟视图的初始化)

为什么需要推迟视图初始化 这里谈谈为什么要推迟视图的初始化. 假设这样一个情况, 如果手机的界面包含大量的视图组件,而且数据大多都来源于服务器. 这就意味着一进入这个界面,应用就会在onCreate...
  • stzy00
  • stzy00
  • 2015年04月26日 22:43
  • 1665

性能优化:RxJava异步响应式编程提升响应速度

本文通过引入RxJava,通过异步的方式,以短短30行的代码,解决了一个常见的性能问题,提升界面的响应速度。作为例子,请看下图:单线程的问题上图中,ID,Status,Runtime数据的获取都需要时...
  • liuwenzhe2008
  • liuwenzhe2008
  • 2016年09月24日 22:37
  • 1078

MySQL表空间回收

MySQL释放空间
  • php521php
  • php521php
  • 2015年04月18日 12:58
  • 17982

提高AJAX客户端响应速度

  • 2010年05月10日 18:49
  • 40KB
  • 下载

提升JSP页面响应速度的七大秘籍绝招

  • 2012年04月22日 18:57
  • 60KB
  • 下载

提升JSP页面响应速度的七大秘籍绝招.txt

  • 2010年07月22日 22:17
  • 7KB
  • 下载

设置键盘输入响应速度的资源

  • 2010年07月01日 13:55
  • 62KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库优化之响应速度优化和空间回收
举报原因:
原因补充:

(最多只允许输入30个字)