Oracle 之索引,妙不可言(二)——BTREE索引上
BTREE索引(最常见,最实用)
- 创建语句
- CREATE INDEX index_name ON table_name (column_name);
- index_name:索引的名称,可任意命名。
- table_name:要在其上创建索引的表的名称。
- column_name:要创建索引的列的名称。
- CREATE INDEX index_name ON table_name (column_name);
- 主外键
- 三个特点
- 主键本身是一种索引
- 保证表中主键所在列的唯一性
- 可以有效的限制,外键依赖的表的记录的完整性
- 外键建索引表连接性能提高
- 外键建索引有效避免锁的竞争
- 主外键的约束
- 多表级联删除(有风险,慎用)
- 三个特点
- 组合索引
- 多列上的索引,一般不易超过三个字段索引
- 适合场景能避免回表
- 组合列返回越少越高效
- 索引列顺序
- 在等值(=)查询的情况下,组合索引的列无论哪列放前面,性能都一样
- 组合索引的两列,当一列是范围查询的情况下,等值查询列在前,范围查询列在后,这样的索引才最高效
- 组合查询
- in 的优化:in() 改为范围查询性能较低
- 如果单列的查询列和联合索引的前置列一样,那单列可以不建索引,直接利用联合索引来进行检索数据。
- 索引危害
- 变换角度看索引的危害
- 索引越多插入数据明显慢得多,索引个数与插入速度关系紧密
- 无序插入索引性能降低,有序插入影响更大
- 优化:索引失效,重建索引
- 修改删除与插入略有区别,索引过多的影响
- 对 insert 语句负面影响最大,有百害无来一利只要有索引插入就慢,越多越慢
- 对 delete 语句,有好有坏,在海量数据定位删除少数记录时,这个条件列是索引列显然是必要的,但是过多列有索引还是会影响明显,因其他的索引也要因此被更新。在经常要删除大量记录时,危害加剧
- 对 update 语句的负面影响最小,快速定位少量记录并更新的场景和delete类似,但具体修改某列时却有差别,不会触及其他索引列的维护
- 建索引动作引发的排序及锁
- 索引会引发排序,排序是非常耗CPU的动作
- 建索引的过程会产生锁,且不是行级锁,会把整个表锁着,对该表无法操作DML
- 如何合理控制索引数量
- 对需要跟踪的索引进行监控
- 监控
- alter index 索引名 monitoring usage;
- 解除监控
- alter index 索引名 nomonitoring usage;
- 监控
- 通过观察 v$object_usage 进行跟踪
- select * from v$object_usage;
- 数字字典查用户所有索引
- user_indexes
- 对需要跟踪的索引进行监控
- 变换角度看索引的危害
- 总结
- Oracle中的B-tree索引是按照键值进行排序的树形结构。B-tree是一种平衡树,可以支持快速的插入、删除和查找操作,在处理大量数据时具有很高的效率。
- B-tree索引叶子节点中存储了索引的键值以及对应行数据的物理存储位置。每个叶子节点还包含指向相邻节点的指针,可以帮助优化索引的扫描操作。
- 内部节点中存储了搜索条件需要使用的键值和指向子节点的指针。因为B-tree索引采用了分级结构,因此根节点有可能包含指向更小一级的节点的指针,这种设计可以提高索引扫描的效率。
- 查询时,数据库会从根节点开始以深度优先的方式遍历B-tree索引,直到查询到符合条件的行数据为止。B-tree索引通常在不同层级上存储了重复的键值,因此查询结果可能包含多个匹配的行数据。
- 总之,Oracle中的B-tree索引是一种非常高效的索引结构,可以帮助数据库快速地查找和处理大量的数据。
此文章为个人的笔记,如有写的不对或你还有什么高见,评论区见。