orcale数据库索引

环酱不爱敲代码

已于 2022-08-23 10:12:26 修改

阅读量348

点赞数

文章标签：数据库 sql

于 2022-08-19 15:28:02 首次发布

本文链接：https://blog.youkuaiyun.com/qq_41424476/article/details/126425560

版权

9 索引

索引是一种数据库对象，它是基于表中的一个列或多个列的值，提供对表进快速访问的一种访问机制。索引的目标是提高取数据的速度。

优点：

使用快速查询
唯一索引可用于实施值得唯一性
可加速连接多个表的查询效率
索引列关联查询快，一旦加索引通过主键索引查询效率高

缺点：

创建索引需要花时间（创建索引条目的时间）
需要额外的存储空间（索引段需要空间去维护）
每次修改数据，索引都需要更新
维护索引需要时间和资源，不应创建不会频繁使用的索引

为什么要用索引：

1)通过指针加速oracle服务器的查询速度

2)通过rowid快速定位数据的方法，减少磁盘I/O (磁盘的读写功能)

Rowid是oracle中唯一确定每张表不同记录的唯一身份证

用Rowid删除完全重复的数据语句（ASCII）：

Delete from rowd---表名
Where   rowid   not in (select max(rowid)
From    rowd
Group by id,name)-----分组后面跟的是需要去除重复的列，如id和name完全相同的去除

orcale自动为主键和唯一键创建唯一索引

9.1索引分类

9.1.1 B-Tree（B树）索引（默认索引类型）

a. 单列普通索引

ceate index 索引名 on 表（列 asc|desc）

b.唯一普通索引 -索引列，保证数据唯一不重复

create unique index 索引名 on 表(列 desc）
注意：表的字段加主键约束，该自动添加唯一索引，并且所有名字与主键约束名称一致

c.复合普通索引-索引

--案例4：删除该列的主键或者唯一约束,唯一索引会自动消除
alter table person drop constraint PERSON_ID_PK;
alter table person drop constraint PERSON_NAME_UK;
--案例5:删除该列的唯一索引,主键或者唯一约束会限制其报错
drop index PERSON_ID_PK;
drop index PERSON_NAME_UK;

列作用在多列上面，多列同时查询才有索引

-- 创建复合普通索引
create index ind1_person_name  on person(name,ID);
-- 全表扫描情况（数据量比较大的时候），效率低
-- 注意：第一特性-前缀性，复合索引列，必须有左右顺序查询，才能有索引效果
-- 因为违反复合索引前缀性原则
select * from person where name = 'xxx' and id=1
-- 使用索引列情况（数据量比较大的时候），效率高
select * from person where id =1 and name ='xxx'
-- 第二特性：skip scan index(跳跃扫描索引），例如（id,name)索引列，但是select * from person where gender ='男'and id = 1 and name='xxx' orcale会自动拆分成两个复合索引（'女',id,name)与('男',id,name)寻找'男'这个索引树，所以依然会利用索引查询，但是破坏前缀性原则
-- 第三特性：可选性，根据字段值多少，进行可选性的高低排序，字段值越多，放前面查询可选性越强，后面字段查找越少，效率越高

9.1.3 bitmap（位图）索引：

基数列（列的不重复值的数量）比较小的时候，建议使用bitmap(位图）索引，适合重复率较高的列，支持or like

create bitmap index bind_gender on mytab(gender);

原理：分别产生两个向量位0与1，1表示记录行有数据，0表示表示记录行无数据

如果是1，通过位图索引取值内部对应的映射函数找到对应的rowid，从而快速获取数据

9.1.3 index索引

index，数据对象

作用表的某一列或者多列上面，称之为索引列；
目的通过索引列查询值，会快速高效的定位的行数据
因为索引通过rowid去匹配数据。
类似书籍目录，快速寻找到对应页数据

-- 案例1：创建单列普通索引
Create index name1_index on XYH.F2_H(EMPName);
-- 查看索引时必须使用all_indexes
select * from all_indexes where table_name='F2_H'

注意：并不是所有情况使用索引列查询就一定快，当表中记录数很少时候，索引查询还不如全表扫描效率高

9.2 B-Tree（B树）索引原理

索引值id使用，查询速度快，效率高

orcale逻辑层次

数据库（database）

1.数据库对象，表，视图，索引，序列

2.段（segment）数据段（表），索引段（索引条目的维护区域）

3. 区间

4. 数据块（block）--最终存储单位

索引条目：索引列+rowid（物理地址）

注意：索引列默认升序排序

9.3 B-Tree（B树）索引应用场景：

基数列（列的不重复值的数量（使用重复率低的））比较大的时候，建议使用B-Tree（B树）索引，不支持or like，如果WHERE中出现，则索引不使用，速度变慢

-- 案例1：B-Tree（B树）索引应用

create table mytab（
name varchar2(30) not null,
age number(3),
gender varchar2(6) not null,
martial_status varchar(10) --婚否
）
insert into mytab values('张三',30,'男','未婚');
insert into mytab values('李四',28,'男','离异');
insert into mytab values('赵六'20,'男','未婚');
insert into mytab values('马奇',31,'女','已婚');

--全表扫描(full table scan)
select * from tab_btree where name = '张三';

--索引值id使用,查询速度快,效率高
select * from tab_btree where id = 1;

--案例2:创建索引字段可以排序
create index ind_name on tab_btree(name desc);

--案例3:重建索引
--alter index ind_name rebuild;

解释执行计划：查看某条sql语句使用索引查询还是全表扫描

explain plan for select 子查询语句

查看执行进度与分步

select* from table （dbms_xplan.display);

9.4 什么时候创建索引：

列中数据值分布范围很广
列经常在WHERE子句或连接条件ON中出现
表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

注意：符合上述某一条要求，都可创建索引，创建索引是一个优化问题，同样也是一个策略问题

9.5 什么时候不要创建索引：

表很小
列不经常在WHERE子句或连接条件ON中出现
查询的数据大于2%到4%
表经常更新（维护比较麻烦，可以先删除索引，对数据修改（添加或删除）后再创建索引）
表经常进行 INSERT/UPDATE/DELETE 操作，因为插入数据的同时，还需要维护一个索引。

覆盖索引：根据where条件一一对比，减少回表

9.6 修改索引

alter index in_name rebuild

9.7 索引的特点

（1）索引一旦建立, Oracle管理系统会对其进行自动维护, 而且由Oracle管理系统决定何时使用索引

（2）用户不用在查询语句中指定使用哪个索引

（3）在定义primary key或unique约束后系统自动在相应的列上创建索引

（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引