数据库应用设计优化浅谈

前言：众所周知对于 OLTP 的交易系统最重要的操作就是数据库的CRUD，数据库层面或者 SQL 优化的程度，对于整个系统的并发处理能力起到至关重要的作用。

很多朋友都会碰到这样的问题，系统运行初期，数据库层面运转非常稳定，SQL处理能力也很强。当业务发展一定阶段，SQL语句性能变差，导致应用并发处理能力下降，究其原因，可能是资源的问题、环境的问题、还可能是数据库设计的问题。

今天我们选择某个核心交易系统历史过期数据删除的逻辑优化案例。通过一些介绍了解业务发展的不同阶段，旧数据的删除逻辑是如何演进的，以及不同演进过程当中有哪些设计优化的经验。我分为以下四个部分做分享。

一、案例背景

首先我们看一下需求背景，这是一套核心的OLTP的交易系统，这套应用背后有二十几张关联的主子表，表中数据过期数据要及时清理。

开发人员用 java 写了删除的程序，我们希望每天六点业务高峰期之前完成数据清理工作。

二、问题描述

接下来我们看下问题。随着业务的推广，相应数据删除执行时间逐渐变长，从最开始只需要三十分钟，逐渐增加到两小时、五小时、甚至十几个小时。

经过分析有一张终极情况下5000万记录的子表B，删除效率比较低，属于这个问题的主要矛盾。终极情况下主表A会有2000万数据，存储七天之前到未来两天的数据，子表B关联主表的ID。子表B每天删除七百万，对于主表A来说需要从子表找到需要删除的记录。

同时因为主表A下面有一系列的子表，我们最开始设计是按串型的方式逐一删除每一张子表，再删除主表。

三、细察深剖

我们会从几个阶段来看看针对数据删除的逻辑我们是如何演进的，如何采用不同的方案进行优化。

首先阶段一业务投入初期，业务量非常有限，开发人员使用SQL通过A表筛选出来七天之前的ID记录，然后和子表B做一个关联删除B表中的数据，同时有rownum作为条件，限制一次删除交易的数据量，避免大事务的产生。

因为子查询当中只存在十天的数据，因此采用了全表扫描的执行计划，根据之前的数据量预估推测，它需要执行七百次的A表全表扫描。这个阶段来说业务量有限，数据库的配置比较高，因此执行时间，可以接受。

系统投产初期，数据量很有限，这个阶段，其实任何满足删除逻辑需求的SQL都是可以接受的，但是它的隐患非常明显，因为我们对一张大表执行了全表扫描，执行时间一定会随着数据量的增加而变长。

到了阶段二，业务量有所增加，这时候 SQL 执行时间也变长了，为了提升效率我们做了一些优化。第一个思路就是能不能少做一些事情，创建一张中间表 C ，存储的是在主表 A 中要删除数据的 ID 值，通过中间表 C 再和子表 B 做关联，它和阶段 1 的差别是什么 ？阶段 1 是做 700 次 2000 万数据的 全表 扫描，现在只需要做 700 次 200 万数据的 全表 扫描， 并将删除程序 改为并行执行， 能同时删除多张子表 。

相比阶段一，它把对于子表的删除由串型改成了并行， 同时全表扫描的数据量有所下降 ， 性能 会有一定的 提升，但是 隐患还在，因为还是对两百万的表进行了全表扫描，执行时间还是随着业务的推广逐渐增加。

到了阶段三，这个时候业务发展比较迅猛，业务量接近终级的状态了，这时候夜维执行时间超出了需求，现在的优化目标就是能不能避免子表全表扫描的操作，我们通过下面四步操作来看一下，首先在创建的中间表C中增加pkid字段，即将原先B表每次批量删除1万条的限制，推至内层循环，以让C表使用索引，避免全表扫描。例如，第一次子查询pkid的字段是1到10001，接下来就是10001到20001。

根据业务的评估，C表中一个ID，对应子表B两到三条。虽然和之前相比一次删除的数据量增加了，但是量级上基本可控。

这个阶段我们的方案通过pkid的索引避免子查询的全表扫描，虽然一次删除B表的数据多了，但相应的执行次数减少了。如果按照我们最初的分析，这种方案应该是比较完美了，可以解决我们之前的问题。

但是，现实和理想是有差距的。我们在上线当晚就报错了，从日志当中看到它抛了一个ORA-01555的错误。这是一个非常经典的错误号。原因就是在做数据检索的时候，有数据变化，我们需要从UNDO检索数据，做一致性读，如果SQL执行空间比较长，UNDO中的镜像就可能被其他事务覆盖，这时候就会抛出ORA-01555的错误。

对于C表它采用了索引范围扫描，用到了索引，但是对于子表B执行了全表扫描，正是因为全表扫描才导致SQL执行时间非常长，才让程序出现了错误。

现在问题来了，是不是因为子查询一万数据太多了？我们尝试将子查询缩小到五千、两千五以及十一个数据，它依然没有改变。偶然的是，我们尝试 1910001到1920001的参数值时，发现执行计划变了，对两表进行了索引扫描，以及嵌套循环连接，这才是我们真正需要的执行计划。

