数据库优化避坑之位图索引

最近在几个项目中遇到系统出现大量死锁，导致整个系统基本无法使用，客户意见很大。结果软件厂商把锅摔倒数据库这边，到现场进行问题排查。
排查步骤
1、打开DEM，发现有慢SQL、锁等，活动会话保持500以上，系统基本瘫痪；
2、分析锁，发现死锁的SQL单独执行都非常快（10ms之内），但是查看执行计划的时候，发现BMSEK这个操作符，也就是说使用的是位图索引，此时系统基本瘫痪，现场决定先将位图索引改为B树索引；改完后活动会话立马下降到50以内。
3、分析慢SQL，发现慢SQL单独执行也没什么问题，但是查看执行计划是发现使用大量位图索引。
4、在中午空闲时段，优化几条相对比较慢的SQL同时将位图索引改为B树索引，下午高峰期发现活动会话数由之前的高峰期几百个降到10个以下。
5、观察几天，发现高峰期活动会话从未超过50个，系统运行稳定，至此问题解决。
6、下面将这次优化的场景大致模拟下，希望可以对其他项目起到借鉴作用。

环境准备
1、创建测试用表

CREATE TABLE "TEST_BITMAP"
(
"ID_" VARCHAR2(128) NOT NULL,
"REV_" NUMBER,
"TYPE_" VARCHAR2(510) NOT NULL,
"NAME_" VARCHAR2(510) NOT NULL,
"EXECUTION_ID_" VARCHAR2(128),
"PROC_INST_ID_" VARCHAR2(128),
"TASK_ID_" VARCHAR2(128),
"BYTEARRAY_ID_" VARCHAR2(128),
"DOUBLE_" NUMBER,
"LONG_" NUMBER(19,0),
"TEXT_" VARCHAR2(3900),
"TEXT2_" VARCHAR2(3900),
NOT CLUSTER PRIMARY KEY("ID_")) ;

COMMENT ON TABLE "TEST_BITMAP" IS '流程变量';
COMMENT ON COLUMN "TEST_BITMAP"."BYTEARRAY_ID_" IS '内容ACT_GE_BYTEARRAY';
COMMENT ON COLUMN "TEST_BITMAP"."DOUBLE_" IS '浮点值';
COMMENT ON COLUMN "TEST_BITMAP"."EXECUTION_ID_" IS '执行id';
COMMENT ON COLUMN "TEST_BITMAP"."ID_" IS '主键';
COMMENT ON COLUMN "TEST_BITMAP"."LONG_" IS '长整型值';
COMMENT ON COLUMN "TEST_BITMAP"."NAME_" IS '名称';
COMMENT ON COLUMN "TEST_BITMAP"."PROC_INST_ID_" IS '流程实例id';
COMMENT ON COLUMN "TEST_BITMAP"."REV_" IS '乐观锁';
COMMENT ON COLUMN "TEST_BITMAP"."TASK_ID_" IS '任务id';
COMMENT ON COLUMN "TEST_BITMAP"."TEXT2_" IS 'jpa变量text存 className,text2存id';
COMMENT ON COLUMN "TEST_BITMAP"."TEXT_" IS '文本值';
COMMENT ON COLUMN "TEST_BITMAP"."TYPE_" IS '类型';

2、插入数据

使用dts工具或者DMFLDR工具，将附件的数据导入到表中。

3、创建模拟表

create table TEST_BITMAP_bak as select * from TEST_BITMAP;
create table TEST_BITMAP_bak1 as select * from TEST_BITMAP;
TEST_BITMAP_bak上面建B树索引
TEST_BITMAP_bak1上建位图索引
CREATE INDEX TEST_BITMAPBAK_EXECUTION_ID_ ON TEST_BITMAP_bak(EXECUTION_ID_ ASC)  ;
SP_INDEX_STAT_INIT('SYSDBA','TEST_BITMAPBAK_EXECUTION_ID_');--收集统计信息
CREATE BITMAP  INDEX TEST_BITMAP_EXECUTION_ID_1 ON SYSDBA.TEST_BITMAP_bak1(EXECUTION_ID_ ASC) ;
--位图索引不需要收集统计信息

