ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 说说函数索引

说说函数索引

原创 Linux操作系统 作者:realkid4 时间:2011-05-22 23:12:35 0 删除 编辑

我们进行数据库检索优化的方法,通常是对特定条件列加索引,减少由于全表扫描带来的逻辑物理IO消耗。索引的种类很多,我们经常使用的B*树索引,由于结构简单、适应性强,可以应对大多数数据访问优化需求。除B*树索引外,其他一些索引类型,也在一些场合中扮演着独特的地位。本篇来介绍其中的函数索引。

 

1、从B*树索引的失效谈起

 

和通常一样,我们准备实验环境。

 

 

SQL> select * from v$version where rownum<2;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

 

SQL> create table t as select * from dba_objects;

Table created

//构建两个索引用作实验对象

SQL> create index idx_t_owner on t(owner);

Index created

 

SQL> create index idx_t_ddlt on t(last_ddl_time);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

环境中,我们在数据表T上建立了一般意义的索引。当我们进行检索的时候,CBO会适时选择合适的索引执行计划。

 

 

SQL> explain plan for select * from t where owner='SCOTT';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |  2419 |   229K|    71   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  2419 |   229K|    71   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |  2419 |       |     6   (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

14 rows selected

 

 

但是,很多时候,我们可能会遇到在where条件里对索引列进行函数处理的情况。比如,选择owner列取值第二个字母是“c”的数据列,或者选取在特定天进行ddl操作的对象信息。这样的情况下,直接的想法就是在where条件列中加入列函数处理,但是这样做,会带来B*树索引的失效问题。

 

SQL> explain plan for select * from t where substr(owner,2,1)='C';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   726 | 70422 |   283   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |   726 | 70422 |   283   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(SUBSTR("OWNER",2,1)='C')

13 rows selected

 

 

 

在对条件列owner进行substr操作之后,生成的执行计划就不会带有索引路径,出现全表扫描。如果列上的B*树普通索引就是为该查询对应的用例服务的,那么这个索引的存在就失去了意义。

 

 

那么,这种时候应该如何处理呢?答案是:在SQL语句本身不存在重构优化的空间时(此种情况通常出现在系统的运维阶段),可以考虑使用函数索引来解决问题。

 

2、函数索引

 

函数索引与通常B*树索引的结构,存在很大相似性。区别就在于形成树结构的叶子节点上,保存的不是索引列的取值,而是经过特定的函数处理过的索引列值。

 

 

这样的结构,进行搜索的时候,就可以直接使用到函数索引的叶子节点,获取到对应的rowid集合。要求是出现于构建函数索引完全相同的函数条件。

 

首先,我们来构建函数索引。

 

 

SQL> create index idx_t_ownerf on t(substr(owner,2,1));

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

构建函数索引的语法和一般索引的语法没有过多的区别,最大的差异就是在声明索引列的位置上,写清楚应用的函数语句。此时,数据字典视图系列中,已经反映出函数索引的不同。

 

 

SQL> select index_type from dba_indexes where index_name='IDX_T_OWNERF';

INDEX_TYPE

---------------------------

FUNCTION-BASED NORMAL

 

 

此时,我们再进行查询,执行计划会发生变化。

 

 

SQL> explain plan for select * from t where substr(owner,2,1)='C';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2485331276

--------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |  4839 |   467K|   135   (0)

|   1 |  TABLE ACCESS BY INDEX ROWID| T            |  4839 |   467K|   135   (0)

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNERF |  4839 |       |     9   (0)

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(SUBSTR("OWNER",2,1)='C')

14 rows selected

 

 

 

加入函数索引之后,我们可以发现同样的SQL语句,执行计划发生变化。函数索引开始起效。

 

那么,函数索引的本质是什么呢?我们检查数据字典视图,就可以发现函数索引的本质。

 

SQL> col table_name for a20;

SQL> col table_owner for a20;

SQL> col column_name for a30;

SQL> select table_owner, table_name, column_name from dba_ind_columns where index_name='IDX_T_OWNERF';

 

TABLE_OWNER          TABLE_NAME           COLUMN_NAME

-------------------- -------------------- ------------------------------

SYS                  T                    SYS_NC00016$

 

SQL> select column_expression from dba_ind_expressions where index_name = 'IDX_T_OWNERF';

 

COLUMN_EXPRESSION

-------------------------------------

SUBSTR("OWNER",2,1)

 

SQL> select column_name,data_type,data_default from dba_tab_cols where wner='SYS' and table_name='T' and column_name='SYS_NC00016$';

 

COLUMN_NAME          DATA_TYPE            DATA_DEFAULT

-------------------- -------

SYS_NC00016$         VARCHAR2             SUBSTR("OWNER",2,1)

 

 

检查了三个视图的情况,我们可以清楚的看出Oracle函数索引的本质。Oracle建立函数索引之后,就会先建立出一个不可见的内部列(SYS_NC00016$)。之后,对这个列建立普通的B*树索引。为了保证该列在不受影响的情况下进行数据生成,使用默认值技术,在数据插入或者变化的时候,进行同步。

 

 

3、函数索引使用

 

函数索引是一种很特殊的索引类型,可以应对开发阶段出现的对数据列加函数处理SQL优化。但是,笔者以为,函数索引的使用还是应当注意一些细节的,在大部分场合下,函数索引可以作为一种应急或者是不得为之的策略。

 

首先,函数索引的综合消耗要大于普通的B*树索引。相对于传统索引,函数索引要保证创造的函数列数据一致性和多次进行函数计算。这样的消耗要远大于普通B*树索引;

 

其次,函数索引的适应范围较小。函数索引其效果的最大要素就是函数的使用和定义是100%相同。如第二部分的例子中,取字符串的第二位字串。如果有一个变更的需求,要求取第三位,这样原来的那个函数索引就不能发挥效应了。而相对来说,普通的B*树索引参与各种SQL的能力要很多。应该说,函数索引的针对性很强,如果这个需求不属于关键需求,这样性价比略差。

 

 

最后,函数索引通常是一种事后补救措施。笔者认为,一个良好设计的应用,一个划分合理的数据库逻辑结构,应该是可以避免函数操作数据列的SQL大量出现的。只有在系统上线之后,开发团队开发的问题暴露出来,但是也没有精力进行修改时,运维人员才开始使用函数索引,保证系统功能能够实现。

 

 

对开发人员和开发DBA而言,函数索引通常是不得已为之的方案,要保证在SQL和数据表结构权衡无效的情况下,再考虑使用函数索引。

 

首先,考虑SQL结构的优化。这个方法可以消灭掉很多看似不得不使用函数索引的场合。如字符串类型比较、日期匹配等等,都可以通过代码检查和SQL改写来避免进入函数索引的状况。下面一个例子:

 

 

//获取前一天进行ddl操作的对象列表

SQL> select count(*) from t where trunc(last_ddl_time)=trunc(sysdate)-1;

 

  COUNT(*)

----------

         9

 

 

日期型操作最大的问题就是时分秒结构的处理。Date类型本身是带有时分秒信息的,而进行查询的时候,常常是使用特定的年月日。这样,就会带来一些检索条件的问题。很多开发人员,就是直接使用trunc函数,将数据列上的时分秒信息进行裁剪。这样的确简单,而且满足需求。但是也留下了列索引失效的隐患。

 

 

正确的解决方式,应该将SQL进行改写,变等于条件为范围条件。如下:

 

 

SQL> explain plan for select count(*) from t where last_ddl_time between to_date('2011-5-20 00:00:00','yyyy-mm-dd hh24:mi:ss')

  2   and to_date('2011-5-20 23:59:59','yyyy-mm-dd hh24:mi:ss');

 

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3824876144

--------------------------------------------------------------------------------

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |            |     1 |     8 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |            |     1 |     8 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_T_DDLT |    91 |   728 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("LAST_DDL_TIME">=TO_DATE(' 2011-05-20 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "LAST_DDL_TIME"<=TO_DATE(' 2011-05-2

              23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

 

16 rows selected

 

 

经过改写,没有使用函数索引,原有的B*树索引起效。很多时候,经过SQL的重新思考,是可以避免函数索引使用场合出现的。特别是在项目的开发阶段,这个尤为重要。

 

 

其次,就是对设计表的改进。我们常说一范式:列不可分。如果出现很多的对数据列的函数处理,我们就需要重新审视我们的设计表方案。是不是存在设计不合理、没有考虑到实际业务技术需求的方面。当SQL没有优化空间时,设计表的重构,冗余字段的加入可能是比较好的思路方法。

 

 

 

4、结论

 

本篇从一般的函数索引,谈到了SQL的改写和设计表优化。核心要义就是一点,慎用函数索引。而且,在绝大多数的情况下,我们是不需要使用函数索引的。只要能够理智冷静的分析实际需求和SQL结构,通常都可以获取到一个折中的方案。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-696006/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7678685