ITPub博客

首页 > 数据库 > Oracle > 一个清理和查询都要兼顾的简单方案

一个清理和查询都要兼顾的简单方案

原创 Oracle 作者:jeanron100 时间:2016-01-04 23:32:29 0 删除 编辑
最近和开发应用的同学在讨论一个需求,目前他们碰到了一些性能问题,想让我来看看是否能够从数据库的角度有一些解决方案。
假设表为消费记录,简称service_details,这是一个普通表,目前这个表数据量很大,需要定期去删除一些过期的数据,至于过期的标准先暂时按照两个星期来算。可见这是一个需求变化极快的场景。
每天的新增数据情况为几百万,那么两个星期的数据量大概就在5千万至一亿左右。
为什么现在考虑要改进这个表呢,有一个主要的原因是他们碰到了性能瓶颈,根据他们的业务需求,他们需要每分钟都定时去查询这个表,然后和其它的几个表做一个关联,然后把查询的结果数据显示在前端页面作为一个实时的展现,是的,是每分钟都会运行。如果数据量达到了一定的程度,查询就会慢,旧数据也越来越多,如果去删除这部分的数据,目前是根据时间条件去删除,使用的是delete方式,而且目前在时间字段上也存在索引,按照这种情况似乎索引让问题也没有什么改善,前段时间他们做一个实时的查询显示,结果刚好有一个旧数据删除的任务,结果删除的操作持续时间比较长,结果其它的查询都会挂起,新增数据的操作也被阻塞。结果一个查询没做出来,第二分钟的查询已经照例开始执行了,然后第三分钟,最后系统就被拖的很慢。当然最后还是找我们临时解决,kill了那些session得以临时释放,而对于他们来说,这种删除操作还是比较昂贵的,每次尝试删除都有些提心吊胆。
所以大体了解了他们的需求,其实主要在一下几个点上,
首先这个表是一个普通表,表中的数据变化较快,需要删除旧数据。
目前的查询逻辑是1分钟触发一次,当然听起来在逻辑上似乎还是有改进的地方,不过可以看出执行频率还是相对比较固定而且频繁,一旦出现了某个查询缓慢或者阻塞,就会马上出现系统缓慢的情况。
目前的数据清理工作比较痛苦,想删除数据,但是感觉删除的操作持续时间较长,不删除旧数据较多,也影响查询。
其实我说到这里,大家应该也猜出来了,我最后推荐他们使用分区表。
推荐理由如下:
首先对于主要的痛点是删除操作,那么最快的删除肯定不是delete,而是truncate,但是不能truncate全表啊,我们可以truncate分区
然后根据删除的策略,是保留2个星期的数据,查询的时候是根据时间字段来查询,所以完全可以考虑采用分区字段作为分区键值,然后通过truncate partition的方式清理分区,然后查询时直接根据时间字段可以直接访问指定的分区,而不会直接访问全表的数据,扫描100个分区和扫描1个分区,其实基数完全不同。

当然还有一个主要的问题就是分区的维护,开发的同事觉得这种方式听起来不错,不过对于新增分区还是存在顾虑,比如在这个项目持续了很长时间,可能只有我们知道这个逻辑,一旦他们撤出项目了,或者没有关注忘记了,分区没有继续增加维护到时候就比较难处理了,其实对于这点来说,DBA是需要付出一些额外的工作的。当然对于解决这个问题而言都是值得的。

假设保留了6天的数据,比如到第9天的时候,就会是右边的蓝色部分
当然也不是只是说说,互联网公司的实践能力就是强,今天提完,明天就马上付诸实践,从现在的情况来看,查询语句是只会扫描某一个分区,不过还是遇到了一些小小的问题,那就是关于统计信息。
这个怎么理解呢。比如分区p_20160105是存放2016年1月6号的数据,但是在1月5号肯定是没有任何数据的,直到1月6号的时候才会生成大量的数据,所以按照Oracle默认的Job去收集统计信息,收集统计信息之后,1月6号的分区统计信息还是0,在执行sql的时候还是可能出现一些问题。这个时候有一个持续的改进方法,一个就是保证统计信息尽可能新,准确,另一个思路就是保证执行计划的执行路径是最优最稳定的。这样的话就有几个额外的附件任务,一个就是可以考虑对于统计信息进行分区级的收集,当然对于某一个特定分区也是几秒钟即可搞定,而且也不会影响在线查询,另外一个思路就是稳定执行计划,可以考虑使用sql profile来做。
上面的方式也都已经付诸实践,还有一个方法,就是根据每天的数据生成量,直接设定一个固定的数据统计信息,比如每天500万的数据,就设置这个分区有500万的数据,也让优化器吃一颗定心丸。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1971212/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
技术文章每天更新,阵地已转移到微信公众号端。 公众号:jianrong-notes

注册时间:2012-05-14

  • 博文量
    1498
  • 访问量
    14638477