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

原创 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),收集表的统计信息,重建索引的操作。

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

相关文章推荐

oracle查看回滚段信息

select rownum,        sys.dba_rollback_segs.segment_name Name,        v$rollstat.extents Extents, ...

把mysql的一些状态输出到文件里显示

有些状态在 mysql里边不好看,比如show engine innodb status\G在mysql里就很难看,找信息也不好找,所以最好输出到外部文件里,比如txt文件,不废话了直接上代码    ...

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

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

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

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

提高AJAX客户端响应速度

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

OpenStack+Ceph存储空间回收

在OpenStack的部署中,开源分布式存储Ceph大行其道,Ceph支持Thinprovision,但是用户删除文件以后,在ceph中这部分空间并没有真正释放,这篇博文就讲述Linux的Discar...
  • wytdahu
  • wytdahu
  • 2015年09月08日 11:39
  • 2102

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

  • 2010年07月01日 13:55
  • 62KB
  • 下载

MySQL表空间回收

MySQL释放空间
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库优化之响应速度优化和空间回收
举报原因:
原因补充:

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