利用虚拟索引(Virtual Index)优化数据库的案例分析

当我们在对生产库做优化的时候,主要就是对SQL语句的优化,包括语句的等价改写等,但其中很大一部分情况,又与索引有关。如果能合理利用合适的索引,可以使原本走全表扫描产生的逻辑读大大降低,提高数据库的性能。由于Oracle数据库中的索引本身就要占用磁盘空间,维护索引需要一定的开销,如何才能知道创建某个索引,会给数据带来性能的提升,而又不至于判断失误,创建了一个不恰当的索引,最后又不得不删除呢?这种情况下,我们可以利用Oralce提供的虚拟索引,即nosegment索引,它并不占用磁盘资源,只是在数据字典中增加一个定义。它为DBA在创建索引对提升数据库性能的方面提供了一定的参考。下面来看具体测试和分析:

SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             675286400 bytes
Database Buffers          155189248 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.

--本测试在11.2.0.3.0环境,与10g略有不同
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--创建测试表fakeind并插入数据
SQL> drop table fakeind_test;
drop table fakeind_test
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table fakeind_test as select * from dba_objects;

Table created.

SQL> insert into fakeind_test select * from fakeind_test;

75540 rows created.

SQL> /

151080 rows created.

SQL> /

302160 rows created.

SQL> select count(*) from fakeind_test;

  COUNT(*)
----------
    604320

--开始测试,执行查询
SQL> set line 130 pages 130
SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>44500 and object_id<45000);

3992 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1190425891

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  3816 |   160K|  4667   (1)| 00:00:57 |
|*  1 |  HASH JOIN RIGHT SEMI|              |  3816 |   160K|  4667   (1)| 00:00:57 |
|   2 |   VIEW               | VW_NSO_1     |  3819 | 49647 |  2333   (1)| 00:00:28 |
|*  3 |    TABLE ACCESS FULL | FAKEIND_TEST |  3819 | 19095 |  2333   (1)| 00:00:28 |
|   4 |   TABLE ACCESS FULL  | FAKEIND_TEST |   604K|    17M|  2331   (1)| 00:00:28 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"="OBJECT_ID")
   3 - filter("OBJECT_ID">44500 AND "OBJECT_ID"<45000)


Statistics
----------------------------------------------------------
         23  recursive calls
          0  db block gets
      17436  consistent gets
          0  physical reads
          0  redo size
     144488  bytes sent via SQL*Net to client
       3445  bytes received via SQL*Net from client
        268  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3992  rows processed

可以看到,用CTAS创建的测试表fakeind上目前并没有索引,因此在生成的执行计划中,该条SQL语句只能走全表扫描

--创建虚拟索引(在普通创建索引命令后加一个nosegmnet即可)
SQL>  create index ind_fake_id on fakeind_test(object_id) nosegment;

Index created.

--设置隐含参数使虚拟索引生效
SQL> alter session set "_use_nosegment_indexes"=true;  --注意必须要写双引号,单引号不行

Session altered.

SQL> set autot off

--查看表是否被分析过
SQL> select table_name,last_analyzed from dba_tables where table_name='FAKEIND_TEST';

TABLE_NAME                     LAST_ANALYZED
------------------------------ ------------------
FAKEIND_TEST

--收集测试表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'FAKEIND_TEST',degree=>4,estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

--再次确认表的分析情况
SQL> select table_name,last_analyzed from dba_tables where table_name='FAKEIND_TEST';

TABLE_NAME                     LAST_ANALYZED
------------------------------ ------------------
FAKEIND_TEST                    17-SEP-14

--再次查询测试表
SQL> set autot trace
SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>44500 and object_id<45000);

3992 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2531911586

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |  3904 |   308K|     12  (17)| 00:00:01 |
|   1 |  VIEW                          | VM_NWVW_2    |  3904 |   308K|    12  (17)| 00:00:01 |
|   2 |   HASH UNIQUE                  |              |  3904 |   179K|    12  (17)| 00:00:01 |
|*  3 |    HASH JOIN                   |              |  3904 |   179K|    11  (10)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           |  IND_FAKE_ID  |  3819 | 19095 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| FAKEIND_TEST |  3819 |   156K|     8   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          |  IND_FAKE_ID  |  3819 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"="OBJECT_ID")
   4 - access("OBJECT_ID">44500 AND "OBJECT_ID"<45000)
   6 - access("OBJECT_ID">44500 AND "OBJECT_ID"<45000)


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

SQL>  set autot off

此时利用虚拟索引获得的执行计划中, COST从之前的4000多降低到12,执行时间也从57s到1s,由此可以判断,当加上真实索引后,性能会大大提高。

--创建真实索引
SQL> create index ind_real_id on fakeind_test(object_id);

Index created.

SQL> set autot trace
SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>45500 and object_id<50000);

