数 据 库 培 训 项 目 研 究 Oracle 索 引 探 究 B*tree 索 引 与 位 图 索 引 的 特 点 作 者 : 赵 超 2008 年 12 月 18 日
实 验 环 境 Windows-server2003 内 存 :2G Oracle 10.2.0 ORACLE_SID=orcl
索 引 类 型 B*tree 索 引 ( 默 认 方 式 ) 位 图 索 引 (bitmap) 反 向 键 索 引 (reverse key) 位 图 连 接 索 引 (bitmap Join) 基 于 函 数 的 索 引 (function-based) 域 索 引 (domain) B*tree 索 引 是 比 较 基 础 的 索 引, 大 多 数 索 引 是 在 其 存 储 结 构 上 的 此 外, 根 据 不 同 的 分 类 方 法, 可 分 为 不 同 的 类 型 : 根 据 存 储 结 构 可 共 分 为 b*tree 索 引 位 图 索 引 反 向 索 引 ; 按 照 索 引 个 数 可 分 为 单 列 索 引 复 合 索 引 ; 按 照 索 引 列 值 唯 一 性 可 分 为 唯 一 性 索 引 和 非 唯 一 性 索 引
B*tree 索 引 B*tree 索 引 :B 是 (balance) 是 默 认 的 索 引 类 型, 是 数 值 的 有 序 列 表 他 是 基 于 二 叉 树, 索 引 的 顶 层 是 根 块 儿, 指 向 下 的 分 支 块 儿, 最 终 指 向 叶 块 叶 块 中 包 含 被 索 引 列 的 值 和 行 所 对 应 的 rowid 结 构 : 少 于 等 于 50 大 于 等 于 50 50-75 75-100 >100 0-25 25-50 100 rid 101 rid 102 rid ~~~ 50 rid 51 rid 52 rid ~~~ 25 rid 26 rid 27 rid ~~~ 0 rid 2 rid 3 rid ~~~
位 图 索 引 Oracle 7.3 版 本 开 始 引 入, 企 业 版 和 个 人 版 支 持, 标 准 版 不 支 持 位 图 索 引 是 一 个 索 引 键 条 目 存 储 指 向 多 个 指 针 ; 有 很 少 的 索 引 条 目, 每 个 条 目 指 向 多 行 行 值 :A/B A 1 0 0 1 B 0 1 1 0
用 户 创 建 用 户 test create user test identified by test default tablespace users quota unlimited on users temporary tablespace temp; 授 权 :create table,create index, connect,plustrace 角 色 等 等
实 验 表 建 表 : Create table order2 (a1 number(10), a2 varchar2(40), a3 varchar2(20), a4 number(10),a5 number(10), a6 varchar2(20),a7 varchar2(20)); 插 入 值 : begin for i in 1..10000000 Loop insert into order2 values(i,dbms_random.string( a',30), dbms_random.string( b',30), dbms_random.value(1,100), dbms_random.value(1000,100000), dbms_random.string( c',30), chr(round(dbms_random.value(97,98)))); if mod(i, 10000) = 0 then Commit; end if; end loop; end; / 表 说 明 :a1 字 段 中 的 值 为 唯 一 性 值, 各 不 相 同 ;a2 a3 a6 中 的 值 为 极 少 数 相 同 ;a4 100 个 不 同 的 值,100/10000000=0.00001, 比 例 非 常 小 ;a5 99000 个 值, 99000/10000000=0.0099 ;a7 只 有 两 个 值 2/10000000=0.0000002; 其 中 这 个 比 例 可 以 叫 做 基 数, 越 小, 选 择 做 位 图 索 引 越 有 可 能
语 句 执 行 跟 踪 使 用 autotrace 自 动 为 用 户 指 定 execution plan Sys 建 立 PLAN_TABLE 表 : @$ORACLE_HOME/rdbms/admin/utlxplan.sql, 建 立 plustrace 角 色 : @$ORACLE_HOME/sqlplus/admin/plustrace.sql Set autot on 或 者 set autotrace traceonly 每 一 次 执 行 查 询 插 入 等 操 作 均 执 行 shutdown immediate 清 空 缓 存
a1 未 建 立 索 引 Select * from order2 where a1=1; Select * from order2 where a1=20000; Select * from order2 where a1=9999999; Select * from order2 where a2=yuxvfnftll;
Select * from order2 where a1>=1 and a1<=1000000; Select * from order2 where a1>=1 and a1<=3000000
a1 建 立 b*tree 索 引 Create index ix_order2_a1 on order2(a1) nologging; Nologging 可 以 减 少 归 档 空 间
Select * from order2 where a1=1; Select * from order2 where a1=20000; Select * from order2 where a1=9999999; Select * from order2 where a2=yuxvfnftll;
Select * from order2 where a2= yuxvfnftll and a1=9999999;
Select * from order2 where a1>=1 and a1<=1000000; Select * from order2 where a1>=1 and a1<=1500000; Select * from order2 where a1>=1 and a1<=2000000; Select * from order2 where a1>=1 and a1<=2500000; Select * from order2 where a1>=1 and a1<=3000000;
Select * from order2 where a4=1; a4( 列 中 100 个 值 重 复 ) 作 为 条 件
a4 建 立 B*tree 索 引 : Create index ix_order2_a4 on order2(a4) nologging;
Select * from order2 where a4=1;
Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ;
Drop index ix_order2_a4; create bitmap index ix_order2_a4_b on order2(a4) nologging; 如 果 有 多 个 重 复 值,Bitmap 索 引 比 b*tree 节 省 物 理 空 间
Select * from order2 where a4=1;
( 属 性 表 ) 10 7 a1 Ix_order2_a1 a4 Ix_order2_a4 Ix_order2_a4_b 数 值 / 行 数 1 * 1/10 5 * * 类 型 * B*tree * B*tree Bitmap 索 引 大 小 * 192M * 160M 22M
(cost(%cpu) 表 ) 字 段 条 件 time 索 引 利 用 a1 ix_order 2_a1 1 行 : * %10: %30: 25~30s * 16807(3) 50~60s * 16849(4) 1 行 : %10: %15: %20: %25: %30: 0.40~0.50 18~22s 25~30s 38~42s 48~53s 60s 以 上 ( 利 ) ( 利 ) ( 利 ) ( 利 ) ( 未 ) ( 未 ) 4(0) 2898(1) 8562(1) 14226(1) 16839(4) 16846(4) a4 1 行 9~10s * 16216(4) ix_order 2_a4 ix_order 2_a4_b 1 行 1 行 9~10s ( 未 ) 16216(4) 8~9s ( 利 ) 9812(1) Cost (%cpu) 7~8s 16119(3)
a7( 只 有 a b 两 个 值 ) 未 建 立 索 引 Select * from order2 where a7= a ; Select * from order2 where a7= b ;
建 立 b*tree 索 引 : Create index ix_order2_a7 on order2(a7); Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ; Select * from order2 where a7= a ; Select * from order2 where a7= b ;
建 立 bitmap 索 引 Drop index ix_order2_a7; Create bitmap index ix_order2_a7_b on order2(a7) nologging; Select owner,segment_name,round(bytes/1024/1024,2) M from dba_segments where owner= TEST ; Select * from order2 where a7= a ; Select * from order2 where a7= b ;
ix_order2_a7_b,ix_order2_a4_b 比 较 10 7 a4 Ix_order2_a4 Ix_order2_a4_ b a7 Ix_order2_a7 Ix_order2_ a7_b 数 值 / 行 数 1/10 5 * * 2/10 7 * * 类 型 * B*tree Bitmap B*tree Bitmap 以 该 列 为 条 件 查 询 (s) 9~10s 9~10s( 未 利 用 索 引 ) 8~9s( 利 用 了 索 引 ) 80-90s 90-100s( 未 利 90-100s 用 索 引 ) ( 利 用 了 索 引 ) 索 引 大 小 * 160M 22M * 144M 4M 当 使 用 位 图 索 引 时, 如 果 表 中 的 行 数 比 较 少, 可 以 迅 速 得 到 结 果, 如 果 数 值 比 较 大, 看 似 时 间 上 没 有 什 么 变 化, 但 是 结 果 输 出 也 占 用 一 定 的 时 间, 对 于 查 询 的 性 能 还 是 提 高 的, 占 用 较 少 的 cpu 资 源
Update 测 试 以 上 索 引 均 正 常 情 况 下 : Update 1 行 : 00:0:13.93(table_full) Update 10 4 行 : 无 法 执 行,session 锁 住
建 立 主 键, 同 时 在 该 列 上 也 有 了 唯 一 索 引 drop index ix_order2_a1; 删 除 系 统 为 了 保 证 b*tree 索 引 的 唯 一 性 而 加 上 的 rowid 索 引 Alter table order2 add constraint cst_a1 primary key (a1); Select * from order2 where a1=1;
总 结 一 B*tree 索 引 可 以 很 好 的 自 动 平 衡 创 建 了 主 键, 同 时 创 建 了 索 引 ; 索 引 会 对 频 繁 的 DML 操 作 造 成 影 响, 而 不 合 适 的 位 图 索 引 造 成 的 结 果 是 严 重 的, 可 能 造 成 表 锁 会 话 锁 在 实 际 应 用 中, 应 结 合 表 数 据 特 点 检 索 行 数 主 外 键 多 表 结 合 检 索 等 因 素, 选 取 合 适 的 列 建 立 相 应 类 别 的 检 索
总 结 二 索 引 的 好 处 : 1 创 建 唯 一 性 索 引, 保 证 数 据 库 表 中 每 一 行 数 据 的 唯 一 性 2 大 大 加 快 数 据 的 检 索 速 度 3 加 速 表 和 表 之 间 的 连 接, 特 别 是 在 实 现 数 据 的 参 考 完 整 性 方 面 特 别 有 意 义 索 引 的 缺 点 : 1 索 引 降 低 了 数 据 库 插 入 修 改 删 除 等 维 护 任 务 的 速 度 2 创 建 索 引 和 维 护 索 引 要 耗 费 时 间, 这 种 时 间 随 着 数 据 量 的 增 加 而 增 加 3 每 一 个 索 引 要 占 一 定 的 物 理 空 间
结 束!
(cost(%cpu) 表 ) 10 7 a1 Ix_order2_a1 a4 Ix_order2_a4 Ix_order2_a4_b 数 值 / 行 数 1 * 1/10 5 * * 类 型 * B*tree * B*tree Bitmap 以 该 列 为 条 件 查 询 (s) 1 行 :7~8s %10: 25~30s %30: 50~60s 1 行 :0.40~0.50 ( 利 ) %10:18~22s ( 利 ) %15:25~30s ( 利 ) %20:38~42s ( 利 ) %25:48~53s ( 未 ) 9~10s 9~10( 未 ) Cost (%cpu): 16216 8~9( 利 用 了 索 引 ) Cost (%cpu):9812 %30:60s 以 上 ( 未 ) 索 引 大 小 * 192M * 160M 22M