B-Tree索引与Bitmap索引的锁代价的比较

最新推荐文章于 2022-02-23 11:13:53 发布

原创最新推荐文章于 2022-02-23 11:13:53 发布 · 3.8k 阅读

2 ·

CC 4.0 BY-SA版权

Oracle 基础管理专栏收录该内容

131 篇文章

订阅专栏

本文对比了B-Tree索引与Bitmap索引在理论和实验上的表现。Bitmap索引适用于基数小、重复值多的场景，如OLAP查询，但DML操作锁代价高。B-Tree索引适用于数据重复度低的字段，提供稳定的查询性能，尤其适合主键或唯一性约束。实验结果显示，B-Tree索引在插入时不会影响其他会话的DML操作，而Bitmap索引可能会因索引段级锁导致锁等待。

环境：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

--理论介绍

1）Bitmap index

场合：列的基数很少，可枚举，重复值很多，数据不会被经常更新

原理：一个键值对应N行,即 (键值:rowid)=(1:n)

格式：键值|start_rowid|end_rowid|位图


优点：
① OLAP
② 重复率高的数据，也就是低cardinality列，例如“性别”列，列值只有“M”，“F”两种
③ 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果
④ 位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多


缺点：
① 不适合重复率低的字段
② 经常DML操作（insert，update，delete），因为位图索引的锁代价极高，会导致一些DML语句出现“锁等待”，例如修改一个键值，会影响同键值的多行，所以对于OLTP系统位图索引基本上是不适用的

2) B-Tree index

场合：非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段，常用于主键、唯一性约束，一般在在线交易的项目中用到的多些。

原理：一个键值对应一行（rowid）

格式：索引头|键值|rowid

优点：
① 查询性能与表中数据量无关，例如查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。
② 当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引

缺点：不适合键值重复率较高的字段上使用，例如第一章 1-500page 第二章 501-1000page

--实验

①--建表

SQL> create table t_bitmap (id number(10),name varchar2(10),sex varchar2(1));

表已创建。

SQL> create bitmap index t_bitmap_idx on t_bitmap(sex);

索引已创建。

SQL> create table t_btree (id number(10),name varchar2(10),sex varchar2(1));

表已创建。

SQL> create index t_btree_idx on t_btree(sex);

索引已创建。


SQL> insert into t_btree values (1,'think','M');

已创建 1 行。

SQL> insert into t_btree values (2,'qinqin','F');

已创建 1 行。

SQL> insert into t_bitmap values(1,'think','M');

已创建 1 行。

SQL> insert into t_bitmap values(2,'qinqin','F');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t_btree;

        ID NAME       S
---------- ---------- -
         1 think      M
         2 qinqin     F

SQL> select * from t_bitmap;

        ID NAME       S
---------- ---------- -
         1 think      M
         2 qinqin     F

②--对Btree index进行DML

***********session_A***************

SQL> insert into t_btree values (3,'hangzhen','M');

已创建 1 行。

***********session_B***************

SQL> insert into t_btree values (4,'yuechuang','M');

已创建 1 行。

SQL> update t_btree set sex='M' where id=2;

已更新 1 行。

SQL> delete from t_btree;

已删除3行。

③--对Bitmap index进行DML

***********session_A****************


SQL> insert into t_bitmap values (3,'hangzhen','M');

已创建 1 行。

***********session_B******************

㈠ insert动作
SQL> insert into t_bitmap values (4,'yuechuang','M');
--出现锁等待

当插入数据未涉及位图索引列“sex”字段时，是可以完成的
SQL> insert into t_bitmap (id,name) values (4,'yuechuang');

已创建 1 行。


㈡ update动作
当更新位图索引列“sex”字段值为“M”时，是无法完成的

SQL> select * from t_bitmap;

        ID NAME       S
---------- ---------- -
         1 think      M
         2 qinqin     F
         4 yuechuang

SQL> update t_bitmap set sex='M' where id=1;

已更新 1 行。
此时成功，是因为第1行数据的sex值本身就是“M”。

SQL> update t_bitmap set sex='M' where id=4;
--锁等待。
SQL> update t_bitmap set sex='M' where id=2；
--锁等待

特别注意一下，如果更新的列不是位图索引对应的列，将不会受位图段级索引锁的限制

SQL> update t_bitmap set name='Water' where  id=1;

已更新 1 行。


㈢ delete 动作

SQL> select * from t_bitmap;

        ID NAME       S
---------- ---------- -
         3 hangzhen   M
         4 yuechuang  F
         5 ss         M

SQL> delete from t_bitmap where id=3;
--锁等待

SQL> delete from t_bitmap where id=4;

已删除 1 行。

SQL> delete from t_bitmap where id=5;
--锁等待