SEian.G

苦练七十二变,笑对八十一难

  • 博客访问: 41118
  • 博文数量: 70
  • 用 户 组: 普通用户
  • 注册时间: 2016-03-12 13:43
  • 认证徽章:
个人简介

冰冻三尺非一日之寒,滴水穿石非一日之功。

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(70)

文章存档

2017年(69)

2016年(1)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle


    本文参考realkid4的博客:

http://blog.itpub.net/17203031/viewspace-681311/


   
   关于B-Tree、Bitmap、函数索引的相关内容请参考另一篇博文:  
 
   Oracle中B-Tree、Bitmap和函数索引使用案例总结

 
   
常来说,索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看,列值都是分布在叶节点位置。这样,通过树结构搜索得到的叶节点数量效率比较高。

 

实际中,我们常常遇到数据列值倾斜的情况。就是说,整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里,其他取值比例极少。比如:一个数据列值有“N”、“B”、“M”、“P”、“Q”几个取值,其中55%数据行取值为“N”,40%数据行取值为“B”,剩下的取值分布在5%的数据行中。对于这种结构的数据列加索引,是存在一些问题的。

 

首先,默认数据库是会为所有的列值(非空)建立索引结构。也就意味着无论是高频度取值,还是低频度取值,都会在索引结构的叶节点上出现。当然,这样的大部分叶节点都是这些重复值。

 

其次,在CBO(基于成本优化器)的作用下,对高频度取值的搜索一般都不会选择索引作为搜索路径,因为进行全表扫描可能效率更高。我们为数据列建立了索引,但高频词的查询永远不会走到索引路径(下面的实验会证明这一点)。

 

最后,建立的索引空间和时间消耗比较大。建立的索引涵盖所有取值,对海量数据表而言,占有的空间势必较大。同时,在进行小频度数据查询的时候,虽然会去走索引路径,但是引起的逻辑物理读也是有一些损耗。

 

 

下面引入一个解决方法

 

思路:既然高频度值在查询的时候不会走到索引路径,可以考虑将其剔出构建索引的过程,只为那些低频度数据值建立索引结构。这样,建立的索引树结构相对较小,而且索引查询的效率也能提升。

 

具体的方法是使用decode函数。decode(a,b,c,d,e…f)含义:如果a=b,则返回c,等于d,返回e,最后没有匹配的情况下,返回f。针对上面的例子,可以使用decode(列名,‘N’, null,‘B’,null,列名),含义是,如果该列取值为N或者B,直接设置为null,否则才返回列值,并且以此建立函数索引。

 

这样做借助了Oracle两个功能:1、对null值不生成索引;2、函数索引;

 

 

下面通过实验来证明该方法:

 

1、构建实验测试环境

 

--创建测试数据表tb_wjq

SEIANG@seiang11g>create table tb_wjq as select * from dba_objects where owner in ('SEIANG','PUBLIC','HR','SYSMAN','XDB','BI','SYS');

Table created.

 

--使用脚本插入大量数据

begin 
for i in 1..8 loop    
insert /*+ append */ into tb_wjq select * from tb_wjq;
commit; 
end loop;
end;
/

 
SEIANG@seiang11g>select count(*) from tb_wjq;

  COUNT(*)
----------
   9804160

 

Elapsed: 00:00:01.54

 

--用于实验的数据量分布情况

SEIANG@seiang11g>select owner,count(*) from tb_wjq group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SEIANG                               3072
PUBLIC                            4352256
HR                                   4352
SYSMAN                             454912
XDB                                149760
BI                                   1024
SYS                               4838784

7 rows selected.

Elapsed: 00:00:02.44

 

可以看到,九万多条数据,绝大部分数据集中到了PUBLICSYSMANSYS上,其他数据取值频数较小。数据倾斜趋势明显。

 

 

2、建索引

 

--分别对owner列建立常规、函数索引。

SEIANG@seiang11g>create index idx_tb_wjq_owner_normal on tb_wjq(owner);

Index created.

Elapsed: 00:00:24.72


SEIANG@seiang11g>create index func_idx_tb_wjq_owner on tb_wjq(decode (owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner));

Index created.

Elapsed: 00:00:12.34

 

索引func_idx_tb_wjq_ownerPUBLICSYSMANSYS值转化为null,剔出了建立索引的过程。不仅可以从上面创建索引所用的时间可以看出,而且从下面的索引段信息看,两个索引所占的空间差异比较大,也证明了这点。

 

SEIANG@seiang11g>select owner,segment_name,segment_type,bytes/1024/1024,blocks,extents from dba_segments where segment_name='IDX_TB_WJQ_OWNER_NORMAL';

OWNER           SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024     BLOCKS    EXTENTS
--------------- ------------------------------ ------------------ --------------- ---------- ----------
SEIANG          IDX_TB_WJQ_OWNER_NORMAL        INDEX                          184      23552         94

Elapsed: 00:00:00.01

SEIANG@seiang11g>select owner,segment_name,segment_type,bytes/1024/1024,blocks,extents from dba_segments where segment_name='FUNC_IDX_TB_WJQ_OWNER';

OWNER           SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024     BLOCKS    EXTENTS
--------------- ------------------------------ ------------------ --------------- ---------- ----------
SEIANG          FUNC_IDX_TB_WJQ_OWNER          INDEX                            3        384         18

 

由上可以看出,同样是对一个数据列加索引。普通索引类型IDX_TB_WJQ_OWNER_NORMAL占据94个区,23552个数据块,空间约占184M。而函数索引FUNC_IDX_TB_WJQ_OWNER的空间只用了初始分配的18个区,384个数据块,空间约占3M。由此,空间优势直观体现!

 

