首页 数据库 mysql教程 利用虚拟索引(VirtualIndex)优化数据库的案例分析

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

Jun 07, 2016 pm 04:03 PM
优化 使用 数据库 索引 虚拟

当我们在对生产库做优化的时候,主要就是对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生产库中是不太现实的。本文主要是起到一个抛砖引玉的作用,当我们面对一个数据库优化的场景,需要测试创建某个特定条件的索引是否会给系统带来性能提升,就可以借助虚拟索引来测试,因为在生产库中通常不允许随意创建索引,而维护索引对数据库而言也是一笔不小的开销,况且如果索引创建不当,可能使数据库性能更糟糕。
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您听不到任何人,如何修复音频
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
WWE 2K25:如何解锁Myrise中的所有内容
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片 iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片 Jul 18, 2024 am 05:48 AM

苹果公司最新发布的iOS18、iPadOS18以及macOSSequoia系统为Photos应用增添了一项重要功能,旨在帮助用户轻松恢复因各种原因丢失或损坏的照片和视频。这项新功能在Photos应用的"工具"部分引入了一个名为"已恢复"的相册,当用户设备中存在未纳入其照片库的图片或视频时,该相册将自动显示。"已恢复"相册的出现为因数据库损坏、相机应用未正确保存至照片库或第三方应用管理照片库时照片和视频丢失提供了解决方案。用户只需简单几步

C++ 程序优化:时间复杂度降低技巧 C++ 程序优化:时间复杂度降低技巧 Jun 01, 2024 am 11:19 AM

时间复杂度衡量算法执行时间与输入规模的关系。降低C++程序时间复杂度的技巧包括:选择合适的容器(如vector、list)以优化数据存储和管理。利用高效算法(如快速排序)以减少计算时间。消除多重运算以减少重复计算。利用条件分支以避免不必要的计算。通过使用更快的算法(如二分搜索)来优化线性搜索。

如何在PHP中处理数据库连接错误 如何在PHP中处理数据库连接错误 Jun 05, 2024 pm 02:16 PM

PHP中处理数据库连接报错,可以使用以下步骤:使用mysqli_connect_errno()获取错误代码。使用mysqli_connect_error()获取错误消息。通过捕获并记录这些错误信息,可以轻松识别并解决数据库连接问题,确保应用程序的顺畅运行。

在PHP中使用MySQLi建立数据库连接的详尽教程 在PHP中使用MySQLi建立数据库连接的详尽教程 Jun 04, 2024 pm 01:42 PM

如何在PHP中使用MySQLi建立数据库连接:包含MySQLi扩展(require_once)创建连接函数(functionconnect_to_db)调用连接函数($conn=connect_to_db())执行查询($result=$conn->query())关闭连接($conn->close())

如何在 Golang 中使用数据库回调函数? 如何在 Golang 中使用数据库回调函数? Jun 03, 2024 pm 02:20 PM

在Golang中使用数据库回调函数可以实现:在指定数据库操作完成后执行自定义代码。通过单独的函数添加自定义行为,无需编写额外代码。回调函数可用于插入、更新、删除和查询操作。必须使用sql.Exec、sql.QueryRow或sql.Query函数才能使用回调函数。

如何用 Golang 连接远程数据库? 如何用 Golang 连接远程数据库? Jun 01, 2024 pm 08:31 PM

通过Go标准库database/sql包,可以连接到MySQL、PostgreSQL或SQLite等远程数据库:创建包含数据库连接信息的连接字符串。使用sql.Open()函数打开数据库连接。执行SQL查询和插入操作等数据库操作。使用defer关闭数据库连接以释放资源。

《黑神话:悟空》Xbox 版被曝因'内存泄漏”而延期,PS5 版优化进行中 《黑神话:悟空》Xbox 版被曝因'内存泄漏”而延期,PS5 版优化进行中 Aug 27, 2024 pm 03:38 PM

近日,《黑神话:悟空》在全球范围内都引发了巨大的关注,各平台的同时在线人数都再创新高,这款游戏在多个平台取得了巨大的商业成功。《黑神话:悟空》的Xbox版延期虽然《黑神话:悟空》已于PC和PS5平台发布,但其Xbox版一直没有确切消息。据了解,官方已确认《黑神话:悟空》将登陆Xbox平台。但具体上线日期尚未公布。最近有消息称,Xbox版的延期是由于技术问题所致。据相关博主透露,他在Gamescom期间与开发人员和"Xbox内部人士"的交流中得知,《黑神话:悟空》的Xbox版存

如何使用C++处理数据库连接和操作? 如何使用C++处理数据库连接和操作? Jun 01, 2024 pm 07:24 PM

在C++中使用DataAccessObjects(DAO)库连接和操作数据库,包括建立数据库连接、执行SQL查询、插入新记录和更新现有记录。具体步骤为:1.包含必要的库语句;2.打开数据库文件;3.创建Recordset对象执行SQL查询或操作数据;4.遍历结果或按照具体需求更新记录。

See all articles