Oracle索引的建立及优缺点

缺乏、安全感 2023-10-08 14:01 31阅读 0赞

在看公司建表语句时发现了这样一段代码  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZCR552A55m-5LiH5bm06Jaq5Yqq5Yqb55qE5bCP6LW1_size_17_color_FFFFFF_t_70_g_se_x_16]  
本着学习的态度面向百度：：：：：  
原来这是Oracle的索引

## Oracle的索引说明 ##

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响

5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

6）oracle创建主键时会自动在该列上创建索引

## 数据库索引是为了提高查询速度的一种数据结构。 ##

1.  索引的创建语句。

CREATE INDEX 索引名 ON 表名(列名);
    --下面是表空间及参数
    tablespace GFMIS
     pctfree 10 --块保留10%的空间留给更新该块数据使用
      initrans 1 --初始化事务槽的个数
      maxtrans 255 --最大事务槽的个数
      storage --存储参数
      ( 
        initial 64k --区段(extent)一次扩展64k
        minextents 1 --最小区段数
        maxextents unlimited --最大区段无限制 
      );

1.  除了单列索引，还可以创建包含多个列的复合索引。

CREATE INDEX 索引名 ON 表名(列名1, 列名2, 列名3, ...);

1.  删除索引。

DROP INDEX 索引名;

1.  查看某个表中的所有索引。

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = '表名'

1.  还可以查看某个表中建立了索引的所有列。

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '表名'

#### 知道了怎么用，下一步就该知道为什么这样用了 ####

我们都是知道，建立索引是为了提高查询效率，那么，应该怎么建立索引呐？

## 索引的建立原则 ##

1.  索引应该建立在WHERE子句中经常使用的列上。如果某个大表经常使用某个字段进行查询，并且检索的啊行数小于总表行数的5%，则应该考虑在该列上建立索引。
2.  对于两个表连接的字段，应该建立索引。
3.  如果经常在某表的一个字段上进行Order By的话，则也应该在这个列上建立索引。
4.  不应该在小表上建立索引。

另外附上索引的优缺点（面试会经常问到）

## 索引的优缺点 ##

索引主要是为了提高数据的查询速度，这就是索引的优点。但是当进行增删改的时候，会更新索引。因此索引越多，增删改的速度就会越慢，因为有一个维护索引的过程。创建索引之前需要权衡该字段是否经常发生增删改操作，否则可能会带来负优化的问题。

### 索引的优点 ###

1.  很大地提高了数据的检索速度。
2.  创建唯一索引能保证数据库表中每一行数据的唯一性（唯一性约束）。
3.  提高表与表之间的连接速度。

### 索引的缺点 ###

1.  索引需要占用物理空间。
2.  当对表中的数据进行增加、删除和修改的时候，索引也要动态地进行维护，降低数据的维护速度。

最后补充点额外知识：

## 关于查询优化器 ##

当Oracle拿到SQL语句的时候，会使用查询优化器去分析该语句，并根据分析结果生成查询执行计划。也就是说，数据库是执行的查询计划，而不是SQL语句。

查询优化器有RBO（Rule-Based-Optimizer，基于规则的查询优化器）和CBO（Cost-Based-Optimizer，基于成本的查询优化器），其中基于规则的查询优化器在10g的版本中彻底消失了。原因是因为RBO最后的查询都是全表扫描，而CBO会根据统计信息选择最优的查询方案。

因此，CBO一旦发现有索引的存在，并且这个索引能帮助提高查询速度，就是使用通过索引进行查询的方法。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZCR552A55m-5LiH5bm06Jaq5Yqq5Yqb55qE5bCP6LW1_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/1419eb339f164884980b9851b37c32eb.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5ZCR552A55m-5LiH5bm06Jaq5Yqq5Yqb55qE5bCP6LW1,size_17,color_FFFFFF,t_70,g_se,x_16