Heim >Datenbank >MySQL-Tutorial >利用虚拟索引(VirtualIndex)优化数据库的案例分析

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

WBOY
WBOYOriginal
2016-06-07 16:03:101318Durchsuche

当我们在对生产库做优化的时候,主要就是对SQL语句的优化,包括语句的等价改写等,但其中很大一部分情况,又与索引有关。如果能合理利用合适的索引,可以使原本走全表扫描产生的逻辑读大大降低,提高数据库的性能。由于Oracle数据库中的索引本身就要占用磁盘

当我们在对生产库做优化的时候,主要就是对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 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"
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 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"44500 AND "OBJECT_ID"
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 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"45500 AND "OBJECT_ID"
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 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"
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生产库中是不太现实的。本文主要是起到一个抛砖引玉的作用,当我们面对一个数据库优化的场景,需要测试创建某个特定条件的索引是否会给系统带来性能提升,就可以借助虚拟索引来测试,因为在生产库中通常不允许随意创建索引,而维护索引对数据库而言也是一笔不小的开销,况且如果索引创建不当,可能使数据库性能更糟糕。
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn