巧用索引降序扫描解决性能问题

这个案例是笔者帮助一个网友解决的问题，某银行须记录其网上银行交易中每笔交易操作者的IP地址以及所属地理位置。很显然，会有一个表记录某个IP地址段属于什么地址。

存在性能问题的SQL如下：

select flg from ip_libwhere ip_addr_start<='192.168.0.5' and ip_addr_end>='192.168.0.5'

这里要查询的IP地址是192.168.0.5（当然实际上不是这个地址，这里只是举例），其执行计划如下：

|SELECT STATEMENT|----- 1137804223 ----| | | |
|TABLE ACCESS BY INDEX ROWID |IP_LIB | | | |
| INDEX RANGE SCAN |IDX_IP_LIB_IP_START_ | | | |
------------------------------------------------

由于对表缺乏分析，所以没办法看到执行计划中的成本及Cardinality值。但是实际返回的数据最多1条，这是绝大多数的情况，少数情况下会返回0条记录。SQL执行所需要的逻辑读少则400次，多则将近3 000次。由于每天这个SQL执行量在数十万次，产生的逻辑读总量还是非常高的。而这条SQL每次最多返回1条数据，而产生这么多的逻辑读，显然存在着优化的余地。

在上面显示的执行计划中，IDX_IP_LIB_IP_START_这个索引，是在IP_ADDR_START和IP_ADDR_END这两个列上的复合索引。SQL执行产生的逻辑读高，就在于扫描这个索引的叶节点块时产生了大量的逻辑读。

我们来详细分析一下这个过程：

1. 首先，由于有IP_ADDR_START<='192.168.0.5'这个条件，Oracle会从索引的Root Block、Branch Block找到最左边的叶节点块，也就是整个索引中，具有最小值的那个叶节点块。

2. 从索引最左边的叶节点块开始，向右扫描叶节点块，由于索引中包括IP_ADDR_END值，所以在扫描过程中，会同时检查IP_ADDR_END值，如果满足条件IP_ADDR_END>='192.168.0.5'，那么就会取得该索引值对应的ROWID，并根据ROWID，从表中取得需要的数据'flg'列。

3. 持续进行第2步的操作，一直扫描到IP_ADDR_START<='192.168.0.5'这个条件不再满足时为止。

实际上由于IP地址数据的特殊性，地址对总是如下的形式：

192.168.0.5 192.168.0.5
192.168.0.6 192.168.0.10

同时从数据上保证了不会出现下面的数据：

192.168.0.1 192.168.0.10
192.168.0.2 192.168.0.2

SQL执行返回的数据，总是在索引叶节点块中最后被扫描到的数据。

我们换个思路，如果能够让这条SQL执行时，进行倒序扫描，也就是从右向左，结果会如何？下面把SQL改写一下：

select * from (selectflg from ip_lib where ip_addr_start<='192.168.0.5' and ip_addr_end>='192.168.0.5'order by ip_addr_start desc ) where rownum=1;

这样，使得SQL在执行时，会根据IP_ADDR_START<='192.168.0.5'这个条件，定位到索引中间某个叶节点块，然后从右向左扫描，由于数据上保证了满足条件的数据是会被最先扫描到的行，因此我们只取1 行。根据提供此问题的网友最后的反馈，SQL修改后，性能问题得到解决，实际执行时只有几个逻辑读。

当然在这个案例中，我们可以将索引设为倒序索引，即

CREATE INDEX XXX ON IP_LIB (IP_ADDR_START DESC，IP_ADDR_END)

而原来的SQL改为：

Select * from (selectflg from ip_lib where ip_addr_start<='192.168.0.5' and ip_addr_end>='192.168.0.5')where rownum=1;

这样也可以达到目的，但是其结果就会依赖于索引是倒序的，如果某一天索引重建成了升序的，那么返回的结果就是错误的。而之前的SQL则不会有这个问题。

在这个案例中，我们通过改写SQL，使Oracle执行时，从右向左进行索引扫描，也就是按降序扫描，解决了须扫描大量索引叶节点块引起的性能问题。

通过索引访问数据时的成本计算

---

其实从前面对索引扫描过程的分析，就可以知道，通过索引访问数据的成本主要包括3个部分：

1. Root Block以及Branch Block的访问成本。

2. 索引的叶节点块访问成本。

3. 从索引取得ROWID，根据ROWID访问表的成本。

如果以公式表示就是：

cost =
blevel + ceiling ( leaf_blocks * effective index selectivity ) +
ceiling ( clustering_factor * effective table selectivity )

上面的公式，以“+”号分为三部分，分别对应前面所描述的1~3这三部分不同的成本。下面做一个简单的解释。

blevel也就是DBA_INDEXES视图中的BLEVEL列，表示索引B Tree树叶节点的层数（注意层数从0开始计数），即树的高度减1，也就是从Root Block访问到叶节点块时所经过的Branch Block包括Root Block的数量。对于一个索引来说，这个值常常介于0~3之间。

