明理知意：复合索引优化及索引访问原理

熊军（老熊）

云和恩墨西区总经理

Oracle ACED，ACOUG核心会员

这个案例发生在某天早上，运行在配置为128GB内存、64CPU的HP Superdome上的系统出现CPU占用将近100%，运行队列达到60~80，应用反应速度很慢的异常情况。

在用户反映速度很慢后，检查Oracle，发现很多的会话在等待latch free，latch#为98：

SQL> select * fromv$latchname where latch#=98;

        LATCH# NAME
    ---------- ----------------------------------------------------------------
            98 cache buffers chains

由于本章重点描述的是索引，关于“cache buffers chains latch”的等待，此处不做过多说明，这个latch的等待，通常情况下表明存在热点块，一般都是由于没有正确使用索引、SQL所使用的索引选择率不高引起。检查正在等待latch free的会话正在执行的SQL，大部分都在执行类似于下面的SQL：

SELECT SUM(cnt),

          to_char(nvl(SUM(nvl(amount, 0)) /100, 0), ’FM9999999999990.90′) amount

     FROM (select count(payment_id) cnt,SUM(amount) amount

             from TABLE_A

            where staff_id = 12345

              and CREATED_DATE >= trunc(sysdate)

              and state = ’C0C’

              and operation_type in (’5KA’,’5KB’, ’5KC’, ’5KP’))

看起来这个SQL并不复杂，查看其执行计划：

PLAN_TABLE_OUTPUT                                                                                                                         

-------------------------------------------------------------------------------------------  

| Id  | Operation                                | Name    | Rows   |Bytes  | Cost   |Pstart  |Pstop   |
-------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT                         |          |     1  |   26  |  125K  |        |        |   
|   1 | SORT AGGREGATE                         |          |     1  |   26  |        |        |        |   
|   2 |  VIEW                                  |          |     1  |   26  |  125K  |        |        |   
|   3 |   SORT AGGREGATE                       |          |     1  |   30  |        |        |        |  
|*  4 |     TABLEACCESS BY GLOBAL INDEX ROWID   |TABLE_   | 19675  |  576K |  125K  | ROWID  |ROW L  | 
|*  5 |      INDEX RANGE SCAN                    | IDX_A_3  | 1062K  |       | 3919   |        |        |    
-------------------------------------------------------------------------------------------    

                                                                                                     

PredicateInformation (identified by operation id):                                                    

---------------------------------------------------                                                    

                                               

  4 - filter(”TABLE_A”.”STAFF_ID”=12345 AND”TABLE_A”.”STATE”=’C0C’ AND                    

      (”TABLE_A”.”OPERATION_TYPE”=’5KA’ OR”TABLE_A”.”OPERATION_TYPE”=’5KB’              

      OR ”TABLE_A”.”OPERATION_TYPE”=’5KC’ OR”TABLE_A”.”OPERATION_TYPE”=’5KP’))          

  5 -access(”TABLE_A”.”CREATED_DATE”>=TRUNC(SYSDATE@!))                                          

                                                                                                       

Note: cpu costing is off                                                                              

从中可以看到，Oracle评估出，利用索引扫描返回的行数高达100万行，可想而知，由于选择率过高，产生了大量的buffers chains latch争用。

检查PAYMENT表的索引：

SQL> select index_name,index_type from user_indexeswhere table_name=’TABLE_A’;

   INDEX_NAME                          INDEX_TYPE
   ------------------------------      ---------------------------
   IDX_A_1                             NORMAL
   IDX_A_2                             NORMAL
   IDX_A_3                             NORMAL
   IDX_A_4                             NORMAL
   IDX_A_5                             NORMAL
   IDX_A_6                             NORMAL
   IDX_A_7                             NORMAL
   IDX_A_8                             NORMAL
   PK_TABLE_A                          NORMAL

   SQL> selectindex_name,column_name,column_position from user_ind_columns where table_name=’TABLE_A’order by 1,3;

   INDEX_NAME                          COLUMN_NAME                            COLUMN_POSITION
   ------------------------------      ------------------------------         ---------------
   IDX_A_1                             SERIAL_NBR                                           1
   IDX_A_2                             A_ID                                                1
   IDX_A_3                             CREATED_DATE                                         1
   IDX_A_4                             METHOD                                               1
   IDX_A_5                             P_METHOD                                            1
   IDX_A_6                             S_ID                                                 1
   IDX_A_7                             STAFF_ID                                             1
   IDX_A_7                             STATE_DATE                                           2
   PK_TABLE_A                          TABLE_A_ID                                           1

