探索索引的奥秘 - 索引的属性

索引是一种奇特的对象，他就像一把双刃剑，用好了可以提高性能，用不好就可能会影响性能，但如何才能用好索引？

可能我们日常工作中，同事、朋友，甚至我自己会问这种问题，

我们创建了索引，为什么这条SQL未用这索引？

创建的索引越多，应用是不是就会越快？

只要SQL运行慢，一定是索引有问题？

应用是否用索引，是谁来决定的？

是否有了索引，应用就一定不会有性能问题？

...

其实这些问题，都蕴含着丰富的信息，就像学习Oracle一样，之所以说Oracle入门不难(例如写一些SQL语句)，但要精通掌握就很难，原因就在于Oracle体系结构的庞大(从Oracle发布的官方文档数量，就能体现出来)，而且还是在不断发展着，这次OOW大会，Larry就提出了Oracle 18c这个版本，会是一款自治(Autonomous)数据库，啥玩意儿啊这是，11g还没搞明白，这就18c了啊？

非常抱歉，有些扯远了，其实我要表达的，就是为了用好数据库索引，我们就需要首先了解索引，了解索引的一些基本知识，以及一些原理，做到知其然，更要知其所以然，这样才能更好地驾驭索引。

注：实验主要基于Oracle，一些知识点，其他数据库，可能通用。

为了这一个目的，我会总结一下，关注一些索引易混淆的知识，以及一些案例，做到查漏补缺，整理下自己的知识体系。我也是在不断的学习中，理解上可能会有偏差，可能会有出入，也欢迎朋友们及时指出来，共同学习，共同进步。

这篇文章，我们关注的是，索引的属性，有什么属性？作用是什么？什么场景使用？

我们先看下官方文档，对于索引属性的描述，

Usability
Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.

索引可以设置为usable(默认属性)或者unusable。unusable的索引做DML操作的时候，不会被维护，而且会被优化器忽略。unusable索引可以提升批量导入性能，且不会消耗空间。

Visibility
Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.

索引可以设置为visible(默认属性)或者invisible。invisible的索引做DDL操作的时候，会被维护，但默认不会被优化器使用。在删除一个索引之前，或者临时使用一个索引时，用这种invisible的索引，特别有用，因为他不会影响应用性能。

创建测试表、数据和索引，

SQL> create table test (id number, name varchar2(1));
Table created.

SQL> begin
       for i in 1 .. 10000 loop
         insert into test values(i, dbms_random.string('a',1));
       end loop;
       commit;
     end;
/
PL/SQL procedure successfully completed.

SQL> create index idx_test_01 on test(id);
Index created.

实验一：usable和unusable


可以看出，上面提及的可用性(usable)和可见性(visible)，是两个字段，值为valid和visible，

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 VALID      VISIBLE

表占用空间196608，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='TEST';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
TEST        TABLE        196608

索引占用空间196608，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_01 INDEX        196608

此时执行select * from test where id = 1，应该可以用索引，

将索引设置为unusable，

SQL> alter index idx_test_01 unusable;
Index altered.

此时索引status就变为了unusable，

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 UNUSABLE   VISIBLE

而且之前的索引段空间，被删除了，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
no rows selected

此时执行select * from test where id = 1，用的就是TABLE ACCESS FULL，

此时向表中插入数据，

SQL> begin
       for i in 10001 .. 20000 loop
         insert into test values(i, dbms_random.string('a',1));
       end loop;
       commit;
     end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from test;
  COUNT(*)
----------
     20000

此时索引段，还是删除状态，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
no rows selected

我们需要恢复索引状态，方法一是可以删除重建，方法二是使用rebuild，

SQL> alter index idx_test_01 rebuild;
Index altered.

现在索引的状态，status=valid，

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 VALID      VISIBLE

此时索引段，已经重建，表和索引的空间，符合现在20000条数据的容量，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='TEST';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
TEST        TABLE        327680

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_01 INDEX        393216

以上实验，可以说明，

1. 索引设置为unusable，此时会删除索引段。

2. 索引处于unusable期间，对表数据做DML操作，此时不维护索引。

3. 索引处于unusable期间，优化器会忽略此索引。

4. 索引处于unusable期间，由于不需要维护索引，因此可以提升批量导入性能。

5. 索引unusable变为usable，有两种方法，一种是删除-重建索引，一种是使用alter index ... rebuild，两种方法，都相当于重新构建了索引。

实验二：visible和invisible


首先我们恢复，测试表包含10000条数据的状态，

SQL> select count(*) from test;
  COUNT(*)
----------
     10000

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 VALID      VISIBLE

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='TEST';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
TEST        TABLE        196608

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_01 INDEX        196608

设置索引状态为invisible，

SQL> alter index idx_test_01 invisible;
Index altered.

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 VALID      INVISIBLE

执行select * from test where id = 1;，从执行计划看，未用索引，

但和之前unusable，不同的是，索引段未被删除，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_01 INDEX        196608

此时向表中插入数据，

SQL> begin
       for i in 10001 .. 20000 loop
         insert into test values(i, dbms_random.string('a',1));
       end loop;
       commit;
     end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from test;
  COUNT(*)
