http://blog.itpub.net/17203031/viewspace-681311/
实际中,我们常常遇到数据列值倾斜的情况。就是说,整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里,其他取值比例极少。比如:一个数据列值有“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使用上有一定差异。
|
普通索引 |
函数索引 |
执行时间 |
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
但是如果是对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(苦练七十二变,笑对八十一难)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。