oracle索引

作者：马育民 • • 阅读：10203

概述

索引 在表中的作用，相当于 目录 在书中的作用

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

优点

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

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

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

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

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

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

缺点

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

  2. 占用物理空间

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

什么时候使用索引

  • 列中数据值分布范围很广
    性别一般就是男、女、未知，所以不适用

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

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

索引分类

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

普通索引

语法：

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）的结构如下：

每个索引记录对应行数据的 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

原文出处：/show_1EF3vKKLis5A.html

吉ICP备15003043号