二十五、索引：加快检索速度的数据库对象

朱雀 2022-04-12 07:38 221阅读 0赞

#                                     索引：加快检索速度的数据库对象 #

数据库对象：表、视图、序列、索引、同义词、约束、存储过程、存储函数、包和包体、触发器

### 1、索引简介 ###

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
    
    　　2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
    
    　　3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
    
    　　4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
    
    　　5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
    
    　　6）oracle创建主键时会自动在该列上创建索引

### 2、索引原理 ###

1）若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
    
    2）若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
    
    3）创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

##### 2.1、**告诉你，为什么使用了索引会变快？** #####

根据索引的列，根据rowID进行升序排列，查询的时候，根据rowID去查询。
    使用索引查一条数据，需要先访问索引的数据，然后再访问表的数据，两次读操作

##### 2.2、不使用索引怎么查询？ #####

数据库会进行全表查询

##### 2.3、**告诉你，为什么使用了索引会变慢？** #####

查询结果占到表数据的50%以上，使用索引必然慢
    一次读操作可以读多条数据，索引的结构又比表复杂
    可能查询结果占到表数据的20%以上，使用索引就不如全表扫描了

### 3、索引使用（创建、修改、删除、查看） ###

#### 3.1、创建索引语法 ####

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
    ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
    [ASC|DESC],…] | [express])
    [TABLESPACE tablespace_name]
    [PCTFREE n1]                                 --指定索引在数据块中空闲空间
    [STORAGE (INITIAL n2)]
    [NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
    [NOLINE]
    [NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

#### 3.2、修改索引 ####

##### 3.2.1、重命名索引 #####

alter index index_sno rename to bitmap_index;

##### 3.2.2、合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低） #####

alter index index_sno coalesce;

##### 3.2.3、重建索引 #####

方式一：删除原来的索引，重新建立索引
    
    　　方式二：alter index index_sno rebuild;

##### 3.2.4、删除索引 #####

drop index index_sno;

##### 3.2.5、查看索引 #####

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
    
     -- eg:    
    		create index index_sno on student('name');
    		select * from all_indexes where table_name='student';

### 4、注意事项 ###

#### 4.1、通配符在搜索词首出现时，oracle不能使用索引 ####

--我们在name上创建索引；
    
    create index index_name on student('name');
    
    --下面的方式oracle不适用name索引
    
    select * from student where name like '%wish%';
    
    --如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
    
    select * from student where name like 'wish%';

#### 4.2、不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描） ####

select * from student where not (score=100);
    
    select * from student where score <> 100;
    
    --替换为
    
    select * from student where score>100 or score <100

#### 4.3、索引上使用空值比较将停止使用索引 ####

select * from student where score is not null;

### 5、索引分类 ###

#### 5.1、B树索引（B-TREE INDEX） ####

B树索引结构图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]

B树索引结构主要由三部分组成：根节点、分支节点、叶子节点。
    对于oracle而言，索引的层级号采用倒序的方式，既对于层级数为N的索引，根节点的层级号为N，其下一层的分支节点为N-1，类推。

#### 5.2、 位图索引（Bit MAP INDEX） ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 1]

https://www.cnblogs.com/yumiko/p/5908302.html

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]: /images/20220412/44884fd0e9e94f3c8b3cecae5f6993ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 1]: /images/20220412/bf68e3b735e241f79c1f1cdc59901ddd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220412/38790f733e1c4c838bbbafa5ef60da98.png