首页 / MYSQL / 不可见索引和虚拟索引。

不可见索引和虚拟索引。

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了不可见索引和虚拟索引。，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13621字，纯文字阅读大概需要20分钟。

内容图文

不可见索引和虚拟列上的索引。今天是2014-01-20，今天开始学习其他类型的索引，在此记录一下学习笔记。不可见索引：默认情况下，当我们创建索引后，数据库就会自动的识别到该索引，并开始在成本估算中纳入创建的索引，不可见索引可是控制优化器是否对索引

不可见索引和虚拟列上的索引。

今天是2014-01-20，今天开始学习其他类型的索引，在此记录一下学习笔记。
不可见索引：

默认情况下，当我们创建索引后，数据库就会自动的识别到该索引，并开始在成本估算中纳入创建的索引，不可见索引可是控制优化器是否对索引进行可见。那么不可见索引的用处就是，有时候当我们在创建一个索引往往对一个或是几个sql的执行存在性能提升，也许会对其他sql执行存在弊端，如果使用不可见索引，可以在优化器使用该索引和不使用该索引之间进行选择，从而便于我们决定索引的创建与否。其他在我们删除一个索引的时候一般步骤是将该索引至于不可见，随后运行将其至于不可用，最后删除索引。

创建不可见索引：

SQL> select index_name,index_TYPE FROM USER_INDEXES WHERE table_name='EMP';

no rows selected

SQL> create index emp_idx1 on emp(empno) invisible;

Index created.

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL>

在user_indexes 视图的visibilit字段可以观察该索引是否为不可见索引。

使当前索引在不可见索引和可见索引之间进行切换：

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL> alter index emp_idx1 visible;

Index altered.

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    VISIBLE

SQL> alter index emp_idx1 invisible;

Index altered.

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL>

让优化器如何使用不可见索引？

一般在进行sql优化的时候常用到该功能，当我们发现一个sql中缺少相关索引，但是又不确定该索引创建后对sql到底有多大用处，那么可以使用该方法。注意：当索引被置为不可见的时候，即使使用hits优化器也是不会看到该索引的。

那么其中涉及到一个数据库动态参数：optimizer_use_invisible_indexes，该参数默认为fale，当将该参数改为true时，将对不可见索引进行使用，可以在会话级别和系统级别进行设定（一般我们在会话级别）。

演示过程如下：

eg:SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1'
  2  ;

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL> select * from emp where empno=7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SQL> set linesize 200
SQL> set autotrace trace exp
SQL> r
  1* select * from emp where empno=7902

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7902)

SQL> select /*+index(EMP_IDX1)*/ * FROM EMP WHERE EMPNO=7902;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7902)

SQL> SHOW parameter optimizer_use_  
ORA-00942: table or view does not exist


SQL> conn sys/root as sysdba
Connected.
SQL> show parameter optimizer_use

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> conn amy/rhys 
Connected.
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> set autotrace trace exp      
SQL> select * from emp where empno=7902;

Execution Plan
----------------------------------------------------------
Plan hash value: 3085206398

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    32 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_IDX1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7902)

SQL> alter session set optimizer_use_invisible_indexe=false;
alter session set optimizer_use_invisible_indexe=false
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION


SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> select * from emp where empno=7902;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7902)

SQL>

对于维护不可见索引和其他Ｂ树索引没有不同，后续将准备在索引维护内容进行学习。

虚拟索引：

　虚拟索引顾名思义，就是虚拟的索引不存在段的索引，注意：该索引在数据库中并不存在真正的物理存储信息，而是只是一个定义（有点类似于外部表的定义）信息，当我们测试一个潜在的索引是否对ｓｑｌ执行有效时，如果不是一个大表那么建议使用不可见索引，而对于大表只是为了测试优化器是否使用该索引而不想等到索引创建完就开始测试，那么可以使用该虚拟索引。对于索引创建测试更推荐使用不可见索引而不是虚拟索引。

在此设计到一个参数那就是：_use_nosegment_indexes ，注意该参数并不是意味着数据库就要使用虚拟索引，而是代表该索引在优化器中是否使用其在执行计划内：

SQL> set feedback off
SQL> @getsp.sql
Enter value for par: _use_nosegment

