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

熊军(老熊)

云和恩墨西区总经理

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也能够使用索引。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-03-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏挖掘大数据

处理海量数据的10种常见方法

本文将介绍10种处理海量数据问题的常见方法,也可以说是对海量数据的处理方法进行一个简单的总结,希望对你有帮助。

20210
来自专栏CDA数据分析师

R语言 apply函数家族详解

? apply {base} 通过对数组或者矩阵的一个维度使用函数生成值得列表或者数组、向量。 apply(X, MARGIN, FUN, ...) X 阵列...

20310
来自专栏数说戏聊

09-10章 汇总分组数据第9章

如果需要汇总数据而不是检索,SQL 提供专用函数,可用于检索数据,以便分析和报表生成。这种类型的检索例子有:

661
来自专栏云飞学编程

python学习,数据分析系列工具,初识numpy

其实,数据分析看着很高大上,也很实用,但是真的很枯燥啊。。。。但是它又不得不学,毕竟数据分析对很多工作是很有帮助的,比如爬虫,抓到的数据,不论是保存到文件还是数...

452
来自专栏编程

C语言干货,新手入门必看,基础知识大汇总!

习C语言始终要记住“曙光在前头”和“千金难买回头看”,“千金难买回头看”是学习知识的重要方法,就是说,学习后面的知识,不要忘了回头弄清遗留下的问题和加深理解前面...

1855
来自专栏QQ音乐技术团队的专栏

Android Native 开发之 NewString 与 NewStringUtf 解析

本文将从一个 Native Crash 分析入手,带大家了解我们平时开发中,那些容易忽略但又很值得学习的底层源码知识。

9599
来自专栏GopherCoder

2016.05.27

1384
来自专栏IT技术精选文摘

MySQL索引背后的数据结构及算法原理

844
来自专栏鹅厂少年的奇妙之旅

MySQL索引背后的数据结构及算法原理

本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MyS...

2293
来自专栏逆向技术

16位汇编中的伪指令

汇编中的伪指令(基于汇编编译器MASM讲解) 一丶什么是伪指令,以及作用 首先我们用汇编开发效率低,如何才能开发效率高,甚至开发速度比C语言或这个高级语言快 答...

1708

扫描关注云+社区