Index of Oracle Database

访问索引列的数据一般需要 3 次 IO，Root-Branch-Leaf。
访问索引列外的数据需要 4 次 IO，Root-Branch-Leaf - 回表 (TABLE ACCESS BY INDEX ROWID)。
索引对 DML 操作的影响，INSERT > DELETE > UPDATE

[创建索引会引发排序及全表索，排序会非常消耗 CPU 性能，全表锁会阻止 DML 操作]{.red}

索引的三大特点

  • 索引树的高度一般比较低。
  • 索引由索引列存储的值及 ROWID 组成。
  • 索引本身是有序的。

分区表索引

  • 分为全局索引和分区索引。
  • 分区索引，要有使用分区字段条件的场景，否则建议使用全局索引。

count (*) 优化

--NULL和NOT NULL不等价，排除NULL值，执行计划由TABLE ACCESS FULL变为INDEX FAST FULL SCAN
select count(*) from t where id is not null;

--或将列设置NOT NULL，执行计划为INDEX FAST FULL SCAN
alter table t modify id not null;
select count(*) from t;

MAX/MIN 优化

--NULL和NOT NULL等价，执行计划为INDEX FULL SCAN (MIN/MAX)，MIN一定位于左边的叶子块，MAX一定位于最右边的叶子块
select max(id) from t;

--Oracle无法同时在左边和最右边读取，执行计划为TABLE ACCESS FULL
select max(id),min(id) from t;

--分开查询，执行计划为INDEX FULL SCAN (MIN/MAX)
select min,max from (select mix(id) min from t) a, (select max(id) max from t) b;

回表优化

--查看索引列以外的数据，会出现回表，执行计划TABLE ACCESS BY INDEX ROWID
select * from t where id=10;

索引全扫与索引快速全扫

  • INDEX FAST FULL SCAN 一次读取多个索引块，INDEX FULL SCAN 一次只读取一个索引块。
  • INDEX FAST FULL SCAN 一次读取多个索引块不容易保持有序，而 INDEX FULL SCAN 一次读取一个索引块可以保持有序，因此在有排序的场合，INDEX FULL SCAN 的顺序读可以消除排序，INDEX FAST FULL SCAN 虽然减少了逻辑读，但是无法消除排序。

UNION 合并优化

UNION 合并会去除重复记录，UNION ALL 则是简单的合并，在 UNION 中索引一般无法消除排序，最常见的优化是将 UNION 改为 UNION ALL。

联合索引

联合索引不宜超过 3 个字段，否则不仅影响定位数据，更严重影响更新性能。可以用类似如下方式测试联合字段返回的记录是否比单个字段记录少得多。

select count(*) from t where a=1;
select count(*) from t where b=2;
select count(*) from t where a=1 and b=2;

在两列都是等值查询的情况下，组合索引的列无论哪一列在前，性能都一样；
在一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样索引才最高效；
单列的查询列和联合索引的前置列一样，那单列可以不建索引，直接利用联合索引来进行检索数据。

位图索引

位图索引的适用场景要满足两个条件：一，位图索引列大量重复；二，该表极少更新。

函数索引

对索引列进行运算会导致索引无法使用，在无法避免列运算时，可以创建函数索引加以优化。

分析是否需要重建索引

分析索引的数据块是否有坏块，以及根据分析得到的数据（存放在 index_stats）來判断索引是否需要重新建立。

SQL> analyze index AATD_IDX1 validate structure;

validate structure 有两种模式：
online ：（默认）会对表加一个 4 级別的锁（表共享），对 run 系統可能造成一定的影响。
offline ：没有表 lock 的影响，但当以 online 模式分析时， 在视图 index_stats 没有统计信息。

从 9i 开始，Oracle 以建议使用 dbms_stats package 代替 analyze 了。

SQL> exec dbms_stats.gather_table_stats(‘用户名’,‘表名’,cascade=>true);

下面视图只支持：analyze index 命令

SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
HEIGHT DEL_LF_ROWS/LF_ROWS
---------- -------------------
    1                   0
Executed in 0.381 seconds

当查询出来的 height>=4，或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合，该索引考虑重建。

重建索引

  1. 删除创建索引

    drop index idx_id_t1;
    create index idx_id_t1 on t1(id);

    说明：此方式耗时间，无法在 24*7 环境中实现，不建议使用。

  2. 重建索引

    alter index idx_id_t1 rebuild;
    alter index idx_id_t1 rebuild online;

    说明：此方式比较快，可以在 24*7 环境中实现，建议使用此方式。

    rebuild 与 rebuild online 的区别

    1. rebuild 根据统计信息的 cost 采用 index fast full scan 或者 table full scan 方式读取原索引中的数据来构建一个新的索引，有排序的操作；rebuild online 执行表扫描获取数据，有排序的操作。
    2. rebuild 会阻塞 dml 操作，rebuild online 不会阻塞 dml 操作。rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表，所有 rebuild online 时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后 drop 掉旧的索引，rebuild online 就完成了。

注意点：

  1. 执行 rebuild 操作时，需要检查表空间是否足够
  2. 虽然说 rebuild online 操作允许 dml 操作，但是还是建议在业务不繁忙时间段进行 rebuild 操作会产生大量 redo log

重建分区索引

  1. 重建分区表上的分区索引:

    alter index indexname rebuild partition PARTITION_NAME tablespace tablespacename;

    select 'alter index '|| index_name || ' rebuild partition '|| partition_name || ' online;' from user_ind_partitions where index_name ='索引';

  2. 子分区索引重建：

    alter index indexname rebuild subpartition PARTITION_NAME tablespace tablespacename;

    select 'alter index '|| index_name || ' rebuild subpartition '|| subpartition_name || ' online;' from user_ind_subpartitions;

    注：这里的 PARTITION_NAME 指 USER_IND_PARTITIONS 中的 PARTITION_NAME (索引分区中的索引分区名)

  3. 查询分区表索引所在的分区

    SELECT PI.TABLE_NAME,
    IP.INDEX_NAME,
    IP.PARTITION_NAME,
    IP.STATUS,
    IP.GLOBAL_STATS
    FROM USER_PART_INDEXES PI, USER_IND_PARTITIONS IP
    WHERE PI.INDEX_NAME = IP.INDEX_NAME AND PI.TABLE_NAME = '表名';