Real World Performance 经典性能优化案例-索引竞争

编辑手记：Real World Performance（RWP）团队是个天才的性能优化团队，不断的寻找和创造新的方法分析诊断当今世界业务系统的性能。在他们眼里，一切性能问题都有解决方案，并且能找到最佳的解决方案。

今天我们要分享的是关于优化索引竞争的案例。

索引竞争可能是我们在数据库中最常见的竞争问题之一，在AWR报告中，enq: TX - index contention 是非常常见的一个等待事件，顾名思义，Index contention是指索引竞争，事实上专指由于索引分裂产生的竞争等待。

原理：

当事务修改索引中的数据时,如果相关索引块没有足够的空间保存数据,就会发生索引块的分裂（split）,在分裂的过程中前台进程需要等待分裂完成之后才能继续操作。如果此时其他会话也要修改这个索引块的数据,那么将会出现索引块的竞争，等待以end: TX- index contention 的形式体现。一般索引块的分裂持有资源和释放非常迅速,并不会对数据库造成严重的性能影响，但是对表操作并发量很大的情况下可能导致严重的竞争。

最常见的索引竞争一般发生在主键索引上，主键值是从序列（sequence）中获取的当事务量比较大的时候，每个事务都会生成一条新的记录，每条记录都要获得一个新的序列号，因为从sequence中取出的值是单向递增的，当向索引中插入数据，并且维护索引结构的时候，不得不一直走向索引树最右边的分支，因此对于每一个操作，都会想要维护索引中最右边的叶节点。那么所有的操作者都会关注同一块内存，希望能够维护这块内存，这就是一种典型的竞争形式。

但是实际情况是，在同一时间，只能有一个人能够修改这块内存，因此，当一个人在修改的时候，其他所有想修改的人只能处于等待状态。这就是竞争。

关于这个话题，接下来我们将通过实验说明如何减少这类索引竞争。

背景：在一个游戏的应用上希望记录每个用户的登录和操作情况，因此希望创建基于主键的索引，我们先后尝试了以下几种索引。

首先来看在没有索引的情况下系统的运行状况，系统运行平稳并且性能良好。

1、B树索引

最初系统是在没有索引的条件下运行的，之后在主键上增加B树索引，而索引值是通过sequence生成的，做插入操作时，会发现对性能产生的严重的影响，响应时间变长，吞吐量直线下降，CPU利用率也从60%降到了20%。

这说明B树索引并不适合。

2、反向键索引

之后我们尝试创建反向键索引，发现性能问题立即得到了改善。这样表面上看起来是解决了我们的问题，但实际上，使用反向键索引也是有问题的。

反向键索引的挑战在于，索引会变得越来越大，可能到最后SGA的buffer Cache都放不下。这时候我们开始使用IO维护索引。

我们通过实验来说明

首先我们来创建一张表，插入历史数据，然后使用反向键的情况下运行两个星期，我们发现，很快性能又开始下降，甚至比之前在B树索引的情况下更糟糕，这时候我们通过AWR或者ASH 报告会发现，这时候最主要的等待是与磁盘读取相关的。

要做一个物理IO把应用中用到的索引块重新读到buffer Cache中，而且很可能这个索引非常大，以至于它把其他所有表或者索引的数据都挤出了内存，因此这时候，不仅仅是该应用本身的性能会下降，而且运行在同一实例中的其他应用或者程序的性能都会下降。因为他们同样要做更多的IO。

而且随着时间的迁移，我们看到后面的性能比之前仅仅存在内存竞争时的情况更加糟糕，我们实际上引入了不同类型的竞争，由于上内存的竞争所产生的开销会比磁盘竞争引起的开销小一些。事实上我们所做的只是把导致系统变慢的一种等待事件替换成了另一种更慢的等待而已。

应用的数据无法获取内存，因为访问内存结构的时间是微秒级的，当获取不到内存，应用就需要从磁盘上读取数据，而磁盘IO的时间是毫秒级别的，也就是说从磁盘读取数据所花的时间是内存读取的一千倍，因此此时很明显看到性能比仅仅存在内存竞争的时候要差很多。

3、索引分区