KSPPINM                                            KSPPSTVL             KSPPDESC
-------------------------------------------------- -------------------- ------------------------------------------------------------
_use_nosegment_indexes                             FALSE                use nosegment indexes in explain plan
SQL>

创建虚拟索引：

SQL> create index emp_nosegment_idx1 on emp(deptno) nosegment;
SQL> select index_name,index_type,table_name from user_indexes where table_name='EMP';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
EMP_IDX1                       NORMAL                      EMP
SQL> create index emp_nosegment_idx1 on emp(deptno) nosegment;
SQL> select index_name,index_type,table_name from user_indexes where table_name='EMP';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
EMP_IDX1                       NORMAL                      EMP

　　有一点可以看到，在创建虚拟索引的时候，ｏｒａｃｌｅ并没有提示索引是否创建成功(嘿嘿。。哈哈）？第二，在查询ｕｓｅｒ＿ｉｎｄｅｘｅｓ视图中并没有显示该虚拟索引的信息？

这是因为ｄｂａ＿ｉｎｄｅｘｅｓ是显示的真实有存储信息的索引信息。

SQL> COL COLUMN_NAME FOR A30
SQL> R
  1* select index_name,column_name from user_ind_columns where table_name='EMP'

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP_IDX1                       EMPNO
EMP_NOSEGMENT_IDX1             DEPTNO
SQL>

如何使用虚拟索引，就是通过在ｓｅｓｓｉｏｎ和ｓｙｓｔｅｍ　ｌｅｖｅｌ调节_use_nosegment_indexes。
ｅｇ：

SQL> alter session set "_use_nosegment_indexes"=true;
SQL> select empno from emp where empno=7369;

Execution Plan
----------------------------------------------------------
Plan hash value: 3309675936

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     1 |     3 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_NOSEGMENT_IDX1 |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("EMPNO"=7369)

SQL> alter session set "_use_nosegment_indexes"=false;
SQL> select empno from emp where empno=7369;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     3 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7369)

SQL> alter session set "_use_nosegment_indexes"=true;
SQL> select * from emp where empno=7369;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7369)

SQL> alter session set "_use_nosegment_indexes"=false;
SQL> drop index EMP_NOSEGMENT_IDX1
  2  ;
SQL> create index EMP_NOSEGMENT_IDX1 on emp(empno);
SQL> select * from emp where empno=7369;

Execution Plan
----------------------------------------------------------
Plan hash value: 612339345

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP                |     1 |    32 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_NOSEGMENT_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7369)

SQL>

从以上可以看出虚拟索引也是不准的，ｏｒａｃｌｅ有可能使用虚拟索引也有可能不使用虚拟索引。

内容总结

以上是互联网集市为您收集整理的不可见索引和虚拟索引。全部内容，希望文章能够帮你解决不可见索引和虚拟索引。所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/530199.html

来源：【匿名】

【上一篇】使用COALESCE+SUM+CASE 【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【不可见索引和虚拟索引。】教程文章相关的互联网学习教程文章

mysql 索引与优化like查询

详见：http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt1011. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。 2. like keyword% 索引有效。 3. like %keyword% 索引失效，也无法使用反向索引。 ==================================================================== 1. 使用下面的函数来进行模糊查询，如果出现的位...

MySQL索引背后的之使用策略及优化(高性能索引策略)【图】

本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。示例数据库　　为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册)：　　图12　　MySQL官方文档中关于此数据库的页面...

MySQL 8.0 隐藏索引【图】

隐式索引最明显的一个作用类似索引回收站。例如数据库长时间运行后，会积累很多索引，做数据库优化时，想清理掉没什么用的多余的索引，但可能删除某个索引后，数据库性能下降了，发现这个索引是有用的，就要重新建立。对于较大的表来说，删除、重建索引的成本是很高的，如果在清理索引时能先放入回收站，确认没影响后再彻底删除，有影响的话就恢复回来，这样就方便多了，把索引设置为隐藏/显示就可以实现这个需求。索引的隐藏不...

MySQL之left join表查询中发生字符集转换导致表索引失效【代码】【图】

一、出现的场景研发leader突然给了1条sql说这条sql在测试环境库db-stage执行非常的慢，放到线上db-read从库上执行非常的快。而且线上库从库的表的数据远多于测试环境库的表数据。让我分析下是什么问题？？二、sql内容如下 select odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id left join db_order.t_orders o on oid.order_id=o.order_id w...

