炼数成金 门户 大数据 Oracle 查看内容

Oracle中 如何巧用函数索引解决数据倾斜列查询?

2017-12-1 13:51| 发布者: 炼数成金_小数| 查看: 21254| 评论: 0|原作者: realkid4|来自: ITPUB

摘要: 通常来说,索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看,列值都是分布在叶节点位置。这样,通过树结构搜索得到的叶节点数量效率比较高。实际中,我们常常遇到数据列值倾斜的情况。就是说,整个 ...

SQL Java Hadoop 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、  构建数据环境
// 数据准备
SQL> create table t as select * from dba_objects where 1=0;

Table created
// 构造大数据环境,使用脚本
declare
 i number;
begin 
 for i in 1..40 loop    
    insert /*+ append */ into t
    select * from dba_objects;
   
    commit; 
 end loop;
end;
/

SQL> select count(*) from t;

 COUNT(*)
----------
  4759209

Executed in 15.522 seconds
 
整理后的数据环境如下:
// 投入实验的数据状态
SQL> select secondary, count(*) from t group by secondary;

SECONDARY   COUNT(*)
--------- ----------
W                273
Q                  9
D                273
T             421230
J            1866592
E                 99
S            2470733

7 rows selected

Executed in 18.002 seconds
 
可以看到,近五百万数据两种,绝大部分数据集中到了 S、T、J 上,其他数据取值频数较小。数据倾斜趋势明显。

2、建索引
分别对 secondary 列建立常规、函数索引。

SQL>create index IND_SEC_NORMAL on t(secondary);
Index created
SQL> create index ind_t_fun on t(decode (secondary, 'S', null, 'J', null, 'T', null, secondary));
Index created
Executed in 28.049 seconds
 
索引 ind_t_fun 将 S、T、J 值转化为 null,剔出了建立索引的过程。从索引段信息看,两个索引所占的空间差异比较大,也证明了这点。
 
SQL> select * from dba_segments where segment_name='IND_SEC_NORMAL';

OWNER    SEGMENT_NAME  SEGMENT_TYPE             BYTES     BLOCKS    EXTENTS
-------  ------------- ------------------  ---------- ---------- ----------
SYS      IND_T_FUN     INDEX                 75497472       9216         80  

Executed in 0.733 seconds

SQL> select * from dba_segments where segment_name=upper('ind_t_fun');

OWNER  SEGMENT_NAME  SEGMENT_TYPE          BYTES     BLOCKS    EXTENTS
------ ------------- --------------   ---------- ---------- ----------
SYS    IND_T_FUN     INDEX                 65536          8          1  

Executed in 0.156 seconds

注:本结果经过额外处理,用于方便显示;
 
可以看出,同样是对一个数据列加索引。普通索引类型 Ind_sec_normal 占据 80 个区,9216 个数据块,空间约占 75.5M。而函数索引 ind_t_fun 的空间只用了初始分配的 1 个区,8 个数据块,空间约占 65K。由此,空间优势立现!
 
收集统计数据,由于是实验性质,而且数据量大,采用高采样率收集统计信息。
SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade => true, estimate_percent => 100,method_opt => 'for all indexed columns');

PL/SQL procedure successfully completed

Executed in 60.403 seconds

3、检索效率分析
针对数据量 273 的 W 取值进行分析。
直接索引搜索:

SQL> select * from t where secondary='W';

已选择 273 行。

已用时间:  00: 00: 00.37

执行计划
----------------------------------------------------------
Plan hash value: 1573525374

--------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   273 | 25935 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |   273 | 25935 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_SEC_NORMAL |   273 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("SECONDARY"='W')

统计信息
----------------------------------------------------------
       775  recursive calls
         0  db block gets
       272  consistent gets
        21  physical reads
         0  redo size
     28339  bytes sent via SQL*Net to client
       583  bytes received via SQL*Net from client
        20  SQL*Net roundtrips to/from client
        16  sorts (memory)
         0  sorts (disk)
       273  rows processed
 
发现采用 W 作为搜索值时,是进行了索引搜索。下面是用函数索引搜索进行对比。

SQL> select * from t where decode(secondary,'S',null,'J',null,'T',null,secondary)='W';

已选择 273 行。

已用时间:  00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 3192598969
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   273 | 25935 |   116   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |   273 | 25935 |   116   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_T_FUN |   273 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(DECODE("SECONDARY",'S',NULL,'J',NULL,'T',NULL,"SECONDARY")='W')

统计信息
----------------------------------------------------------
        45  recursive calls
         0  db block gets
       140  consistent gets
         0  physical reads
         0  redo size
     13225  bytes sent via SQL*Net to client
       583  bytes received via SQL*Net from client
        20  SQL*Net roundtrips to/from client
         1  sorts (memory)
         0  sorts (disk)
       273  rows processed
 
对比后,我们可以发现,使用函数索引的方法,在执行时间、物理逻辑读、CPU 使用上有一定差异。

 
结论:使用函数索引处理偏值方法,在一定长度上优化查询效率和索引结构。上表的数据表明,会使逻辑物理读的消耗很大程度的减少(索引结构简化),同时连带影响执行时间的缩小。因为使用函数要进行计算,CPU 使用率相对较高,在可以接受的范围内。
 
但是,这种方法是存在一些限制的,应用前一定要仔细规划。
首先,数据表数据要保证较大。因为毕竟函数索引的建立和搜索较普通索引消耗大,如果数据表小,带来的优化程度不能弥补消耗的成本,结果可能得不偿失。笔者进行的一系列实验中,也发现在数据量中等偏小时,这种性能优势不能凸显。
 
其次,列值倾斜趋势明显。通过开篇的讨论我们不难发现,列值倾斜的程度越高,使用函数索引剔出的数据量也就越大,生成的索引树结构也就越小越优化。这一点是本方法的核心!
 
最后,使用函数索引搜索时,搜索的取值频数越高,优化效果越好。在本例中,取值 W 的列有 273 行,可以看出明显的性能优化。当我们选择值有 9 条数据的 Q 值时,这种优化趋势可以看到,但是明显程度降低(实验结果略)。这里的原因可能是数据量小时,两种方法逻辑物理读的差异度缩小。

欢迎加入本站公开兴趣群
软件开发技术群
兴趣范围包括:Java,C/C++,Python,PHP,Ruby,shell等各种语言开发经验交流,各种框架使用,外包项目机会,学习、培训、跳槽等交流
QQ群:26931708

Hadoop源代码研究群
兴趣范围包括:Hadoop源代码解读,改进,优化,分布式系统场景定制,与Hadoop有关的各种开源项目,总之就是玩转Hadoop
QQ群:288410967 

鲜花

握手

雷人

路过

鸡蛋

相关阅读

最新评论

热门频道

  • 大数据
  • 商业智能
  • 量化投资
  • 科学探索
  • 创业

即将开课

 

GMT+8, 2018-12-15 22:35 , Processed in 0.157193 second(s), 24 queries .