Oracle - 浅谈索引

索引本身非常复杂，外国的东西来到中国之后，语义产生的偏差，加上个人不同的理解，网络上索引的相关知识就显得特别乱。

（代码部分为 Oracle 语法）

索引

索引，就像书本上的目录，用于辅助快速找到所需的数据。

索引的创建与使用，就像刚学编程的时候，把数据从小到大排列，然后创建一个数组，把数据放进去，最后进行折半查找。

确定数据排序规则 => 选用适当的数据结构 => 查找数据。

简单分类

  1. 算法原理上看，分为 b-tree 、bitmap、hash、全文检索等等，想要深层了解索引，最好从算法开始学。

  2. 然后，索引有不同的应用场景，算法本质不变，但是结合业务场景做了调整，比如:

* 普通索引，就是很纯粹的索引；
* 唯一索引（主键），主键约束和唯一约束，需要索引支持，默认会添加索引；
* 函数索引，经常使用函数进行查询 (比如：substr)，则考虑使用函数索引；
* 聚簇索引，将数据存储在连续的磁盘单元上；
  1. 最后，是一些更加特殊场景，如果建立了分区，需要使用分区索引（Hash 索引），如果考虑开发全文检索的功能，就要使用域索引。

更细致划分（面试）

大学考试或者面试可能会遇到：

  1. 聚簇索引和非聚簇索引。

（这种分类方式，是从 SQL SERVER 开始的，聚簇索引是极其特殊的存在）

  1. 根据列的性质划分，分为：普通索引、唯一索引、主键索引、域索引。

（有些教学视频会这么划分）

  1. 按照字段个数，分为：单列索引和联合索引。

普通索引

--创建普通索引
CREATE INDEX TABLE1_T_NAME_UX ON TABLE1 (T_NAME);

--删除索引
DROP INDEX TABLE1_T_NAME_UX;

使用 CREATE INDEX 创建普通索引，既没有唯一约束，也不具备举聚簇的特点。

聚簇索引

聚簇索引是极其特殊的存在，三大数据库：mysql、oracle、sqlserver，实现方式都不相同。

直接说聚簇（clustering），可能理解不来，你把它翻译成集群，一下子就记住了，cluster 也有集群的意思。

简单地说：聚簇就是将数据集中存放在连续的物理块（一种数据存放方式）。

具体怎么使用连续的物理块？以主键索引为例，我们能想到很多种方式：

  • 把所有的主键，存放在连续的物理块？
  • 主键其实不连续，但是主键跟所指向的那一行数据，存放在连续的物理块？
  • 还是说，要把表中的每一行数据，存放在连续的物理块？（正确）

第三种说法是对的，聚簇索引会重新组织表的数据行，使它们按照索引键的顺序存储在磁盘上。

因为会对所有的数据排序，因此说，聚簇索引只能存在一个。

聚簇的优点：

前面的说法，有些抽象，可能理解不了聚簇的作用。

就以语文书为例，假设你的目录是这样，是不是看着很难受？

1. 静夜思…………120
2. 锄禾………………3
3. 将进酒…………32

你肯定是希望，书本的内容顺序，跟目录是一样。

索引决定了书本目录要怎么设计，而聚簇，决定了书本内容的排版方式。

还是前面那句话，三大数据库：mysql、oracle、sqlserver，最终实现方式都不相同，仍然可以进一步深入研究。

唯一索引

唯一索引（unique index），提到唯一索引，就不得不提唯一约束， 唯一约束就是在索引算法基础上，额外进行了一次判断，如果出现重复数据就报错。

如果我们细细观察，就会发现：当我们创建唯一约束时，oracle 会自动创建一个同名的索引，且该索引就是唯一索引。

为什么创建唯一约束的时候，需要默认添加索引？这样做有什么说法？

要保证数据唯一，就需要在新增的时候，把所有的数据查询一遍，如果已经存在，就抛出异常。 数据查询的过程，是十分耗时的，而索引功能，就是为了提高查询效率。

因此，有必要在创建主键，或者唯一索引的时候，默认添加索引。

--建立唯一约束
ALTER TABLE TABLE1
    ADD CONSTRAINT TABLE1_NAME_CONSTRAINT UNIQUE (T_NAME);

ALTER TABLE TABLE1
    ADD UNIQUE (T_NAME);

--删除立唯一约束  
ALTER TABLE TABLE1
    DROP CONSTRAINT TABLE1_NAME_CONSTRAINT;

--建表的时候创建唯一约束
CREATE TABLE EXAMPLE_TABLE
(
    T_NAME number not null,
    T_AGE  number,
    CONSTRAINT EXAMPLE_TABLE_T_NAME_CT UNIQUE (T_NAME)
);

DROP TABLE EXAMPLE_TABLE;

--禁用
ALTER TABLE TABLE1
    DISABLE CONSTRAINT TABLE1_NAME_CONSTRAINT;

--启用
ALTER TABLE TABLE1
    ENABLE CONSTRAINT TABLE1_NAME_CONSTRAINT;

--从user_indexes 和user_ind_columns 查找索引
select *
from user_indexes
where table_name = 'TABLE1';

select *
from user_ind_columns t;

--创建唯一索引的简写方式
CREATE UNIQUE INDEX TABLE1_T_NAME_UX ON TABLE1 (T_NAME)

函数索引

-- 如果查询过程中，经常对字段进行处理，比如：字符串截取
select *
from employee
where substr(ename, 1, 2) = 'XX';

-- 这时候就可以创建函数索引，这样，执行上面的查询语句时，索引就能排上用场
create index employee_ename_substr on eemp (substr(ename, 1, 2));

posted on 2018-06-05 19:21  疯狂的妞妞  阅读(190)  评论(0)  编辑  收藏  举报

导航