Oracle数据库优化：位图索引的高效与风险

一. 简介

位图索引（Bitmap Index） 是 Oracle 数据库中一种特殊类型的索引，适用于低基数（Low Cardinality）列，即那些列中可选值相对较少的情况下使用。它与常规的 B-tree 索引不同，位图索引通过位图（bitmap）来表示列的不同取值及其在表中的位置，从而有效加速特定类型的查询。但位图索引有很大的弊端，使用不当会造成大量的等待事件，甚至造成系统崩溃，位图索引需要谨慎使用。

二. 实验 

create table bm_table as select * from dba_objects;
insert into bm_table select * from bm_table;
/
/
/
/
/
update bm_table set object_id = rownum;
复制

此时表内记录约 230 万。

未建立索引的执行计划：

set autotrace traceonly
set linesize 200
select count(*) from bm_table;
复制

建立普通索引的执行计划

create index idx1 on bm_table(object_id);
复制

此时仍走全表扫描，原因是索引无法存储空行，CBO 不会为整行都为空行的表走索引。

 设置 object_id 列为 not null，让语句走索引。

alter table bm_table modify object_id not null;
select count(*) from bm_table;
复制

意料之中，走索引快速全扫描，一次性读取多个数据块，速度要快于 INDEX FULL SCAN。

建立位图索引的执行计划

create bitmap index index2 on bm_table(status);
复制

根据位图索引的特性，这个索引我们建在 status 列上，因为 status 列 可选值相对较少。

select count(*) from bm_table;
复制

执行结果比较

为什么位图索引这么快？

这是由位图索引的原理决定的

如果某个列有三个可能的值（例如，性别列有 "M"、"F"、"U"），那么 Oracle 会为每个可能的值创建一个位图，如下所示：

值为'M' 的位图: 101001
值为'F' 的位图: 010100
值为'U' 的位图: 000010
 

位图索引以二进制的形式存储，性能极高。

三. 位图索引的弊端

insert into bm_table(OBJECT_ID,STATUS) values(195555,'invalid');
select distinct status from bm_table;
复制

此时模拟 DML 语句。

当前会话 id：

select sid from v$mystat where rownum=1;
复制

另开一个会话 2，此时的会话 id：

会话 1 执行 DML 操作：

insert into bm_table(OBJECT_ID,STATUS) values(195555,'invalid');
复制

会话 2 执行 DML 操作：

insert into bm_table(OBJECT_ID,STATUS) values(195555,'invalid')；
复制

发现 sql 被锁，一直在等待。

中止 sql 执行，换一条 sql 执行。

insert into bm_table(OBJECT_ID,STATUS) values(195555,'valid')；
复制

sql 反而又执行成功。

如果不更新 status 列呢?

insert into bm_table(OBJECT_ID) values(195555);
复制

也会更新成功。 

四. 总结

在会话一中，我们做了插入 status 列 为 invalid， 在会话二中，我们做了插入 status 列为 invalid 造成 sql 等待，又做了插入 status 列 为 valid 反而插入成功，继续做插入不涉及 status 列 也可以更新成功。

结合位图索引结构与存储方式，发现

如果在表的某列建了位图索引，那么在做 DML 操作时（包括 delete 与 update），会造成 位图索引列 =‘x’，的列全被锁住，例如，在 gender 列上有位图索引，此时做 DML 操作更新 某行 gender = 男，那么此时 所有会话 想更新 gender = 男 全都会被锁住，直到 初始更新 gengder = 男 的会话 事务提交。

就 OLTP 系统来说，这样是不能容忍的，所以 ，位图索引仅仅适用于很少更新的场场景，并且列的取值大多重复的场景，做实验得出，如果列的取值重复度不高，位图索引的效率甚至要比全表扫描还要差！ 

所以 如果只知道位图索引只适用于列的重复值较多，不知道位图索引会造成所有会话的 sql 等待而盲目创建位图索引，那后果是很严重的。