----------
     20000

可以看出，表和索引空间，均和数据量吻合，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='TEST';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
TEST        TABLE        327680

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_01 INDEX        393216

Oracle提供了一个参数，optimizer_use_invisible_indexes，可以控制优化器是否使用属性状态为invisible的这些索引，默认值是false，

SQL> show parameter optimizer_use_invisible_indexes
NAME                     TYPE    VALUE
------------------------------------ ---------- ------------------------------
optimizer_use_invisible_indexes      boolean    FALSE

可以设置session级别optimizer_use_invisible_indexes值，

SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.

再次执行select * from test where id = 1;，此时执行计划用到了索引，

让索引设置为visible，直接使用alter index ... visible，

SQL> alter index idx_test_01 visible;
Index altered.

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 VALID      VISIBLE

以上实验，可以说明，

1. 索引设置为invisible，不会删除索引段。

2. 索引处于invisible期间，对表数据做DML操作，此时会维护索引。

3. 索引处于invisible期间，优化器会忽略此索引，但可以使用optimizer_use_invisible_indexes控制。

4. 索引invisible变为visible，直接使用alter index ... visible。

实验三：同时设置unusable和invisible


首先我们恢复，测试表包含10000条数据的状态，

SQL> select count(*) from test;
  COUNT(*)
----------
     10000

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 VALID      VISIBLE

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='TEST';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
TEST        TABLE        196608

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_01 INDEX        196608

同时设置unusable和invisible，

SQL> alter index idx_test_01 unusable;
Index altered.

SQL> alter index idx_test_01 invisible;
Index altered.

SQL> select table_name, index_name, status, visibility from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY
---------- ----------- ---------- ----------
TEST       IDX_TEST_01 UNUSABLE   INVISIBLE

执行select * from test where id = 1;，从执行计划看，未用索引，但不能明确，这是因为unusable还是invisible，

可以看出，索引段已被删除，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_01';
no rows selected

以上实验，可以说明，unusable比invisible优先级要高，同时设置，起作用的是unusable。

实验四：disable和enable


索引还有一种状态disable和enable，但并不是通用的，例如对之前创建的索引，执行disable会报错，

SQL> alter index idx_test_01 disable;
alter index idx_test_01 disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

这是因为disable和enable只对函数索引有效，创建函数索引，

SQL> create index idx_test_02 on test(upper(name));

Index created.

设置函数索引disable，

SQL> alter index idx_test_02 disable;
Index altered.

函数索引段未被删除，

SQL> select segment_name, segment_type, bytes from user_segments where segment_name='IDX_TEST_02';
SEGMENT_NAM SEGMENT_TYP      BYTES
----------- ----------- ----------
IDX_TEST_02 INDEX        196608

user_indexes视图FUNCIDX_STATUS字段，表示的是函数索引的状态，有三个值，

NULL - Index is not a function-based index
ENABLED - Function-based index is enabled
DISABLED - Function-based index is disabled

可以看出，此时函数索引，FUNCIDX_STATUS值为DISABLE，

SQL> select table_name, index_name, status, visibility, funcidx_status from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY FUNCIDX_STATUS
---------- ----------- ---------- ---------- ------------------------
TEST       IDX_TEST_01 VALID      VISIBLE
TEST       IDX_TEST_02 VALID      VISIBLE    DISABLED

此时执行select * from test where name = upper('a');，不会用索引，

向表中插入数据，就会报错，禁止插入数据，因为函数索引DISABLED了，数据DML操作会维护索引，索引不能维护，进而不让插数据，

SQL> begin
       for i in 10001 .. 20000 loop
         insert into test values(i, dbms_random.string('a',1));
       end loop;
       commit;
     end;
/
begin
*
ERROR at line 1:
ORA-30554: function-based index BISAL.IDX_TEST_02 is disabled
ORA-06512: at line 3

所有需要维护索引的操作，都会报这个错，

SQL> update test set name='b' where id=1;
update test set name='b' where id=1
*
ERROR at line 1:
ORA-30554: function-based index BISAL.IDX_TEST_02 is disabled

SQL> delete from test where id=1;
delete from test where id=1
*
ERROR at line 1:
ORA-30554: function-based index BISAL.IDX_TEST_02 is disabled

当然，根据上面的结论，只要不维护索引，就应该可以操作，

SQL> update test set id=1 where id=1;
1 row updated.

alter index ... enable，可以让函数索引enable，

SQL> alter index idx_test_02 enable;
Index altered.

SQL> select table_name, index_name, status, visibility, funcidx_status from user_indexes;
TABLE_NAME INDEX_NAME  STATUS      VISIBILITY FUNCIDX_STATUS
---------- ----------- ---------- ---------- ------------------------
TEST       IDX_TEST_02 VALID      VISIBLE    ENABLED

以上实验，可以说明，函数索引disable，则所有涉及，这个函数索引维护的操作，会被禁止，且执行计划，不会用这索引。

总结：