--收集统计数据,由于是实验性质,而且数据量大,采用高采样率收集统计信息。

SEIANG@seiang11g>exec dbms_stats.gather_table_stats('SEIANG', 'TB_WJQ', cascade => true, estimate_percent => 100,method_opt => 'for all indexed columns');

PL/SQL procedure successfully completed.

Elapsed: 00:00:49.67 

 

 

3、检索效率分析

 

针对owner数据量149760XDB取值进行分析。

 

--直接索引搜索:

SEIANG@seiang11g>select * from tb_wjq where owner='XDB';

149760 rows selected.

Elapsed: 00:00:01.89

Execution Plan
----------------------------------------------------------
Plan hash value: 3735191644

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |   149K|    13M|  4708   (1)| 00:00:57 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_WJQ                  |   149K|    13M|  4708   (1)| 00:00:57 |
|*  2 |   INDEX RANGE SCAN          | IDX_TB_WJQ_OWNER_NORMAL |   149K|       |   348   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='XDB')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      26000  consistent gets
       6349  physical reads
          0  redo size
   16482673  bytes sent via SQL*Net to client
     110336  bytes received via SQL*Net from client
       9985  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     149760  rows processed

 

发现采用BI作为搜索值时,是进行了索引搜索。下面是用函数索引搜索进行对比。

 

SEIANG@seiang11g>select * from tb_wjq where decode (owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner)='XDB';

149760 rows selected.

Elapsed: 00:00:01.54

Execution Plan
----------------------------------------------------------
Plan hash value: 3652333940

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |   149K|    13M|  7091   (1)| 00:01:26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_WJQ                |   149K|    13M|  7091   (1)| 00:01:26 |
|*  2 |   INDEX RANGE SCAN          | FUNC_IDX_TB_WJQ_OWNER |   149K|       |   316   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(DECODE("OWNER",'PUBLIC',NULL,'SYSMAN',NULL,'SYS',NULL,"OWNER")='XDB')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      25998  consistent gets
          0  physical reads
          0  redo size
    9017261  bytes sent via SQL*Net to client
     110336  bytes received via SQL*Net from client
       9985  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     149760  rows processed

 

对比后,我们可以发现,使用函数索引的方法,在执行时间、物理逻辑读、CPU使用上有一定差异。

 

普通索引

函数索引

执行时间

00: 00: 01.89

00: 00: 01.54

CPU使用

4708

7091

consistent gets

26000

25998

physical reads

6349

0

 

结论:使用函数索引处理偏值方法,在一定长度上优化查询效率和索引结构。上表的数据表明,会使逻辑物理读的消耗很大程度的减少(索引结构简化),同时连带影响执行时间的缩小。因为使用函数要进行计算,CPU使用率相对较高,在可以接受的范围内。

 

但是,这种方法是存在一些限制的,应用前一定要仔细规划。

首先,数据表数据要保证较大。因为毕竟函数索引的建立和搜索较普通索引消耗大,如果数据表小,带来的优化程度不能弥补消耗的成本,结果可能得不偿失。笔者进行的一系列实验中,也发现在数据量中等偏小时,这种性能优势不能凸显。

 

其次,列值倾斜趋势明显。通过开篇的讨论我们不难发现,列值倾斜的程度越高,使用函数索引剔出的数据量也就越大,生成的索引树结构也就越小越优化。这一点是本方法的核心!

 

最后,使用函数索引搜索时,搜索的取值频数越高,优化效果越好。在本例中,取值XDB的列有149760行,可以看出明显的性能优化。但是当我们选择值有1024条数据的BI值时,这种优化趋势可以看到,但是明显程度降低(实验结果如下所示)。这里的原因可能是数据量小时,两种方法逻辑物理读的差异度缩小。

 

--直接索引

SEIANG@seiang11g>select * from tb_wjq where owner='BI';

1024 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3735191644

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |  1024 |    98K|    35   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_WJQ                  |  1024 |    98K|    35   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TB_WJQ_OWNER_NORMAL |  1024 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='BI')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        280  consistent gets
        151  physical reads
          0  redo size
      98579  bytes sent via SQL*Net to client
       1271  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1024  rows processed 

 

--函数索引

SEIANG@seiang11g>select * from tb_wjq where decode (owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner)='BI';

1024 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3652333940

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |  1024 |    98K|    50   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_WJQ                |  1024 |    98K|    50   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FUNC_IDX_TB_WJQ_OWNER |  1024 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(DECODE("OWNER",'PUBLIC',NULL,'SYSMAN',NULL,'SYS',NULL,"OWNER")='BI')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        279  consistent gets
          3  physical reads
          0  redo size
      33969  bytes sent via SQL*Net to client
       1271  bytes received via SQL*Net from client
         70  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1024  rows processed

 

但是如果是对SYSPUBLICSYSMAN进行查询时,将会跳过所有的索引,直径进行全表扫描。

 

SEIANG@seiang11g>select * from tb_wjq where owner='SYS';

4838784 rows selected.

Elapsed: 00:00:45.85

Execution Plan
----------------------------------------------------------
Plan hash value: 1501781665

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |  4838K|   452M| 39893   (1)| 00:07:59 |
|*  1 |  TABLE ACCESS FULL| TB_WJQ |  4838K|   452M| 39893   (1)| 00:07:59 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
     457638  consistent gets
     139684  physical reads
          0  redo size
  255169095  bytes sent via SQL*Net to client
    3548958  bytes received via SQL*Net from client
     322587  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    4838784  rows processed

 

 
作者:SEian.G(苦练七十二变,笑对八十一难)


阅读(530) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册