Oracle中的位图索引和函数索引

简单介绍位图索引和函数索引

位图索引

之前说过BTREE索引是将数据表的索引列和行号排序后以树状形式存在磁盘中。那位图索引是什么样的呢？

现有如下日志表，有操作类型字段op_type，该字段的取值只有“查看”、“查询”、“新增”、“修改”和“删除”。

如下表，当在op_type列建位图索引，如果某数据行取值是“查看”则将该行的“查看”的单元格记为1，在其他取值上记0；如果取值是“修改”则在该行的“修改”的单元格记1，其他取值上记0：

同理，在操作用户列建位图索引如下

查看如下计数语句在各种情况下的效率如何呢？

select count(*) from tsys_log_click;

未建索引前：

建了BTREE索引之后：

在操作类型op_type列上建位图索引之后：

我们在不同前提下进行了三次统计查询，在未建索引的情况下，统计查询走的全表扫描，代价是223，逻辑读是838；在非空列r_id上建BTREE索引之后，统计查询走的索引快速扫描，代价是46，逻辑读是193；在操作类型列上建位图索引之后，统计查询走的位图索引快速扫描，代价是3，逻辑读是7。

所以，在统计查询中，代价最高的是全表扫描；在非空列上建BTREE索引后，一般走BTREE索引的效率要比全表扫描高；最快的是在列取值高度重复的列上建位图索引。

BTREE索引存储的是列值，而位图索引存储的是比特位值，加入位图索引所在的列只有一个取值，比如操作类别列只有“查看”，这时整个位图索引的大小大致等于行数乘以这1个字节，如果操作类型有“查看”和“查询”，最多也就是只有“查看”的情况下的两倍大，所以位图索引在重复度很低时，体积非常小，所以count(*)统计非常快。

当我们在做

where op_type = ‘删除’ and call_user = ‘xrisk’

这样的即席查询时，如果在条件列上建了位图索引，那么查询其实就是个逻辑运算，在我们上面两张表中，找出这两列的取值都为1（与运算）的结果就是了。

但是建了位图索引的数据表，在更新数据时会有很大的问题：

当位图索引列的取值越少，更新数据时，维护索引影响的列数就越多，比如操作类别列如果取值只有“查看”和“查询”的话，键值为“查看”的索引条目指向了数据库一半以上的数据，更新就要影响了一半以上的数据。

所以，如果某列是被频繁更新的，或者频繁插入记录的表，建位图索引是不合适的。上面还有一条，这个列的取值是高度重复的，否则我们像上面构建的位图索引的所谓的表也会很庞大。

函数索引

如果对索引列做运算会导致索引无法使用

select * from tsys_log_click where upper(call_user) = 'TOM';

即使在call_user列上建了索引，这样的查询也不会走索引。但我们可以连带着函数一起建索引，即函数索引

函数索引的性能是在全表扫描和普通索引之间。

所以应该尽可能避免函数索引。例如

select * from tsys_user where substr(user_name,1,3) = 'Tom';

可以改写成

select * from tsys_user where user_name like 'Tom%';