以上输出是对真正的输出信息加工处理后的结果。

由上可知，执行计划中使用的索引IDX_A_3是在CREATED_DATE列上建立的单列索引。

这个SQL在之前没有出现过类似问题，那问题在哪里？

原来在当天凌晨做了一个大数量的业务操作，在TABLE_A中插入了大量的数据，因此用CREATED_DATE>=TRUNCATE(SYSDATE)这个条件时会从索引扫描中返回大量的行。而实际上回表之后用STAFF_ID和OPERATION_TYPE列上的条件过滤后的行数仅约2万行（这是评估的数据，实际的数据远远比这个少）。很显然，如果我们建立一个复合索引，那么索引扫描返回的行数将大大减少，这样也就大大减少了在表上访问并进行过滤的数据量。

以STAFF_ID列为前导列与CREATE_DATE列一起建立复合索引后，系统马上恢复正常。不过，有人会问，为什么要使用STAFF_ID列做索引的前导列，而不用CREATE_DATE列做前导列？很多文档不是介绍说，复合索引要把选择性最好的列放在最前面吗？要回答这个问题，得首先了解索引的基本原理，包括Oracle数据库对索引是如何存储的、是怎样通过索引来检索索引数据的。

B Tree索引的结构及特点

---

Oracle数据库中索引的存储结构使用的是B Tree的一种变体，称为B*Tree（B Star Tree），在数据库中存储数据以块为单位，索引也不例外，数据库中构建索引形成的BTree，与教科书中提到的B Tree有很明显的差异。下面以图11-1为例，介绍Oracle数据库中B Tree索引的结构及其特点。

图11-1  Oracle数据库中B Tree索引的结构及其特点示意图

图11-1是一个简单的B Tree索引示意图，图中虚线部分表示省略的部分。在介绍B Tree索引的特点之前，我们先来回顾一下数据结构中树的几个术语。

节点M的深度：从树根节点到节点M的最短路径长度。图中根节点Root的深度为0，节点L1-1的深度为1，节点L0-1的深度为2。

节点M的层数：节点M的层数与其深度，实际上是相同的。

树的高度：树的深度值最大的那个节点，其深度+1即为树的高度。比如图中树的高度为3。

Oracle数据库的索引，有以下几个特点：

• 存储索引数据的块，称为B Tree树的节点。有三种类型的节点，根（Root）节点、分枝（Branch）节点和叶（Leaf）节点。高度为1的索引，只有根节点，这个时候，索引只有唯一的一个叶节点，也同时是根节点，高度大于1时，根节点与分枝节点具有完全相同的结构，也就是说，这个时候的根节点，实际上也是一种分枝节点。分枝节点索引块存储的数据主要包括：索引值、键值对应的下一级节点块地址、还有一个称之为“kdxbrlmc”的指针，也就是如图所示的“lmc”，这个指针就是比当前枝节点中最小的索引值还小的下一级节点块的数据块地址（DBA）。而叶节点索引块存储的数据主要是索引值以及对应的ROWID，和当前节点的前后两个节点的数据块地址。

• 索引的根节点，总是紧接在索引段头的后面的一个数据块。比如某个索引的段头为7/7817（相对文件号/块号），那么根节点块就是7/7818。这个特点，是很少有文档提及的，但是这个小小的特点，其实非常重要。Oracle执行SQL时，直接从数据字典得到段头位置后就能定位到根节点。这个特性使得就算是在小表上，使用索引也能减少逻辑读，对于频繁访问的索引，特别是以INDEX UNIQUE SCAN方式访问索引，所节省的逻辑读是非常多的。

• 下面我们做一个简单的测试，测试的数据库版本为Linux AS4上的Oracle 10.2.0.4：

--创建一个只有2列、4行的表：

SQL> create tablet1 as select object_id,object_name from dba_objects where rownum<=4;  ="">

Table created.

--创建一个非唯一索引：

SQL> create indext1_idx1 on t1(object_id);

Index created.

SQL> set autot onstat

SQL> colobject_name for a30                          

--全表扫描（Table Full Scan）：

SQL> select /*+full(t1) */ * from t1 where object_id=28;

