Oracle 索引类型
B 树索引
特定类型索引
确定索引列
主键和唯一键值列的索引
外键索引
其他合适的索引列
B 树索引
B 树索引算法
B 树是指 B-tree(Balanced Tree),B 树的存在是为了存储设备而设计的一种多分叉的树 B 树中
每个节点至多含有 m 个子节点
每个非叶子节点除了根节点之外至少含有 m/2 个子节点
根节点至少含有 2 个子节点否则它是叶子节点
有 k 个子节点的非叶子节点包含 k-1 个键值
所有叶子节点出现在同一层
其中, m 称为该 B 树的阶, 一个 3 阶 B 树的节点的排列如同
指针键值指针键值指针
当遍历到该节点时顺次搜索键值, 根据大小比较去向该键值前的指针指向的子节点还是该节点后的指针指向的
B+-tree 是 B-tree 的演变
区别在于 B + 树的每个索引键值对应一个子节点, 然后叶子节点保持对齐(Balanced)
B 树索引子类型
索引组织表
索引组织表 IOT 在一个 B 树索引结构中存储表行的所有内容
创建索引组织表
CREATE TABLE ORGANIZATION
索引组织表是一类特殊的表, 它将索引和表的数据存储在一起普通表的数据以无序 (Heap) 的方式存放在数据库中而索引组织表按照主键进行排序, 以二叉树 (B 树) 的方式对表的数据进行存储索引组织表不需要列 ID, 而是通过主键直接对表的数据进行访问
索引组织表的优点
快速随机访问: 索引和表存储在一起, 表就是索引自身, oracle 只需要索引就可以更新表
快速范围扫描:(Range)所有的数据都是按照主键进行排序的, 所以扫描速度非常容易接近 logN
存储需求变少: 索引和表存储在一起减少了索引的存储空间
缺点:
和访问十分快速的位图索引一样, 索引组织表的缺点在于更新表中的内容会导致大规模的索引重建, 因此索引组织表不是以用于频繁数据变动的表中
唯一索引
类似于唯一键值约束, 唯一索引保证所有插入表中的非空键值都是不同的
创建唯一索引通常与主键和唯一约束联合使用
CREATE UNIQUE INDEX
反向键索引
反向键索引可以应对大量顺序插入索引的 IO, 能够均匀的分布索引数据避免相似的键值大量的聚集在一起
创建反向索引
CREATE INDEX cust_ridx1 ON cust(cust_id) REVERSE
不能对位图索引或索引组织表指定 REVERSE 语句
反向键索引不能是降序类型
反向键索引的思路跟传统索引的思路正好相反, 传统的索引思路对密集型数据区域会增加索引密度以达到提升改高频访问地带搜索效率的目的但是反向键索引思路相反, 为了防止局部过热 (在数据规模过大的情况向有可能高频热点数据塞满了某一块磁盘的通道导致该局部数据过热) 有大量顺序数据插入导致该分区表索引树极其不平衡的情况下, 将建索引的值进行反向操作
绝对正向有序的数据在连续插入索引是, 如果索引的键值方案是反向, 则绝对正向有序的数据退化为随机数据(不是绝对反向数据), 进而我们保持了所引述的平衡性否则, 索引树在大量顺序插入时极端不平衡导致一侧分支的 I/O 次数大量增加, 进而产生前文说明的局部过热问题
键压缩索引
减少前导列经常重复的组合索引, 使用 COMPRESS N 创建压缩索引
CREATE INDEX cust_cidx_1 ON cust(last_name, first_name) COMPRESS 2
不能在位图索引上创建压缩索引
降序索引
B 树的算法中默认索引是升序索引, 创建降序索引, 可以通过 DESC 关键词反转降序
譬如, 在大数值密集型的数据中降序索引速度很快
- CREATE INDEX cust_didx1
- ON CUST(cust_id DESC);
特定索引类型
特定索引类型是专用于特定情况下的索引类型
位图索引
位图索引常用于数据仓库环境中, 适用于相对较低数量不同值的列
经常使用 INSERT/UPDATE/DELETE 等操作的数据库不应使用位图索引, 因为位图索引会在 DML 操作期间锁定多个行
使用关键字 BITMAP 创建位图索引
- CREATE BITMAP index f_sales_fk1
- ON f_sales(d_date_id);
位图索引算法
位图索引把某一列的所有值枚举出来后按位的方式 (1/0) 的方法列后, 对所有值为该枚举量的列标注为 1
如 test 表中有 state 这样一列, 10 行数据如下:
10 20 30 20 10 30 10 30 20 30
那么会建立三个位图, 如下:
- BLOCK1 KEY=10 1 0 0 0 1 0 1 0 0 0
- BLOCK2 KEY=20 1 0 0 0 1 0 1 0 0 0
- BLOCK3 KEY=30 1 0 0 0 1 0 1 0 0 0
位图索引统计的速度很快, 对行列进行连贯的按位操作是非常迅速地但是缺点是实体不应该经常改变, 因为所有的列与所有枚举值都有一一对应关系, 经常变动的实体集对位图索引的变动范围非常大
基于函数的索引
基于定义的 SQL 函数或表达式创建的索引
- CREATE INDEX cust_fidx1
- ON cust(upper(last_name));
分区索引
分区索引在逻辑上是一个索引, 但是在不同的段中实现, 能够保证很大的数据库也有良好的性能(分区索引是 oracle 的付费服务)
全局分区索引不映射到基础表的段, 可以为常规表或分区表建立全局分区索引, 用 B 树实现, 使用 GLOBAL PARTITION 创建全局分区索引
- CREATE INDEX f_sales_gidx1 ON f_sales(sales_amt)
- GLOBAL PARTITION BY RANGE(sales_amt)P
- PARTITION pg1 VALUES LESS THAN (25),
- PARTITION pg1 VALUES LESS THAN (50),
- PARTITION pg1 VALUES LESS THAN (maxvalue));
本地分区索引必须建立在分区表上, 与基础表遵循相同的分区策略
分区表与索引
分区表刚开始让我联想到磁盘的 MBR 分区表和 GPT 分区表在 oracle 数据库中从 10 支持多种分区表分区表类似于磁盘分区, 为数据添加物理隔断, 分区表之间是物理不相关联的这样做的好处是即便某个分区表的物理存储损坏也不会影响到其他数据
分区索引就是在所有每个区上单独创建索引, 它能自动维护, 在 drop 或 truncate 某个分区时不影响该索引的其他分区索引的使用, 也就是索引不会失效, 维护起来比较方便, 但是在查询性能稍微有影响
为分区表创建分区时主键必须包含分区列, 并且不能创建额外的约束, 原因还是物理空间的独立隔离
局部索引与全局索引
全局索引, 就是索引跨越分区表, 在做跨分区查询优势快;
局部索引, 分区表某个分区的索引, 速度比全局快, 而且维护更方便 ;
全局索引和局部索引的概念是与分区表有关的全局索引和局部索引的机制不同, 全局索引既可以分区也可以不分区, 但是全局索引必须是前缀索引
优化索引的方法
索引与约束相关
索引与约束相关相当于在索引期间就依照实体集的约束进行有条件的判断由于所有索引的依据都是它对应的实体集合, 因此实体集合的约束就是索引的约束, 索引约束是对无效搜索在索引阶段进行提前判断的有效方法
创建局部区索引
局部索引一方面节约大量的磁盘 IO 和存储空间开支, 因为只对实体集合的局部进行索引大幅度减少索引的存储量; 另一方面局部索引是对高频访问的实体区域进行有针对性的高密度索引, 让查找的速度尽量推进 logN
基础表必须是分区表
基础表构建为分区表一方面是处于保护数据的目的, 将不相关的物理设计进行隔离, 另一方面保持所有基础表单数据独立性映射到物理设计中的分离方法, 有利于对数据的操作逻辑规划与效能分析的对应关系建立
心得与总结
通过本学期的数据库学习和 Oracle 数据库索引技术的研究, 我对数据库技术有了更加全面的认识
最开始接触到数据库是网站方面的需求, 往往会选择较为流行和配置简单的 mysql 数据库进行数据操作使用到的功能一般也只有基础的数据库表操作, 完全是当作一个性能增强的 excel 使用通过本学期的数据库学习我对 oracle 数据库包括整个数据库体系有了完整的认识, 包括从抽象概念定义和基本的物理定义都有了清晰的了解, 懂得了数据库的数据关系和数据操作的思想
特别是数据库索引技术的扩展研究, 让我对大规模数据管理和系统假设的要点有了认识, 同时应用到了课程中学到的基本数据库模型和一些磁盘相关的知识巩固了基础的 Balance 树算法和构造方法, 同时对很多高级索引技术的思想有了认识
索引技术可以理解为对数据查询到一个预先准备通常, 速度越快的索引技术对于数据实体的静态性要求越高, 而这些索引建立时所对应的抽象层次相对较高, 需要一个比较坚实的数据基础, 否则频繁更新实体往往伴随着大规模的表重建操作
参考文献
Oracle 索引技术 (美)Darl Kuhn
图源: http://blog.csdn.net/v_JULY_v/article/details/6530142/
B 树的定义: https://en.wikipedia.org/wiki/B-tree
位图索引: https://www.cnblogs.com/LBSer/p/3322630.html
分区索引:
- http://ctwen.iteye.com/blog/2250320
- http://blog.itpub.net/35489/viewspace-616711/
索引组织表: http://blog.csdn.net/ljean/article/details/51360398
反向键索引: http://blog.csdn.net/zq9017197/article/details/7321604
来源: http://www.bubuko.com/infodetail-2515181.html