Welcome 微信登录

首页 / 数据库 / MySQL / Oracle索引种类之位图索引

位图索引

1.1 位图索引概述

  • 位图索引通过位图向量，表示索引键值在表中的分布。
  • 适用于没有大量更新操作的对象，如：OLAP数据库。
  • 对于存在大量更新操作的索引列，不适用位图索引。因此对于OLTP并不适用。
    • 更新位图向量时，相应位图涉及的所有数据行会被锁定，无法针对这些数据行的该索引列进行DML操作。

1.2 位图索引结构的说明

与B-tree索引的联系及区别如下：
  • 与B-tree索引的联系：位图索引使用B-tree形式组成。
  • 与B-tree索引的区别：位图索引的一个索引键值对应一个叶子节点。（B-tree的叶节点包含多个索引键值）
  • 与B-tree索引的区别：位图索引使用位图向量标识键值对应的数据行分布情况。（B-tree采用rowid定位数据行）
此外：
  • 位图索引创建时，不需要进行排序，因此速度较快；而B-tree索引创建时，需要排序等操作，因此慢很多。
  • 位图索引允许键值为NULL，因此进行NULL条件查询时，可以使用索引。而B-tree索引不记录NULL（组合索引除外），因此会使用全表扫描。
  • 对于表的访问效率很高
    • 当使用count（XX）,可以直接访问索引就快速得出统计数据.
    • 当根据位图索引的列进行and,or或 in（x,y,..）查询时,直接用索引的位图进行或运算,在访问数据之前可事先过滤数据.
需要重申的是：
  • B树索引中，由于根据键值的rowid定位数据行，因此相同的键值存在多次，与相应的rowid对应。
  • 位图索引中，由于根据位图向量定位键值所在的数据行，因此相同键值只需要记录一次。 

1.3 位图索引对于执行计划的影响

对于B-tree索引，CBO优化器会根据数据的具体分布情况，选择是否应用索引。对于位图索引，即使读取非常大的数据，仍然会选择索引。-- 查看测试数据表的结构
Yumiko@sunny >desc test01; NameNull？Type --------------------------------------------- -------- ---------------- OWNERVARCHAR2（30） OBJECT_NAMEVARCHAR2（128） SUBOBJECT_NAME VARCHAR2（30） OBJECT_IDNUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPEVARCHAR2（19） CREATEDDATE LAST_DDL_TIMEDATE TIMESTAMPVARCHAR2（19） STATUS VARCHAR2（7） TEMPORARYVARCHAR2（1） GENERATEDVARCHAR2（1） SECONDARYVARCHAR2（1）

-- 查看表的总行数Yumiko@sunny >select count（*） from test01;COUNT（*）---------- 22928


-- 查看owner字段的数据分布情况，可以发现owner字段的含SYS的数据非常庞大，存在明显的数据倾斜
-- 准备为owner字段分两次，建立b-tree索引以及位图索引，通过相同的查询条件，比较索引的使用情况
Yumiko@sunny >select owner,count（*） from test01 group by owner order by count（*）;OWNERCOUNT（*）------------------------------ ----------BI8SCOTT10SYS 22910

--建立b-tree索引并验证Yumiko@sunny >create index BTREE_OWNER on test01（owner）;Index created.
Yumiko@sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME = "TEST01";INDEX_NAME INDEX_TYPETABLE_NAME------------------------------ --------------------------- ------------------------------BTREE_OWNERNORMALTEST01
--打开会话根据，已查看执行计划Yumiko@sunny >set autotrace trace




--查询owner字段为BI时，由于从较多数据中检查个别数据，执行计划用到了索引扫描，通过索引块，快速定位目标数据的rowid，进而访问数据块获得结果。此时效率最高
Yumiko@sunny >select * from test01 where owner="BI";8 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 725909888-------------------------------------------------------------------------------------------| Id| Operation | Name| Rows| Bytes | Cost （%CPU）| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 8 |1416 | 2 （0）| 00:00:01 || 1 |TABLE ACCESS BY INDEX ROWID| TEST01| 8 |1416 | 2 （0）| 00:00:01 ||*2 | INDEX RANGE SCAN| BTREE_OWNER | 8 | | 1 （0）| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 2 - access（"OWNER"="BI"）Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 40recursive calls0db block gets 84consistent gets4physical reads0redo size 2010bytes sent via SQL*Net to client469bytes received via SQL*Net from client2SQL*Net roundtrips to/from client0sorts （memory）0sorts （disk）8rows processed

-- 查询owner字段为SYS时，由于数据中该字段的数据存在明显的数据倾斜，若通过索引方式检索，将先扫面大部分的索引块，然后再根据rowid查找数据块，此时代价十分大，不如直接全表扫描效率高，因此不用索引。
Yumiko@sunny >select * from test01 where owner="SYS";22910 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 262542483----------------------------------------------------------------------------| Id| Operation | Name | Rows| Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT|| 22064 |3813K| 159 （2）| 00:00:02 ||*1 |TABLE ACCESS FULL| TEST01 | 22064 |3813K| 159 （2）| 00:00:02 |----------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 1 - filter（"OWNER"="SYS"）Note----- - dynamic sampling used for this statementStatistics----------------------------------------------------------7recursive calls0db block gets 2288consistent gets8physical reads0redo size1148463bytes sent via SQL*Net to client17266bytes received via SQL*Net from client 1529SQL*Net roundtrips to/from client0sorts （memory）0sorts （disk）22910rows processed

