关于索引的可用性-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

关于索引的可用性

简介: 作为一名DBA,我们有责任找到并清除这些闲置的以及对性能有负面影响的索引。但我们从哪里开始呢?其实Oracle已经为我们提供了解决之道。  有两种基本的情况:  1、 我们必须确定索引是否被使用,如果索引没有使用,只需要删除它就可以了。

作为一名DBA,我们有责任找到并清除这些闲置的以及对性能有负面影响的索引。但我们从哪里开始呢?其实Oracle已经为我们提供了解决之道。
  有两种基本的情况:
  1、 我们必须确定索引是否被使用,如果索引没有使用,只需要删除它就可以了。
  2、 如果索引被使用了,或认为索引将会被使用,对于这种索引,要确定索引对数据库性能的影响稍微有点难度。
  对于第一种情况(判断索引是否被使用),我们可以对数据库索引进行监视,关键是要监视足够长的时间,可以监视一小时,一天,一周或一个业务季度,这要取决于表上的索引是与什么相关的。
  那该如何监视一个索引呢?其实简单得很,只需要使用ALTER INDEX命令,加上MONITORING USAGE子句就可以了,还是来看一看实例吧:

    SQL> ALTER INDEX pk_emp MONITORING USAGE;
  Index altered.
  SQL> ALTER INDEX ix_emp_sal MONITORING USAGE;
  Index altered.
  当你在该表上进行SELECT,UPDATE,DELETE(没有INSERT)时,一旦使用了索引,就会在V$OBJECT_USAGE动态视图中将该索引标记为在使用中:

SQL> select * from emp where empno = 7844;
  EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  ----- ------ --------- ---- --------- ----- ---- ------
  7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
  SQL> SELECT v.index_name, v.table_name,
  v.monitoring, v.used,
  start_monitoring, end_monitoring
  FROM v$object_usage v, user_indexes u
  WHERE v.index_name = u.index_name;
  INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
  ---------- ---------- --- --- ------------------- -------------------
  PK_EMP EMP YES YES 04/28/2009 10:16:51
  IX_EMP_SAL EMP YES NO 04/28/2009 10:17:01
  就这么简单。显然,如果前面的SELECT语句只是查询EMP表,或许我们应该删除掉IX_EMP_SAL索引。
  如果索引已经被使用,或将被使用,在采取行动(如删除或创建索引)之前,我们必须放聪明点。为了帮助解决这些问题,Oracle为我们提供了一个新特性,INVISIBLE索引,允许我们将索引隐藏起来,隐藏的索引不能使用,但仍然可以通过INSERT,UPDATE和DELETE进行维护。要使一个索引不可见,可以使用CREATE或ALTER INDEX INVISIBLE命令,这里以上面的IX_EMP_SAL索引为例进行演示:
    SQL> create index ix_emp_sal on emp(sal) INVISIBLE;
  SQL> alter index ix_emp_sal INVISIBLE;
  当一个索引被置为INVISIBLE时,应用程序就看不到它了,也不能在任何DML操作中使用它了。优化器也看不到隐藏索引,因此也不会被任何执行计划使用,除非明确指定了一个提示(hint),会话被设置为使用隐藏索引,或者数据库被设置为可以使用所有的隐藏索引,这正是某些DBA梦寐以求的功能,使用一个新的init.ora参数optimizer_use_invisible_indexes,你可以固定会话,或全系统范围内都可以使用隐藏索引,让你有机会测试新建索引在完全移动到生产环境之前的影响,可以通过设置这个初始化参数使用隐藏索引,或在SQL中增加提示使用隐藏索引,如: 
    SQL> alter system set optimizer_use_invisible_indexetrue;
  SQL> alter session set optimizer_use_invisible_indexetrue;
  SQL> select * /*+ index (emp ix_ep_sal) */ ename from emp where sal=1500;
  要使一个索引从不可见状态变为可见状态,使用ALTER INDEX语句+ VISIBLE关键字即可:
    SQL> alter index ix_emp_sal VISIBLE;
  此外,如果你想找出在你的数据库中哪些索引是隐藏的,
    可以查询DBA_,USER_或ALL_INDEXES视图中的VISIBILITY列。

  SQL>select index_name, visibility
  from dba_indexes
  where index_name='IX_EMP_SAL';
  INDEX_NAME VISIBILIT
  -------------- ---------
  IX_EMP_SAL INVISIBLE
  在运用INVISIBLE索引功能时要注意由其它方法创建或修改的隐藏索引,如果你以前写有一个SQL查看索引的结构,现在需要修改一下增加检查VISIBILITY列,否则你看到的仅仅是索引的部分信息,当执行了大量的INSERT,UPDATE或DELETE操作时,这可能会变成一个隐藏的恶梦。
  索引的可用性一向有些黑色艺术,对于删除一个索引是否会对性能产生影响从来都没有明确的判断标准,现在通过监视索引的可用性,并可以修改索引的可见性,DBA完全可以更好地测试和验证索引的可用性了。

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章