组合索引的三大特点和查询条件以及应用场景说明

概述

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

1. B树索引

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

1.1 索引特征优化

外键索引：不仅可以提高查询效率，还可以有效避免锁竞争（外键所在表的记录不提交，主键所在表就会被锁）。

1.2 联合索引

应用场景一：SQL查询列很少，为查询列建立联合索引可以有效杜绝回表，但一般超过3个字段的联合索引不适合。

应用场景二：A字段返回多条记录，B字段返回多条记录，A、B字段都返回少条记录。比如下面的查询结果c1和c2多，c3少。

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

谁是联合索引的第一列？

有一种流行的观点：把重复记录少的字段放在前面，把重复记录多的字段放在后面。 其实这个结论并不准确。

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

 --等值查询：
 select * from t where object_id = 20 and object_type = 'TABLE';
 select  * from t where object_id = 20 and object_type = 'TABLE';
 select  * from t where object_id = 20 and object_type = 'TABLE';

结论：在等价查询的情况下，无论哪一列在复合索引的前面，性能都是一样的。

 --范围查询：
 select * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE';
 select  * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE';
 select  * from t where object_id >=20 and object_id < 2000 and object_type = 'TABLE';

结论：对于组合索引的列，等价查询列在最前面，范围查询列在最后。 但是在实际生产环境中如果要确定复合索引列中谁排在第一位，则必须综合考虑所有普通SQL中索引的使用，因为索引过多会影响存储性能。

1.3 索引的危险

表上过多的索引会严重影响插入性能；

1.4 指标监测

 --监控
 alter index [index_name] monitoring usage;
 select * from v$object_usage;
 --取消监控： 
 alter index [index_name] nomonitoring usage;

根据索引监控的结果，可以考虑删除长期不用的索引。

1.5 索引常用的执行计划

INDEX FULL SCAN：索引的全扫描，单块读取，有序

INDEX RANGE SCAN：索引范围扫描

INDEX FAST FULL SCAN：索引的快速全扫描，多块读取，无序

INDEX FULL SCAN(MIN/MAX)：查询 MAX()、MIN() 函数

INDEX SKIP SCAN：查询条件不使用复合索引的第一列，但可能在复合索引的第一列重复度较高时使用

2.位图索引

应用场景：表的更新操作很小，重复度很高的列。

优点：count(*) 是高效的

 create table t(
 name_id,
 gender not null,
 location not null,
 age_range not null,
 data
 )as select 
 rownum,
 decode(floor(dbms_random.value(0,2)),0,'M',1,'F') gender,
 ceil(dbms_random.value(0,50)) location,
 decode(floor(dbms_random.value(0,4)),0,'child',1,'young',2,'middle',3,'old') age_range,
 rpad('*',20,'*') data
 from dual connect by rownum <= 100000; 

 create index idx_t on t(gender,location,age_range);
 create bitmap index gender_idx on t(gender);
 create bitmap index location_idx on t(location);
 create bitmap index age_range_idx on t(age_range);

 select * from t where gender = 'M' and location in (1,10,30) and age_range = 'child';
 select * from t where gender = 'M' and location in (1,10,30) and age_range = 'child';

三、功能指标

应用场景：需要对某列进行函数操作的场景。

使用函数索引比使用普通索引效率低。

要在 中创建函数索引，您可以为您使用的任何函数创建函数索引，例如

* 来自 1=1 和 (field,0,2) in ('01') 的表

创建索引的语句是

表上的索引((,0,2)) ;

觉得有用的朋友帮忙转发一下！ 后面会分享更多关于DBA的内容，感兴趣的朋友可以关注一下~