在学习索引之前,有必要了解一下B树的创建,B树索引就是为了优化数据库查找。
1.索引的概念
但我们的表中数据很多的时候(比如有1亿条数据),我们想找出一条符合特定条件的记录就会比较慢,这个时候,我们希望表中的数据是有序的,这样我们可以使用B树,诸如二分法之类的方法加快查询,而不是做全表扫描,但我们每次要查询的数据可能来自不同的列,我们也无法保证插入表中的数据就是有序的,怎么办呢?
1、 Oracle 的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度
2、Oracle数据库提供“索引”来解决这个问题,B树索引直接指向包含所查询值的行的位置,减少磁盘I/O。
3、 与所索引的表是相互独立的物理结构(类似,书本 [表] 与目录 [索引] 的关系,无索引,只不过是查询表的速度,性能方面有影响)
4、 Oracle 自动使用并维护索引,插入、删除、更新表后,自动更新索引(B树也称为二叉平衡树,具有矮胖,自平衡的特点,每次做DML操作,B树索引都会做一次自平衡,因此索引不适用在DML操作频繁的表中)
5、 语法:CREATE INDEX index ON table (column[, column]...);
我们可以根据我们查询条件要比较的列来创建“索引”,从索引开始查找。
2.索引的分类
2.1 逻辑分类
唯一索引/非唯一索引:对某一列或几列的键值(key)是否是唯一的,当某列任意两行的值都不相同时适合创建唯一索引(CREATE UNIQUE INDEX index ON table (column););当表建立Primary Key(主键)或者Unique constraint(唯一约束)时,唯一索引将被自动建立;
对一列或多列建的索引:索引分离于表,作为一个单独的个体存在,除了可以根据单个字段创建索引,也可以根据多列创建索引。Oracle要求创建索引最多不可超过32列。
基于函数(function-based)的索引:基于某些函数索引,当执行某些函数时需要对其进行计算,可以将某些函数的计算结果事先保存并加以索引,提高效率;
组合索引:当两个或多个列经常一起出现在where条件中时,则适合在这些列上同时创建组合索引;
域(domain)索引:索引数据库以外的数据,使用相对较少;
2.2 物理分类
B-tree索引(默认方式):Root为根节点,branch 为分支节点,leaf 到最下面一层称为叶子节点。每个节点表示一层,当查找某一数据时先读根节点,再读支节点,最后找到叶子节点。叶子节点会存放index entry (索引入口),每个索引入口对应一条记录(key的值、长度、rowid等)
位图(bitmap)索引:位图索引主要针对大量相同值的列而创建,位图索引通过不同位图取值直接的位运算(与或),来获取到结果集合向量(计算出的结果)。
3.案例
3.1 创建表
--1)创建表
create table czh_test_index(
id number,
a number,
b number,
c number
);
--2)新增100000条数据
begin
for i in 1 .. 100000 loop
insert into czh_test_index
values
(i,mod(i,2),mod(i,20000),mod(i,20000));
end loop;
commit;
end;
3.2 唯一索引
先修改id为主键(自动生成索引):
alter table czh_test_index add constraintpk_czh_test_indexprimary key (id) using index;
图1-唯一索引
图2-非主键查询全表扫描
图3
根据图1,2,3可以看出,查询同一条记录,所需要的成本是不一样的。因为图1的查询语句有两个特点,查询条件(id)在索引上。这条语句通过B树索引查询到rowid后,不需要额外在去原来的表里查数据了。为什么呢?因为索引包括根、枝、叶,叶子上存储的是索引值,包括:rowid、键值、键值长度、所属标号。看到没,如果所取的值都在索引上,就可以直接返回了。而图2的查询语句中,查询条件(b)不在索引上,所以只能走全表扫描了。
3.3 组合索引
先给a,b创建组合索引:
create index idx_test_id_ab on czh_test_index(a,b);
图4
图5
图6
根据图4,5,6可以看出,当两个或多个列经常一起出现在where条件中时,则适合在这些列上同时创建组合索引;图4走索引范围扫描,而图5,6走的是全表扫描。
3.4 函数索引
先创建函数索引:CREATE INDEX to_char_dept_name_idx1 ON czh_test_index(to_char(b,'fm99,99.00'));
当查询语句的Where条件中,对于某些列使用了函数表达式时,普通索引对查询没有帮助,如果想利用索引,则必须创建函数索引,比如在下面的例子中
图7
图8
对于图7的查询语句,如果建立普通索引,比如create index idx_test_id_b on czh_test_index(b);
那么上述SQL执行的时候,Oracle是不会走索引的,因此需要建立函数索引,如图8所示。
4.问题
4.1 在什么样的情况下创建索引对加快查询有利呢
答:查询条件中使用到这个列(或者这个列于其他列的组合),且这个列(或者与其他列的组合)上的数字
范围跨度很大,而大多数情况下我们要获取的数据的量占整个表的数据总量 小于4%;
4.2 在什么样的情况下不适合创建索引呢
答: 1)被查询的表本身就很小,即是是全表扫描也非常快; 或者基于这张表的查询,大多数情况下需要获取
的数据量都超过了总量的4%;或者这张表需要频繁的被更新,建立索引的话会引起索引的频繁更新,从而反而
降低数据库的整体效率。
=========================详细的各类索引后续,等我熟悉执行计划之后再完善=========================