现在问题又来了，这是为什么？通过分析，pkid区间1-10001对应B表a_id的值比较无序，而且间隔较远，pkid区间1910001-1920001对应B表a_id的值比较有序，间隔较近，由此，我们联想到Oracle中Clustering Factor聚簇因子这个概念。他表示的是索引键值的排列顺序，和对应表中数据排列顺序的相近程度。

通过一个索引扫描一张表时需要访问的表的数据块的数量。反映了索引范围扫描可能带来的对整个表访问过程的IO开销情况。如果值越小(接近表的数据块数量)，说明表中数据是有序的，同一个索引叶子结点上相邻的索引键值，对应的表中记录可能位于相同的数据块上，(表是按照索引字段的顺序存储)，相应根据索引，回表检索数据，就会消耗更少的物理IO。

如果值越大(接近表的行数)，说明表中数据是无序的，同一个索引叶子节点上相邻的索引键值，对应的表中记录就可能位于不同的数据块上，(表不是按照索引字段的顺序存储)，相应根据索引，回表检索数据，就会消耗更多的物理IO。

我们通过图示，可以更加形象的理解，这张图是聚簇因子比较小的示意图，我们看一个索引叶子节点上，索引键值对应到表的记录，可能位于相同的数据块，组织有序。

这张图是聚簇因子比较大的示意图，同一个索引叶子结点上，相邻的索引键值，对应表中的记录，可能位于不同的恶数据块，组织无序。相同一次检索，回表需要消耗更多的物理IO。

我们再用图示，看下pkid在1-10001和1910001-1920001两个区间，可能的情况，1-10001对应B表待删除a_id索引，可能位于不同的数据块上，1910001-1920001对应B表待删除a_id索引，可能位于有限个相邻的数据块，因此前者检索B表的时候，有可能全表扫描的成本，就要低于索引范围扫描，而后者索引范围扫描的成本，低于全表扫描，所以pkid区间不同，导致B表扫描成本的不同，因此选择了不同的执行计划。

我们知道了原因，应该如何改造？很明显，目标是让 B 表一次删除的 a_id 尽量接近。原先 C 表构造，只是根据时间 ，将符合条件的 A 表记录，随机插入 C 表，现在我们加上 order by id ，保证插入 C 表的时候，是按照 id 字段 排序 存储，相当于按序构造了表 C 。

从优化效果来看 ，子表 B 在优化之前都需要两个小时到三个小时的删除时间，优化之后，基本上五十分钟就可以完成数据的删除工作。

子表B是属于优化主要矛盾，解决主要矛盾之后，相应夜维的总体执行时间也有所下降，原先需要四个小时以上的时间完成清理，现在仅需要两个半小时时间就可以完成所有数据清的理工作。

上面是我们对数据删除的演进过程进行了说明。

四、总结回顾

最后我们简单总结回顾一下问题。首先我们投产初期，只要 SQL 语句符合需求，在执行时间上就可以。之后全表扫描的隐患就会逐渐暴露出来，我们通过将串行改为并行，同时增加了 pkid 字段，旨在使用索引。

但是，实际过程中pkid的数据分布变成了乱序的，进而导致使用索引扫描的成本值非常高，无法使用索引，还出现了ORA-01556的报错。明确了原因之后解决方案也很简单。我们通过指定排序构造中间表，来降低索引访问的成本。

除了这些优化操作外，或许可能有其他的方法，比如使用引用分区特性、删除外键约束并发执行，其实对于一些优化的工作，方法可能不止一种，殊途同归，选择你最熟悉、最易操作的执行就好，毕竟技术是为业务服务的。

通过案例我们也做了一些思考。

第一个就是好架构不是设计出来的，而是演进来的，对于数据库应用的逻辑一样如此，对于不同的阶段，可能会有不同的逻辑调整。

第二点，在优化过程中，首先要抓住主要矛盾，不能眉毛胡子一把抓，要明确主要的矛盾，作为首要的目标去解决。

第三个，就是无论对于开发人员还是架构师来说，我们不能把数据库当做一个黑盒，我们理解越全面、越深入，越可以理解和发现问题。

最后一个就是对于一些性能隐患，是可以提前避免的，例如大表的全表扫描，它的执行时间，一定会随着数据量的增加而增长。

咱们这次会议的主题，是自动化运维、智能运维。对于这些隐患，现在业界也有了像SQL审核 工具 这种产品，无论是开源的，还是商业的，通过自动化的方式，提前预警。

我们的团队，也在做这方面的探索，正在自研我们自己的数据库智能审核分析平台 Sherlock ，希望他可以像神探夏洛克一样，帮助我们找出数据库开发的隐患，辅助开发人员、 DBA ，进行数据库开发的工作，践行 DevOps ，希望未来有机会，分享出来这方面的工作。以上就是我今天的分享。感谢各位。

AIOps，DevOps？

运维行业的风向标

想知道

2019 运维行业必关注的行业趋势？

4.12-13 ，我们在深圳等您

点击阅读原文，了解 GOPS 2019 · 深圳站更多精彩