-- 删除B-tree索引，创建位图索引并查看Yumiko@sunny >create bitmap index bitmap_owner on test01（owner）;Index created.Yumiko@sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME = "TEST01";INDEX_NAME INDEX_TYPETABLE_NAME------------------------------ --------------------------- ------------------------------BITMAP_OWNER BITMAPTEST01


-- 打开会话跟踪，以查看执行计划Yumiko@sunny >set autotrace trace


-- 当查看owner为BI时，与b-tree一样，使用索引扫描的方式进行检索。Yumiko@sunny >select * from test01 where owner="BI";8 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3098739824---------------------------------------------------------------------------------------------| Id| Operation| Name | Rows| Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 8 |1416 |55 （0）| 00:00:01 || 1 |TABLE ACCESS BY INDEX ROWID | TEST01 | 8 |1416 |55 （0）| 00:00:01 || 2 | BITMAP CONVERSION TO ROWIDS|| | ||||*3 |BITMAP INDEX SINGLE VALUE | BITMAP_OWNER | | |||---------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 3 - access（"OWNER"="BI"）Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 40recursive calls0db block gets 82consistent gets0physical reads0redo size 2010bytes sent via SQL*Net to client469bytes received via SQL*Net from client2SQL*Net roundtrips to/from client0sorts （memory）0sorts （disk）8rows processed


--当查看owner为SYS时，此时与B树索引不同，依然选择了位图索引
Yumiko@sunny >select * from test01 where owner="SYS";22910 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3098739824---------------------------------------------------------------------------------------------| Id| Operation| Name | Rows| Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 22064 |3813K|55 （0）| 00:00:01 || 1 |TABLE ACCESS BY INDEX ROWID | TEST01 | 22064 |3813K|55 （0）| 00:00:01 || 2 | BITMAP CONVERSION TO ROWIDS|| | ||||*3 |BITMAP INDEX SINGLE VALUE | BITMAP_OWNER | | |||---------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 3 - access（"OWNER"="SYS"）Note----- - dynamic sampling used for this statementStatistics----------------------------------------------------------7recursive calls0db block gets 2130consistent gets0physical reads0redo size2526059bytes sent via SQL*Net to client17266bytes received via SQL*Net from client 1529SQL*Net roundtrips to/from client0sorts （memory）0sorts （disk）22910rows processed从上面可以看到，对于位图索引，即使从表中读取很多行，也会使用索引。

1.4 DML操作对于位图索引的影响

对于位图索引，当一个事务更新一条记录的索引列键值，且未提交事务时，其他事务对于该索引键值涉及的数据行的该索引列值的修改，将处于等待。--查看当前两个会话的事件，无特殊事件
Yumiko@sunny >select SID,USERNAME,PROGRAM,EVENT from v$session where username="SCOTT"; SID USERNAME PROGRAM EVENT------- ----------------- ------------------------------------------------------------- 147 SCOTTsqlplus@OA01 （TNS V1-V3）SQL*Net message from client 153 SCOTTsqlplus@OA01 （TNS V1-V3）SQL*Net message from client

--查看表的前三行数据Yumiko@sunny01 >select OWNER,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from test01 where rownum < 4;OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE------------------------------ ---------- -------------- -------------------SYS202 TABLESYS44 44 INDEXSYS28 28 TABLE
--发起事务1对于位图索引列owner为SYS值的一行数据的owner字段的更新操作，但不提交事务。Yumiko@sunny01 >update test01 set OWNER="SCOTT" where DATA_OBJECT_ID=28;1 row updated.

--发起事务2对于位图索引列owner为SYS值的另一行数据库的owner字段的更新操作，此时事务停顿。Yumiko@sunny02 >update test01 set OWNER="BI" where DATA_OBJECT_ID=44;

--查看此时会话的事件，发现有row lock等待事件Yumiko@sunny >select SID,USERNAME,PROGRAM,EVENT from v$session where username="SCOTT"; SID USERNAME PROGRAMEVENT--------- ------------------------------ ------------------------------------------------ 147 SCOTTsqlplus@OA01 （TNS V1-V3） enq: TX - row lock contention 153 SCOTTsqlplus@OA01 （TNS V1-V3） SQL*Net message from client通过上面的示例证明了对于位图索引列的更新，相应键值的位图向量涉及的数据行在键值更新事务提交前，会始终处于锁定状态，其他事务无法对这些数据行的该索引列进行DML操作。因此，对于DML操作频繁度较高的OLTP数据库而言，位图索引不推荐使用。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址
版权所有©石家庄振强科技有限公司2024 冀ICP备08103738号-5 网站地图