oracle索引-马育民老师

# 概述
**索引** 在表中的作用，相当于 **目录** 在书中的作用

在oracle数据库默认配置情况下，一般表中数据超过300万条以上，查询速度才会变慢，这是使用索引才有意义

### 优点
1. 索引是与表相关的一个可选结构 
一个表中可以存在索引，也可以不存在索引

3. 在逻辑上和物理上都独立于表的数据 
索引与表 **完全独立**，表里的内容是我们真正感兴趣的内容，索引和数据可以存放在不同的表空间中，或者 可以存放在不同的磁盘。

2. 提高 SQL 语句的 **查询速度**
快速定位我们需要查找的表的内容（物理位置），提高sql语句的执行性能。
取数据从磁盘上取到数据缓冲区中，再交给用户。磁盘IO非常不利于表的查找速度（效率的提高）。

4. Oracle 自动维护索引 
当对一个建立索引的表的数据进行增删改的操作时，oracle会自动维护索引，使得其仍然能够更好的工作。

5. 索引被删除或破坏后，不会对表中数据造成影响，影响的只是查询速度

6. 删除表时，该表的索引也会被删除

### 缺点
1. 当表中的数据量越大，创建索引、维护索引时，耗费的时间 就越长

2. 占用物理空间

3. 对表进行 **增删改** 时，数据发送变化，索引也要动态维护，会降低**增删改** 的速度

# 什么时候使用索引
- 列中数据值分布范围很广
性别一般就是男、女、未知，所以不适用

- 列经常在where中作为 **查询条件**，或者 **多表关联条件**

- 表 **频繁被访问** 而且 **数据量很大**，但访问的数据一般占总量的 2% ~ 4%

# 索引分类

1. **普通索引**
2. **唯一索引**，UNIQUE
2. **组合索引**
3. 位图索引，Bitmap
3. 反向键索引，REVERSE 
5. 函数索引

### 普通索引
语法：
```
create index index_name on table_name (column_name);
```

### 唯一索引
1. 唯一索引确保在定义索引的列中没有重复值 
2. 创建主键（primary key）后，自动创建唯一索引

语法：
```
create unique index index_name on table_name (column_name);
```
具体列值： 索引相关列上的值 **必须唯一**，但可以 **不限制NULL**值

### 组合索引（联合索引、复合索引）： 
1. 组合索引是在表的多个列上创建的索引 
2. 索引中列的顺序是任意的 
3. 如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度
4. 第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列

语法：
```
create index index_name on table_name (column_name1，column_name2); 
```
具体列值：该表中的元组由两列共同确定一行，例如班级号 学号 唯一确定一个学生。

# 索引建立原则
1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

3. 小表不要简历索引

4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引

5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引

6. 经常进行连接查询的列应该创建索引

7. 使用create index时要将最常查询的列放在最前面

8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

# 索引操作
### 创建
使用 CREATE INDEX 语句创建索引

### 重命名索引
```
alter index index_name1 rename to index_name2;
```

# 索引原理
oracle中索引分为 **B树索引**（平衡树索引，最常用）和 **位图索引**（多用于 **数据仓库**）。

假设有下面索引值：

|索引值   |
| ------------ |
|李雷   |
|韩梅梅   |
|lucy   |
|lili   |
|蔡徐坤   |
|鹿晗   |
|周杰伦 |

B树（balance tree）的结构如下：
[![](https://www.malaoshi.top/upload/0/0/1EF47XrSqNNT.png)](https://www.malaoshi.top/upload/0/0/1EF47XrSqNNT.png)

每个索引记录对应行数据的 **rowid**

**注意：**
当增删改记录后，上面树的结果会改变，就需要重新 **整理索引**

### 运行机制
1. 若没有索引，搜索某个记录时（例如查找```name='wish'```）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍

2. 若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方

3. 创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

# 索引的变动
对表进行 **增删改** 操作后，表中的数据可能不存在或数据发生变化，但其索引仍然存在，极大的影响了查询速度，降低了索引的利用率。

我们可以通过 查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。 
对索引碎片的查询，在该语句前后都要使用分析索引语句，已使得索引利用率发生改变。

分析：
```
alter index index_name validate structure;
```
查询碎片：
```
select name,pct_used from index_stats where name='index_name'; 
```

# 整理索引
当表中数据发生变化时，我们可以通过两种方式来对索引进行更新，：

### 1. 合并索引
```
alter index index_sno coalesce;
```

### 1. 通过删除该索引，再建立新的索引来提高索引的利用率。
```
drop index index_name 
create index index_name on 表名（列名） tablespace tname；
```
### 2. 通过重建索引来提高索引利用率
```
alter index index_name rebuild REBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS]; 
```
##### 解释：
 - ONLINE：使得在重建索引过程中，用户可用对原来的索引进行修改，也就是其他的用户同时可以对表进行增删改操作；
 - NOLOGGING：表示在重建过程中产生最少的重做条目redo Entry，加快重建的速度；
 - COMPUTE STATISTICS：表示在重建过程中就生成了oracle优化器所需的统计信息，避免了索引重建之后再进行analyze或dbms_stats来收集统计信息。

感谢：
https://blog.csdn.net/bibibrave/article/details/80876967
https://www.cnblogs.com/liangyihui/p/5886619.html

原文出处：http://malaoshi.top/show_1EF3vKKLis5A.html