MySQL索引背后的数据结构及算法原理

摘要本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。文章主要内容分为三个部分。第一部分主要从数据结构及算法理论层面讨论M...

MYSQL索引

mysql的4种常用索引类型：唯一索引，主键索引，全文索引，以及普通索引。普通索引（INDEX）：普通索引为索引的核心部分，无限制，目的就是加快数据的访问速度，而主键索引，唯一索引，全文索引则是在普通索引延伸出来的有规则限制的索引，方便用于某些特定的业务场景。主键索引（PRIMARY）：可以理解主键（常用的那个id主键），每一张表都必须有一个主键，且不允许为空。唯一索引（UNIQUE）：内容具备唯一性，像手机号，身份证这种...

mysql测试索引在表中的作用【代码】

实验内容单表中的索引使用1．建表 create table t_book(id int,a1 varchar(200),a2 varchar(200),a3 varchar(200),a4 varchar(200),a5 varchar(200),a6 varchar(200),a7 varchar(200),a8 varchar(200),a9 varchar(200)) 2．写存储过程向表中插入较大数据//或其他方式使数据增大（1）绑定变量： PREPARE stmt1 FROM‘insert into t_book(id,a1,a2,a3,a4,a5,a6,a7,a8,a9)values(?,?,?,?,?,?,?,?,?,?)‘; （2）存储过程创建：delimite...

mysql中索引的使用【图】

索引是加速查询的主要手段，特别对于涉及多个表的查询更是如此。本节中，将介绍索引的作用、特点，以及创建和删除索引的语法。13.4.1 使用索引优化查询索引是快速定位数据的技术，首先通过一个示例来了解其含义及作用，详细的介绍请参考第14章。1．索引示例假设对于10.3节所建的表，各个表上都没有索引，数据的排列也没有规律，如表13.3所示。表13.3 没有索引的students表...

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) ：ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) ：ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) ：ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `col...

MySql分区后创建索引加速单表查询和连表查询【代码】【图】

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。本文链接：https://blog.csdn.net/konkon2012/article/details/96482548为了加快查询，我们通常根据Where条件创建索引！那么分区后再创建索引，那就应该更快了！我们依据订单表和订单商品表举例，先创建表结构：CREATE TABLE `zstb_orders` ( `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `org_id` INT(10) UNSIGNED NOT NU...

mySql---剖析InnoDB索引原理【图】

摘要：本篇为参考别人的文章（http://blog.csdn.net/voidccc/article/details/40077329）1 各种树形结构本来不打算从二叉搜索树开始，因为网上已经有太多相关文章，但是考虑到清晰的图示对理解问题有很大帮助，也为了保证文章完整性，最后还是加上了这部分。先看看几种树形结构： 1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结...

mysql索引整理

一.索引：1.创建一般索引 create index index_name on table_name(columnname[...]); 2.分析sql执行语句在select 之前加explain eg:explain select * from table_name;3.mysql从5.5.5之后默认采用innerdb为默认的存储引擎,该引擎支持的两种常见索引：b+树索引和hash索引，hash索引的效率高于b+树，但hash索引只支持=类型。b+树可以支持范围的eg:>< ，b+树一般三层即可表示，即访问3次io可找到数值。二.常见丢失索引的情况1.左前缀...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / 不可见索引和虚拟索引。

不可见索引和虚拟索引。

内容导读

内容图文

不可见索引和虚拟列上的索引。

内容总结

内容备注

内容手机端

【不可见索引和虚拟索引。】教程文章相关的互联网学习教程文章

mysql 索引与优化like查询

MySQL索引背后的之使用策略及优化(高性能索引策略)【图】

MySQL 8.0 隐藏索引【图】

MySQL之left join表查询中发生字符集转换导致表索引失效【代码】【图】

MySQL索引背后的数据结构及算法原理

MYSQL索引

mysql测试索引在表中的作用【代码】

mysql中索引的使用【图】

Mysql如何为表字段添加索引？？？

MySql分区后创建索引加速单表查询和连表查询【代码】【图】

mySql---剖析InnoDB索引原理【图】

mysql索引整理

MySql 三大知识点，索引、锁、事务，原理分析【图】

MySQL索引

MYSQL索引分析和优化设计方案

虚拟 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程