OBJECT_ID   OBJECT_NAME
----------    ------------------------------
         28  CON$

Statistics

----------------------------------------------------------

         0 recursive calls

         0 db block gets

         4 consistent gets

         0 physical reads

         0 redo size

       478 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

         1 rows processed

--索引范围扫描（Index Range Scan）：

SQL> select /*+index(t1) */ * from t1 where object_id=28;

OBJECT_ID   OBJECT_NAME
----------    ------------------------------
       28  CON$

Statistics

----------------------------------------------------------

         0 recursive calls

         0 db block gets

         3 consistent gets

         0 physical reads

         0 redo size

       478 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

         1 rows processed

SQL> set autotoff

--删除索引，重新创建一个唯一索引：

SQL> drop indext1_idx1;

Index dropped.

SQL> createunique index t1_idx1 on t1(object_id);

Index created.

--索引唯一扫描（Index Unique Scan）：

SQL> set autot onstat

SQL>  select /*+ index(t1) */ * from t1 whereobject_id=28;

OBJECT_ID   OBJECT_NAME
----------    ------------------------------
       28  CON$

Statistics

----------------------------------------------------------

         0 recursive calls

         0 db block gets

         2 consistent gets

         0 physical reads

         0 redo size

       478 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

        1  rows processed

在上面的测试中，创建了一个只有2列，4行的表，这个表只占用了1个数据块的空间。对同样的SQL，全表扫描、索引范围扫描、索引唯一扫描3种不同的访问方式，其逻辑读各不相同：

注意在实际的测试中，每一个SQL应至少执行两次，并以最后一次SQL执行后的逻辑读等统计数据为准，因为在SQL解析时有递归调用，产生了其他的逻辑读。

从上面的测试可以看到，对即使是很小的表，如果返回的数据量很小，使用索引都能够减少逻辑读，从而具有更好的性能。

• 索引是始终保持平衡的。这里所说的平衡是指索引高度是保持平衡的，也就是从根节点到任意一个叶节点，其路径都是等距的。比如图11-1中，从“Root”到叶节点“L0-1”与“Root”到叶节点“L0-5”，都要访问3个块。可以说，这是B Tree索引最重要的一个特性。值得注意的是，在有的书和文章上面，提到B Tree索引不平衡，是指索引中的数据是倾斜的。如果某一个表删除了大量的数据，会形成索引中很多的块，只有很少量的数据甚至是空块。比如图11-1中，叶节点“L0-2”只有1条数据。这种情况常见于单向增长的列上的索引，比如Sequence、日期类型等，在删除了大量数据后，由于列是单向增长的，除非是空块，否则剩余空间很难得到重用。

• 索引的每一个叶节点，有两个指针，分别指向比当前节点最小索引值还小的叶节点块地址，以及比当前节点最大索引值还大的叶节点块地址。通过这两个指针，把所有的叶节点串起来，形成一个双向链表。在这个双向链表上的所有索引值，从小到大排列，而对于倒序（Desc）索引，则是从大到小排列。值得注意的是，对于非唯一索引来说，每个值所对应的ROWID，也是索引值的一部分，所以在组成索引的各个列值均相等的情况下，会按ROWID为顺序进行排序。

• 索引的分枝节点块所存储的索引值，并不是完整的索引值，而只是整个索引值的前缀，只要能够区分其大小就可以了。比如在前面的索引示意图11-1中的“L1-1”分枝节点，有两个值，AD和AK，其指向的叶节点起始索引值为ADK以及AKA，但是其前缀AD和AK即可以区分其大小。这种设计，能够使分枝节点存储更多的条目，减少了分枝节点数，特别是在多列复合索引中，对于很大的表，甚至可以减少B Tree树的高度。

• B Tree索引不对NULL值进行索引，对于某一行，索引的所有列的值都是NULL值时，该行不能被索引。不过Cluster Index是可以对NULL值进行索引的，但是本文主要是讨论普通表上的B Tree索引，对Cluster Index不做讨论。由于Oracle索引的这个特性，使得IS NULL这种条件的SQL不能够使用索引。但是我们可以通过建复合索引的形式来使这种SQL也能够使用索引。

本文内容节选自《Oracle DBA手记》，关注本公众号（扫码关注），回复“DBA01”，可以获取本书全部内容下载。ACOUG Asia Tour正在展开全国巡回，点击链接参加您当地的技术分享活动：ACOUG 亚洲之旅。