4、先看现象
（1）执行查询：
使用位图索引

select * from TEST_BITMAP_bak1 where EXECUTION_ID_ = '3117501' and TASK_ID_ is null ;

1 #NSET2: [0, 42, 530]
2 #PRJT2: [0, 42, 530]; exp_num(13), is_atom(FALSE)
3 #SLCT2: [0, 42, 530]; TEST_BITMAP.TASK_ID_ IS NULL
4 #BLKUP2: [0, 115, 530]; INDEX33557680(TEST_BITMAP)
5 #BMCVT: [0, 115, 530]
6 #BMSEK: [0, 115, 530]; scan_type(ASC), INDEX33558340(BM$_33558339), scan_range[(‘3117501’,null2),(‘3117501’,max))
使用B树索引

select * from TEST_BITMAP_bak where EXECUTION_ID_ = '3117501' and TASK_ID_ is null ;

1 #NSET2: [0, 42, 530]
2 #PRJT2: [0, 42, 530]; exp_num(13), is_atom(FALSE)
3 #SLCT2: [0, 42, 530]; TEST_BITMAP_bak.TASK_ID_ IS NULL
4 #BLKUP2: [0, 42, 530]; TEST_BITMAPBAK_EXECUTION_ID_(TEST_BITMAP_bak)
5 #SSEK2: [0, 42, 530]; scan_type(ASC), TEST_BITMAPBAK_EXECUTION_ID_(TEST_BITMAP_bak), scan_range[‘3117501’,‘3117501’]

查询效率几乎没差别

（2）测试插入场景
A、插入单条记录
总共2个语句正依次执行…

执行语句1:
insert into ACT_RU_VARIABLE_bak select * from ACT_RU_VARIABLE where rownum=1;
执行成功, 执行耗时2毫秒. 执行号:62112
影响了1条记录

执行语句2:
insert into ACT_RU_VARIABLE_bak1 select * from ACT_RU_VARIABLE where rownum=1;
执行成功, 执行耗时6毫秒. 执行号:62113
影响了1条记录

2条语句执行成功
对于单条插入，执行时间基本没差别。

B、插入多条记录
总共2个语句正依次执行…

insert into TEST_BITMAP_bak select * from TEST_BITMAP;
执行成功, 执行耗时1秒 102毫秒. 执行号:54900
影响了252,179条记录

insert into TEST_BITMAP_bak1 select * from TEST_BITMAP;
执行成功, 执行耗时21分 55秒 331毫秒. 执行号:59501
影响了252,179条记录

2条语句执行成功

5、了解定义：
位图索引主要针对含有大量相同值的列而创建。位图索引被广泛引用到数据仓库中，创建方式和普通索引一致，对低基数（不同的值很少）的列创建位图索引，能够有效提高基于该列的查询效率。且执行查询语句的 where 子句中带有 AND 和 OR 谓词时，效率更加明显。
位图索引具有以下约束：

支持普通表、堆表和水平分区表创建位图索引；
不支持对大字段创建位图索引；
不支持对计算表达式列创建位图索引；
不支持在 UNIQUE 列和 PRIMARY KEY 上创建位图索引；
不支持对存在 CLUSTER KEY 的表创建位图索引；
仅支持单列或者不超过 63 个组合列上创建位图索引；
MPP 环境下不支持位图索引的创建；
不支持快速装载建含有位图索引的表；
不支持全局位图索引；
包含位图索引的表不支持并发的插入、删除和更新操作。

6、他山之石
通过这次项目优化，可以看出在对索引使用不当时，对整个系统可能造成灾难性的影响。这里需要大家注意，在oltp系统中不要使用位图索引，切记。
7、通过这条SQL可以检查系统中是否使用位图索引：

select * from all_indexes where index_type='BITMAP';

如果有的话，可以执行

create or replace  index "TEST_BITMAP_EXECUTION_ID_1" on "TEST_BITMAP_BAK1"("EXECUTION_ID_");

对索引进行重建，将位图索引改为B树索引。

数据库优化避坑之位图索引

作者