Oracle中利用函数索引处理数据倾斜案例

实际中，我们常常遇到数据列值倾斜的情况。就是说，整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里，其他取值比例极少。比如：一个数据列值有“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

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

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_owner将PUBLIC、SYSMAN、SYS值转化为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数据量149760的XDB取值进行分析。

--直接索引搜索：

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使用上有一定差异。

结论：使用函数索引处理偏值方法，在一定长度上优化查询效率和索引结构。上表的数据表明，会使逻辑物理读的消耗很大程度的减少（索引结构简化），同时连带影响执行时间的缩小。因为使用函数要进行计算，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

但是如果是对SYS、PUBLIC或SYSMAN进行查询时，将会跳过所有的索引，直径进行全表扫描。

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（苦练七十二变，笑对八十一难）