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

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

我们创建了索引，为什么这条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的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)