ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 聊聊索引和SQL优化

聊聊索引和SQL优化

原创 Linux操作系统 作者:realkid4 时间:2011-02-03 00:36:36 0 删除 编辑

 

和一个朋友聊起索引和执行路径的问题,觉得很有意思,就把一点想法写下来,权当一个记录。

 

讨论是这样:有一个数据表,其中有字段为月份(使用数字类型)。根据业务的需要,很多搜索都是依据这个月份字段来进行的。于是从提高整体搜索性能角度,选择该列作为一个索引列。但是某个月份的数据是在一个统一的时间点被插入到系统中。所以,该表中所包含对应月份数据,是逐渐的增加的。开始的一段时间里,可能只有一个月份的数据。当进行使用月份作为搜索条件的SQL语句时,朋友发现执行路径并没有使用索引。

 

 

问题原理和解释

 

这个问题其实是比较好理解的。设置了索引,而执行路径没有选择索引路径。最直接的可能性就是Oracle优化器认为当前的数据分布情况下,使用全表扫描的成本更低。

 

这里面我们再说一下Oracle优化器。SQL语句本质上是一种描述性语句,本身不会决定获取数据的操作和方法。Oracle对于SQL语句的解析parse中,需要对该SQL生成执行计划,也就是确定这个SQL进行操作的方式。从SQL语句到执行计划的过程,其实就是Oracle优化器的主要工作。

 

Oracle优化器主要是两个类型,基于规则(Rule-Based Optimizer RBO)和基于成本(Cost-Based Optimizer CBO)。RBO是早期的一种优化器,是依据SQL语句本身书写的一些规则来确定执行计划。这种方法的优点是规则简单,我们通常指通过表结构、索引结构和执行SQL语句,就可以确定出执行计划。生活是复杂的,简单通常也就伴随着武断。一些时候,RBO生成的执行计划往往不是最好。例如:当我们在where条件中书写一个字段选择,并且字段为索引列。RBO会直接选择索引路径。但是这时候,走索引可能不是很好的选择。因为索引本身读取也需要额外的成本付出。

 

 

于是,CBO应运而生。CBO依据的是数据表和数据项的统计量,包括数据分布情况,取值分布等。根据这些数据当前的实际情况,CBO去生成执行计划。应该说,CBO是更科学、更贴近实际需要的优化器方法。从Oracle9i之后,CBO成为优化器默认的配置(感谢 lixin_2002 的指正  ),成为优化器发展的一个方向。

 

本质上说,RBO是一系列生成规则的集合,而CBO则是一系列参数控制公式的集合。对每个SQL语句,Oracle优化器都会生成多条执行计划,根据收集的统计数据和成本参数(作为系统参数的一部分)为每个执行计划试算出一个成本cost数值,依据最少cost的原则确定选择的执行计划。

 

回到朋友提到的案例。在最开始的时候,数据表中只有一个月份的信息,在月份列上建立了索引对象。当执行带月份的条件查询时,Oracle会生成两份执行计划:全表扫描计划FTS和索引计划。当全表数据都是一个月份的情况下,Oracle如果选择索引执行计划,意味着会搜索全部索引树,并且根据索引树中所有的rowid获取数据表。成本要远远大于直接进行FTS。

 

下面我们进行一个简单实验。

 

SQL> conn scott/tiger@orcl

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

//构建数据表

SQL> create table t as select rownum as ronum, object_id,object_name, 201101 as month from all_objects;

 

Table created

 

SQL> select month,count(*) from t group by month;

 

     MONTH   COUNT(*)

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

201101      40718

 

 

构建索引,并且收集统计量。

 

SQL> create index idx_t_month on t(month);

 

Index created

 

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

 

PL/SQL procedure successfully completed

 

 

首先,月份month列值有一种取值201101,上面建立索引。我们先查看搜索路径。

 

 

SQL> select * from t where month=201101;

 

已选择40718行。

 

已用时间:  00: 00: 00.98

 

执行计划

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 40718 |  1590K|    63   (4)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    | 40718 |  1590K|    63   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("MONTH"=201101)

统计信息

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

        201  recursive calls

          0  db block gets

       2995  consistent gets

          0  physical reads

          0  redo size

    1608149  bytes sent via SQL*Net to client

      30239  bytes received via SQL*Net from client

       2716  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

      40718  rows processed

 

 

