唯一索引 VS 非唯一索引

通过一个简单的测试，对比了一下唯一索引和非唯一索引在做等值谓词查询情况下，对于cache buffers chains latch和consistent gets两个指标的影响。

测试结论：
如果表上的列符合建立唯一索引的条件，那么为了避免cache buffers chains latch的争用，最好建立唯一索引。唯一索引不但可以减少cache buffers chains latch争用，还可以一定程度上减少逻辑读。


唯一索引在等值谓词查询时，根节点、分支节点、叶节点、数据块都只需要获得一次cache buffers chains latch（简称cbc latch），在cbc latch的保护下读取buffer。
 大体步骤：
1）依据数据块的地址计算出数据块所在的Hash Bucket
2）获得保护这个Hash Bucket的cbc latch（共享模式获取）
3）在这个链表上找寻我们需要的数据块，找到后，步骤4）
4）读取数据块的内容
5）释放cbc latch
上面的读取操作在oracle里有一个单独的统计量来体现：consistent gets - examination，consistent gets - examination的值最终也会被汇总到consistent gets里。


非唯一索引在等值谓词查询时，根节点、分支节点都只需要获得一次cbc latch，叶节点、数据块都需要获得两次CBC LATCH。之所以根节点和分支节点只需要获得一次cbc latch，是因为oracle做了优化，根节点和分支节点的读取量要远远大于叶节点，非常可能会引起cbc latch的争用，因此oracle对于跟节点和分支节点的操作，都使用了获取共享cbc latch不加buffer pin的方式。

叶块和数据块的大体步骤：

1）依据数据块的地址计算出数据块所在的Hash Bucket
2）获得保护这个Hash Bucket的cbc latch（排他模式获取）
3）在这个链表上查找需要的数据块，找到后，pin这个buffer
4）释放cbc latch
5）在buffer pin的保护下读取/修改数据块的内容，读取完成后步骤6）
6）再次获取cbc latch（排他模式获取）
7）unpin这个buffer
8）释放cbc latch

上面的读取操作在oracle里有一个单独的统计量来体现：consistent gets。

由上面的步骤可以看出，唯一索引的代码路径比非唯一索引短，如果表上的列符合唯一索引的创建条件，建议创建唯一索引来最小化cbc latch可能产生的争用。

我们来看下具体的测试例子：

----创建测试表
create table test as select * from dba_ocache bjects;


场景一：非唯一索引下，cbc latch的持有次数

------在object_id上创建非唯一索引
create  index tt on test(object_id);

execute snap_latch.start_snap

select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;

execute snap_latch.end_snap

---------------------------------
Latch waits:-   05-Aug 13:17:21
Interval:-      0 seconds
---------------------------------
Latch                              Gets      Misses     Sp_Get     Sleeps     Im_Gets   Im_Miss Holding Woken Time ms
-----                              ----      ------     ------     ------     -------   ------- ------- ----- -------
cache buffers chains                 71           0          0          0           0         0       0     0      .0

上面的代码对一个同样的SQL做了10次查询。并且打印出了这期间会话cache buffers chains latch的统计量（增量）。
cache buffers chains的获取次数为71次。
我们上面已经提到，根节点，分支节点（这里分支节点和跟节点是同一个）的cbc latch每次只需持有一次。而叶节点和数据块节点，每次读取需要持有2次。
那么执行一次SQL，需要的cbc latch数为：
【根节点】1+【叶块】2+【数据块】2=5
执行十次：
5*10=50次
但是我们看到的cbc latch是持有了71次，多了21次，其实21次中的20次，是叶节点产生的，由于是非唯一索引，ORACLE从叶块取出第一个符合的值回表取出这条记录后，还需要再读取索引块一次，因为它不确认是不是下一个索引entry是不是也符合记录，但是唯一索引就没这个问题。通过逻辑读取数也可以验证这一点：

execute snap_my_stats.start_snap

select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;
select object_name  from test where object_id=46;

execute snap_my_stats.end_snap

---------------------------------
Session stats - 05-Aug 13:25:07
Interval:-  0 seconds
---------------------------------
Name                                                                     Value
----                                                                     -----
consistent gets                                                             40
consistent gets from cache                                                  40
consistent gets from cache (fastpath)                                       30
consistent gets - examination                                               10

40次逻辑读，每次SQL的逻辑读是4,4=1次根块+2次索引块+1次数据块
如果是唯一索引的话，只需要读取一次索引块就OK了。


场景二：唯一索引下，cbc latch的持有次数

------在object_id上创建非唯一索引
drop index tt;
create unique index tt on test(object_id);

-----执行代码，略

---------------------------------
Latch waits:-   05-Aug 13:27:11
Interval:-      0 seconds
---------------------------------
Latch                              Gets      Misses     Sp_Get     Sleeps     Im_Gets   Im_Miss Holding Woken Time ms
-----                              ----      ------     ------     ------     -------   ------- ------- ----- -------
cache buffers chains                 31           0          0          0           0         0       0     0      .0

【根节点】1+【叶块】1+【数据块】1=3
执行十次，共30次的cbc latch获取。多余的一次为？？？？
cbc latch的获取次数比上一个场景减少了71-31=40次。这个非常棒！

同理逻辑读取数：
---------------------------------
Session stats - 05-Aug 13:27:46
Interval:-  0 seconds
---------------------------------
Name                                                                     Value
----                                                                     -----
consistent gets                                                             30
consistent gets from cache                                                  30
consistent gets - examination                                               30

一次SQL的逻辑读取数：
1次根块+1次索引块+1次数据块=3
10次执行的逻辑读就为：10*3=30
由于是唯一索引，所以查询一次叶块后，不需要再次回索引块做check。
逻辑读取数也比上一个场景少了40-30=10次。

---

魏兴华  xinghua.wei@woqutech.com  

沃趣科技高级数据库专家、项目经理

TEL:18652489962

公司网站：http://www.woqutech.com

杭州市滨江区长河街道滨安路1190号3幢智汇领地科技园A区A楼10层1004室（310053）

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

转载于:http://blog.itpub.net/22034023/viewspace-1762606/