再看看第二部分的成本，leaf_blocks * effective index selectivity，leaf_blokcs就是DBA_INDEXES视图中LEAF_BLOCKS列，也就是索引叶节点块数。effective indexselectivity指的是SQL的查询条件中的用于索引扫描时的字段的选择率，比如，有一个复合索引，以及执行的SQL如下：

CREATE INDEX T_IDX_C1 ON T ( C1, C2 )
SELECT * FROM T WHERE C1 >= 123 AND C2 > 345

对此，Oracle会根据C1 >= 123 这个条件进行索引扫描，这个条件谓词的选择率，就是effective index selectivity。当然选择率是怎么计算的，实际上是一个涉及面非常广并且比较深入的话题，本节不再细述，有兴趣的朋友，可以参考《Cost-Based Oracle Fundamentals》。

接下来，让我们看看第三部分的成本，clustering_factor * effectivetable selectivity，这里clustering_factor，聚集因子，也就是DBA_INDEXES视图中的CLUSTERING_FACTOR列。effective table selectivity，有效表选择率，指的是查询条件中能够在索引上进行过滤的所有字段的选择率。比如以前面的SQL为例，虽然在扫描索引时，是以C1 >= 123为条件定位到叶节点块再进行扫描的，但是扫描过程中，会同时将不满足C2 > 345的行丢弃，满足条件的才会利用ROWID到表的数据块中获取数据。因此，在这里表的有效选择率（effective table selectivity）即为C1 > 123和C2 > 345这两个谓词的选择率的结合。

当遇到性能问题，考虑用索引来解决时，通常会考虑减小effective table selectivity来优化性能。如果说这个术语比较枯燥难懂，那可以这样说，就是尽量使数据通过索引访问，减少回表的数据量。一种特殊的情况是SELECT后面的字段列表，全部在索引中，无须通过访问表（回表）即可以得到。

值得注意的是，虽然BLEVEL部分通常在0~3之间，但是在返回数量非常少，比如只有1行，而同时查询次数又特别多时，降低索引高度，也就是减少BLEVEL，同样能够减少大量的成本，即逻辑读。常用的方法就是将索引进行分区。不过，提到分区，不能不提到本地索引，如果某个查询，使用了本地索引，但是没有在分区列上使用查询/约束条件，那么不能够进行分区剪裁，分区表中这个索引的所有的分区都要被访问，那么Root和Branch Block的访问成本就应该是blevel * 分区数，这种情况下，成本是非常高的。

我们掌握了Oracle通过索引访问数据的成本计算公式之后，再来看看案例一（点击查看历史消息：利用复合索引解决性能问题）中留下的问题：

1. 为什么建立复合索引能够解决此性能问题？建立复合索引，也就是减少了effective table selectivity，使得通过ROWID访问表的数据量大大减少，从而降低了成本。

2. 为什么建立复合索引时，STAFF_ID列排在CREATED_DATE列的前面？要回答这个问题，先看看表上的谓词条件staff_id =12345 and CREATED_DATE >= trunc(sysdate)，如果将STAFF_ID列排在后面，则effective index selectivity就只能根据CREATED_DATE>= trunk(sysdate)而来，反之，如果STAFF_ID为索引的第1列，则effective index selectivity能够同时根据这两个谓词条件得来，也就是effective index selectivity更小，访问索引的叶节点部分的成本就会更低。

与索引访问的成本有关的参数还包括OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ，它们涉及面比较广，由于篇幅所限，不再详细介绍，有兴趣的朋友可联系笔者进一步讨论。

下面再简单介绍一下索引的clustering factor（聚集因子）。clustering factor表示索引的所有列在表中的有序程度。索引中的数据，始终是有序排列的，Oracle在访问数据时，也是按索引中数据的顺序进行。索引的相邻两条数据，其ROWID指向的表的数据如果是在同一个数据块中，那么在通过ROWID访问表时，只要访问1个块就可以了，也就是只需要1次逻辑IO。理想的情况是，索引的数据，其ROWID尽可能地在同一块中，在这种情况下，clustering factor就是表中数据块的数目，而在最坏的情况下，索引数据的ROWID全部是散乱的，没有相邻的索引数据其ROWID是指向同一个数据块的，这种情况下，clustering factor就是表中所有行的总数。由于clustering factor反映了表的数据的有序程度，索引中固定的列依前列的顺序排列，其clustering factor是固定的，重建索引并不会导致clustering factor的减小，除非重建表，使表按索引的列进行排序。

虽然一般情况下表的数据是固定排列的，但在有的情况下，却可以按我们的意愿来排列表数据，比如像电信运营商向客户提供账单和话单查询的数据，一些交易系统，如股票、基金交易的历史数据。这些数据一般是从原始的生产数据重新整理而来，所以可以按一定的条件进行有序排列，也就是说，降低索引的clustering factor，从而为一些主要的查询提供更好的性能。

本文摘取自《Oracle DBA手记1》第11章 合理利用索引解决性能问题，作者熊军

配图来源：http://img.weiyangx.com/2014/04/32.jpg