Oracle之位图索引

前两天看到有个SQL 查询的，当走位图索引的时候非常慢，当不走索引的事情反而更快一点。趁机会了解学习下。

先看一个数据表的结构，WORKERS

ID	NAME	SEX	DEPT	AGE	HIREDATE
1	zhangsan	Male	DOM1	23	201709
2	lisi	Female	DOM1	35	201707
3	wangwu	Male	DOM2	26	201704
4	maliu	Female	DOM2	28	201301
5	zhaoqi	Male	DOM3	27	201201
...	...	...	...	...

对于此表，我们看下下面几种情况：

1）不使用索引
　　我们知道，不使用索引时，数据库只能Table Full Scan 所有记录，然后判断该记录是否满足查询条件进行 filter。

2）B树索引
对于性别、部门这样的字段，记录的取值范围只有两个或者几个，而数据库里面数据量反而很大。这时候如果使用B 树索引的话，甚至还要取出一半的数据。我们知道，Oracle会对我们的SQL 进行优化，一般会优先进行索引查询。这样的话 Oracle就会从硬盘上加载大量的数据，而且这些数据绝大部分还会被下一步给 filter 掉。显然，这不是我们想要的。实际上，B 树索引适合那种取值范围很广，且几乎没有重复的数据列。

3）位图索引
在Oracle 中，位图索引适用于只有几个固定值的列，如部门，性别等。

回到上面的数据例子，在上面的数据库表 WORKERS 中 在 SEX 和 DEPT 两列上加位图索引。
此时对于 SEX 这列会生成如下向量，10101...，和 01010...。

ID	1	2	3	4	5	...
Male	1	0	1	0	1
Female	0	1	0	1	0

对于 DEPT 这列会生成如下向量, 11000...，00111...，00001...。

ID	1	2	3	4	5	...
DOM1	1	1	0	0	0
DOM2	0	0	1	1	0
DOM3	0	0	0	0	1

当Oracle 使用位图索引查询时，就会对向量进行操作，比如当执行下面的SQL 查询时，会进行 and 操作。

SELECT * FROM WORKERS w where w.SEX = 'Male' and w.DEPT = 'DOM1' ;

Oracle 发现两个列上用设置了位图索引，会为该查询进行向量操作，即对 Male 向量 和 DOM1向量 进行 and 操作。

ID	1	2	3	4	5	...	...
Male	1	0	1	0	1
DOM1	1	1	0	0	0
AND 结果	1	0	0	0	0

从上面的结果看，对 Male 向量和 DOM1 向量进行 AND 操作后，得到的结果中只有 第一个为 1 。因此, 位图索引的查询结果只有第一列。

适用条件

上面也讲了，位图索引适合只有固定几个值的列。另外，位图索引适合静态数据，即不适合频繁更新的列。这个很好理解，比如用户 A 将了一个员工的 DEPT 从DOM3更新为 DOM1，这个操作会导致 DOM1 和 DOM3 的向量发生改变，Oracle 会将 锁定 DOM1 和 DOM3 的所有数据行，且只有在 用户A commit 后才解锁。在此之前，其他用户只能更新 DOM2向量的记录。无疑，这会导致性能变慢，影响系统并发。

还有，当数据库表中数据量比较大时，而查询结果数据量比较少时，最好先使用其他索引进行 filter 后，再使用位图索引进行 filter。还用上面的例子，比如以下查询：

SELECT * FROM WORKERS w where w.SEX = 'Male' and w.DEPT = 'DOM1' and HIREDATE > '201708' ;

Oracle会使用 两个列的位图索引进行查询。当该表的数据量比较大时，比如百万条。而我们的查询结果估计也只有一百条左右。

Oracle 先使用位图索引，从硬盘上加载数十万的记录，然后再用 HIREDATE 列进行过滤。这个过程中，大量的无用数据被加载到内存中，耗时耗内存，得不偿失。因此使用位图索引一定要谨慎。