> 索引设置为unusable，会有以下特点，

    1. 索引设置为unusable，此时会删除索引段。

    2. 索引处于unusable期间，对表数据做DML操作，此时不维护索引。

    3. 索引处于unusable期间，优化器会忽略此索引。

    4. 索引处于unusable期间，由于不需要维护索引，因此可以提升批量导入性能。

    5. 索引unusable变为usable，有两种方法，一种是删除-重建索引，一种是使用

        alter index ... rebuild，两种方法，都相当于重新构建了索引。

> 索引设置为invisible，会有以下特点，

    1. 索引设置为invisible，不会删除索引段。

    2. 索引处于invisible期间，对表数据做DML操作，此时会维护索引。

    3. 索引处于invisible期间，优化器会忽略此索引。

    4. 索引invisible变为visible，直接使用alter index ... visible。
> unusable比invisible优先级要高，同时设置，起作用的是unusable。

> 只有函数索引可以设置disable和enable，涉及函数索引维护的操作，会被禁止，且执行计划，不会用这索引。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)

时间： 2024-11-01 16:35:38

探索索引的奥秘 - 索引的属性的相关文章

探索索引的奥秘 - 有索引就一定会用么？

上一篇文章<探索索引的奥秘 - 索引的属性>,我们了解了索引的属性,回顾一下, > 索引设置为unusable,会有以下特点,     1. 索引设置为unusable,此时会删除索引段.     2. 索引处于unusable期间,对表数据做DML操作,此时不维护索引.     3. 索引处于unusable期间,优化器会忽略此索引.     4. 索引处于unusable期间,由于不需要维护索引,因此可以提升批量导入性能.     5. 索引unusable变为usable,有两种方

oracle查找索引及表的其它属性

1.查找表的所有索引(包括索引名,类型,构成列): select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表 2.查找表的主键(包括名称,构成列): select cu.* from user_cons_columns cu, user_constr

MySQL中主键索引与聚焦索引之概念的学习教程_Mysql

主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录.一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL. 在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则: 采用一个没有业务用途的自增属性列作为主键: 主键字段值总是不更新,只有新增或者删除两种操作: 不选择会动态更新的类型,比如当前时间戳等. 这么做的好处有几点: 新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了:可以

采用 部分索引、表达式索引 提高搜索效率

标签 PostgreSQL , partial index , 部分索引 , 表达式索引 , 复合索引 , gist_btree混合索引 , 空间索引 背景 在现实场景中,经常有搜索的需求,例如搜索附近的店铺,搜索通常会有一些搜索的附带条件,例如搜索附近的美食类店铺,加油站等. 这里实际上涉及两类搜索需求,一类是距离,一类是属性. 如果将属性枚举掉,那么搜索时可以变成只按距离搜索.建立空间索引即可. 而如果属性无法枚举,那么需要同时搜索空间和属性,可以建立 "空间+属性" 的"

【索引】Oracle之不可见索引和虚拟索引的比对

[索引]Oracle之不可见索引和虚拟索引的比对    Oracle之不可见索引 :http://blog.itpub.net/26736162/viewspace-2124044/ Oracle之虚拟索引 :  http://blog.itpub.net/26736162/viewspace-2123687/   之前给大家分享过不可见索引和虚拟索引,今天给大家分享的是Oracle之不可见索引和虚拟索引的比对.   比较项目 不可见索引(Invisible Indexes) 虚拟索引(Virt

高性能的MySQL（5）索引策略-覆盖索引与索引排序

一.覆盖索引 索引是一种查找数据的高效方式,但是MySQL也可以使用索引来直接获取列的数据,这样就不再需要读取数据行.如果索引的叶子节点中已经包含要查询的数据,那么还有什么必要回表查询呢? 如果一个索引包含或者说覆盖所有需要查询的字段的值,我们就称之为"覆盖索引". 覆盖索引的好处: 1.索引条目通常远小于数据行大小,所以如果只需要读取索引,就极大的减少数据访问量.这对MyISAM尤其正确,因为MyISAM能压缩索引以变得更小. 2.因为索引是按照顺序存储的,所以对于I/O密集型的范围

mysql降序索引和减轻索引扫描

Descending indexing and loose index scan 降序索引和减轻索引扫描 Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls "better range" support. None o

主键和聚集索引和单列索引和多列索引

  1. 主键与聚集索引 首先澄清一个概念,主键并不等于聚集索引.(这不是废话么,如果是同一个东西,微软也不会叫两个不同的名字了) 一个表只能有一个聚集索引,数据在物理上是按照聚集索引的顺序来存放的. 主键分为聚集的主键和非聚集的主键.默认是聚集的主键.下面代码是SqlServer自动生成的CREATE TABLE 代码,注意设定主键那句话中的' CLUSTERED',即表示聚集的主键. /****** Object: Table [dbo].[User] Script Date: 03/28/

【索引】反向索引--条件 范围查询

---查看索引的类型 SQL> col table_name for a10 SQL> col index_name foa a10SQL> select table_name ,index_name ,index_type from user_indexes where table_name in ('T1','T2'); TABLE_NAME INDEX_NAME                     INDEX_TYPE ---------- ------------------