|
|
51CTO旗下网站
|
|
移动端

详解Oracle数据库的三大索引类型

今天主要介绍Oracle数据库的三大索引类型,仅供参考。下面,我们一起来看。

作者:波波说运维来源:今日头条|2019-11-29 07:37

今天主要介绍Oracle数据库的三大索引类型,仅供参考。

详解Oracle数据库的三大索引类型

一、B-Tree索引

三大特点:高度较低、存储列值、结构有序

1. 利用索引特性进行优化

  • 外键上建立索引:不但可以提升查询效率,而且可以有效避免锁的竞争(外键所在表delete记录未提交,主键所在表会被锁住)。
  • 统计类查询SQL:count(), avg(), sum(), max(), min()
  • 排序操作:order by字段建立索引
  • 去重操作:distinct
  • UNION/UNION ALL:union all不需要去重,不需要排序

2. 联合索引

应用场景一:SQL查询列很少,建立查询列的联合索引可以有效消除回表,但一般超过3个字段的联合索引都是不合适的.

应用场景二:在字段A返回记录多,在字段B返回记录多,在字段A,B同时查询返回记录少,比如执行下面的查询,结果c1,c2都很多,c3却很少。

  1. select count(1) c1 from t where A = 1
  2. select count(1) c2 from t where B = 2
  3. select count(1) c3 from t where A = 1 and B = 2

联合索引的列谁在前?

普遍流行的观点:重复记录少的字段放在前面,重复记录多的放在后面,其实这样的结论并不准确。

  1. drop table t purge; 
  2. create table t as select * from dba_objects; 
  3. create index idx1_object_id on t(object_id,object_type); 
  4. create index idx2_object_id on t(object_type,object_id); 

等值查询:

  1. select * from t where object_id = 20 and object_type = 'TABLE'
  2. select /*+ index(t,idx1_object_id) */ * from t where object_id = 20 and object_type = 'TABLE'
  3. select /*+ index(t,idx2_object_id) */ * from t where object_id = 20 and object_type = 'TABLE'

结论:等值查询情况下,组合索引的列无论哪一列在前,性能都一样。

范围查询:

  1. select * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE'
  2. select /*+ index(t,idx1_object_id) */ * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE'
  3. select /*+ index(t,idx2_object_id) */ * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE'

结论:组合索引的列,等值查询列在前,范围查询列在后。 但如果在实际生产环境要确定组合索引列谁在前,要综合考虑所有常用SQL使用索引情况,因为索引过多会影响入库性能。

3. 索引的危害

表上有过多索引主要会严重影响插入性能;

  • 对delete操作,删除少量数据索引可以有效快速定位,提升删除效率,但是如果删除大量数据就会有负面影响;
  • 对update操作类似delete,而且如果更新的是非索引列则无影响。

4. 索引的监控

  1. --监控 
  2. alter index [index_name] monitoring usage; 
  3. select * from v$object_usage; 
  4. --取消监控:  
  5. alter index [index_name] nomonitoring usage; 

根据对索引监控的结果,对长时间未使用的索引可以考虑将其删除。

5. 索引的常见执行计划

  • INDEX FULL SCAN:索引的全扫描,单块读,有序
  • INDEX RANGE SCAN:索引的范围扫描
  • INDEX FAST FULL SCAN:索引的快速全扫描,多块读,无序
  • INDEX FULL SCAN(MIN/MAX):针对MAX(),MIN()函数的查询
  • INDEX SKIP SCAN:查询条件没有用到组合索引的第一列,而组合索引的第一列重复度较高时,可能用到

二、位图索引

应用场景:表的更新操作极少,重复度很高的列。

优势:count(*) 效率高

  1. create table t( 
  2. name_id, 
  3. gender not null, 
  4. location not null, 
  5. age_range not null, 
  6. data 
  7. )as select  
  8. rownum, 
  9. decode(floor(dbms_random.value(0,2)),0,'M',1,'F') gender, 
  10. ceil(dbms_random.value(0,50)) location, 
  11. decode(floor(dbms_random.value(0,4)),0,'child',1,'young',2,'middle',3,'old') age_range, 
  12. rpad('*',20,'*') data 
  13. from dual connect by rownum <= 100000;  
  1. create index idx_t on t(gender,location,age_range); 
  2. create bitmap index gender_idx on t(gender); 
  3. create bitmap index location_idx on t(location); 
  4. create bitmap index age_range_idx on t(age_range); 
  1. select * from t where gender = 'M' and location in (1,10,30) and age_range = 'child'
  2. select /*+ index(t,idx_t) */* from t where gender = 'M' and location in (1,10,30) and age_range = 'child'

三、函数索引

应用场景:不得不对某一列进行函数运算的场景。

利用函数索引的效率要低于利用普通索引的。

oracle中创建函数索引即是 你用到了什么函数就建什么函数索引,比如substr

  1. select * from table where 11=1 and substr(field,0,2) in ('01') 

创建索引的语句就是

  1. create index indexname on table(substr(fileld,0,2)) online nologging ; 

【编辑推荐】

  1. 5个优秀的开源图数据库
  2. 数据库连接池技术的原理
  3. 详解SQL Server数据库sql优化注意事项25条
  4. 值得关注的五大SQL数据库恢复软件
  5. 记一次Oracle数据库实验--索引的常见执行计划
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

骨干网与数据中心建设案例

骨干网与数据中心建设案例

高级网工必会
共20章 | 捷哥CCIE

403人订阅学习

中间件安全防护攻略

中间件安全防护攻略

4类安全防护
共4章 | hack_man

151人订阅学习

CentOS 8 全新学习术

CentOS 8 全新学习术

CentOS 8 正式发布
共16章 | UbuntuServer

291人订阅学习

视频课程+更多

强哥带你精通OpenStack私有云

强哥带你精通OpenStack私有云

讲师:周玉强48185人学习过

Java项目实战上卷[SpringBoot/SpringCloud/RabbitMQ/Redis]

Java项目实战上卷[SpringBoot/SpringCloud/Ra

讲师:鸟哥教育38043人学习过

强哥带你精通tomcat

强哥带你精通tomcat

讲师:周玉强4704人学习过

读 书 +更多

Eclipse Web开发从入门到精通(实例版)

本书由浅入深、循序渐进地介绍了目前流行的基于Eclipse的优秀框架。全书共分14章,内容涵盖了Eclipse基础、ANT资源构造、数据库应用开发、W...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微