35992 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2531911586

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              | 41816 |  3307K|       |    548   (1)| 00:00:07 |
|   1 |  VIEW                          | VM_NWVW_2    | 41816 |  3307K|       |   548   (1)| 00:00:07 |
|   2 |   HASH UNIQUE                  |              | 41816 |  1919K|  2472K|   548   (1)| 00:00:07 |
|*  3 |    HASH JOIN                   |              | 41816 |  1919K|       |    53   (2)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           |  IND_FAKE_ID  | 34375 |   167K|       |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| FAKEIND_TEST | 34375 |  1409K|       |    49   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          |  IND_FAKE_ID  | 34375 |       |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"="OBJECT_ID")
   4 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)
   6 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       11017  consistent gets
         82  physical reads
          0  redo size
    1293055  bytes sent via SQL*Net to client
      26908  bytes received via SQL*Net from client
       2401  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      35992  rows processed

虽然创建了真实索引,但数据库却仍旧在用虚拟索引,此时COST和TIME反而还上去了一点,那么需要先禁用虚拟索引

SQL> alter session  set "_use_segment_indexes"=false;

--禁用虚拟索引后继续查看刚才的SQL
SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>45500 and object_id<50000);

35992 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 750753197

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              | 34375 |  1443K|   2414   (1)| 00:00:29 |
|*  1 |  HASH JOIN RIGHT SEMI|              | 34375 |  1443K|  2414   (1)| 00:00:29 |
|   2 |   VIEW               | VW_NSO_1     | 34375 |   436K|    79   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN  | IND_REAL_ID  | 34375 |   167K|    79   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | FAKEIND_TEST |   604K|    17M|  2331   (1)| 00:00:28 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"="OBJECT_ID")
   3 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       11017  consistent gets
          0  physical reads
          0  redo size
    1293055  bytes sent via SQL*Net to client
      26908  bytes received via SQL*Net from client
       2401  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      35992  rows processed

虽然使用真实索引之后,性能提升并不如使用虚拟索引时那样多,但至少比最初没有索引的情况下,要 快了将近28秒,COST也减少了将近一半,当真实索引建立完毕以后,可以对虚拟索引进行删除,以免白白占用一个对象名,删除语法和删除普通索引一致。

虚拟索引有几个要注意的地方:

--虚拟索引并不存在于dba_indexes视图
SQL> select index_name from  dba_indexes where index_name=' IND_FAKE_ID';

no rows selected

--无法创建与虚拟索引同名的真实索引
SQL> create index ind_fake_id on fakeind_test(object_name);
create index ind_fake_id on fakeind_test(object_name)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

--无法使用alter命令来修改或重建索引
SQL>  alter index ind_fake_id  rename to ind_fake_name;
alter index ind_fake_id rename to ind_fake_name
*
ERROR at line 1:
ORA-08114: can not alter a fake index


SQL>  alter index ind_fake_id  rebuild;
alter index ind_fake_id rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

--查看虚拟索引的方法
SQL> set autot off
SQL> SELECT index_owner, index_name  
  2  FROM dba_ind_columns
  3  WHERE index_name NOT LIKE 'BIN$%' 
  4  MINUS
  5  SELECT owner, index_name 
  6  FROM dba_indexes;

INDEX_OWNER                    INDEX_NAME
------------------------------ ------------------------------
SYS                             IND_FAKE_ID

--drop table并不会直接删除虚拟索引,必须purge recyclebin或者显示drop index
SQL> conn zlm/oracle  -- 由于SYS用户默认没有recyclebin,这里用zlm用户做测试
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.
SQL> create index ind_t1_id on t1(object_id) nosegment;

Index created.

SQL> drop table t1;

Table dropped.

SQL> SELECT index_owner, index_name  
  2  FROM dba_ind_columns
  3  WHERE index_name NOT LIKE 'BIN$%' 
  4  MINUS
  5  SELECT owner, index_name 
  6  FROM dba_indexes;

INDEX_OWNER                    INDEX_NAME
------------------------------ ------------------------------
ZLM                             IND_T1_ID     --尽管已经删除了T1表,但是虚拟索引仍然还在

SQL> purge recyclebin;

Recyclebin purged.

SQL> SELECT index_owner, index_name  
  2  FROM dba_ind_columns
  3  WHERE index_name NOT LIKE 'BIN$%' 
  4  MINUS
  5  SELECT owner, index_name 
  6  FROM dba_indexes;

no rows selected

--清空回收站后,虚拟索引才被彻底删除了,或者直接显示删除虚拟索引
SQL> drop index ind_t1_id;

总结:

这个测试其实并没有做的很完善,SQL语句选取得不好,正常情况下,原有语句所涉及的表至少且肯定会有一个主键索引,没有索引的表在OLTP生产库中是不太现实的。本文主要是起到一个抛砖引玉的作用,当我们面对一个数据库优化的场景,需要测试创建某个特定条件的索引是否会给系统带来性能提升,就可以借助虚拟索引来测试,因为在生产库中通常不允许随意创建索引,而维护索引对数据库而言也是一笔不小的开销,况且如果索引创建不当,可能使数据库性能更糟糕。


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值