B树索引会导致内存的竞争和等待，而反向键索引则会导致太多的IO，接下来微秒尝试第三种方案：索引分区，通过hash将索引分成一个一个小块，这样竞争就不会聚集在最右边的节点上，也就是说，我们是否可以用一些小的竞争代替右边节点上的集中的热点竞争，我们将hash分区索引应用到这个问题上，看看能不能减少IO，同时让系统的响应时间和CPU使用率回归到正常的水平。

从某种角度上来说，我们已经hash分区索引实现了目标，在单实例上，通过hash分区索引在一定程度上缓解了插入数据的竞争问题，但我们组在进行性能优化的过程中，总是很谨慎使用hash 分区索引，原因有很多。

1、在接下来的测试中我们会发现，hash分区索引并不总是有效的 2、要对原来没有分区的表进行分区是有风险的，比如可能会引起执行计划改变，一些非插入的操作的性能下降等等，会存在一系列的连带伤害。

首先我们来做一个实验，将之前的单实例增加到两个节点，连接数和处理的事务数都增加到原来的两倍，硬件配置增加到两倍，同样使用hash分区索引，我们来看看性能和吞吐量会怎样。

当我们看到第二个节点起来之后，观察到第一个节点的CPU使用率有所降低，但单纯从吞吐量来看基本上是看不出来性能的变化的。接下来我们看到响应时间变长了，出现了一些新的等待事件，都是一些GC buffer的事件。

也就是说此时，我们并没有遇到串行化处理的问题，因为通过hash分区索引我们使得负载分散到了索引的所有叶节点上了，但我们此时却遇到了扩展的问题，之前我们说过，这根访问数据块的时间有关，如果你想修改的叶节点所在的数据块正好在当前节点的buffer Cache中，那么修改的时间就是微秒级的，但如果你要修改的数据在其他节点上，那就是说通过网络访问需要一毫秒的时间，随着节点的扩展，这些数据块存在于当前实例buffer Cache的可能性就会降低，两个节点，50%，三个节点33%，

因此这种方案不能进行扩展，也不可取。

因此我们一直在寻找一种方法，希望能够从应用层就把竞争的可能性消除。既能解决单节点的竞争问题，又能在扩展中不带来新的问题，这就需要保证缓存的相关性，让数据所在的实例恰好是会被访问的实例。

那么最佳的解决方案会是怎样的呢？

跟所有RWP的解决方案一样，我们并不推荐通过各种配置或者参数的调整来解决问题，我们会扩展问题的领域，希望把问题上升到应用层去解决，对于案例中的索引竞争的问题，如果我们能控制如何生成代理主键，我们就能把这些特征放入到生成的主键中

这样不仅能够保证得到较好的缓存相关度，从而使RAC可扩展，而且可以把主键分散开，这样在单实例上也不会出现竞争。

所以关于索引竞争，我们面临两个挑战，

• 一是实例间的竞争或者说扩展性问题
• 二是单节点间的竞争

因此我们考虑生成一个只能主键，智能主键常常需要找到应用代码中的某一行，弄清楚我们要如何生成这一串字节才能确保不会出现竞争。

首先要考虑的是可以使用实例号作为主键号的开头，这样插入数据的时候就会保存在树节点的一边，也正是这些数据应该被保存到的实例上，这样就可以建立与插入操作相关的缓存相关性。

当我们在访问的时候能够准确定位数据所在的实例之后，第二个要考虑的问题就是，访问同一个实例上数据的时候不会竞争同一块内存，

我们考虑，如果说智能主键的中间部分如果是对进程号某种方式取余，这样就把对索引的维护分散到同一实例的多个内存块上去，而智能主键的最后一部分是sequence的本身，这样可以保证引用和完整性，确保每一行都是唯一的。

因此最终智能主键的组成是：实例ID-进程号取余-序列号

接下来我们通过实验来看一下，在使用智能主键的情况下，发现系统的响应时间减少，其他等待事件消失，CPU利用率提高，并且只有CPU在占用时间。跟最初系统没有产生竞争的情况下的性能一样。

通过自定义智能主键，很好地避免了传统的索引方案的不足，在不影响性能的情况下有效实现了业务的需求。

3月28日，Oracle RWP 性能之旅，北京站再度来袭！Andrew Holdsworth 和 Graham Wood 将带领大家在一天之内，探秘 OLTP 并分享现实世界中性能表现及监控案例，这是继2015年8月 Tom 大师归隐之后，RWP 团队第一次公开亮相！