0%

oracle系列之索引

为什么加索引后会使写入、修改、删除变慢?

事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

什么情况下要同时在两个字段上建索引?

有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句

1
2
3
4
//建立索引
create index index_birthday on user_info(birthday);
//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1'

这句SQL语句的执行过程如下

  • 首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

  • 然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

  • 最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引

1
create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为:

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能.

https://www.cnblogs.com/aspwebchh/p/6652855.html

B树索引(B-Tree)

位图索引

位图索引的应用场景:

  1. 适合只有几个固定值的列,比如性别,行政区,祖籍等,这种基数很低的情况.
  2. 适合相对静态的数据,不适合值频繁更新的字段.
1
2
3
4
5
# demo:
create bitmap index random_empno_bmx on test_random(empno);

# 这个时候有人会说使用位图索引,因为busy只有两个值。好,我们使用位图索引索引busy字段!假设用户A使用update更新某个机器的busy值,比如update table set table.busy=1 where rowid=100;,但还没有commit,而用户B也使用update更新另一个机器的busy值,update table set table.busy=1 where rowid=12; 这个时候用户B怎么也更新不了,需要等待用户A commit。
  原因:用户A更新了某个机器的busy值为1,会导致所有busy为1的机器的位图向量发生改变,因此数据库会将busy=1的所有行锁定,只有commit之后才解锁。

http://hongyitong.github.io/2016/08/22/%E4%BD%8D%E5%9B%BE%E7%B4%A2%E5%BC%95%E5%8E%9F%E7%90%86%EF%BC%88BitMap%20index%EF%BC%89/

反向键索引

  1. 反向键索引是特殊的B数索引
  2. 适用于在表中严格排序的列上创建反向键索引

在常规的B树索引中,如果主键列时递增的,那么往表中添加新的数据时,B树索引将直接访问最后一个数据,而不是一个节点一个节点的访问。这种情况造成的现象是:随着数据行的不断添加,已经原有数据行的删除,B树索引的很多空间会被浪费掉,如1012节点上方的几个数据行都是空的。而且B树索引将会变得越来越不均匀。如下图这般。

反向键索引原理
如果用户使用序列编号在表中添加新的纪录,则反向键索引首先反向转换每个键值的字节,然后在反向后的新数据在进行索引.

例如:用户输入的索引键是4201,那么反向键索引将其反向转换为1024,这样索引键就变成非递增了,也就意味着,如果将反向后的索引键添加到叶子节点中,可能在任意的叶子节点中进行,从而使新的数据在值得范围分布式比原来的均匀。

原文:https://blog.csdn.net/zhou920786312/article/details/72792355

1
2
3
4
# 创建反向索引的demo
create unique index re_emp2no_idx on emp2(empno) reverse;
alter table emp2 add constraint pk_emp2 primary key (empno) USING INDEX re_emp2no_idx;
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM all_constraints WHERE TABLE_NAME = 'EMP2';