什么是索引

索引的主要作用就是加速表的查询。
索引可以显示创建，执行索引命令创建。
索引也可以隐式创建，主键和唯一键都是隐式创建索引。

索引的特征

1. 索引是模式Schema Object，高于衍生数据对象。和表一样，索引也有自己的段结构。
   衍生数据对象：视图、同义词都是从表衍生出来的。
   表和索引都具有自己的物理对象，段所对应的数据文件。
2. Oracle使用索引来进行检索加速，索引可以提升SELECT语句的查询速度。
3. 使用快速路径访问的方法快速查找数据，减少磁盘IO操作。快速访问实际上就是用ROWID加速的。
4. 索引与索引所在的表无关，索引都是对象，索引和他所在的表都是独立的。视图是表衍生出来的，视图和表是有关系的。
5. 索引是由Oracle自身进行更新和维护的。当表插入数据的时候，会自动更新表中列对应的索引数据。不需要人工干预。

索引的原理

索引的数据包含了索引列和Rowid。
rowid是每条记录的唯一ID，rowid和索引列对应起来，找到索引列的rowid，然后查找这一行的内容。

SELECT rowid, employee_id FROM employees;

ROWID EMPLOYEE_ID
------------------ -----------
AAAR6BAADAAALWLAAA 100
AAAR6BAADAAALWLAAB 101
AAAR6BAADAAALWLAAC 102

索引的创建。

隐式创建索引

定义了主键或者唯一键约束的时候，会自动创建一个唯一索引。

显示创建

用户可以手工通过创建索引的命令，在列上创建唯一或者非唯一索引，加速对数据的访问。
默认创建的索引是B平衡树索引，采用的BTREE算法。MYSQL使用的B+TREE索引。

Oracle索引的类型

1. 唯一索引： 既可以隐式创建，也可以显式创建。
2. B*TREE索引： 默认值
3. 位图索引： 数据仓库用的比较多
4. 复合索引： 将多个列做成一个索引
5. 函数索引： 当索引里需要函数转换的时候，可以预先创建一个函数索引。
6. 反向索引： 为了避免热块争用(批量Insert的时候)
7. 分区索引： 分区表中使用的索引。本地前缀分区索引、本地无前缀分区索引、全局前缀分区索引

创建索引的场合

不建议表中的索引过度，增加维护成本，影响查询速度。尤其是大量插入或更新的表，索引越多越危险。

1. 列中包含的较大范围的值
2. 列中包含了大量的值。
3. 在where子句或者联结条件中频繁使用的一个列或者多个列。
   使用多个列的时候，创建符合索引或者给每个列创建索引。
4. 表很大，但一般检索的数据不到表的2%至4%时。
5. 按照列中数据的重复度去选择：

• 重复度较低或者无重复，选择B*TREE索引或者唯一索引。
• 数据仓库的数据有一定重复度的话， 一般创建位图索引。

6. 加快分区表的查询速度的话，一般创建分区索引。
7. 为了避免热块争用，一般创建反向索引。

不适合创建索引的场合

1. 表比较小，全盘扫描的速度快于索引参与查询的速度。
2. 查询中不常使用的列。
3. 语句检索的数据超过表的2%至4%时。
4. 表更新比较频繁，造成索引会跟着频繁更新。
5. 被索引的列将被函数引用，比如对这个列进行函数操作，函数会造成索引失败。
   如果列被函数操作的话，建议使用函数索引。

创建索引的命令

CREATE （索引种类） INDEX 索引名[表名_列名_IDX] ON [模式SCHEMA.]表名(列名);

主键索引和唯一键索引

主键约束和唯一键约束是逻辑上控制的约束，而唯一键索引是具备物理结构的对象。
唯一键索引对字段进行唯一性检查，禁止该字段出现重复值，但是允许null插入。

create table test_index (id number);

CREATE UNIQUE INDEX TEST_INDEX_ID_IDX ON HR.TEST_INDEX(ID);

INSERT INTO test_index VALUES (1);
COMMIT;

INSERT INTO test_index VALUES (1);
ORA-00001: 违反唯一约束条件 (HR.TEST_INDEX_ID_IDX)

创建函数索引

正常来说，当查询的时候，对一个列的数据进行函数转换的时候，将不会走索引。
如果做了函数索引，对应的函数出现时，仍将走索引查询。

-- 创建一个depts表
CREATE TABLE depts AS SELECT * FROM departments where 1=1;

-- 创建一个大写字段名的函数索引
CREATE INDEX upper_depts_department_name ON depts(UPPER(department_name));

-- 创建一个大写字段名的函数索引
CREATE INDEX upper_depts_department_name ON depts(UPPER(department_name));

-- 以下查询将通过索引查找
SELECT * FROM depts WHERE UPPER(department_name) = 'SALES';

创建复合索引

用户可以在多个列上建立索引，这种索引叫作符合索引。符合索引在数据库操作期间所需的开销更小，可以替代多个单列索引。

窄索引： 索引列为1~2列的索引。
宽索引： 索引列超过两列的索引。

设计索引的一个重要原则就是能用窄索引不用宽索引，拥有更多的窄索引，能给优化程序更多的选择余地，有助于提高性能。

CREATE INDEX 索引名 ON 表名(列名1，列名2，列名3)
列名1 是前导列； 列名2，列名3是非前导列。
尽量把关系紧密的列创建为复合索引。

CREATE INDEX DEPTS_DEPTID_DEPTNAME_IDX ON depts(DEPARTMENT_ID, DEPARTMENT_NAME);
-- Index DEPTS_DEPTID_DEPTNAME_IDX 已创建。

确认索引

USER_INDEXES数据字典视图包含了索引的名称,表名和索引的唯一性。
USER_IND_COLUMNS 数据字典视图包含了索引名、表名和列名。

-- 创建实验表
CREATE TABLE EMP1 AS SELECT * FROM EMPLOYEES;

-- 创建基于employee_id的索引
CREATE INDEX EMP1_EMPLOYEE_ID ON EMP1(EMPLOYEE_ID);

--
SELECT IDX.INDEX_NAME, IDX.TABLE_NAME, IDXCOL.COLUMN_NAME,
IDX.UNIQUENESS
FROM USER_INDEXES IDX INNER JOIN USER_IND_COLUMNS IDXCOL
ON IDX.INDEX_NAME = IDXCOL.INDEX_NAME AND IDX.TABLE_NAME = IDXCOL.TABLE_NAME
WHERE IDX.TABLE_NAME = 'EMP1';

INDEX_NAME TABLE_NAME COLUMN_NAME UNIQUENESS
-------------------- -------------------- -------------------- --------------------
EMP1_EMPLOYEE_ID EMP1 EMPLOYEE_ID NONUNIQUE

删除索引

DROP INDEX 索引名字 ;

SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES WHERE TABLE_NAME = 'DEPTS';
-- UPPER_DEPTS_DEPARTMENT_NAME DEPTS

DROP INDEX UPPER_DEPTS_DEPARTMENT_NAME;
-- Index UPPER_DEPTS_DEPARTMENT_NAME已删除。

删除表的时候，索引也同样被删除。

DROP TABLE 表名 -> 会删除索引。