Oracle 索引详解（index）_oracle sql如何使用index

1 概述

1. 索引是什么？  
   (1) 一种供服务器在表中快速查找一行的 '数据库结构'
   (2) '索引之于表'，相当于 '目录之于书'。
   (3) rowid 就相当于 '页码'
   
2. 索引的优点
   (1) 提高数据 '检索速度'
   (2) 提高表与表之间的 '连接速度'
   (3) 在使用 order by、group by 时，可以减少排序和分组的时间
   (4) 保证数据的 '唯一性'（unique、主键）

3. 索引的缺点
   (1) 占用 '存储空间'，索引 和 表 一样，都需要占用表空间
   (2) 进行 DML 操作时，索引自动维护，从而 '降低 DML 操作的速度'
   (3) 创建索引和维护索引要 '耗费时间'，这种时间随着数据量的增加而增加
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2 索引管理

create table scott.student_info (  
  sno   number(3) constraint pk_student_info_sno primary key,
  name  varchar2(30),
  sex   varchar2(2),
  age   number(3)
);
1
2
3
4
5
6

2.1 创建索引

create [bitmap] index [schema.]索引名
on [schema.]表名 (列名1, ..., 列名N);
1
2

示例1：创建一般索引（B-Tree 索引，默认）

create index scott.idx_si_name on scott.student_info(name);
create index scott.idx_si_name_age on scott.student_info(name, sex);
1
2

示例2：创建位图索引（bitmap 索引）

create bitmap index scott.bidx_si_sex on scott.student_info(sex);
1

2.2 删除索引

drop index scott.idx_si_name_age;
1

2.3 修改索引

-- 修改索引名称 idx_si_sname -> idx_si_sname_new
alter index scott.idx_si_sname rename to idx_si_sname_new;

-- 修改索引为无效
alter index scott.idx_si_sname_new unusable;

-- 重建索引
alter index scott.idx_si_sname_new rebuild online;
1
2
3
4
5
6
7
8

2.4 查询索引

-- 查询权限范围由大至小：dba_* > all_* > user_*
select * from dba_indexes;
select * from all_indexes;
select * from user_indexes;

-- 索引查询
select t.*
  from all_indexes t
 where t.table_owner = 'SCOTT'
   and t.table_name = 'STUDENT_INFO';

-- 索引统计信息
select t.owner,
       t.index_name,
       t.blevel,
       t.leaf_blocks,
       t.num_rows
  from all_ind_statistics t
 where t.table_owner = 'SCOTT'
   and t.table_name = 'STUDENT_INFO';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3 索引类型

3.1 B-Tree 平衡树索引

1. B-Tree 索引
   (1) B 代表 "平衡（balanced）" 是一种树结构 -- 不是 "二叉树" 哦   
   (2) Oracle '默认' 的索引类型

2. 使用场景   
   (1) 列的数据中，'不同值的个数很多'   
   (2) 如：主键列（值不相同、值个数多）      
1
2
3
4
5
6
7

B-Tree 索引 示意图：

3.2 bitmap 位图索引

1. bitmap 索引

2. 使用场景   
   (1) 列的数据中，'不同值的个数很少'   
   (2) 如：性别（一般只有三种取值：男、女、未知）

3. 内部逻辑
   (1) 向量相加：全 1 为 1，有 0 为 0
1
2
3
4
5
6
7
8

3.3 反向键索引

-- 索引列 逆序排列 优化  desc
create index 索引名 on 表名(列名) reverse;

create index scott.idx_si_sno_desc on scott.student_info(sno) reverse;
1
2
3
4

3.4 基于函数索引

-- 索引列 使用函数 优化
create index 索引名 on 表名(函数(列名))

create index scott.idx_si_sname_upper on scott.student_info(sname);
1
2
3
4

4 扩展

4.1 走不走索引的情况

1. '独立的列'：不在索引列做任何操作（包括但不限于: 计算、函数、类型转换）
   正例:  -- id 为 number 类型
   where id = 2
   where id = 1 + 1
     
   反例:
   where id + 1 = 3
   
2. like 查询：'最左原则'
   正例:
   where name like '张三%'  
    
   反例:
   where name like '%张三'
   where name like '%张三%'
   
3. 复合索引：如 （A，B，C） '必须含有 第一个索引 A' 
   正例: -- 原则：A 最常用，B 次之， C 最末   
   where A = 'A'
   
   反例:
   where B = 'B'
     and C = 'c'
     
4. or 运算：所有参与运算的字段 '都存在索引'，才会用到索引

5. <>、is null、is not null：都不走索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

提示：最直观的 => 看 执行计划