执行计划上,可以方便看到执行路径为全表扫描,并没有选择索引路径。如果我们强制要求Oracle生成走索引的路径,可以使用hint。

 

SQL> select /*+ index(t idx_t_month) */ * from t where month=201101;

 

已选择40718行。

 

已用时间:  00: 00: 01.09

 

执行计划

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

Plan hash value: 3445114591

 

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

-----------

 

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

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

|   0 | SELECT STATEMENT            |             | 40718 |  1590K|   349   (1)| 00:00:05 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           | 40718 |  1590K|   349   (1)| 00:00:05 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_MONTH | 40718 |       |    93   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   2 - access("MONTH"=201101)

统计信息

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

         64  recursive calls

          0  db block gets

       5766  consistent gets

         92  physical reads

          0  redo size

    2191670  bytes sent via SQL*Net to client

      30239  bytes received via SQL*Net from client

       2716  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      40718  rows processed

 

 

从执行计划和实际执行计时上看,强制走索引的执行计划明显占劣势。可见,Oracle优化器在做这个选择的时候,是选择成本较低的全表扫面执行计划。

 

所以,我们不难得出结论。我们确定某某列上需要加索引,是由于业务操作需求上,存在对该列访问需求。但是,这不意味着该索引一定会成为所有情况SQL的执行计划。索引路径只是作为Oracle可选的一种执行路径,实际执行情况还要看各种可选路径的成本估算值。也就是说,没有走索引,也不一定是坏事,重点在于是否满足性能需求,要区别对待。

 

 

 

索引方案的使用

 

那么,在什么情况下,索引路径才会执行呢?索引路径成本的组成=读取索引树中符合条件记录的rowid成本+据rowid成本获取数据表块记录成本。只有选择性比较好的时候,这种成本小于全表扫描的时候,Oracle会选择索引路径。

 

继续实验,我们修改一下数据表数据结构。

 

 

SQL> select month,count(*) from t group by month;

 

     MONTH   COUNT(*)

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

    201103       1999

    200112        499

    201102        999

    201101      40718

 

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

 

PL/SQL procedure successfully completed

 

 

我们重新整理了一下数据分布情况,增加了数据的选择性(虽然数据取值稍偏)。我们进行搜索。

 

 

SQL> select * from t where month=201102;

 

已选择999行。

 

已用时间:  00: 00: 00.11

 

执行计划

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

Plan hash value: 3445114591

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

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

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

|   0 | SELECT STATEMENT            |             |  1022 | 39858 |    10   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  1022 | 39858 |    10   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_MONTH |  1022 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("MONTH"=201102)

统计信息

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

        383  recursive calls

          0  db block gets

        209  consistent gets

          8  physical reads

          0  redo size

      44074  bytes sent via SQL*Net to client

       1111  bytes received via SQL*Net from client

         68  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

        999  rows processed

 

 

索引起效果了。Oracle针对这个查询,发现执行索引路径成本较低。反之,对一些SQL,Oracle同样会执行全表扫描。

 

 

SQL> select * from t where month=201101;

 

已选择40718行。

 

已用时间:  00: 00: 01.00

 

执行计划

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 40773 |  1552K|    73   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    | 40773 |  1552K|    73   (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("MONTH"=201101)

统计信息

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

          1  recursive calls

          0  db block gets

       3011  consistent gets

         29  physical reads

          0  redo size

    1608149  bytes sent via SQL*Net to client

      30239  bytes received via SQL*Net from client

       2716  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      40718  rows processed

 

 

这种灵活性,也就是体现出CBO的优势,针对实际情况,执行灵活的执行计划。

 

最后,笔者感觉有必要说明一个原则,就是优化的原则。在相同的情况(软硬件、配置)下,全表获取一百条数据的成本要低于全表获取100万条数据的成本。从100行数据中获取10行数据成本要低于从100万行数据中获取10行数据。随着数据表的容量扩大,系统总会出现瓶颈。我们进行优化的原则就是随着系统容量增加,性能变化处于一个线性变化就可以了。 

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

下一篇: Oracle数据字典
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7677512