Feng's Blog

kiwidict.com

0%

oracle系列之索引

Posted on Edited on In

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

事物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， 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';