巧用函数索引解决数据倾斜列查询
时间:2014-12-08 14:22 来源:linux.it.net.cn 作者:IT
首先声明：本方法是受到dbsnake的指导，再次感谢指点。
通常来说，索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看，列值都是分布在叶节点位置。这样，通过树结构搜索得到的叶节点数量效率比较高。
实际中，我们常常遇到数据列值倾斜的情况。就是说，整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里，其他取值比例极少。比如：一个数据列值有“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使用上有一定差异。
普通索引
函数索引
执行时间
00: 00: 00.37
00: 00: 00.04
CPU使用
11
116
consistent gets
272
140
physical reads
21
0
结论：使用函数索引处理偏值方法，在一定长度上优化查询效率和索引结构。上表的数据表明，会使逻辑物理读的消耗很大程度的减少（索引结构简化），同时连带影响执行时间的缩小。因为使用函数要进行计算，CPU使用率相对较高，在可以接受的范围内。
但是，这种方法是存在一些限制的，应用前一定要仔细规划。
首先，数据表数据要保证较大。因为毕竟函数索引的建立和搜索较普通索引消耗大，如果数据表小，带来的优化程度不能弥补消耗的成本，结果可能得不偿失。笔者进行的一系列实验中，也发现在数据量中等偏小时，这种性能优势不能凸显。
其次，列值倾斜趋势明显。通过开篇的讨论我们不难发现，列值倾斜的程度越高，使用函数索引剔出的数据量也就越大，生成的索引树结构也就越小越优化。这一点是本方法的核心！
最后，使用函数索引搜索时，搜索的取值频数越高，优化效果越好。在本例中，取值W的列有273行，可以看出明显的性能优化。当我们选择值有9条数据的Q值时，这种优化趋势可以看到，但是明显程度降低（实验结果略）。这里的原因可能是数据量小时，两种方法逻辑物理读的差异度缩小。
(责任编辑：IT)
| 首先声明：本方法是受到dbsnake的指导，再次感谢指点。 通常来说，索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看，列值都是分布在叶节点位置。这样，通过树结构搜索得到的叶节点数量效率比较高。 实际中，我们常常遇到数据列值倾斜的情况。就是说，整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里，其他取值比例极少。比如：一个数据列值有“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值时，这种优化趋势可以看到，但是明显程度降低（实验结果略）。这里的原因可能是数据量小时，两种方法逻辑物理读的差异度缩小。 (责任编辑：IT) |