利用虚拟索引（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

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