一个清理和查询都要兼顾的简单方案(r7笔记第68天)

最近和开发应用的同学在讨论一个需求，目前他们碰到了一些性能问题，想让我来看看是否能够从数据库的角度有一些解决方案。 假设表为消费记录，简称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万的数据，也让优化器吃一颗定心丸。