ITPub博客

首页 > 数据库 > 数据库开发技术 > 优化你的系统--索引(一) 正确使用索引

优化你的系统--索引(一) 正确使用索引

原创 数据库开发技术 作者:foreverlee 时间:2005-02-03 03:17:55 0 删除 编辑

优化你的系统--索引(一) 正确使用索引

正确应用索引可以明显提高应用性能.
正确应用索引包括两部分,创建正确的索引和这确使用索引.
这里我们关心后者,开发人员如何正确使用索引.

首先建立测试环境.

SQL> create table test (id number,tu_mdn varchar2(20),name varchar2(20)) tablespace users;

表已创建。

[@more@]

优化你的系统--索引(一) 正确使用索引

正确应用索引可以明显提高应用性能.
正确应用索引包括两部分,创建正确的索引和这确使用索引.
这里我们关心后者,开发人员如何正确使用索引.

首先建立测试环境.

SQL> create table test (id number,tu_mdn varchar2(20),name varchar2(20)) tablespace users;

表已创建。

SQL> begin
  2       for i in 1..10000 loop
  3  insert into test values (i,13300000000+i,'索引测试'||i);
  4       end loop;
  5       commit;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select count(*) from test;

  COUNT(*)
----------
     10000

SQL> select * from test where rownum<10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
       667 13300000667          索引测试667
       668 13300000668          索引测试668
       669 13300000669          索引测试669
       670 13300000670          索引测试670
       671 13300000671          索引测试671
       672 13300000672          索引测试672
       673 13300000673          索引测试673
       674 13300000674          索引测试674
       675 13300000675          索引测试675

已选择9行。

SQL> analyze table test compute statistics;

表已分析。

SQL> create index test_tu_mdn_idx on test(tu_mdn);

索引已创建。

SQL> analyze index test_tu_mdn_idx compute statistics;

索引已分析

了解了什么情况下会导致索失效
典型的几种索引失效的例子.

1 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn定义为varchar2(20),但在查询时把number类型做为where条件传给Oracle,这样会导致索引失效.

SQL> set autotrace on
SQL> select * from test where tu_mdn=13333333333;

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=14)
   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=14)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
        269  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
         
正确的sql
SQL> select * from test where tu_mdn='13333333333';

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=14)

   2    1     INDEX (RANGE SCAN) OF 'TEST_TU_MDN_IDX' (NON-UNIQUE) (Co
          st=1 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        269  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

2 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+,-,*,/,! 等)
SQL> create index test_id on test(id);
SQL> analyze index test compute statistics;
SQL> select * from test where id-1=9;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=260
          0)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=5 Card=100 Bytes=2600)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

正确sql:
SQL> select * from test where id=10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=26)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID' (NON-UNIQUE) (Cost=1 Car
          d=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


3 使用Oracle内部函数导致索引失效.
对于这样情况应当创建基于函数的索引.
SQL> select * from test where round(id)=10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=260
          0)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=5 Card=100 Bytes=2600)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


创建基于函数的索引解决问题:       
SQL> create index test_id_fbi_idx on test(round(id));

索引已创建。
SQL> analyze index test_id_fbi_idx compute statistics;

索引已分析
SQL> select * from test where round(id)=10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=260
          0)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=100 B
          ytes=2600)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID_FBI_IDX' (NON-UNIQUE) (Co
          st=1 Card=40)

Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
         

还有一些操作会导致索引实效,我只说明原因和避免方法.


4 避免在索引列使用not,is null,is not null,Oracle不维护空值.
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引 .
对于单列索引,如果列包含空值,索引中将不存在此记录.
对于复合索引,如果每个列都为空,索引中同样不存在此记录. 
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.


5 用>=代替> 当然前提是你对业务做出正确的判断.
比如我想找出id大于9000的数据,那么有两种方法可以查出.
select * from test where id > 9000; t1
已用时间:  00: 00: 00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1000 Bytes=2
          6000)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=16 Card=1000
           Bytes=26000)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID_IDX' (NON-UNIQUE) (Cost=6
           Card=1000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        150  consistent gets
          0  physical reads
          0  redo size
      38761  bytes sent via SQL*Net to client
       1229  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

select * from test  where id >= 9001; t2

已用时间:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1002 Bytes=2
          6052)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=16 Card=1002
           Bytes=26052)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID_IDX' (NON-UNIQUE) (Cost=6
           Card=1002)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        150  consistent gets
          0  physical reads
          0  redo size
      38820  bytes sent via SQL*Net to client
       1229  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 
      

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

请登录后发表评论 登录
全部评论

注册时间:2008-11-26

  • 博文量
    72
  • 访问量
    1356224